Reply to thread

Message: <blockquote data-quote="MJGraf" data-source="post: 1031702" data-attributes="member: 17886">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.</blockquote>

Verification