MySQL服务器设置优化之二-连接数调优

#MySQL服务器设置优化之二-连接数调优

如需转载请标明出处：http://blog.csdn.net/itas109
QQ技术交流群：129518033

环境：
MySQL版本：5.5.15
操作系统：windows

1.客户端连接的最大允许数量

优化参数：max_connections
参数作用：同时进行的客户端连接的最大允许数量
优化方法：
在mysql的配置文件my.ini中修改max_connections参数。

[mysqld]

# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections=1000

注意： 该参数在服务器资源够用的情况下应该尽量设置大，以满足多个客户端同时连接的需求。否则将会出现类似”Too many connections”的错误。

原文：

max_connections

The maximum permitted number of simultaneous client connections. By default, this is 151. See Section B.5.2.7, “Too many connections”, for more information.

Increasing this value increases the number of file descriptors that mysqld requires. See Section 8.4.3.1, “How MySQL Opens and Closes Tables”, for comments on file descriptor limits.

mysqld actually permits max_connections+1 clients to connect. The extra connection is reserved for use by accounts that have the SUPER privilege. By granting the SUPER privilege to administrators and not to normal users (who should not need it), an administrator can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected. See Section 13.7.5.30, “SHOW PROCESSLIST Syntax”.

2.客户端连接错误最大数量

优化参数：max_connect_errors
参数作用：负责阻止过多尝试失败的客户端。max_connect_errors的值与性能并无太大关系。
优化方法：
默认情况下，my.ini文件中可能没有此行。
在mysql的配置文件my.ini中添加max_connect_errors参数。

[mysqld]

max_connect_errors=1000

注意： 如果某个客户端的连接达到了max_connect_errors的限制，将被禁止访问，并提示以下错误： Host 'xxx' is blocked because of many connection errors.Unblock with 'mysqladmin flush-hosts' 解决方式： a.非锁定的mysql客户端中执行 ``` flush hosts; ``` b..非锁定的mysql客户端bin目录的mysqladmin.exe

mysqladmin flush-hosts -h192.168.2.100 -P3306   -uroot  -p123456

c.max_connections、max_connect_errors参数调优

---

原文：
max_connect_errors

If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections. You can unblock blocked hosts by flushing the host cache. To do so, issue a FLUSH HOSTS statement or execute a mysqladmin flush-hosts command. If a connection is established successfully within fewer than max_connect_errors attempts after a previous connection was interrupted, the error count for the host is cleared to zero. However, once a host is blocked, flushing the host cache is the only way to unblock it.

##3.未完待续…

---

Reference：
https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html

---

觉得文章对你有帮助，可以扫描二维码捐赠给博主，谢谢！

如需转载请标明出处：http://blog.csdn.net/itas109
QQ技术交流群：129518033