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

morpheus_xx

Lead Dev MP2
  • Team MediaPortal
  • March 24, 2007
    11,400
    6,971
    Germany Germany
    Country flag
    • Thread starter
    • Moderator
    • #1
    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 :)
     

    Attachments

    Last edited:

    jameson_uk

    Retired Team Member
  • Premium Supporter
  • January 27, 2005
    7,258
    2,528
    Birmingham
    United Kingdom United Kingdom
    Country flag
    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...
     

    morpheus_xx

    Lead Dev MP2
  • Team MediaPortal
  • March 24, 2007
    11,400
    6,971
    Germany Germany
    Country flag
    • Thread starter
    • Moderator
    • #3
    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
     

    tourettes

    Retired Team Member
  • Premium Supporter
  • January 7, 2005
    17,301
    4,800
    Deletes are always an issue. What is the statement? Sounds like it can be tuned...
    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.
     

    jameson_uk

    Retired Team Member
  • Premium Supporter
  • January 27, 2005
    7,258
    2,528
    Birmingham
    United Kingdom United Kingdom
    Country flag
    delete from mediaitem where miamid in (
    select miamid from provideraspect where system=@system and (path like '/resourcepath/_%' or path like '/resourcepath/>_%')
    )
    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?
     

    morpheus_xx

    Lead Dev MP2
  • Team MediaPortal
  • March 24, 2007
    11,400
    6,971
    Germany Germany
    Country flag
    • Thread starter
    • Moderator
    • #6
    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.
    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).

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

    tourettes

    Retired Team Member
  • Premium Supporter
  • January 7, 2005
    17,301
    4,800
    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.
    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).
    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).
     

    morpheus_xx

    Lead Dev MP2
  • Team MediaPortal
  • March 24, 2007
    11,400
    6,971
    Germany Germany
    Country flag
    • Thread starter
    • Moderator
    • #8
    I've updated the first post with the plugin, so testing can begin :)
     

    commander197

    Portal Pro
    December 23, 2008
    97
    7
    Hi Morpheus,

    nice work. Do you plan to create a plugin for the mssql database?
     

    Users Who Are Viewing This Thread (Users: 0, Guests: 1)

    OP Similar threads Forum Replies Date
    joecrow 1.25.0 Radio Time plugin fails after Resume from Sleep with MP 1.25 Bugreports 4
    P MP2: US user, OTA - can NOT get SchedulesDirect plugin to work on MP1 or MP2 --> how do I get OTA schedule? General 5
    E aktuelle englische Forums Webseite von - Showtime Plugin v3 Plugins & Erweiterungen 4
    mrmojo666 [Tv Server Plugin ]Direct Show Video Control Plugin 0.0.0.1 for Hauppauge PVR cards MediaPortal 1 Plugins 0
    joecrow MultiShortcut Plugin (Uavailable) in DWHD skin General Support 58
    vapourEyes Building TV Plugin General Development (no feature request here!) 49
    Golf4 Nach Windows-Updates läuft TV-Plugin nicht mehr Allgemeines Support- und Diskussionsforum 0
    joecrow How about a Launcher plugin for MP1? MediaPortal 1 6
    D [solved] TVServer Plugin must be missing something. General Development (no feature request here!) 1
    framug Nouveau plugin XmlTvFR pour récupérer le guide télé français Kazer / ZguideTv (WebEPGfr) 11
    emphatic Allow remote control mapping of all plugins Improvement Suggestions 0
    D Where should plugins be located on hard drive? General Support 0
    D how to disengage from one version of a plugin to use another Newcomers Forum 0
    cbr954rr How to disable a pre-configured plugin? General 2
    mrmojo666 [Plugin]Marantz Control Plugin 0.0.0.2 MediaPortal 1 Plugins 2
    mrmojo666 beginner: trying to write a plugin, need help to catch event General Development (no feature request here!) 3
    framug [Rejected] Fix PowerScheduler plugin for MP2 single seat Submit: Code Patches 9
    doskabouter [Question] Stale plugins Developer Discussions 9
    M MP1 stops responding after Starting Plugins General Support 14
    Terra LAVFilter Plugin cannot be updated (MP1.23) MediaPortal 1 Plugins 10
    L Mediaportal 1.21 and Windows 10: user schedules de-activated when powerscheduler plugin is selected General Support 1
    C Can the XmlTv Plugin handle 600+ channels? General 3
    M Online Video Plugin V.2.32 keine Installtion unter MediaPortal 1.22 ? TV / Streaming 3
    emphatic Pictures in the cloud - would a plugin be possible for services like Google Photos MediaPortal 1 Plugins 1
    C where is the Online Videos plugin General Support 1
    Similar threads

























    Top Bottom