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?

Fidget

myisam is by design not ACID compliant. InnoDB is. If you relax the contraints, then it's no longer ACID compliant, and you might as well NOT use innodb. – Oletta

You are using transactions ... right? When a transaction completes InnoDB must perform a hardware flush to guarantee the "D" in ACID. Even fast ACID DBs are limited to about 30-50 transactions / second on "standard" spindle disks. (However, it isn't hard to get thousands of updates / second. There is a difference.) – Wetterhorn

It's true, we don't need full ACID compliance. But, what if we want a weaker form of ACI (no D) for features like transaction isolation? Since our data isn't too sensitive, and we use Amazon RDS with Multi-AZ, rare crashes and power failures are not a concern for us. – Fidget

@Fidget Consider putting that information (bring it more to the front, really), along with the usage of transactions (or not), in the main post. – Wetterhorn

Isolation, but not durability? I wonder what are you doing. – Acclaim

@Acclaim They are different; Isolation affects "interleaving" or "visibility" between different transactions. Durability is not involved here. The Isolation should be honored, even if something goes terribly wrong and the changes done by a TX on commit are lost (Durability failure). The TX might have been rolled-back instead in which cases the changes would also have be "lost". – Wetterhorn

@pst: I see how durability and isolation are different. I wonder what kind of a system would need isolation (that is, complex multipart transactions in parallel) but not durability (transactions are not that valuable). – Acclaim

@Acclaim Transactions are valuable to us. In this scenario, durability is important for surviving events equivalent to power failure. Since we use Amazon RDS with multi-AZ this is less of a concern for us. But even if we only had a single DB server, the 10x speed gain we see from relaxing durabilty are worth the extra application logic we would implement to fix the state of the database ourselves in the event of a rare power failure. – Fidget

@pst Will do. Thanks for helping facilitate the discussion! – Fidget

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.

Fidget

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.

Preemie