Register Guidelines E-Books Today's Posts Search

Go Back   MobileRead Forums > E-Book Software > Calibre > Library Management

Notices

Reply
 
Thread Tools Search this Thread
Old 06-14-2020, 11:01 AM   #1
ownedbycats
Custom User Title
ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.
 
ownedbycats's Avatar
 
Posts: 10,799
Karma: 74203799
Join Date: Oct 2018
Location: Canada
Device: Kobo Libra H2O, formerly Aura HD
Any rule of thumb of how often to vacuum the database via Check Library?

Pretty much as stated in title.
ownedbycats is online now   Reply With Quote
Old 06-14-2020, 11:22 AM   #2
kovidgoyal
creator of calibre
kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.
 
kovidgoyal's Avatar
 
Posts: 45,251
Karma: 27110894
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
Once in a blue moon )
kovidgoyal is offline   Reply With Quote
Advert
Old 06-14-2020, 02:56 PM   #3
DaltonST
Deviser
DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.
 
DaltonST's Avatar
 
Posts: 2,265
Karma: 2090983
Join Date: Aug 2013
Location: Texas
Device: none
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
DaltonST is offline   Reply With Quote
Old 07-05-2020, 01:01 PM   #4
droopy
Guru
droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.
 
Posts: 834
Karma: 2912460
Join Date: Apr 2009
Device: Kobo Forma
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
droopy is offline   Reply With Quote
Old 07-05-2020, 01:47 PM   #5
theducks
Well trained by Cats
theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.
 
theducks's Avatar
 
Posts: 30,939
Karma: 60358908
Join Date: Aug 2009
Location: The Central Coast of California
Device: Kobo Libra2,Kobo Aura2v1, K4NT(Fixed: New Bat.), Galaxy Tab A
Quote:
Originally Posted by droopy View Post
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
theducks is offline   Reply With Quote
Advert
Old 07-05-2020, 06:25 PM   #6
BetterRed
null operator (he/him)
BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.
 
Posts: 21,660
Karma: 29711016
Join Date: Mar 2012
Location: Sydney Australia
Device: none
I clean/vacuum/compact all databases as part of my weekly housekeeping (system management) job.

BR
BetterRed is offline   Reply With Quote
Old 07-05-2020, 06:41 PM   #7
ownedbycats
Custom User Title
ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.
 
ownedbycats's Avatar
 
Posts: 10,799
Karma: 74203799
Join Date: Oct 2018
Location: Canada
Device: Kobo Libra H2O, formerly Aura HD
Quote:
Originally Posted by theducks View Post
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.

Last edited by ownedbycats; 07-05-2020 at 06:44 PM.
ownedbycats is online now   Reply With Quote
Old 07-05-2020, 07:32 PM   #8
theducks
Well trained by Cats
theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.
 
theducks's Avatar
 
Posts: 30,939
Karma: 60358908
Join Date: Aug 2009
Location: The Central Coast of California
Device: Kobo Libra2,Kobo Aura2v1, K4NT(Fixed: New Bat.), Galaxy Tab A
Quote:
Originally Posted by ownedbycats View Post
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)
theducks is offline   Reply With Quote
Old 07-05-2020, 08:36 PM   #9
ownedbycats
Custom User Title
ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.
 
ownedbycats's Avatar
 
Posts: 10,799
Karma: 74203799
Join Date: Oct 2018
Location: Canada
Device: Kobo Libra H2O, formerly Aura HD
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.
ownedbycats is online now   Reply With Quote
Old 07-06-2020, 07:15 AM   #10
BetterRed
null operator (he/him)
BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.
 
Posts: 21,660
Karma: 29711016
Join Date: Mar 2012
Location: Sydney Australia
Device: none
Quote:
Originally Posted by theducks View Post
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
BetterRed is offline   Reply With Quote
Old 07-06-2020, 08:51 AM   #11
droopy
Guru
droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.
 
Posts: 834
Karma: 2912460
Join Date: Apr 2009
Device: Kobo Forma
Quote:
Originally Posted by theducks View Post
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?
droopy is offline   Reply With Quote
Old 07-06-2020, 10:38 AM   #12
ownedbycats
Custom User Title
ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.ownedbycats ought to be getting tired of karma fortunes by now.
 
ownedbycats's Avatar
 
Posts: 10,799
Karma: 74203799
Join Date: Oct 2018
Location: Canada
Device: Kobo Libra H2O, formerly Aura HD
Quote:
Originally Posted by droopy View Post
Awww, that's unfortunate. I wish last_modified didn't change.
Does this bump affect all books, without exception?
From my own experience yes.
ownedbycats is online now   Reply With Quote
Old 07-06-2020, 11:56 AM   #13
DNSB
Bibliophagist
DNSB ought to be getting tired of karma fortunes by now.DNSB ought to be getting tired of karma fortunes by now.DNSB ought to be getting tired of karma fortunes by now.DNSB ought to be getting tired of karma fortunes by now.DNSB ought to be getting tired of karma fortunes by now.DNSB ought to be getting tired of karma fortunes by now.DNSB ought to be getting tired of karma fortunes by now.DNSB ought to be getting tired of karma fortunes by now.DNSB ought to be getting tired of karma fortunes by now.DNSB ought to be getting tired of karma fortunes by now.DNSB ought to be getting tired of karma fortunes by now.
 
DNSB's Avatar
 
Posts: 45,338
Karma: 168808725
Join Date: Jul 2010
Location: Vancouver
Device: Kobo Sage, Libra Colour, Lenovo M8 FHD, Paperwhite 4, Tolino epos
Quote:
Originally Posted by droopy View Post
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.
DNSB is offline   Reply With Quote
Old 07-06-2020, 12:53 PM   #14
theducks
Well trained by Cats
theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.
 
theducks's Avatar
 
Posts: 30,939
Karma: 60358908
Join Date: Aug 2009
Location: The Central Coast of California
Device: Kobo Libra2,Kobo Aura2v1, K4NT(Fixed: New Bat.), Galaxy Tab A
Quote:
Originally Posted by droopy View Post
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)
theducks is offline   Reply With Quote
Old 07-06-2020, 01:12 PM   #15
droopy
Guru
droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.droopy ought to be getting tired of karma fortunes by now.
 
Posts: 834
Karma: 2912460
Join Date: Apr 2009
Device: Kobo Forma
Quote:
Originally Posted by DNSB View Post
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.
droopy is offline   Reply With Quote
Reply


Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to best format thumb drive to put my library on it LadyKate Library Management 18 12-09-2016 11:51 PM
How to fix "Extra Titles" in Database in " Check Library - Problems Found millan Library Management 33 07-16-2015 07:12 AM
What's your update rule-of-thumb? Anabana Calibre 20 06-13-2012 11:23 PM
Rule of Thumb, US Copyright Poppa1956 General Discussions 24 06-23-2010 04:38 AM
Rule of thumb-- never stand on a wombat. ardeegee Lounge 17 04-07-2010 01:42 PM


All times are GMT -4. The time now is 06:03 AM.


MobileRead.com is a privately owned, operated and funded community.