PG、MySQL、CH最大连接数问题集合

在日常工作中，对于数据库难免处理一些最大连接数相关的问题，原因都是大同小异，一般都是业务做统计完成之后不关掉，访问量过高，或者timeout时间设置不合理等问题导致监控报警。

对于最大连接数的调整，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的情况下，因为如果连接数越来越多，数据库实例为每个连接提供缓冲区，就会开销越多的内存，所以需要适当的调整该值，较合理的最大连接数一般 used_connections/max_connections在85%~90%左右正好。

1. PostgreSQL
   对于PostgreSQL而言，最大连接数默认值是100。并没有在线设置最大连接数相关的配置，而且即使可以设置，也是一刀切，并没有真正处理掉已经很久的 state = ‘idle’ 连接。

处理流程：

a. 查询当前连接数
selelct count(1) from pg_stat_activity;
b. 查询最大连接数
show max_connections;
c. 查询分析维度
select client_addr，count(*) from pg_stat_activity where state = 'idle' group by 'client_addr';

select * from pg_stat_activity where state = 'idle' and client_addr = 'x.x.x.x' and state_change < (now()-interval '5h')::timestamp;

select * from pg_stat_activity where state = 'idle' and application_name='NAVICAT' and client_addr = 'x.x.x.x' and state_change < (now()-interval '5h')::timestamp;
d. 处理
select pg_terminate_backend(pid) from pg_stat_activity where state = 'idle' and client_addr = 'x.x.x.x' and application_name='NAVICAT' and state_change < (now()-interval '5h')::timestamp;

如果通过计算，确实存在设置不合理的情况，可以设置适当重载，但不建议立即重启！

2. MySQL
   对于MySQL而言，最大连接数默认值是151，其实可以连接151+
   本地socket个连接。MySQL是有在线设置参数的功能，但是治标不治本。
   监控中可能会遇到MySQL: ERROR 1040: Too many connections的异常情况，造成这种情况的一种原因是访问量过高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力；另一种原因就是MySQL配置文件中max_connections值过小。

处理流程：

a. 查看mysql的最大连接数和默认连接超时
show variables like '%max_connections%';
select @@max_connections;

+-------------------+
| @@max_connections |
+-------------------+
|               151 |
+-------------------+
1 row in set (0.00 sec)


show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |                    
+---------------+-------+

1 row in set (0.00 sec)

默认连接释放8小时
    b. 调整时的参考值
show global status like 'Max_used_connections';

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 114   |
+----------------------+-------+

1 row in set (0.00 sec)

对于MySQL服务器最大连接数值的设置范围比较理想的是：服务器响应的最大连接数值占服务器上限连接数值的比例值在10%以上，如果在10%以下，说明MySQL服务器最大连接上限值设置过高。

max_used_connections / max_connections * 100% （理想值≈ 85%）

select concat(round(114/151*100),'%') as percent;
+---------+
| percent |
+---------+
| 75%     |
+---------+

1 row in set (0.00 sec)
c.查询MySQL线程
show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
| 2 | root | localhost | NULL | Query   |    0 | starting | show processlist |
|  3 | root | localhost | NULL | Sleep   |  199 |          | NULL             |
+----+------+-----------+------+---------+------+----------+------------------+

2 rows in set (0.00 sec)

4.处理方式：

方式一：  kill或者pt-kill掉Command='sleep'

kill 3;
Query OK, 0 rows affected (0.00 sec)

show full processlist;
+----+------+-----------+------+---------+------+----------+-----------------------+
| Id | User | Host      | db   | Command | Time | State    | Info                  |
+----+------+-----------+------+---------+------+----------+-----------------------+
|  2 | root | localhost | NULL | Query   |    0 | starting | show full processlist |
+----+------+-----------+------+---------+------+----------+-----------------------+

1 row in set (0.00 sec)

方式二:根据参考值计算百分比适当在线调参
select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|               151 |
+-------------------+

1 row in set (0.00 sec)

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

set global wait_timeout=600;
Query OK, 0 rows affected (0.00 sec)

select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|               200 |
+-------------------+

1 row in set (0.00 sec)

show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 600   |
+---------------+-------+

1 row in set (0.00 sec)

在线修改完成之后需要手动修改my.cnf配置文件，防止重启之后在线设置失效。

3. ClickHouse

对于ClickHouse而言，配置文件实时生效很是方便。

vim /etc/clickhouse/config.xml

    <max_connections>4096</max_connections>
    <keep_alive_timeout>3</keep_alive_timeout>
    <max_concurrent_queries>100</max_concurrent_queries>

除过以上参数的资源限制，更多查询配额、权限相关详细信息请点击：ClickHouse的用户配置详解及SQL化用户配置

更多精彩内容欢迎关注