mysql 配置优化

mysql 配置优化

系列文章

• mysql 性能优化 | 第一篇 mysql B+Tree
• mysql 性能优化 | 第二篇 MySql Myisam和innodb对比 索引优化建议
• mysql 性能优化 | 第三篇 mysql存储引擎
• mysql 性能优化 | 第四篇 mysql数据库的隔离级别
• mysql 性能优化 | 第五篇 mysql 表锁 行锁
• mysql 性能优化 | 第六篇 mysql MVCC Undo Redo
• mysql 性能优化 | 第七篇 mysql 执行路径 执行计划 慢查询
• 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

使用grep可以屏蔽其他信息

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

默认加载顺序

1. /etc/my.cnf

2. /etc/mysql/my.cnf

3. /usr/etc/my.cnf

4. ~/.my.cnf

全局配置文件

最大连接数

max_connections

set GLOBAL max_connections = 2000;

最大连接数还取决于以下两个配置，因为系统和mysql都有最大句柄数的限制

1. 系统句柄数

etc/security/limits.conf

或者通过shell命令查看

ulimit -a

2. mysql句柄数配置

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

如果连接数配置6000，实际上只有4190，因为mysql句柄是5000上限，而且mysql的其他服务也需要占据一些。

常见全局配置

参数	值
port	3306
scoket	/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)

上一篇mysql MVCC Undo Redo讲Undo Log 中mysql的buffer/cache缓冲区的大小

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

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

参考计算公式

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

全部参数可以在官网中进行检索

• 官网参数查询地址

• innodb相关参数

参考

1. 腾讯课堂-咕泡学院-seven老师-mysql性能优化
2. mysql官网