为什么插入查询有时需要很长时间才能完成？

这是一个非常简单的问题。将数据插入表中通常工作正常，除了少数情况下插入查询需要几秒钟的时间。 （我是not尝试批量插入数据。）因此，我为插入过程设置了一个模拟，以找出为什么插入查询偶尔需要超过 2 秒才能运行。 Joshua建议索引文件可能正在调整；我删除了id（主键字段），但延迟仍然发生。

我有一个 MyISAM 表：daniel_test_insert（此表开始完全地 empty):

create table if not exists daniel_test_insert ( 
    id int unsigned auto_increment not null, 
    value_str varchar(255) not null default '', 
    value_int int unsigned default 0 not null, 
    primary key (id) 
)

我将数据插入其中，有时插入查询需要超过 2 秒的时间才能运行。没有读取内容在此表上 - 仅由单线程程序串行写入。

我运行了完全相同的查询 100,000 次，以找出查询有时需要很长时间的原因。到目前为止，这似乎是一个随机事件。

例如，此查询花费了 4.194 秒（对于插入来说非常长的时间）：

Query: INSERT INTO daniel_test_insert SET value_int=12345, value_str='afjdaldjsf aljsdfl ajsdfljadfjalsdj fajd as f' - ran for 4.194 seconds
status               | duration | cpu_user  | cpu_system | context_voluntary | context_involuntary | page_faults_minor
starting             | 0.000042 | 0.000000  | 0.000000   | 0                 | 0                   | 0                
checking permissions | 0.000024 | 0.000000  | 0.000000   | 0                 | 0                   | 0                
Opening tables       | 0.000024 | 0.001000  | 0.000000   | 0                 | 0                   | 0                
System lock          | 0.000022 | 0.000000  | 0.000000   | 0                 | 0                   | 0                
Table lock           | 0.000020 | 0.000000  | 0.000000   | 0                 | 0                   | 0                
init                 | 0.000029 | 0.000000  | 0.000000   | 1                 | 0                   | 0                
update               | 4.067331 | 12.151152 | 5.298194   | 204894            | 18806               | 477995           
end                  | 0.000094 | 0.000000  | 0.000000   | 8                 | 0                   | 0                
query end            | 0.000033 | 0.000000  | 0.000000   | 1                 | 0                   | 0                
freeing items        | 0.000030 | 0.000000  | 0.000000   | 1                 | 0                   | 0                
closing tables       | 0.125736 | 0.278958  | 0.072989   | 4294              | 604                 | 2301             
logging slow query   | 0.000099 | 0.000000  | 0.000000   | 1                 | 0                   | 0                
logging slow query   | 0.000102 | 0.000000  | 0.000000   | 7                 | 0                   | 0                
cleaning up          | 0.000035 | 0.000000  | 0.000000   | 7                 | 0                   | 0

（这是 SHOW PROFILE 命令的缩写版本，我扔掉了全为零的列。）

现在，更新有数量惊人的上下文切换和轻微页面错误。此数据库上的 Opened_Tables 大约每 10 秒增加 1 个（未耗尽 table_cache 空间）

Stats:

• MySQL 5.0.89

• 硬件：32 Gigs 内存/8 核 @ 2.66GHz； raid 10 SCSI 硬盘 (SCSI II???)

• 我已查询硬盘驱动器和 raid 控制器：没有报告错误。 CPU 大约有 50% 空闲。

• iostat -x 5（报告硬盘利用率低于 10%） 顶部报告负载平均约为 10 持续 1 分钟（对于我们的数据库机器来说是正常的）

• 交换空间已使用 156k（32 GB 内存）

我不知道是什么导致了这种性能滞后。这种情况不会发生在我们的低负载从站上，只会发生在我们的高负载主站上。内存和 innodb 表也会发生这种情况。有没有人有什么建议？ （这是一个生产系统，所以没有什么奇怪的！）

我在我的系统上注意到了同样的现象。通常需要 1 毫秒的查询突然会需要 1-2 秒。我的所有案例都是简单的单表 INSERT/UPDATE/REPLACE 语句 --- 不在任何 SELECT 上。没有明显的负载、锁定或螺纹堆积。

我曾怀疑这是由于清除脏页、刷新磁盘更改或某些隐藏的互斥体造成的，但我尚未缩小范围。

也排除了

• 服务器负载——与高负载没有相关性
• 引擎——与 InnoDB/MyISAM/Memory 一起发生
• MySQL 查询缓存——无论打开还是关闭都会发生
• 日志轮换——事件中没有相关性

此时我唯一的其他观察结果来自于我在多台机器上运行相同的数据库这一事实。我有一个大量读取的应用程序，因此我使用具有复制的环境 - 大部分负载都在从属服务器上。我注意到，即使主服务器上的负载最小，但这种现象在主服务器上发生得更多。即使我没有看到锁定问题，也许是 Innodb/Mysql 在（线程）并发方面遇到了问题？回想一下，从站上的更新将是单线程的。

MySQL 版本 5.1.48

Update

我想我已经找到了解决我案件问题的线索。在我的一些服务器上，我比其他服务器更注意到这种现象。了解不同服务器之间的差异并进行调整，我得到了MySQL innodb 系统变量 http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit innodb_flush_log_at_trx_commit.

我发现该文档读起来有点尴尬，但是innodb_flush_log_at_trx_commit可以取值 1,2,0：

• 对于 1，日志缓冲区被刷新到 每次提交的日志文件以及日志 每次提交时文件都会刷新到磁盘。
• 对于 2，日志缓冲区被刷新到 每次提交的日志文件以及日志 文件大约每 1-2 秒刷新到磁盘一次。
• 对于 0，日志缓冲区被刷新到 每秒的日志文件，以及日志 文件每秒刷新到磁盘。

实际上，按照报告和记录的顺序 (1,2,0)，您应该获得交易绩效的提高，但风险也会增加。

话虽如此，我发现服务器innodb_flush_log_at_trx_commit=0比具有以下功能的服务器表现更差（即“长更新”多 10-100 倍）innodb_flush_log_at_trx_commit=2。此外，当我将其切换到 2 时，不良实例的情况立即得到改善（请注意，您可以即时更改它）。

所以，我的问题是，你的设置是什么？请注意，我并不是责怪此参数，而是强调它的上下文与此问题相关。