MySQL参数调优和相关命令

一. 相关命令说明

1. show status: 查看当前状态

# 示例
# 查看全部状态
show status;


# 模糊查询
show status like 'Thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 22    |
| Threads_connected | 17    |
| Threads_created   | 39    |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.04 sec)

# 常用查询项

2.show processlist: 查看当前进程列表

# 示例
# 查看全部进程列表
show processlist;
# 查看全部进程列表详细信息
show full processlist;

3.show global variables: 查看当前参数

# 示例
# 查看全部参数
show global variables;
# 模糊查询
show global variables like 'key%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| key_buffer_size          | 536870912 |
| key_cache_age_threshold  | 300       |
| key_cache_block_size     | 1024      |
| key_cache_division_limit | 100       |
| keyring_operations       | ON        |
+--------------------------+-----------+
5 rows in set (0.04 sec)

二. 推荐配置

1. 1-2G内存, 使用 < 1G

key_buffer_size=128M
query_cache_size=64M
tmp_table_size=64M
innodb_buffer_pool_size=256M
innodb_log_buffer_size=16MB
thread_cache_size=64
table_open_cache=128
max_connections=100
sort_buffer_size=768KB
read_buffer_size=768KB
read_rnd_buffer_size=512KB
join_buffer_size=1024KB
binlog_cache_size=64KB

2. 2-4G内存, 使用 2G

key_buffer_size=256M
query_cache_size=128M
tmp_table_size=384M
innodb_buffer_pool_size=384M
innodb_log_buffer_size=16MB
thread_cache_size=96
table_open_cache=192
max_connections=200
sort_buffer_size=768KB
read_buffer_size=768KB
read_rnd_buffer_size=512KB
join_buffer_size=2048KB
binlog_cache_size=64KB

3. 4-8G内存, 使用 3G

key_buffer_size=384M
query_cache_size=192M
tmp_table_size=512M
innodb_buffer_pool_size=512M
innodb_log_buffer_size=16MB
thread_cache_size=128
table_open_cache=384
max_connections=300
sort_buffer_size=1024KB
read_buffer_size=1024KB
read_rnd_buffer_size=768KB
join_buffer_size=2048KB
binlog_cache_size=128KB

4. 8-16G内存, 使用 6.5G

key_buffer_size=512M
query_cache_size=256M
tmp_table_size=1024M
innodb_buffer_pool_size=1024M
innodb_log_buffer_size=16MB
thread_cache_size=192
table_open_cache=1024
max_connections=400
sort_buffer_size=2048KB
read_buffer_size=2048KB
read_rnd_buffer_size=1024KB
join_buffer_size=4096KB
binlog_cache_size=192KB

5. 16-32G内存, 使用 16G

key_buffer_size=1024M
query_cache_size=384M
tmp_table_size=2048M
innodb_buffer_pool_size=4096M
innodb_log_buffer_size=16MB
thread_cache_size=256
table_open_cache=2048
max_connections=500
sort_buffer_size=4096KB
read_buffer_size=4096KB
read_rnd_buffer_size=2048
join_buffer_size=8192KB
binlog_cache_size=256KB

三. MySQL my.ini 参数调整说明

# 数据库配置:
# key_buffer_size: 索引缓冲区  越大越好  建议目标内存的1/10

# query_cache_size: 查询缓存 按需开启  索引的一半大小, 使用SHOW VARIABLES LIKE 'have_query_cache';命令查看是否支持, YES代表支持

# tmp_table_size: 临时表缓存 越大越好 建议目标内存的1/5

# innodb_buffer_pool_size: Innodb引擎缓冲区大小 越大越好 建议同临时表大小或更大一些

# innodb_log_buffer_size: Innodb引擎日志缓冲大小, 16M够用

# thread_cache_size: 线程缓存大小, 建议为索引缓冲区M的数量的一半, 使用 show status like Threads_created 命令查看, 如果数值过大, 建议增大此配置

# table_open_cache: 表缓存, 建议同临时表缓冲M数量

# max_connections: 建议同索引缓冲M数量, 可根据项目实际情况调整

## ---------------------------------------

# 单链接配置
# sort_buffer_size: 排序缓冲, 根据数据库中单行数据大小确定, 建议 索引缓冲区/最大连接数 * 2

# read_buffer_size: 读缓冲, 同排序缓冲

# read_rnd_buffer_size: 随机读取缓冲区, 建议读缓冲的一半

# join_buffer_size: 表关联缓冲区大小, 根据执行SQL的关联情况而定, JOIN的越多, 建议此项配置越大, 正常为 读缓冲 * 2 即可

# binlog_cache_size: 二进制日志缓冲大小, 必须为分区簇大小的倍数, 如: 硬盘分区时进行了4K对齐, 则此处必须为4的倍数, 建议128K