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: 1032501" data-attributes="member: 17886"><p>Some more test results:</p><p></p><p>In the following I used a regular path and file name for the database file name again (instead of an URI). This means that Shared Cache mode is switched off. Also SQLiteTransactions are always disposed. And of course we use our own ConnectionPool.</p><p></p><p>Page Size = 4KB / Autocheckpoint every 8192 pages</p><p>Import Test: 22:08</p><p>Page Size = 4KB / Autocheckpoint every 16384 pages</p><p>Import Test: 21:30</p><p>Page Size = 4KB / Autocheckpoint every 32768 pages</p><p>Import Test: 20:59</p><p></p><p>Page Size = 8KB / Autocheckpoint every 8192 pages</p><p>Import Test: 21:41</p><p>Page Size = 8KB / Autocheckpoint every 16384 pages</p><p>Import Test: 21:22</p><p>Page Size = 8KB / Autocheckpoint every 32768 pages</p><p>Import Test: 20:55</p><p></p><p>Page Size = 16KB / Autocheckpoint every 8192 pages</p><p>Import Test: 22:40</p><p>Page Size = 16KB / Autocheckpoint every 16384 pages</p><p>Import Test: 21:51</p><p></p><p>So for 4KB page size, Autocheckpoint=32768 seems to be the best (i.e. max wal-file size is 128MB)</p><p>For 8KB page size, we are about at the same speed for Autocheckpoint=32768 - which means however, that the maximal size of the wal file would be 256MB. I would consider this a bit too much and would therefore prefer to go with Autocheckpoint=16384 - assuming that 8KB page size has any advantage for read speed.</p><p>16KB page size is in general less ideal than 4KB and 8KB regarding write speed. So if we don't find it much faster for reads, this one is out of the game...</p><p></p><p>For the read tests, I will test 4KB, 8KB and also 16KB page size - the autocheckpoint value has no impact on pure read speed so I will use the respective best value determined for each of the options above.</p><p></p><p>But now I'm facing a problem with my read tests. I have a problem in getting repeatable and reliable results and I suppose this is caused by the Windows OS read cache. Sometimes the first read is really slow, but sometimes the first read is nearly as fast as the second. So I suppose that Windows has in the latter case already read the database file into memory.</p><p>I'm not yet clear how we deal with this. Maybe use some utility (don't remember the name right now - started with "RAM...") to clear the Windows OS cache before starting up the server. We will see...</p><p></p><p>Finally I understood how to hook into SQLite to get the internal log messages of SQLite. I will probably also introduce some optional trace logging. Let's see what information it can give us...</p></blockquote><p></p>
[QUOTE="MJGraf, post: 1032501, member: 17886"] Some more test results: In the following I used a regular path and file name for the database file name again (instead of an URI). This means that Shared Cache mode is switched off. Also SQLiteTransactions are always disposed. And of course we use our own ConnectionPool. Page Size = 4KB / Autocheckpoint every 8192 pages Import Test: 22:08 Page Size = 4KB / Autocheckpoint every 16384 pages Import Test: 21:30 Page Size = 4KB / Autocheckpoint every 32768 pages Import Test: 20:59 Page Size = 8KB / Autocheckpoint every 8192 pages Import Test: 21:41 Page Size = 8KB / Autocheckpoint every 16384 pages Import Test: 21:22 Page Size = 8KB / Autocheckpoint every 32768 pages Import Test: 20:55 Page Size = 16KB / Autocheckpoint every 8192 pages Import Test: 22:40 Page Size = 16KB / Autocheckpoint every 16384 pages Import Test: 21:51 So for 4KB page size, Autocheckpoint=32768 seems to be the best (i.e. max wal-file size is 128MB) For 8KB page size, we are about at the same speed for Autocheckpoint=32768 - which means however, that the maximal size of the wal file would be 256MB. I would consider this a bit too much and would therefore prefer to go with Autocheckpoint=16384 - assuming that 8KB page size has any advantage for read speed. 16KB page size is in general less ideal than 4KB and 8KB regarding write speed. So if we don't find it much faster for reads, this one is out of the game... For the read tests, I will test 4KB, 8KB and also 16KB page size - the autocheckpoint value has no impact on pure read speed so I will use the respective best value determined for each of the options above. But now I'm facing a problem with my read tests. I have a problem in getting repeatable and reliable results and I suppose this is caused by the Windows OS read cache. Sometimes the first read is really slow, but sometimes the first read is nearly as fast as the second. So I suppose that Windows has in the latter case already read the database file into memory. I'm not yet clear how we deal with this. Maybe use some utility (don't remember the name right now - started with "RAM...") to clear the Windows OS cache before starting up the server. We will see... Finally I understood how to hook into SQLite to get the internal log messages of SQLite. I will probably also introduce some optional trace logging. Let's see what information it can give us... [/QUOTE]
Insert quotes…
Verification
Post reply
Forums
MediaPortal 2
Plugin Development
SQLiteDatabase Plugin for MP2
Contact us
RSS
Top
Bottom
