Some background information about this plugin. As I mentioned in another
post, it started out as a helper for myself to investigate the effect of
auto_vacuum. I did notice that under certain circumstances some database
files (artwork/image proxy caches) could grow to massive size - and
wouldn't free the disk space any more. I've been using them with
auto_vacuum enabled for months without the slightest issue. Deletion
queries might be slightly slower than without, but we rarely do them,
and when we do, then it's either during a wipe & rescan, or in idle
times (cache purging).
Last week, while offline on a train ride, I decided to investigate an
issue some users have been reporting: searching for tracks using the web
UI with large collections was said to be "atrocious". I had no reason to
complain: it was reasonably quick with my Atom based NAS and my <20k
collection. But indeed, searching 100k tracks took several seconds. I
figured out a few interesting facts:
- the index built on the title is never used during searches, as we do
"select 'name%'..." to match partial matches. SQLite doesn't use indices
in this case but does search the full table. Much to my surprise this
wasn't the reason for the slow search: 100k tracks would return four
matches within less than 500ms reliably.
- the performance hit came from sorting these four records by title.
This part took well over 5 seconds (yes, more than 10x the time the
search took to sort four records!). This just didn't make sense to me.
Until I monitored disk I/0: much of that time was spent reading lots of
data from the disk. FWICT SQLite would load the indices for the sorting.
It's my understanding that loading the indices into memory for the
sorting is what took the longest! I repeatedly reproduced this with a
simple SQL query on the tracks table alone using a SQLite client.
This observation lead me to play with the buffer size. I set it to be
about the size of the library.db. And voilà: after an initial first
search during which all the data needed to be read, all searches were
returned within a fraction of a second.
I then reduced the buffer size to see whether there was a clear
threshold where things slowed down again. 20MB buffer were obviously not
good enough. 30MB were (library.db being about 140MB). I therefore guess
that today's 20MB are good enough for the vast majority of users. But
users beyond 100k tracks might greatly profit from using more memory.
Which kind of confirms what users have been saying: there's a threshold
after which LMS with SQLite is much slower than when using MySQL. The
buffer size might eliminate (or shift :-)) that threshold.
About the indices: all SQLite optimization FAQs recommend to create as
little indices as possible, as maintaining them is costly, but add as
many as needed. We do have quite a few indices we don't seem to be
using. I don't expect wonders from deleting them. But if we really don't
use them, then it would be reasonable to get rid of them anyway. Maybe
it would at least reduce the data loaded into the buffers.
I'm still looking for some feedback from a Windows user with > 100k
tracks...
--
Michael
_______________________________________________
beta mailing list
[email protected]
http://lists.slimdevices.com/mailman/listinfo/beta
