Jump to content


Photo

4.2.0.32 - Vacuum database config switch


  • Please log in to reply
32 replies to this topic

#1 Luke OFFLINE  

Luke

    System Architect

  • Administrators
  • 156852 posts
  • Local time: 04:22 PM

Posted 17 July 2019 - 06:33 PM

Since many of you like to vacuum your databases, there is now a hidden config switch in the server configuration xml file.

 

For those not familiar, vacuuming your Sqlite database db files every once in a while can help compact them to a smaller size, and for some this may help improve performance. The server does not do this automatically because it can take a long time and no other database activity is possible while it is occurring. 

 

How to Vacuum 

Locate the system.xml file and set VacuumDatabaseOnStartup to true. On your next server startup, databases will be vacuumed, and this config switch will be automatically set back to false.

 

Important

  • This can take a long time depending your server machine performance and the size of your database files. For some it could take a few seconds, others several minutes or more.
  • There is no way to monitor progress
  • The best way to monitor for completion would be to wait until you see this in the server log:
  • Info App: Core startup complete
    
  • In the server's data folder, I would suggest backing up all the *.db files first.
  • Don't force kill the server process just because it's taking too long, you might be left with corrupted databases if you do.

Have fun.


  • FrostByte, Vicpa, MikePlanet and 9 others like this

#2 Happy2Play OFFLINE  

Happy2Play

    Trial and Error

  • Moderators
  • 19144 posts
  • Local time: 01:22 PM
  • LocationWashington State

Posted 17 July 2019 - 09:30 PM

Some OS users don't know how to get to that file.

 

A check box somewhere on maybe the Settings page to enable this flag for next restart would be nice.  Instead of having to get to the system.xml.


Edited by Happy2Play, 17 July 2019 - 09:38 PM.

  • ginjaninja, FrostByte, MikePlanet and 4 others like this

#3 abescalamis OFFLINE  

abescalamis

    Advanced Member

  • Members
  • 292 posts
  • Local time: 01:22 PM

Posted 17 July 2019 - 09:47 PM

Some OS users don't know how to get to that file.



A check box somewhere on maybe the Settings page to enable this flag for next restart would be nice. Instead of having to get to the system.xml.

Yep, I use a nas, and that option will be nice

#4 Luke OFFLINE  

Luke

    System Architect

  • Administrators
  • 156852 posts
  • Local time: 04:22 PM

Posted 17 July 2019 - 11:26 PM

Yea that does make sense but there's a lot more that would have to happen before this could go in the web interface. Right now it's targeting advanced users. You have to handle backing up the db files as well as restoring them if there's a problem. If we move this into the web interface then the server will have to handle all of this or else troubleshooting will be a nightmare. It's a good thing to do, just can't do it right now.


  • FrostByte, shocker, PuffyToesToo and 1 other like this

#5 neik OFFLINE  

neik

    Advanced Member

  • Members
  • 1142 posts
  • Local time: 10:22 PM

Posted 18 July 2019 - 01:57 AM

Nice stuff, indeed!



#6 CBers OFFLINE  

CBers

    Advanced Member

  • Moderators
  • 15919 posts
  • Local time: 09:22 PM
  • LocationEngland

Posted 18 July 2019 - 03:51 AM

Since many of you like to vacuum your databases, there is now a hidden config switch in the server configuration xml file.

 
I know I asked for it to be included, although running it manually isn't time-consuming, for the lay-person it's probably a bit scary.
 
So for the people that don't know, and would be hesitant to try it, can you explain in your own words what this process does and how it does it please.
 
Does the process take a copy of the library.db in case of failure ?
 
Are there full instructions on what to do in the event of a failure ?
 
Do we get a notification of a failure ?
 
Obviously if it all works OK, then that's fine, but it's when it goes wrong, we need to know what to do.
 
I guess there are a lot of questions about how you've implemented it, so is there a wiki entry ?
 
I'm not going to "try it and see", as it is a major issue if the library.db file gets corrupted/deleted.
 

Have fun.

 
Until it all goes wrong.

 

.


Edited by CBers, 18 July 2019 - 04:26 AM.

  • maegibbons and Senna like this

#7 pünktchen OFFLINE  

pünktchen

    Advanced Member

  • Members
  • 2524 posts
  • Local time: 10:22 PM

Posted 18 July 2019 - 06:17 AM



I know I asked for it to be included, although running it manually isn't time-consuming, for the lay-person it's probably a bit scary.

So for the people that don't know, and would be hesitant to try it, can you explain in your own words what this process does and how it does it please.

Does the process take a copy of the library.db in case of failure ?

Are there full instructions on what to do in the event of a failure ?

Do we get a notification of a failure ?

Obviously if it all works OK, then that's fine, but it's when it goes wrong, we need to know what to do.

I guess there are a lot of questions about how you've implemented it, so is there a wiki entry ?

I'm not going to "try it and see", as it is a major issue if the library.db file gets corrupted/deleted.


Until it all goes wrong.

.


Don't you read what Luke writes?

Important[list]
[*]In the server's data folder, I would suggest backing up all the *.db files first.



#8 CBers OFFLINE  

CBers

    Advanced Member

  • Moderators
  • 15919 posts
  • Local time: 09:22 PM
  • LocationEngland

Posted 18 July 2019 - 08:31 AM

Don't you read what Luke writes?

 
What Luke wrote means nothing.

No other instructions on what to do if anything goes wrong.
 
Come on @pünktchen, you're better than that.



#9 paulsalter OFFLINE  

paulsalter

    Advanced Member

  • Members
  • 396 posts
  • Local time: 09:22 PM

Posted 18 July 2019 - 08:34 AM

Thanks,

 

I bit the bullet and gave it a try, certainly seems faster after this

 

My library is fairly small, went from 240meg down to 130meg


  • CBers likes this

#10 CBers OFFLINE  

CBers

    Advanced Member

  • Moderators
  • 15919 posts
  • Local time: 09:22 PM
  • LocationEngland

Posted 18 July 2019 - 09:28 AM

OK, I also bit the bullet and it appears to have worked.

2019-07-18 14:18:53.305 Info App: Completed vacumming SqliteItemRepository after 4352ms
2019-07-18 14:18:53.305 Info App: Begin vacumming SqliteUserRepository
2019-07-18 14:18:53.316 Info App: Completed vacumming SqliteUserRepository after 12ms
2019-07-18 14:18:53.316 Info App: Begin vacumming AuthenticationRepository
2019-07-18 14:18:53.334 Info App: Completed vacumming AuthenticationRepository after 18ms
2019-07-18 14:18:53.334 Info App: Begin vacumming SqliteDisplayPreferencesRepository
2019-07-18 14:18:53.349 Info App: Completed vacumming SqliteDisplayPreferencesRepository after 15ms
It appears to have done the same as @PenkethBoy's script  that I was using.
 
It would be nice to see the sizes before and after logged, as @PenkethBoy's scripts does.
 
[18 July 2019 14:12:55] - Compacted Library.DB file has a size of:   72.71 MB
[18 July 2019 14:12:55] - Original (copied) Library DB file has a file size of:  202.82 MB
[18 July 2019 14:12:55] - Difference in size of Library DB files:  130.11 MB
Good effort for the first release :)
 


#11 pwhodges OFFLINE  

pwhodges

    Advanced Member

  • Members
  • 704 posts
  • Local time: 09:22 PM
  • LocationOxford, UK

Posted 18 July 2019 - 09:31 AM

 
What Luke wrote means nothing.

 

Actually, it clearly means that you shouldn't do this at present unless you can work out the answers for yourself, and that's why the switch is being kept pretty inaccessible.


  • pünktchen and Garbonzo17 like this

#12 mgworek OFFLINE  

mgworek

    Advanced Member

  • Members
  • 676 posts
  • Local time: 04:22 PM

Posted 18 July 2019 - 09:40 AM

I just ran it. Seemed to work. my library db went from 447meg to 331meg. It did create another library db that wasn't there before. library.db-wal which is 327meg. Not sure if it is a backup? Can I delete it?

 

It didn't seem to do anything with activitylog.db that is was and still is 522meg.  I just did a search and you said in a post awhile back that we can just delete it so I will probably do that so it starts over.


Edited by mgworek, 18 July 2019 - 09:51 AM.


#13 mgworek OFFLINE  

mgworek

    Advanced Member

  • Members
  • 676 posts
  • Local time: 04:22 PM

Posted 18 July 2019 - 09:45 AM

after a restart of the server, the extra library was gone.



#14 CBers OFFLINE  

CBers

    Advanced Member

  • Moderators
  • 15919 posts
  • Local time: 09:22 PM
  • LocationEngland

Posted 18 July 2019 - 09:51 AM


It did create another library db that wasn't there before. library.db-wal which is 327meg. Not sure if it is a backup? Can I delete it?
 
The library-wal.db file is the WRITE-AHEAD LOGGING file, which is used during normal operations for updates.
 
The updates are then written to the library.db file when it is closed, or periodically.
 
You will find that the wal files comes and goes on startup/changes and shutdown.
 
I'm sure someone else can explain it more eloquently than I just did :)
 

  • mgworek and Psyborg like this

#15 mgworek OFFLINE  

mgworek

    Advanced Member

  • Members
  • 676 posts
  • Local time: 04:22 PM

Posted 18 July 2019 - 09:52 AM

 

 
 
The library-wal.db file is the WRITE-AHEAD LOGGING file, which is used during normal operations for updates.
 
The updates are then written to the library.db file when it is closed, or periodically.
 
You will find that the wal files comes and goes on startup/changes and shutdown.
 
I'm sure someone else can explain it more eloquently than I just did :)
 

 

 

 

ah, thanks. I did not know that.



#16 GhostRider OFFLINE  

GhostRider

    Advanced Member

  • Members
  • 413 posts
  • Local time: 08:22 PM

Posted 18 July 2019 - 03:15 PM

What version of Emby is required for the  VacuumDatabaseOnStartup flag to be available in the system.xml file please?



#17 ebr OFFLINE  

ebr

    Chief Bottle Washer

  • Administrators
  • 51945 posts
  • Local time: 04:22 PM

Posted 18 July 2019 - 03:37 PM

What version of Emby is required for the  VacuumDatabaseOnStartup flag to be available in the system.xml file please?

 

Hi.  The version is indicated in the title of this topic :).



#18 djhifi OFFLINE  

djhifi

    Advanced Member

  • Members
  • 492 posts
  • Local time: 08:22 PM
  • LocationLisboa - Portugal

Posted 31 January 2020 - 10:36 AM

Sorry to dig this one up but just hearing of this as of now.

 

For Synology users is this now the better method or the "old":

 

/var/packages/EmbyServer/target/sqlite/bin/sqlite3 library.db "VACUUM" 
/var/packages/EmbyServer/target/sqlite/bin/sqlite3 library.db "ANALYZE" 
/var/packages/EmbyServer/target/sqlite/bin/sqlite3 library.db "REINDEX" 

 

Still works?

 

PS: Any plans for a hidden switch on the "Scheduled tasks" @Luke ? :)



#19 Luke OFFLINE  

Luke

    System Architect

  • Administrators
  • 156852 posts
  • Local time: 04:22 PM

Posted 31 January 2020 - 11:11 AM

Yes I would use this.

#20 djhifi OFFLINE  

djhifi

    Advanced Member

  • Members
  • 492 posts
  • Local time: 08:22 PM
  • LocationLisboa - Portugal

Posted 31 January 2020 - 11:30 PM

Yes I would use this.

 

I cannot find system.xml on my synology install. Using the "old" method. But is a "hidden" switch feature on the to-do list?






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users