Fork MediaPortal on GitHub
  1. MediaPortal 1.9.0 Pre out now!

    We are very happy to present you the first testversion of MediaPortal 1.9.0. This release includes a number of new features and bug fixes.

    Read the full news: MediaPortal 1.9.0 Pre Release

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

Discussion in 'Plugin Development' started by morpheus_xx, November 28, 2012.

  1. morpheus_xx
    • Team MediaPortal

    morpheus_xx

    System Specs
    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 :)

    Attached Files:

    Last edited: February 21, 2013
    • Like Like x 9
  2. jameson_uk
    • Team MediaPortal

    jameson_uk

    System Specs
    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...
  3. morpheus_xx
    • Team MediaPortal

    morpheus_xx Thread Starter

    System Specs
    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
  4. tourettes
    • Team MediaPortal

    tourettes

    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.
  5. jameson_uk
    • Team MediaPortal

    jameson_uk

    System Specs
    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?
  6. morpheus_xx
    • Team MediaPortal

    morpheus_xx Thread Starter

    System Specs
    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.
  7. tourettes
    • Team MediaPortal

    tourettes

    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).
  8. morpheus_xx
    • Team MediaPortal

    morpheus_xx Thread Starter

    System Specs
    I've updated the first post with the plugin, so testing can begin :)
  9. commander197

    commander197

    Hi Morpheus,

    nice work. Do you plan to create a plugin for the mssql database?
  10. morpheus_xx
    • Team MediaPortal

    morpheus_xx Thread Starter

    System Specs
    This is quite easy, I think it will happen (sooner or later ;))
    • Like Like x 2

Share This Page

Users Viewing Thread (Users: 0, Guests: 0)

Running the latest version?

V1.8.0 FINAL - released July 2014 
Releasenews | Download
Changelog
 | Requirements
HTPC
Team-MediaPortal
 
About
Contact |  Press
Partners