SQLiteDatabase Plugin for MP2 (1 Viewer)

[MJGraf]

Hi Lehmnden,

thanks a lot for your test!

The cpu usage is something I can't reproduce - for me it is about 20% during the import (having a core i5). Reducing the thread priority is something I can't do here with the database plugin. This would have to be done in the ImportWorkerThread.
But nevertheless, the freezes and slow responding in v0.1 resulted from write locks blocking reads to the database. The ImportWorker does a lot of writes and when you are browsing through your GUI in the client, there are reads to the database. In v0.2, this should be much improved due to the use of "WAL" (see above).

Unfortunately, the v0.1 database file is not compatible with v0.2 (because I increased the page size and now store GUIDs as binaries instead of strings).

Regarding the idea of having only one connection with multiple transactions mentioned above, this is neither possible nor necessary. If you set "pooling=true" in the Connection string (was already contained in v0.2), closing a connection does not "really close" the database file. The connection is just given back to the pool and reused later. So no need for improvement there.

What I finally did is a test with pagesize=8192 (explanation see above). But this didn't bring any improvement for me (import time was 38 min instead of 36 min), so from my Point of view v0.2 should be final unless someone reports a bug I really would like to have a test resulting in a database with a size > 2GB. I already added all the media files I could think of, but I still can't get past the 2 GB with my db file. Anyone of the MP1-@Testers willing to help out here?

Michael

 

[Lehmden]

Hi.

20% during the import (having a core i5

My Core2Duo is more than 5 times slower than your i5, so this seems to be normal. But it's way faster than Atom/Brazoz CPU and a lot of HTPC using this type of CPU. We don't have to forget them as this type of PC will be much more common as HTPC in future than it is now. The process could use 100% CPU if needed, no problem. But this has to be at lowest priority. E.g. when I'm transcoding TV Recordings with XMediaRecode, CPU usage is 100% all the time, but I can use the PC as normal without any disadvantages. The transcoding process runs at lowest priority so other stuff can use CPU if needed...

I can import all my TV Shows, Movies, Pictures and Music. With this the DB file should be a lot more than 2 GB (if all GFX is stored inside DB) but for that I need a local data provider for GFX (or a better Internet connection )... Moving Pictures and MyTVSeries Thumbs dir is more than 6 GB. My Internet Connection is limited to 5 GB per Month (UMTS, no DSL available here), so I can't import those gfx over and over again...


Edit:
I understand you can't change process priority in your plugin. This is meant more global for all MP especially MP2 related stuff. (MP1 can not be rescued at all so this is for MP2 only, I fear...)

 

[MJGraf]

Yep, you're right!
But I just had a look and the thread priority of the ImporterWorker-Thread is already "BelowNormal".

What I'm just doing is a speed comparison to the SQLCE-Database and after that I found one final Setting, which may improve the Speed. Will Report back...

 

[MJGraf]

Speed-Comparison to SQLCE:

Did the same Import as above (about 24k Music files) with SQLCE. The import stopped after about 48 min due to database size limit (1GB, I could do 2GB by restarting, but this wouldn't bring too much further info).
After those 48 min, about 50% of my music collection was imported. That means about 96 min for the whole import - compared to 36 min with SQLite.
From what I remember @morpheus_xx reported somewhere that MySQL is about twice as fast as SQLCE - that means on the other hand that SQLite is about 30% faster than MySQL and nearly three times as fast as SQLCE

Will try the last optimization mentioned above and report back...

 

[Lehmden]

Hi

ImporterWorker-Thread is already "BelowNormal".

Maybe this isn't enough. If I remember right, on XMediaRecode there also is an issue if priority (it's changeable during transcoding in UI) is not set to "Low"...

 

[MJGraf]

Ok, the last test for me today was with only one change compared to v0.2. In the connection string I added

• SyncMode=SynchronizationModes.Normal

Details can be found here: http://www.sqlite.org/pragma.html#pragma_synchronous . With this setting, a transaction may not survive a power loss (but of course the database itself does), which is ok imo.
We could have more speed improvements by using SynchronizationModes.Off, but then the database may become corrupted in case of a power loss and this is something that gets on my nervers with the MP1-TVServer database already, so I want to avoid that in MP2...

Result is as follows:
Same import as above (24k music files)
Import took 30 min - now it's more than three times as fast as SQLCE...
This is noticably a further speed improvement, so I think we should use this further setting (I have no idea, whether MP2 could even cope with a non-committed transaction, which is still there after a power loss, so this tradeoff seems to be acceptable).
Attached is therefore a v0.3 - only change to v0.2 is the setting above. As a result, the v0.2 database file IS compatible with the v0.3 version. No changes to the database structure were introduced.

What I also did is just copy part of my music collection and import that as well. This now brought me a database file size > 2GB and this also worked without a problem. http://www.sqlite.org/limits.html reports that the maximum database file size is about 140TB - should be enough for most use cases

I'd be glad if we could get some more testing on this provider. Since it is much faster than SQLCE and has no database file size limitations, I'm tempted to propose this as new standard database provider. Furthermore it includes only one dll, which is much smaller than all the SQLCE-DLLs and I think there are also NUGet-packages available, which should make @chefkoch happy But for this to happen we need MUUUUUCH more testing, since the database is really a core component of MP2 (and we also need to sort out Morph's comment regarding the native TV provider, but I don't think this is a real problem...)

Michael

[Attachment removed - please use version from first post]

 

[MJGraf]

Really can't get my hands off it

Just had the Situation again that a big Import Job on the Server leads to the GUI being not responsive and after 30 sec (timeout if no write Transaction can be started) an exception was thrown. This happened when I tried to add further Shares, while in the Background the Import was already going on.
v0.4 should resolve this. By adding "false" as second Parameter when calling BeginTransaction, the so called "deferred write lock" is switched of. Before it was on (=defaul for SQLite), which means that there could be many Transactions in parallel, which eventually needed a write lock afterward. Now there can only be one.
I've done some testing and couldn't repproduce the Problem anymore, but here as well more testing is needed.

Now off for dinner

[Attachment removed - please use version from first post]

 

[Lehmden]

Hi.
Yesterday evening I've installed V0.4 and add Series, Movies, Music and Pictures (my whole test environment) to DB. All went well and really fast. CPU load has gone down during Import from 100% to 60-70% what leads MP2 to keep fluid. Great job.
So for me I will use SQLite as standard DB. Especially as it's possible to export DB (with SQLite Expert Personal) to SQL file so it could be possible to transfer data to MySQL if needed in future...

 

[morpheus_xx]

Nice to read Thanks a lot for development and testing. I will also do so this week. Maybe we have a new default DB soon?

 

[rickyman1977]

I am wondering where do I put ur SQLite datebase at?