6 ms·
SQLite async connection pool for high-performance
- slaily 1y agoIf you’re building Python async apps (FastAPI, background jobs, etc.) with SQLite, you’ll eventually hit two issues - Opening/closing connections is fast, but not free—overhead adds up under load - SQLite writes are globally locked aiosqlitepool is a tiny library that adds connection pooling for any asyncio SQLite driver (like aiosqlite): - It avoids repeated database connection setup (syscalls, memory allocation) and teardown (syscalls, deallocation) by reusing long-lived connections - Long-lived connections keep SQLite's in-memory page cache "hot." This serves frequently requested data directly from memory, speeding up repetitive queries and reducing I/O operations - Allows your application to process significantly more database queries per second under heavy load Enjoy!
- slashdev 1y agoHow does this help with the second issue, the write locks?
- ncruces 1y agoNo idea if it applies, but one way would be to direct all writes (including any transaction that may eventually write) to a single connection. Then writers queue up, while readers are unimpeded.
- dathinab 1y agoif you enable WAL mode with sqlite then readers are not blocked by writer so only writers queue up without needing any special case handling to archive it (in general you _really_ should use WAL mode if using sqlite concurrently, you also should read the documentation about WAL mode tho)
- ncruces 1y agoWriters won't queue up, rather they'll storm the place, taking turns at asking “can I go now” and sleeping for (tens, hundreds of) milliseconds at a time. This only gets “worse” as computers get faster: imagine how many write transactions a serial writer could complete (WAL mode and normal synchronous mode) while all your writers are sleeping after the previous one left, because they didn't line up? And, if you have a single limited pool, your readers will now be stuck waiting for an available connection too (because they're all taken by sleeping writers). It's much fairer and more efficient for writers to line up with blocking application locks.
- rich_sasha 1y agoI was running into some horrendous issues with WAL, where the WAL file would grow boundlessly, eventually leading to veery slow reads and writes. It's fixable by periodically forcing the WAL to be truncated, but it took me a lot of time and pain to figure it out.
- normie3000 1y agoInteresting, were there any warning signs beyond general query slowdown?
- rich_sasha 1y agoNo warning signs and very little about it on the Internet. Just performance slows to a grind. Also hard to replicate. If I had a blog, I'd be writing about it.
- normie3000 1y agoAnd how big was the WAL file getting compared to normal? As someone running SQLite in prod it would be comforting at least to have some heuristics to help detect this situation!
- bawolff 1y agoI think this is mentioned in the docs https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html
- mostlysimilar 1y agoAround what amount of load would you say the overhead of opening/closing becomes a problem?
- jitl 1y agoIt depends hugely on how you decide to manage the connection objects. If you have a single thread / single core server that only even opens a single connection, then connection open overhead is never a problem even under infinite load. The two main issues w opening a connection are: 1. There is fixed cost O(database schema) time spent building the connection stuff. Ideally SQLite could use a “zygote” connection that can refresh itself and then get cloned to create a new one, instead of doing this work from scratch every time. 2. There is O(number of connections) time spent looking at a list of file descriptors in global state under a global lock. This one is REALLY BAD if you have >10,000 connections so it was a major motivator for us to do connection pooling at Notion. Ideally SQLite could use a hash table instead of a O(n) linear search for this, or disable it entirely. Both of these issues are reasons I’m excited about Turso’s SQLite rewrite in Rust - it’s so easy to fix both of these issues in Rust (like a good hash table is 2 LoC to adopt in Rust) whereas in the original C it’s much more involved to safely and correctly fix the issue in a fork. Furthermore, it would be great to share more of the cache between connections as a kind of “L2 cache”; again tractable and safe to build in Rust but complicated to build in a fork of the original C. Notion uses a SQLite-backed server for our “Database” product concept that I helped write, we ran in to a lot of these kinds of issues scaling reads. We implemented connection pooling over better-sqlite3 Node module to mitigate these issues. We also use Turso’s existing SQLite C fork “libsql” for some connections since it offers a true async option backed by thread pool under the hood in the node driver, which helps in cases where you can have a bottleneck serializing or deserializing data from “node” layout to “SQLite c” layout or many concurrent writes to different DBs from a single NodeJS process.
- manmal 1y agoDoesn’t SQLite have its own in-memory cache? Is this about having more control re cache size?
- dathinab 1y agoyes, per "open connection", hence why not closing+reopening connections all the time helps the cache ;)
- bootsmann 1y agoIs there a significant advantage of the sqlite in-memory page cache over the page cache that's already included with the operating system?
- jitl 1y agoYes: SQLite needs to inspect the schema when it opens a new connection object and does some O(number of conns) lookups in global state during this process. It’s best to avoid re-doing this work.
- gwbas1c 1y agoImportant word: > Python Your repo and the readme.md don't say "python." The title of this post doesn't say "python." It took me a while to realize that this is for python, as opposed to a general-purpose cache for, say, libsqlite.
- kstrauser 1y agoThe tag at the top of the readme, under the title, shows which Python versions it supports. If it never mentioned Python at all, that would be the tipster.
- sjsdaiuasgdia 1y agoLet's see... There's tags showing what Python versions are supported. The root dir of the repo contains a 'pyproject.toml' file. The readme contains installation instructions for pip, poetry, and uv, all of which are Python package managers. The readme contains example code, all of which is in Python. The readme references asyncio, a Python module that is included in the standard library for Python 3. The 'Languages' widget on the page shows 99.2% of the repo's code is in Python. Every file not in the root dir has a .py extension. Yeah, I can see why it was so hard to figure out...
- tracker1 1y agoI'm mostly with you.. it would still be nice if the title reflected the language limitation/feature.
- d1l 1y agoThis is strange on so many levels. SQLite does not even do network I/O. How does sharing a connection (and transaction scope) in an asyncio environment even work? Won’t you still need a connection per asyncio context? Does sqlite_open really take long compared to the inevitable contention for the write lock you’ll see when you have many concurrent contexts? Does sqlite_open even register in comparison with the overhead of the python interpreter? What is an asyncio SQLite connection anyways? Isn’t it just a regular one that gets hucked into a separate thread?
- simonw 1y agoIf you're talking to a 100KB SQLite database file this kind of thing is likely unnecessary, just opening and closing a connection for each query is probably fine. If you're querying a multi-GB SQLite database there are things like per-connection caches that may benefit from a connection pool. > What is an asyncio SQLite connection anyways? Isn’t it just a regular one that gets hucked into a separate thread? Basically yes - aiosqlite works by opening each connection in a dedicated thread and then sending async queries to it and waiting for a response that gets sent to a Future. https://github.com/omnilib/aiosqlite/blob/895fd9183b43cecce89310d8ef00f9f632b9afe9/aiosqlite/core.py#L112-L122 https://github.com/omnilib/aiosqlite/blob/895fd9183b43cecce8...
- crazygringo 1y ago> If you're querying a multi-GB SQLite database In which case SQLite is probably the wrong tool for the job, and you should be using Postgres or MySQL that is actually designed from the ground up for lots of concurrent connections. SQLite is amazing. I love SQLite. But I love it for single-user single-machine scenarios. Not multi-user. Not over a network.
- simonw 1y agoMulti-GB is tiny these days. I didn't say anything about concurrent access. SQLite with WAL mode is fine for that these days for dozens of concurrent readers/writers (OK only one writer gets to write at a time, but if your writes queue for 1-2ms who cares?) - if you're dealing with hundreds or thousands over a network then yeah, use a server-based database engine.
- mayli 1y agoFYI, I've once had few long-lived connection with wal, and wal file just goes exploded. Turns out sqlite won't truncate the wal if there are open connections.
- infamia 1y agoUsing WAL2 should make that problem better. It has two WAL files it alternates between when making writes, so the system has an opportunity to check point the WAL file not in use. https://sqlite.org/src/doc/wal2/doc/wal2.md https://sqlite.org/src/doc/wal2/doc/wal2.md
- bob1029 1y agoI've been thinking about trying pre-serialization of SQLite commands to enable single-writer against a singleton SQLiteConnection using something like Channel<T> or other high performance MPSC abstraction. Most SQLite providers have an internal mutex that handles serialization, but if we can avoid all contention on this mutex things might go faster. Opening and closing SQLite connections is expensive. If we can re-use the same instance things go a lot faster.
- bawolff 1y ago> The primary challenge with SQLite in a concurrent environment (like an asyncio web application) is not connection time, but write contention. SQLite uses a database-level lock for writes. When multiple asynchronous tasks try to write to the database simultaneously through their own separate connections, they will collide. This contention leads to a cascade of SQLITE_BUSY or SQLITE_LOCKED errors. I really don't get it. How would this help? The benchmarks dont mention which journal mode sqlite is configured as, which is very suspicious as that makes a huge difference under concurrent load.
- pornel 1y agoSharing one SQLite connection across the process would necessarily serialize all writes from the process. It won't do anything for contention with external processes, the writes within the process wouldn't be concurrent any more. Basically, it adds its own write lock outside of SQLite, because the pool can implement the lock in a less annoying way.
- bawolff 1y agoI don't understand, all writes to a single sqlite DB are going to be serialized no matter what you do. > Basically, it adds its own write lock outside of SQLite, because the pool can implement the lock in a less annoying way. Less annoying how? What is the difference?
- pornel 1y agoSQLite's lock is blocking, with a timeout that aborts the transaction. An async runtime can have a non-blocking lock that allows other tasks to proceed in the meantime, and is able to wait indefinitely without breaking transactions.
- bawolff 1y agoWhat's the benefit of this over just doing PRAGMA busy_timeout = 0; to make it non-blocking ? After all, as far as i understand, the busy timeout is only going to occur at the beginning of a write transaction, so its not like you have to redo a bunch of queries.
- anacrolix 1y agoWhen you have multiple sqlite connections, any write will flush the caches of other connections. So more connections is not always better.
- ddorian43 1y agoNo synchronous api support ?
- wmanley 1y agoRegarding shared caching: Use `PRAGMA mmap_size` to enable mmap for reading your database. This way SQLite won't add another layer of page caching on top saving RAM and making things faster. SQLite only uses mmap for reads and will continue to write to the database with pwrite(). You must set it to a value higher than the size of your DB. I use: PRAGMA mmap_size = 1099511627776; (1TB)
- rogerbinns 1y agoUnless you compile SQLite yourself, you'll find the maximum mmap size is 2GB. ie even with your pragma above, only the first 2GB of the database are memory mapped. It is defined by the SQLITE_MAX_MMAP_SIZE compile time constant. You can use pragma compile_options to see what the value is. https://sqlite.org/compile.html#max_mmap_size https://sqlite.org/compile.html#max_mmap_size Ubuntu system pragma compile_options: MAX_MMAP_SIZE=0x7fff0000
- otterley 1y agoThat seems like a holdover from 32-bit days. I wonder why this is still the default.
- rogerbinns 1y agoSQLite has 32 bit limits. For example the largest string or blob it can store is 2GB. That could only be addressed by an incompatible file format change. Many APIs also use int in places again making limits be 32 bits, although there are also a smattering of 64 bit APIs. Changing this default requires knowing it is a 64 bit platform when the C preprocessor runs, and would surprise anyone who was ok with the 2GB value. There are two downsides of mmap - I/O errors can't be caught and handled by SQLite code, and buggy stray writes by other code in the process could corrupt the database. It is best practise to directly include the SQLite amalgamation into your own projects which allows you to control version updating, and configuration.
- otterley 1y ago
- deleted 1y ago[deleted]