My application uses SQLite to cache data. Specifically, the cache isn't the storage of record for anything; it's just a local copy of data that's stored remotely on servers of record. The cache primarily facilitates rapid searching of the data locally, via FTS.
I'm seeking specific advice on how to tune SQLite for this application when deployed on a target with extremely poor write performance. On this target writing in many small chunks is much more expensive than writing in a single big sequential chunk. In particular, the write syscall is very expensive, because frequent writes cause the host to spend huge amounts of time in lock contention, because the locking strategy is very coarse. Given that the data I am storing in SQLite is expendable -- I can always fetch it again from the server -- I don't really care if I lose, say, the last 30 seconds of data written if the machine loses power, the app is killed by the host operating system, etc. However, I want to avoid the database going corrupt, since that requires fetching everything again. It seems like an optimal strategy for this would be to keep a WAL-like journal in memory. I have plenty of RAM so 64MB for an in-memory WAL "file" would work. However, I don't see any way to tell SQLite to use WAL mode but keep the WAL file in memory. I also believe from reading the docs that if the memory-based WAL file is lost (e.g., loss of power) then the database will be corrupt. I've tried journal_mode=MEMORY but that didn't seem to help any. It seems that no matter what pragmas I use, I can't convince SQLite to keep transactions in memory very long. What I need, I guess, is some tuning parameter -- or I can write my own VFS -- that buffers entire transactions, then periodically flushes large groups of transactions at once, minimizing the number of write calls. I'd welcome any suggestions from SQLite experts on this. Dave Sent with inky<http://inky.com?kme=signature>
