MySQL服务参数优化

目录

1、max_connections

1.1、监控指标

活动/峰值连接数 = Max_used_connections / max_connections

  • 占比在0.85左右为健康值，如果过大，则须增大max_connections的值

1.2、查看当前max_connections设置

mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 400   |
+-----------------+-------+
1 row in set (0.00 sec)

1.3、查看自服务启动到目前峰值连接数

mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 22    |
+----------------------+-------+
1 row in set (0.00 sec)

1.3.1、临时调整

mysql> set global max_connections = 200;
Query OK, 0 rows affected (0.00 sec)

1.3.2、永久调整

[mysqld]
max_connections = 200

2、table_open_cache

2.1、监控指标

如果 0.85 ≤ Open_tables / table_open_cache ≤ 0.95，无需调整，否则需要相应调整

2.2、查看已打开表数量

mysql> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 442   |
| Opened_tables | 1509  |
+---------------+-------+
2 rows in set (0.00 sec)

2.3、调整

2.3.1、临时调整

mysql> set global table_open_cache = 2048;
Query OK, 0 rows affected (0.00 sec)

2.3.2、永久调整

[mysqld]
table_open_cache = 1024

3、thread_cache_size

每建立一个连接，都需要一个线程来与之匹配，此参数用来缓存空闲的线程，以至不被销毁，如果线程缓存中有空闲线程，这时候如果建立新连接，MYSQL就会很快的响应连接请求。

3.1、监控指标

线程缓存命中率 = (1 -Threads_created / Connections) * 100%
命中率 > 90%： 通常认为状态良好。你的thread_cache_size设置可能已经足够。
命中率 < 90%： 表明线程缓存可能偏小，导致 MySQL 频繁创建新线程。这会造成额外的系统开销

3.2、查看当前Thread状态

mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 11    |
| Threads_created   | 79    |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)
  • Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。
  • Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
  • Threads_created :代表从最近一次服务启动，已创建线程的数量。为处理连接请求创建过的线程数，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值。
  • Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，这里相对应的线程也是sleep状态。

3.3、查看连接信息

mysql> show global status like 'Connections%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 141   |
+---------------+-------+
1 row in set (0.00 sec)

3.4、调整

3.4.1、临时调整

set global thread_cache_size = 64;
Query OK, 0 rows affected (0.00 sec)

3.4.2、永久调整

[mysqld]
thread_cache_size = 64

4、key_buffer_size

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。
key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。

mysql> SHOW VARIABLES LIKE '%key_buffer_size%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| key_buffer_size | 201326592 |
+-----------------+-----------+
1 row in set (0.00 sec)

4.1、监控指标

(1 - Key_reads / Key_read_requests) > 0.99 较好

4.2、查看key_buffer_size的使用情况

mysql> SHOW GLOBAL STATUS LIKE '%key_read%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 237   |
| Key_reads         | 10    |
+-------------------+-------+
2 rows in set (0.00 sec)

4.3、调整

4.3.1、临时调整

SET GLOBAL key_buffer_size = 1024 * 1024 * 192;

4.3.2、永久调整

[mysqld]
key_buffer_size = 256M

5、innodb_buffer_pool_size

5.1、监控指标

Innodb缓冲池命中率 = (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%
如果命中率过低（例如低于 95%），可以考虑

5.2、查询Innodb_buffer_pool_read_requests

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| Innodb_buffer_pool_read_requests | 228436850 |
+----------------------------------+-----------+
1 row in set (0.00 sec)

5.3、查询Innodb_buffer_pool_reads

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Innodb_buffer_pool_reads | 16952 |
+--------------------------+-------+
1 row in set (0.00 sec)

5.4、调优

修改 innodb_buffer_pool_size 参数值
检查系统SQL语句

5.4.1、查看

mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 536870912 |
+-------------------------+-----------+
1 row in set (0.01 sec)

查询结果为536870912，也就是512M

5.4.2、临时修改

SET GLOBAL innodb_buffer_pool_size = 1024 * 1024 * 512;

5.4.3、永久修改

[mysqld]
innodb_buffer_pool_size = 512M

6、tmp_table_size

tmp_table_size决定了内存中内部临时表的最大大小。当查询需要创建临时表（例如，进行GROUP BY、DISTINCT、UNION或一些JOIN操作）时，MySQL 会优先在内存中创建。如果这个内存临时表的大小超过了tmp_table_size（或其姊妹参数max_heap_table_size，两者取最小值），MySQL 就会将其转换为在磁盘上创建的 MyISAM 表，这会导致性能急剧下降。

6.1、监控指标

磁盘临时表比率 = Created_tmp_disk_tables / Created_tmp_tables
比率过高（例如 > 20%）：这是一个明确的信号，说明有大量临时表因为大小不足而被迫写入磁盘。这时你应该考虑增加tmp_table_size。
比率很低（例如 < 20%）：说明当前设置基本合理，大部分临时表都在内存中处理。

6.2、查看指标相关参数

mysql> SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1188  |
| Created_tmp_tables      | 2006  |
+-------------------------+-------+
2 rows in set (0.00 sec)

6.3、调整

6.3.1、临时调整

SET GLOBAL tmp_table_size = 1024 * 1024 * 64; -- 64MB
SET GLOBAL max_heap_table_size = 1024 * 1024 * 64; -- 通常建议将这两个值设为相同

6.3.2、永久调整

[mysqld]
max_heap_table_size = 64M
tmp_table_size = 64M
posted @ 2025-11-08 15:32  Bruce.Chang.Lee  阅读(15)  评论(0)    收藏  举报