innodb_buffer_pool_size

介绍：配置缓冲池的大小，在内存允许的情况下，DBA往往会建议调大这个参数，越多数据和索引放到内存里，数据库的性能会越好。海尔生产48G。默认是取内存的百分之60-百分之80

Buffer Pool本质其实就是数据库的一个内存组件，你可以理解为他就是一片内存数据结构，所以这个内存数据结构肯定是有一定的大小的，不可能是无限大的。

Buffer Pool决定了 innodb 存储引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下，innodb_buffer_pool_size 的值越大，缓存命中率越高，访问InnoDB表需要的磁盘I/O 就越少，性能也就越高。

这个Buffer Pool默认情况下是128MB，还是有一点偏小了，我们实际生产环境下完全可以对Buffer Pool进行调整。

比如我们的数据库如果是16核32G的机器，那么你就可以给Buffer Pool分配个2GB的内存，使用下面的配置就可以了。

innodb_buffer_pool_size = 2147483648

innodb-buffer-pool-size 推荐设置到 70%-80% 的内存。

Buffer Pool的预热机制

预热机制实际上是想让重启后的MySQL快速适应大规模的流量请求。

InnoDB 在服务器关闭时为每个缓冲池保存一部分最近高频使用的页面，并在服务器启动时恢复这些页面。

保存多大比例的缓存页由参数innodb_buffer_pool_dump_pct控制。

在启动时还原缓冲池，实际上会缩短预热的时间。

你可以通过下面的方式配置该参数

# 通过命令

SET GLOBAL innodb_buffer_pool_dump_pct=40;

# 通过文件

[mysqld]=40

参数innodb_buffer_pool_dump_at_shutdown控制 MySQL关闭时保存缓冲池的状态，默认为on的状态。

启动参数--innodb-buffer-pool-load-at-startup 表示启动MySQL的时候恢复缓冲池中的状态，默认也是开启的

设置ChangeBuffer大小

我们可以通过innodb_change_buffer_max_size来动态设置change buffer占用的内存大小，默认25。

假设参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的 50%。

changebuffer加速读:减少随机IO访问

假设需要向表T插入数据:insert into T (user_id,name) values(23,"张三");

则过程如下：

情景一：插入的数据在bufferPool的缓存页中

若user_id为唯一索引：找到user_id为22和24之间的位置，判断没有冲突，则插入数据；

若user_id为普通索引：找到user_id为22和24之间的位置，插入数据；

情景二：插入的数据不在bufferPool的缓存页中

若user_id为唯一索引：将数据从磁盘读到内存，找到user_id为22和24之间的位置，判断没有冲突，则插入数据；

若user_id为普通索引：将数据插入到change buffer；

所以在普通索引场景下，change buffer可以减少磁盘读入内存随机IO的访问，对更新性能有明显的提升。

redolog加速写:将随机写磁盘变成了顺序写磁盘。

扩大redologbuffer

redologbuffer决定了innodb重做日志缓存的大小，对于可能产生大量更新记录的大事务。

增加innodb_log_buffer_size的大小，可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作。

redo log buffer大小是通过设置innodb_log_buffer_size实现的。

redo log buffer会周期性的flush到磁盘的log file中。一个大的redo log buffer允许大事务在commit之前不写入磁盘的redo log。

因此，如果你有事务需要update,insert,delete许多记录，增加你的log buffer来节省磁盘I/O。

我们可以通过启动参数innodb_log_buffer_size来指定log buffer的大小,我们生产是8G。

SHOW variables like 'innodb_log_buffer_size' ;

8388608/1024/1024=8GB

redo log日志文件配置

日志文件的大小有两个参数决定：

（1）innodb_log_files_in_group：表示一个组里有多少个文件，默认为2。

（2）innodb_log_file_size：表示单个日志文件的大小，默认为48MB。

redo log太小的话，会导致很快就被写满，然后不得不强行刷redo log，这样WAL机制的能力就发挥不出来了。所以，如果是现在常见的几个TB的磁盘的话，就不要太小气了，直接将redo log设置为4个文件、每个文件1GB吧。

一般情况下上述的4*1GB已经够用了,但是具体的业务场景要具体分析,InnoDB的日志产生量是衡量数据库繁忙程度的重要指标，也是设置日志文件大小的依据。一个合理大小的日志文件应该可以容纳数据库在高峰时1到2个小时的数据变化。

参考:blog.51cto.com/u_13874232/…

双1配置

redo log用于保证crash-safe能力。innodb_flush_log_at_trx_commit这个参数设置成1的时候，表示每次事务的redo log都直接持久化到磁盘。这个参数我建议你设置成1，这样可以保证MySQL异常重启之后数据不丢失。

sync_binlog这个参数设置成1的时候，表示每次事务的binlog都持久化到磁盘。这个参数我也建议你设置成1，这样可以保证MySQL异常重启之后binlog不丢失。

参数 innodb_flush_log_at_trx_commit

如果innodb_flush_log_at_trx_commit设置为0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行.该模式下，在事务提交的时候，不会主动触发写入磁盘的操作;

如果innodb_flush_log_at_trx_commit设置为1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去; 如果

innodb_flush_log_at_trx_commit设置为2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。

注意：由于进程调度策略问题,这个"每秒执行一次 flush(刷到磁盘)操作"并不是保证100%的"每秒"。

redo log刷盘策略总结

0:延迟写。效率最高,最不安全:log_buff —> 每隔1秒 —> os cache —>实时---—> disk

1:实时写,实时刷。效率最低,最安全:log_buff —> 实时 —> os cache —>实时---—> disk

2:实时写,延迟刷。效率折中,安全折中:log_buff —> 实时 —> os cache —>每隔1秒—> disk

参数 sync_binlog

sync_binlog 的默认值是0，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。

sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync。

当sync_binlog =N (N>0) ，MySQL 在每写 N次二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

注意：如果启用了autocommit，那么每一个语句statement就会有一次写操作；否则每个事务对应一个写操作。

性能

两个参数在不同值时对db的纯写入的影响表现如下：

测试场景1

innodb_flush_log_at_trx_commit=2 sync_binlog=1000,场景1的TPS:41000

测试场景2

innodb_flush_log_at_trx_commit=1 sync_binlog=1000,场景2的TPS:33000

测试场景3

innodb_flush_log_at_trx_commit=1 sync_binlog=1,场景3的TPS:26000

测试场景4

innodb_flush_log_at_trx_commit=1 sync_binlog=1000,场景4的TPS:33000

由此可见，当两个参数设置为双1的时候，写入性能最差。

在sync_binlog=N (N>1 ),innodb_flush_log_at_trx_commit=2时,MySQL写操作才能达到最高性能。

安全

当innodb_flush_log_at_trx_commit和sync_binlog 都为 1 时是最安全的。

在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。

但是鱼与熊掌不可兼得，双11 会导致频繁的io操作，因此该模式也是最慢的一种方式。

当innodb_flush_log_at_trx_commit设置为0，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。

当innodb_flush_log_at_trx_commit设置为2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。

"双1设置"适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如订单,交易,充值,支付消费系统。

双1模式下，当磁盘IO无法满足业务需求时比如11.11 活动的压力。

推荐的做法是 innodb_flush_log_at_trx_commit=2 ，sync_binlog=N (N为500 或1000) 且使用带蓄电池后备电源的缓存cache，防止系统断电异常。

小结

系统性能和数据安全是业务系统高可用稳定的必要因素。我们在对系统的优化需要寻找一个平衡点，合适的才是最好的，根据不同的业务场景需求，可以将两个参数做组合调整，以便是db系统的性能达到最优化。

47.mysql优化-配置篇