pragma journal_mode = WAL;
pragma synchronous = normal;
pragma temp_store = memory;
pragma mmap_size = 30000000000;
> WAL mode is always consistent with synchronous=NORMAL, but WAL mode does lose durability. A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash.
But then I realised "pragma fullfsync=off" is the default, which is a MacOS-specific pragma.
So, on MacOS only, WAL with synchronous=FULL (the default for that pragma) has the same durability issue as synchronous=NORMAL, that committed transactions might be rolled back following power loss or system crash, albeit with different probabilities.
If you think about this in latency terms, you are able to insert a row and be done with the entire ceremony in about 12 microseconds. This is serialized throughput too.
I think it is unlikely you would get this kind of performance with a hosted solution across the network. You could cheat with batching & caching, but for a cold, one-record non-query, nothing comes close to the latency of SQLite on a local NVMe device.
Source code of Python test file: https://gist.github.com/maksadbek/2385b002b439e03dc948b05593...
I’m open to being convinced otherwise, but I would expect that inserts/s would vary a bunch by filesystem.
But I do agree that local nvme with any file system is absurdly better than what you’re likely to find in typical cloud envs.
If my memory serves me well, in approximately 6.8 seconds (give or take), I could insert 10M rows and bear in mind I didn't use WAL at all.
Not bad I would say, not bad at all.
https://github.com/onthegomap/planetiler/blob/db0ab02263baaa...
It batches inserts into bulk statements and is able to do writes in the 500k+ per second range, and reads are 300-400k/s using those settings.
I can make a car go really fast if I eliminate the weight of having safety equipment on it like a bumper, seatbelt and airbag.
This is one of those use cases where SQLite isn’t replacing <database> - it’s replacing fopen.
1. Ugh this thing is too integrated, we should break it up into modular parts so we can re-assemble them using only the parts we need. (Examples: most OS kernels, sqlite apparently, big excel spreadsheets, web frameworks like Rails)
2. Ugh this thing is too modular, you need a bazillion parts just to get a useful system. (Examples: analytics platforms consisting of 20+ unix tools strapped together in a pipeline, most microservices architectures, everything in the node ecosystem)
Measuring software in human capital isn't a good idea. Software becomes crusty very quickly. Once you get a huge user base re-architecting becomes almost impossible without a re-write.
A lot of velocity can come from completely freeing yourself from an existing solution and it's customers in the short-term.
These knobs by the way are part and parcel for generic storage systems. Including filesystems a bit too and definitely all databases I've ever seen (because different HW will have different optimal settings).
actually, i believe it's quite the contrary: most of what we need nowadays it's already been invented and developed.
you can be incredibly successful as an engineer at many companies just by knowing what knobs are there and how to turn them properly.
and i think this is relevant because most people end up reinventing a square wheel that only does 10% of what the most used, widely spread, already-open-source wheel does.
[1]: https://phiresky.github.io/blog/2021/hosting-sqlite-database...
WAL mode has some issues where depending on the write pattern, the WAL size can grow to infinity, slowing down performance a lot. I think this usually happens when you have lots of writes that lock the table so sqlite never gets to doing wal_autocheckpoint.
Wal2 mode does not have this problem. In wal2 mode, wal files do not grow indefinitely even if the checkpointer never has a chance to finish uninterrupted
I wonder if this could be further extended to better support concurrent writes. Depending on the implementation, with wal2 readers may be reading from both hot and cold files without blocking. So this may potentially be extendable to read from two hot files, or two hot files and two cold files.
sqlite> PRAGMA journal_mode = delete;
delete
sqlite> PRAGMA journal_mode = wal2;
delete
[0]: https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_co...
Based on https://www.sqlite.org/wal.html it seems the WAL index is mmapped as a workaround to some edge cases not relevant to many application developers. They say it shouldn’t matter, but with the larger page sizes you’re using, using the original implementation approach they describe (volatile shared memory) actually might improve performance slightly - do you know if your WAL index ever exceeds 32KiB? Not sure as to the difficulty of resurrecting that old functionality though.
Also, this case seems like something that could happen from time to time depending on what you’re doing. Did you encounter it? Case:
> When the last connection to a particular database is closing, that connection will acquire an exclusive lock for a short time while it cleans up the WAL and shared-memory files. If a second database tries to open and query the database while the first connection is still in the middle of its cleanup process, the second connection might get an SQLITE_BUSY error.
Both the WAL docs and the article mention blocking checkpointing/a need for reader gaps to ensure the WAL flushes, or a possibility that WAL files will grow indefinitely. I had some speculation that this was an implementation limitation, and it turns out another comment mentions WAL2 may relax this requirement by using two WAL files split between “hot” and “cold”. Curious how the performance might compare with this: https://sqlite.org/cgi/src/doc/wal2/doc/wal2.md
I have explained my rationale and approach here - https://avi.im/blag/2021/fast-sqlite-inserts/
the repo link - https://github.com/avinassh/fast-sqlite3-inserts
(Not suggesting it’s wrong, just interesting to hear from others as to why)
To answer the person’s question, there are easy ways and hard ways with all the trade offs you would expect. The easy way is to use an ODBC connector. That makes it easier to change the DB engine but it’s going to hurt performance. Chances are though that the performance will still be good enough for applications like database-as-file-format.
I would consider data loss on crash to be "corruption", for sure. And synchronous=normal + journal_mode=WAL can lose data:
https://www.sqlite.org/pragma.html#pragma_synchronous
> A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash.
It's _easier_ to write IO with mmap, but hand-optimized file IO could do even faster. And DBMSes historically cared a lot about optimizing file access.
So I'd expect SQLite to be faster without mmap, as I expect their developers to nurture the file access, instead of relying on OS-provided mmap.
PS: For example, say your code has a byte array, memory-mapped from a file. If the code needs to do a random read from it, it has no way of knowing whether that read would require waiting for a page to be read from disk, or it's already cached. Hand-optimized file IO have an option to maybe do some other things instead of waiting for disk (or during, or before).