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

Question

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

我有一个 MyISAM 表:daniel_test_insert(此表开始时完全为空):

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 空间)

统计数据:

• MySQL 5.0.89

• 硬件:32 Gigs 内存/8 核 @ 2.66GHz；突袭 10 个 SCSI 硬盘 (SCSI II???)

• 我查询过硬盘和raid Controller :没有报告错误。CPU 大约有 50% 空闲。

• iostat -x 5(报告硬盘利用率低于 10%)顶部报告负载平均约 10 1 分钟(我们的数据库机器正常)

• 已使用 156k 交换空间(32 gigs 内存)

我不知道是什么导致了这种性能滞后。这不会发生在我们的低负载从属设备上，只会发生在我们的高负载主机上。这也发生在内存和 innodb 表中。有没有人有什么建议？ (这是一个生产系统，所以没有什么异国情调!)

Answer 1

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

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

也排除了

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

我在这一点上唯一的其他观察来 self 在多台机器上运行相同的数据库这一事实。我有一个繁重的读取应用程序，所以我正在使用一个具有复制功能的环境——大部分负载都在从属设备上。我注意到，即使主服务器上的负载很小，这种现象也更多地发生在那里。即使我没有看到锁定问题，也许是 Innodb/Mysql 在(线程)并发方面遇到问题？回想一下，从站上的更新将是单线程的。

MySQL 版本 5.1.48

更新

我认为我对我的案子的问题有线索。在我的一些服务器上，我注意到这种现象比其他服务器更多。看到不同服务器之间有什么不同，并调整了一些东西，我被带到了MySQL innodb system variable。 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 的服务器比 innodb_flush_log_at_trx_commit=2。此外，当我将其切换为 2 时，坏实例的情况立即得到改善(请注意，您可以随时更改它)。

那么，我的问题是，你的设置是什么？请注意，我并不是在责怪这个参数，而是强调它的上下文与这个问题有关。