Assuming one has about 1k requests per second that require an insert.

Now, there are a lot of answers to this on the internet... but they're technically wrong in this specific context. Yes, pretty much any RDBMS can handle 1k inserts per second on standard hardware but IF AND ONLY IF you drop ACID guarantees. It's surprising how many terrible answers there are on the internet. Such as "you can always scale up CPU and RAM" which is supposed to give you more inserts per second but that's not how it works. The limiting factor is disk speed or more precise: how many transactions you can actually flush/sync to disk. And this is the tricky bit.

On decent "commodity hardware" (unless you invest into high performance SSDs) this is about what you can expect:

  • SQLite: 30 inserts/s
  • MySQL: 80 inserts/s

This is the rate you can insert while maintaining ACID guarantees. This essentially means that if you have a forum with 100 posts per second... you can't handle that with such a setup.

Read requests aren't the problem. You can have many thousands of read requests per second no problem but write requests are usually <100 per second.

Thus, this question is specifically aimed at how one can handle 1k inserts per second while still maintaining ACID guarantees - assuming that a single node can handle about 80 transactions per second.

One way I could see this working is if you buffer inserts somewhere in your application logic and submit them as larger transactions to the database (while keeping clients waiting until the transaction is over) which should work fine if you need single inserts only although it complicates the application logic quite a bit.

closed as unclear what you're asking by mustaccio, RDFozz, Md Haidar Ali Khan, hot2use, Tom V Jul 18 at 6:51

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    Why don't you want to invest in a high-performance SSD? – mustaccio Jul 17 at 15:20
  • 3
    30/ 80 inserts seem very low. Where do these numbers come from? – a1ex07 Jul 17 at 15:56
  • 1
    In order to get a more accurate solution, would you include in your question a deployment diagram of your entire soution. (hardware / OS / Software / DB)? – AMG Jul 17 at 16:56
  • @a1ex07 from benchmarks on a couple of machines. You can also write a little program that does write followed by fsync and you'll notice that you can do something something around 50-100 write/fsyncs per second and since every storage engine sooner or later has to at least fdatasync you're basically limited by the amount of fsync/fdatasyncs you can do per second and it turns out on regular hardware there's just not that many syncs you can do per second and that's the reason why neither mysql nor sqlite nor any other of these DB can pull of more than 100 inserts per second. – mroman Jul 17 at 18:20
  • @a1ex07 also look for example at percona.com/blog/2018/02/08/fsync-performance-storage-devices which shows that even on some SSDs you can only do about 100-200 fsyncs per second which limits your inserts per second to about that (although fdatasync is faster than fsync). – mroman Jul 17 at 18:23
up vote 6 down vote accepted

My simple RAID 10 array running on old hardware with 300GB SAS disks can handle 200-300 inserts per second without any trouble; this is with SQL Server running on a VM, with a lot of other VMs running simultaneously.

With just a consumer grade SSD, you can expect 3,000 to 5,000 or more 4K I/Os per second.

What exactly is your question?

  • Indeed. With a physical RAID this seems to be possible. Thanks! – mroman Jul 17 at 18:27
  • 1
    @mroman Specifically with RAID 10, RAID 5 does not typically improve write performance (and often hurts it). – RBarryYoung Jul 17 at 19:00

This

This essentially means that if you have a forum with 100 posts per second... you can't handle that with such a setup.

Is simply incorrect. What you're missing is that multiple users can enqueue changes in each log flush. So while each log flush takes, say 10ms, it can harden dozens or hundreds of separate, concurrent transactions.

Analogy: A train that goes back and forth once an hour can move a lot more than 1 person per hour.

In SQL Server the concurrent sessions will all write to the Log Buffer, and then on Commit wait for confirmation that their LSN was included in a subsequent log flush.

Assume your log disk has 10ms write latency and 100mB/s max write throughput (conservative numbers for a single spinning disk). If each transaction requires 100kB of log space (big), you can flush 1000 transactions per second on the disk, so long as you have at least 10 users waiting to commit a transaction at any time.

  • Hm. Interesting. It doesn't look like standard mysql supports such a thing. If you have 1 connection doing inserts I can do about 80 inserts per second. With two concurrent connections each can do about 38 inserst per second so it doesn't look like it buffers transaction to fsync multiple transactions at the same time. Need to test it on MSSQL sometime. – mroman Jul 18 at 6:06

For an ACID Compliant systems, the following code is known to be slow:

loop
  insert into T values (?, ?, ?, ?);
  commit;
end loop;

The commit won't return until the disk subsystem says that the data is safe on the platter (at least, with Oracle). Due to c (the speed of light), you are physically limited to how fast you can call commit; SSDs and RAID can only help out so much.. (It seems Oracle has an Asynchronous Commit method, but, I haven't played with it.)

One of the solutions (I have seen) is to queue the request with something like Apache Kafka and bulk process the requests every so often. This idea comes from of my googling on Streaming Analytics.

With that method, you can easily process 1M requests every 5 seconds (200 k/s) on just about any ACID Compliant system.

Not the answer you're looking for? Browse other questions tagged or ask your own question.