mysql 最大连接数的设置与优化

泰山神

　　很多开发人员都会遇见”MySQL: ERROR 1040: Too many connections”的异常情况，造成这种情况的一种原因是访问量过高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力；另一种原因就是MySQL配置文件中max_connections值过小。
　　首先，我们来查看mysql的最大连接数：
　　1
　　2
　　3
　　4
　　5
　　6
　　7
　　mysql> show variables like '%max_connections%';
　　+-----------------+-------+
　　| Variable_name  | Value |
　　+-----------------+-------+
　　| max_connections | 151  |
　　+-----------------+-------+
　　1 row in set (0.00 sec)
　　其次，查看服务器响应的最大连接数:
　　1
　　2
　　3
　　4
　　5
　　6
　　7
　　mysql> show global status like 'Max_used_connections';
　　+----------------------+-------+
　　| Variable_name    | Value |
　　+----------------------+-------+
　　| Max_used_connections | 2   |
　　+----------------------+-------+
　　1 row in set (0.00 sec)
　　可以看到服务器响应的最大连接数为2，远远低于mysql服务器允许的最大连接数值。
　　对于mysql服务器最大连接数值的设置范围比较理想的是：服务器响应的最大连接数值占服务器上限连接数值的比例值在10%以上，如果在10%以下，说明mysql服务器最大连接上限值设置过高。
　　1
　　Max_used_connections / max_connections * 100% = 2/151 *100% ≈ 1%
　　我们可以看到占比远低于10%（因为这是本地测试服务器，结果值没有太大的参考意义，大家可以根据实际情况设置连接数的上限值）。
　　再来看一下自己 linode VPS 现在（时间：2013-11-13 23：40：11）的结果值：
　　1
　　2
　　3
　　4
　　5
　　6
　　7
　　8
　　9
　　10
　　11
　　12
　　13
　　14
　　mysql> show variables like '%max_connections%';
　　+-----------------+-------+
　　| Variable_name  | Value |
　　+-----------------+-------+
　　| max_connections | 151  |
　　+-----------------+-------+
　　1 row in set (0.19 sec)
　　mysql> show global status like 'Max_used_connections';
　　+----------------------+-------+
　　| Variable_name    | Value |
　　+----------------------+-------+
　　| Max_used_connections | 44  |
　　+----------------------+-------+
　　1 row in set (0.17 sec)
　　这里的最大连接数占上限连接数的30％左右。
　　上面我们知道怎么查看mysql服务器的最大连接数值，并且知道了如何判断该值是否合理，下面我们就来介绍一下如何设置这个最大连接数值。
　　方法1：
　　1
　　2
　　3
　　4
　　5
　　6
　　7
　　8
　　9
　　mysql> set GLOBAL max_connections=256;
　　Query OK, 0 rows affected (0.00 sec)
　　mysql> show variables like '%max_connections%';
　　+-----------------+-------+
　　| Variable_name  | Value |
　　+-----------------+-------+
　　| max_connections | 256  |
　　+-----------------+-------+
　　1 row in set (0.00 sec)
　　方法2：
　　修改mysql配置文件my.cnf，在[mysqld]段中添加或修改max_connections值：
　　max_connections=128
　　重启mysql服务即可。
　　问题
　　在使用MySQL数据库的时候，经常会遇到这么一个问题，就是“Can not connect to MySQL server. Too many connections”-mysql 1040错误，这是因为访问MySQL且还未释放的连接数目已经达到MySQL的上限。通常，mysql的最大连接数默认是100, 最大可以达到16384。
　　常用的有两种方式修改最大连接数。
　　解决方式一：通过命令
　　可以通过 set GLOBAL max_connections=100; 命令将最大连接数设置为100，此方法是即时生效的，不需要重启mysql服务。如下图所示：

http://files.iyunv.net/file_images/article/201608/201685154355396.jpg?201675154410

　　需注意的是，要通过root权限的mysql帐号才能操作，否则会报“1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation”的错误。
　　同时，从上图也可以看出，设置max_connections最小值为1。
　　设置完成后，我再通过另一个终端连接时，就报出了如下的错误提示：1040-Too many connections。

http://files.iyunv.net/file_images/article/201608/201685154510871.jpg?201675154519

　　解决方式二：修改my.cnf
　　打开mysql的配置文件vim /etc/my.cnf，加入max_connections=100一行（如果有，直接修改值即可），然后重启服务：/etc/init.d/mysqld restart，此时生效。

http://files.iyunv.net/file_images/article/201608/201685154558933.jpg?201675154635

　　区别：
　　1.通过修改配置文件，需要重启服务；而用命令修改，即时生效。
　　2.采用修改配置文件的方式，更稳定可靠。因为如果配置文件中有max_connections=100，再去用命令修改的话，一旦重启mysql服务后，会重新以配置文件中指定的连接数为准。
　　总结：
　　在修改最大连接数的时候会有这样一个疑问—这个值是不是越大越好，或者设置为多大才合适？这个参数的大小要综合很多因素来考虑，比如使用的平台所支持的线程库数量（windows只能支持到2048）、服务器的配置（特别是内存大小）、每个连接占用资源（内存和负载）的多少、系统需要的响应时间等。可以在global或session范围内修改这个参数。连接数的增加会带来很多连锁反应，需要在实际中避免由此引发的负面影响。希望本文大家使用mysql有所帮助。