最近,在一个系统的慢查询日志里发现有个insert操作很慢,达到秒级,并且是比较简单的SQL语句,把语句拿出来到mysql中直接执行,速度却很快。
这种问题一般不是SQL语句本身的问题,而是在具体的应用环境中,由于并发等原因导致的。最可怀疑的地方就是在等待表级锁。
加上监控的日志来看,很多SQL是在同一时间完成的,下面的第三列是结束时间,第四列是开始时间:
14:27:30 bizId30905 1355812050 1355812045 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官网对此参数的描述如下:
If the value of innodb_flush_log_at_trx_commit
is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.
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数据。
从应用的角度,批量提交也是解决问题的办法,当然要在应用场景许可的前提下。
相关推荐
MySQL 数据库中的 `innodb_flush_log_at_trx_commit` 和 `sync_binlog` 是两个非常重要的配置参数,它们直接影响到数据库的性能与数据安全性。理解并合理设置这两个参数对于优化数据库系统至关重要。 首先,`innodb...
可以看到,只有1才能真正地保证事务的持久性,但是由于刷新操作 fsync() 是阻塞的,直到完成后才返回,我们知道写磁盘的速度是很慢的,因此 MySQL 的性能
在网上看了无数的my....结果是只有innodb_flush_log_at_trx_commit可以提高性能,对于1,2,3参数无论是开其中某一个,还是三个同时调节都没有影响到测试性能。我想了下,可能是我的测试数据量还不够大造成的,后续有条
- `innodb_flush_log_at_trx_commit`:决定日志何时刷新到磁盘。 #### 5. 恢复和性能 InnoDB的重做日志对于数据库的恢复和性能至关重要。在数据库崩溃后,InnoDB可以重放日志来恢复未提交的事务和重做已提交的事务...
6. **innodb_flush_log_at_trx_commit**:此参数控制事务日志的持久化策略,影响事务的ACID特性。设置为1确保每次提交都同步到磁盘,牺牲速度以保证强一致性;设置为0或2则可能牺牲一些一致性以换取性能提升。 7. *...
`innodb_flush_log_at_trx_commit` - **描述**:控制事务提交时是否同步写入日志文件,默认值为0。 - **应用场景**:设置为1可增强数据安全性,但可能会降低性能;设置为0则牺牲安全性换取更高性能。 ##### 9. `...
在MySQL的InnoDB存储引擎中,事务的ACID特性是通过多种机制来保证的,其中redo log(重做日志)扮演着至关重要的角色。...通过合理设置`innodb_flush_log_at_trx_commit`参数,可以在性能和数据安全性之间找到平衡。
6. **innodb_flush_log_at_trx_commit**:这个参数决定事务日志何时被刷入磁盘。设置为1表示每次提交事务都会同步到磁盘,确保强一致性但可能影响性能;设置为0或2则牺牲一致性以提高性能,但可能导致数据丢失。 7....
- **innodb_flush_log_at_trx_commit**:控制日志写入磁盘的策略,对于实时业务交易,一般配置为2,既保证了事务的一致性又减少了性能损耗,而设置为0或2时在系统崩溃时可能会有数据丢失的风险。 - **innodb_read_...
[client] port=3306 [mysql] no-beep default-character-set=utf8 [mysqld] datadir=D:/Data port=3306 server-id=...log_file_size=1G innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=2 innodb_file_per_t
总的来说,理解 `innodb_flush_log_at_trx_commit` 参数对 MySQL 性能的影响,以及如何根据业务需求和系统环境来选择合适的值,是优化数据库性能的关键。通过分析 mysqlreport 提供的详细信息,我们可以更深入地了解...
7. `innodb_log_files_in_group`、`innodb_flush_log_at_trx_commit`、`innodb_log_file_size` 和 `innodb_max_dirty_pages_pct` 与事务日志和脏页处理相关,确保事务安全性和性能平衡。 测试工具 Sysbench 被用来...
redo log的持久化策略由参数`innodb_flush_log_at_trx_commit`控制: 1. 当`innodb_flush_log_at_trx_commit=0`时,redo log仅保留在内存中,直到InnoDB后台线程定期刷新到磁盘,丢失数据的风险较高。 2. 当`innodb...
query_cache_size、query_cache_type、innodb_buffer_pool_size、innodb_log_file_size、innodb_log_buffer_size、innodb_flush_logs_at_trx_commit、transaction_isolation、innodb_file_per_table、innodb_open_...
innodb_flush_log_at_trx_commit参数控制了InnoDB如何刷新事务日志。通常,InnoDB使用一个后台线程“log thread”在磁盘上通过fsync()命令来刷新日志。文章还提到了Redo日志文件组中文件的数量,通常是2到3个。 9. ...
6. **innodb_flush_log_at_trx_commit**:决定事务日志何时写入磁盘。值为2时,保证ACID特性,但牺牲性能;值为0或1时,性能较高,但可能丢失部分事务数据。 7. **innodb_read_io_threads** 和 **innodb_write_io_...
* innodb_flush_log_at_trx_commit:日志提交方式(关键参数) + 0 每秒写 1 次日志,将数据刷入磁盘,相当于每秒提交一次事务 + 1 每次提交事务写日志,同时将刷新相应磁盘,默认参数 + 2 每提交事务写一次日志...
5. 磁盘IO优化:通过调整磁盘IO参数,例如sync_binlog、innodb_flush_log_at_trx_commit等,来提高MYSQL数据库的性能。 MYSQL数据库慢SQL定位与分析 MYSQL数据库慢SQL定位与分析是MYSQL数据库性能优化的重要步骤。...