常见Mysql系统参数调整

最新推荐文章于 2023-10-20 11:01:28 发布

yumushui

最新推荐文章于 2023-10-20 11:01:28 发布

阅读量3.7k

点赞数

分类专栏： MySQL数据库文章标签： mysql 优化

本文链接：https://blog.csdn.net/yumushui/article/details/40982037

版权

本文介绍了MySQL系统参数调整的重要性，包括慢查询设置、连接数管理、key_buffer_size优化、临时表处理和Table Cache相关参数调整。通过监控各项指标如慢查询日志、连接数、索引缓存命中率等，确保MySQL高效运行。建议定期分析和调整这些参数以提升数据库性能。

摘要由CSDN通过智能技术生成

MySQL参数对于MySQL数据库的功能和形成有非常重要的影响，下面是一些从网络上收集的一些资料，对于mysql数据库的常见参数有一些借鉴和参考的意义。

常见Mysql系统参数调整

列出MySQL服务器运行各种状态值：

mysql> show global status;

查询MySQL服务器配置信息：

mysql> show variables;

1. 慢查询

mysql> show variables like '%slow%';

+---------------------+-------------------------------+

| Variable_name | Value |

+---------------------+-------------------------------+

| log_slow_queries （已废弃） | OFF |

| slow_launch_time | 2 |

| slow_query_log | OFF |

| slow_query_log_file |/var/lib/mysql/twf13-slow.log |

+---------------------+-------------------------------+

4 rows in set (0.00 sec)

mysql> show global status like '%slow%';

+---------------------+-------+

| Variable_name | Value |

+---------------------+-------+

| Slow_launch_threads | 0 |

| Slow_queries | 0 |

+---------------------+-------+

2 rows in set (0.00 sec)

配置中关闭了记录慢查询，执行时间超过2秒的即为慢查询，系统显示有0个慢查询，你可以分析慢查询日志，找出有问题的SQL语句，慢查询时间不宜设置过长，否则意义不大，最好在5秒以内，如果你需要微秒级别的慢查询，可以考虑给MySQL打补丁：http://www.percona.com/docs/wiki/release:start，记得找对应的版本。

打开慢查询日志可能会对系统性能有一点点影响，如果你的MySQL是主-从结构，可以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响又小。（消耗的主要是CPU）

mysqldumpslow查询慢查询日志

2. 连接数

经常会遇见”MySQL: ERROR 1040: Too many connections”的情况，一种是访问量确实很高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力，另外一种情况是MySQL配置文件中max_connections值过小：

max_connections

Max_used_connections

Connections

max_user_connections

back_log

max_connect_errors

max_connections：整个MySQL允许的最大连接数；

这个参数主要影响的是整个MySQL应用的并发处理能力，当系统中实际需要的连接量大于

max_ connections的情况下，由于MySQL的设置限制，那么应用中必然会产生连接请求的等待，

从而限制了相应的并发量。所以一般来说，只要MySQL主机性能允许，都是将该参数设置的尽可能大一点。一般来说500到800左右是一个比较合适的参考值

当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySql会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小

mysql> show variables like 'max_connections';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| max_connections | 151 |

+-----------------+-------+

1 row in set (0.00 sec)

这台MySQL服务器最大连接数是151，然后查询一下服务器响应的最大连接数：

mysql> show global status like 'Max_used_connections';

+----------------------+-------+

| Variable_name | Value |

+----------------------+-------+

| Max_used_connections | 1 |

+----------------------+-------+

查看当前连接数

mysql> show global status like 'Connections';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Connections | 2 |

MySQL服务器过去的最大连接数是1，没有达到服务器连接数上限151，应该没有出现1040错误，比较理想的设置是：

Max_used_connections /max_connections * 100% ≈ 85%（好像和上面有冲突）

最大连接数占上限连接数的85%左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了。

max_user_connections：每个用户允许的最大连接数；是针对于单个用户的连接限制。在一般情况下我们可能都较少使用这个限制，只有在一些专门提供MySQL数据存储服务，或者是提供虚拟主机服务的应用中可能需要用到。

back_log 是要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。如果期望在一个短时间内有很多连接，你需要增加它。也就是说，如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。另外，这值（back_log）限于您的操作系统对到来的 TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制（可以检查你的OS文档找出这个变量的最大值），试图设定 back_log高于你的操作系统的限制将是无效的。

mysql> show variables like 'back_log';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| back_log | 50 |

+---------------+-------+

l max_connect_errors

max_connect_errors默认值为10，如果受信帐号错误连接次数达到10则自动堵塞，需要flush hosts来解除。如果你得到象这样的一个错误：

Host ’hostname’ is blocked because of many connection errors.

Unblock with ’mysqladmin flush-hosts’

这意味着，mysqld已经得到了大量(max_connect_errors)的主机’hostname’的在中途被中断了的连接请求。在 max_connect_errors次失败请求后，mysqld认定出错了(象来字一个黑客的攻击)，并且阻止该站点进一步的连接，直到某人执行命令mysqladmin flush-hosts。

内网连接的话，建议设置在10000以上，已避免堵塞，并定期flushhosts。

l connect_timeout

指定MySQL服务等待应答一个连接报文的最大秒数，超出该时间，MySQL向客户端返回 bad handshake。默认值是5秒，在内网高并发环境中建议设置到10-15秒，以便避免bad hand shake。建议同时关注thread_cache_size并设置thread_cache_size为非0值，大小具体调整。

l skip-name-resolve

skip-name-resolve能大大加快用户获得连接的速度，特别是在网络情况较差的情况下。MySQL在收到连接请求的时候，会根据请求包中获得的ip来反向追查请求者的主机名。然后再根据返回的主机名又一次去获取ip。如果两次获得的ip相同，那么连接就成功建立了。在DNS不稳定或者局域网内主机过多的情况下，一次成功的连接将会耗费很多不必要的时间。假如MySQL服务器的ip地址是广域网的，最好不要设置skip-name- resolve。