参数详解

1. mysql参数详解

目录

• 1. mysql参数详解
  • 1.1. 重要参数
    • 1.1.1. innodb_buffer_pool_size
    • 1.1.2. innodb_log_file_size
    • 1.1.3. innodb_buffer_pool_instances
    • 1.1.4. transaction_isolation
    • 1.1.5. report_host 和report_port
    • 1.1.6. max_connections
    • 1.1.7. wait_timeout和interactive_timeout
    • 1.1.8. lock_wait_timeout
    • 1.1.9. innodb_lock_wait_timeout
    • 1.1.10. Slow query log
    • 1.1.11. log_slow_extra
    • 1.1.12. lower_case_table_names
    • 1.1.13. Binary log
    • 1.1.14. sync_binlog
    • 1.1.15. innodb_flush_log_at_trx_commit
    • 1.1.16. Relay log
    • relay_log_recovery
    • 1.1.17. log_slave_updates
    • 1.1.18. innodb_io_capacity
    • 1.1.19. innodb_flush_neighbors
    • innodb_flush_method
    • 1.1.20. innodb_read_io_threads和innodb_write_io_threads
    • 1.1.21. innodb_page_cleaners
    • 1.1.22. innodb_status_file
    • 1.1.23. performance_schema
    • 1.1.24. tmpdir
    • 1.1.25. slave-load-tmpdir
• 当 slave 执行 load data infile 时用
  • • 1.1.26. skip options
• 禁止 MySQL 对外部连接进行 DNS 解析，使用这一选项可以消除 MySQL 进行 DNS 解析的时间。但需
  • • • 1.1.26.1. skip-name-resolve
• grant 时，必须使用 ip 不能使用主机名
  • • • 1.1.26.2. skip-symbolic-links
• 不能使用连接文件,禁用符号链接以防止各种安全风险
  • • 1.1.27. skip-external-locking
• 不使用系统锁定，要使用 myisamchk,必须关闭服务器 ,避免 MySQL的外部锁定，减少出错几率增强稳定性。
• 开启该选项可以彻底关闭 MySQL 的 TCP/IP
  • • 1.1.28. skip-networking
• 开启该选项可以彻底关闭 MySQL 的 TCP/IP 连接方式，如果 WEB 服务器是以远程连接的方式访问 MySQL 数据库服务器则不要开启该选项！否则将无法正常连接！如果所有的进程都是在同一台服务器连接到本地的 mysqld, 这样设置将是增强安全的方法
  • • 1.1.29. back_log = 50
• 指定 MySQL 可能的连接数量。当 MySQL 主线程在很短的时间内接收到非常多的连接请求，该参数生效，主线程花费很短的时间检查连接并且启动一个新线程。
  • • 1.1.30. 连接超时相关
• 指定 MySQL 允许的最大连接进程数。如果在访问数据库时经常出现 Too Many Connections 的错误提 示，则需要增大该参数值。
• open_files_limit = 10240
• 指定一个请求的最大连接时间，对于 4GB 左右内存的服务器可以设置为 5-10
  • • 1.1.31. max_allowed_packet = 64M
    • 1.1.32. table_cache = 512
• 打开一个表的开销可能很大。例如MyISAM把MYI文件头标志该表正在使用中。你肯定不希望这种操作太频繁，所以通常要加大缓存数量，使得足以最大限度地缓存打开的表。它需要用到操作系统的资源以及内存，对当前的硬件配置来说当然不是什么问题了。如果你有200多个表的话，那么设置为 1024 也许比较合适(每个线程都需要打开表)，如果连接数比较大那么就加大它的值。我曾经见过设置为100,000的情况。
  • • 1.1.33. thread_cache_size
    • 1.1.34. MySQL innodb_autoinc_lock_mode
      • 1.1.34.1. 我们先对insert做一下分类
      • 1.1.34.2. innodb_autoinc_lock_mode 的说明
        • 1.1.34.2.1. innodb_auto_lockmode有三个取值：
        • 1.1.34.2.2. tradition(innodb_autoinc_lock_mode=0) 模式:
        • 1.1.34.2.3. consecutive(innodb_autoinc_lock_mode=1) 模式:
        • 1.1.34.2.4. interleaved(innodb_autoinc_lock_mode=2) 模式

1.1. 重要参数

1.1.1. innodb_buffer_pool_size

参数含义：
缓存数据和索引的地方, # 限制Innodb能打开的表的数据，如果库里的表特别多的情况，请增加这个。这个值默认是300

MySQL默认配置文件my-innodb-heavy-4G（中设置的是innodb_buffer_pool_size = 2G）
# InnoDB使用一个缓冲池来保存索引和原始数据, 不像MyISAM.
# 这里你设置越大,你在存取表里面数据时所需要的磁盘IO越少.
# 在一个独立使用的数据库服务器上,你可以设置这个变量为服务器物理内存大小的80%
# 不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.
# 注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制,
# 所以不要设置的太高.

innodb_buffer_pool_size的值我们通常配置为系统的70%左右，但是实际情况并不一定都适用，我们如果要明确的知道当前配置的参数值的大小，我们必须了解以下参数：

1. innodb_buffer_pool_reads
2. innodb_buffer_pool_read_requests

innodb_buffer_pool_reads:  表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取.
innodb_buffer_pool_read_requests:  表示从内存中读取逻辑的请求数。

通过查看从磁盘读取或者从内存中读取的情况，确定改系统的内存参数配置是否合理：
Perfonmance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100

比值越小越好
比值小：表示从磁盘中直接读取的数据越少，那么表示内存充足，可以适当的减少内存值
比值大：表示大量数据需要从磁盘中读取，这样I/O就会很高，这是表示内存不足，可以适当的增大改值

查看内存配置情况：
show engine innodb status\G

1.1.2. innodb_log_file_size

参数含义：
缓存重做日志。 默认值是64M， 建议通过查看innodb_log_wait，调整innodb_log_buffer_size大小。

优化建议：
此参数确定数据日志文件的大小，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间
如果是非生产环境，测试极限性能时，尽量调大日志文件。如果是商用场景，需要考虑数据恢复时间，综合折中后设置日志文件大小。

innodb_log_file_size = 2G

1.1.3. innodb_buffer_pool_instances

innodb_buffer_pool_instances = 8

开启多个内存缓冲池，把需要缓冲的数据hash到不同的缓冲池中，这样可以并行的内存读写。建议设置8~16。

1.1.4. transaction_isolation

默认的事务隔离级别

transaction_isolation = READ-COMMITTED		# 尽量规避Gap lock

1.1.5. report_host 和report_port

如果需摇做数据校验，那么以下参数必须要配置， 否者无法做数据校验

report_host = 10.25.247.204
report_port = 3307

1.1.6. max_connections

max_connections是指MySql的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySql会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小。

max_connections = 3000
max_connect_errors = 1000000

在线设置

mysql 5.7以及之后的版本，root用在连接数据用完之后任然可以连接到数据库
set global max_connections=xxx;

mysql5.7之前的版本，连接数用完，root也无法登陆的处理方法
gdb -p $(pidof mysqld) -ex "set max_connections=1500" -batch
使用 gdb 临时调大 参数 max_connections 。

1.1.7. wait_timeout和interactive_timeout

wait_timeout参数值，默认的8小时, 单位秒
说得比较通俗一点，就是当你的MySQL连接闲置超过一定时间后将会被强行关闭。MySQL默认的wait-timeout 值为8个小时，可以通过命令show variables like 'wait_timeout'查看结果值;。

设置这个值是非常有意义的，比如你的网站有大量的MySQL链接请求（每个MySQL连接都是要内存资源开销的 ），由于你的程序的原因有大量的连接请求空闲啥事也不干，白白占用内存资源，或者导致MySQL超过最大连接数从来无法新建连接导致“Too many connections”的错误。在设置之前你可以查看一下你的MYSQL的状态（可用show processlist)，如果经常发现MYSQL中有大量的Sleep进程，则需要 修改wait-timeout值了。

interactive_timeout：服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。

wait_timeout:服务器关闭非交互连接之前等待活动的秒数。在线程启动时，根据全局wait_timeout值或全局 interactive_timeout值初始化会话wait_timeout值，取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义).
这两个参数必须配合使用。否则单独设置wait_timeout无效

空闲会话等待时间，
wait_timeout = 600
interactive_timeout = 600

1.1.8. lock_wait_timeout

不宜随便修改该值大小， 过多锁等待容易造成死锁和系统压力过大

innodb_lock_wait_timeout = 10

1.1.9. innodb_lock_wait_timeout

InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。InnoDB用LOCK TABLES语句注意到锁定设置。默认值是50秒

The length of time in seconds an InnoDB transaction waits for a row lock before giving up
innodb使用这个参数能够有效避免在资源有限的情况下产生太多的锁等待；指的是事务等待获取资源时等待的最长时间，超过这个时间还未分配到资源则会返回应用失败；参数的时间单位是秒，最小可设置为1s(一般不会设置得这么小)，最大可设置1073741824秒（34年，一条语句锁等待超过30分钟估计业务该有反馈了），默认安装时这个值是50s，超过这个时间会报 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

1.1.10. Slow query log

slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow3307.log
long_query_time = 0.1
log_queries_not_using_indexes =1

slow_query_log=1       #是否启用慢查询日志，1为启用，0为禁用  
slow_query_log_file=slow.log       #指定慢查询日志文件的路径和名字，可使用绝对路径指定；默认值是'主机名_slow.log'，位于datadir目录  
long_query_time=2     #SQL语句运行时间阈值，执行时间大于参数值的语句才会被记录下来  
min_examined_row_limit=100     #SQL语句检测的记录数少于设定值的语句不会被记录到慢查询日志，即使这个语句执行时间超过了long_query_time的阈值  
log_queries_not_using_indexes=1   #将没有使用索引的语句记录到慢查询日志  
log_throttle_queries_not_using_indexes=10     #设定每分钟记录到日志的未使用索引的语句数目，超过这个数目后只记录语句数量和花费的总时间  
log-slow-admin-statements=1    #记录执行缓慢的管理SQL，如alter table,analyze table, check table, create index, drop index, optimize table, repair table等。  
log_slow_slave_statements=0    #记录从库上执行的慢查询语句  
log_timestamps=system     #5.7版本新增时间戳所属时区参数，默认记录UTC时区的时间戳到慢查询日志，应修改为记录系统时区  
log_output=FILE,TABLE     #指定慢查询日志的输出方式，从5.5版本开始可以记录到日志文件(FILE，慢查询日志)和数据库表(TABLE，mysql.slow_log)中 
log_slow_extra    #记录慢查询的额外信息（8.0.14开始）

1.1.11. log_slow_extra

推荐打开

log_slow_extra=1

启用log_slow_extra系统变量(从MySQL 8.0.14开始可用)会导致服务器将以下额外字段写入文件输出(不影响表输出)。一些字段描述引用状态变量名。有关更多信息，请参阅状态变量描述。但是，在慢速查询日志中，计数器是每个语句的值，而不是每个会话的累积值。

Thread_id: ID
The statement thread identifier.
Errno: error_number
The statement error number, or 0 if no error occurred.
Killed: N
If the statement was terminated, the error number indicating why, or 0 if the statement terminated normally.
Bytes_received: N
The Bytes_received value for the statement.
Bytes_sent: N
The Bytes_sent value for the statement.
Read_first: N
The Handler_read_first value for the statement.
Read_last: N
The Handler_read_last value for the statement.
Read_key: N
The Handler_read_key value for the statement.
Read_next: N
The Handler_read_next value for the statement.
Read_prev: N
The Handler_read_prev value for the statement.
Read_rnd: N
The Handler_read_rnd value for the statement.
Read_rnd_next: N
The Handler_read_rnd_next value for the statement.
Sort_merge_passes: N
The Sort_merge_passes value for the statement.
Sort_range_count: N
The Sort_range value for the statement.
Sort_rows: N
The Sort_rows value for the statement.
Sort_scan_count: N
The Sort_scan value for the statement.
Created_tmp_disk_tables: N
The Created_tmp_disk_tables value for the statement.
Created_tmp_tables: N
The Created_tmp_tables value for the statement.
Start: timestamp
The statement execution start time.
End: timestamp
The statement execution end time.

给定的慢速查询日志文件可能包含通过启用log_slow_extra而添加或不添加额外字段的行组合。日志文件分析器可以通过字段计数确定一行中是否包含其他字段。

写入慢速查询日志文件的每个语句前面都有一个SET语句，其中包含一个时间戳。在MySQL 8.0.14中，时间戳表示慢语句开始执行的时间。在8.0.14之前，时间戳指示记录慢速语句的时间(发生在语句完成执行之后)。

1.1.12. lower_case_table_names

以下参数从mysql8.0开始必须要在初始化数据库的时候设置，一旦初始化完成，就无法改变改参数的值。
故需要在初始化的时候就确定改参数的值

#默认不区分大小写
lower_case_table_names=1

1.1.13. Binary log

log-bin = /data/mysql/logs/binary/mybinlog
log-bin-index = /data/mysql/logs/binary/mybinlog.index
binlog_format = ROW
binlog_checksum = NONE
binlog_cache_size = 4M
max_binlog_cache_size = 4G
max_binlog_size = 1G
#mysql 8.0.3开始不可同时使用以下两个参数（废弃expire_logs_days）
expire_logs_days = 7 
#binlog_expire_logs_seconds = 3

1.1.14. sync_binlog

MySQL提供一个sync_binlog参数来控制数据库的binlog刷到磁盘上去。
默认，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。

如果sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。
最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，同样很大的影响MySQL和IO性能。

虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。对于高并发事务的系统来说，
“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。
所以很多MySQL DBA设置的sync_binlog并不是最安全的1，而是100或者是0。这样牺牲一定的一致性，可以获得更高的并发和性能。

sync_binlog = 1

关闭binglig:
skip-log-bin

1.1.15. innodb_flush_log_at_trx_commit

# 0：如果innodb_flush_log_at_trx_commit的值为0,log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作（执行是由mysql的master thread线程来执行的。
# 主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件(REDO LOG)中。不论事务是否已经提交）默认的日志文件是ib_logfile0,ib_logfile1

# 1：当设为默认值1的时候，每次提交事务的时候，都会将log buffer刷写到日志。

# 2：如果设为2,每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是，并不能保证100%每秒一定都会刷到磁盘，这要取决于进程的调度。
# 每次事务提交的时候将数据写入事务日志，而这里的写入仅是调用了文件系统的写入操作，而文件系统是有 缓存的，所以这个写入并不能保证数据已经写入到物理磁盘
# 默认值1是为了保证完整的ACID。当然，你可以将这个配置项设为1以外的值来换取更高的性能，但是在系统崩溃的时候，你将会丢失1秒的数据。
# 设为0的话，mysqld进程崩溃的时候，就会丢失最后1秒的事务。设为2,只有在操作系统崩溃或者断电的时候才会丢失最后1秒的数据。InnoDB在做恢复的时候会忽略这个值。

注：innodb_flush_log_trx_commit参数对InnoDB Log的写入性能有非常关键的影响,默认值为1。该参数可以设置为0，1，2，解释如下：
0：log buffer中的数据将以每秒一次的频率写入到log file中，且同时会进行文件系统到磁盘的同步操作，但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作;
1：在每次事务提交的时候将log buffer 中的数据都会写入到log file，同时也会触发文件系统到磁盘的同步;
2：事务提交会触发log buffer到log file的刷新，但并不会触发磁盘文件系统到磁盘的同步。此外，每秒会有一次文件系统到磁盘同步操作。

实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。根据MySQL手册，在存在丢失最近部分事务的危险的前提下，可以把该值设为0。

总结
设为1当然是最安全的，但性能页是最差的（相对其他两个参数而言，但不是不能接受）。如果对数据一致性和完整性要求不高，完全可以设为2，如果只最求性能，例如高并发写的日志服务器，设为0来获得更高性能。

1.1.16. Relay log

relay_log = /data/mysql/logs/relay/mysqlrelay
relay_log_index = /data/mysql/logs/relay/mysqlrelay.index
relay_log_recovery = 1
relay-log-purge = 1

relay_log_recovery

改参数默认是打开的，在数据库启动后立即启动自动relay log恢复，在恢复过程中，创建一个新的relay log文件，将sql线程的位置初始化到新的relay log，并将i/o线程初始化到sql线程位置。

mysql在运行过程中，从库上可能会出现意外宕机的情况，那么在从库启动后，必须能够恢复到停止之前的状态。i/o线程必须恢复到接受事务的位置，sql线程必须恢复到一致执行事务的位置。该信息传统上是存储在文件中，那么有可能存在不一致或者存在的风险，从mysql5.7开始，可以使用表来存储这些信息，并且把这些表设置为innodb引擎，通过使用事务性存储引擎，总能够恢复这个信息。可以配置参数master_info_repository=table和relay_log_info_repository=table使从库信息存储到表中。

从库如何从宕机状态恢复到正确状态，取决与从库是单线程还是多线程、relay_log_recover参数的值，以及master_auto_position的使用方式。

1，单线程模式的复制（mysql5.7）
当基于gtid模式复制的时候，并且设置了master_auto_posion参数和relay_log_recover=0，使用该设置，其中relay_log_info_repository和其他变量的设置都不会影响恢复。

当基于传统模式复制的情况下，需要设置relay_log_recovery=1和relay_log_info_repository=table。

2，多线程模式的复制（5.7）

当基于gtid模式复制的时候，并且设置了master_auto_position和relay_log_recover=0，使用该配置，其relay_log_info_repository和其他变量的设置都会影响恢复。

当基于传统模式复制的时候，请设置relay_log_recover=1，sync_relay_log=1和relay_log_info_repository=table。

1.1.17. log_slave_updates

log_slave_updates = 1

通常情况，从服务器从主服务器接收到的更新不记入它的二进制日志。该选项告诉从服务器将其SQL线程执行的更新记入到从服务器自己的二进制日志。为了使该选项生效，还必须用--logs-bin选项启动从服务器以启用二进制日志。如果想要应用链式复制服务器，应使用--logs-slave-updates。例如，可能你想要这样设置：
A -> B -> C
也就是说，A为从服务器B的主服务器，B为从服务器C的主服务器。为了能工作，B必须既为主服务器又为从服务器。你必须用--logs-bin启动A和B以启用二进制日志，并且用--logs-slave-updates选项启动B。

1.1.18. innodb_io_capacity

数据库落盘脏页个数 ,配置压力和磁盘的性能相关，如果过大，IO能力不足，则出现卡顿。
innodb_io_capacity默认是200，单位是页，该参数的设置大小取决于硬盘的IOPS，即每秒的输入输出量(或读写次数)。
可以动态调整参数:set global innodb_io_capacity=2000;

innodb_io_capacity = 4000
innodb_io_capacity_max = 8000

innodb 后台线程每秒最大iops上限。建议为IO QPS总能力的75%。
配置建议如下:

innodb_io_capacity           磁盘配置
200                         单盘SAS/SATA
2000                        SAS*12  RAID10
5000                        SSD
20000                       FUSION-IO

建议：尽量不要超过20000.

设置建议

Innodb_data_fsyncs
记录着数据刷新到磁盘的次数

show status like '%fsyncs%';

1.1.19. innodb_flush_neighbors

参数是InnoDB用来控制buffer pool刷脏页时是否把脏页邻近的其他脏页一起刷到磁盘，在传统的机械硬盘时代，打开这个参数能够减少磁盘寻道的开销，显著提升性能。

取值范围：0，1，2
默认值：5.7版本为1， 8.0版本为0

含义：

• 设置为0时，表示刷脏页时不刷其附近的脏页。
• 设置为1时，表示刷脏页时连带其附近毗连的脏页一起刷掉。
• 设置为2时，表示刷脏页时连带其附近区域的脏页一起刷掉。1与2的区别是2刷的区域更大一些。

innodb_flush_neighbors = 0

innodb_flush_method

Log和数据刷新磁盘的方法：

1. datasync模式：写数据时， write这一步并不需要真正写到磁盘才算完成（可能写入到操作系统buffer中就会返回完成），真正完成是flush操
   作， buffer交给操作系统去flush,并且文件的元数据信息也都需要更新到磁盘。
2. O_DSYNC模式：写日志操作是在write这步完成，而数据文件的写入是在flush这步通过fsync完成。
3. O_DIRECT模式：数据文件的写入操作是直接从mysql innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步,日志还是要经过OS缓冲。

建议O_DIRECT模式。

######################################################################

1.1.20. innodb_read_io_threads和innodb_write_io_threads

充分利用CPU多核的处理能力 innodb_read_io_threads和innodb_write_io_threads
在MySQL5.1.X版本中，innodb_file_io_threads参数默认是4，该参数在Linux系统上是不可更改的，但Windows系统上可以调整。这个参数的作用是：InnoDB使用后台线程处理数据页上读写I/O（输入输出）请求的数量。

在MySQL5.5.X版本中，或者说是在InnoDB Plugin1.0.4以后，就用两个新的参数，即innodb_read_io_threads和innodb_write_io_threads，取代了innodb_file_io_threads如此调整后，在Linux平台上就可以根据CPU核数来更改相应的参数值了，默认是4。
这两个参数不支持动态改变，需要把该参数加入my.cnf里，修改完后重启MySQL服务，允许值的范围是1～64。

参数innodb_write_io_threads和innodb_read_io_threads 分别负责数据块的写入和读取,默认是4，高并发可设置为8.
假如CPU是2颗8核的，那么可以设置：

innodb_read_io_threads = 8  写线程 默认四个，负责数据块的写入
innodb_write_io_threads = 8  读线程  默认四个，负责数据块的读取

如果数据库的读操作比写操作多，那么可以设置：

innodb_read_io_threads = 10
innodb_write_io_threads = 6

调整完成后，你可以用命令“show engine innodb status\G;”来查看调整结果，如下所示：
show engine innodb status
也就是说，你可以根据情况加以设置。

1.1.21. innodb_page_cleaners

刷新lru list 和flush list的参数
在MySQL5.6中，开启了一个独立的page cleaner线程来进刷lru list 和flush list。默认每秒运次1次.MySQL5.7 可设置多个page cleaner线程提高脏页刷新效率 ;
1、5.6版本以前，脏页的清理工作交由master线程的；
2、Page cleaner thread是5.6.2引入的一个新线程（单线程），从master线程中卸下buffer pool刷脏页的工作独立出来的线程(默认是启一个线程)；
3、5.7开始支持多线程刷脏页；

show global status like '%Innodb_buffer_pool_wait_free%';

如果值很大，则需要增加innodb_page_cleaners值，同时增加写线程。

innodb_purge_threads = 4
innodb_page_cleaners = 4

1.1.22. innodb_status_file

首先，让我们来了解一下 SHOW INNODB STATUS 输出的基础，它打印了很多关于 InnoDB 内部性能相关的计数器、统计、事务处理信息等。在 MySQL 5 中，InnoDB 的性能统计结果也在 SHOW STATUS 结果中显示了。大部分和 SHOW INNODB STATUS 的其他信息相同，在旧版本中还没有这个功能。

非动态参数, 可以再my.cnf中添加innodb_status_file=1启用
开启后会在datadir下生成一个innodb_status.pid文件, 周期性15秒向这个文件输出show engine innodb status. 如果异常关闭数据库, 这个文件不会被删除。

innodb_status_output
开启后会周期性向error log输出show engine innodb status

innodb_status_output_locks
单独开这个影响show engine innodb status. 这个参数参数配合innodb_status_file或innodb_status_output使用时, 当前两者开启, 则会向前两者输出位置输出锁信息。

设置建议：

## innodb status monitor
innodb_status_file = 1
# 注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快
innodb_status_output = 0
innodb_status_output_locks = 0

1.1.23. performance_schema

启用mysql性能模式

#performance_schema
performance_schema = 1
performance_schema_instrument = '%=on'

启动性能模式对数据库性能的影响在CPU资源比较充足的时候比较小，但是在CPU资源比较紧张的时候是有一定的影响的，但是改模式启用之后可以查看很多会话的锁信息，已经数据库运行的信息等等，所以建议启用。

1.1.24. tmpdir

说明：
此目录被 MySQL 用来保存临时文件.例如,

# 1. 它被用来处理基于磁盘的大型排序,和内部排序一样.
# 2. 以及简单的临时表.
# 3. 如果你不创建非常大的临时文件,将其放置到 swapfs/tmpfs 文件系统上也许比较好
# 4. 另一种选择是你也可以将其放置在独立的磁盘上.
# 5. 你可以使用”;”来放置多个路径
# 6. 他们会按照 roud-robin 方法被轮询使用.

优化建议：

tmpdir=/data/log/tmp

建议设置到一个磁盘空间较大的位置，防止出现大查询时tmp空间不足，报如下错误：

曲库2.0  MYSQL : materialdb3 ALERT  2020-08-19 17:36:26 23022 
[ERROR] /usr/sbin/mysqld: Sort aborted: Error writing file /tmp/MYuHKfLA 
(Errcode: 28 - No space left on device)

1.1.25. slave-load-tmpdir

当 slave 执行 load data infile 时用

slave-load-tmpdir = /home/mysql/mysql/tmp/

1.1.26. skip options

禁止 MySQL 对外部连接进行 DNS 解析，使用这一选项可以消除 MySQL 进行 DNS 解析的时间。但需

要注意，如果开启该选项，则所有远程主机连接授权都要使用 IP 地址方式，否则 MySQL 将无法正常
处理连接请求！

1.1.26.1. skip-name-resolve

grant 时，必须使用 ip 不能使用主机名

skip-name-resolve是禁用dns解析，避免网络DNS解析服务引发访问MYSQL的错误，一般应当启用。

cat /etc/my.cnf
skip-name-resolve
skip-external-locking
skip-new

设置建议：

1.1.26.2. skip-symbolic-links

不能使用连接文件,禁用符号链接以防止各种安全风险

不要对表使用软连接（--skip-symbolic-links参数用来禁用这一功能），表的软连接只有MyISAM支持，因为开启表软连接后（尤其是对于使用系统root用户启动MySQL服务的），MySQL用户就可以使用mysqld来删除、重命名数据文件所在位置以外的文件了

skip_symbolic_links=1

1.1.27. skip-external-locking

. #多个客户可能会访问同一个数据库，因此这防止外部客户锁定 MySQL 服务器。该选项
默认开启

不使用系统锁定，要使用 myisamchk,必须关闭服务器 ,避免 MySQL的外部锁定，减少出错几率增强稳定性。

skip-slave-start #启动 mysql,不启动复制

开启该选项可以彻底关闭 MySQL 的 TCP/IP

MySQL的配置文件my.cnf中默认存在一行skip-external-locking的参数，即跳过外部锁定。根据MySQL开发网站的官方解释，External-locking用于多进程条件下为MyISAM数据表进行锁定
如果你有多台服务器使用同一个数据库目录（不建议），那么每台服务器都必须开启external locking；

当外部锁定（external-locking）起作用时，每个进程若要访问数据表，则必须等待之前的进程完成操作并解除锁定。由于服务器访问数据表时经常需要等待解锁，因此在单服务器环境下external locking会让MySQL性能下降。所以在很多Linux发行版的源中，MySQL配置文件中默认使用了skip-external-locking来避免external locking。
当使用了skip-external-locking后，为了使用MyISAMChk检查数据库或者修复、优化表，你必须保证在此过程中MySQL服务器没有使用需要操作的表。如果没有停止服务器，也至少需要先运行.

1.1.28. skip-networking

开启该选项可以彻底关闭 MySQL 的 TCP/IP 连接方式，如果 WEB 服务器是以远程连接的方式访问 MySQL 数据库服务器则不要开启该选项！否则将无法正常连接！如果所有的进程都是在同一台服务器连接到本地的 mysqld, 这样设置将是增强安全的方法

1.1.29. back_log = 50

指定 MySQL 可能的连接数量。当 MySQL 主线程在很短的时间内接收到非常多的连接请求，该参数生效，主线程花费很短的时间检查连接并且启动一个新线程。

如果你有非常高的连接率并且出现”connection refused” 报错,你就应该增加此处的值.back_log 参数的值指出在 MySQL 暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。如果系统在一个短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的 TCP/IP 连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自己的限制。
试图设定 back_log 高于你的操作系统的限制将是无效的。默认值为 50。对于 Linux 系统推荐设置为小于 512 的整数。

back_log = 50 #接受队列，对于没建立 tcp 连接的请求队列放入缓存中，队列大小为 back_log，受限制与 OS 参数

1.1.30. 连接超时相关

指定 MySQL 允许的最大连接进程数。如果在访问数据库时经常出现 Too Many Connections 的错误提 示，则需要增大该参数值。

max_connections = 1000 #最大并发连接数 ，增大该值需要相应增加允许打开的文件描述符数

max_connect_errors = 10000 #如果某个用户发起的连接 error 超过该数值，则该用户的下次连接将被阻塞，直到管理员执行 flush hosts ; 命令或者服务重启，防止黑客 ，非法的密码以及其他在链接时的错误会增加此值

34. open_files_limit = 10240

35. connect-timeout = 10 #连接超时之前的最大秒数,在 Linux 平台上，该超时也用作等待服务器首次回应的时间

指定一个请求的最大连接时间，对于 4GB 左右内存的服务器可以设置为 5-10

wait-timeout = 28800 #等待关闭连接的时间
38. interactive-timeout = 28800 #关闭连接之前，允许 interactive_timeout（取代了
wait_timeout）秒的不活动时间。客户端的会话 wait_timeout 变量被设为会话
interactive_timeout 变量的值。
39. slave-net-timeout = 600 #从服务器也能够处理网络连接中断。但是，只有从服务器超过
slave_net_timeout 秒没有从主服务器收到数据才通知网络中断
40. net_read_timeout = 30 #从服务器读取信息的超时
41. net_write_timeout = 60 #从服务器写入信息的超时
42. net_retry_count = 10 #如果某个通信端口的读操作中断了，在放弃前重试多次
43. net_buffer_length = 16384 #包消息缓冲区初始化为 net_buffer_length 字节，但需要时可
以增长到 max_allowed_packet 字节

1.1.31. max_allowed_packet = 64M

服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小(当与大的BLOB 字段一起工作时相当必要)， 每个连接独立的大小.大小动态增加。 设置最大包,限 制server接受的数据包大小，避免超长SQL的执行有问题 默认值为16M，当MySQL客户端或mysqld服务器收到大于 max_allowed_packet 字节的信息包时，将发出“信息包过大”错误，并关闭连接。

对于某些客户端，如果通信信息包过大，在执行查询期间，可能会遇到“丢失与 MySQL 服务器的连接”错误。默认值 16M。

1.1.32. table_cache = 512

所有线程所打开表的数量. 增加此值就增加了 mysqld 所需要的文件描述符的数量，这样你需要确认在[mysqld_safe]中 “open-files-limit”
变量设置打开文件数量允许至少4096

打开一个表的开销可能很大。例如MyISAM把MYI文件头标志该表正在使用中。你肯定不希望这种操作太频繁，所以通常要加大缓存数量，使得足以最大限度地缓存打开的表。它需要用到操作系统的资源以及内存，对当前的硬件配置来说当然不是什么问题了。如果你有200多个表的话，那么设置为 1024 也许比较合适(每个线程都需要打开表)，如果连接数比较大那么就加大它的值。我曾经见过设置为100,000的情况。

#table_cache = 512 #所有线程打开的表的数目。增大该值可以增加 mysqld 需要的文件描述符的数量

1.1.33. thread_cache_size

thread_cache_size = 8

# 这个值（默认8）表示可以重新利用保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中，如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.通过比较Connections和Threads_created状态的变量，可以看到这个变量的作用。(–表示要调整的值)
# 根据物理内存设置规则如下：
# 1G  — 8
# 2G  — 16
# 3G  — 32
# 大于3G  — 64

Mysql支持单线程和多线程两种连接线程模式，如果单线程，则在同一时刻，只能有一个connection连接到Mysql，其他的连接会被挂起。如果是多线程，则同一时刻可以支持多个connection同时连接到服务器。mysql使用哪种线程模式通过下面变量控制。

mysql> show variables like '%thread_handling%';

客户端发起连接到mysql server，mysql server监听进程，监听到新的请求，然后mysql为其分配一个新的 thread，去处理此请求。从建立连接之开始，CPU要给它划分一定的thread stack，然后进行用户身份认证，建立上下文信息，最后请求完成，关闭连接，同时释放资源，在高并发的情况下，将给系统带来巨大的压力，不能保证性能。所以，mysql通过线程缓存来是实现线程重用，减小这部分的消耗；一个connection断开，并不销毁承载其的线程，而是将此线程放入线程缓冲区，并处于挂起状态，当下一个新的Connection到来时，首先去线程缓冲区去查找是否有空闲的线程，如果有，则使用之，如果没有则新建线程。

mysql通过 thread_cache_size这参数来设置可以重用线程的个数，他的大小可以通过状态变量Threads_cached和Threads_created来设置

mysql> show status like 'thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 2     |
| Threads_created   | 1065  |
| Threads_running   | 1     |
+-------------------+-------+

Threads_cached：已经被线程缓存池缓存的线程个数
Threads_created：已经创建的线程个数，通过这个变化的趋势，可以判断thread_cache_size参数值
在5.5.16以前，线程处理的模式是，每个请求就对应一个线程的模式，这就意味着当有成千上万的请求时，对应的也就需要成千上万的线程来响应这些请求，那么此刻问题就很明显了，系统的资源是有限的，必须要保证thread_number*thread_stack不能超过可以使用的内存资源，还要考虑CPU的调度能力，I/O的处理能力，这是一种很粗放的资源使用方式，同时，这种不加控制的处理方式，也会带来资源使用的冲突，大量互斥锁的出现，性能的急剧下降。在5.5.16以后通过Thread Pool来控制确保不会超过服务器的最大负载能力，避免出现服务无响应，导致宕机的惨状。

1.1.34. MySQL innodb_autoinc_lock_mode

innodb_autoinc_lock_mode这个参数控制着在向有auto_increment 列的表插入数据时，相关锁的行为；
通过对它的设置可以达到性能与安全(主从的数据一致性)的平衡

1.1.34.1. 我们先对insert做一下分类

首先insert大致上可以分成三类：

• 1、simple insert 如insert into t(name) values('test')
• 2、bulk insert 如load data | insert into ... select .... from ....
• 3、mixed insert 如insert into t(id,name) values(1,'a'),(null,'b'),(5,'c');

1.1.34.2. innodb_autoinc_lock_mode 的说明

1.1.34.2.1. innodb_auto_lockmode有三个取值：

• 1、0 这个表示tradition 传统
• 2、1 这个表示consecutive 连续
• 3、2 这个表示interleaved 交错

1.1.34.2.2. tradition(innodb_autoinc_lock_mode=0) 模式:

1、它提供了一个向后兼容的能力
2、在这一模式下，所有的insert语句("insert like") 都要在语句开始的时候得到一个
表级的auto_inc锁，在语句结束的时候才释放这把锁，注意呀，这里说的是语句级而不是事务级的，一个事务可能包涵有一个或多个语句。
3、它能保证值分配的可预见性，与连续性，可重复性，这个也就保证了insert语句在复制到slave
          的时候还能生成和master那边一样的值(它保证了基于语句复制的安全)。
4、由于在这种模式下auto_inc锁一直要保持到语句的结束，所以这个就影响到了并发的插入。

1.1.34.2.3. consecutive(innodb_autoinc_lock_mode=1) 模式:

1、这一模式下去simple insert 做了优化，由于simple insert一次性插入值的个数可以立马得到
          确定，所以mysql可以一次生成几个连续的值，用于这个insert语句；总的来说这个对复制也是安全的
          (它保证了基于语句复制的安全)
2、这一模式也是mysql的默认模式，这个模式的好处是auto_inc锁不要一直保持到语句的结束，只要语句得到了相应的值后就可以提前释放锁

1.1.34.2.4. interleaved(innodb_autoinc_lock_mode=2) 模式

　　1、由于这个模式下已经没有了auto_inc锁，所以这个模式下的性能是最好的；但是它也有一个问题，就是
          对于同一个语句来说它所得到的auto_incremant值可能不是连续的。

如果你的二进制文件格式是mixed | row 那么这三个值中的任何一个对于你来说都是复制安全的。由于现在mysql已经推荐把二进制的格式设置成row，所以在binlog_format不是statement的情况下最好是innodb_autoinc_lock_mode=2 这样可能知道更好的性能。