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

[ownedbycats]

Pretty much as stated in title.

[kovidgoyal]

Once in a blue moon )

[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.

Remember to make periodic full backups of all of your Libraries, and also before doing anything en-masse.



DaltonST

[droopy]

Until I read this post, I had no idea about the vacuuming databases.
Should everybody be vacuuming their databases?

I have just one Calibre library

[theducks]

Originally Posted by droopy

Until I read this post, I had no idea about the vacuuming databases.
Should everybody be vacuuming their databases?

I have just one Calibre library

The reason is Dust Bunnies
This is bloat from deleted records. The highest {ID} vs # of books (shown on the status bar) is a clue if the range has jumped a lot since the last cleaning. (Changing most anything will cause some dust. It just depends where. Understanding a bit about Relational DB's is needed. Adding a book to from an existing Author, makes no change to the Author table. It just uses the key# it was assigned for the new title. Changing (correcting) that name for the new book, WILL leave dust (was 2 Authors records, now back to 1)
For this kind of reason, having an 'Intake Library' for the dirty work of data cleaning before using 'Copy to Library:Main' , keeps the dirt down

In most cases, if your Library is smallish or Authors - Titles are stable, it probably does not need to be done
The deleted records are really marked inactive until housekeeping tosses the trash

Note that a side effect is the Last_Modified gets bumped. For the Library

[BetterRed]

I clean/vacuum/compact all databases as part of my weekly housekeeping (system management) job.

BR

[ownedbycats]

Originally Posted by theducks

For this kind of reason, having an 'Intake Library' for the dirty work of data cleaning before using 'Copy to Library:Main' , keeps the dirt down

I just use a virtual library for these kinds of tasks, and have the rest of my VLs set to exclude the @cleanup tag. Probably not best practice, but it works well enough for my purposes and keeps the imported tag clutter out of the way.

Plus I can use column icons to add a cute little broom (the polish icon) to any books needing cleanup.

[theducks]

Originally Posted by ownedbycats

I just use a virtual library for these kinds of tasks, and have the rest of my VLs set to exclude the @cleanup tag. Probably not best practice, but it works well enough for my purposes and keeps the imported tag clutter out of the way.

Plus I can use column icons to add a cute little broom (the polish icon) to any books needing cleanup.

A VL still dirties the DB (tables) if you delete or rename any field value.
There are many Tables within the DB and their Links. AFAIK once a use goes to Zero, the entry is not reused (recovered).

I rather guess BR makes hundreds of changes, thus making more dust.
OTOH I have caught up with most of my changes (all we need is all thosE writers to STOP for a WHILE ) So I only do mine monthly (or so)

[ownedbycats]

I've been doing more frequent vacuuming the last few weeks because I've been working on adding a huge cache of old fanfiction I had saved to my library. As PDF files, too (what was I thinking), and awful metadata where it even exists. I just drag them into Calibre in small batches, convert them to text so that FanFicFare can grab the URLs and re-download them. Except the ones that don't exist anymore that I have to manually fix the metadata. Must be quite a bit of dust there.

[BetterRed]

Originally Posted by theducks

I rather guess BR makes hundreds of changes, thus making more dust.

Nuh hardly any these days - legacy of decades of sysadm-ing etc on big iron.

BR

[droopy]

Originally Posted by theducks

Note that a side effect is the Last_Modified gets bumped. For the Library

Awww, that's unfortunate. I wish last_modified didn't change.
Does this bump affect all books, without exception?

[ownedbycats]

Originally Posted by droopy

Awww, that's unfortunate. I wish last_modified didn't change.
Does this bump affect all books, without exception?

From my own experience yes.

[DNSB]

Originally Posted by droopy

Awww, that's unfortunate. I wish last_modified didn't change.
Does this bump affect all books, without exception?

One of the Job Spy plugins metadata options is to reset the last modified to the added date/time. One friend of mine uses it so when she is finished modifying the books in her Working library, she resets the date and sends them to her Main library.

[theducks]

Originally Posted by droopy

Awww, that's unfortunate. I wish last_modified didn't change.
Does this bump affect all books, without exception?

AFAIK
Yes
You are affecting the entire DB with phase 1 (even if there is no real data change unless corruption was repaired)

[droopy]

Originally Posted by DNSB

One of the Job Spy plugins metadata options is to reset the last modified to the added date/time. One friend of mine uses it so when she is finished modifying the books in her Working library, she resets the date and sends them to her Main library.

Thanks. I plan to check that option out.