SQLite in production with WAL
victoria.dev
victoria.dev
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.
Have a look at 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-...
Most streaming applications will be write heavy (each record written individually, reads batched) or roughly equal reads/writes.
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".
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.
For context, fish shell is considering using SQLite for history, but some users have NFS home directories.
What would the benefit be for choosing Sqlite over MySql?
InnoDB has been the default storage engine since 5.5, released in late 2010, so for over a decade.
MySQL 3.x was mostly just okay for simple website that didn't do much. (MyISAM is good for either extremely read heavy with few writes or vice versa, was very performant in those cases for the time)
MySQL 4.x added subquery support and InnoDB as a standard component, even if not the default.
MySQL 5.0 added cursors, triggers, stored procedures, views.
I think from about this point MySQL was fairly usable in place of some enterprise DB's depending on use case, and most of the essential features were there, even if not always the default.
MySQL started as a super easy to use and accessible DB, and slowly accreted enough enterprise level features to be able to compete in that field, but it's been in use successfully there for quite a while.
In comparison, my (super limited and possibly incorrect) understanding of Postgres's history is that they focused on stability and enterprise level features first, and then later added some things for more convenience. I think they aren't super different in capabilities now, at least for the core functionality most expect.
Second, 400 concurrent users per second is a lot? If I had 400 users per second, with a 10 minute engagement time for 4 hours a day, I’d have ~10000 daily users. If they paid a monthly SAAS of 5$/user, I’d be making 600k$/year.
So… considering the implementation simplicity, that seems like a good trade off?
I have no experience moving from sqlite -> mysql, but I imagine it's only a bit more difficult.
But I agree with thechao's comments also.
-- Assume an employee filling out a form on a web site that takes on minute to complete.
-- Assume submits are randomly sent. (A bad assumption)
Per minute that's (1 * 60 * 400) 24,000 people submitting a form. Or over a million people submitting their form per hour. Nothing Google scale but enough for most any internal business application.