tangjianft 2020-02-15
select @@innodb_buffer_pool_size; +---------------------------+ | @@innodb_buffer_pool_size | +---------------------------+ | 1073741824 | +---------------------------+ select 1073741824/1024/1024; +----------------------+ | 1073741824/1024/1024 | +----------------------+ | 1024.00000000 | +----------------------+ # innodb_buffer_pool_size 一般建议最多是物理内存的 75-80%
主要控制了 innodb 将 log buffer 中的数据写入日志文件并 flush 磁盘的时间点,取值分别为0、1、2三个。
select @@innodb_flush_log_at_trx_commit; +----------------------------------+ | @@innodb_flush_log_at_trx_commit | +----------------------------------+ | 1 | +----------------------------------+ 参数说明: 1,每次事物的提交都会引起日志文件写入、flush 磁盘的操作,确保了事务的ACID;flush 到操作系统的文件系统缓存 fsync到物理磁盘. 0,表示当事务提交时,不做日志写入操作,而是每秒钟将 log buffer 中的数据写入文件系统缓存并且秒 fsync 磁盘一次; 2,每次事务提交引起写入文件系统缓存,但每秒钟完成一次fsync磁盘操作。 -------- The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit. With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash. With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash. -------
可以参考官方文档:https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_method
该选项控制的是 log buffer 和 data buffer 刷写磁盘的时候是否经过文件系统缓存
查看现状的策略
show variables like '%innodb_flush%'; # 参数说明 O_DIRECT : 数据缓冲区写磁盘,不走 OS buffer fsync : 日志和数据缓冲区写磁盘,都走 OS buffer O_DSYNC : 日志缓冲区写磁盘,不走 OS buffer
使用建议
最高安全模式: innodb_flush_log_at_trx_commit=1 Innodb_flush_method=O_DIRECT 最高性能: innodb_flush_log_at_trx_commit=0 Innodb_flush_method=fsync