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

Question

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

35

这是一个相当简单的问题。通常情况下，将数据插入表格中都能很顺利地进行，但有时会出现插入查询耗时几秒钟的情况。（我并不是在尝试批量插入数据。）因此，我设置了一个模拟插入过程来找出为什么插入查询偶尔需要超过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 GB RAM / 8核心 @ 2.66GHz；RAID 10 SCSI硬盘（SCSI II？？？）
已查询硬盘和RAID控制器：未报告任何错误。CPU约50％空闲。
iostat -x 5（显示硬盘利用率低于10％）
top报告负载平均值为1分钟的10个（对于我们的db机器而言正常）
交换空间已使用156k（32 GB RAM）

我无法找出是什么导致了性能滞后。这种情况在我们的低负载从属机上不会发生，只在我们的高负载主服务器上发生。内存和innodb表也会发生此问题。有人有什么建议吗？（这是一个生产系统，因此不要使用奇怪的方法！）

- Daniel

其他表/数据库是否正常工作？可能有坏驱动器（系统日志中是否有任何I/O错误）？从mysql命令行需要多长时间才能完成查询？mysql mydb < lots-of-inserts.sql呢？ - Paul Schreiber

现在我们在其他表中也遇到了这个问题。我创建了一个样例表，仅对其进行了转储以查看会发生什么。如果我使用mysql mydb < lots-of-inserts.sql，那么就不会对其进行“概要分析”。这种情况只是偶尔发生，但我们的监控软件会捕捉到它。 - Daniel

1

“这个相同的行；100,000次”是什么意思？你是指单个插入需要2秒钟，还是执行相同的插入100,000次只需要2秒钟？ - Jim Garrison

2

我插入了相同的行100,000次。偶尔单个插入需要2秒或更长时间。插入100,000行通常情况下都很顺利，只有1/20,000的概率查询需要超过2秒钟。我将更新问题。 - Daniel

11个回答

1

我在使用INNODB表时遇到了这个问题。（而且INNODB索引比MYISAM还要慢）

我猜你正在对其他表进行多个查询，所以问题可能是MySQL必须处理在文件中的磁盘写入，这些文件变得越来越大，并需要为这些文件分配额外的空间。

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

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

命令; MYISAM对此非常快（即使有主键），文件可以格式化为csv，您可以指定列名（或者可以将NULL作为自增字段的值）。

在此处查看MYSQL文档。

- Jay

1

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

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

这样做有助于提高性能，因为索引缓冲区只在所有INSERT语句完成后刷新一次到磁盘。通常情况下，每个INSERT语句都会导致一次索引缓冲区刷新。

但是，如果您的应用程序允许，最好的方法可能是使用单个SELECT进行批量插入。

这可以通过向量绑定来实现，这是最快的方式。

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

但只有在您使用的MySQL驱动程序可以进行参数向量绑定时，才考虑此选项。

否则，我倾向于第一种可能性，并为每1000个插入锁定表格。不要为100k次插入锁定它，因为会出现缓冲区溢出的情况。

- BitKFu

你使用什么编程语言？也许我可以给你一个例子来说明如何做。 - BitKFu

我正在使用Ruby。然而，我正在进行相同的插入操作100,000次，因为其中有几个需要很长时间。我正在尝试找出其中一些需要很长时间的原因。 - Daniel

你尝试过对表进行LOCK、UNLOCK操作并检查性能表现吗？ - BitKFu

我移除了索引，以免出现索引问题。但是我仍然遇到了相同的性能问题。 - Daniel

我在非生产机器上没有遇到这个问题，这让我认为这是由于临时的“峰值”或其他全局锁随机干扰插入所导致的。这可以解释周期性的插入延迟。或者可能只是代码中其他地方的一两次“批量”写入的不良时间。 - Daniel

我使用显式事务而不是自动提交来帮助自己。谢谢。我在插入中遇到了“查询结束”的问题。 - Andras Gyomrey

1

我们升级到了MySQL 5.1，在此过程中，查询缓存成为了一个问题，出现了很多“Freeing items?”线程状态。然后我们移除了查询缓存。

无论是升级到MySQL 5.1还是移除查询缓存都解决了这个问题。

供未来读者参考。

-丹尼尔

- Daniel

1

如果您正在使用PHP的for循环进行多次插入操作，请在每个循环后使用sleep("秒数")函数暂停一下。

- Manish

1

你能再创建一个有 400 个（非空）列的表并运行测试吗？如果慢插入的数量增加了，这可能意味着 MySQL 在写入记录时浪费了时间。（我不知道它是如何工作的，但它可能分配了更多块，或移动某些内容以避免碎片化……真的不知道）

- Plínio Pantaleão

我添加了更多的列，发现写入速度非常慢。我在想这个延迟是否与将数据写入硬盘有关。我们的硬盘似乎具有性能容量。 - Daniel

每秒34次写入。对于非索引表来说，这相当慢！ - Daniel

我不知道，但你可以运行一些MySQL碎片整理工具，看看是否有帮助。我认为MySQL正在浪费你的时间寻找放置记录的良好位置... - Plínio Pantaleão

1

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

我们正在使用5.1.52，但尚未找到解决方案。我们可能需要关闭QC以避免这种性能损失。

- fxwan

0

关于Myisam性能的文章： http://adminlinux.blogspot.com/2010/05/mysql-allocating-memory-for-caches.html 搜索以下内容：

“ MyISAM键块大小，键块大小很重要”（不包括单引号），可能是问题所在。我认为他们在5.1中解决了其中一些这类问题。

- Phill Pafford

问题在于，即使删除了键，这种情况仍然会发生。也就是说，没有任何索引的表。 - Daniel

可以理解为MySQL会预留一定的空间来写入记录，比如说它预留了足够的空间来写入5条记录，当这些空间被用完后，MySQL需要为下一组记录预留另一个空间块。这可能是导致额外执行时间的原因之一。 - Phill Pafford

0

你能检查一下磁盘子系统的统计数据吗？I/O 是否已经饱和了？这听起来像是内部数据库工作，将内容刷新到磁盘/日志中。

- bigtang

嗯，我无法获得IO的精确测量。但是我运行了“iostat -x 5”，在运行时没有看到任何关于5-10％的内容。可能会有暂时的峰值，由于“平均”，这些峰值可能被隐藏。我不知道如何查询系统以获得更精细的测量，除了观察vmstat，但它也没有提供任何有用的信息。 - Daniel

我曾经在SQLServer中遇到过性能问题，有时候会出现奇怪的情况 - 当不是我的代码（哈哈）时，通常是磁盘子系统的问题。 SQLServer有一个恢复间隔参数，它会将事物刷新到磁盘上。如果你真的在插入数据并且达到了恢复间隔，性能会受到很大的影响，这会导致应用程序中的某些插入操作需要很长时间，实际上与插入操作本身无关，而是由于SQLServer内部的一些清理工作 - 还可能是磁盘通道 - 磁盘和/或磁盘控制器只是达到了100%。 - bigtang

0

检查磁盘是否表现不佳，如果您在Windows操作系统中，可以创建一个批处理cmd文件来创建10,000个文件：

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

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

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

CMD.exe /E:ON

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

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

- vulkanino

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Riedsio · Accepted Answer

我在我的系统上也注意到了同样的现象。通常只需要一毫秒的查询，突然会需要1-2秒的时间。在我所有的案例中，都是简单的单表插入/更新/替换语句，而不是任何选择语句。没有负载、锁定或线程积聚的迹象。

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

排除的原因

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

此时我唯一的其他观察结果来自于一个事实，即我在多台机器上运行相同的数据库。由于我有一个大量读取的应用程序，所以我使用带有复制的环境——大部分负载在从库上。我注意到即使主服务器上负载很少，这种现象也经常发生在那里。尽管我看不到锁定问题，也许是Innodb/Mysql在(线程)并发方面有问题？请记住，从库上的更新将是单线程的。

MySQL版本5.1.48

更新

我认为我已经找到了我案例中的问题线索。在我的一些服务器上，我注意到这种现象比其他服务器更多。看到不同服务器之间的差异，并进行调整后，我找到了MySQL innodb系统变量 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时，问题立即得到了改善（请注意，您可以在运行时更改它）。

那么，我的问题是，您的设置是什么？请注意，我不是谴责此参数，而是强调它与此问题相关。