6 ms·
SQLite is incredible. If you are struggling to beat the "one query per second" meme, try the following 2 things: 1. Only use a single connection for all access
by bob1029 6y ago
SQLite is incredible. If you are struggling to beat the "one query per second" meme, try the following 2 things:
1. Only use a single connection for all access. Open the database one time at startup. SQLite operates in serialized mode by default, so the only time you need to lock is when you are trying to obtain the LastInsertRowId or perform explicit transactions across multiple rows. Trying to use the one connection per query approach with SQLite is going to end very badly.
2. Execute one time against a fresh DB: PRAGMA journal_mode=WAL
If at this point you are still finding that SQLite is not as fast or faster than SQL Server, MySQL, et.al., then I would be very surprised.
I do not think you can persist a row to disk faster with any other traditional SQL technology. SQLite has the lowest access latency that I am aware of. Something about it living in the same process as your business application seems to help a lot.
We support hundreds of simultaneous users in production with 1-10 megs of business state tracked per user in a single SQLite database. It runs fantastically.
- hans_castorp 6y ago> If at this point you are still finding that SQLite is not as fast or faster than SQL Server, MySQL, et.al., then I would be very surprised. What about large aggregation queries, that are parallelized by modern DBMS? Does it still scale that well if you have many concurrent read and write transactions (e.g. on the same table)?
- bob1029 6y agoWe aren't running any reports on our databases like this. I would argue it is a bad practice in general to mix OLTP and OLAP workloads on a single database instance, regardless of the specific technology involved. If we wanted to run an aggregate that could potentially impact live transactions, we would just copy the SQLite db to another server and perform the analysis there. We have some telemetry services which operate in this fashion. They go out to all of the SQLite databases, make a copy and then run analysis in another process (or on another machine). I am not aware of any hosted SQL technology which is capable of magically interleaving large aggregate queries with live transactions and not having one or both impacted in some way. At the end of the day, you still have to go to disk on writes, and this must be serialized against reads for basic consistency reasons. After a certain point, this is kinda like trying to beat basic information theory with ever-more-complex compression schemes. I'd rather just accept the fundamental truth of the hardware/OS and have the least amount of overhead possible when engaging with it.
- castorp 6y ago> At the end of the day, you still have to go to disk on writes, and this must be serialized against reads for basic consistency reasons. No, absolutely not. That's why modern databases use a thing called multi version concurrency control. You can run (multiple) queries on the same table that is updated by multiple transactions at the same time without one blocking the others (assuming the write transactions don't block each other). Of course they are fighting for I/O, but there is no need so serialize anything. Mixing OLTP and OLAP becomes increasingly "normal" theses days as the capabilities of the database products and the hardware improve. With modern high-end hardware (hundreds of CPUs, a lot of SSDs, large RAM) this actually scales quite nicely .
- BFatts 6y agoI think we're seeing the difference between someone who is a programmer and someone who plays a programmer at work. :) Arguing that some modern, complex feature is "better than" a simpler system is crazy talk. Any time I can simplify a system vs. add complexity I will go simplicity. Adding in plug-ins and features goes a long way toward vendor lock-in that prevents your org from being agile when you have to swap systems because you run into limits or have catastrophic failures.
- unilynx 6y agoI think this is specifically because random reads are scaling much better than writes, even though you won't see it on standard "that many MB/s" benchmarks where read is 'just' a few multiples of the write performance. Persisting a transaction to the database is still (and especially in MVCC): "send data write". "wait for write to be flushed". "toggle metadata bit to mark write as completed". "wait for bit to be completed" which still serialises transaction commits while reads can complete in parallel as fast as the device can handle. Especially now that the reads and writes don't have to share the disk head, it makes sense for random reads to keep on scaling better than writes.
- ComputerGuru 6y agoThis is actually the opposite of current limitations. Stick a capacitor and some DRAM on the NVMe and you can "instantly" flush to disk, but there's no way to anticipate where the next read will come from and therefore no way to accelerate it. You'll see modern NVMe disks with sustained writes greatly outpacing reads until the write cache is saturated, at which point what you say is true and reads will greatly outpace writes. But you don't want your disks to ever be in that threshold.
- saberience 6y agoAre you living on another planet? Are you seriously suggesting people replace SqlServer or MySQL with SQLite? If you can come to my company and replace our 96-core SqlServer boxes with SQLite I'll pay you any salary you ask for.
- bob1029 6y agoI can assure you that I live on the same planet as everyone else posting here. Whether or not I could perform this miracle depends entirely on your specific use cases. Many people who have this sort of reaction are coming from a place where there is heavy use of the vendor lock-in features such as SSIS and stored procedures. If you are ultimately just trying to get structured business data to/from disk in a consistent manner and are seeking the lowest latency and highest throughput per request, then SQLite might be what you are looking for. The specific core counts or other specifications are meaningless. SQLite scales perfectly on a single box, and if you have some good engineers you might even be able to build a clustering protocol at the application layer in order to tie multiple together. At a certain point, writing your own will get cheaper than paying Microsoft for the privilege of using SQL Server.
- SNACKeR99 6y agoThis is a great answer. The details REALLY matter. One of my best early tech success stories was rewriting a SQL query that took 27 hours to one that took ~5 seconds. This was running on a very large Oracle cluster. They had poured more and more money into hardware and licensing trying solve this. In the end, it was a matter of turning a cursor-based query into a set-based query.
- zimpenfish 6y agoIn some respects, I think the constraints of something like SQLite can focus people's attention on making things work properly rather than throwing hardware at the problem. I can think of a couple of places I've worked where they had simple problems that could have been solved by some thinking and coding but instead were solved* by more expensive hardware.
- barrkel 6y agoYou've just moved a bunch of problems to whatever is accessing SQLite. How do you scale out the application server compute if it needs to make transactionally conditional updates? E.g.: transaction { if (expensiveFunction(query())) update(); } (My applications always get much faster when I plug them into Postgres after SQLite. But then I do do the odd sort and group by, not OLAP, but because they express the computation I want and Postgres is simply much better at that.)
- neurostimulant 6y agoHow do you handle access from multiple worker processes? Some languages/frameworks handle have poor multi-threading performance and must be deployed in a multi-process setup (e.g. python webapps). Or is it not a good fit for sqlite?
- bob1029 6y agoMultiprocess can work, but you may want to develop an intermediary process that is exclusive owner of the database for performance reasons and then delegate to it. I.e. you could have: database.db <=> SQLiteAgent <=> localhost HTTP JSON <=> MyPythonWebApps This exercise would also encourage development of a concise and generic schema for storing your business data (presumably because changes to the above JSON contract would be time consuming).
- itroot 6y agoWhy single connection? Sqlite in WAL mode will allow to co-exist writer and readers.
- lenkite 6y agoWhere are these databases persisted ? HA (multi-process)? Failover ? Backup ? How do you handle containerisation ?Some details would be seriously welcome.