Choose database engine ?

[Cerberus]

the DB contains JPEG thumbnails, so if you have a lot (especially images) there will not be much compression in either DB engine I assume

be good to see the test results though zip im really intrested in this thread as size on lower end system is very important.

[zip]

the DB contains JPEG thumbnails, so if you have a lot (especially images) there will not be much compression in either DB engine I assume

be good to see the test results though zip im really intrested in this thread as size on lower end system is very important.

sure, but regarding size, not sure what's the fuss about as the DB will max take what, 2x the size of a ripped DVD movie?

[patters]

I'm certainly more interested in lower latency than storage.

[Cerberus]

i like the idea of both of them.

[kairoh]

I haven't seen much difference : I should definitely redo the test with a bigger collection.

Derby v10.6.2.1 : 00:01:38,17 then 00:00:06,25
Derby v10.8.1.2 : 00:01:34,69 then 00:00:06,23
H2 v1.3.159 : 00:01:27,03 then 00:00:05,39

I have transalate the serviio db schema in a MySQL one, but can start the server at the moment has I have some problem with the connection classe, as I have to register the MySQL java driver.

You don't have to register the driver. You have to change serviio.sh to include the mysql.jar file instead of the derby.jar, and you have to change the connectionstring in the serviio.jar file. Anyway, that's as far as it goes, it starts the server alright, without logging any exception in the logfle. It even adds some values to the database (METADATA_EXTRACTOR_CONFIG table). However, every REST request from the serviio console fails with http error 500, without logging the exception (in normal log mode). I did not turn on more exceptionhandling, since my son wanted to watch a movie

To be continued, but I'm also interested in MySQL (or even SQLite) as backend. It makes editing metadata sooo much easier for this C/C++ developer...I'll turn on debug logging next, and hope to see an exception somewhere.

[Cerberus]

i still say Mysql on anything other than linux would be a pain in the arse, and to much buggering about to be worth it.

i still say Mysql on anything other than linux would be a pain in the arse, and to much buggering about to be worth it.

MySQL is a database, so it's not Linux or Windows... and Serviio is a Java application, so it's not Linux or Windows oriented too... Sorry, I don't understand what you're saying here. Can you explain ?

To be continued, but I'm also interested in MySQL (or even SQLite) as backend. It makes editing metadata sooo much easier for this C/C++ developer...I'll turn on debug logging next, and hope to see an exception somewhere.

Totally aggree with you , as a Java developper too... and I plan to build a Java app to manage my serviio medias databases. Plus, I already have some MySQL databases running on my Linux server (for Greyhole and other stuff...), so I would like to have only one database engine running on it, and I think that MySQL is the most common one and a really good performance worker (we use it in production environnement vs Sybase with really large databases here !).
Not to add that MySQL is also natively added in many built-in NAS.

Can someone add MySQL in the performance tests ? I would have test it, but I'm very busy at work (and at home ) since i'm back from holidays. I have a correct schema if someone need it.

Thanks all...

[moltra]

One thing about using MySQL is the license.

  Code:

If you are developing and distributing open source applications under the GPL License, then you are free to use MySQL under the GPL License. More Info »
If you are developing and distributing open source applications under an OSI-Approved License, but not the GPL, MySQL provides the GPL License with a FLOSS Exception. More Info »

I do not think that serviio falls in the above catagory since The source code is not public.

  Code:

For OEMs, ISVs, and VARs who distribute MySQL with their products, and do not license and distribute their source code under the GPL, MySQL provides a flexible OEM Commercial License. More Info »


I think that this is the one that serviio falls under, even tho there is no charge for Serviio, Serviio is not open source. I looked into this a while back and it gets expensive fast.

Now maybe a modification can be made by the end user to use MySQL, but I am not sure if that would meet the MySQL license requirements.

One thing about using MySQL is the license.

  Code:

If you are developing and distributing open source applications under the GPL License, then you are free to use MySQL under the GPL License. More Info »
If you are developing and distributing open source applications under an OSI-Approved License, but not the GPL, MySQL provides the GPL License with a FLOSS Exception. More Info »

I do not think that serviio falls in the above catagory since The source code is not public.

I think it does. Serviio doesn't use MySQL, it uses JDBC. As long as you don't distribute MySQL, or have the default connection set to MySQL, I don't see a problem. Otherwise a license has to be bought for every possible database system with which a connection can be made by JDBC.

The good news is that after making the tables in MySQL lowercase, Serviio seems to have no problem in indexing my movies. The Serviio Console also works fine. Too bad browsing files on the TV doesn't work:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY' at line 1

There seems no nice JDBC solution for this. JDBC only allows setMaxResults, which truncates the resultset at the application level instead of database level. setFetchSize is an option, but I'm not sure about that. Of course, I'm not sure if this is the only problem, I still have to index music and pictures.

[Cerberus]

i still say Mysql on anything other than linux would be a pain in the arse, and to much buggering about to be worth it.

MySQL is a database, so it's not Linux or Windows... and Serviio is a Java application, so it's not Linux or Windows oriented too... Sorry, I don't understand what you're saying here. Can you explain ?

MYsql is designed and installed as standard on linux systems ONLY, for it to run on a windows or MACosX system the user would have to manualy install the system files required for mysql to run seperately, and that is more hassle than it is worth as H2 and Derby are java based and dont require anything extra to be installed.

One thing about using MySQL is the license.

  Code:

If you are developing and distributing open source applications under the GPL License, then you are free to use MySQL under the GPL License. More Info »
If you are developing and distributing open source applications under an OSI-Approved License, but not the GPL, MySQL provides the GPL License with a FLOSS Exception. More Info »

I do not think that serviio falls in the above catagory since The source code is not public.

  Code:

For OEMs, ISVs, and VARs who distribute MySQL with their products, and do not license and distribute their source code under the GPL, MySQL provides a flexible OEM Commercial License. More Info »


I think that this is the one that serviio falls under, even tho there is no charge for Serviio, Serviio is not open source. I looked into this a while back and it gets expensive fast.

Now maybe a modification can be made by the end user to use MySQL, but I am not sure if that would meet the MySQL license requirements.

i agree moltra it look from what your saying like serviio would have issues

so again i say that Derby and H2 are much better options, and looking at the comparing of the 2 there little point changing from derby as the figure are almost identical.

OK, undertood what you mean... and you're probably right for that.

Derby or H2 as default database seems to be more simple for common users and you don't have to take care of the OS Serviio is running in.
But installing MySQL isn't quite difficult on Windows, and comes as standard with Linux (more than H2 or Derby, or others...).

Do you know the repartition between OS for Serviio ?

[Cerberus]

OK, undertood what you mean... and you're probably right for that.

Derby or H2 as default database seems to be more simple for common users and you don't have to take care of the OS Serviio is running in.
But installing MySQL isn't quite difficult on Windows, and comes as standard with Linux (more than H2 or Derby, or others...).

i agree its not difficult, BUT for the new user, even editing the log file to detail is beyond them, so installing mysql would be way out of there react.

Do you know the repartition between OS for Serviio ?

no sure exactly what u mean but serviio will run on following OS:

Window
Windows home server
Linux
BSD
MACosX
plus other more custom enviroments like NAS systems.

OK, undertood what you mean... and you're probably right for that.

Derby or H2 as default database seems to be more simple for common users and you don't have to take care of the OS Serviio is running in.
But installing MySQL isn't quite difficult on Windows, and comes as standard with Linux (more than H2 or Derby, or others...).

i agree its not difficult, BUT for the new user, even editing the log file to detail is beyond them, so installing mysql would be way out of there react.

Do you know the repartition between OS for Serviio ?

no sure exactly what u mean but serviio will run on following OS:

Window
Windows home server
Linux
BSD
MACosX
plus other more custom enviroments like NAS systems.

In which % of each ?

[moltra]

In which % of each ?

I do not think that we are tracking that right now. I would start a poll but do not se the command to make one.

[Cerberus]

ok i see what your getting at i can give you a ruff estimate from the user i have delt with:

Window - 55%
Windows home server - 5%
Linux\BSD - 10%
MACosX - 20%
plus other more custom enviroments like NAS systems. - 10%

In which % of each ?

I do not think that we are tracking that right now. I would start a poll but do not se the command to make one.

restricted to mods/admins i would expect

I do not think that we are tracking that right now. I would start a poll but do not se the command to make one.

We could ask zip for the webalizer stats of the releases.xml for that...anyway, I'm fine with either database engine, but I always prefer an engine which doesn't have a pretty strict requirement for programming language. Since zip won't add support for modifying the media information, you'll have to jump a lot of hoops to edit the database using a webinterface. That's the only reason I prefer another engine which is more widely supported, like MySQL, SQLite, ProgressSQL or something like that.

I do not think that we are tracking that right now. I would start a poll but do not se the command to make one.

We could ask zip for the webalizer stats of the releases.xml for that...anyway, I'm fine with either database engine, but I always prefer an engine which doesn't have a pretty strict requirement for programming language. Since zip won't add support for modifying the media information, you'll have to jump a lot of hoops to edit the database using a webinterface. That's the only reason I prefer another engine which is more widely supported, like MySQL, SQLite, ProgressSQL or something like that.

+1000

[Cerberus]

I do not think that we are tracking that right now. I would start a poll but do not se the command to make one.

We could ask zip for the webalizer stats of the releases.xml for that...anyway, I'm fine with either database engine, but I always prefer an engine which doesn't have a pretty strict requirement for programming language. Since zip won't add support for modifying the media information, you'll have to jump a lot of hoops to edit the database using a webinterface. That's the only reason I prefer another engine which is more widely supported, like MySQL, SQLite, ProgressSQL or something like that.

+1000

derby is just as easy to edit as mysql ( http://www.razorsql.com/docs/derby_data ... owser.html ) and all of your examples are aimed around linux and most of our user are not using linux.

derby is just as easy to edit as mysql ( http://www.razorsql.com/docs/derby_data ... owser.html ) and all of your examples are aimed around linux and most of our user are not using linux.

Not as easy, since those tools have a GUI. Users on headless machines (like NAS) can't use those, unless they open the database on a share or something like that. They will also have to stop Serviio, or the database will be locked. Maybe you guys know if there is a CLI version of a derby database editor? I couldn't find one.

I got the point though, and it's fine. This tiny downside doesn't weigh up against the excellent DLNA server Serviio is.

Edit: Or is 'ij' the CLI tool to use?