Reply to thread

Message

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

<blockquote data-quote="morpheus_xx" data-source="post: 936726" data-attributes="member: 48495">MP2 - MySQL database provider, updated for Winter Release!&nbsp;After a long day of coding and testing I implemented support for MySQL as database backend for MP2-Server!&nbsp;What does this bring for users?<ul>
<li data-xf-list-type="ul">Much better performance compared to MSSQL-CE</li>
<li data-xf-list-type="ul">No size limit for your MediaLibrary</li>
</ul>How to install and configure?<ul>
<li data-xf-list-type="ul">Remove existing SQLCEDatabase plugin from MP2-Server\Plugins folder</li>
<li data-xf-list-type="ul">Place MySQLDatabase plugin into MP2-Server\Plugins folder (there can be only one!!!)</li>
<li data-xf-list-type="ul">Overwrite contents of MP2-Server with contents from MP2_Server_Update (smaller changes on medialibrary were needed)</li>
<li data-xf-list-type="ul">If you like to change connection details, edit the &quot;MySQLSettings.xml&quot; inside plugin folder</li>
</ul>Changelog:<ul>
<li data-xf-list-type="ul">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. 
</li>
<li data-xf-list-type="ul">28.11.2012: initial version of MySQL plugin (removed)</li>
</ul>How big is the impact of chosen database?&nbsp;For testing I created 4 shares:<ul>
<li data-xf-list-type="ul">local folder with BluRay images (type &quot;Movie&quot;)</li>
<li data-xf-list-type="ul">local folder with custom videos (type &quot;Video&quot;)</li>
<li data-xf-list-type="ul">folder with Series on NAS (type &quot;Series&quot;)</li>
<li data-xf-list-type="ul">folder with Music on NAS (type &quot;Audio&quot;)</li>
</ul>A normal import using the default SQL-CE database took ~9 minutes. This includes thumbnail extraction and DB access.&nbsp;Using MySQL on same PC the import time decreased to ~4 minutes! It's twice as fast as MSSQL-CE.&nbsp;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 &quot;DELETE&quot; SQL-statement that uses subqueries with &quot;LIKE&quot; on the path part. MySQL performs a lot better, probably because it is able to use the existing index on path.&nbsp;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!)&nbsp;Stay tuned <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite1" alt=":)" title="Smile&nbsp; &nbsp; :)" loading="lazy" data-shortname=":)" /></blockquote>

[QUOTE="morpheus_xx, post: 936726, member: 48495"] [SIZE=5][U][B]MP2 - MySQL database provider, [U][COLOR=#ff0000]updated for Winter Release[/COLOR][/U]![/B][/U][/SIZE] After a long day of coding and testing I implemented support for MySQL as database backend for MP2-Server! [U][B]What does this bring for users?[/B][/U] [LIST] [*]Much better performance compared to MSSQL-CE [*]No size limit for your MediaLibrary [/LIST] [U][B]How to install and configure?[/B][/U] [LIST] [*][COLOR=#ff0000][B]Remove[/B][/COLOR] existing SQLCEDatabase plugin from MP2-Server\Plugins folder [*][COLOR=#000000]Place[/COLOR] 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) [*][COLOR=#888888][COLOR=#000000]If you like to change connection details, edit the "MySQLSettings.xml" inside plugin folder[/COLOR][/COLOR] [/LIST] [SIZE=5][B][U]Changelog:[/U][/B][/SIZE] [LIST] [*][COLOR=#ff0000][B]21.02.2013[/B][/COLOR]: 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. [*][B]28.11.2012[/B]: initial version of MySQL plugin (removed) [/LIST] [U]How big is the impact of chosen database?[/U] For testing I created 4 shares: [LIST] [*]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") [/LIST] 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 :) [/QUOTE]