On Thu, 9 Jul 2009 17:51:40 +0200 Sivert Berg <sive...@stud.ntnu.no> wrote: > Benchmarks: So.. was it all worth it? Well, you tell me: > (Warning: highly unscientific benchmarks) > +---------+------------+------------+-------------+ > | Backend | Disk space | List time* | Query time**| > +---------+------------+------------+-------------+ > | SQlite | 36.85 MB | ~10.6 s | ~0.22 s | > | S4 | 18.52 MB | ~ 2.0 s | ~0.14 s | > +---------+------------+------------+-------------+ > * xmms2 list > /dev/null > ** xmms2 mlib search "~foo" AND "id<4000" > /dev/null
I have several comments here. First of all the indices in the sqlite database (which take a lot of the diskspace) need to be cleaned up. Also does S4 have case-insensitive as well as case-sensitive indices? Secondly a generic match like ~foo (which is equivalent with the collparser-pattern ":*foo*"), is exactly where S4's stringstore pays off and sqlite is at a disadvantage. I think it would be fair to also test the performance of patterns like ":foo" and ":foo*". Moreover it is worth noting that the SQL-query xmms2 currently generates is sub-optimal. It is something like this: SELECT m0.id FROM Media AS m0 JOIN Media AS m1 ON m0.id = m1.id AND m1.key = 'album' JOIN Media AS m2 ON m0.id = m2.id AND m2.key = 'artist' WHERE m0.key = 'title' AND ( (m0.value LIKE '%foo%' OR m1.value LIKE '%foo%' OR m2.value LIKE '%foo') AND m0.id < 4000); Which in my database takes about 0.027 seconds. (This was measured directly on the database.) In coll2 (of which I am currently cleaning the history) it would be rendered differently. Something like this: SELECT id FROM ( SELECT id FROM Media WHERE key = 'artist' AND value LIKE '%foo%' UNION SELECT id FROM Media WHERE key = 'album' AND value LIKE '%foo%' UNION SELECT id FROM Media WHERE key = 'title' AND value LIKE '%foo%') WHERE id < 4000; Which in my database takes only 0.003 seconds, which is faster. > As you can see just listing all entries in the playlist S4 is about > 5 times as fast, or so it would seem, however running it in callgrind > reveals that only about 15% of the time is spent in S4, the rest is > spent packing the values into dicts and whatnot. For SQlite the time > spent inside the backend is ~55%. This is a bit weird, as 100% - 55% = 45% of 10.6 is 4.77, which should be the time spent outside the backend. 4.77 is however more than S4's 2.0 seconds total time?! > Drawbacks: You didn't think the performance improvements came for free > did you? The biggest drawback is that S4 isn't ACID-compliant, meaning > we don't guarantee anything. So if your little kid thinks your > powerplug looks like a fun toy and unplugs it all bets are off and you > may lose all data that has not been synced yet. However in most cases > this will not be a big deal as the medialib are mostly just read. You don't accidentally have S4 implemented in Berkeley DB as well? Great work! Erik Massop / nesciens P.S. Part of the sqlite session of my timing. sqlite> .timer on sqlite> SELECT m0.id FROM Media AS m0 JOIN Media AS m1 ON m0.id = m1.id AND m1.key = 'album' JOIN Media AS m2 ON m0.id = m2.id AND m2.key = 'artist' WHERE m0.key = 'title' AND ((m0.value LIKE '%foo%' OR m1.value LIKE '%foo%' OR m2.value LIKE '%foo') AND m0.id < 4000); 1276 145 155 132 CPU Time: user 0.029998 sys 0.003333 sqlite> SELECT id FROM (SELECT id FROM Media WHERE key = 'artist' AND value LIKE '%foo%' UNION SELECT id FROM Media WHERE key = 'album' AND value LIKE '%foo%' UNION SELECT id FROM Media WHERE key = 'title' AND value LIKE '%foo%') WHERE id < 4000; 132 145 155 1276 CPU Time: user 0.003333 sys 0.000000 -- _______________________________________________ Xmms2-devel mailing list Xmms2-devel@lists.xmms.se http://lists.xmms.se/cgi-bin/mailman/listinfo/xmms2-devel