6 ms·

I run the backend/website of my side business on sqlite. It is one of the best technology decisions I have made. It performs reasonably, is super straightforw

by xiaomai 10y ago

I run the backend/website of my side business on sqlite. It is one of the best technology decisions I have made. It performs reasonably, is super straightforward (at my day job we have a team of postgres people to keep our dbs running smoothly, but for my little side business I don't have those resources); backups are dead simple. I love sqlite.

chii 10y ago
How do you handle concurrent access to your db?
- Scaevolus 10y ago
  Most applications don't actually need concurrent access. SQLite handles concurrent reads without any issues, with writes requiring exclusive locks. As long as your queries are fast and your write load is minimal, you won't really have any problems.
  - teraflop 10y ago
    What you describe is the way SQLite worked originally. With the newer WAL mode, things are slightly better -- you can still only have one active write transaction at a time, but writers no longer block readers.
    - Scaevolus 10y ago
      Good point! I find WAL mode especially useful when long write transactions would starve reads.
    - wila 10y ago
      So I read about WAL mode down here and cheered because it sounded like it would solve the occasional "database is locked" error that the app I am working on is bumping into. The database is only opened by 2 users on the same machine. One is a normal user, the other one is root for a daemon process. That by itself might be an uncommon scenario. So I tried it out this morning and found that any writes are invisible unless I restart the app to close the database. For my use case that isn't an improvement as writes made by either user should be visible by the other user. Even tried it with setting read_uncommitted to true, but that did not help either. Of course it is possible I am still doing something wrong, but at this moment it doesn't look like the WAL journal mode is an option for my app. A pity as I expected -without WAL- to be able to read when another process is writing, well just a delayed read would be fine, but instead there's a -database is locked- error that pops up to the user.
      - domador 10y ago
        Sorry to ask an obvious question, but are both users committing their transactions? Does the other user requery after a transaction?
        wila 10y ago
        Yeah, they both can write, although almost all of the writes are done by the daemon process and the normal user (the GUI process) reads and processes the results. The 'database is locked' problem seemed to happen most while the daemon user is writing and the normal user is reading. For the moment I added a patch to my apps whereby the applications handle the locking by itself at a slightly higher level as I got a bit tired of the problem. This is done via a separate lock file that is opened exclusively before any write action and closed after the write. By doing that I can simply delay the reads for a bit when the GUI process tests to open the lock file and that appears to have cured most problems. It's a tiny bit more advanced as the above, but that's basically it and it appears to have cured most issues. edit: might have misread your question, was it about the WAL journal mode? Yes the processes do commit the transactions they write. I need the results immediately, not after sqlite decides to process the WAL journal.
        domador 10y ago
        No, the question was not about WAL journal mode, but about running the SQLite COMMIT command. You may wish to take a look at the last section (Transaction Control At The SQL Level) of the following page. Maybe it's relevant to your case: https://www.sqlite.org/lockingv3.html https://www.sqlite.org/lockingv3.html
        wila 10y ago
        Thanks, I had read that part before though. When staying in normal journal mode the app sees the data just fine and the data is committed directly in that case. Updates/Deletes are all pretty much instant and any queries results are correct. There might be an issue with the database drivers I depend on (FireDAC) in that layer I even go as far as closing the tables on each query/update after a commit. The problem with normal journal mode is the lock error popping up. When I switch to WAL journal mode the data no longer appears to be written directly even when turning autocommit back on. So while WAL mode appears to fix the lock issue, the data only gets committed on closing the database connection. As a result the GUI process can't interact with the daemon process anymore as it only sees old data. Opening and closing the database on each insert/update/delete to force the data to be written simply isn't an option.
  - jack9 10y ago
    > Most applications don't actually need concurrent access That's an interesting claim. I would rephrase that to, "Are their more http calls that use concurrent connections to a DB, or standalone applications that do not?" I would wager the former.
    - qwertyuiop924 10y ago
      I think what GPP meant is that most apps don't need concurrent writes.
    - the_duke 10y ago
      SQLite is not such a great choice for the server side. SQLite typically get's used on the client side portion, either as a "caching db" for offline work, or for client side only programs without a server backend.
    - emn13 10y ago
      The sqlite website runs on an sqlite db. Even on most websites, I suspect the need for concurrent, long-lived write transactions is much rarer than people assume. If your write transactions are short-lived, then sequential execution is a reasonable approximation of (slow) concurrency, at which point it's a question of load whether that's good enough. But the window in which it's not good enough is very slim - hardware simply isn't all that concurrent in the first place, and as you scale, some sharding strategy is required anyhow. So the more plausible limitation is long-lived write transactions; e.g. where a write cannot be committed until after some other confirmation occurs, possibly over the network. That simply won't work well at all in sqlite - not that it's a great strategy to use on other DBs...
      - icebraining 10y ago
        The sqlite website runs on an sqlite db. Yeah, but the sqlite website doesn't need a db at all.
        emn13 10y ago
        Well, "need"... To quote the sqlite website itself: > The SQLite website (https://www.sqlite.org/ https://www.sqlite.org/) uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Each dynamic page does roughly 200 SQL statements. This setup runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time. I think its fair to assume that the sqlite site could be redesigned to meet most of its functionality as a largely static site, but that would come at a loss of functionality. And obviously it's a form of dogfooding, but that's not objectionable, right?
- AstroJetson 10y ago
  Read and understand this document: https://www.sqlite.org/lockingv3.html It is the inside the engine on how to do things. There are exact steps that need to be done the way the document reads to make it happen. There is a also big section on how to corrupt the database. It's a heads up that if you decide to do shortcuts there will not be a happy ending. A little more complicated doing concurrent use than with something like MySQL, but there is much more engine on the MySQL side. If multiple concurrent users with high transaction levels, SQLite may not be your best first choice.
- xiaomai 10y ago
  The WAL (https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html) facilitates concurrency.
- edwinyzh 10y ago
  We use mORMot(https://github.com/synopse/mORMot https://github.com/synopse/mORMot) to achieve concurrent access. On windows it utilizes the http.sys engine (which is the same used by IIS the Microsoft web server) With FreePascal it also works on Linux .
- beagle3 10y ago
  SQLite has always supported multiple readers, which is the most common concurrent access pattern. Before the WAL (Write-ahead log) was introduced, a writer would block readers (and vice versa), but with a WAL, a single writer and multiple readers do not block each others. SQLite does not have multi writer concurrency (usually MVCC as in Oracle/MySQL/PostgreSQL or optimstic transactions like Backplane). If you need those, SQLite is not for you.
nilved 10y ago
This used to be how I felt, since the performance criticism of SQLite is vastly overblown. However, the safety criticism of a dynamically typed database is vastly underblown. Since it completes with fopen(), you get about as much structure and validity.
- jstimpfle 10y ago
  I'm currently developping a text format called WSL[1]. By nature text files don't have indices, but it is strongly typed, supports standard relational integrity constraints, and indices can be automatically created when reading the file. There is a currently only a simplistic python library which reads databases at about 1MB/s. On the plus side it's dead simple to use, only a single library call to parse a file as schema, tables, and indices. There is also a C library in development which lexes at about 300-600 MB/s in a single thread (depending on how many columns are actually needed and thus have to be written to per-column lexem buffers) and which I hope will have a release next month. [1] http://jstimpfle.de/projects/wsl/main.html http://jstimpfle.de/projects/wsl/main.html
- qwertyuiop924 10y ago
  What's the safety issue? Will your data corrupt, or are your types just not 100% guaranteed? Because I can live with the ladder: weak types suck in programming languages, but are okay in DBs, and the types get verified multiple times on their way in and out of the DB in most systems. Besides, it won't mangle your data. Unlike some DBs that I could name...
  - nilved 10y ago
    I don't think it would mangle your data directly, but it could lead to incorrect results since there is a degree of mystery from query to query. You should definitely base a conclusion on their words and not mine. https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
    - qwertyuiop924 10y ago
      Thanks for the link. Yeah, it's as I remember: type affinities will convert to their type if possible, and if not... well, you get out what you put in. The reason most people don't complain about this is that it's a far from common issue to totally miswrite your SQL statements so badly that you wind up mixing up columns. And when you do, it's usually detected pretty fast.

by xiaomai 10y ago

chii 10y ago

How do you handle concurrent access to your db?

Scaevolus 10y ago
Most applications don't actually need concurrent access. SQLite handles concurrent reads without any issues, with writes requiring exclusive locks. As long as your queries are fast and your write load is minimal, you won't really have any problems.
- teraflop 10y ago
  What you describe is the way SQLite worked originally. With the newer WAL mode, things are slightly better -- you can still only have one active write transaction at a time, but writers no longer block readers.
  - Scaevolus 10y ago
    Good point! I find WAL mode especially useful when long write transactions would starve reads.
  - wila 10y ago
    So I read about WAL mode down here and cheered because it sounded like it would solve the occasional "database is locked" error that the app I am working on is bumping into. The database is only opened by 2 users on the same machine. One is a normal user, the other one is root for a daemon process. That by itself might be an uncommon scenario. So I tried it out this morning and found that any writes are invisible unless I restart the app to close the database. For my use case that isn't an improvement as writes made by either user should be visible by the other user. Even tried it with setting read_uncommitted to true, but that did not help either. Of course it is possible I am still doing something wrong, but at this moment it doesn't look like the WAL journal mode is an option for my app. A pity as I expected -without WAL- to be able to read when another process is writing, well just a delayed read would be fine, but instead there's a -database is locked- error that pops up to the user.
    - domador 10y ago
      Sorry to ask an obvious question, but are both users committing their transactions? Does the other user requery after a transaction?
      - wila 10y ago
        Yeah, they both can write, although almost all of the writes are done by the daemon process and the normal user (the GUI process) reads and processes the results. The 'database is locked' problem seemed to happen most while the daemon user is writing and the normal user is reading. For the moment I added a patch to my apps whereby the applications handle the locking by itself at a slightly higher level as I got a bit tired of the problem. This is done via a separate lock file that is opened exclusively before any write action and closed after the write. By doing that I can simply delay the reads for a bit when the GUI process tests to open the lock file and that appears to have cured most problems. It's a tiny bit more advanced as the above, but that's basically it and it appears to have cured most issues. edit: might have misread your question, was it about the WAL journal mode? Yes the processes do commit the transactions they write. I need the results immediately, not after sqlite decides to process the WAL journal.
        domador 10y ago
        No, the question was not about WAL journal mode, but about running the SQLite COMMIT command. You may wish to take a look at the last section (Transaction Control At The SQL Level) of the following page. Maybe it's relevant to your case: https://www.sqlite.org/lockingv3.html https://www.sqlite.org/lockingv3.html
        wila 10y ago
        Thanks, I had read that part before though. When staying in normal journal mode the app sees the data just fine and the data is committed directly in that case. Updates/Deletes are all pretty much instant and any queries results are correct. There might be an issue with the database drivers I depend on (FireDAC) in that layer I even go as far as closing the tables on each query/update after a commit. The problem with normal journal mode is the lock error popping up. When I switch to WAL journal mode the data no longer appears to be written directly even when turning autocommit back on. So while WAL mode appears to fix the lock issue, the data only gets committed on closing the database connection. As a result the GUI process can't interact with the daemon process anymore as it only sees old data. Opening and closing the database on each insert/update/delete to force the data to be written simply isn't an option.
- jack9 10y ago
  > Most applications don't actually need concurrent access That's an interesting claim. I would rephrase that to, "Are their more http calls that use concurrent connections to a DB, or standalone applications that do not?" I would wager the former.
  - qwertyuiop924 10y ago
    I think what GPP meant is that most apps don't need concurrent writes.
  - the_duke 10y ago
    SQLite is not such a great choice for the server side. SQLite typically get's used on the client side portion, either as a "caching db" for offline work, or for client side only programs without a server backend.
  - emn13 10y ago
    The sqlite website runs on an sqlite db. Even on most websites, I suspect the need for concurrent, long-lived write transactions is much rarer than people assume. If your write transactions are short-lived, then sequential execution is a reasonable approximation of (slow) concurrency, at which point it's a question of load whether that's good enough. But the window in which it's not good enough is very slim - hardware simply isn't all that concurrent in the first place, and as you scale, some sharding strategy is required anyhow. So the more plausible limitation is long-lived write transactions; e.g. where a write cannot be committed until after some other confirmation occurs, possibly over the network. That simply won't work well at all in sqlite - not that it's a great strategy to use on other DBs...
    - icebraining 10y ago
      The sqlite website runs on an sqlite db. Yeah, but the sqlite website doesn't need a db at all.
      - emn13 10y ago
        Well, "need"... To quote the sqlite website itself: > The SQLite website (https://www.sqlite.org/ https://www.sqlite.org/) uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Each dynamic page does roughly 200 SQL statements. This setup runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time. I think its fair to assume that the sqlite site could be redesigned to meet most of its functionality as a largely static site, but that would come at a loss of functionality. And obviously it's a form of dogfooding, but that's not objectionable, right?
AstroJetson 10y ago
Read and understand this document: https://www.sqlite.org/lockingv3.html It is the inside the engine on how to do things. There are exact steps that need to be done the way the document reads to make it happen. There is a also big section on how to corrupt the database. It's a heads up that if you decide to do shortcuts there will not be a happy ending. A little more complicated doing concurrent use than with something like MySQL, but there is much more engine on the MySQL side. If multiple concurrent users with high transaction levels, SQLite may not be your best first choice.
xiaomai 10y ago
The WAL (https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html) facilitates concurrency.
edwinyzh 10y ago
We use mORMot(https://github.com/synopse/mORMot https://github.com/synopse/mORMot) to achieve concurrent access. On windows it utilizes the http.sys engine (which is the same used by IIS the Microsoft web server) With FreePascal it also works on Linux .
beagle3 10y ago
SQLite has always supported multiple readers, which is the most common concurrent access pattern. Before the WAL (Write-ahead log) was introduced, a writer would block readers (and vice versa), but with a WAL, a single writer and multiple readers do not block each others. SQLite does not have multi writer concurrency (usually MVCC as in Oracle/MySQL/PostgreSQL or optimstic transactions like Backplane). If you need those, SQLite is not for you.

nilved 10y ago

This used to be how I felt, since the performance criticism of SQLite is vastly overblown. However, the safety criticism of a dynamically typed database is vastly underblown. Since it completes with fopen(), you get about as much structure and validity.

jstimpfle 10y ago
I'm currently developping a text format called WSL[1]. By nature text files don't have indices, but it is strongly typed, supports standard relational integrity constraints, and indices can be automatically created when reading the file. There is a currently only a simplistic python library which reads databases at about 1MB/s. On the plus side it's dead simple to use, only a single library call to parse a file as schema, tables, and indices. There is also a C library in development which lexes at about 300-600 MB/s in a single thread (depending on how many columns are actually needed and thus have to be written to per-column lexem buffers) and which I hope will have a release next month. [1] http://jstimpfle.de/projects/wsl/main.html http://jstimpfle.de/projects/wsl/main.html
qwertyuiop924 10y ago
What's the safety issue? Will your data corrupt, or are your types just not 100% guaranteed? Because I can live with the ladder: weak types suck in programming languages, but are okay in DBs, and the types get verified multiple times on their way in and out of the DB in most systems. Besides, it won't mangle your data. Unlike some DBs that I could name...
- nilved 10y ago
  I don't think it would mangle your data directly, but it could lead to incorrect results since there is a degree of mystery from query to query. You should definitely base a conclusion on their words and not mine. https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
  - qwertyuiop924 10y ago
    Thanks for the link. Yeah, it's as I remember: type affinities will convert to their type if possible, and if not... well, you get out what you put in. The reason most people don't complain about this is that it's a far from common issue to totally miswrite your SQL statements so badly that you wind up mixing up columns. And when you do, it's usually detected pretty fast.