5 ms·

This 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 w

by d1l 1y ago

This 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 ago
If 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 ago
    Multi-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.
    - brulard 1y ago
      I always had troubles having multiple processes get write access to the sqlite file. For example if I have node.js backend work with that file, and I try to access the file with different tool (adminer for example) it fails (file in use or something like that). Should it work? I don't know if I'm doing something wrong, but this is my experience with multiple projects.
      - Groxx 1y ago
        They can't write concurrently, but generally speaking yes, they can: https://sqlite.org/faq.html#q5 https://sqlite.org/faq.html#q5 Your throughput will be much worse than a single process, but it's possible, and sometimes convenient. Maybe something in your stack is trying to hold open a writable connection in both processes?
      - cyanydeez 1y ago
        PRAGMA journal_mode = WAL;
      - simonw 1y ago
        That is because the default SQLite mode is journal, but for concurrent reads and writes you need to switch it to WAL.
        brulard 1y ago
        I use WAL basically everywhere. I thought that would fix my problem some time ago, but it didn't
        simonw 1y ago
        Are you seeing SQLITE_BUSY errors? Those are a nasty trap. The solution is non-obvious: you have to use BEGIN IMMEDIATE on any transaction that performs at least one write: https://simonwillison.net/tags/sqlite-busy/ https://simonwillison.net/tags/sqlite-busy/
  - naasking 1y ago
    > In which case SQLite is probably the wrong tool for the job Why? If all it's missing is an async connection pool to make it a good tool for more jobs, what's the problem with just creating one?
    - nomel 1y ago
      It's a bit re-inventing the wheel, since solving all the problems that come with network access is precisely why those databases exist, and what they've already done. asyncpg is a nice python library for postgres. I think postgres releasing a nice linkable, "serverless" library would be pretty amazing, to make the need for abusing sqlite like this (I do it too) go away.
      - simonw 1y ago
        https://pglite.dev/ https://pglite.dev/ is a version of that, in 3MB of WASM.
        actionfromafar 1y ago
        That's wild. Not sure if I love it or hate it, but I'm impressed.
      - jitl 1y ago
        Postgres has really not solved problems that come with being a networked server and will collapse under concurrent connections far before you start to feel it with SQLite. 5000 concurrent connections will already start to deadlock your Postgres server; each new connection in Postgres is a new Postgres process and the state for the connection needs to be written to various internal tracking tables. It has a huge amount of overhead; connection pooling in PG is required and often the total system has a rather low fixed limit compared to idk, writing 200 lines of python code or whatever and getting orders of magnitude more connections out of a single machine.
        anarazel 1y ago
        A connection definitely has overhead in PG, but "5000 concurrent connections will already start to deadlock your Postgres server" is bogus. People completely routinely run with more connections. Check the throughput graphs from this blog post from 2020 (for improvements I made to connection scalability): https://techcommunity.microsoft.com/blog/adforpostgresql/improving-postgres-connection-scalability-snapshots/1806462 https://techcommunity.microsoft.com/blog/adforpostgresql/imp... That's for read-mostly work. If you do write very intensely, you're going to see more contention earlier. But that's way way worse with sqlite, due to its single writer model. EDIT: Corrected year.
  - Kranar 1y ago
    SQLite is a great database for organizing data in desktop applications, including both productivity software and even video games. It's certainly not at all unreasonable for those use cases to have files that are in the low GB and I would much rather use SQLite to process that data instead of bundling MySQL or Postgres into my application.
  - jitl 1y ago
    Postgres will shit itself without a connection pooling proxy server like PGBouncer if you try even like 5000 concurrent connections because Postgres spawned a UNIX process per inbound connection. There’s much more overhead per connection in Postgres than SQLite!
    - drzaiusx11 1y ago
      Likewise MySQL will shit itself with just a couple hundred connections unless you have a massive instance size. We use AWS' RDS proxy in front for a similar solution. I've spent way too many hours tuning pool sizes, resolving connection pinning issues...
- d1l 1y ago
  That's even crazier - so you're using asyncio because you have a ton of slow network-bound stuff - but for your database access you are running every sqlite connection in it's own thread and just managing those threads via the asyncio event loop?
  - quietbritishjim 1y ago
    What is crazy about that?
    - lttlrck 1y ago
      Of course I don't know what the parent is thinking, but my thought is: why can't it be entirely event loop driven? What are the threads adding here? (I don't know anything about that project and this isn't meant as a criticism of its design or a challenge - cos I'd probably lose :-) )
      - maxbond 1y ago
        Python's asyncio is single threaded. If you didn't send them into a different thread, the entire event loop would block, and it would degenerate to a fully synchronous single threaded program with additional overhead.
      - mayli 1y ago
        Cause the sqlite-lib that python ships isn't async, and sqlite itself usually doesn't give an async API.
      - eurleif 1y ago
        SQLite doesn't have a separate server process; it does all of the work for queries in your process. So it's intrinsically CPU-heavy, and it needs threads to avoid blocking the event loop. One way to look at is that with a client-server database and an async client library, you have a thread pool in the database server process to do the heavy lifting, and async clients talk to it via TCP. With SQLite, you have that "server" thread pool in the same process instead, and async "clients" talk to it via in-process communication.
charleslmunger 1y ago
A connection pool is absolutely a best practice. One of the biggest benefits is managing a cache of prepared statements, the page cache, etc. Maybe you have temp tables or temp triggers too. Even better is to have separate pools for the writer connection and readers in WAL mode. Then you can cache write relevant statements only once. I am skeptical about a dedicated thread per call because that seems like it would add a bunch of latency.
pjmlp 1y ago
For some strange reason, some people feel like using SQLite all over the place, even when a proper RDMS would be the right answer.
- fidotron 1y ago
  I recently encountered a shared SQLite db being used for inter process pub sub for real time data . . . in a safety critical system. Wrong on so many levels it's frightening.
  - aynyc 1y ago
    Is it? It was designed for damage control system on naval combat vessels. I have no idea what it does on a naval vessel, but I imagine there is certain level of safeness.
  - asa400 1y ago
    How was SQLite used in that scenario? What was the architecture?
- 9rx 1y ago
  It is not that strange when you consider the history. You see, as we started to move away from generated HTML into rich browser applications, we started to need minimal direct DBMS features to serve the rich application. At first, few functions were exposed as "REST APIs". But soon enough those few featured turned into full-on DBMSes, resulting in a DMBS in front of a DBMS. But then people, rightfully, started asking: "Why are we putting a DBMS in front of a DBMS?" The trouble is that nobody took a step back and asked: "Can we simply use the backing DBMS?" Instead, they trudged forward with "Let's get rid of the backing DBMS and embed the database engine into our own DBMS!" And since SQLite is a convenient database engine...
Retr0id 1y ago
My preferred python wrapper for sqlite is apsw. The maintainer gives a good answer here for why not to use an async interface in most cases: https://github.com/rogerbinns/apsw/discussions/456#discussioncomment-6332999 https://github.com/rogerbinns/apsw/discussions/456#discussio... It really depends on what your workload looks like, but I think synchronous will win most of the time.

by d1l 1y ago

simonw 1y ago

If 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 ago
  Multi-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.
  - brulard 1y ago
    I always had troubles having multiple processes get write access to the sqlite file. For example if I have node.js backend work with that file, and I try to access the file with different tool (adminer for example) it fails (file in use or something like that). Should it work? I don't know if I'm doing something wrong, but this is my experience with multiple projects.
    - Groxx 1y ago
      They can't write concurrently, but generally speaking yes, they can: https://sqlite.org/faq.html#q5 https://sqlite.org/faq.html#q5 Your throughput will be much worse than a single process, but it's possible, and sometimes convenient. Maybe something in your stack is trying to hold open a writable connection in both processes?
    - cyanydeez 1y ago
      PRAGMA journal_mode = WAL;
    - simonw 1y ago
      That is because the default SQLite mode is journal, but for concurrent reads and writes you need to switch it to WAL.
      - brulard 1y ago
        I use WAL basically everywhere. I thought that would fix my problem some time ago, but it didn't
        simonw 1y ago
        Are you seeing SQLITE_BUSY errors? Those are a nasty trap. The solution is non-obvious: you have to use BEGIN IMMEDIATE on any transaction that performs at least one write: https://simonwillison.net/tags/sqlite-busy/ https://simonwillison.net/tags/sqlite-busy/
- naasking 1y ago
  > In which case SQLite is probably the wrong tool for the job Why? If all it's missing is an async connection pool to make it a good tool for more jobs, what's the problem with just creating one?
  - nomel 1y ago
    It's a bit re-inventing the wheel, since solving all the problems that come with network access is precisely why those databases exist, and what they've already done. asyncpg is a nice python library for postgres. I think postgres releasing a nice linkable, "serverless" library would be pretty amazing, to make the need for abusing sqlite like this (I do it too) go away.
    - simonw 1y ago
      https://pglite.dev/ https://pglite.dev/ is a version of that, in 3MB of WASM.
      - actionfromafar 1y ago
        That's wild. Not sure if I love it or hate it, but I'm impressed.
    - jitl 1y ago
      Postgres has really not solved problems that come with being a networked server and will collapse under concurrent connections far before you start to feel it with SQLite. 5000 concurrent connections will already start to deadlock your Postgres server; each new connection in Postgres is a new Postgres process and the state for the connection needs to be written to various internal tracking tables. It has a huge amount of overhead; connection pooling in PG is required and often the total system has a rather low fixed limit compared to idk, writing 200 lines of python code or whatever and getting orders of magnitude more connections out of a single machine.
      - anarazel 1y ago
        A connection definitely has overhead in PG, but "5000 concurrent connections will already start to deadlock your Postgres server" is bogus. People completely routinely run with more connections. Check the throughput graphs from this blog post from 2020 (for improvements I made to connection scalability): https://techcommunity.microsoft.com/blog/adforpostgresql/improving-postgres-connection-scalability-snapshots/1806462 https://techcommunity.microsoft.com/blog/adforpostgresql/imp... That's for read-mostly work. If you do write very intensely, you're going to see more contention earlier. But that's way way worse with sqlite, due to its single writer model. EDIT: Corrected year.
- Kranar 1y ago
  SQLite is a great database for organizing data in desktop applications, including both productivity software and even video games. It's certainly not at all unreasonable for those use cases to have files that are in the low GB and I would much rather use SQLite to process that data instead of bundling MySQL or Postgres into my application.
- jitl 1y ago
  Postgres will shit itself without a connection pooling proxy server like PGBouncer if you try even like 5000 concurrent connections because Postgres spawned a UNIX process per inbound connection. There’s much more overhead per connection in Postgres than SQLite!
  - drzaiusx11 1y ago
    Likewise MySQL will shit itself with just a couple hundred connections unless you have a massive instance size. We use AWS' RDS proxy in front for a similar solution. I've spent way too many hours tuning pool sizes, resolving connection pinning issues...
d1l 1y ago
That's even crazier - so you're using asyncio because you have a ton of slow network-bound stuff - but for your database access you are running every sqlite connection in it's own thread and just managing those threads via the asyncio event loop?
- quietbritishjim 1y ago
  What is crazy about that?
  - lttlrck 1y ago
    Of course I don't know what the parent is thinking, but my thought is: why can't it be entirely event loop driven? What are the threads adding here? (I don't know anything about that project and this isn't meant as a criticism of its design or a challenge - cos I'd probably lose :-) )
    - maxbond 1y ago
      Python's asyncio is single threaded. If you didn't send them into a different thread, the entire event loop would block, and it would degenerate to a fully synchronous single threaded program with additional overhead.
    - mayli 1y ago
      Cause the sqlite-lib that python ships isn't async, and sqlite itself usually doesn't give an async API.
    - eurleif 1y ago
      SQLite doesn't have a separate server process; it does all of the work for queries in your process. So it's intrinsically CPU-heavy, and it needs threads to avoid blocking the event loop. One way to look at is that with a client-server database and an async client library, you have a thread pool in the database server process to do the heavy lifting, and async clients talk to it via TCP. With SQLite, you have that "server" thread pool in the same process instead, and async "clients" talk to it via in-process communication.

charleslmunger 1y ago

A connection pool is absolutely a best practice. One of the biggest benefits is managing a cache of prepared statements, the page cache, etc. Maybe you have temp tables or temp triggers too. Even better is to have separate pools for the writer connection and readers in WAL mode. Then you can cache write relevant statements only once. I am skeptical about a dedicated thread per call because that seems like it would add a bunch of latency.

pjmlp 1y ago

For some strange reason, some people feel like using SQLite all over the place, even when a proper RDMS would be the right answer.

fidotron 1y ago
I recently encountered a shared SQLite db being used for inter process pub sub for real time data . . . in a safety critical system. Wrong on so many levels it's frightening.
- aynyc 1y ago
  Is it? It was designed for damage control system on naval combat vessels. I have no idea what it does on a naval vessel, but I imagine there is certain level of safeness.
- asa400 1y ago
  How was SQLite used in that scenario? What was the architecture?
9rx 1y ago
It is not that strange when you consider the history. You see, as we started to move away from generated HTML into rich browser applications, we started to need minimal direct DBMS features to serve the rich application. At first, few functions were exposed as "REST APIs". But soon enough those few featured turned into full-on DBMSes, resulting in a DMBS in front of a DBMS. But then people, rightfully, started asking: "Why are we putting a DBMS in front of a DBMS?" The trouble is that nobody took a step back and asked: "Can we simply use the backing DBMS?" Instead, they trudged forward with "Let's get rid of the backing DBMS and embed the database engine into our own DBMS!" And since SQLite is a convenient database engine...

Retr0id 1y ago

My preferred python wrapper for sqlite is apsw. The maintainer gives a good answer here for why not to use an async interface in most cases: https://github.com/rogerbinns/apsw/discussions/456#discussioncomment-6332999 https://github.com/rogerbinns/apsw/discussions/456#discussio... It really depends on what your workload looks like, but I think synchronous will win most of the time.