WAL cannot be in page. WAL is where your data is written before the real database file is updated. It is needed to manage multi-user and multi-program access, and to ensure that if your computer crashes no data is lost. For more information see

https://sqlite.org/wal.html

Thanks for the answer. I am still confused that it prevent data loss in case of crash, why the fdatasync after the data page has been written? I suppose with WAL, the order should be:

pwrite64(4,       WAL,    24, 1278920) = 24
fdatasync(4)                           = 0
pwrite64(4, data page, 32768, 1278944) = 32768

as the comment said:

On a checkpoint, the WAL is first VFS.xSync-ed, then valid content of the WAL is transferred into the database, then the database is VFS.xSync-ed. The VFS.xSync operations serve as write barriers - all writes launched before the xSync must complete before any write that launches after the xSync begins.

And why not to persist WAL in another file (currently both fd=4).

I don't think your straces here are looking at a CHECKPOINT. I'm not a WAL expert by any means but I'd say that fd=4 is your -wal file and that each transaction is appending a 24-byte header followed by the contents of modified pages. Later these will be merged to the main DB (probably fd=3?) via a CHECKPOINT.

I suspect there's no point syncing the WAL header without the associated data because in the event of a crash/power outage you need to be able to have the whole transaction persisted to disk or roll back to the previous transaction.

That really makes sense. Since at each fdatasync, there are two writes (one is in page_size; another is in 24B and will be padding to 4KB by OS, observed by blktrace), but my device (WD-SN850) block size (min-write-unit) is 32KB, where un-aligned write would be slow, just like this one.

I believe many new devices have larger block size, so maybe it is possible that SQLite can split WAL log into a separate directory. e.g., changing from data_store_directory/0001.wal to data_store_directory/wal/0001.wal, and add something like a PRAGMA to control this behavior. In this way, I can make a symbol link of wal to another device. I think this may provide a more flexible env for better performance.

Just like what has been done in MongoDB: directoryForIndexes

Maybe in some versions in the future, would SQLite could take that into consideration?

Thanks!

In my experience fdatasync is usually the bottleneck -- lots of small transactions are the worst for this so best to batch changes together where possible.

You might find PRAGMA journal_mode=rollback works better in your case as all writes would be aligned in that case. But this changes concurrency semantics; readers will sometimes encounter SQLITE_BUSY when a change is pending and have to retry once the commit has completed. The writer also has to wait for readers to clear so a single long transaction can cause havoc in this mode.

Sure! my benchmark tells me fsync/fdatasync is the root cause. Batching is very encouraging improving direction, so in case WAL can not fit in page and we want to keep the benefits that the read concurrency WAL provides, maybe there may be some intermediate solution. We do have a optane 905p that bear small write but with only limited capacity. So I may like to have a workaround like symbol link.