文章目录

  • 说明
  • innodb_buffer_pool_size
  • innodb_buffer_pool_size的设置
  • InnoDB缓冲池性能
  • innodb_log_file_size
  • logfile的作用
  • 检查当前logfile的设置
  • logfile的推荐大小
  • 改变logfile的大小
  • innodb_flush_log_at_trx_commit
  • sync_binlog
  • max_connections


说明

本章介绍MySQL 5.7 常用参数的设置

innodb_buffer_pool_size

innodb_buffer_pool_size的设置

  1. innodb_buffer_pool_size默认大小为128M。 在专用数据库服务器上，可以将缓冲池大小设置为服务器物理内存的80%。
  2. 缓冲池大小必须始是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。
  3. 从 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的大小

  1. L修改innodb_log_file_size参数，例如把innodb_log_file_size设置为100M， 添加到配置文件/etc/mysql/my.cnf中；
  2. 干净的关闭MySQL， mysqladmin shutdown。
  3. 删除当前logifle， rm /var/lib/mysql/ib_logfile?
  4. 重新启动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

  1. sync_binlog=0:用操作系统机制进行缓冲数据同步
  2. sync_binlog=1:表示采用同步写磁盘的方式来写二进制日志
  3. 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。