代码之家 › 专栏 › 技术社区 › Daniel

为什么insert查询有时要花这么长时间才能完成?

query-cache performance mysql sql

Daniel · 技术社区 · 16 年前

不

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

没有读取 在这个表上-单线程程序只能串行写入。

例如,这个查询花费了4.194秒(对于insert来说是一个很长的时间):

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命令的缩写版本,我抛出了所有为零的列。)

现在这个更新有大量的上下文切换和小的页面错误。在此数据库上,打开的\u表大约每10秒增加1个(表\u缓存空间没有用完)

MySQL5.0.89版本
硬件:32千兆ram/8核@2.66GHz;raid 10 SCSI硬盘(SCSI II??)
CPU大约有50%空闲。
top report负载平均约10分钟(对于我们的db机器来说是正常的)
交换空间已使用156k(32 GB ram)

我不知道是什么导致了这种性能滞后。这不会发生在我们的低负载奴隶,只有在我们的高负载主机。内存和innodb表也会发生这种情况。有人有什么建议吗?(这是一个生产系统,所以没有异国情调!)

11 回复 | 直到 12 年前

Riedsio 15 年前

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

MySQL版本5.1.48

更新

MySQL innodb system variable innodb_flush_log_at_trx_commit .

我觉得医生有点难看,但是 innodb \u flush \u log \u at \u trx \u commit

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

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

说到这里,我发现服务器 innodb_flush_log_at_trx_commit=0 性能比使用 innodb_flush_log_at_trx_commit=2 . 此外,当我将它切换到2时,糟糕的情况立即得到了改善(注意,您可以动态更改它)。

Jay 16 年前

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

我假设您正在其他一些表上执行多个其他查询,因此问题是MySQL必须处理文件中的磁盘写入,这些文件变得越来越大,并且需要为这些文件分配额外的空间。

如果使用MYISAM表,我强烈建议使用

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

View MYSQL doc here .

BitKFu 16 年前

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

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

但可能是您所能做的最好的事情,如果在您的应用程序中这是可能的,您可以通过一次选择来进行批量插入。

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

但是,只有在您使用的mysql驱动程序可以进行参数向量绑定时,才考虑使用这个选项。

否则我会倾向于第一种可能性,每1000次插入就锁定一个表。不要为100k的插入锁定它,因为你会得到一个缓冲区溢出。

Plínio Pantaleão 16 年前

您能再创建一个包含400(非空)列的表并再次运行测试吗?如果慢插入的数量增加,这可能表明MySQL在浪费时间编写记录。(我不知道它是怎么工作的,但他可能在多放几个街区,或者移动一些东西以避免碎片……)。。。。真的不知道)

fxwan 15 年前

我们找到了完全相同的问题并报告如下: http://bugs.mysql.com/bug.php?id=62381

Phill Pafford 16 年前

http://adminlinux.blogspot.com/2010/05/mysql-allocating-memory-for-caches.html

“MyISAM密钥块大小密钥块大小很重要”(减去单引号),这可能就是正在发生的事情。我认为他们用5.1解决了其中一些问题

bigtang 16 年前

你能检查一下磁盘子系统的数据吗?I/O是否饱和?这听起来像是内部数据库正在将数据刷新到磁盘/日志。

vulkanino 16 年前

要检查磁盘是否运行不正常,如果您在Windows中,可以创建一个创建10000个文件的批处理cmd文件:

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

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

CMD.exe /E:ON

然后运行批处理,看看第一个和最后一个输出文件之间的时间是秒还是分钟。

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

Guy Gordon 16 年前

服务器中是否有SSD驱动器?一些SSD驱动器出现“studer”问题,这可能会导致您的症状。

你的服务器是什么操作系统,MySQL数据在什么文件系统上?

Daniel 15 年前

我们升级到MySQL5.1,在这次事件中,查询缓存成了一个问题,有很多“释放项?”线程状态。然后我们删除了查询缓存。

仅供未来读者参考。

-丹尼尔

Manish 10 年前