5 ms·

I want to warn that one huge issue with SQLite in WAL mode is that if your site gets a high load, the WAL file will grow unboundedly. I ran a stress test of 14k

by kgeist 2y ago

I want to warn that one huge issue with SQLite in WAL mode is that if your site gets a high load, the WAL file will grow unboundedly. I ran a stress test of 14k RPS (that's the maximum my PC can pull off in my Go application, but it probably can happen with a more modest RPS) and the WAL file quickly exploded to tens of gigabytes, which can render your machine inoperable (it almost broke my PC). The default checkpointer can't properly function if the DB is continuously written to, due to the internal limitations of SQLite. I managed to solve it by running a separate goroutine (thread) which monitors the WAL size on disk every second: if it goes above the target size of 8 MB, my framework initiates the "slow down" mode where all reads and writes are slowed down by artificially calling sleep(), starting with 16ms and gradually increasing the sleep time according to a few heuristics. This allows the application to have small time gaps where no reads or writes happen and the checkpointer can actually proceed (the goroutine activates it manually). The slow down mode is deactivated when the WAL size is within the target size again. I think SQLite in WAL mode is not really fit for production without this kind of hack.

anze3db 2y ago
I've heard this is a potential issue but I've never encountered it. Do you know about the experimental WAL2 branch[0] that splits the WAL file into two to circumvent this problem? You'd have to compile SQLite yourself from the WAL2 branch to try it out, but it might be worth it at your scale. Python is much slower than Go, so I don't think we could get 14k RPS as easily with Django, but I do have to see if I can reproduce the problem in Django. Topic for a future blog post! Thanks for sharing this, I really appreciate knowing where the limits of SQLite are! [0] https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md
baq 2y ago
I love this story because it shows you really should read some docs about your storage layer before you jump in head first and just assume it’ll work forever and/or gracefully handle any load including overload. Postgres is the same, it’ll work until it won’t, in some case it’ll break in a way you can’t recover from without stopping production for a long time. (You can guess how I know - you’re right, I didn’t read the relevant part of the manual.) Read your database’s manual, people! Even just going through the table of contents will put you in the top 20%.
- belter 2y ago
  > you can’t recover from without stopping production for a long time. Should that not be addressed at the level of Architectural design of your System? Everything fails all the time...bla bla....and all that?
  - baq 2y ago
    If the system is small enough for a single postgres (which let's be honest 99% of them are) and short maintenance downtimes are easily tolerated by customers it's easy to forget about some failure modes and especially if you haven't been on the receiving end of such an incident. An experienced DBA would also notice the problem if only because they've been resolving a few of them in their professional past (hence 'experienced').
- stavros 2y ago
  Well, maybe the database should have more suitable defaults? Arguably, slowing down a bit once when the WAL grows beyond X MB to clean it up is better behavior than having it grow unboundedly, but maybe that's not possible.
  - kmac_ 2y ago
    There is no one-size-fits-all solution in the SQL world, and using SQLite in such a scenario is not a good idea. SQLite is an embedded database for embedded use. Even PostgreSQL is not universal, as many people assume. For use cases like analytics, there are many better-suited database engines.
    - stavros 2y ago
      Sure, but there are one-size-fits-95% solutions, and they're better than one-size-fits-90% solutions.
      - kmac_ 2y ago
        Absolutely agree. As most projects are small or medium in size, PostgreSQL is a good default choice. Problems arise when the problem does not fit the solution or tool. For example, using SQLite for high-volume transactions and then complaining that it doesn't work.
stefanos82 2y ago
I think there's a workaround if I understood the official documentation correctly: https://www.sqlite.org/wal.html#avoiding_excessively_large_wal_files https://www.sqlite.org/wal.html#avoiding_excessively_large_w...
- kgeist 2y ago
  Yeah, when implementing this hack, I took the advice from this exact page: >This scenario can be avoided by ensuring that there are "reader gaps": times when no processes are reading from the database and that checkpoints are attempted during those times I create reader/writer gaps by calling sleep() (before opening transactions) and activate the checkpointer manually in the checkpointer goroutine.
sgbeal 2y ago
> I ran a stress test of 14k RPS My built-in Devil's Advocate feels compelled to ask: do you have any reason to believe that your production site would get even a small fraction (single-digit percentage) of that traffic? As a point of comparison, i have it on good authority that sqlite3's own sites typically get far less than 0.1% of that traffic. Two of those sites (sqlite.org/src and sqlite.org/forum) are database-driven applications.
- kgeist 2y ago
  Sure I don't expect my application to have 14k RPS all the time. However, some spikes can happen, and it can leave you with a large WAL file which doesn't shrink by itself by default. 14k RPS is the maximum my PC could pull off, I think it can happen with a more modest RPS. I ran the stress test with 12 parallel threads, so technically it can happen with just 12 RPS too, if your read or write transactions take a lot of time each (say, it has to read a large table, or you forgot to add indexes, or write transactions update many rows). In fact, a single open read transaction already can trigger checkpoint starvation. Also, it's not only about HTTP requests, my application is event-driven so a few event handlers processing events in the background can easily trigger it, too. A system can accidentally produce many events without having a lot of user requests (say, you forgot to batch events and generate N separate events for N items).
- anze3db 2y ago
  While this blog post was at the #2 spot on HN it was getting 0.138 rps, so yeah 14k RPS is A LOT! That said it's still good to know when your tools will break! As far as I understand it's not really about RPS but more about constant write operations preventing the WAL file from being flushed. You can reproduce this issue with a lot fewer RPS.
brigadier132 2y ago
From what I remember reading 1k rps is the recommended limit for sqlite right?
tiffanyh 2y ago
Not an issue for WAL2 mode, which mitigates this issue. —- > In wal2 mode, wal files do not grow indefinitely https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md
kissgyorgy 2y ago
Isn't journal_size_limit is exactly for this? From the doc: "Each time a transaction is committed or a WAL file resets, SQLite compares the size of the rollback journal file or WAL file left in the file-system to the size limit set by this pragma and if the journal or WAL file is larger it is truncated to the limit."
- anze3db 2y ago
  From what I understand, you still need the experimental WAL2 mode[0] to not have this problem. [0] https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md
- kgeist 2y ago
  If there's checkpoint starvation, the WAL file cannot be reset, and it will still grow indefinitely even with this setting.
kissgyorgy 2y ago
I would argue that if you have this problem, SQLite is not a good database for your use-case.
jfloresremar 2y ago
maybe this could help: https://turso.tech/blog/something-you-probably-want-to-know-about-if-youre-using-sqlite-in-golang-72547ad625f1 https://turso.tech/blog/something-you-probably-want-to-know-...

by kgeist 2y ago

anze3db 2y ago

I've heard this is a potential issue but I've never encountered it. Do you know about the experimental WAL2 branch[0] that splits the WAL file into two to circumvent this problem? You'd have to compile SQLite yourself from the WAL2 branch to try it out, but it might be worth it at your scale. Python is much slower than Go, so I don't think we could get 14k RPS as easily with Django, but I do have to see if I can reproduce the problem in Django. Topic for a future blog post! Thanks for sharing this, I really appreciate knowing where the limits of SQLite are! [0] https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md

baq 2y ago

I love this story because it shows you really should read some docs about your storage layer before you jump in head first and just assume it’ll work forever and/or gracefully handle any load including overload. Postgres is the same, it’ll work until it won’t, in some case it’ll break in a way you can’t recover from without stopping production for a long time. (You can guess how I know - you’re right, I didn’t read the relevant part of the manual.) Read your database’s manual, people! Even just going through the table of contents will put you in the top 20%.

belter 2y ago
> you can’t recover from without stopping production for a long time. Should that not be addressed at the level of Architectural design of your System? Everything fails all the time...bla bla....and all that?
- baq 2y ago
  If the system is small enough for a single postgres (which let's be honest 99% of them are) and short maintenance downtimes are easily tolerated by customers it's easy to forget about some failure modes and especially if you haven't been on the receiving end of such an incident. An experienced DBA would also notice the problem if only because they've been resolving a few of them in their professional past (hence 'experienced').
stavros 2y ago
Well, maybe the database should have more suitable defaults? Arguably, slowing down a bit once when the WAL grows beyond X MB to clean it up is better behavior than having it grow unboundedly, but maybe that's not possible.
- kmac_ 2y ago
  There is no one-size-fits-all solution in the SQL world, and using SQLite in such a scenario is not a good idea. SQLite is an embedded database for embedded use. Even PostgreSQL is not universal, as many people assume. For use cases like analytics, there are many better-suited database engines.
  - stavros 2y ago
    Sure, but there are one-size-fits-95% solutions, and they're better than one-size-fits-90% solutions.
    - kmac_ 2y ago
      Absolutely agree. As most projects are small or medium in size, PostgreSQL is a good default choice. Problems arise when the problem does not fit the solution or tool. For example, using SQLite for high-volume transactions and then complaining that it doesn't work.

stefanos82 2y ago

I think there's a workaround if I understood the official documentation correctly: https://www.sqlite.org/wal.html#avoiding_excessively_large_wal_files https://www.sqlite.org/wal.html#avoiding_excessively_large_w...

kgeist 2y ago
Yeah, when implementing this hack, I took the advice from this exact page: >This scenario can be avoided by ensuring that there are "reader gaps": times when no processes are reading from the database and that checkpoints are attempted during those times I create reader/writer gaps by calling sleep() (before opening transactions) and activate the checkpointer manually in the checkpointer goroutine.

sgbeal 2y ago

> I ran a stress test of 14k RPS My built-in Devil's Advocate feels compelled to ask: do you have any reason to believe that your production site would get even a small fraction (single-digit percentage) of that traffic? As a point of comparison, i have it on good authority that sqlite3's own sites typically get far less than 0.1% of that traffic. Two of those sites (sqlite.org/src and sqlite.org/forum) are database-driven applications.

kgeist 2y ago
Sure I don't expect my application to have 14k RPS all the time. However, some spikes can happen, and it can leave you with a large WAL file which doesn't shrink by itself by default. 14k RPS is the maximum my PC could pull off, I think it can happen with a more modest RPS. I ran the stress test with 12 parallel threads, so technically it can happen with just 12 RPS too, if your read or write transactions take a lot of time each (say, it has to read a large table, or you forgot to add indexes, or write transactions update many rows). In fact, a single open read transaction already can trigger checkpoint starvation. Also, it's not only about HTTP requests, my application is event-driven so a few event handlers processing events in the background can easily trigger it, too. A system can accidentally produce many events without having a lot of user requests (say, you forgot to batch events and generate N separate events for N items).
anze3db 2y ago
While this blog post was at the #2 spot on HN it was getting 0.138 rps, so yeah 14k RPS is A LOT! That said it's still good to know when your tools will break! As far as I understand it's not really about RPS but more about constant write operations preventing the WAL file from being flushed. You can reproduce this issue with a lot fewer RPS.

brigadier132 2y ago

From what I remember reading 1k rps is the recommended limit for sqlite right?

tiffanyh 2y ago

Not an issue for WAL2 mode, which mitigates this issue. —- > In wal2 mode, wal files do not grow indefinitely https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md

kissgyorgy 2y ago

Isn't journal_size_limit is exactly for this? From the doc: "Each time a transaction is committed or a WAL file resets, SQLite compares the size of the rollback journal file or WAL file left in the file-system to the size limit set by this pragma and if the journal or WAL file is larger it is truncated to the limit."

anze3db 2y ago
From what I understand, you still need the experimental WAL2 mode[0] to not have this problem. [0] https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md
kgeist 2y ago
If there's checkpoint starvation, the WAL file cannot be reset, and it will still grow indefinitely even with this setting.

kissgyorgy 2y ago

I would argue that if you have this problem, SQLite is not a good database for your use-case.

jfloresremar 2y ago

maybe this could help: https://turso.tech/blog/something-you-probably-want-to-know-about-if-youre-using-sqlite-in-golang-72547ad625f1 https://turso.tech/blog/something-you-probably-want-to-know-...