说明
本章介绍MySQL 5.7 常用参数的设置
innodb_buffer_pool_size
innodb_buffer_pool_size的设置
innodb_buffer_pool_size默认大小为128M。 在专用数据库服务器上，可以将缓冲池大小设置为服务器物理内存的80%。
缓冲池大小必须始是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。
从 5.7 后，此参数可以动态调整;
mysql> set @@global.innodb_buffer_pool_size=512*1024*1024; Query OK, 0 rows affected (0.01 sec)
缓冲池大小调整进度也记录在服务器错误日志中
~# tail -f /var/log/mysql/error.log ...... 2020-01-03T06:35:28.708835Z 0 [Note] InnoDB: Resizing buffer pool from 134217728 to 536870912 (unit=134217728). 2020-01-03T06:35:28.709074Z 0 [Note] InnoDB: Disabling adaptive hash index. 2020-01-03T06:35:28.710335Z 952 [Note] InnoDB: Disabling adaptive hash index. (new size: 536870912 bytes) 2020-01-03T06:35:28.724755Z 0 [Note] InnoDB: disabled adaptive hash index. 2020-01-03T06:35:28.724829Z 0 [Note] InnoDB: Withdrawing blocks to be shrunken. 2020-01-03T06:35:28.724875Z 0 [Note] InnoDB: Latching whole of buffer pool. 2020-01-03T06:35:28.724966Z 0 [Note] InnoDB: buffer pool 0 : resizing with chunks 1 to 4. 2020-01-03T06:35:28.857278Z 0 [Note] InnoDB: buffer pool 0 : 3 chunks (24576 blocks) were added. 2020-01-03T06:35:28.857404Z 0 [Note] InnoDB: Resizing hash tables. 2020-01-03T06:35:28.864089Z 0 [Note] InnoDB: buffer pool 0 : hash tables were resized. 2020-01-03T06:35:28.864200Z 0 [Note] InnoDB: Resizing also other hash tables. 2020-01-03T06:35:28.924476Z 0 [Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary. 2020-01-03T06:35:28.924587Z 0 [Note] InnoDB: Completed to resize buffer pool from 134217728 to 536870912. 2020-01-03T06:35:28.924639Z 0 [Note] InnoDB: Re-enabled adaptive hash index. 2020-01-03T06:35:28.924723Z 0 [Note] InnoDB: Completed resizing buffer pool at 200103 14:35:28.
InnoDB缓冲池性能
可以使用以下公式计算InnoDB缓冲池性能：
InnoDB buffer pool 命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100
innodb_buffer_pool_reads：表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。
去查innodb_buffer_pool_read_requests：表示从内存中读取逻辑的请求数。
mysql> show status like 'innodb_buffer_pool_read%'; +---------------------------------------+--------+ | Variable_name | Value | +---------------------------------------+--------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 0 | | Innodb_buffer_pool_read_ahead_evicted | 0 | | Innodb_buffer_pool_read_requests | 935387 | | Innodb_buffer_pool_reads | 469 | +---------------------------------------+--------+ 5 rows in set (0.01 sec)
可以使用show engine innodb status \G命令查询缓冲区使用情况
mysql> show engine innodb status \G ...... ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 137428992 Dictionary memory allocated 116177 Buffer pool size 8192 Free buffers 7744 Database pages 448 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 414, created 34, written 36 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 805 / 1000, young-making rate 0 / 1000 not 0 / 1000 ......
Free buffers :表示有多少空闲buffer。如果 此值长时间都较高，则可以考虑减小InnoDB缓冲池大小。
innodb_log_file_size
logfile的作用
logfile大小对于性能的影响主要体现在checkpoint上，一般太小的logfile size的表现情况就是checkpoint比较频繁，导致刷新dirty page到磁盘的次数增加，在刷新时会使整个系统变得很慢，所以这种情况要尽量避免。
检查当前logfile的设置
root@scutech:~# ll -h /var/lib/mysql/ib_logfile* -rw-r----- 1 mysql mysql 48M Jan 3 14:51 /var/lib/mysql/ib_logfile0 -rw-r----- 1 mysql mysql 48M Jan 2 13:56 /var/lib/mysql/ib_logfile1 root@scutech:~# mysqladmin variables |grep innodb_log_file | innodb_log_file_size | 50331648 | | innodb_log_files_in_group | 2
logfile的推荐大小
可以通过engine innodb的状态来查看当前的 log和checkpoint的位置之差，确定checkpoint是否频繁。
mysql> show engine innodb status\G; ........... LOG --- Log sequence number 125715587 Log flushed up to 125715165 Pages flushed up to 118193770 Last checkpoint at 118129836 ..........
官方文档建议最大当前的log序号到最后一次的checkpoint不要超过logfile总和的0.75，logfile的总和是innodb_log_files_in_group*innodb_log_file_size的0.75， 通常innodb_log_files_in_group的
default值为2。 一般太小的logfile size的表现情况就是checkpoint比较频繁，导致刷新dirty page到磁盘的次数增加，在刷新时会使整个系统变得很慢。
另一种算法就logfile的总和应该是1-2小时产生的redo，检查一下一分钟产生的redo大小。
mysql> show engine innodb status\G;select sleep(60); show engine innodb status\G ...... Log sequence number 158544688 ...... Log sequence number 164552482 ......
计算一下一个小时产生多少M的redo。
mysql> select (164552482-158544688)*60/1024/1024; +-------------------------------------+ | ( 164552482-158544688)*60/1024/1024 | +-------------------------------------+ | 343.76873016 | +-------------------------------------+ 1 row in set (0.00 sec)
这样的压力可以把logfile设置为300M到600M。
改变logfile的大小
L修改innodb_log_file_size参数，例如把innodb_log_file_size设置为100M， 添加到配置文件/etc/mysql/my.cnf中；
干净的关闭MySQL， mysqladmin shutdown。
删除当前logifle， rm /var/lib/mysql/ib_logfile?
重新启动MySQL；
检查
# ll -h /var/lib/mysql/ib_logfile? -rw-r----- 1 mysql mysql 100M Jan 3 16:07 /var/lib/mysql/ib_logfile0 -rw-r----- 1 mysql mysql 100M Jan 3 16:07 /var/lib/mysql/ib_logfile1
innodb_flush_log_at_trx_commit
参数说明，innodb_flush_log_at_trx_commit = N：
N=0 每隔一秒，把事务日志缓存区的数据写到日志文件中，以及把日志文件的数据刷新到磁盘上，log buffer会每秒写入到日志文件并刷写（flush）到磁盘。但每次事务提交不会有任何影响，也就是 log buffer 的刷写操作和事务提交操作没有关系。在这种情况下，MySQL性能最好，但如果 mysqld 进程崩溃，通常会导致最后 1s 的日志丢失。
N=1 每个事务提交时候，把事务日志从缓存区写到日志文件中，并且刷新日志文件的数据到磁盘上， 当取值为 1 时，每次事务提交时，log buffer 会被写入到日志文件并刷写到磁盘。这也是默认值。这是最安全的配置，但由于每次事务都需要进行磁盘I/O，所以也最慢。
N=2 每事务提交的时候，把事务日志数据从缓存区写到日志文件中；每隔一秒，刷新一次日志文件，但不一定刷新到磁盘上，而是取决于操作系统的调度，当取值为 2 时，每次事务提交会写入日志文件，但并不会立即刷写到磁盘，日志文件会每秒刷写一次到磁盘。这时如果 mysqld 进程崩溃，由于日志已经写入到系统缓存，所以并不会丢失数据；在操作系统崩溃的情况下，通常会导致最后 1s 的日志丢失。
这个参数可以动态修改。
sync_binlog
sync_binlog=0:用操作系统机制进行缓冲数据同步
sync_binlog=1:表示采用同步写磁盘的方式来写二进制日志
sync_binlog=[N]表示每写缓冲多次就同步到磁盘
Default Value (>= 5.7.7) 1
Default Value (<= 5.7.6) 0
max_connections
MySQL的max_connections参数用来设置最大连接（用户）数。每个连接MySQL的用户均算作一个连接，max_connections的默认值为151。如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，但这建立在机器能支撑的情况下，因为如果连接数越多，MySQL为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。数值过小会经常出现ERROR 1040: Too many connections错误。
mysql> show status like 'Threads%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 0 | | Threads_connected | 32 | ###这个数值指的是打开的连接数 | Threads_created | 10539 | | Threads_running | 21 | ###这个数值指的是激活的连接数，这个数值一般远低于connected数值 +-------------------+-------+ 4 rows in set (0.01 sec) mysql> show variables like 'max_conne%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 100 | | max_connections | 151 | +--------------------+-------+ 2 rows in set (0.01 sec) mysql> show status like 'max%connections'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Max_used_connections | 102 | +----------------------+-------+ 1 row in set (0.01 sec)
max_used_connections / max_connections * 100% （理想值≈ 85%） MySQL无论如何都会保留一个用于管理员（SUPER）登陆的连接，用于管理员连接数据库进行维护操作，即使当前连接数已经达到了max_connections。因此MySQL的实际最大可连接数为max_connections+1。
