文章目录

• mysql 配置优化

• 系列文章

  • 参数的作用域
  • 配置文件
  • 全局配置文件
  • 常见全局配置
  • mysql内存参数配置

• sort_buffer_size connection
  * join_buffer_size connection

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

  
  1
  2
  1  * Innodb_buffer_pool_size
  
  2
  
  

• 参考计算公式

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

mysql 配置优化

系列文章

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

参数的作用域

全局参数

1
2
3
1 set global autocommit = ON/OFF;

2

3

会话参数(会话参数不单独设置则会采用全局参数)

1
2
3
1set session autocommit = ON/OFF;

2

3

注意

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

配置文件

寻找配置文件

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

1
2
3
4
1Default options are read from the following files in the given order:

2/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

3

4

使用grep可以屏蔽其他信息

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

2

3

默认加载顺序

/etc/my.cnf

/etc/mysql/my.cnf

/usr/etc/my.cnf

~/.my.cnf

全局配置文件

最大连接数

max_connections

1
2
3
1set GLOBAL max_connections = 2000;

2

3

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

1. 系统句柄数

1
2
3
1etc/security/limits.conf

2

3

或者通过shell命令查看

1
2
3
1ulimit -a

2

3

1. mysql句柄数配置

1
2
3
1/usr/lib/systemd/system/mysqld.service

2

3

如果连接数配置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官网