修改innodb_flush_log_at_trx_commit参数提升insert的性能
问题描述:
查看系统的慢查询日志发现有个insert操作很慢,达到秒级,并且是比较简单的SQL语句,把语句拿出来到mysql中直接执行,速度却很快。
原因分析:
这种问题一般不是SQL语句本身的问题,而是在具体的应用环境中,由于并发等原因导致的。最可怀疑的地方就是在等待表级锁。
加上监控的日志来看,很多SQL是在同一时间完成的,下面的第三列是结束时间,第四列是开始时间:
14:27:30 bizId28907 1355812050 1355812043
14:27:30 bizId30905 1355812050 1355812047
14:27:30 bizId17388 1355812050 1355812040
14:27:30 bizId40563 1355812050 1355812044
14:27:30 bizId15477 1355812050 1355812048
14:27:30 bizId32588 1355812050 1355812048
但是通过应用的分析来看,并不存在表级锁的地方,而insert自身的操作也只是对要插入的记录本身加锁,不会影响其他并发的insert操作。
只能在MySQL写入磁盘的性能上考虑,MySQL有个innodb_flush_log_at_trx_commit参数,用来配置flush log到磁盘的时机。
具体就是从log buffer写到log file,并写入到磁盘上的时机。这个参数的默认值是1,即每次事务提交的时候会把日志刷到磁盘,而频繁的insert操作就会引起flush log操作的不断积累,进而引发性能问题。
在应用数据可接受的前提下,可以把这个值改成0,就是每秒才操作一次。修改后潜在的问题是,在事务已经提交的情况下,如果尚未写入磁盘的时候发生故障,可能丢失数据。
MySQL官网对innodb_flush_log_at_trx_commit参数参数的描述:
The default value of 1 is required for full ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose up to one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, only an operating system crash or a power outage can erase the last second of transactions. InnoDB‘s crash recovery works regardless of the value.
另外,还可以参考其它角度的优化办法:
如果是MyISAM存储引擎,可以使用insert delay的方式来提高性能,其原理是MySQL自身会在内存中维护一个insert队列,在实际表空闲的时候insert数据。
最后一个方面,就是从应用的角度,批量提交也是解决问题的办法,当然要在应用场景许可的前提下。
您可能感兴趣的文章:
修改innodb_flush_log_at_trx_commit参数提升insert的性能
mysql 优化innodb_flush_log_at_trx_commit的案例介绍
优化mysql insert性能的方法
mysql innodb优化配置方法分享
MySQL性能调优和使用新手必读教程
mysql占用虚拟内存达8百多兆的解决方法
Mysql大指量插入数据时SQL语句的优化
mysql innodb数据库引擎解析
MySQL常见错误代码解析
Mysql内存表有什么作用