InnoDB瓶颈：放宽ACID以提高性能

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.

基准测试：我计算在InnoDB表中增加相同行3000次所需的时间，其中行由其主键索引，并且正在更新的列不是任何索引的一部分。我使用在远程计算机上运行的20个并发客户端执行这3000次更新，每个客户端都有自己与数据库的单独连接。

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，MyISAM和MEMORY拥有他们所做的配置文件。我也希望了解为什么InnoDB相比之下的表现如此糟糕。

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

InnoDB（20个并发客户端）：每次更新需要0.175秒。所有更新都在6.68s之后完成。

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

MyISAM（20个并发客户端）：每次更新需要0.003秒。所有更新都在0.85秒后完成。

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

内存（20个并发客户端）：每次更新需要0.0019秒。所有更新都在0.80秒后完成。

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

考虑到并发可能导致此行为，我还对单个客户端进行基准测试，按顺序执行100次更新。

InnoDB: Each update takes 0.0026s.

InnoDB：每次更新需要0.0026秒。

MyISAM: Each update takes 0.0006s.

MyISAM：每次更新需要0.0006秒。

MEMORY: Each update takes 0.0005s.

内存：每次更新需要0.0005秒。

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

实际的机器是Amazon RDS实例（http://aws.amazon.com/rds/），主要是默认配置。

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?

我猜测答案将是以下几行：每次更新后InnoDB fsyncs（因为每次更新都是符合ACID的事务），而MyISAM则没有，因为它甚至不支持事务。 MyISAM可能正在内存中执行所有更新，并定期刷新到磁盘，这是它的速度接近MEMORY存储引擎的方式。如果是这样，是否有办法使用InnoDB进行事务支持，但是可能会放松一些约束（通过配置），以便以一些持久性为代价更快地完成写入？

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.

此外，关于如何随着客户数量的增加提高InnoDB性能的任何建议？它显然比其他存储引擎更糟糕。

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.

我找到了https://blogs.oracle.com/MySQL/entry/comparing_innodb_to_myisam_performance，这正是我想要的。设置innodb-flush-log-at-trx-commit = 2允许我们在发生电源故障或服务器崩溃的情况下放松ACID约束（每秒刷新一次磁盘）。这给了我们与MyISAM类似的行为，但我们仍然可以从InnoDB中提供的事务功能中受益。

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

运行相同的基准测试，我们发现写入性能提高了10倍。

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

InnoDB（20个并发客户端）：每次更新需要0.017秒。所有更新都在0.98秒后完成。

Any other suggestions?

还有其他建议吗？

2 个解决方案

#1

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.

我们在应用程序中做了一些类似的测试，我们注意到如果没有显式打开事务，则在事务中处理每个单独的SQL指令，这需要更多的时间来执行。如果您的业务逻辑允许，您可以在事务块中放置几个SQL命令，从而减少总体ACID开销。在我们的案例中，我们通过这种方法有了很大的性能提升。

2 个解决方案

#1

更多相关文章

随机推荐