Reply to thread

Message: <blockquote data-quote="MJGraf" data-source="post: 1035685" data-attributes="member: 17886">Back to SQLiteDatabase and the speed improvements. In the meantime I got two things solved:Shared Cache ModeAs mentioned above, I would like to use shared cache more because I expect it to improve the performance in particular when using multiple clients at the same time. The problem was that to enable shared cache mode in system.data.sqlite it is necessary to use a so called "FullURI" with the parameter "?cache=shared" at the end. But when doing so, system.data.sqlite ignores the page size value that we specify in the connection string. I found the respective code in the system.data.sqlite sources and I'm not sure whether this is a bug in system.data.sqlite. The standard page size of SQLite is 1024 bytes, which is considerably slower than using 4096 or 8192 bytes. Now the solution to this was easier than I thought. The page size is - obviously - saved in the respective database file. I.e., if you create a database file with a page size of 4096 bytes, it will keep this page size forever unless you explicitly change it. So the solution was that SQLiteDatabase now checks whether the database file already exists. If not, it creates the database with a connection string that uses the datasource property instead of the FullURI (and therefore does not use shared cache) and the correct page size as specified in our SQLiteSettings. It then closes and disposes this "one-time-connection" and as of now uses connections with FullURIs in the connection string. Now we have a database file with a correct page size and for all future accesses to the database we have the shared cache mode. <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite24" alt="(y)" title="Thumbs Up    (y)" loading="lazy" data-shortname="(y)" />Since I don't know whether the behavior explained above is a bug in system.data.sqlite or whether it is intended behavior, I put a lot of comments in the code to explain this situation. If system.data.sqlite considers this a bug and this bug is solved in future versions, we can take this potential "workaround" out of our code...A small hint to @[USER=48495]morpheus_xx[/USER] (sorry for tagging you once more <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" /> ): When I remember correctly, for your integration of TVE3.5 into MP2 Server you take the connection string from the MP2 database, extract the DataSource property, change the name of the database(-file) and use the resulting connection string for the TVE3.5 EF-database-connection. Now as explained above, SQLiteDatabase does not have a DataSource property anymore, it has a "FullURI" instead so we would have to adapt this part of the integration code accordingly. For now we first need code first support in sqlite, so it still has time until we have to take care of this. Just wanted to make sure one of us remembers this when we first see the respective exception <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" />locking_mode=EXCLUSIVEAnother thing I wanted to try for quite some time was the so called <a href="http://sqlite.org/pragma.html#pragma_locking_mode" target="_blank">exclusive locking mode</a>. Normally, SQLite acquires OS file locks only for the time it actually reads or writes to the database file. This sounds reasonable, but it requires getting and releasing OS file locks for each and every transaction, which takes time. That's what exclusive locking mode avoids. When a connection first acquires an operating system file read lock, it keeps this lock. When it first acquires an operating system write lock, it keeps the write lock forever. So no need to permanently acquire and release read or write locks = better performance.There is another potential performance gain when using exclusive locking mode and WAL journal mode (which we do). In WAL-mode, we have in particular three files while MP2 server is running. The database file itself (Datastore.s3db), the write ahead log file (Datastore.s3db-wal) and the shared memory index file (Datastore.s3db-shm). The latter one is used when multiple connections access the same database file at the same time. It is kind of an index of what is contained in the write ahead log file. This shared memory index file is not needed in locking mode exclusive, because there is only one connection holding a lock on the database - no need to write this file to the file system, the index can be kept in memory.Now switching on exclusive locking mode was easy - just issue "PRAGMA locking_mode=EXCLUSIVE;" to the database. But the shared memory index file was still there. It took a while until I solved this one: When using WAL-mode, you can in general easily switch between locking mode exclusive and locking mode normal. To make this possible, SQLite generates and maintains this shared memory index file although you switch to exclusive locking mode - so no performance improvement. If you want to avoid this file to be created, you have to switch on exclusive locking mode BEFORE you enter WAL-mode. But the problem is that we specify WAL-mode in our connection string - which results in system.data.sqlite issuing a "PRAGMA journal_mode=WAL" to the database already when we open the connection (I only found this out with my extensive logging option...). So when I created a connection I thought the first command was "PRAGMA locking_mode=EXCLUSIVE;", but it was not.The solution I found is similar to the solution for the shared cache mode above. Unfortunately, the locking mode is not stored in the database file, but has to be set for every connection. So we have to apply this "workaround" on every MP2 server start - but the impact on the startup speed is not measurable on my system (below 1ms). What I do now is we first create another "temporary connection" without entering WAL-mode. Then we switch on exclusive locking mode. We close the connection and dispose it. As of now we can use our regular connections with the regular connection string - but we still have to switch on exclusive logging mode for each and every "regular" connection. By doing so, no shared memory index file is ever created. Bingo...The combinationSome of you may already have asked yourself: What the heck is the advantage of exclusive locking mode? There can only be one connection holding an exclusive operating system file lock on our database file. Doesn't that make multithreaded access to the database impossible?Yes and No <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" />If we don't use shared cache mode, but exclusive locking mode, we are doomed. Wonderfully reproducible deadlocks as soon as two or more database connections are in use. One of those connections has the exclusive lock and when a thread fetches a connection from our cache pool, does NOT get this one connection holding the exclusive lock but one of the other connections and then tries to write something into the database, it waits forever for this lock to be released.BUT... When using shared cache mode, all the connections used by one process (even if they are used by multiple threads) look to other processes and - most importantly to the operating system - as one single connection. The picture here probably explains it best: <a href="http://www.sqlite.org/sharedcache.html" target="_blank">http://www.sqlite.org/sharedcache.html</a> As a result, we can have as many connections as we want withing one process - the MP2 Server process - but with as many threads as we want and nevertheless save the time for permanently acquiring and releasing OS file locks.The only downside I could see is that now you can't open the database file with another third party tool while the MP2 Server is running - but you shouldn't do so anyway so this is imho acceptable <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" /> If you want to open the DB file externally, stop MP2 Server Service first...What we could now do is additionally enable "read uncommited" mode for our transactions (only possible in SQLite when shared cache is enabled). This would mean that we can even read from a table in our database file while another thread at the same time writes to exactly that table. On the other hand, this may obviously result in reading parts of a transaction, which may not be desired. I don't know how this would affect MP2 as a whole, because we are not talking about a business critical banking database here - it's all just about media files... So I will probably test how much this would influence our performance and only analyze the impact if there is a material performance increase by that. Otherwise I would stick with "serializable" for our transactions.Now the resultsThe pure ImportTest result is not that impressive:Page Size = 4KB / Shared Cache / temp_store=MEMORY / Exclusive Locking without SHM-FileImportTest: 20:09So a couple of seconds faster than before (heck I want those missing 10 seconds to be below 20 mins before I release the next version <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" /> )But what was impressive for me was indeed the "feeling" when using multiple MP2 clients. What I did is that I started my import test a second time and while the import was running, I "tortured" the MP2 Server with 3 concurrent MP2 clients (couldn't find more boxes here...). I tried to enter the audio section of MP2 with all three clients concurrently (as good as possible with two hands...) and permanently clicked on new and different filters. The log file showed that in the end the MP2 Server was using 6 database connections. And I couldn't notice any difference to when I use one MP2 Client and no import is running. Everithing was smooth. Really impressive. Unfortunately, I cannot "measure" that improvement, so everyone who want's to feel this improvement probably has to install at least three MP2 Clients <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" />What is nextAs mentioned above, I'm currently waiting for the next system.data.sqlite version to be released.Then there is read uncommitted mode - as explained above.But I already have an idea what else to do in the meantime... My extensive logs of SQLiteDatabase are very interesting. When we e.g. execute a simple insert, the extended SQLite logs show the following:[CODE]2013-10-23 20:37:19.668130 [Debug][Main]: SQLite (): Preparing {INSERT INTO MIA_TYPES (MIAM_ID, NAME, CREATION_DATE, MIAM_SERIALIZATION) VALUES (@MIAM_ID, @[USER=45291]name[/USER], @CREATION_DATE, @MIAM_SERIALIZATION)}...2013-10-23 20:37:19.668130 [Debug][Main]: SQLite (): Statement 117328960 paramter count is 4.2013-10-23 20:37:19.668130 [Debug][Main]: SQLite (): Statement 117328960 paramter #1 name is {@MIAM_ID}.2013-10-23 20:37:19.668130 [Debug][Main]: SQLite (): Statement 117328960 paramter #2 name is {@NAME}.2013-10-23 20:37:19.668130 [Debug][Main]: SQLite (): Statement 117328960 paramter #3 name is {@CREATION_DATE}.2013-10-23 20:37:19.668130 [Debug][Main]: SQLite (): Statement 117328960 paramter #4 name is {@MIAM_SERIALIZATION}.2013-10-23 20:37:19.668130 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #1 with database type Guid and raw value {0a296acd-f95b-4a28-90a2-e4fd2a4cc4ed}...2013-10-23 20:37:19.683730 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #1 as type System.Byte[] with value {cd6a290a5bf9284a90a2e4fd2a4cc4ed}...2013-10-23 20:37:19.683730 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #2 with database type String and raw value {ProviderResource}...2013-10-23 20:37:19.683730 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #2 as type System.String with value {ProviderResource}...2013-10-23 20:37:19.683730 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #2 as type System.Byte[] with value {50726f76696465725265736f7572636500}...2013-10-23 20:37:19.683730 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #3 with database type DateTime and raw value {23.10.2013 20:37:19}...2013-10-23 20:37:19.683730 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #3 as type System.DateTime with value {2013-10-23T20:37:19.6681304+02:00 Local 635181574396681304}...2013-10-23 20:37:19.683730 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #3 as type System.Byte[] with value {323031332d31302d32332032303a33373a31392e3636383133303400}...2013-10-23 20:37:19.683730 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #4 with database type String and raw value {<MediaItemAspectMetadata xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Id="0a296acd-f95b-4a28-90a2-e4fd2a4cc4ed" IsSystemAspect="false"><Name>ProviderResource</Name><Attributes><AttributeType><AttributeName>System-Id</AttributeName><AttributeType>System.String</AttributeType><Cardinality>Inline</Cardinality><Indexed>true</Indexed><MaxNumChars>100</MaxNumChars></AttributeType><AttributeType><AttributeName>Path</AttributeName><AttributeType>System.String</AttributeType><Cardinality>Inline</Cardinality><Indexed>true</Indexed><MaxNumChars>1000</MaxNumChars></AttributeType><AttributeType><AttributeName>ParentDirectory</AttributeName><AttributeType>System.Guid</AttributeType><Cardinality>Inline</Cardinality><Indexed>true</Indexed><MaxNumChars>0</MaxNumChars></AttributeType></Attributes></MediaItemAspectMetadata>}...2013-10-23 20:37:19.683730 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #4 as type System.String with value {<MediaItemAspectMetadata xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Id="0a296acd-f95b-4a28-90a2-e4fd2a4cc4ed" IsSystemAspect="false"><Name>ProviderResource</Name><Attributes><AttributeType><AttributeName>System-Id</AttributeName><AttributeType>System.String</AttributeType><Cardinality>Inline</Cardinality><Indexed>true</Indexed><MaxNumChars>100</MaxNumChars></AttributeType><AttributeType><AttributeName>Path</AttributeName><AttributeType>System.String</AttributeType><Cardinality>Inline</Cardinality><Indexed>true</Indexed><MaxNumChars>1000</MaxNumChars></AttributeType><AttributeType><AttributeName>ParentDirectory</AttributeName><AttributeType>System.Guid</AttributeType><Cardinality>Inline</Cardinality><Indexed>true</Indexed><MaxNumChars>0</MaxNumChars></AttributeType></Attributes></MediaItemAspectMetadata>}...2013-10-23 20:37:19.683730 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #4 as type System.Byte[] with value {3c4d656469614974656d4173706563744d6574616461746120786d6c6e733a7873693d22687474703a2f2f7777772e77332e6f72672f323030312f584d4c536368656d612d696e7374616e63652220786d6c6e733a7873643d22687474703a2f2f7777772e77332e6f72672f323030312f584d4c536368656d61222049643d2230613239366163642d663935622d346132382d393061322d6534666432613463633465642220497353797374656d4173706563743d2266616c7365223e3c4e616d653e50726f76696465725265736f757263653c2f4e616d653e3c417474726962757465733e3c417474726962757465547970653e3c4174747269627574654e616d653e53797374656d2d49643c2f4174747269627574654e616d653e3c417474726962757465547970653e53797374656d2e537472696e673c2f417474726962757465547970653e3c43617264696e616c6974793e496e6c696e653c2f43617264696e616c6974793e3c496e64657865643e747275653c2f496e64657865643e3c4d61784e756d43686172733e3130303c2f4d61784e756d43686172733e3c2f417474726962757465547970653e3c417474726962757465547970653e3c4174747269627574654e616d653e506174683c2f4174747269627574654e616d653e3c417474726962757465547970653e53797374656d2e537472696e673c2f417474726962757465547970653e3c43617264696e616c6974793e496e6c696e653c2f43617264696e616c6974793e3c496e64657865643e747275653c2f496e64657865643e3c4d61784e756d43686172733e313030303c2f4d61784e756d43686172733e3c2f417474726962757465547970653e3c417474726962757465547970653e3c4174747269627574654e616d653e506172656e744469726563746f72793c2f4174747269627574654e616d653e3c417474726962757465547970653e53797374656d2e477569643c2f417474726962757465547970653e3c43617264696e616c6974793e496e6c696e653c2f43617264696e616c6974793e3c496e64657865643e747275653c2f496e64657865643e3c4d61784e756d43686172733e303c2f4d61784e756d43686172733e3c2f417474726962757465547970653e3c2f417474726962757465733e3c2f4d656469614974656d4173706563744d657461646174613e00}...[/CODE]What we can see here is that for every parameter added to the insert statement, there are multiple type conversions happening. One conversion for type GUID and two conversions for type DateTime and String in this case. The reason is probably that (except for binary data) we use DBUtils.AddSimpleParameter method to add the parameters. This method uses kind of a generic database type vs. dot net type mapping - but SQLite doesn't support all these generic database types and maps them to its internal types. I think we could get some further speed improvement, if we used the specific system.data.sqlite methods for adding parameters.Let's see if this gives us the missing 10 seconds <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" />Michael</blockquote>

Verification