5 ms·

> doesn't support multi-core Can you expand on that? I thought it supported multiple readers, but only a single writer. They do all have to be on the same host

> doesn't support multi-core Can you expand on that? I thought it supported multiple readers, but only a single writer. They do all have to be on the same host, though. Since you mentioned Django - I think there are some complexities where the Python driver can't really do concurrent access if using threads instead of processes, but this is due to Python/GIL limitations, not sqlite limitations.

aynyc 4y ago
sqlite3 is a single core application, so even if you have multiple cores on a box, sqlite3 isn't going to take advantage of that. If you want sqlite3 to go faster, you need better single core box. That being said, I do believe sqlite3 allow multiple processes to read the same database file. Our django app sits behind Gunicorn that will spin up multiple django instances, as long as Sqlite3 has WAL mode on, concurrent access (read) isn't a problem. For write, we basically use a lock.
- markusw 4y ago
  When you embed it in Go, you can have concurrent reads. Each HTTP handler request gets a separate goroutine, so you can definitely take advantage of multiple cores.
- ketralnis 4y ago
  This is confusingly worded but for onlookers: sqlite as you think of it isn't an application at all, it's a C library. There does exist a binary called sqlite3 which is an interface to that library, and that binary is (mostly, though not exclusively) single-threaded. But unless you're using that specific tool, and you're probably not unless you yourself are typing "sqlite3" into a bash shell, you're using the library rather than this query tool. And that library interacts in a similar way to a socket connect or a fopen call. You can have multiple connections open to the same sqlite database, and those connections can operate concurrently (subject to various limitations, but "you need better single core box" is not the summary)
stonemetal12 4y ago
It is even a little better than that these days. Beginning a transaction normally locks the whole database, but with Wal mode they have added a Begin Concurrent command so there can be multiple transactions going at once. However when you get to the commit it still requires the exclusive lock. I bet after a few years even that restriction will be gone. https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi...
- SPBS 4y ago
  https://sqlite.org/forum/info/2ad28cb1e0356816e7b0bd2ab458f75daff18e0b346220769febf541dc90e0ef https://sqlite.org/forum/info/2ad28cb1e0356816e7b0bd2ab458f7... I don't think BEGIN CONCURRENT is out yet. AFAIK it's only available if you build the experimental wal2 branch yourself.
  - stonemetal12 4y ago
    I guess I miss read the branch list, I thought the begin-concurrent-3.39 branch said it had merged to trunk but that is the line above it.
ketralnis 4y ago
> Python driver can't really do concurrent access if using threads instead of processes, but this is due to Python/GIL limitations Confusingly you can't run 2 lines of Python code at the same time, but you can run 2 SQL queries (sqlite, remote server, etc) at the same time. Python threads are true pthreads, they just need to hold the GIL while running Python code. They release it when running C code or doing I/O. So you can have 2 queries running at the same time no problem, but you'll only get a single core of compute processing their results.

aynyc 4y ago

sqlite3 is a single core application, so even if you have multiple cores on a box, sqlite3 isn't going to take advantage of that. If you want sqlite3 to go faster, you need better single core box. That being said, I do believe sqlite3 allow multiple processes to read the same database file. Our django app sits behind Gunicorn that will spin up multiple django instances, as long as Sqlite3 has WAL mode on, concurrent access (read) isn't a problem. For write, we basically use a lock.

markusw 4y ago
When you embed it in Go, you can have concurrent reads. Each HTTP handler request gets a separate goroutine, so you can definitely take advantage of multiple cores.
ketralnis 4y ago
This is confusingly worded but for onlookers: sqlite as you think of it isn't an application at all, it's a C library. There does exist a binary called sqlite3 which is an interface to that library, and that binary is (mostly, though not exclusively) single-threaded. But unless you're using that specific tool, and you're probably not unless you yourself are typing "sqlite3" into a bash shell, you're using the library rather than this query tool. And that library interacts in a similar way to a socket connect or a fopen call. You can have multiple connections open to the same sqlite database, and those connections can operate concurrently (subject to various limitations, but "you need better single core box" is not the summary)

stonemetal12 4y ago

It is even a little better than that these days. Beginning a transaction normally locks the whole database, but with Wal mode they have added a Begin Concurrent command so there can be multiple transactions going at once. However when you get to the commit it still requires the exclusive lock. I bet after a few years even that restriction will be gone. https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi...

SPBS 4y ago
https://sqlite.org/forum/info/2ad28cb1e0356816e7b0bd2ab458f75daff18e0b346220769febf541dc90e0ef https://sqlite.org/forum/info/2ad28cb1e0356816e7b0bd2ab458f7... I don't think BEGIN CONCURRENT is out yet. AFAIK it's only available if you build the experimental wal2 branch yourself.
- stonemetal12 4y ago
  I guess I miss read the branch list, I thought the begin-concurrent-3.39 branch said it had merged to trunk but that is the line above it.

ketralnis 4y ago

> Python driver can't really do concurrent access if using threads instead of processes, but this is due to Python/GIL limitations Confusingly you can't run 2 lines of Python code at the same time, but you can run 2 SQL queries (sqlite, remote server, etc) at the same time. Python threads are true pthreads, they just need to hold the GIL while running Python code. They release it when running C code or doing I/O. So you can have 2 queries running at the same time no problem, but you'll only get a single core of compute processing their results.