Choose database engine ?

Hi,

First of all, I must say that Serviio works great, and that it's really a nice piece of software.
Sorry if I have already posted those thoughs in another thread, but I think it was misplaced.

As I experiment some performance problem with my numerous files on my server, I was asking myself if it can be a benefit to put it on another database engine as Serviio is acceding to the database every X min to check if metadata already exists. It can be very long with numerous files.

I think that one piece of the problem is the java browsing. There's a hope with Java 7 in this direction which is "event oriented", and so Serviio don't have to always browse all files in all directories... but simply ask for a change in it.

The other piece is perhaps the embeded java database engine. I know that embeded database can be very fast as they are load in memory, but there's one moment where the database cannot fit into memory Plus, a native system database is surelly a little more optimized than a Java one.
Don't know really if Derby is faster than MySQL or other db engine, but is it possible to have the connection URL in a propertie file, in order to choose the db.

Also, I already have a MySQL engine running on my linux DLNA server, and it would be nice to put Serviio db in it (only one db engine on the server, less memory usage). I imagine that using Derby is probably a "deployment choice", as it more easier as the database engine is embeded with the server... and I can understand and agreed, but with putting the database URL in a properties file, some of us can easily choose to change the db engine.

As a Java developper, I know that there's a little code change in the Connection Pool class, but the choice is yours of course

Hope I can by heard

Have a nice day.

[moltra]

I am starting to develop databases commercially, and derby actually looks like a good embedded / server database.

Here is a site that has a Comparison of OpenJPA with MySQL server vs Hibernate with Derby server

http://www.jpab.org/OpenJPA/MySQL/server/Hibernate/Derby/server.html

here is another.

http://www.encorewiki.org/display/encore/Open+Source+Databases+Comparison

Thanks for the links...

For the first link, I can see that it's very close between the two, but MySQL seems to have average better performance.
For the second, it's the contrary ! lol...
And here, Derby is the slowest ! (http://www.h2database.com/html/performance.html)

I think that it's probably just a personnal choice in fact... so putting the db connection URL in a propertie file can be interesting lol

[moltra]

Also you have to look at the licenses. If you use Mysql you have to share your source code. unless you buy the licenses. Derby you do not.

You're right ! I probably have to have multiple database engine on my server so

Zip, any chance to put the connection URL in serviio properties file ?

Thanks a lot

[moltra]

Also in the new 0.6 beta Zip has been optimizing the database to improve speed.

[zip]

DB URL is in properties file inside serviio.jar. Obviously I'm not sure if it'll work as Derby might use some non-standard SQL syntax, but feel free to give it a go.

Hi !

I've done a some "research" in servioo.jar... and found db_schema_url. That's a nice maven project (pom.xml
I've read that the source code isn't downloadable.
But do you authorize to reverse the java build classes in order to modify the DB connection classes and try to rebuild the project with a connection to a MySQL db for example ?
That would be some works without the source code and the project structure but I'm really interested in comparing the results between Derby and MySQL. Plus, I can build some web apps based on Serviio's library metadatas. I've read that some are interested in seeing their library infos...

[zip]

yes, you can reverse engineer it, for your own use

I guest there's no way to have the maven project structure to help me, even for my own use?

[zip]

I think kairoh managed to run serviio on H2 database just by changing the URL and driver, so first give that a try before changing the code itself.

Ok, I will try as soon as I have created the database structure...
Derby SQL syntax isn't very "SQL normalized", and I have to modify your SQL scripts


Ok, done...
But I have to modify the DbConnectionPool class to register mysql driver.
I'm trying to recreate the maven project in order to compile the classes but I miss the serviio-parent project...

Hi Zip,

For future release and improved file performance, perhaps you could have a look at this (file notification). I just tried it and it works like a charm... The fact is that you need to go to Java 7...

http://download.oracle.com/javase/tutor ... ation.html

Cheers,

[zip]

it's something I'm looking at... but will wait for java 7 to be more spread across different platforms

[kairoh]

I think kairoh managed to run serviio on H2 database just by changing the URL and driver, so first give that a try before changing the code itself.

Serviio works easily on H2 (don't even need Derby compatibility mode).
You just have to change the url and remove serviio's db shutdown hook (cause h2 provides one).
Here is the bitbucket issue.
H2 is faster than Derby, so is the scanning process. I uses fewer files than Derby and its memory footprint is better.

The drawback is the database should be recreate from scratch.

I've already tried with Hsqldb but there where issues with current sql code.
I think many other db (Mysql, PostgreSQL Oracle, ...) will present this kind of issue too.

[patters]

Those comparison stats you posted about H2 look impressive, especially for the low power embedded systems running Serviio like NAS units.
Is it really such a big deal to have to rebuild the DB going to a new major version release? Personally I wouldn't let that reason hold back such a clear performance bump, doubly so considering how many forum posts there are bemoaning high CPU use.

[Cerberus]

For future release and improved file performance, perhaps you could have a look at this (file notification). I just tried it and it works like a charm... The fact is that you need to go to Java 7...

i stand with ZIP on this point yes brilliant faster performance but if it wont work with all platform then its useless..

Those comparison stats you posted about H2 look impressive, especially for the low power embedded systems running Serviio like NAS units.
Is it really such a big deal to have to rebuild the DB going to a new major version release? Personally I wouldn't let that reason hold back such a clear performance bump, doubly so considering how many forum posts there are bemoaning high CPU use.

i second that if it really is significantly faster then a switch maybe a good idea going into 0.6 BUT as for the people complaining of the high cpu usage they need to understand that even with a different db engine the Transcoding process will still use alot of cpu as well so gain wont be as most will be expecting, i would expect only to see about 10% max decrease in cpu usage overall.

my only question is will it work for ALL platforms currently supported by serviio? Windows xp/vista/7, Mac OSx, Linux, BSD.

[kairoh]

i second that if it really is significantly faster then a switch maybe a good idea going into 0.6 BUT as for the people complaining of the high cpu usage they need to understand that even with a different db engine the Transcoding process will still use alot of cpu as well so gain wont be as most will be expecting, i would expect only to see about 10% max decrease in cpu usage overall.

The total scanning process is faster (cause SQL instructions are faster), and memory and disk usage should be smaller too.
I don't think CPU usage will be significantly reduced.
In fact, H2 is more multi-threaded.
Transcoding should not be affected by the database.

my only question is will it work for ALL platforms currently supported by serviio? Windows xp/vista/7, Mac OSx, Linux, BSD.

Yes it will : H2 is pure java database just like Derby.

[zip]

kairoh, would it be possible to run a test with both DB's and measure:
1) the time it takes to add a set of files
2) the time it takes to check if there are any new files (ie second run of the loop) and if you can see any high CPU usage in this step (FFmpeg not running)
3) the time it takes to check if there are any updates to the currently shared files (make sure the checkbox is selected in the console)

Obviously this would have to be run on a relatively big file set (e.g. 100). Also run it in a clean installation of the latest beta (includes some added indexes).

If it's too much bother, don't worry.

Hi all !

I was on holidays these last weeks, so I haven't been working on the MySQL tests.

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.

Can the 0.6 release of serviio add a more configurable connection classes, based on properties file in order to let people "choose" their db engine ? I can help in that as I'm very interested with that.

Cheers...