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

Why does an insert query occasionally take so long to complete?

提问by Daniel

This is a pretty simple problem. Inserting data into the table normally works fine, except for a few times where the insert query takes a few seconds. (I am nottrying to bulk insert data.) So I setup a simulation for the insert process to find out why the insert query occasionally takes more than 2 seconds to run. Joshua suggested that the index file may be being adjusted; I removed the id (primary key field), but the delay still happens.

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

I have a MyISAM table: daniel_test_insert(this table starts completelyempty):

我有一个 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) 
)

I insert data into it, and sometimes a insert query takes > 2 seconds to run. There are no readson this table - Only writes, in serial, by a single threaded program.

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

I ran the exact same query 100,000 times to find why the query occasionall takes a long time. So far, it appears to be a random occurrence.

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

This query for example took 4.194 seconds (a very long time for an insert):

例如，此查询耗时 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

(This is an abbreviated version of the SHOW PROFILE command, I threw out the columns that were all zero.)

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

Now the update has an incredible number of context switches and minor page faults. Opened_Tables increases about 1 per 10 seconds on this database (not running out of table_cache space)

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

Stats:

统计数据：

• MySQL 5.0.89

• Hardware: 32 Gigs of ram / 8 cores @ 2.66GHz; raid 10 SCSI harddisks (SCSI II???)

• I have had the hard drives and raid controller queried: No errors are being reported. CPUs are about 50% idle.

• iostat -x 5 (reports less than 10% utilization for harddisks) top report load average about 10 for 1 minute (normal for our db machine)

• Swap space has 156k used (32 gigs of ram)

• MySQL 5.0.89

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

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

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

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

I'm at a loss to find out what is causing this performance lag. This does NOT happen on our low-load slaves, only on our high load master. This also happens with memory and innodb tables. Does anyone have any suggestions? (This is a production system, so nothing exotic!)

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

采纳答案by Riedsio

I have noticed the same phenomenon on my systems. Queries which normally take a millisecond will suddenly take 1-2 seconds. All of my cases are simple, single table INSERT/UPDATE/REPLACE statements --- not on any SELECTs. No load, locking, or thread build up is evident.

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

I had suspected that it's due to clearing out dirty pages, flushing changes to disk, or some hidden mutex, but I have yet to narrow it down.

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

Also Ruled Out

也排除了

• Server load -- no correlation with high load
• Engine -- happens with InnoDB/MyISAM/Memory
• MySQL Query Cache -- happens whether it's on or off
• Log rotations -- no correlation in events

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

The only other observation I have at this point is derived from the fact I'm running the same db on multiple machines. I have a heavy read application so I'm using an environment with replication -- most of the load is on the slaves. I've noticed that even though there is minimal load on the master, the phenomenon occurs more there. Even though I see no locking issues, maybe it's Innodb/Mysql having trouble with (thread) concurrency? Recall that the updates on the slave will be single threaded.

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

MySQL Verion 5.1.48

MySQL 版本 5.1.48

Update

更新

I think I have a lead for the problem on my case. On some of my servers, I noticed this phenomenon on more than the others. Seeing what was different between the different servers, and tweaking things around, I was lead to the MySQL innodb system variableinnodb_flush_log_at_trx_commit.

我想我对我的案子的问题有一个线索。在我的一些服务器上，我比其他服务器更注意到这种现象。看到不同服务器之间有什么不同，并调整周围的东西，我被引导到MySQL innodb 系统变量innodb_flush_log_at_trx_commit。

I found the doc a bit awkward to read, but innodb_flush_log_at_trx_commitcan take the values of 1,2,0:

我发现文档有点难读，但innodb_flush_log_at_trx_commit可以取 1,2,0 的值：

• For 1, the log buffer is flushed to the log file for every commit, and the log file is flushed to disk for every commit.
• For 2, the log buffer is flushed to the log file for every commit, and the log file is flushed to disk approximately every 1-2 seconds.
• For 0, the log buffer is flushed to the log file every second, and the log file is flushed to disk every second.

• 对于 1，每次提交都会将日志缓冲区刷新到日志文件，并且每次提交都会将日志文件刷新到磁盘。
• 对于 2，每次提交都会将日志缓冲区刷新到日志文件，并且大约每 1-2 秒将日志文件刷新到磁盘。
• 对于 0，日志缓冲区每秒刷新到日志文件，日志文件每秒刷新到磁盘。

Effectively, in the order (1,2,0), as reported and documented, you're supposed to get with increasing performance in trade for increased risk.

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

Having said that, I found that the servers with innodb_flush_log_at_trx_commit=0were performing worse (i.e. having 10-100 times more "long updates") than the servers with innodb_flush_log_at_trx_commit=2. Moreover, things immediately improved on the bad instances when I switched it to 2 (note you can change it on the fly).

话虽如此，我发现innodb_flush_log_at_trx_commit=0带有innodb_flush_log_at_trx_commit=2. 此外，当我将其切换为 2 时，坏实例的情况立即得到改善（请注意，您可以即时更改它）。

So, my question is, what is yours set to? Note that I'm not blaming this parameter, but rather highlighting that it's context is related to this issue.

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

回答by fxwan

We hit exactly the same issue and reported here: http://bugs.mysql.com/bug.php?id=62381

我们遇到了完全相同的问题并在此处报告：http: //bugs.mysql.com/bug.php?id=62381

We are using 5.1.52 and don't have solution yet. We may need to turn QC off to avoid this perf hit.

我们正在使用 5.1.52 并且还没有解决方案。我们可能需要关闭 QC 以避免这种性能下降。

回答by Jay

I had this problem using INNODB tables. (and INNODB indexes are even slower to rewrite than MYISAM)

我在使用 INNODB 表时遇到了这个问题。（而且 INNODB 索引的重写速度甚至比 MYISAM 还要慢）

I suppose you are doing multiple other queries on some other tables, so the problem would be that MySQL has to handle disk writes in files that get larger and needs to allocate additional space to those files.

我想您正在对其他一些表进行多个其他查询，因此问题在于 MySQL 必须处理变大的文件中的磁盘写入，并且需要为这些文件分配额外的空间。

If you use MYISAM tables I strongly suggest using

如果您使用 MYISAM 表，我强烈建议您使用

LOAD DATA INFILE 'file-on-disk' INTO TABLE `tablename` 

command; MYISAM is sensationally fast with this (even with primary keys) and the file can be formatted as csv and you can specify the column names (or you can put NULL as the value for the autoincrement field).

命令; MYISAM 在这方面非常快（即使使用主键），并且文件可以格式化为 csv 并且您可以指定列名（或者您可以将 NULL 作为自动增量字段的值）。

View MYSQL doc here.

在此处查看 MYSQL 文档。

回答by BitKFu

The first Tip I would give you, is to disable the autocommit functionality and than commit manually.

我要给你的第一个提示是禁用自动提交功能，而不是手动提交。

LOCK TABLES a WRITE;
... DO INSERTS HERE
UNLOCK TABLES;

This benefits performance because the index buffer is flushed to disk only once, after all INSERT statements have completed. Normally, there would be as many index buffer flushes as there are INSERT statements.

这有利于性能，因为索引缓冲区只在所有 INSERT 语句完成后刷新到磁盘一次。通常，索引缓冲区刷新与 INSERT 语句一样多。

But propably best you can do, and if that is possible in your application, you do a bulk insert with one single select.

但最好你能做到，如果这在你的应用程序中是可能的，你可以用一个单一的选择进行批量插入。

This is done via Vector Binding and it's the fastest way you can go.

这是通过矢量绑定完成的，这是您可以使用的最快方式。

Instead
of:
"INSERT INTO tableName values()"
DO
"INSERT INTO tableName values(),(),(),().......(n) " ,

But consider this option only if parameter vector binding is possible with your mysql driver you're using.

但是，仅当您正在使用的 mysql 驱动程序可以进行参数向量绑定时，才考虑使用此选项。

Otherwise I would tend to the first possibility and LOCK the table for every 1000 inserts. Don't lock it for 100k inserts, because you'l get a buffer overflow.

否则我会倾向于第一种可能性并为每 1000 次插入锁定表。不要为 10 万次插入锁定它，因为你会得到缓冲区溢出。

回答by Plínio Pantale?o

Can you create one more table with 400 (not null) columns and run your test again? If the number of slow inserts became higher this could indicate MySQL is wasting time writing your records. (I dont know how it works, but he may be alocating more blocks, or moving something to avoid fragmentation.... really dont know)

您能否再创建一个包含 400 个（非空）列的表并再次运行您的测试？如果缓慢插入的数量变得更高，这可能表明 MySQL 正在浪费时间写入您的记录。（我不知道它是如何工作的，但他可能正在分配更多的块，或者移动一些东西以避免碎片......真的不知道）

回答by bigtang

Can you check the stats on the disk subsystem? Is the I/O satuated? This sounds like internal DB work going on flushing stuff to disk/log.

你能检查磁盘子系统的统计信息吗？I/O 是否饱和？这听起来像是内部数据库工作正在将内容刷新到磁盘/日志。

回答by vulkanino

To check if your disk is behaving badly, and if you're in Windows, you can create a batch cmd file that creates 10,000 files:

要检查您的磁盘是否运行不良，如果您使用的是 Windows，您可以创建一个批处理 cmd 文件来创建 10,000 个文件：

@echo OFF
FOR /L %%G IN (1, 1, 10000) DO TIME /T > out%%G.txt

save it in a temp dir, like test.cmd

将其保存在临时目录中，例如 test.cmd

Enable command extensions running CMD with the /E:ON parameter

使用 /E:ON 参数启用运行 CMD 的命令扩展

CMD.exe /E:ON

Then run your batch and see if the time between the first and the last out file differ in seconds or minutes.

然后运行您的批处理并查看第一个和最后一个输出文件之间的时间是否以秒或分钟为单位不同。

On Unix/Linux you can write a similare shell script.

在 Unix/Linux 上，您可以编写类似的 shell 脚本。

回答by Guy Gordon

By any chance is there an SSD drive in the server? Some SSD drives suffer from 'studder', which could cause your symptom.

服务器中是否有SSD驱动器？某些 SSD 驱动器会出现“抖动”，这可能会导致您的症状。

In any case, I would try to find out if the delay is occurring in MySQL or in the disk subsystem.

在任何情况下，我都会尝试找出延迟是否发生在 MySQL 或磁盘子系统中。

What OS is your server, and what file system is the MySQL data on?

您的服务器是什么操作系统，MySQL 数据在什么文件系统上？

回答by Phill Pafford

Read this on Myisam Performance: http://adminlinux.blogspot.com/2010/05/mysql-allocating-memory-for-caches.html

在 Myisam Performance 上阅读：http: //adminlinux.blogspot.com/2010/05/mysql-allocating-memory-for-caches.html

Search for:

搜索：

'The MyISAM key block size The key block size is important' (minus the single quotes), this could be what's going on. I think they fixed some of these types of issues with 5.1

'MyISAM 密钥块大小密钥块大小很重要'（减去单引号），这可能是正在发生的事情。我认为他们在 5.1 中修复了其中一些类型的问题

回答by Manish

if you are using multiple insertion at one using for loop, then please take a break after every loop using PHP's sleep("time in seconds") function.

如果您使用 for 循环在一次使用多个插入，那么请在每次循环后使用 PHP 的 sleep("time in seconds") 函数休息一下。