Plugin: MySQL database provider - better performance and no size limit for your MP2-MediaLibrary

MP2 - MySQL database provider, updated for Winter Release!

After a long day of coding and testing I implemented support for MySQL as database backend for MP2-Server!

What does this bring for users?

• Much better performance compared to MSSQL-CE
• No size limit for your MediaLibrary

How to install and configure?

• Remove existing SQLCEDatabase plugin from MP2-Server\Plugins folder
• Place MySQLDatabase plugin into MP2-Server\Plugins folder (there can be only one!!!)
• Overwrite contents of MP2-Server with contents from MP2_Server_Update (smaller changes on medialibrary were needed)
• If you like to change connection details, edit the "MySQLSettings.xml" inside plugin folder

Changelog:

• 21.02.2013: Updated to work with MP2 Alpha1 Winter Release! This version works much easier, with close to zero configuration requirements. I removed all old notes, as they are no longer required.
  
• 28.11.2012: initial version of MySQL plugin (removed)

How big is the impact of chosen database?

For testing I created 4 shares:

• local folder with BluRay images (type "Movie")
• local folder with custom videos (type "Video")
• folder with Series on NAS (type "Series")
• folder with Music on NAS (type "Audio")

A normal import using the default SQL-CE database took ~9 minutes. This includes thumbnail extraction and DB access.

Using MySQL on same PC the import time decreased to ~4 minutes! It's twice as fast as MSSQL-CE.

The reason for this is probably the fact, that MSSQL-CE is run in the process of the host (MP2-Server) and it's not as efficient as a dedicated database process. On analysis I found out, that most time was spent on a "DELETE" SQL-statement that uses subqueries with "LIKE" on the path part. MySQL performs a lot better, probably because it is able to use the existing index on path.

To get a number: for MSSQL-CE the time for DB-Access was double of the time required for thumbnail creation! (160 sec vs. 80 sec!)

Stay tuned

Interesting about the delete. MySQL is not going to use an index if it is a full search '%val%' but can if you bound the start 'val'.

Deletes are always an issue. What is the statement? Sounds like it can be tuned...

Don't have it here, it is like:

delete from mediaitem where miamid in (
select miamid from provideraspect where system=@system and (path like '/resourcepath/_%' or path like '/resourcepath/>_%')
)

in principle a DB should be able to use the index on "path" column, because we are doing a "starts with" like query.

And obviously MySQL is able to do so. Not sure if MsSQL-CE can't use the index at all or that it is "only" slow because sharing process resources

I find it at least curious why the delete gets done a lot during a clean import. Of course it could be some objects that are "extended" and need to be updated in DB.

Out of interest what is performance like if you run the select to get a result set and then iterate over it in code and call the delete for each item?

Believe me, there is a logic behind, i.e. to handle chained resources (i.e. ISO->VIDEO_TS) and especially folder-type mediaitems (DVD-, BD-folders). Maybe @Albert can check this logic for tuning possibilities (from logical level).

I'm at work and can't do any analysis. Also there is not management tools for SQLCE that could show some query plans or other statistics.

Usually I wont believe to anything - but I might do an exception in this case Sounds like there could be some extra work done, but it might be really hard to reduce such as I assume there is much abstraction between the RAR -> ISO -> VIDEO_TS case. Abstraction is good, but it will come as more complexity and in some cases it will be impossible to optimise (this case it would be creating all the "correct" data in one pass).

I've updated the first post with the plugin, so testing can begin

This is quite easy, I think it will happen (sooner or later )