Would remove the MDB_NOSYNC then, and see how it goes...
And LMDB beats the crap out of SQLite, in any mode. http://www.lmdb.tech/bench/microbench/
Replacing SQLite's Btree engine with LMDB makes the SQLite footprint smaller, faster, and more reliable too. https://github.com/LMDB/sqlightning
Alright, I went through this page to see why my experience is different. While it mentions that they enabled SQLite's WAL journal, it also mentions that the synchronous writes were performed with PRAGMA synchronous=FULL.
I believe that if they set PRAGMA synchronous=NORMAL, they will get an ACI-reliable database that is way faster than LMDB with write-to-disk workloads.
Most of the measurements on that page are not really useful to me; they do not persist the data (db on tmpfs) or they do not care about reliability, using dangerous settings. Only few measurements are both persisting the data and writing safely, but the SQLite configuration is sub-optimal for them.
For my purposes as an application developer, I care about comparing databases operating in safe mode i.e. a system crash should never cause total data loss. According to my experience SQLite's safe mode is many times faster than LMDB's safe mode, with a write workload. While LMDB is thrashing the disk and achieves only a handful of write transactions per second.
https://wisdom.cs.wisc.edu/workshops/spring-14/talks/Thanu.p...
LMDB is not.
[1] https://www.usenix.org/system/files/conference/osdi14/osdi14...
"using a 512GB Samsung 830 SSD and an ext4 partition.
The actual drive characteristics should not matter because the test datasets still fit entirely in RAM and are all using asynchronous writes"
Aren't they interested in persistence of the key-value data? In my experience, once data is persisted to disk or SSD, LMDB is way slower from alternatives because it needs to operate in synchronous mode to avoid corruption (effectively flushing to disk after after every transaction committed). If operated in the non-default MDB_NOSYNC mode (which is the mode chosen in the above benchmarks), then there is a high probability to be left with an unreadable database file after a crash, thus losing all your data.
It is not fair to compare with other databases in sync mode, since they might operate safe but faster in async mode. For example sqlite with PRAGMA journal_mode=WAL and PRAGMA synchronous=NORMAL can operate in semi-asynchronous mode (fsync()ing sporadically) without fear of corruption in case of crash, because it keeps a WAL journal and is able to properly roll-back after a crash. This should be much faster than LMDB's default-and-safe synchronous mode, that msync()s on every value written.