Choose database engine ?

[kairoh]

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

I will try to do some benchmark this week-end.

[Cerberus]

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...

personaly i would say Mysql isnt an option as users would have to manualy install that first before being able to use serviio (windows and Mac) but i liking the sound of H2, if someone could point me in direction of how to change to that id be very willing to give it a go. I can even do benchmark test if you require Zip.

[kairoh]

The last SQL script (for 0.6beta) is not H2 compliant : I've changed the syntax.
Here are my benchs results comparing H2 (h2-1.3.159.jar) to Derby on my PC (Intel Q6600 Quad Core @2.4GHz).
Graphs have been made with VisualVM (included in JDK6), you can open results with it.

VisualVm_File.zip

VisualVM Files

(15.2 KiB) Downloaded 285 times

Scenario :
- Fresh new Serviio 0.6b3, transcoding disabled.
- Delete Serviio db between bench.
- Create db. => 1st peak
- Scan a new directory (with around 200 media in sub dir). => 2nd peak
- Add some files (20 files). => 3rd peak

Stats (H2 / Derby) :
- Db size on disk (after test) : ~2Mo / ~3Mo
- Number of files (with lock file) : 3 / 148
- Jar size : 1226ko / 2567ko
- Max CPU usage : 10% / 30%
- Max Heap : 12Mo / 16Mo
- Max Permgen : 15Mo / 20Mo

Conclusion : it seems promising but it should be tested on other platform.

[zip]

that's great. Did you get any measurings in terms of how long it took to index/look for changes from serviio.log?

[kairoh]

By index/look, do you mean log from LibraryUpdatesCheckerThread and LibraryAdditionsCheckerThread?

Derby :

  Code:

2011-08-27 16:05:10,500 INFO  [LibraryUpdatesCheckerThread] Started looking for updates to currently shared files 
2011-08-27 16:05:39,812 INFO  [LibraryUpdatesCheckerThread] Finished looking for updates to currently shared files 
=> 29,312s
2011-08-27 16:05:39,921 INFO  [LibraryUpdatesCheckerThread] Started looking for updates to currently shared files 
2011-08-27 16:06:51,093 INFO  [LibraryUpdatesCheckerThread] Finished looking for updates to currently shared files
=> 71,172s
2011-08-27 16:05:10,484 INFO  [LibraryAdditionsCheckerThread] Started looking for newly added files 
2011-08-27 16:05:39,718 INFO  [LibraryAdditionsCheckerThread] Finished looking for newly added files
=> 29,234s
2011-08-27 16:05:39,921 INFO  [LibraryAdditionsCheckerThread] Started looking for newly added files 
2011-08-27 16:06:51,000 INFO  [LibraryAdditionsCheckerThread] Finished looking for newly added files 
=> 71,079s

H2 :

  Code:

2011-08-27 16:10:55,015 INFO  [LibraryUpdatesCheckerThread] Started looking for updates to currently shared files 
2011-08-27 16:11:17,250 INFO  [LibraryUpdatesCheckerThread] Finished looking for updates to currently shared files 
=> 22,235s
2011-08-27 16:11:17,343 INFO  [LibraryUpdatesCheckerThread] Started looking for updates to currently shared files 
2011-08-27 16:12:05,437 INFO  [LibraryUpdatesCheckerThread] Finished looking for updates to currently shared files 
=> 48,094s
2011-08-27 16:10:55,015 INFO  [LibraryAdditionsCheckerThread] Started looking for newly added files 
2011-08-27 16:11:17,140 INFO  [LibraryAdditionsCheckerThread] Finished looking for newly added files 
=> 22,125s
2011-08-27 16:11:17,343 INFO  [LibraryAdditionsCheckerThread] Started looking for newly added files 
2011-08-27 16:12:05,343 INFO  [LibraryAdditionsCheckerThread] Finished looking for newly added files 
=> 48s

[zip]

Thanks. Interesting it goes in these cycles, 29, 48 29, 48. Normally the first run for additions checker should be long (indexing all the media) and the subsequent runs should take similar time (just going through all shared folders and its files and checking if they exist in the DB.

Updates thread should take smaller amount of time at the beginning and then take longer as there is more files.

In both cases, H2 seems indeed faster so I'll consider switching at some point.

[kairoh]

In both cases, H2 seems indeed faster so I'll consider switching at some point.

Just ask me if you want a bitbucket ticket

[zip]

https://bitbucket.org/xnejp03/serviio/i ... than-derby

if you can comment on what changes need to be done it'd be good.

[kairoh]

if you can comment on what changes need to be done it'd be good.

Done!

[zip]

thanks

[wagstaff]

I am finding this discussion on alternate embedded databases very interesting. On a whim, I explored the derby.jar file delivered with the 0.6Beta3 version of Serviio. Based on the manifest, it appears to be v10.6.2.1 of Derby which was released last October.

I noticed that there have been two subsequent releases of Derby since that time with the most recent being v10.8.1.2 which was released in late April of this year. Without re-indexing my media collection, I temporarily moved the existing older version of the Derby jar file out of the Serviio /lib folder and replaced it with the newer v10.8.1.2 version of the file and re-started Serviio. I only have my own empirical observations to base this on, but running with this newer version of the Derby code is noticeably faster than the previous version. All operations on my renderer (a Samsung C-series TV) are quite noticeably faster running with his newer jar file.

I will spend some time and do some benchmarking of my own (including a re-indexing of my media collection with the version of Derby to be tested) to validate my observations, but we may have a horse race here!

Question for kairoh: When you performed your benchmarks on Derby, were you using the v10.6.2.1 of Derby bundled with Serviio v0.6Beta3 or a later version?

[zip]

I found out yesterday and already included the latest version for the 0.6 release

[kairoh]

When you performed your benchmarks on Derby, were you using the v10.6.2.1 of Derby bundled with Serviio v0.6Beta3 or a later version?

v10.6.2.1

[kairoh]

Stats (H2 / Derby) :
- Db size on disk (after test) : ~2Mo ~300ko / ~3Mo

I made a mistake during testing : I've killed the java process and H2 db.
When stopping properly, the db files are compacted (almost by 85% for my tiny db !).

[wagstaff]

First benchmark complete - re-indexing my complete media collection (758 GB of music and video).

Starting with a clean db and ending with a complete index ready to run using Serviio 0.6Beta3 and each Derby version:

Derby v10.6.2.1: 6:05:55
Derby v10.8.1.2: 2:06:44

Wow! The new Derby version cuts the indexing time by nearly 2/3rds!!! It's a keeper!!!

[Cerberus]

First benchmark complete - re-indexing my complete media collection (758 GB of music and video).

Starting with a clean db and ending with a complete index ready to run using Serviio 0.6Beta3 and each Derby version:

Derby v10.6.2.1: 6:05:55
Derby v10.8.1.2: 2:06:44

Wow! The new Derby version cuts the indexing time by nearly 2/3rds!!! It's a keeper!!!

sounds good but is H2 even faster? and fact H2 compresses the db so it tiny as well is a bonus.

[zip]

First benchmark complete - re-indexing my complete media collection (758 GB of music and video).

Starting with a clean db and ending with a complete index ready to run using Serviio 0.6Beta3 and each Derby version:

Derby v10.6.2.1: 6:05:55
Derby v10.8.1.2: 2:06:44

Wow! The new Derby version cuts the indexing time by nearly 2/3rds!!! It's a keeper!!!

great, thanks for the test. It'll be in the next beta so let's see if people can see the difference.

[wagstaff]

Cerberus wrote:
sounds good but is H2 even faster? and fact H2 compresses the db so it tiny as well is a bonus.

Ooops! Forgot to mention that the /db directory shrank slightly from 945MB down to 942MB with the Derby upgrade, so the potential space savings is considerably less impressive, but for the speed improvement, I'm still a huge fan.

As to the H2 vs. Derby speed, I will defer to kairoh on that as he did extensive testing with the previous version of Derby and it would most likely be easier for him to swap out the Derby jar files than it would be for me to try and replicate his benchmark conditions.

For kairoh: Are you up to replicating your earlier speed testing using the new Derby jar file? We would all like to know where this latest version of Derby compares speed-wise to H2.

[kairoh]

and fact H2 compresses the db so it tiny as well is a bonus.

It happened clearly on my test with a almost empty db. I've tried with a few more scans and now its around 12Mo vs 14Mo.

For kairoh: Are you up to replicating your earlier speed testing using the new Derby jar file?

Ok!

[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