On 10/28/18, Gerlando Falauto <gerlando.fala...@gmail.com> wrote: > > This is the current approach: > - There is just one single-threaded writer process, which also periodically > issues DELETE statements to remove older data. > - In order to prevent long-running reading queries from blocking the > writer, I'm using WAL mode. > - The database is opened with SYNCHRONOUS=1 to prevent database corruption. > - Not doing any manual checkpoints, just limiting journal size to 100MB. > - Page size is at its default > - Underlying storage is a F2FS partition on a commodity 32GB SSD. >
Other comments on this thread have been good. Thanks, everybody! For peak performance and efficiency, omit indexes from the log table. Adding to a log is just appending to a table, which is particularly fast. But if there are indexes, entries must be inserted into the middle of the indexes (so that the all entries end up in index-order) which requires more I/O. If possible, combine two or more (as many as practical, subject to your timing constraints) inserts into a single transaction. Using the latest Linux kernels and F2FS filesystem modules, and if you compile SQLite using SQLITE_ENABLE_BATCH_ATOMIC_WRITE, and if you use DELETE mode rather than WAL mode, then SQLite will try to take advantage of the batch atomic write capabilities provided by F2FS. This can be *much* more efficient in terms of performance and I/O and also reduced wear on the flash chips. The downsides to this approach are (1) there is an exclusive lock held for a few milliseconds while doing each write, so readers and writers cannot overlap as they can in WAL mode and (2) there are scattered reports of instabilities with F2FS following power failures in the middle of transactions - it is unclear if these reports are true or not and they are rare in any event, but F2FS is relatively new technology (compared to EXT4) so there *might* still be some obscure problems on optimized code paths. Having the WAL file on a ramdisk could result in database corruption if you take a power failure in the middle of a checkpoint. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
