MySQL-配置优化

mysql服务器参数类型

基于参数的作用域：

全局参数

set global autocommit = ON/OFF;

会话参数

set session autocommit = ON/OFF;

注意：

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

寻找配置文件

mysql --help 寻找配置文件的位置和加载顺序

Default options are read from the following files in the given order:/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

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

全局配置文件配置

最大连接数配置

max_connections

系统句柄数配置

/etc/security/limits.conf
ulimit -a

mysql 句柄数配置

/usr/lib/systemd/system/mysqld.service

常见全局配置文件配置

port = 3306

socket = /tmp/mysql.sock

basedir = /usr/local/mysql

datadir = /data/mysql

pid-file = /data/mysql/mysql.pid

user = mysql

bind-address = 0.0.0.0

max_connections=2000

lower_case_table_names = 0 # 表名区分大小写

server-id = 1

tmp_table_size=16M

transaction_isolation = REPEATABLE-READ

ready_only=1

mysql内存参数配置

每一个connection内存参数配置：

sort_buffer_size connection 排序缓冲区大小

建议256K( 默认值)-> 2M 之内

当查询语句中有需要文件排序功能时，马上为connection 分配配置的内存大小

join_buffer_size connection 关联查询缓冲区大小

建议256K( 默认值)-> 1M 之内

当查询语句中有关联查询时，马上分配配置大小的内存用这个关联查询，所以有可能在一个查询语句中会分配很多个关联查询缓冲区

上述配置4000 连接占用内存：

4000*(0.256M+0.256M) = 2G

Innodb_buffer_pool_size

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

Innodb_buffer_pool

数据缓存

索引缓存

缓冲数据

内部结构

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

参考计算公式：

Innodb_buffer_pool_size =  （总物理内存 -  系统运行所用 - connection  所用）* 90%