4 ms·
This is a really long blog post to just say that you should turn on the WAL if you want concurrency out of SQLite. All the other stuff is superfluous.
by rbranson 2y ago
This is a really long blog post to just say that you should turn on the WAL if you want concurrency out of SQLite. All the other stuff is superfluous.
- hu3 2y agoCan't agree. I learned about BEGIN IMMEDIATE TRANSACTION. And there's also busy_timeout. The article also explains why/how/when things occur in detail which is valuable.
- rbranson 2y agoWAL mode makes those redundant. Just use WAL mode.
- adamtaylor_13 2y ago…what? No it doesn’t. Go read the article. Every optimization listed addresses a different performance aspect of SQLite.
- rbranson 2y agoHow does busy_timeout address a performance aspect of SQLite?
- fractaledmind 2y agoAs I explain in the post, if you have multiple connections and consistent write load, the timeout will penalize older quereres and noticeably harm your long tail latency
- hu3 2y agoWe all wish you were right. But alas life's not that simple. I suggest reading the manual on the section: "Sometimes Queries Return SQLITE_BUSY In WAL Mode" https://sqlite.org/wal.html#sometimes_queries_return_sqlite_busy_in_wal_mode https://sqlite.org/wal.html#sometimes_queries_return_sqlite_...
- rbranson 2y agoYeah, this almost never happens in practice. It’s not even worth being concerned about.
- nikisweeting 2y agoHappened all the time to me before I did some tuning Depends how much write contention you have and how long open transactions take to finish.
- rbranson 2y agoThe list of circumstances for WAL-mode busy errors is in the doc linked by one of the posters above. It has nothing to do with transactions.
- ncruces 2y agoThat list lists reasons queries might return busy; queries aka. reads. Reads returning busy is rare under WAL, but WAL mode does very little for writer-writer contention.
- NortySpock 2y agoI thought it was a pretty good list of common Rails-application-specific and sqlite3-specific knobs to turn, for newcomers to performance tuning. (Really just a guided tour though -- turn this knob to enable this particular tool for dealing with concurrency problems...)
- deleted 2y ago[deleted]