参数的作用域

全局参数：SET GLOBAL autocommit = ON/OFF;
会话参数（会话参数不单独设置则会采用全局参数）：SET SESSION autocommit = ON/OFF;
注意：

  • 全局参数的设定对于已经存在的会话无法生效，会话重新建立才会生生效
  • 会话参数的设定随着会话的销毁而失效
  • 全局类的统一配置建议配置在默认配置文件中，否则重启服务会导致配置失效

如何查找 MySQL 配置文件位置

通过 mysql --help 命令可以寻找配置文件的位置和加载顺序。

image.png

可以通过如下命令精确查找：

  1. mysql --help | grep -A 1 'Default options are read from the following files in the given order'

最大连接数配置

max_connections：最大连接数配置，配置后需要重启 MySQL 服务才能生效。

当我们配置该参数为6000个连接是，重启 MySQL 后，发现最大连接数的值并不是6000，而是4190个连接数。是什么限制了该配置？

  1. 系统句柄数会限制，系统句柄数配置通过 /etc/security/limits.conf 配置文件修改，通过 ulimit -a 查看当前的 Linux 系统参数。

image.png

  1. MySQL 句柄数会限制，MySQL 句柄数配置通过 /usr/lib/systemd/system/mysqld.service 配置文件修改，该文件中有个 LimitNOFILE 参数设置了 MySQL 的句柄数。

内存参数配置

针对每一个 connection 内存参数配置

  1. sort_buffer_size：connection 排序缓冲区大小。

    1. 建议 256K（ 默认值）到 2M 之间
    2. 当查询语句中有需要文件排序功能时，马上为 connection 分配配置的内存大小

  2. join_buffer_size：connection 关联查询缓冲区大小。

    1. 建议 256K（ 默认值）到 2M 之间
    2. 当查询语句中有关联查询时，马上分配配置大小的内存用于这个关联查询，如果一个查询语句中存在多个关联，会为每一个关联都分配一个关联查询缓存区，所以有可能在一个查询语句中会分配很多个关联查询缓冲区

上述配置4000连接占用内存：4000 * (256K + 256K) ≈ 2G

innodb_buffer_pool_size

innodb buffer/cache 的大小（默认128M）。

在 Innodb_buffer_pool 池里面保存了如下内容：

  • 数据缓存
  • 索引缓存
  • 缓冲数据
  • 内部结构

大的缓冲池可以减小多次磁盘 I/O 访问相同的表数据以提高性能。

参考计算公式：Innodb_buffer_pool_size = （总物理内存 - 系统运行所用 - connection 所用） * 90%

其他配置

wait_timeout：服务器关闭非交互连接之前等待活动的秒数。

innodb_open_files：限制 InnoDB 能打开的表的个数。

innodb_write_io_threads、innodb_read_io_threads：InnoDB 使用后台线程处理 InnoDB 缓冲区数据页上的读写 I/O（输入输出）请求的线程数。

innodb_lock_wait_timeout：InnoDB 事务在被回滚之前可以等待一个锁定的超时秒数

常见的配置参考：https://www.cnblogs.com/wyy123/p/6092976.html

作者：殷建卫 链接：https://www.yuque.com/yinjianwei/vyrvkf/ehgeem 来源：殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。