MySQL服务器最大连接数怎么设置才合理

如果mysql 连接数据设置不合理可能会导致很小的流量mysql就提示MySQL:ERROR 1040: Too many connections错误了,那么要如何才算是合理设置mysql最大连接数呢,下面我来给大家介绍介绍.

MySQL服务器的连接数并不是要达到最大的100%为好,还是要具体问题具体分析,下面就对MySQL服务器最大连接数的合理设置进行了详尽的分析,供您参考.

我们经常会遇见“MySQL: ERROR 1040: Too many connections”的情况,通常,mysql的最大连接数默认是100,最大可以达到16384.

一种是访问量确实很高,MySQL服务器抗不住,这个时候就要考虑增加从服务器分散读压力,另外一种情况是MySQL配置文件中max_connections值过小,代码如下:

1. mysql> show variables like 'max_connections'; 
2. +-----------------+-------+ 
3. | Variable_name | Value | 
4. +-----------------+-------+ 
5. | max_connections | 256 | 
6. +-----------------+-------+ 

这台MySQL服务器最大连接数是256,然后查询一下服务器响应的最大连接数:

mysql> show global status like 'Max_used_connections';

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

Max_used_connections / max_connections * 100% ≈ 85%

最大连接数占上限连接数的85%左右,如果发现比例在10%以下,MySQL服务器连接上线就设置得过高了,在Windows下常用的有两种方式修改最大连接数.

第一种:命令行修改。

>mysql -uuser -ppassword(命令行登录MySQL)

mysql>show variables like 'max_connections';(查可以看当前的最大连接数)

msyql>set global max_connections=1000;(设置最大连接数为1000,可以再次查看是否设置成功)

mysql>exit(推出)

这种方式有个问题,就是设置的最大连接数只在mysql当前服务进程有效,一旦mysql重启,又会恢复到初始状态,因为mysql启动后的初始化工作是从其配置文件中读取数据的,而这种方式没有对其配置文件做更改.

第二种:修改配置文件.

这种方式说来很简单,只要修改MySQL配置文件my.ini 或 my.cnf的参数max_connections,将其改为max_connections=1000,然后重启MySQL即可,但是有一点最难的就是my.ini这个文件在哪找。通常有两种可能,一个是在安装目录下,这是比较理想的情况,另一种是在数据文件的目录下,安装的时候如果没有人为改变目录的话,一般就在C:/ProgramData/MySQL往下的目录下.

与连接数相关的几个参数:

在修改最大连接数的时候会有这样一个疑问—这个值是不是越大越好，或者设置为多大才合适？这个参数的大小要综合很多因素来考虑，比如使用的平台所支持的线程库数量（windows只能支持到2048）、服务器的配置（特别是内存大小）、每个连接占用资源（内存和负载）的多少、系统需要的响应时间等。可以在global或session范围内修改这个参数。连接数的增加会带来很多连锁反应，需要在实际中避免由此引发的负面影响。

首先看一下MySQL的状态,代码如下:

1. mysql> status; 
2. -------------- 
3. mysql  Ver 14.14 Distrib 5.5.15, for Win32 (x86) 
4. Connection id:          1 
5. Current database: 
6. Current user:           root@localhost 
7. SSL:                    Not in use 
8. Using delimiter:        ; 
9. Server version:         5.5.15 MySQL Community Server (GPL) 
10. Protocol version:       10 
11. Connection:             localhost via TCP/IP 
12. Server characterset:    utf8 
13. Db     characterset:    utf8 
14. Client characterset:    gbk 
15. Conn.  characterset:    gbk 
16. TCP port:               3306 
17. Uptime:                 1 hour 3 min 27 sec 
18. Threads: 12  Questions: 18  Slow queries: 10  Opens: 33  Flush tables: 5  Open tab 
19. les: 34  Queries per second avg: 6.256 
20. -------------- 

Open tables:34,即当前数据库打开表的数量是34个,注意这个34并不是实际的34个表，因为MySQL是多线程的系统,几个不同的并发连接可能打开同一个表,这就需要为不同的连接session分配独立的内存空间来存储这些信息以避免冲突,因此连接数的增加会导致MySQL需要的文件描述符数目的增加,另外对于MyISAM表,还会建立一个共享的索引文件描述符.

在MySQL数据库层面,有几个系统参数决定了可同时打开的表的数量和要使用的文件描述符,那就是table_open_cache、max_tmp_tables和open_files_limit,代码如下:

1. mysql> show variables like 'table_open%'; 
2. +------------------+-------+ 
3. | Variable_name    | Value | 
4. +------------------+-------+ 
5. | table_open_cache | 256   | 
6. +------------------+-------+ 
7. 1 row in set (0.00 sec) 

table_open_cache:256,这就是说所有的MySQL线程一共能同时打开256个表,我们可以搜集系统的打开表的数量的历史记录和这个参数来对比,决定是否要增加这个参数的大小,查看当前的打开表的数目(Open tables)可用上边提到过的status命令,另外可以直接查询这个系统变量的值,代码如下:

1. mysql> show status like 'open_tables'; 
2. +---------------+-------+ 
3. | Variable_name | Value | 
4. +---------------+-------+ 
5. | Open_tables   | 3     | 
6. +---------------+-------+ 
7. 1 row in set (0.00 sec) 

Open_tables就是当前打开表的数目,通过flush tables命令可以关闭当前打开的表,这个值如果过大，并且如果没有经常的执行flush tables命令,可以考虑增加table_open_cache参数的大小.

接下来看max_tmp_tables:

1. mysql> show variables like 'max_tmp%'; 
2. +----------------+-------+ 
3. | Variable_name  | Value | 
4. +----------------+-------+ 
5. | max_tmp_tables | 32    | 
6. +----------------+-------+ 
7. 1 row in set (0.00 sec) 

max_tmp_tables:32即单个客户端连接能打开的临时表数目,查看当前已打开的临时表的信息:

1. mysql> show global status like '%tmp%table%'; 
2. +-------------------------+-------+ 
3. | Variable_name           | Value | 
4. +-------------------------+-------+ 
5. | Created_tmp_disk_tables | 0     | 
6. | Created_tmp_tables      | 11    | 
7. +-------------------------+-------+ 
8. 2 rows in set (0.00 sec) 

根据这两个值可以判断临时表的创建位置,一般选取BLOB和TEXT列、Group by 和 Distinct语句的数据量超过512 bytes，或者union的时候select某列的数据超过512 bytes的时候，就直接在磁盘上创建临时表了，另外内存中的临时表变大的时候，也可能被MySQL自动转移到磁盘上（由tmp_table_size和max_heap_table_size参数决定）。

增加table_open_cache或max_tmp_tables 参数的大小后，从操作系统的角度看，mysqld进程需要使用的文件描述符的个数就要相应的增加，这个是由open_files_limit参数控制的。

1. mysql> show variables like 'open_files%'; 
2. +------------------+-------+ 
3. | Variable_name    | Value |  --Vevb.com 
4. +------------------+-------+ 
5. | open_files_limit | 2670  | 
6. +------------------+-------+ 
7. 1 row in set (0.00 sec) 

但是这个参数是OS限制的，所以我们设定的值并不一定总是生效。如果OS限制MySQL不能修改这个值，那么置为0。如果是专用的MySQL服务器上，这个值一般要设置的尽量大，就是设为没有报Too many open files错误的最大值，这样就能一劳永逸了。当操作系统无法分配足够的文件描述符的时候，mysqld进程会在错误日志里记录警告信息。

相应的,有两个状态变量记录了当前和历史的文件打开信息:

1. mysql> show global status like '%open%file%'; 
2. +---------------+-------+ 
3. | Variable_name | Value | 
4. +---------------+-------+ 
5. | Open_files    | 0     | 
6. | Opened_files  | 76    | 
7. +---------------+-------+ 
8. 2 rows in set (0.00 sec) 

MySQL为每个连接分配线程来处理,可以通过threads_connected参数查看当前分配的线程数量:

1. mysql> show status like '%thread%'; 
2. +------------------------------------------+-------+ 
3. | Variable_name                            | Value | 
4. +------------------------------------------+-------+ 
5. | Delayed_insert_threads                   | 0     | 
6. | Performance_schema_thread_classes_lost   | 0     | 
7. | Performance_schema_thread_instances_lost | 0     | 
8. | Slow_launch_threads                      | 0     | 
9. | Threads_cached                           | 0     | 
10. | Threads_connected                        | 1     | 
11. | Threads_created                          | 1     | 
12. | Threads_running                          | 1     | 
13. +------------------------------------------+-------+ 
14. 8 rows in set (0.00 sec) 

比较threads_connected参数和前面提到的max_connections参数,也可以作为目前的系统负载的参照,决定是否需要修改连接数.