Any rule of thumb of how often to vacuum the database via Check Library?

[droopy]

Originally Posted by DaltonST

If you make a lot of metadata changes via various plugins, you will see that the metadata.db file will grow enormously due to unclaimed SQLite database space.

Real example statistics from a SQLite browsing tool that does a compress:

Before compacting<<<----------->>>After compacting:
Page Count = 118,342<<<---------->>>Page Count = 3,606
Database Size = 121,182,208 bytes<<>>Database Size = 3,692,544 bytes

So, the metadata.db file went from over 121mb to less than 4mb in size.


If you have many Libraries, and if you use Job Spy, there is a menu option under "Utilities" named "Vacuum/Compress metadata.db [All Other Libraries]". It vacuums all except the current Library which is open in Calibre at the moment. Libraries must be "closed" to be vacuumed.

Dalton's page count was reduced to 1/40th of its original size and db size reduced to 1/33th. How does this affect Calibre performance?

What are the side effects of never vacuuming?


Kovid, do you think "vacuuming" should be automatically done by Calibre?
I ask for 2 reasons:
1. I don't want any changes to modification date
2. If it's beneficial, it's good for it to be automatic

[ownedbycats]

Originally Posted by droopy

What are the side effects of never vacuuming?

From other posts I've read on this forum, the metadata.db file gets oversized and over time the Calibre library starts slowing down, though I suppose this might also depend on the computer hardware too.

[davidfor]

Originally Posted by droopy

Dalton's page count was reduced to 1/40th of its original size and db size reduced to 1/33th. How does this affect Calibre performance?

It should only affect performance when starting calibre or when any database writes are done. Most of the latter will probably not be noticed as they are done in the background. Closing calibre might take longer as any database updates not already done have to be done.

Looking at the databases for my libraries, I suspect that @DaltonST's are an anomaly. The biggest is 16MB, and it's size changed by less than 1MB when I vacuumed it. The others are smaller and the change was similar. But, how much is needed depends on how many changes you are making. For those using an intake database, where books are added and removed constantly, it is probably more noticeable. For anyone adding one book a day, fixing the metadata and making no more changes, it probably isn't noticeable.

What are the side effects of never vacuuming?


Kovid, do you think "vacuuming" should be automatically done by Calibre?
I ask for 2 reasons:
1. I don't want any changes to modification date
2. If it's beneficial, it's good for it to be automatic

I don't see any changes to the book modification date. @theducks said something about it, but, the post seems to be incomplete. I think he meant when moving the book between libraries.

[kovidgoyal]

Like I said a long time ago in this thread, vacuuming needs to be done only once in a blue moon, if ever. I dont know what DaltonST does to his database, but any normal user is not going to be affected in any significant way by db fragmentation. And no vacuuming does not affect last modified date.