8 ms·

SQLite in production with WAL

83 points by manjana 5y ago

pstuart 5y ago
I love me some SQLite but that post was worthless. Edit: flagged for using "worthless"? It tells us that SQLite has WAL mode and that should be enabled if using it in production. New version: I love me some SQLite but that article merely points out that WAL mode is a thing and should be used. Is that really news or valuable to this community?
- deleted 5y ago
  [deleted]
- joncfoo 5y ago
  Please be nice.
  - pstuart 5y ago
    Sorry, wasn't meaning to be rude but the title of the post implied there was some insights being shared. Instead, it was telling us that SQLite has WAL mode, and that it should be used. There was nothing there that actually talked about issues or lessons from working with it. What did that post tell us about that this one doesn't: https://sqlite.org/wal.html https://sqlite.org/wal.html Edit: instead of drive-by downvotes, what about giving me a counter example that shows that the article is worthy of being on the front page of HN?
    - acchow 5y ago
      I see your account is from 2007, so I'm a bit surprised you're not familiar with the community guidelines. I'll link them here for you: https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html I'm copying out 2 points I think are most relevant here: - Be kind. Don't be snarky. Have curious conversation; don't cross-examine. Please don't fulminate. Please don't sneer, including at the rest of the community. - Please don't post shallow dismissals, especially of other people's work. A good critical comment teaches us something.
      - pstuart 5y ago
        I asked for counter-proof of my dismissal and reasoning, and all you offer up is copy pasted points that aren't applicable. Yes my dismissal was shallow, as was the article in question. Here's a more verbose version that should satisfy you? The article speaks of using SQLite in production but only mentions the existence of WAL-mode and suggests its use. That is general (not production specific) advice that is well known in the community. While the promotion of SQLite is a good thing, I found this article wanting in delivering on what the title suggested.
    - deleted 5y ago
      [deleted]
RcouF1uZ4gsC 5y ago
And if you are looking for a simple way to make sure your sqlite database is replicated, check out litestream https://litestream.io/ https://litestream.io/ It continuously streams your changes to an an S3 compatible service.
SMFloris 5y ago
So 400 writes per second is not exactly what I would think about when thinking about a "Production" environment.
- zie 5y ago
  For Google sized, obviously not. But lots and lots of production stuff will likely live quite comfortably in 400 writes per second.
  - dstick 5y ago
    True, until it isn’t? It’s quite a big gamble and a large lock-in. What would the benefit be for choosing Sqlite over MySql?
    - gpapilion 5y ago
      Easy to scale distribution of files vs having to scale a db server.
    - yjftsjthsd-h 5y ago
      Does mysql still risk eating your data? https://stackoverflow.com/questions/6158568/mysql-myisam-data-loss-possibilities https://stackoverflow.com/questions/6158568/mysql-myisam-dat...
      - kbenson 5y ago
        That's MyISAM. Nobody uses that engine for production without specifically choosing it and hopefully for a good reason. InnoDB has been the default storage engine since 5.5, released in late 2010, so for over a decade.
        yjftsjthsd-h 5y ago
        And to be explicit: It looks like InnoDB is ACID compliant and crash consistent, so indeed this criticism appears out of date. Thanks for correcting me.
zubspace 5y ago
What is not so obvious to me: When should WAL be used and when not? Does it even make a difference? It says that WAL might be 1-2% slower on read heavy applications, but how much faster is it for write heavy applications? I'm trying to think of a use case because most (web) applications are ready heavy. And another question: Are both, WAL and rollback journal multithreading and multiprocess safe while being accessed by multiple applications on a single server? I always asked myself this but somehow never found a clear yes/no answer.
- rapsey 5y ago
  Yes they are. As for what to use, I would always go for wal. https://sqlite.org/faq.html#q5 https://sqlite.org/faq.html#q5
- onli 5y ago
  Not 100% certain about this, but I can give my understanding and some links. Have a look at https://www.sqlite.org/faq.html#q5 https://www.sqlite.org/faq.html#q5. Multiple process can have the database open. Multiple processes can read the database simultaneously. Only one process can write to the database at each moment. For the above to be true WAL has to be enabled. Otherwise writes in the one process will block reads from other processes. You can test this with two simple scripts that write and read with an uneven interval from the database (I have an example for that under [0], german, but with code). I think the idea is that you can also have multiple process that write, they just have to set a timeout and retry. That's not the default configuration, not with the sqlite libs I know at least. But if write processes just retry then that should also work. PRAGMA busy_timeout should be the keyword here, or handling it manually by catching the timeout exception and retrying. I usually try to avoid that scenario regardless, one process that writes and many that read worked for me for everything so far. [0]: https://www.onli-blogging.de/1886/Eine-SQLite-Datenbank-mit-mehreren-Prozessen-teilen.html https://www.onli-blogging.de/1886/Eine-SQLite-Datenbank-mit-...
- RhodesianHunter 5y ago
  > I'm trying to think of a use case Most streaming applications will be write heavy (each record written individually, reads batched) or roughly equal reads/writes.
hansvm 5y ago
One thing to note: the bottleneck demonstrated here is in write transactions per second, not in GB|rows inserted per second or anything. The vast majority of businesses will never hit 400qps for writes anyway (and many of the ones that would could throw a sharding strategy at the problem), but just having a lot of data being written isn't sufficient to make sqlite an untenable solution; problems only arise when the insertions of that data need to be extremely fine-grained.
ridiculous_fish 5y ago
Can anyone share their experiences using SQLite over NFS? It seems like SQLite has major caveats here (no WAL, dot-file locking); how much of a headache is it? For context, fish shell is considering using SQLite for history, but some users have NFS home directories.
- nikisweeting 5y ago
  A big headache in my experience. Different NFS servers lie about whether or not they really fsync writes when the clients request it, so you can end up with clobbered writes and corrupted databases in the worst case, and lots of "database is locked" errors that persist for longer than they should in the best case.
netcyrax 5y ago
"Benchmarks on the smallest EC2 instance, with no provisioned IOPS, put this little trooper at 400 write transactions per second, and thousands of reads" -> I think 90% of SaaS would be super fine with this write limits.

83 points by manjana 5y ago

I love me some SQLite but that post was worthless. Edit: flagged for using "worthless"? It tells us that SQLite has WAL mode and that should be enabled if using it in production. New version: I love me some SQLite but that article merely points out that WAL mode is a thing and should be used. Is that really news or valuable to this community?

deleted 5y ago
[deleted]
joncfoo 5y ago
Please be nice.
- pstuart 5y ago
  Sorry, wasn't meaning to be rude but the title of the post implied there was some insights being shared. Instead, it was telling us that SQLite has WAL mode, and that it should be used. There was nothing there that actually talked about issues or lessons from working with it. What did that post tell us about that this one doesn't: https://sqlite.org/wal.html https://sqlite.org/wal.html Edit: instead of drive-by downvotes, what about giving me a counter example that shows that the article is worthy of being on the front page of HN?
  - acchow 5y ago
    I see your account is from 2007, so I'm a bit surprised you're not familiar with the community guidelines. I'll link them here for you: https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html I'm copying out 2 points I think are most relevant here: - Be kind. Don't be snarky. Have curious conversation; don't cross-examine. Please don't fulminate. Please don't sneer, including at the rest of the community. - Please don't post shallow dismissals, especially of other people's work. A good critical comment teaches us something.
    - pstuart 5y ago
      I asked for counter-proof of my dismissal and reasoning, and all you offer up is copy pasted points that aren't applicable. Yes my dismissal was shallow, as was the article in question. Here's a more verbose version that should satisfy you? The article speaks of using SQLite in production but only mentions the existence of WAL-mode and suggests its use. That is general (not production specific) advice that is well known in the community. While the promotion of SQLite is a good thing, I found this article wanting in delivering on what the title suggested.
  - deleted 5y ago
    [deleted]

RcouF1uZ4gsC 5y ago

And if you are looking for a simple way to make sure your sqlite database is replicated, check out litestream https://litestream.io/ https://litestream.io/ It continuously streams your changes to an an S3 compatible service.

SMFloris 5y ago

So 400 writes per second is not exactly what I would think about when thinking about a "Production" environment.

zie 5y ago
For Google sized, obviously not. But lots and lots of production stuff will likely live quite comfortably in 400 writes per second.
- dstick 5y ago
  True, until it isn’t? It’s quite a big gamble and a large lock-in. What would the benefit be for choosing Sqlite over MySql?
  - gpapilion 5y ago
    Easy to scale distribution of files vs having to scale a db server.
  - yjftsjthsd-h 5y ago
    Does mysql still risk eating your data? https://stackoverflow.com/questions/6158568/mysql-myisam-data-loss-possibilities https://stackoverflow.com/questions/6158568/mysql-myisam-dat...
    - kbenson 5y ago
      That's MyISAM. Nobody uses that engine for production without specifically choosing it and hopefully for a good reason. InnoDB has been the default storage engine since 5.5, released in late 2010, so for over a decade.
      - yjftsjthsd-h 5y ago
        And to be explicit: It looks like InnoDB is ACID compliant and crash consistent, so indeed this criticism appears out of date. Thanks for correcting me.

zubspace 5y ago

What is not so obvious to me: When should WAL be used and when not? Does it even make a difference? It says that WAL might be 1-2% slower on read heavy applications, but how much faster is it for write heavy applications? I'm trying to think of a use case because most (web) applications are ready heavy. And another question: Are both, WAL and rollback journal multithreading and multiprocess safe while being accessed by multiple applications on a single server? I always asked myself this but somehow never found a clear yes/no answer.

rapsey 5y ago
Yes they are. As for what to use, I would always go for wal. https://sqlite.org/faq.html#q5 https://sqlite.org/faq.html#q5
onli 5y ago
Not 100% certain about this, but I can give my understanding and some links. Have a look at https://www.sqlite.org/faq.html#q5 https://www.sqlite.org/faq.html#q5. Multiple process can have the database open. Multiple processes can read the database simultaneously. Only one process can write to the database at each moment. For the above to be true WAL has to be enabled. Otherwise writes in the one process will block reads from other processes. You can test this with two simple scripts that write and read with an uneven interval from the database (I have an example for that under [0], german, but with code). I think the idea is that you can also have multiple process that write, they just have to set a timeout and retry. That's not the default configuration, not with the sqlite libs I know at least. But if write processes just retry then that should also work. PRAGMA busy_timeout should be the keyword here, or handling it manually by catching the timeout exception and retrying. I usually try to avoid that scenario regardless, one process that writes and many that read worked for me for everything so far. [0]: https://www.onli-blogging.de/1886/Eine-SQLite-Datenbank-mit-mehreren-Prozessen-teilen.html https://www.onli-blogging.de/1886/Eine-SQLite-Datenbank-mit-...
RhodesianHunter 5y ago
> I'm trying to think of a use case Most streaming applications will be write heavy (each record written individually, reads batched) or roughly equal reads/writes.

hansvm 5y ago

One thing to note: the bottleneck demonstrated here is in write transactions per second, not in GB|rows inserted per second or anything. The vast majority of businesses will never hit 400qps for writes anyway (and many of the ones that would could throw a sharding strategy at the problem), but just having a lot of data being written isn't sufficient to make sqlite an untenable solution; problems only arise when the insertions of that data need to be extremely fine-grained.

ridiculous_fish 5y ago

Can anyone share their experiences using SQLite over NFS? It seems like SQLite has major caveats here (no WAL, dot-file locking); how much of a headache is it? For context, fish shell is considering using SQLite for history, but some users have NFS home directories.

nikisweeting 5y ago
A big headache in my experience. Different NFS servers lie about whether or not they really fsync writes when the clients request it, so you can end up with clobbered writes and corrupted databases in the worst case, and lots of "database is locked" errors that persist for longer than they should in the best case.

netcyrax 5y ago

"Benchmarks on the smallest EC2 instance, with no provisioned IOPS, put this little trooper at 400 write transactions per second, and thousands of reads" -> I think 90% of SaaS would be super fine with this write limits.