Hi sqlite-users, I'm working on improving the SQLite database performance for Songbird, an open-source media player based on Firefox/Mozilla. We're seeing pretty poor performance with large media libraries - ideally we'd like to support 100,000 media items and maintain reasonable performance. I'd really appreciate any help you can give (and so will our users)!
For reference, you can see the majority of our code that interacts with the DB here: http://src.songbirdnest.com/source/xref/client/components/dbengine/src/DatabaseEngine.cpp And the schema is here: http://src.songbirdnest.com/source/xref/client/components/library/localdatabase/content/schema.sql The high level is that we store a GUID for each media item and some top-level properties in one table (media_items), and all the user-visible metadata (track name, artist, album, etc.) in another (resource_properties). We use custom collation for things like sorting numbers correctly (so "2 Live Crew" comes before "10,000 Maniacs") and localization. I've updated to 3.7.0 (which seems to have better start-up and shut-down times, yay!) and poked around for a few days, and discovered some areas I'd like some help investigating. The first is memory usage - I'm pretty new to this stuff so bear with me ;) My test library has around 85,000 tracks, which ends up using about 300MB of DB. I have pre-allocated a 16000 page cache, with 16KB pages. My memory usage after using the DB for a while looks like: DumpMemoryStatistics() format Current Highwater SQLITE_STATUS_MEMORY_USED: 264849264 560992624 SQLITE_STATUS_PAGECACHE_USED: 8 15996 SQLITE_STATUS_PAGECACHE_OVERFLOW: 264435640 560259280 SQLITE_STATUS_SCRATCH_USED: 0 0 SQLITE_STATUS_SCRATCH_OVERFLOW: 0 102664 SQLITE_STATUS_MALLOC_SIZE 328 135807 SQLITE_STATUS_PARSER_STACK 0 0 SQLITE_STATUS_PAGECACHE_SIZE 16384 16384 SQLITE_STATUS_SCRATCH_SIZE 80536 102664 What surprises me is that the Highwater page-cache usage is nearly 100%, but the Current usage is generally less than 10 pages. Bumping the page size up to 32KB resulted in about twice as many pages in cache after some usage, but that still results in massive overflow (which I'm assuming is significantly slower). My initial guess is that the majority of the accesses are just too large to fit in cache... Does this sound reasonable? How would I go about correcting this? I'd also like some help with prepared statements and indexing. We use some seemingly complex queries, and I'm having trouble understanding which indexes and prepared statements are actually useful to improve the performance of these queries. Being relatively unfamiliar with SQL, some documentation is probably what I'm after at the moment. Lastly, if you have any suggestions on the schema we're using, that would probably be very helpful as well. Big thanks in advance :) -Mike _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
