home
products
contribute
download
documentation
forum
Home
Forums
New posts
Search forums
What's new
New posts
All posts
Latest activity
Members
Registered members
Current visitors
Donate
Log in
Register
What's new
Search
Search
Search titles only
By:
New posts
Search forums
Search titles only
By:
Menu
Log in
Register
Navigation
Install the app
Install
More options
Contact us
Close Menu
Forums
MediaPortal 2
Plugin Development
SQLiteDatabase Plugin for MP2
Contact us
RSS
JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser
.
Reply to thread
Message
<blockquote data-quote="MJGraf" data-source="post: 1029420" data-attributes="member: 17886"><p><u><span style="font-size: 18px"><strong>Database Test Protocol</strong></span></u></p><p>In the following I will describe the exact steps I will take for the tests:</p><p></p><p><u><strong>Used Code Base</strong></u></p><p>I will use the code base of MP2 Alpha 3 to exclude influences caused by other developments. There are, however, two changes in the code necessary for the tests compared to a clean MP2 Alpha 3 installation:</p><p></p><p>In MediaLibrary.Search(...) I have replaced</p><p>[CODE]IList<MediaItem> items = cmiq.QueryList();[/CODE]</p><p>by</p><p>[CODE]var sw = System.Diagnostics.Stopwatch.StartNew();</p><p>IList<MediaItem> items = cmiq.QueryList();</p><p>sw.Stop();</p><p>ServiceRegistration.Get<ILogger>().Debug("MediaLibrary: Search time {0} for query {1}", sw.Elapsed, query.ToString());[/CODE]</p><p></p><p>In MediaLibrary.GetValueGroups(...) I have replaced</p><p>[CODE]return cdavq.Execute();[/CODE]</p><p>by</p><p>[CODE]var sw = System.Diagnostics.Stopwatch.StartNew();</p><p>var result = cdavq.Execute();</p><p>sw.Stop();</p><p>ServiceRegistration.Get<ILogger>().Debug("MediaLibrary: Search time {0} for query {1}", sw.Elapsed, cdavq.ToString());</p><p>return result;[/CODE]</p><p></p><p>Both changes are necessary to log the Read Test results to Server.log.</p><p>The Skin used during the tests is "Simple MP2 Default Skin".</p><p></p><p><u><strong>Time Measurement</strong></u></p><p>For Read Tests, we have to differentiate between the time it takes until the query has been executed (<strong>Query Time</strong>) and the time it takes until the data from the query result has actually been read from the database (<strong>Read Time</strong>).</p><p>The Query Time will be taken from the SQLDebug.log. If a particular Read Test results in multiple SQL queries, I will just add the time shown for all queries in the Debug.log and just report one Query Time here to keep the amount of data manageable.</p><p>The Read Time will be logged into the Server.log by the code changes as described above. The Read Time comprises the Query Time and additionally the time it takes to read the data from the database and put it into the form of the objects we use within MP2.</p><p>Every Read Test will be executed twice without quitting the MP2 Client or restarting the MP2 server in between. The first result is expected to be slower than the second result due to various caching mechanisms in place. The result will be shown as where y ms is the result of the first test run and z ms is the result of the second test run, and zs ms is the sum of both, each in milliseconds.</p><p></p><p>The Write Test consists of a large import of music files. The time will be taken from the sever.log as the difference between the start time and the end time of the import job. It will be shown as mm:ss.</p><p></p><p><u><strong>Preparation</strong></u></p><ul> <li data-xf-list-type="ul">Quit MP2 Client and stop MP2 server</li> <li data-xf-list-type="ul">Delete the data directories of MP2 Client and MP2 server</li> <li data-xf-list-type="ul">Start MP2 Server</li> <li data-xf-list-type="ul">Start MP2 Client and attach it to the MP2 Server</li> <li data-xf-list-type="ul">Go to Audio/View Mode and select "small list" (to avoid the non-virtualized wrap panel)</li> </ul><p><u><strong>Write Test</strong></u></p><ul> <li data-xf-list-type="ul">Add a global share with the local file system resource provide comprising 17.794 MP3 files and 2.332 flac files (in total 20.126), each file comprehensively tagged and each containing embedded cover art in different sizes.</li> <li data-xf-list-type="ul">Exit MP2 Client and wait for the import to finish</li> </ul><p><u><strong>Read Tests</strong></u></p><p>There will be four Read Tests:</p><p></p><p><u>Read Test 1</u></p><ul> <li data-xf-list-type="ul">Restart the MP2 Server</li> <li data-xf-list-type="ul">Execute the following synthetic search<br /> [CODE]var necessaryMias = new List<Guid> { MediaAspect.ASPECT_ID };<br /> var optionalMias = new List<Guid>(ServiceRegistration.Get<IMediaItemAspectTypeRegistration>().LocallyKnownMediaItemAspectTypes.Keys);<br /> MediaItemQuery miq = ServiceRegistration.Get<IMediaLibrary>().BuildSimpleTextSearchQuery(searchString, necessaryMias, optionalMias, null, false, true);<br /> var result = ServiceRegistration.Get<IMediaLibrary>().Search(miq, false);[/CODE]<br /> with searchString="simon".<br /> This search leads to 12 SQL queries which are shown <a href="https://forum.team-mediaportal.com/threads/sqlitedatabase-plugin-for-mp2.118535/page-6#post-1028063" target="_blank">here </a>and <a href="https://forum.team-mediaportal.com/threads/sqlitedatabase-plugin-for-mp2.118535/page-6#post-1028064" target="_blank">here </a>and results in 235 MediaItems found and read from the database into RAM including all their metadata and covers.</li> <li data-xf-list-type="ul">Execute this synthetic search a second time</li> </ul><p><u>Read Test 2</u></p><ul> <li data-xf-list-type="ul">Restart the MP2 Server</li> <li data-xf-list-type="ul">Start the MP2 Client</li> <li data-xf-list-type="ul">Enter the Audio Section<br /> This leads to two identical calls to MediaLibrary.GetValueGroups and, hence, to two identical SQL Queries as shown <a href="https://forum.team-mediaportal.com/threads/sqlitedatabase-plugin-for-mp2.118535/page-7#post-1028428" target="_blank">here</a>. [Note: This may be a bug. For now I take the first call to GetValueGroups as the first test run and the second call as second test run. Should this be a bug and be solved, we leave the Audi Section via ESC and then reenter it for the second test run].<br /> The search returns 2.306 albums.</li> </ul><p><u>Read Test 3</u></p><ul> <li data-xf-list-type="ul">Enter an album with 11 tracks (Album 1).<br /> Each track of Album 1 has an embedded cover art of 48,3 KB so that, besides the text metadata, cover art in the size of 531,3 KB are read from the database.<br /> This leads to six SQL queries as shown <a href="https://forum.team-mediaportal.com/threads/sqlitedatabase-plugin-for-mp2.118535/page-8#post-1028432" target="_blank">here</a>.</li> <li data-xf-list-type="ul">Leave Album 1 with ESC<br /> [Note: This leads to Read Test 2 being executed again, but we ignore these log entries]</li> <li data-xf-list-type="ul">Reenter the same album for the second test run.</li> </ul><p><u>Read Test 4</u></p><ul> <li data-xf-list-type="ul">Leave Album 1 with ESC<br /> [Note: This leads to Read Test 2 being executed again, but we ignore these log entries]</li> <li data-xf-list-type="ul">Enter an album with 12 tracks (Album 2).<br /> Each track of Album 2 has an embedded cover art of 2,80 MB KB so that, besides the text metadata, cover art in the size of 33,6 MB are read from the database.<br /> This leads to the same six SQL queries as in Read Test 3, but with a different parameter-string V0.</li> <li data-xf-list-type="ul">Leave Album 2 with ESC<br /> [Note: This leads to Read Test 2 being executed again, but we ignore these log entries]</li> <li data-xf-list-type="ul">Reenter the same album for the second test run.</li> </ul><p>These are all the Read Tests I am planning to perform for now. I think this gives a good picture of the overall read performance. Read Test 1 results in heavy queries with a lot of "likes" in it and returns a huge amount of data. Read Test 2 is not too complex and does not return any binary data, but it very common in day to day use. Read Tests 3 and 4 are also very common scenarios with Read Test 3 returning a "usual" amount of binary data in form of cover art and Read Test 4 being the "hard core binary data test". I don't think we will regularly store fan art with more than 2.8MB in the database...</p><p></p><p>And now finally we can start our tests <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite8" alt=":D" title="Big Grin :D" loading="lazy" data-shortname=":D" /></p></blockquote><p></p>
[QUOTE="MJGraf, post: 1029420, member: 17886"] [U][SIZE=5][B]Database Test Protocol[/B][/SIZE][/U] In the following I will describe the exact steps I will take for the tests: [U][B]Used Code Base[/B][/U] I will use the code base of MP2 Alpha 3 to exclude influences caused by other developments. There are, however, two changes in the code necessary for the tests compared to a clean MP2 Alpha 3 installation: In MediaLibrary.Search(...) I have replaced [CODE]IList<MediaItem> items = cmiq.QueryList();[/CODE] by [CODE]var sw = System.Diagnostics.Stopwatch.StartNew(); IList<MediaItem> items = cmiq.QueryList(); sw.Stop(); ServiceRegistration.Get<ILogger>().Debug("MediaLibrary: Search time {0} for query {1}", sw.Elapsed, query.ToString());[/CODE] In MediaLibrary.GetValueGroups(...) I have replaced [CODE]return cdavq.Execute();[/CODE] by [CODE]var sw = System.Diagnostics.Stopwatch.StartNew(); var result = cdavq.Execute(); sw.Stop(); ServiceRegistration.Get<ILogger>().Debug("MediaLibrary: Search time {0} for query {1}", sw.Elapsed, cdavq.ToString()); return result;[/CODE] Both changes are necessary to log the Read Test results to Server.log. The Skin used during the tests is "Simple MP2 Default Skin". [U][B]Time Measurement[/B][/U] For Read Tests, we have to differentiate between the time it takes until the query has been executed ([B]Query Time[/B]) and the time it takes until the data from the query result has actually been read from the database ([B]Read Time[/B]). The Query Time will be taken from the SQLDebug.log. If a particular Read Test results in multiple SQL queries, I will just add the time shown for all queries in the Debug.log and just report one Query Time here to keep the amount of data manageable. The Read Time will be logged into the Server.log by the code changes as described above. The Read Time comprises the Query Time and additionally the time it takes to read the data from the database and put it into the form of the objects we use within MP2. Every Read Test will be executed twice without quitting the MP2 Client or restarting the MP2 server in between. The first result is expected to be slower than the second result due to various caching mechanisms in place. The result will be shown as where y ms is the result of the first test run and z ms is the result of the second test run, and zs ms is the sum of both, each in milliseconds. The Write Test consists of a large import of music files. The time will be taken from the sever.log as the difference between the start time and the end time of the import job. It will be shown as mm:ss. [U][B]Preparation[/B][/U] [LIST] [*]Quit MP2 Client and stop MP2 server [*]Delete the data directories of MP2 Client and MP2 server [*]Start MP2 Server [*]Start MP2 Client and attach it to the MP2 Server [*]Go to Audio/View Mode and select "small list" (to avoid the non-virtualized wrap panel) [/LIST] [U][B]Write Test[/B][/U] [LIST] [*]Add a global share with the local file system resource provide comprising 17.794 MP3 files and 2.332 flac files (in total 20.126), each file comprehensively tagged and each containing embedded cover art in different sizes. [*]Exit MP2 Client and wait for the import to finish [/LIST] [U][B]Read Tests[/B][/U] There will be four Read Tests: [U]Read Test 1[/U] [LIST] [*]Restart the MP2 Server [*]Execute the following synthetic search [CODE]var necessaryMias = new List<Guid> { MediaAspect.ASPECT_ID }; var optionalMias = new List<Guid>(ServiceRegistration.Get<IMediaItemAspectTypeRegistration>().LocallyKnownMediaItemAspectTypes.Keys); MediaItemQuery miq = ServiceRegistration.Get<IMediaLibrary>().BuildSimpleTextSearchQuery(searchString, necessaryMias, optionalMias, null, false, true); var result = ServiceRegistration.Get<IMediaLibrary>().Search(miq, false);[/CODE] with searchString="simon". This search leads to 12 SQL queries which are shown [URL='https://forum.team-mediaportal.com/threads/sqlitedatabase-plugin-for-mp2.118535/page-6#post-1028063']here [/URL]and [URL='https://forum.team-mediaportal.com/threads/sqlitedatabase-plugin-for-mp2.118535/page-6#post-1028064']here [/URL]and results in 235 MediaItems found and read from the database into RAM including all their metadata and covers. [*]Execute this synthetic search a second time [/LIST] [U]Read Test 2[/U] [LIST] [*]Restart the MP2 Server [*]Start the MP2 Client [*]Enter the Audio Section This leads to two identical calls to MediaLibrary.GetValueGroups and, hence, to two identical SQL Queries as shown [URL='https://forum.team-mediaportal.com/threads/sqlitedatabase-plugin-for-mp2.118535/page-7#post-1028428']here[/URL]. [Note: This may be a bug. For now I take the first call to GetValueGroups as the first test run and the second call as second test run. Should this be a bug and be solved, we leave the Audi Section via ESC and then reenter it for the second test run]. The search returns 2.306 albums. [/LIST] [U]Read Test 3[/U] [LIST] [*]Enter an album with 11 tracks (Album 1). Each track of Album 1 has an embedded cover art of 48,3 KB so that, besides the text metadata, cover art in the size of 531,3 KB are read from the database. This leads to six SQL queries as shown [URL='https://forum.team-mediaportal.com/threads/sqlitedatabase-plugin-for-mp2.118535/page-8#post-1028432']here[/URL]. [*]Leave Album 1 with ESC [Note: This leads to Read Test 2 being executed again, but we ignore these log entries] [*]Reenter the same album for the second test run. [/LIST] [U]Read Test 4[/U] [LIST] [*]Leave Album 1 with ESC [Note: This leads to Read Test 2 being executed again, but we ignore these log entries] [*]Enter an album with 12 tracks (Album 2). Each track of Album 2 has an embedded cover art of 2,80 MB KB so that, besides the text metadata, cover art in the size of 33,6 MB are read from the database. This leads to the same six SQL queries as in Read Test 3, but with a different parameter-string V0. [*]Leave Album 2 with ESC [Note: This leads to Read Test 2 being executed again, but we ignore these log entries] [*]Reenter the same album for the second test run. [/LIST] These are all the Read Tests I am planning to perform for now. I think this gives a good picture of the overall read performance. Read Test 1 results in heavy queries with a lot of "likes" in it and returns a huge amount of data. Read Test 2 is not too complex and does not return any binary data, but it very common in day to day use. Read Tests 3 and 4 are also very common scenarios with Read Test 3 returning a "usual" amount of binary data in form of cover art and Read Test 4 being the "hard core binary data test". I don't think we will regularly store fan art with more than 2.8MB in the database... And now finally we can start our tests :D [/QUOTE]
Insert quotes…
Verification
Post reply
Forums
MediaPortal 2
Plugin Development
SQLiteDatabase Plugin for MP2
Contact us
RSS
Top
Bottom
