Large Library Performance Comparisons

[Down South]

I ran some performance comparisions to try and shed some light on
what was happening and possible solutions.
I used 6 drive configurations ranging in speed from an OCZ Diesel 8GB
USB flash drive to 2 Samsung 830 - 128GB SSD's in RAID 0.
I tested with and without a 1.5GB RAM drive for TEMP files and the metadata.db.

Using Process Monitor to see what Calibre was doing and Process Explorer
to see performance and contol CPU affinity allowed me to look at what was
happening when opening the library. (Sysinternals.com)

PROCESSING the data in the metadata.db file which is read in 1K chunks
is what takes all the time.
Any reasonably fast drive will give equal times to open or refresh Calibre.
Let me rephrase that. Use any drive you like. The speed will be the same.

Copying JUST the 51 MB metadata.db file from my 38,000 record library
to 6 different devices and then opening or refreshing the library
on each device took ~17 seconds (same as opening the actual library).

Underclocking and overclocking slow and speed up Calibre by
proportionate amounts of time.
CPU 50% faster 2.4GHZ -> 3.6 GHZ (3/2) = opens in 2/3 the time. ~11 secs.
CPU 2/3 the speed 1.6 GHZ = 3/2 the opening time. ~25 secs.

Calibre/SimpleSQL? uses only 1 processor/core so an Intel Q6600 (My CPU)
(4X 2.4 GHZ) is SLOWER than a single core @ 3 GHZ of the SAME family.
This can be seen in Process Explorer and CPU affinity from 1-4 CPU's
can be assigned to Calibre with benchmarks remaining the same.

Perhaps the more robust version of SQL uses Multiple CPU's?
If so I will be happy to contribute so Kovid can buy and use a
database engine that can multiprocess and open my library 3-4 times faster.

Please do NOT take my word for it. Run your own benchmarks and report back.
I live on a mountain in Mexico and only get to town for the internet every 7-14 days so please do not expect a rapid response from me.

Kovid: Thank you for providing such a fine, multifaceted program.
I KNOW you never expected it to take so much of your time and to
grow to what is has become which is a program being used far beyond it's
original design parameters.

If I remember correctly full versions of SQL ran on 8 or more CPU's and handled 100's of thousands of records with a search or update time under 5 seconds.
That is what we had in the mainframe world at clients like State Farm and AT&T.
I doubt the version of SQL in Calibre is designed to do so.

[DoctorOhh]

Moderator Notice
You posted to a 2 year old thread and calibre has gone through about 100 revisions since then. Thank you for this info. I moved the post to its own thread.

[PeterT]

While it is possible that other database engines might make use of more processing power and threads, remember that the challenge is to find a multi-platform engine. Don't forget that calibre runs on OS/X, Windows (32 and 64 bit), as well as Linux, so whatever engine is used HAS to support all of those environments.

[BetterRed]

Originally Posted by PeterT

While it is possible that other database engines might make use of more processing power and threads, remember that the challenge is to find a multi-platform engine. Don't forget that calibre runs on OS/X, Windows (32 and 64 bit), as well as Linux, so whatever engine is used HAS to support all of those environments.

And it has to be free to use for the end user - donating towards buying Kovid a copy of a high performance enterprise database and the iron to run it on might be good for Kovid, but who's going to buy it for the rest of us.

Christine Lagarde could put in on her Get Out of Jail for Free card

BR

[speakingtohe]

I am not sure what we are comparing here so sorry if this is not relevant.

In my large calibre library, moving from one edited fied to another takes approximately 8 seconds. In a smaller library it seems instant. However in the large library editing metadata and going from one edited record to the next seems instant and the changes show up in the GUI instantly. The only delay is when I exit from the edit metadata dialog to the GUI and it is still much shorter than the delay in moving from edited field to another field in the Gui.

Same 2 databases, converting 100 books takes upwards of 30 minutes in large library and calibre is in a non-resnsive state for about 10-30 minute after the job number is zero.
Calibre stops responding during the entire process and I am lucky to be able to play Solitaire The little icon in the corner does not whir around and clicking anywhere gives a white screen flash.

Same task in smaller library with same books takes less than 10 minutes total and both calibre and other programs are not noticeably slower. The little icon keeps whirring and I can view the job list etc.

I don't see this as a database problem, but I am not an expert.

Not complaining mind you, as it is my choice to have all my books and documents in one library.

Helen

[kovidgoyal]

@speakingtohe: Hide the Tag browser, that should improve your large library performance. After every edit, the entire tag browser has to be re-calculated, which is very slow on a large library.

[BetterRed]

Originally Posted by speakingtohe

Same 2 databases, converting 100 books takes upwards of 30 minutes in large library and calibre is in a non-resnsive state for about 10-30 minute after the job number is zero.

Could this behaviour be related to this thread - https://www.mobileread.com/forums/sho...d.php?t=215232 - I'm not sure if opf files are written after conversion

See recent exchange between theducks and myself

BR

[speakingtohe]

Originally Posted by kovidgoyal

@speakingtohe: Hide the Tag browser, that should improve your large library performance. After every edit, the entire tag browser has to be re-calculated, which is very slow on a large library.

Well that made a huge difference in the speed of going from one edited field to another and the little icon is whirring away.


Thanks a great big bunch. I love the tag browser, but I guess I can hide it and unhide it. The spirit is willing but the memory is weak.)

Helen

[theducks]

Kovid
Have you thought of making a 'auto-hide' function similar to that available in Linux Unity or the Windows taskbar? n-seconds of no use and it tucks its self away.

[kovidgoyal]

No need for needless complexity, I am working on a db backend re-write that (among other things) maintains a normalised view of the data in memory which should greatly improve the tag browsers performance. This is a long term project, though, so no promises.