4 ms·

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 applicat

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.
Rapzid 5y ago
The article discusses ec2 so I can tell you at least this much. The flush FS commands will get translated into storage system commands to ensure blocks are written through to the disk. When using network-attached storage, like EBS, the minimum latency will likely be determined by the network latency(and the bulk of all the latency will likely be network) involved and sending these commands and waiting for the ack. If using WAL halves the number of times this needs to occur, you should see double the number of serial transactions per second. 1-2% slower on reads is likely as insignificant as it sounds, particularly if your write load has you interested in double the number of write qps; possibly without changing a single schema or line of code! Edit: This is generalized information though. I would like to see some actual SQLite numbers and I disagree with the article's generalization that SQLite is best suited for applications that "Are read-heavy but not write-heavy".

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.

Rapzid 5y ago

The article discusses ec2 so I can tell you at least this much. The flush FS commands will get translated into storage system commands to ensure blocks are written through to the disk. When using network-attached storage, like EBS, the minimum latency will likely be determined by the network latency(and the bulk of all the latency will likely be network) involved and sending these commands and waiting for the ack. If using WAL halves the number of times this needs to occur, you should see double the number of serial transactions per second. 1-2% slower on reads is likely as insignificant as it sounds, particularly if your write load has you interested in double the number of write qps; possibly without changing a single schema or line of code! Edit: This is generalized information though. I would like to see some actual SQLite numbers and I disagree with the article's generalization that SQLite is best suited for applications that "Are read-heavy but not write-heavy".