InnoDB Bottleneck: Relaxing ACID to Improve Performance

Question

After noticing that our database has become a major bottleneck on our live production systems, I decided to construct a simple benchmark to get to the bottom of the issue.

The benchmark: I time how long it takes to increment the same row in an InnoDB table 3000 times, where the row is indexed by its primary key, and the column being updated is not part of any index. I perform these 3000 updates using 20 concurrent clients running on a remote machine, each with its own separate connection to the DB.

I'm interested in learning why the different storage engines I benchmarked, InnoDB, MyISAM, and MEMORY, have the profiles that they do. I'm also hoping to understand why InnoDB fares so poorly in comparison.

InnoDB (20 concurrent clients): Each update takes 0.175s. All updates are done after 6.68s.

MyISAM (20 concurrent clients): Each update takes 0.003s. All updates are done after 0.85s.

Memory (20 concurrent clients): Each update takes 0.0019s. All updates are done after 0.80s.

Thinking that the concurrency could be causing this behavior, I also benchmarked a single client doing 100 updates sequentially.

InnoDB: Each update takes 0.0026s.

MyISAM: Each update takes 0.0006s.

MEMORY: Each update takes 0.0005s.

The actual machine is an Amazon RDS instance (http://aws.amazon.com/rds/) with mostly default configurations.

I'm guessing that the answer will be along the following lines: InnoDB fsyncs after each update (since each update is an ACID compliant transaction), whereas MyISAM does not since it doesn't even support transaction. MyISAM is probably performing all updates in memory, and regularly flushing to disk, which is how its speed approaches the MEMORY storage engine. If this is so, is there a way to use InnoDB for its transaction support, but perhaps relax some constraints (via configurations) so that writes are done faster at the cost of some durability?

Also, any suggestions on how to improve InnoDB's performance as the number of clients increases? It is clearly scaling worse than the other storage engines.

Update

I found https://blogs.oracle.com/MySQL/entry/comparing_innodb_to_myisam_performance, which is precisely what I was looking for. Setting innodb-flush-log-at-trx-commit=2 allows us to relax ACID constraints (flushing to disk happens once per second) for the case where a power failure or server crash occurs. This gives us a similar behavior to MyISAM, but we still get to benefit from the transaction features available in InnoDB.

Running the same benchmarks, we see a 10x improvement in write performance.

InnoDB (20 concurrent clients): Each update takes 0.017s. All updates are done after 0.98s.

Any other suggestions?

Answer 1

I found https://blogs.oracle.com/MySQL/entry/comparing_innodb_to_myisam_performance, which is precisely what I was looking for. Setting innodb-flush-log-at-trx-commit=2 allows us to relax ACID constraints (flushing to disk happens once per second) for the case where a power failure or server crash occurs. This gives us a similar behavior to MyISAM, but we still get to benefit from the transaction features available in InnoDB.

Running the same benchmarks, we see a 10x improvement in write performance.

InnoDB (20 concurrent clients): Each update takes 0.017s. All updates are done after 0.98s.

Answer 2

We have done some similar tests in our application and we noticed that if no transaction is explicitly opened, each single SQL instruction is treated inside a transaction, which takes much more time to execute. If your business logic allows, you can put several SQL commands inside a transaction block, reducing overall ACID overhead. In our case, we had great performance improvement with this approach.