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: 1031637" data-attributes="member: 17886"><p>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:</p><p></p><p>ConnectionPool / Page Size = 1KB / No disposing of transactions at all</p><p>ImportTest: 20:25</p><p>==> Database File: 1kb, WAL-File: 1,8GB</p><p>After restart of MP2 Server: Database File: 5kb, Wal-File disappeared. No data in the database</p><p>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...</p><p></p><p>ConnectionPool / Page Size = 1KB / dispose transactions only when Commit() or Rollback() was called</p><p>ImportTest: 21:57</p><p>==> Database File: 1kb, WAL-File: 1,8GB</p><p>After restart of MP2 Server: Database File: 5kb, Wal-File disappeared. No data in the database</p><p>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:</p><p></p><p>ConnectionPool / Page Size = 1KB / dispose transactions only when NEITHER Commit() NOR Rollback() were called:</p><p>ImportTest: 28:13</p><p>This one is much slower, but actually stores all the data permanently in the database.</p><p></p><p>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)</p><p>Import Test: 23:39</p><p>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.</p><p></p><p>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</p><p>ImportTest: 29:45</p><p>As you see, slower again due to the AutoCheckpoint happening more often. Putting the call to Dispose() in a separate Task doesn't help...</p><p></p><p>ConnectionPool / Page Size = 1KB / dispose transactions ALWAYS; Autocheckpoint every 1.000 pages / Dispose SQLiteTransaction in new "longrunning" Task</p><p>ImportTest: 31:08</p><p>Again much slower - because we more often call Dispose(). Separate Task doesn't help...</p><p></p><p>ConnectionPool / Page Size = 1KB / dispose transactions ALWAYS; AutocheCkpoint every 10.000 pages / Dispose transaction and return connection to pool in new "longrunning" Task</p><p>ImportTest: 25:38</p><p>Now it's getting faster again - due to the higher value for AutoCheckpoint....</p><p></p><p>ConnectionPool / Page Size = 1KB / dispose transactions ALWAYS; Autocheckpoint every 10.000 pages</p><p>ImportTest: 23:15</p><p>Now it's clear that the Task-idea was stupid. Doing the same as above just without the Task-Overhead makes it even faster...</p><p></p><p>ConnectionPool / Page Size = 1KB / dispose transactions ALWAYS; Autocheckpoint every 32.768pages</p><p>ImportTest: 22:11</p><p>Another performance improvement by just setting an even higher value for Autocheckpoint...</p><p></p><p>ConnectionPool / Page Size = 1KB / dispose transactions ALWAYS; Autocheckpoint every 65.536 pages</p><p>Import Test: 22:10</p><p>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...</p><p></p><p>More tests to come...</p></blockquote><p></p>
[QUOTE="MJGraf, post: 1031637, member: 17886"] 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... [/QUOTE]
Insert quotes…
Verification
Post reply
Forums
MediaPortal 2
Plugin Development
SQLiteDatabase Plugin for MP2
Contact us
RSS
Top
Bottom
