Calibre Very Slow

[ladyoflorien]

Calibre was extemely slow and since I only have 5800 books, this seemed odd. I found the metadata.db file was 2GB in size. Finding this a ridiculous size for so few books, I poured over the file structure. I found 52 metadata.opf files that were over 1MB in size and some as large as 250MB. I either could edit the metadata for the book (I found some insanely large comments that needed to be shortened) within Calibre and it saved much smaller (less than 16KB), or I had to delete three books (the one I needed to be deleted would freeze Calibre unless it was sandwiched between two good books) and reimport them. Many hours later and Calibre works as it did before. The metadata.db file is now less than 15MB, significantly smaller than 2GB. Most of the large file sizes occurred on January 31, 2020 but the books were purchased many years prior. I'm not sure it's a bug but something happened on that date and I did not have any computer problems at that time nor do I recall Calibre doing anything odd.

I'm posting this in case others are struggling with Calibre being slow.

[theducks]

Your findings are NORMAL. The OPF just backs up what is IN the DB

You must have some VERY LARGE book comments in those with OPF files (or custom comment like notes)

Most of my books with typical comments fitting within a single window (no scroll bars) are 14K

FWIW, I have seen comments with a Sample chapter
You might want to EDIT those LARGER comments (Another download is not going to help, as that is where they probably came from )

[JSWolf]

Originally Posted by ladyoflorien

Calibre was extemely slow and since I only have 5800 books, this seemed odd. I found the metadata.db file was 2GB in size. Finding this a ridiculous size for so few books, I poured over the file structure. I found 52 metadata.opf files that were over 1MB in size and some as large as 250MB. I either could edit the metadata for the book (I found some insanely large comments that needed to be shortened) within Calibre and it saved much smaller (less than 16KB), or I had to delete three books (the one I needed to be deleted would freeze Calibre unless it was sandwiched between two good books) and reimport them. Many hours later and Calibre works as it did before. The metadata.db file is now less than 15MB, significantly smaller than 2GB. Most of the large file sizes occurred on January 31, 2020 but the books were purchased many years prior. I'm not sure it's a bug but something happened on that date and I did not have any computer problems at that time nor do I recall Calibre doing anything odd.

I'm posting this in case others are struggling with Calibre being slow.

It could be that you have a lot of wasted space in the database. Try compacting the database. To do this...

Right click on the library's icon in the toolbar and select Library maintenance > Check library and it will compact the database. Then try running Calibre again and see if it runs faster.

[BetterRed]

Originally Posted by ladyoflorien

Calibre was extemely slow and since I only have 5800 books, this seemed odd. I found the metadata.db file was 2GB in size. Finding this a ridiculous size for so few books, I poured over the file structure. I found 52 metadata.opf files that were over 1MB in size and some as large as 250MB. I either could edit the metadata for the book (I found some insanely large comments that needed to be shortened) within Calibre and it saved much smaller (less than 16KB), or I had to delete three books (the one I needed to be deleted would freeze Calibre unless it was sandwiched between two good books) and reimport them. Many hours later and Calibre works as it did before. The metadata.db file is now less than 15MB, significantly smaller than 2GB. Most of the large file sizes occurred on January 31, 2020 but the books were purchased many years prior. I'm not sure it's a bug but something happened on that date and I did not have any computer problems at that time nor do I recall Calibre doing anything odd.

I'm posting this in case others are struggling with Calibre being slow.

If it happens again (or to anyone else) run the sqlite3_analyzer.exe Utility Program. It will extract extensive statistics for a database (metadata.db) including physical space usage by table/index etc.

BR

[ladyoflorien]

Originally Posted by theducks

Your findings are NORMAL. The OPF just backs up what is IN the DB

You must have some VERY LARGE book comments in those with OPF files (or custom comment like notes)

Most of my books with typical comments fitting within a single window (no scroll bars) are 14K

FWIW, I have seen comments with a Sample chapter
You might want to EDIT those LARGER comments (Another download is not going to help, as that is where they probably came from )

I did not redownload the books. I just copied the epub file to a temporary location and redownloaded the metadata after reimporting the book. It came down with a very small comment section. I'd also point out that these books are from 2015 and earlier yet the metadata was updated on January 20th of this year, except I did not modify these books then. I don't know how they became modified.

Yes, there were a few books that had sample chapters or a lot of reviews in the comment section but these were relatively small. Again, the books were purchased prior to 2015 yet had their metadata updated on January 20th but not by myself. I did clean these up as well.

[ladyoflorien]

Originally Posted by JSWolf

It could be that you have a lot of wasted space in the database. Try compacting the database. To do this...

Right click on the library's icon in the toolbar and select Library maintenance > Check library and it will compact the database. Then try running Calibre again and see if it runs faster.

Hi JSWolf, I had already compacted the DB. I fixed the issue by cleaning up the big metadata.opf files, that somehow modified themselves on the 20th of January of this year.

[ladyoflorien]

Originally Posted by BetterRed

If it happens again (or to anyone else) run the sqlite3_analyzer.exe Utility Program. It will extract extensive statistics for a database (metadata.db) including physical space usage by table/index etc.

BR

Hi BetterRed, would sqlanalyze show how a file was modified? The files I fixed were from books purchased prior to 2015, yet their metadata was modified on January 20th of this year, but not by myself.

[ladyoflorien]

Originally Posted by theducks

Your findings are NORMAL. The OPF just backs up what is IN the DB

You must have some VERY LARGE book comments in those with OPF files (or custom comment like notes)

Most of my books with typical comments fitting within a single window (no scroll bars) are 14K

FWIW, I have seen comments with a Sample chapter
You might want to EDIT those LARGER comments (Another download is not going to help, as that is where they probably came from )

Hi theducks, The books that had the really large OPF files were purchased prior to 2015 yet their OPF files were modified on January 20th of this year. I did not modifiy them. I'd imported and read them years ago and I haven't had to do any metadata clean-up in years unless it's for a newly purchased book. I've also checked my library loans and my newly purchased books and none were purchased or borrowed on that date or even close to it. The only reason I'd have opened Calibre that day was to decide on a book to read. It's easier to search within Calibre than on an eReader, if one is very undecided.

[theducks]

You don't have the picture.
metadata.opf gets rewritten any time ANY of the data for that record in the DB changes. It is a Backup. Sometimes other activities trigger a rewrite.
Adding /removing a Column, making a change to a TAG that was also USED in that book. Obviously, you triggered a update on 20 Jan (btw there is a button item under maintenance:backup status, that forces a update)

It does not matter WHEN you downloaded a HOG of a comment. It is there until you open the MDE and PRUNE it (or delete the book)

[BetterRed]

Originally Posted by ladyoflorien

Hi BetterRed, would sqlanalyze show how a file was modified?

I'm not sure - best thing is to look for yourself? I don't have it currently installed on my calibre machine. But I suspect it doesn't.

The optional Audit Log plugin maintains a history of metadata changes, even lets you undo them, but you have to have it installed. See Index of plugins

Originally Posted by ladyoflorien

The files I fixed were from books purchased prior to 2015, yet their metadata was modified on January 20th of this year, but not by myself.

Some metadata changes will cascade through many books, e.g. a change to the Link field in the Manage Authors list will cascade through all books by that author. Is there something common about the books that had their metadata bloated.

Originally Posted by ladyoflorien

It's easier to search within Calibre than on an eReader, if one is very undecided.

Hmmm - - what optional plugins do you have installed?

BR

[ladyoflorien]

Originally Posted by theducks

You don't have the picture.
metadata.opf gets rewritten any time ANY of the data for that record in the DB changes. It is a Backup. Sometimes other activities trigger a rewrite.
Adding /removing a Column, making a change to a TAG that was also USED in that book. Obviously, you triggered a update on 20 Jan (btw there is a button item under maintenance:backup status, that forces a update)

It does not matter WHEN you downloaded a HOG of a comment. It is there until you open the MDE and PRUNE it (or delete the book)

Hi theducks, I am explaining myself extremely poorly, sorry. The books I had to delete and reimport did not have large comments. In fact, once reimported, their OPF file was on average 8KB. I know that at least 5 of the books I had to delete I had on my first Sony eReader back in 2010 and the metadata for those books hasn't been knowingly touched since then. Even if I can't recall making a change to a tag, I guess I must have. I know I didn't add a column because I haven't done that in years. I will keep in mind the maintenance:backup status if this ever happens again.

[ladyoflorien]

Originally Posted by BetterRed

I'm not sure - best thing is to look for yourself? I don't have it currently installed on my calibre machine. But I suspect it doesn't.

The optional Audit Log plugin maintains a history of metadata changes, even lets you undo them, but you have to have it installed. See Index of plugins


Some metadata changes will cascade through many books, e.g. a change to the Link field in the Manage Authors list will cascade through all books by that author. Is there something common about the books that had their metadata bloated.



Hmmm - - what optional plugins do you have installed?

BR

Hi BetterRed, Well, the only plugin I can think of that could have bolloxed a lot of books would be one called "Cleaning Comments". Now, that I might have run because I prefer to see plain text in the comments field.

[theducks]

Originally Posted by ladyoflorien

Hi BetterRed, Well, the only plugin I can think of that could have bolloxed a lot of books would be one called "Cleaning Comments". Now, that I might have run because I prefer to see plain text in the comments field.

THAT would touch many books and cause an UPDATE of the metadata.opf

ANY VALUE change for (used by) a book, causes THAT BOOK.
I said Used by because: fixing someting in the Tag Browser,, Links to All books where used. Calibre is a true (and pretty elegantly done) Relational database underneath. Define once, use many

[BetterRed]

Originally Posted by ladyoflorien

Hi BetterRed, Well, the only plugin I can think of that could have bolloxed a lot of books would be one called "Cleaning Comments". Now, that I might have run because I prefer to see plain text in the comments field.

Originally Posted by theducks

THAT would touch many books and cause an UPDATE of the metadata.opf

assuming one selected many books - e.g. the whole library.


@ladyoflorien - you wrote in your opening post that there were some 'insanely large comments' that you edited/removed - what was the nature of the 'insanity'?

I'm wondering if it might have been uuencoded data. I recall some discussion about replacing url links in Comments (e.g. to http://someplace.com/some_image.jpg) with the uuencoded binary data. I don't use the CleaningComments plugin, but if such a thing were to be done, then that plugin might provide a good place to do it.

BR

[ladyoflorien]

Originally Posted by BetterRed

assuming one selected many books - e.g. the whole library.


@ladyoflorien - you wrote in your opening post that there were some 'insanely large comments' that you edited/removed - what was the nature of the 'insanity'?

I'm wondering if it might have been uuencoded data. I recall some discussion about replacing url links in Comments (e.g. to http://someplace.com/some_image.jpg) with the uuencoded binary data. I don't use the CleaningComments plugin, but if such a thing were to be done, then that plugin might provide a good place to do it.

BR

@theducks and @betterred - The insanity was a lot of (thousands of) backslashes (\\\\\\\) in the comments field, at least for the comments fields I was able to edit.