XMMS2 Database Structure

[Florian Kristen]

Hello,

i use the xmms2 on my CarMediacenter. I find the Database is so slow and
so big. Ok i show the Entity-relationship model of the xmms2 sqlite
Databes. I so wondering about this interesting structure, after this i
don't wondering why the database is so big. So many Data are dublicated in
the Database - sorry about this statement but this is no database model.
Why you have not designe the Databese on the Normalization rules?
(http://en.wikipedia.org/wiki/Database_normalization)

Ok i have some advancement proposal about the Database to earn more
performance and need less space.
On my carmediacenter i have an embedded Processor(Geode LX800) (10GB
Music) with have not so many performance.
So i can offer the Project my Database experience and i would redesigne
the Database and costomize the server code on the sql selecte querys and
the rest.

Greets
Florian



--

[Sebastien Cevey]

On 3 Aug 2010, at 13:17, Florian Kristen wrote:

Hey Florian,

Thanks for your feedback.

So many Data are dublicated in the Database

This is correct – this causes the database to be larger than it could be.
Slow and big are different issues though.

May I ask how big your database is (how many media, and how large is the db file)?
What kind of performance qualifies as slow, e.g. how long do slow query take, and what kind of queries are they?

Why you have not designe the Databese on the Normalization rules?
(http://en.wikipedia.org/wiki/Database_normalization)

There are a couple of reasons, among which:
1) Properties are free-form, in that the set of keys for a media is not known ahead of time, which means you cannot have a simple property-column mapping. It is possible to set any arbitrary property on a media.
2) A property value is identified by a [media, source, key] tuple, not a simple [media, key] tuple. Again, the set of sources is not set so that makes it harder to normalise the model.

So our database is more like a key-value store (or to be precise, a source-key-value tuple store), and yes it is a bit of a stretch to use sqlite for this. Although using SQL (often MySQL) as a backend for a k/v store is somewhat popular too.

Fortunately, we have a Google Summer of Code student working on a new medialib backend based on a more memory-efficient (and hopefully more performant and extendable) schema. You can check his progress in his git repo if you're curious:

http://git.xmms.se/?p=xmms2-cippo.git;a=summary

So hopefully, it'll all get better quite soon !

[Sebastien Cevey]

I only noticed afterwards that the discussion had turned private, so here it is for your collective pleasure.

Date: 3 August 2010 14:22:03 GMT+01:00
Subject: Re: XMMS2 Database Structure
Hi Sébastien ,
thanks for your replay!
Ok my Database is 24mb big and only mp3 files that are round about 10gb
medias.
I know the reason about key/value storage but i think this is very
confertable but on this system you don't need it - because the flexibility
on meda is not so high!
I prefere a fast and smal database on this system if you have a external
SQL Server with performance than the key/value is ok but not so nice!
So i find the player very good and interresting but i must say for
embedded devices is the player only limited usefull because the storage. I
think the player have some future and aceptable for embedded devices. The
Softwaredesign is very nice!
Ok i hope the Student build a powerfull Mediastorage system.
Greets
Florian Kristen.

Post by Sebastien Cevey
Hey Florian,
Thanks for your feedback.

So many Data are dublicated in the Database

This is correct ? this causes the database to be larger than it could be.
Slow and big are different issues though.
May I ask how big your database is (how many media, and how large is the
db file)? What kind of performance qualifies as slow, e.g. how long do
slow query take, and what kind of queries are they?

Why you have not designe the Databese on the Normalization rules?
(http://en.wikipedia.org/wiki/Database_normalization)

1) Properties are free-form, in that the set of keys for a media is not
known ahead of time, which means you cannot have a simple property-column
mapping. It is possible to set any arbitrary property on a media. 2) A
property value is identified by a [media, source, key] tuple, not a
simple [media, key] tuple. Again, the set of sources is not set so that
makes it harder to normalise the model.
So our database is more like a key-value store (or to be precise, a
source-key-value tuple store), and yes it is a bit of a stretch to use
sqlite for this. Although using SQL (often MySQL) as a backend for a k/v
store is somewhat popular too.
Fortunately, we have a Google Summer of Code student working on a new
medialib backend based on a more memory-efficient (and hopefully more
performant and extendable) schema. You can check his progress in his git
http://git.xmms.se/?p=xmms2-cippo.git;a=summary
So hopefully, it'll all get better quite soon !
--
Sébastien Cevey / inso.cc

--
Florian Kristen
Münchnerstraß2 24a
82178 Puchheim
Tel: 0 89 / 84 93 31 64
Fax: 0 89 / 84 93 31 71
Handy: 0176 - 4550 1665

[Sebastien Cevey]

Date: 4 August 2010 08:21:08 GMT+01:00
Subject: Re: XMMS2 Database Structure
Hi,
i know subselect and join's.
Your can desing your database as you like, but for this soulution you
don't need a database you have a flat storage.
Ok for a nice database design you need some tables and you don't have
dublicates. In your design if you have an literal error on
Artist/Genre/Album you must corect more lines.
A table Line in a database is a tuple.
So is quite easy for extra lines for example {"lastfm_popularity" =>
"0.13"}, you have a reverenc table in this you can have some v/k storage
and this value is an exclusion.
Don't misunderstand me i will give you some ideas/proposals and will not
criticize your work. Your/Our work on xmms2 is very nice and the idee of
the design very good!
Maby i will change for me the database desing in xmms2, because i need
performance on an slow embedded procesor and have not so many disk space.
If i do this i will it do not on all features. It's a litelbit another
approach of your imagen of an multimedia player. On the modern PC's this
storage have realy no problem and it's quit easy. If i do this change and
your/our interresting on this change you can have it (git-branch ...).
Greets
Flo

ok i try in your Table Media to get All albums with there Interprets in
one Select/command. It's not realy posible without sql views etc.

You don't need views, you can use JOINs or subqueries. See our query
builder.

For Example on a normal DB with 3 Tables: Artist, Album, Media

As I explained in my first email, this model doesn't take into account
the fact that: 1) custom properties can be set on a media (e.g.
{"lastfm_popularity" => "0.13"}); dynamically creating tables for all new
properties sounds very wrong, and you'd need to either add a column to
Media, or another intermediate table to link Media to the new table. We
could have tables for the "standard" metadata (there isn't really such a
thing presently) and special tables for extra metadata, but that's still
tedious.
2) values are addressed by a [media, *source*, key] tuple. Your example
doesn't work with sources, so you'd need a more complex schema to handle
that.

So you see it quite simple and the SQL is efective and much fuster as a
own implementation and it work without testing.

Yes, but it doesn't solve the same problem as the current medialib does.

In a key/value Storage is to komplicatet and fault-prone for the same
result, sorry i dont's see any benefit on k/v Storage it is a very old
way to resulve this problem.

Relational databases are old, Tuple-stores are the future, haven't you
heard? :-)
--
Sébastien Cevey / inso.cc

--
Florian Kristen
Münchnerstraß2 24a
82178 Puchheim
Tel: 0 89 / 84 93 31 64
Fax: 0 89 / 84 93 31 71
Handy: 0176 - 4550 1665

[Sebastien Cevey]

Date: 3 August 2010 15:02:30 GMT+01:00
Subject: Re: XMMS2 Database Structure
Hi Sebastien,
ok i try in your Table Media to get All albums with there Interprets in
one Select/command.
It's not realy posible without sql views etc.
For Example on a normal DB with 3 Tables: Artist, Album, Media
[Artist]
id = Numeric Autoincrement
name = String Artist Name
Genre = Artist Genre
...
[Album]
id = Numeric Autoincrement
idArtist = Reverence Id to Artist ID
name = Album Name
year = Numeric
cover = BIN for Pictures
....
[Media]
id = Numeric Autoincrement
idAlbum = Revernece ID to Album ID
name = Trac Name
length = Numeric
url = String
...
Ok now you will get all Albums in the Medialib is so easy
SELECT Artist.name, Album.name FROM Artist, Album
WHERE Artist.id = Album.idArtist GROUP BY Album.id
Get all Media with Info Album, Arist TitleName
SELECT Artist.name, Album.name, Media.name FROM Artist, Album, Media
WHERE Artist.id = Album.idArtist AND Album.id = Media.idAlbum
So you see it quite simple and the SQL is efective and much fuster as a
own implementation and it work without testing.
In a key/value Storage is to komplicatet and fault-prone for the same
result, sorry i dont's see any benefit on k/v Storage it is a very old way
to resulve this problem.
If your need help or tips you can ask me about database and so on.
Greets Flo

Post by Sebastien Cevey
Hey Florian,
Thanks for your feedback.

So many Data are dublicated in the Database

This is correct ? this causes the database to be larger than it could be.
Slow and big are different issues though.
May I ask how big your database is (how many media, and how large is the
db file)? What kind of performance qualifies as slow, e.g. how long do
slow query take, and what kind of queries are they?

Why you have not designe the Databese on the Normalization rules?
(http://en.wikipedia.org/wiki/Database_normalization)

1) Properties are free-form, in that the set of keys for a media is not
known ahead of time, which means you cannot have a simple property-column
mapping. It is possible to set any arbitrary property on a media. 2) A
property value is identified by a [media, source, key] tuple, not a
simple [media, key] tuple. Again, the set of sources is not set so that
makes it harder to normalise the model.
So our database is more like a key-value store (or to be precise, a
source-key-value tuple store), and yes it is a bit of a stretch to use
sqlite for this. Although using SQL (often MySQL) as a backend for a k/v
store is somewhat popular too.
Fortunately, we have a Google Summer of Code student working on a new
medialib backend based on a more memory-efficient (and hopefully more
performant and extendable) schema. You can check his progress in his git
http://git.xmms.se/?p=xmms2-cippo.git;a=summary
So hopefully, it'll all get better quite soon !
--
Sébastien Cevey / inso.cc

--
Florian Kristen
Münchnerstraß2 24a
82178 Puchheim
Tel: 0 89 / 84 93 31 64
Fax: 0 89 / 84 93 31 71
Handy: 0176 - 4550 1665

[Peter Stuge]

Florian,

ok i try in your Table Media to get All albums

Forget about the SQL database. It's the current solution, but it will
go away.

The S4 database is an amazing improvement in performance. Further, it
is a datastore which fits perfectly with how it will be used by
xmms2.

I agree that it would be possible to improve the SQL schema in xmms2
so that it has better performance. I disagree that this is a good
idea. It's better to keep using the present one which works, but
which is slow, until S4 is finished.

Did you already try to use S4 in your setup? You will notice a big
difference.


//Peter

--