乐趣区

关于mysql:MySQL配置再学习

jiezi

简介

之前这篇文章 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 倍开始调整。
退出移动版