SQLiteDatabase Plugin for MP2 (3 Viewers)

[MJGraf]

ok - difference is not big:
Import time down to 30:31 from about 31 minutes.
Filesize down to 1.770.092 kb from 1.773.888 KB
Doesn't give enough speed-up to deal with that now since it requires changes to MP2 core code. But nevertheless we should keep it in mind as IMO this is just wrong.
@morpheus_xx: I know you are busy with putting the release together - but if you find the time, could you check whether MySQL also auto creates an index on PRIMARY KEY columns? Thanks!

For my further tests, I will again use the original database generated by MP2 and not the tweaked one from the last test.
Michael

 

[MJGraf]

Again for the files from here regarding SQL CE:

Constraints

• PRIMARY KEY Constraints
  
  • A table can contain only one PRIMARY KEY constraint.
    
    
  • Each PRIMARY KEY generates an index.
    
    
  • All columns defined within a PRIMARY KEY constraint must be defined as NOT NULL. If nullability is not specified, all columns participating in a PRIMARY KEY constraint have their nullability set to NOT NULL.

So with the standard SQL CE database plugin we can also leave out named indices created just for a PRIMARY KEY column.

 

[morpheus_xx]

I think we won't gain too much with more tweaking of the DB model (indices).

I would like to see the impact of querying thumbnails with the MediaItems. If the DB needs to join and sort tables that contain (large) binary data, this is both CPU, IO and memory intensive.

It should be possible to remove the ThumbnailSmallAspect/ThumbnailLargeAspect from the list of optional aspects to be queried. Please check the selected Skin's plugin.xml and comment the registration of thumb aspects. I'm not sure if they are also included by code (I think not), then this should be commented also.

See this for example: https://github.com/MediaPortal/Medi...aPortal/Incubator/Titanium/plugin.xml#L16-L45

As I now already load "album thumbs" (*1) from MediaLibrary, we could do the same for all thumbs. This will reduce the server processing and network transfer times for MediaItems, but brings (much?) overhead for requesting each thumb per HTTP.

(*1) an "Album" thumb is in fact the first available thumbnail of all AudioItems that have the album title.

 

[morpheus_xx]

@morpheus_xx: I know you are busy with putting the release together - but if you find the time, could you check whether MySQL also auto creates an index on PRIMARY KEY columns? Thanks!

Yes, MySQL also indexes PKs: http://stackoverflow.com/questions/1071180/is-the-primary-key-automatically-indexed-in-mysql

Another note: For TVE35 DB model I added manually indexes on FK columns, which improved query performance a lot.

 

[MJGraf]

Thanks for your comments Morph.

It should be possible to remove the ThumbnailSmallAspect/ThumbnailLargeAspect from the list of optional aspects to be queried

I will do that in my synthetic Read Test 1 first, because there I can easily take out those aspects from the optional aspects.
We have to keep in mind, however, in particular two things:

• most likely (if not certainly) the queries will be faster without these aspects to be queried. But what we don't know is how long it would take to read the (in this case 235) image files from disk. Although my first thought was that 300ms is quite heavy for single search in the database, I changed my mind a bit when I did the following calculation: My database is about 1.7GB in size holding about 20k media items. this means about 90KB per media item in average. At least 89 of these 90KB are for the cover picture. This means that if we store the covers as files in the file system instead of the database, we would have to read 235 files with about 89KB size each, i.e. we read in total 20.5MB from disk just for the covers. As per this, my SSD can do about 275MB/sec sequential reads and about 83MB/sec 4k random reads. With our 235 files of about 89KB we are most likely somwhere in between those two values, say about 150MB/sec. As a result, it would already take about 140ms to read these covers from files under ideal conditions (meaning those SSD performance tests usually don't work on a file system basis but on a very low level disk access basis to result in higher values. We on the other hand have to take into account the file system overhead). So 300ms for executing 12 SQL queries AND reading those 235 files from the database is not really that bad...
• When I do the searches without the covers (i.e. no ThmubnailAspects in the optional aspects), the covers are still in the database. So the results I will get are not really what you would get if the covers were not in the database at all. For that I would probably have to comment the respective lines out in the AudioMetadataExtractor. Let's see if I find the time to do so over the weekend...

Anyway, I will try without fetching covers and report back.

Another note: For TVE35 DB model I added manually indexes on FK columns, which improved query performance a lot.

Will definitely have a look at MIA_Management to find out why this does not happen automatically. If it really doesn't create indices on foreign keys automatically, we should definitely include this as standard in our MIA_Management...

 

[MJGraf]

Indices on FK columns should be created automatically in MIA_Management.cs:

One-to-many: line 992 ff.
Many-to-one: line 1023 ff.
Many-to-many: line 1086 ff.

Will check directly in the database tonight...

 

[morpheus_xx]

Fine, I guessed so! I only did this for tuning of TVE35 model.

 

[MJGraf]

The more I test around, the more I think the database itself is by far fast enough and the speed problems stem from somewhere else. But so far this is only a gut feeling - Let's first describe my further tests:

Connection Test
What I do is a fresh import of my test music collection. As described above, I close my MP2 client as soon as I have added the share and the import runs. After the import has completed, I restart the MP2 server, start up the MP2 client and see what happens in the SQLDebug.log. When A client connects, there are apparently three SQL statements executed:

SQL1 (Update)

UPDATE ATTACHED_CLIENTS SET LAST_HOSTNAME = @LAST_HOSTNAME,
  LAST_CLIENT_NAME = @LAST_CLIENT_NAME
WHERE
  SYSTEM_ID = @SYSTEM_ID
-------------------------------------------------------
"LAST_HOSTNAME" [String]: 'MP2Client.fritz.box'
"LAST_CLIENT_NAME" [String]: 'MediaPortal 2 client'
"SYSTEM_ID" [String]: 'f3feaa94-a92c-4ba1-b841-aa617dadefe9'

SQL2 (Attached Clients):

SELECT
  SYSTEM_ID,
  LAST_HOSTNAME,
  LAST_CLIENT_NAME
FROM ATTACHED_CLIENTS

SQL3 (Shares)

SELECT
  SHARE_ID,
  SYSTEM_ID,
  BASE_RESOURCE_PATH,
  NAME
FROM SHARES
WHERE
  SYSTEM_ID=@SYSTEM_ID
-------------------------------------------------------
"SYSTEM_ID" [String]: 'f3feaa94-a92c-4ba1-b841-aa617dadefe9'

The first one (SQL1 (Update)) apparently updates one row in a table, which most likely only has a few rows equaling the number of MP2 Clients you have in your network. The second one reads all the rows in that table again and the third one reads all the shares available in the MP2 system. All queries should really execute quickly, since there is not much data to read or update and they do:

SQL1 (Update): 1 ms
SQL2 (Attached Clients): 0 ms
SQL3 (Shares): 0 ms

As mentioned, this is the result with a 1.7GB database. So I can't really see how this could slow down the connection process of the MP2 Client to the MP2 Server. So I guess we will have to wait for Lehmden's SQLDebug.Logs to get more insight in this problem.

 

[MJGraf]

Read Test 3
For the next test I continued from the Connection Test and just entered the audio section of my MP2 Client. As a result, there are 2.306 albums displayed and it took about 29 seconds until they showed up. This is extremely long, but to cut a long story short, it is probably caused by (1) my MP2 Client is connected via a slow WIFI connection and (2) we do not have a VirtualStackPanel, yet. But back to the database: Entering the audio section resulted in 2 SQL queries:

SQL1 (ProviderResouce):

SELECT
  COUNT(
  V.C
) C,
  V.A0
FROM (
  SELECT
  DISTINCT T1.MEDIA_ITEM_ID C,
  T0.ALBUM A0
FROM M_PROVIDERRESOURCE T1
  INNER JOIN M_MEDIAITEM T2 ON T2.MEDIA_ITEM_ID = T1.MEDIA_ITEM_ID
  INNER JOIN M_AUDIOITEM T0 ON T0.MEDIA_ITEM_ID = T1.MEDIA_ITEM_ID  
WHERE
  (
  T1.SYSTEM_ID = @V0
  OR T1.SYSTEM_ID = @V1
)
) V
GROUP BY
  V.A0
-------------------------------------------------------
"V0" [String]: 'ef2d2144-c347-4db2-b7d2-3e634c6f7705'
"V1" [String]: 'f3feaa94-a92c-4ba1-b841-aa617dadefe9'

SQL2 (ProviderResource):

SELECT
  COUNT(
  V.C
) C,
  V.A0
FROM (
  SELECT
  DISTINCT T1.MEDIA_ITEM_ID C,
  T0.ALBUM A0
FROM M_PROVIDERRESOURCE T1
  INNER JOIN M_MEDIAITEM T2 ON T2.MEDIA_ITEM_ID = T1.MEDIA_ITEM_ID
  INNER JOIN M_AUDIOITEM T0 ON T0.MEDIA_ITEM_ID = T1.MEDIA_ITEM_ID  
WHERE
  (
  T1.SYSTEM_ID = @V0
  OR T1.SYSTEM_ID = @V1
)
) V
GROUP BY
  V.A0
-------------------------------------------------------
"V0" [String]: 'ef2d2144-c347-4db2-b7d2-3e634c6f7705'
"V1" [String]: 'f3feaa94-a92c-4ba1-b841-aa617dadefe9'

Test result:
SQL1 (ProviderResource) : 697ms
SQL2 (ProviderResource): 209ms

The first observation is that only 0.9s of the 29sec until the albums are shown are caused by the database. The performance problem is therefore definitely NOT the database.

But it seems that we have nevertheless found a bug! When you have a close look at the two SQL queries, you will notice that they are identical. Somehow, the MP2 System runs the same query twice - which should not be the case. So there is room for improvement, but (1) this improvement will not lead to the MP2 System being much faster (only 0.2sec from a total of 29sec) and (2) this improvement does not have anything to do with the particular database - it is a bug in the core system. And what you can also see is that SQLites caching mechanism works quite well. The second query is 3.5 times as fast as the first identical one...

Last but not least: I carried out this test with the MP2 standard skin - as a result, the albums are shown without covers. I don't know whether this has changed with the Titanium skin - will try later...

 

[morpheus_xx]

For the next test I continued from the Connection Test and just entered the audio section of my MP2 Client. As a result, there are 2.306 albums displayed and it took about 29 seconds until they showed up. This is extremely long, but to cut a long story short, it is probably caused by (1) my MP2 Client is connected via a slow WIFI connection and (2) we do not have a VirtualStackPanel, yet.

We have a VirtualizingStackPanel, but missing a VirtualizingWrapPanel

Does it also take 29 seconds if you use any of the list view modes (small/medium/large)? If so, the network time needs to be looked at. You might try fiddler the shows all traffic and allows measuring the times.