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: 1028048" data-attributes="member: 17886"><p>Thanks for your suggestions, guys. I have in the meantime cleaned up the code made it "MP2-standard-compliant" (hopefully) and at the same time updated to the latest version of the system.data.sqlite library. This will then be v0.07, which I'll publish as soon as Alpha3 is out. As soon as we have the new dev branch including all the changes of Alpha3, I'll base my code on dev and push it to git. My "import-test" as described above went down from about 34 minutes to about 31 minutes - probably caused by some improvements in the system.data.sqlite code (website says something about the removal of unnecessary conversion of data types, which may affect the speed).</p><p></p><p>Besides that, it made me nervous that Lehmden mentioned he sees a material impact on performance of the whole MP2 system as soon as his database grows to 1 or 2GB and that it gets really bad when we are beyond 3 or 4GB. This must not be the case. In particular something so deep down in the system as the database must not be a bottleneck and we should easily be able to handle databases with 20 or even 50GB of data without suffering from (material) performance decrease.</p><p></p><p>The problem for me is that in the past development of this plugin I concentrated only on the import speed, i.e. the speed of adding media items to the database. But most of the time, we are probably not importing data, we are reading data from the database to display it in the MP2 Client. But I never tested and optimized the speed in that respect. The reason is probably because this is A LOT more cumbersome and takes A LOT more time. But I think now the time has come <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><p>I will use this thread as kind of a test protocol to make sure that we have all the data in one place, but if someone has any comments, please feel free to post them here.</p><p></p><p>First we have to define a set of performance tests to make sure that we can compare the results when we change the code:</p><p></p><p><u><strong>Hardware</strong></u></p><p>Let's start with the hardware I'm using so that others can conclude whether their systems should be faster or slower. I have an Intel i5 with 8GB ram, Win7 (x64). System drive is a Crucial C300 SSD, data drive is a WD 2TB disk drive. The MP2 Server binary files are as well as the media files located on the data drive, while the MP2 Server data directory (including the database file) is located on the SSD. The MP2 Client computer should be irrelevant for the pure database performance, but for the sake of completeness: It is installed on my Laptop (Vaio TT), which is connected via WLan.</p><p></p><p><u><strong>Import Test</strong></u></p><p>First let my specify the import test, which I have used previously in this thread, in a bit more detail. For the tests I intentionally only import music files. No pictures, videos, movies or series. The reason is simple, I don't have many of the latter ones, whereas I can use a music collection having a reasonable size and all music files are perfectly tagged. The test collection consists of 17.794 MP3 files and 2.332 flac files (in total 20.126), all converted "by hand" from my CD collection, carefully tagged with MusicBrainz Picard (including genres) and in particular, each file is tagged with a cover (resolution usually between 500x500 and 1000x1000). I know that this is storing redundant data, since I store every cover multiple times (once in each music file) but I want the files to be self-contained.</p><p></p><p>The test itself is easy: I delete the data directory of client and server (including the database), start the server and the client, add a global share with the local file system resource provider pointing to the root directory of the music test collection and terminate the client. From the server log file I can see when the import has started and when it was finished. As mentioned above, this currently (with the yet to be published v0.07) takes about 31 minutes. The size of the database file after the import is 1.773.888 KB in the windows explorer.</p><p>I ran the test multiple times exactly like that and the results were somewhere between 30 minutes and 33 minutes - so there is a deviation of about +/- 5% caused by things like SSD garbage collection, etc.</p><p></p><p>This is where we stand with this test. As you can read in this thread, I wasn't able to speed this up anymore, no matter what additional settings I chose for the SQLite database. But we have to keep this test as (1) we have to make sure that the import doesn't slow down while we are optimizing the database reads and (2) I will use exactly the database resulting from this import for the read tests below.</p><p></p><p><u><strong>Read Tests</strong></u></p><p>As mentioned above, what we need now are some tests regarding reading from the database. I haven't defined them all, yet, because I'm still stuck with doing the first test runs on the first test - which are already very interesting, but more about that later....</p><p>The idea was to have four read tests - two synthetic tests and two real live tests:</p><ul> <li data-xf-list-type="ul">The first (synthetic) test should be a complex search query resulting in a lot of media items ("<strong>Read Test 1</strong>")</li> <li data-xf-list-type="ul">The second (synthetic) test should be a simple query to return exactly one media item based on a search for the media item ID ("<strong>Read Test 2</strong>")</li> <li data-xf-list-type="ul">The third test will be entering the audio section of MP2 with the music collection as described above ("<strong>Read Test 3</strong>")</li> <li data-xf-list-type="ul">The fourth test will be clicking on the first album displayed, which contains 11 music files in my test collection ("<strong>Read Test 4</strong>")</li> </ul><p>What I will measure with these test is exclusively the time the respective SQL queries consume. I'll take these figures from the SQLDebug.log. I will NOT measure the time until anything is displayed, because we have to concentrate on one thing to improve and this is only the database itself here. If we realize that the database is not the bottleneck, we can do other tests for other parts of MP2.</p><p>If you have comments on the tests I chose, please let me know. So far I have only done some first tests runs for Read Test 1 - so nearly anything can still be changed. I will describe the Read Tests in detail later below together with the respective first test results.</p><p></p><p><s><u><strong>Startup Test</strong></u></s></p><p><s>Since Lehmden mentioned that a big database also slows down the startup of the MP2 client, I will later also have a look at the queries executed at startup and treat this as a further "Startup Test".</s></p><p></p><p><u><strong>Connection Test</strong></u></p><p>As pointed out by Lehmde below, a big database slows down the time until the MP2 Client connects to the MP2 Server. I will have a look at the queries executed when a client connects as further "Connection Test".</p><p></p><p>So far so good - have to get some food now and will continue later to report my first findings...</p><p></p><p>Michael</p></blockquote><p></p>
[QUOTE="MJGraf, post: 1028048, member: 17886"] Thanks for your suggestions, guys. I have in the meantime cleaned up the code made it "MP2-standard-compliant" (hopefully) and at the same time updated to the latest version of the system.data.sqlite library. This will then be v0.07, which I'll publish as soon as Alpha3 is out. As soon as we have the new dev branch including all the changes of Alpha3, I'll base my code on dev and push it to git. My "import-test" as described above went down from about 34 minutes to about 31 minutes - probably caused by some improvements in the system.data.sqlite code (website says something about the removal of unnecessary conversion of data types, which may affect the speed). Besides that, it made me nervous that Lehmden mentioned he sees a material impact on performance of the whole MP2 system as soon as his database grows to 1 or 2GB and that it gets really bad when we are beyond 3 or 4GB. This must not be the case. In particular something so deep down in the system as the database must not be a bottleneck and we should easily be able to handle databases with 20 or even 50GB of data without suffering from (material) performance decrease. The problem for me is that in the past development of this plugin I concentrated only on the import speed, i.e. the speed of adding media items to the database. But most of the time, we are probably not importing data, we are reading data from the database to display it in the MP2 Client. But I never tested and optimized the speed in that respect. The reason is probably because this is A LOT more cumbersome and takes A LOT more time. But I think now the time has come :D I will use this thread as kind of a test protocol to make sure that we have all the data in one place, but if someone has any comments, please feel free to post them here. First we have to define a set of performance tests to make sure that we can compare the results when we change the code: [U][B]Hardware[/B][/U] Let's start with the hardware I'm using so that others can conclude whether their systems should be faster or slower. I have an Intel i5 with 8GB ram, Win7 (x64). System drive is a Crucial C300 SSD, data drive is a WD 2TB disk drive. The MP2 Server binary files are as well as the media files located on the data drive, while the MP2 Server data directory (including the database file) is located on the SSD. The MP2 Client computer should be irrelevant for the pure database performance, but for the sake of completeness: It is installed on my Laptop (Vaio TT), which is connected via WLan. [U][B]Import Test[/B][/U] First let my specify the import test, which I have used previously in this thread, in a bit more detail. For the tests I intentionally only import music files. No pictures, videos, movies or series. The reason is simple, I don't have many of the latter ones, whereas I can use a music collection having a reasonable size and all music files are perfectly tagged. The test collection consists of 17.794 MP3 files and 2.332 flac files (in total 20.126), all converted "by hand" from my CD collection, carefully tagged with MusicBrainz Picard (including genres) and in particular, each file is tagged with a cover (resolution usually between 500x500 and 1000x1000). I know that this is storing redundant data, since I store every cover multiple times (once in each music file) but I want the files to be self-contained. The test itself is easy: I delete the data directory of client and server (including the database), start the server and the client, add a global share with the local file system resource provider pointing to the root directory of the music test collection and terminate the client. From the server log file I can see when the import has started and when it was finished. As mentioned above, this currently (with the yet to be published v0.07) takes about 31 minutes. The size of the database file after the import is 1.773.888 KB in the windows explorer. I ran the test multiple times exactly like that and the results were somewhere between 30 minutes and 33 minutes - so there is a deviation of about +/- 5% caused by things like SSD garbage collection, etc. This is where we stand with this test. As you can read in this thread, I wasn't able to speed this up anymore, no matter what additional settings I chose for the SQLite database. But we have to keep this test as (1) we have to make sure that the import doesn't slow down while we are optimizing the database reads and (2) I will use exactly the database resulting from this import for the read tests below. [U][B]Read Tests[/B][/U] As mentioned above, what we need now are some tests regarding reading from the database. I haven't defined them all, yet, because I'm still stuck with doing the first test runs on the first test - which are already very interesting, but more about that later.... The idea was to have four read tests - two synthetic tests and two real live tests: [LIST] [*]The first (synthetic) test should be a complex search query resulting in a lot of media items ("[B]Read Test 1[/B]") [*]The second (synthetic) test should be a simple query to return exactly one media item based on a search for the media item ID ("[B]Read Test 2[/B]") [*]The third test will be entering the audio section of MP2 with the music collection as described above ("[B]Read Test 3[/B]") [*]The fourth test will be clicking on the first album displayed, which contains 11 music files in my test collection ("[B]Read Test 4[/B]") [/LIST] What I will measure with these test is exclusively the time the respective SQL queries consume. I'll take these figures from the SQLDebug.log. I will NOT measure the time until anything is displayed, because we have to concentrate on one thing to improve and this is only the database itself here. If we realize that the database is not the bottleneck, we can do other tests for other parts of MP2. If you have comments on the tests I chose, please let me know. So far I have only done some first tests runs for Read Test 1 - so nearly anything can still be changed. I will describe the Read Tests in detail later below together with the respective first test results. [S][U][B]Startup Test[/B][/U] Since Lehmden mentioned that a big database also slows down the startup of the MP2 client, I will later also have a look at the queries executed at startup and treat this as a further "Startup Test".[/S] [U][B]Connection Test[/B][/U] As pointed out by Lehmde below, a big database slows down the time until the MP2 Client connects to the MP2 Server. I will have a look at the queries executed when a client connects as further "Connection Test". So far so good - have to get some food now and will continue later to report my first findings... Michael [/QUOTE]
Insert quotes…
Verification
Post reply
Forums
MediaPortal 2
Plugin Development
SQLiteDatabase Plugin for MP2
Contact us
RSS
Top
Bottom
