简介

之前这篇文章MySQL配置根底简要阐明了MySQL的配置根底，包含配置文件的地位、配置项的分段、配置变量的失效、以及配置变量和状态变量的查看，对MySQL的配置有了一个根底。
当初则会进一步理解更多底层原理，搞清楚更多配置的含意和作用。

本文为《高性能MySQL》读书笔记，配合文档查阅更佳： https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

InnoDB IO配置

InnoDB 事务日志：

InnoDB在每个事务提交的时候，不会把缓冲池的内容立刻刷到磁盘。而是从缓冲池将事务记录到事务日志中，输入日志(长久化)，再由事务日志实现写磁盘的操作。
事务日志会把数据文件的随机I/O转换成简直程序的I/O，而且把刷新到磁盘的操作转移到后盾，从而让查问更快。
事务日志有固定的大小，采纳环形形式写（写到开端时跳转到结尾持续写）。定期将缓冲池传来的事务通过日志形式长久化，再将脏数据刷到磁盘中。

缓冲池 <--> 事务日志 <--> 磁盘
先将事务变成日志，写入磁盘(长久化)，再缓缓依据日志内容写入磁盘。

事务日志的配置：

事务日志依赖innodb_log_file_size和innodb_log_files_in_groups这两个变量。前者申明每个日志文件的大小，后者申明日志文件的个数。
InnoDB会应用多个日志文件作为循环日志（1号文件写完了写2号，2号写完了写1号）

默认是50M，2个文件。共100M。倡议增大单个文件的大小，依然应用2个文件。(本文应用MySQL 8.0.21 不同版本默认参数可能不统一)
mysql> show variables where variable_name like "%inno%log_file%";+---------------------------+----------+| Variable_name             | Value    |+---------------------------+----------+| innodb_log_file_size      | 52428800 || innodb_log_files_in_group | 2        |+---------------------------+----------+

事务日志自身的写入缓存：

事务日志，将事务写入日志文件的时候，也并不是间接写入的，而是应用写入缓存。先写入缓存中，再由缓存定期写入文件。由innodb_log_buffer_size 参数决定应用写入缓存大小。
以下3个条件，满足任一条件就会刷新缓存到日志文件中。

  • 每隔1秒
  • 写入缓存满
  • 有事务提交
MySQL 8.0.21 默认大小为16M。因为最长每1秒会刷新一次写入缓存，所以这个参数不必设置的过大，只须要超过每秒产生的事务量即可。
mysql> show variables where variable_name like "%inno%log_b%";+------------------------+----------+| Variable_name          | Value    |+------------------------+----------+| innodb_log_buffer_size | 16777216 |+------------------------+----------+

InnoDB和文件系统的交互方式

InnoDB和磁盘的读写交互都通过innodb_flush_method来抉择。次要有如下几种：

  • fdatasync
  • O_DIRECT
  • O_DSYNC

1、fdatasync()：和fsync()相似，然而只刷新文件数据自身，不包含元数据。而且，这个选项会应用应用双重缓冲（包含操作系统这一层的缓存）
2、O_DIRECT：仍然应用fsync()来刷新文件到磁盘，然而会敞开操作系统缓存，告知操作系统不要缓存且不要预读。（所有读写都间接达到存储设备，防止双重缓冲）

这个设置只会影响操作系统，不会影响RAID卡的预读。
如果应用这个选项，最好应用带预读的RAID卡，且关上写回(write_back)

3、O_DSYNC
这个选项会使所有写同步，或者说，只有数据确切写到磁盘后，写操作才会返回。
每个write()或pwrite()操作都会函数实现前将数据同步到磁盘，且这个过程是阻塞的。[而fsync()容许积攒写操作到缓存，再一次性刷新数据。]

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_method
更多可参考：https://www.cnblogs.com/CNty/p/10943626.html
InnoDB I/O配置文档：https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-diskio.html

InnoDB 表空间配置【施工中】

双写缓冲(DoubleWrite)配置【施工中】

MySQL 高并发【施工中】

呈现高并发时，如何发现问题？

呈现高并发时，如何取得更好的性能？

线程进入内核阶段-并发瓶颈；

innodb_thread_concurrency：限度一次性能够有多少线程进入内核。（0示意不限度

推荐值：并发值 = CPU 数量 * 磁盘数量 * 2【理论倡议设置稍小的值，再行调整

两段解决：

如果已进入内核的线程过多，则新线程无奈进入内核。会应用两段解决：

两段解决能够缩小操作系统导致的上下文切换。

1、如果未能进入内核，则开始第一次休眠，工夫为：innodb_thread_sleep_delay。休眠完结后重试。

2、如果仍未进入内核，则将这个线程退出一个期待线程队列，让操作系统解决。

【如果有很多小查问，innodb_thread_sleep_delay能够思考适当减小。这相当于10毫秒的查问延时。

提交阶段-并发瓶颈：
innodb_commit_concurrency：同一时间提交的线程的数量 下限。
线程池？

平安和稳固配置

  • expire_logs_days：如果应用Binlog就应该关上 ，保留二进制日志的天数。清理过期的日志。依据理论状况，在保障备份的状况上来配置。（被动申明的意义在于，防止二进制日志打满磁盘，如果有须要，能够增长保留工夫）
  • max_allowed_packet：禁止服务器收发过大的数据包。

  • max_connect_errors：如果某个主机间断x个连贯失败，则会被BAN掉。一旦被BAN，只能命令刷新缓存能力解除。不倡议设置的过小，免得呈现所有应用程序被BAN的状况。

    https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_connect_errors
  • skip_name_resolve：敞开DNS查找。MySQL在连贯时，会正向/反向查DNS，确认连贯主机的主机名，依据理论状况判断是否敞开。

备库相干配置

  • read_only：备库强烈建议开启只读，只承受从主库传输过去的变更。
  • skip_slave_start：阻止MySQL主动启动复制，如果备库呈现问题重启，相对不能主动复制，要手动确认查看后才可。
  • slave_net_timeout：备库连贯主库失败时，重连前等待时间.默认60分钟太长了，倡议改为1分钟或者更短。

备库日志同步写磁盘相干：
【这3个变量都是动静变量，一旦设置，会立刻对所有备库失效。】

  • sync_master_info：每有x个事件产生，就将 master.info 向磁盘同步写一次。【应用fdatasync() 】

    https://dev.mysql.com/doc/refman/5.7/en/replication-options-replica.html#sysvar_sync_master_info

  • sync_relay_log：每有x个事件被写入relay log，就将 relay log 向磁盘同步写一次。【应用fdatasync() 】

    https://dev.mysql.com/doc/refman/5.7/en/replication-options-replica.html#sysvar_sync_relay_log

  • sync_relay_log_info：与上一个相似，每x个事件产生后，就将 relay-log.info 向磁盘同步写一次。

    https://dev.mysql.com/doc/refman/5.7/en/replication-options-replica.html#sysvar_sync_relay_log_info

其余常见配置

  • tmp_table_size和max_heap_table_size:
    如果隐式内存长期表超过这两个设置，则会转换为硬盘表。须要关注Created_tmp_disk_tables和Created_tmp_tables，这两个磁盘长期表状态，来确认。
    这两个参数能够简略的配置为一样的大小，不倡议设置的过大。如果长期表过大，应用磁盘比应用硬盘好，省得内存溢出。

  • max_connections
    更像是紧急刹车，保障数据库不会因为应用程序连接数突增导致本身不堪重负。（爱护数据库自身）当呈现问题，导致新建过多新连贯时，把多余的谬误链接回绝掉，是一种疾速、低代价的失败形式。
    这个参数应该设置的足够高（能够解决失常状况下的负载，服务失常运行），其次也要足够平安（保障能够登录上服务器，进行保护操作）

    比方，失常状况下有300连接数，那么这个值必然不能低于300（保障服务失常运行），能够思考400~500.
  • Max_used_connections：这个状态是最高连接数的值。同时还有一个Max_used_connections_time，申明工夫。
    你能够分明的晓得，连接数的峰值在什么时候。
  • thread_cache_size
    线程缓存要依据理论状态来制订。比方Threads_connected、Threads_created、Threads_cached。
    能够依据正在连接中的线程数来估测缓存大小。如果每秒创立的线程数很多或者每秒创立的线程越来越多，那么就须要增大缓存。有时候，也须要依据曾经缓存了的线程数来判断缓存大小是否适合。
  • table_cache_size
    表缓存倡议配置的足够大，防止须要常常从新关上、从新解析表定义。（如果表不是很多，齐全能够配置一个大的表缓存，把所有表构造都缓存了。
    相干状态：Opened_tables，如果已关上的表始终在增长，倡议适当增大表缓存。
    值举荐：1、不倡议这个值超过10 000。2、倡议从连接数的10倍开始调整。