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.


3 Answers 3


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?



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.


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

  insert into T values (?, ?, ?, ?);
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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