Hello,
I guess this subject is a bit worn out. But I am having scalabillity
problems with SQLite in XMMS2. We have dimensioned XMMS2 to handle
insanely huge media libraries, playlists and clients. Our goal is to
be able to run medialibs with 50.000 files without problem. Our
backend is SQLite3. When we get somewhere around 160000 rows (10k
entries) we're starting to see problems with scalabillity of SQLite3.
I would like some views on how we could speed up the storage backend
and also comments on our structure.
Today we save all data in one table called Media. Each entry has a id
number and each id number can have a indefinitive number of
properties. To keep this flexible and clean we choose to add one row
per property. A property can be "artist", "album", "url" and more.
The schema is this:
create table Media (id integer, key, value, source integer);
and the indexes:
create unique index key_idx on Media (id,key,source);
create index prop_idx on Media (key,value);
create index source_idx on Media (key,source);
create index key_source_val_idx on Media (key,source,value);
The most common query is something like this:
select value from Media where id=5 and key='url' and source=1;
This query remains very fast no matter how many entries I have in my
database also things like:
select key, value from Media where id=5 and source=1;
is still very fast.
But more advanced queries like "show me all albums and artists that
are not compilations" are very slow:
select distinct m1.value as artist, ifnull(m2.value,'[unknown]') as
album from Media m1 left join Media m2 on m1.id = m2.id and
m2.key='album' left join Media m3 on m1.id = m3.id and
m3.key='compilation' where m1.key='artist' and m3.value is null;
In fact, whenever I join with myself and try to extract a big number
of values it can take forever to get the result.
I have tried to increase the cache_size to somewhere around 32000 to
see if it made any difference, it didn't.
idxchk tells me that the good indexes are in use.
Any comments, help or blame is welcome to try to solve this issue of
scalabillity. You can download a medialib here:
http://debian.as/~skid/medialib.db.gz this contains almost 200000
rows and 14000 songs and is a real user library.
Looking forward to getting your input.
Thanks
Tobias
