SQLiteDatabase Plugin for MP2 (1 Viewer)

[MJGraf]

Finally I tested the shared cache mode of SQLite. It didn't have any positive or negative influence on the ReadTests. But this was more or less expected since my tests only use one connection from the pool (there is no parallel access to the database when you only use one client). But I would expect this to improve the database performance when you use multiple MP2 clients because in that case we may have multiple connections reading at the same time and then all connections share a common cache, i.e. when one client has read some media items, the second client (using another connection) would use the data already in the cache from the first client. Besides that, I also expect this to improve using a client while an import is running. So for now, since it has neither positive nor negative influence on my set of tests, I will continue to use shared cache mode.

What we now could test is changing the page size (currently 4KB). I only tested the influence of the page size for the ImportTest so far and there was no real difference with 4KB, 8KB or 16KB. But maybe this affects the ReatTests. The only downside is that this is a "breaking change" compared to v0.07 so people would have to reimport their shares. But anyway, I will test it....

Edit: It's not really a "breaking change". PageSize=16KB will run with a database file generated with PageSize=4KB without problems - but the database will keep it's page size of 4KB. So if there should be a performance improvement and we decide to use PageSize=16KB it will still work with old databases, but the performance gain is only there if you do a reimport - so that should be acceptable in any case...

 

[MJGraf]

ConnectionPool / Page Size = 16KB
ImportTest 29:15
ReadTest 1 and 2: No significant changes
ReadTest 3, ReadTime: 18ms, 10ms
ReadTest 4, ReadTime: 195ms, 180ms

As you see, a page size of 16KB instead of 4KB reduces the performance of the ImportTest. It has no impact on ReadTest2 1 and 2. It slows down ReadTest 3 a bit but it slows down ReadTest 4 considerably. So not a good idea...

For the protocol: I set PageSize=16384 (instead of 4096) and at the same time CacheSize=16384 (instead of 65536) because the cache size is set in number of pages. This way the cache size remained the same (256MB).

Test with 8KB page size is running...

 

[MJGraf]

Although it's only early in the afternoon, I think I need some beer now...

I did a lot of tests in the meantime and I learned a lot. I also found a bug in my code, which was responsible for a performance improvement. I'll explain later how this is possible. What I found out a couple of minutes ago, however, means that more or less all my tests I did since this post, are useless. Hence, the beer...

As I explained there, I started to use an URI instead of a regular file path and name to be able to enable the Shared Cache mode of SQLite. What I didn't know is that when you use an URI, SQLite ignores your page size setting. I just tried this several times and checked with an external database tool the page sizes used in the database files. When using an URI, the page size is always 1024 bytes - no matter what page size value you specify in the connection string. When using a regular file path and name instead, the page size in the connection string is actually applied. I don't know whether this is a bug in System.Data.SQLite or whether this is intended behavior because it's not possible to have a page size different from 1024 bytes when using Shared Cache mode. The documentation of SQLite doesn't say anything about it. But the really nasty thing is now that many of the other parameters are measured in number of pages - e.g. the cache size. So when I said use a cache size of 10000 pages and I thought the cache size was 10.000 x 4KB due to a page size of 4KB resulting in a cache of about 40MB it actually was only 10M because the page size was 1KB instead of 4KB. The same applies to the "PRAGMA wal_autocheckpoint" statement. There you can specify after what number of pages a checkpoint is run automatically. Of course I assumed wrong values in Megabytes there as well.

This is really annoying because (a) all my tests are at least partly useless and (b) we cannot use Shared Cache mode if we want to have a page size different from 4KB. For now I will drop the Shared Cache mode and do some further tests with the page size. Let's see what the outcome is...

But there is also good news: The last ImportTest took 20:59 minutes. We are getting there

 

[MJGraf]

Ok, let me tell you a bit more about what I found out in the meantime. For me it is more like a test protocol - maybe that's boring for everyone else, but I need to write this down to make sure whoever touches the SQLiteDatabase in the future knows why we chose which setting...

First of all, I realized that there is a bug in my code. Background: MP2 with SQLiteDatabase uses the MediaPortal.Database.SQLite.SQLiteTransaction class to implement transactions. Let's call this class the MP2-Transaction for now. This class wraps around the System.Data.SQLite.SQLiteTransaction class - I will call this class SQLiteTransaction in the following. Now the code in the MediaLibrary gracefully calls Dispose() on every MP2-Transaction it uses. But the implementation of the MP2-Transaction's Dispose() method did not call Dispose() on the SQLiteTransaction it contained. Now why did it work anyway?
When you call Commit() or Rollback() on the MP2-Transaction, it calls Commit() or Rollback on the contained SQLiteTransaction. By browsing through the source code of SQLiteTransaction I found out that by calling Commit() or Rollback() on the SQLiteTransaction it more or less cleans up all the necessary resources so that an additional call to Dispose() is not absolutely necessary.
But why do we have to call Dispose() anyway? Well, now it gets a bit complicated... The reason is a combination of two things: (1) When the MediaLibrary reads from the database it also uses a MP2-Transaction. For this transaction of course a "BEGIN" statement is issued to the database ("BEGIN" is the short SQL syntax for "BEGIN TRANSACTION" in SQLite). However, when we just read from the database, we of course never call Commit() or Rollback(). Now why should we care? This results from the second cause (2): System.Data.SQLite pretends that it can deal with nested transactions. But in reality, this is not really the case. What actually happens is the following: When you begin a SQLiteTransaction on a fresh SQLiteConnection, a "BEGIN" statement is issued to the database and the so called "TransactionLevel" of the SQLiteConnection is raised from 0 to 1. If you now start a second SQLiteTransaction on the same SQLiteConnection, it does not issue a further "BEGIN" command. It just raises the TransactionLevel from 1 to 2. When you now Commit() the second SQLiteTransaction you would expect that the transaction is actually committed. But this is not the case. The only thing that happens is that the TransactionLevel of the connection is lowered to 1 again. Only when you Commit() the first SQLiteTransaction and the TransactionLevel is lowered to 0 again, it actually issued a "COMMIT" statement to the database.
Now let's bring these two things together: We have a MP2-Transaction (containing a SQLiteTransaction) which is used for reading. That means we acutally issue a "BEGIN" to the database. The MP2-Transaction is disposed, but for the contained SQLiteTransaction Dispose() is not called - i.e. the TransactionLevel of the respective SQLiteConnection is still 1. Now the connection is returned to our ConnectionPool. MediaLibrary wants to write something to the database and gets a MP2-Transaction with the same underlying SQLiteConnection from our pool. We call SQLiteConnection.BeginTransaction() - and what happens? The TransactionLevel of this Connection was still 1. Therefore no "BEGIN" is issued to the database, but only the TransactionLevel of the SQLiteConnection is raised to 2. Later on the MediaLibrary wants to commit everything it has written. What happens: We do not issue a "COMMIT" to the database, we just lower the TransactionLevel from 2 to 1.
How can this lead to a performance increase? SQLite can insert multiple thousands of rows into the database in one second when all inserts happen within a single transaction. But it can only handle a few transactions per second. What we achieved here is that we put many inserts into one transaction although we thought they were executed in several transactions. This gave us a performance improvement.
Is that bug also in v0.07: Yes, but it doesn't matter... In v0.07 we close the respective SQLiteConection after the MP2-Transaction is disposed. Closing the SQLiteConnection also means resetting the TransactionLevel so the story above cannot happen...

Now I thought why not use this "bug" to improve the speed - and I did some tests. In the end, however, the best thing is to properly call Dispose() on every SQLiteTransaction no matter what it was used for. Test result will follow anyway for documentation purposes...

 

[MJGraf]

Now these are some of the test results I got in the meantime - as you see I only did the Import Test for now. As mentioned, I supposed that the page size used was 4KB, but it actually was 1KB. I corrected this in the following:

ConnectionPool / Page Size = 1KB / No disposing of transactions at all
ImportTest: 20:25
==> Database File: 1kb, WAL-File: 1,8GB
After restart of MP2 Server: Database File: 5kb, Wal-File disappeared. No data in the database
As you see, not calling Dispose() at all on the SQLiteTransaction results in a very fast import - only 20:25 minutes, but also in a mess. The transactions are never really committed and when you restart the MP2 Server all your data is gone...

ConnectionPool / Page Size = 1KB / dispose transactions only when Commit() or Rollback() was called
ImportTest: 21:57
==> Database File: 1kb, WAL-File: 1,8GB
After restart of MP2 Server: Database File: 5kb, Wal-File disappeared. No data in the database
As you see, this one is a bit slower, but results in the same mess. Huge wal-file which disappears together with all the data when you restart the MP2 Server. Reason is: When Commit() or Rollback() is called, you don't have to call Dispose() - it's the other way around: When neither Commit() nor Rollback() have been called it is in particular important to call Dispose(). Therefore:

ConnectionPool / Page Size = 1KB / dispose transactions only when NEITHER Commit() NOR Rollback() were called:
ImportTest: 28:13
This one is much slower, but actually stores all the data permanently in the database.

ConnectionPool / Page Size = 1KB / dispose transactions only when NEITHER Commit() NOR Rollback() were called; Autocheckpoint every 10.000 pages (the tests above were with a standard value for AutoCheckpoint every 1.000 pages)
Import Test: 23:39
This one is much faster because the AutoCheckpoint doesn't happen that often. The downside is that the wal-file may grow up to 10.000 (Autocommit value) x 1KB = about 10 MegaByte.

ConnectionPool / Page Size = 1KB / dispose transactions only when NEITHER Commit() NOR Rollback() were called; Autocheckpoint every 1.000 pages / Dispose SQLiteTransaction in a separate Task
ImportTest: 29:45
As you see, slower again due to the AutoCheckpoint happening more often. Putting the call to Dispose() in a separate Task doesn't help...

ConnectionPool / Page Size = 1KB / dispose transactions ALWAYS; Autocheckpoint every 1.000 pages / Dispose SQLiteTransaction in new "longrunning" Task
ImportTest: 31:08
Again much slower - because we more often call Dispose(). Separate Task doesn't help...

ConnectionPool / Page Size = 1KB / dispose transactions ALWAYS; AutocheCkpoint every 10.000 pages / Dispose transaction and return connection to pool in new "longrunning" Task
ImportTest: 25:38
Now it's getting faster again - due to the higher value for AutoCheckpoint....

ConnectionPool / Page Size = 1KB / dispose transactions ALWAYS; Autocheckpoint every 10.000 pages
ImportTest: 23:15
Now it's clear that the Task-idea was stupid. Doing the same as above just without the Task-Overhead makes it even faster...

ConnectionPool / Page Size = 1KB / dispose transactions ALWAYS; Autocheckpoint every 32.768pages
ImportTest: 22:11
Another performance improvement by just setting an even higher value for Autocheckpoint...

ConnectionPool / Page Size = 1KB / dispose transactions ALWAYS; Autocheckpoint every 65.536 pages
Import Test: 22:10
Doubling the AutoCheckpoint value again doesn't bring much more speed. So an Autocheckpoint every 32.768 pages seems to be the sweet spot for a page size of 1KB...

More tests to come...

 

[MJGraf]

Before I share the remaining test results for the Import Test, some general thoughts:
As you can already see from the tests above (and confirmed by the tests to follow) the Import Test performance gets better when we increase the Autocheckpoint value. This is expected as a higher value for AutoCheckpoint means that less checkpoints occur during an import run. At the same time, as explained some posts above, the read speed decreases until the next checkpoint has run. While an import is running, we also perform reads to the database. So when the Autocheckpoint value becomes to big, the slower reads overcompensate the faster writes and the import starts to get slower. So we have to find a value for Autocheckpoint which gives the optimal performance.
What other consequences does raising the Autocheckpoint value have: As explained above, SQLite writes new database rows into a WAL-file until a checkpoint is performed. Let's assume we have a page size of 4KB and perform an autocheckpoint every 32768 pages. This means that SQLite first writes 4KB x 32768 = 128MB to the WAL-file. Then a checkpoint is performed, i.e. those 128MB are written to the actual database file. But the WAL-file is not deleted. For performance reasons, the WAL-file stays in the size of 128MB on the disk and is overwritten from the beginning. At this point in time we use 128MB for the database file and another 128MB for the WAL-file. When the WAL-file is filled with 128MB of new data the next checkpoint is performed. After that the database file will have 256MB and the WAL-file still 128MB - and so on and so on... When the MP2 Server service is stopped, the WAL-file is checkpointed a last time and finally deleted. When restarting the MP2 Server a new WAL-file is generated, but only with some KB in size - until we start writing to the database again.
So the result of a higher autocheckpoint value is also more disk space requirement. This is the tradeoff for the performance increase. But for now I just assume that a maximum of additional 128MB - furthermore only temporary until the next restart of MP2 Server - is not a problem in the days of hundreds of gigabyte capacity even on SSDs and terabytes on regular hard disks.
I have already started to make all these values "Settings" of the SQLiteDatabase plugin so that finally everyone can tweak these settings by editing the respective XML-file. But nevertheless we need standard settings which are applied by default and restored when the XML-file is deleted (in particular if someone messed up his settings...). For these standard settings I consider a temporary additional space requirement of max 128MB as ok.

 

[mrj]

Hi MJGraf
Thanks for your work on this.
Why not keep the default xml file on line, and download it when it is missing.
That way a user can get back the default values by simply removing his/hers messed up xml file.
/mrj

 

[MJGraf]

Thanks mrj,

I thought about something even simpler. I use the inbuilt setting system of MP2 which means I have a separate SQLiteSettings class. This class contains the default values as constants. When you startup MP2 Server for the first time, these values are written to disk as an XML file. As of then MP2 uses the XML file to read the settings, i.e. you can change the values in the XML file. When you want to go back to the default values, you just have to delete the XML file. On the next startup, a new XML files with the standard values is automatically generated again. This is at least how other parts of MP2 handle this.

That reminds me of something that we could improve in the settings system. We have an attribute to be applied to the respective setting properties. With the attribute we can specify whether this is a per user or global setting. We can also specify a default value through this attribute. But as default value we can only use constants. It would be helpful if we could also use a parameterless delegate - which is not possible at the moment. Use case: The default value for the cache size of the SQLiteDatabase is not a constant, but it depends on the amount of RAM available to the OS. So I wrote a little method which determines the amount of RAM and based on this returns the recommended default cache size. Currently I have to do a workaround to get this working (using the constructor of the SQLiteSettings class to call this method and store the result in a private variable. For the settings property I use a default value of -1 in the respective attribute and in the Get-method of the property I check whether it is -1 and if so I return the calculated value from the private variable - else the value actually stored in the property). It would be much simpler if we could just use a delegate to the calculating method as default value in the attribute. No need for any workaround. But that's just a reminder so that I don't forget about it...

 

[morpheus_xx]

That reminds me of something that we could improve in the settings system. We have an attribute to be applied to the respective setting properties. With the attribute we can specify whether this is a per user or global setting. We can also specify a default value through this attribute. But as default value we can only use constants. It would be helpful if we could also use a parameterless delegate - which is not possible at the moment. Use case: The default value for the cache size of the SQLiteDatabase is not a constant, but it depends on the amount of RAM available to the OS. So I wrote a little method which determines the amount of RAM and based on this returns the recommended default cache size. Currently I have to do a workaround to get this working (using the constructor of the SQLiteSettings class to call this method and store the result in a private variable. For the settings property I use a default value of -1 in the respective attribute and in the Get-method of the property I check whether it is -1 and if so I return the calculated value from the private variable - else the value actually stored in the property). It would be much simpler if we could just use a delegate to the calculating method as default value in the attribute. No need for any workaround. But that's just a reminder so that I don't forget about it...

When using Attributes on properties you can only use constants, that's right. If you need custom logic, simply use the constructor of the settings class and fill in the required properties for the first time.

 

[MJGraf]

When using Attributes on properties you can only use constants, that's right. If you need custom logic, simply use the constructor of the settings class and fill in the required properties for the first time.

Thanks, Morph. I wasn't sure whether that works. That means I don't specify any standard value in the attribute, just set the respective property to the standard value in the constructor and the SettingsManager will take care of replacing this value with the one stored in the xml-file, if any, before returning the SQLiteSettings object. Is that correct?