MySQL/InnoDB偶尔有些更新运行很慢，处于 'Updating'状态

Question

我们的数据库更新性能偶尔会大幅下降。

例如，表 FooTable 我们有大约 40 列与 varchar PK 此外还有 10 个索引。以下查询用时 44 秒，而在其他时候它几乎立即运行。在减速期间，服务器上的平均负载非常低(5 分钟平均负载为 1.5)，并且根据 vmstat 的 IO 也相当合理。

这里有一个例子:

mysql> update FooTable set BarColumn=1349981286086 where varcharPK='e4348411-0fbb-460a-80f7-f1de304a9f7c'
Query OK, 1 row affected (44.94 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show profile for QUERY 1;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000030 |
| checking permissions |  0.000004 |
| Opening tables       |  0.000007 |
| System lock          |  0.000003 |
| Table lock           |  0.000003 |
| init                 |  0.000035 |
| Updating             | 44.949727 |
| end                  |  0.000006 |
| query end            |  0.000003 |
| freeing items        |  0.000115 |
| logging slow query   |  0.000002 |
| logging slow query   |  0.000052 |
| cleaning up          |  0.000003 |
+----------------------+-----------+
13 rows in set (0.02 sec)

值得一提的是，上面的示例查询是在不到一周前“重建”(ALTER TABLE FooTable ENGINE=InnoDB;) 的 InnoDB 表上运行的。我最初怀疑这与 InnoDB 已知的 varchar/非顺序 PK 性能问题有关，但是我们还有其他使用顺序 PK 的表并且遇到了同样的问题。

这是在生产服务器上:Centos 5 2.6.18-238.19.1.el5 x86_64MySQL/Percona 5.1.57-rel12.8-日志96GB 内存，58.8G 数据分布在 87 个表中

相关的InnoDB设置如下:

innodb_flush_log_at_trx_commit  = 2
innodb_buffer_pool_size         = 70G
innodb_log_file_size            = 512M
innodb_log_buffer_size          = 64M
innodb_file_per_table           = 1
innodb_thread_concurrency       = 0
innodb_flush_method             = O_DIRECT
innodb_read_io_threads          = 64
innodb_write_io_threads         = 64
optimizer_search_depth          = 0
innodb_file_format              = barracuda

我没有在此表上使用 FORMAT=COMPRESSED，但我在其他表上使用。

有什么关于如何弄清楚更新阶段发生了什么的建议吗？

Answer 1

根本原因似乎是长时间运行的应用程序事务。解决方案是将大型事务分解为更小的工作单元。