本文将为您介绍如何对实例中的连接进行诊断和管理。
连接和Query管理概述
- 查询实例的默认最大连接数:不同的实例规格对应不同的默认连接数,通过命令查询当前实例规格的最大连接数。
- HoloWeb可视化管理连接:通过HoloWeb可视化查看活跃连接,并进行管理如Kill等操作。
- 通过SQL查询连接信息:通过查询实例、DB的连接数、每个连接状态以及终止空闲连接,更好的管理实例。
- 释放连接:通过SQL函数,释放指定连接资源。
- 管理员预留连接:用于在连接数达到最大时对连接进行管理操作。
- 单个用户连接数限制:为单个用户设置连接数上限,以防止某个用户占用过多连接造成资源浪费。
- 自动释放空闲连接(Beta):开启自动释放空闲连接功能自动释放长期不使用连接。
- 连接数使用最佳实践:使用Hologres连接数的最佳实践建议。
查询pg_stat_activity视图信息
select * from pg_stat_activity ;
pg_stat_activity视图的参数说明如下所示。字段 | 描述 |
---|---|
datid | Hologres后端连接到的数据库的OID。 |
datname | Hologres后端连接到的数据库的名称。 |
pid | Hologres后端的进程ID。 |
usesysid | 登录到Hologres后端的用户OID。 |
usename | 当前连接的用户名。 |
application_name | 客户端的应用类型。 其中常见的应用类型如下:
|
client_addr | 客户端的IP地址。 显示的IP地址可能是被解析过的,不保证一定是源端IP。 |
client_hostname | 客户端的主机名。 |
client_port | 客户端的端口。 |
backend_start | 后台进程开始的时间。 |
xact_start | 该进程的当前事务被启动的时间。
|
query_start | 当前活动查询开始的时间,如果当前连接状态不是active,取值为上一个查询开始的时间。 |
state_change | 连接的状态(state)上一次被改变的时间。 |
wait_event_type | 后端正在等待的事件类型,如果不存在则为NULL。可能的取值有:
|
wait_event | 如果后端当前正在等待,则是等待事件的名称,否则为 NULL。 |
state | 表示连接的状态。常见的状态如下:
|
backend_xid | Hologres后端的顶层事务标识符。 |
backend_xmin | 当前后端的xmin范围。 |
query | 后端最近执行的查询。如果state为active ,将会显示当前正在执行的查询。在所有其他状态下,显示上一个被执行的查询。 |
backend_type | 当前后端的类型。可能的类型为autovacuum launcher、autovacuum worker、logical replication launcher、logical replication worker、parallel worker、background writer、client backend、checkpointer、startup、walreceiver、walsender以及 walwriter。除此之外还包括后端的执行组件,例如PQE等。 说明 需要关注 client backend 类型,代表应用的连接。 |
使用说明
- 仅超级管理员(Superuser)可以看到所有连接信息,普通用户只能看到自己的连接信息。
- SQL查询结果中,
holo_admin
账号为系统账号,用于后台进程管理,会定期释放,无需特别关注。
查询实例的默认最大连接数
- 查询当前实例的最大连接数,Hologres V1.3.23及以上版本支持。
select instance_max_connections();
- 查询单个FrontEnd(FE)节点的最大连接数。实例总连接数即为FE数量乘以单个节点的最大连接数。
show max_connections;
HoloWeb可视化管理连接
- 登录HoloWeb控制台,详情请参见连接HoloWeb。
- 单击顶部导航栏的诊断与优化。
- 在左侧导航栏单击活跃连接管理。
- 进入活跃连接管理页面,配置好参数,可查看实例的连接以及相关信息。参数说明如下。说明 仅Superuser账号可以看到全部连接信息,普通用户只能看到自己的连接信息。
参数 说明 实例名 Hologres实例名称。 数据库 Hologres数据库名称,可选择指定数据库,不选择则默认展示全部数据库。 Database 连接的数据库名称。 说明 若数据库名称为Postgres,则说明是后台运维连接,可忽略。User Name 连接的用户账号。 Client Address 客户端IP地址,但不一定是真实的IP,可能是经过路由转发的出口IP。 Application Name 连接的应用名称。 State 连接的状态,常见的状态如下。 - active:活跃。
- idle:空闲。
- idle in transaction:长事务中的空闲状态。
- idle in transaction(Aborted):已失败事务中的空闲状态。
Query Start Query开始时间。 Query 执行的Query。 说明 若是Query太长,可能会被截断。PID Query的pid。 操作 - Kill:不符合预期的连接可直接Kill或者批量删除。
- 详情:单击可以看到详细的连接信息。
通过SQL查询连接信息
若您更倾向于使用SQL的方式查询连接信息,可以通过以下方式操作。
- 查询当前数据库的连接数。您可以通过如下命令查看当前数据库的连接数,更多关于参数的说明,请参见参数说明。
- Hologres V1.1及以上版本。
SELECT datname::TEXT ,COUNT(1) AS COUNT FROM pg_stat_activity WHERE backend_type = 'client backend' AND application_name != 'hologres' AND usename != 'holo_admin' GROUP BY datname::TEXT;
- Hologres V0.10及以下版本。
SELECT datname ,COUNT(1) AS COUNT FROM pg_stat_activity WHERE backend_type = 'client backend' AND application_name != 'hologres' AND usename != 'holo_admin' GROUP BY datname;
- Hologres V1.1及以上版本。
- 查看每个连接的状态。您可以在Hologres管控台通过HoloWeb查看每个实例的连接状态,也可以执行如下语句,通过查询pg_stat_activity视图来获取所有JDBC或PSQL连接的状态。
其中statename是需要填写的状态参数名,包括以下几种:select * from pg_stat_activity where backend_type = 'client backend' and state = '<statename>';
- idle:空闲连接,表示进程在等待新的客户端命令。
- active:活跃连接,表示进程正在执行查询操作。
- idle in transaction:表示进程处于一个事务中,但是当前没有执行查询操作。
- idle in transaction (aborted):表示进程处于一个事务中,该事务存在语句错误,并且进程当前没有执行查询操作。
- fastpath function call:表示进程正在执行一个
fast-path
函数。 - disabled:表示进程的
track activities
被禁用。
select * from pg_stat_activity where backend_type = 'client backend' and state = 'idle';
Holoweb等Hologres周边组件会通过JDBC的方式占用一定的连接数,如果您的连接数满足需求,无需关心此类连接数的占用。当SQL连接数长期接近或达到
max_connections
时,意味着您需要检查您的应用是否存在连接数泄漏情况,需要在应用端合理设置连接池大小,或者您也可以根据下面章节释放空闲连接,详情请参见释放连接。 - 查看每个接入节点的连接数查询实例当前每个FrontEnd节点的连接数,Hologres V1.3.23版本起支持。当前没有连接使用的FE节点不会显示在查询结果中。
查询结果字段含义如下:select * from hologres.hg_connections;
- fe_id:FE节点的编码。
- used_connections:FE节点当前已使用的连接数。
- max_connections:FE节点的最大连接数,同
show max_connections
命令的返回值。
释放连接
如果您遇到如下情况,则说明系统连接数(或者某个接入节点)已经达到上限:
- 连接数达到甚至超出
max_connections
的取值,您可以在Hologres管控台的监控告警页查看连接数。 - 产生
FATAL: sorry, too many clients already connection limit exceeded for superusers
报错。 - 产生
FATAL: remaining connection slots are reserved for non-replication superuser connections
报错。
select * from pg_stat_activity where backend_type = 'client backend' and state = 'idle';
如果查询结果显示空闲进程过多,并且确定是无用的空闲连接时,可以找到上述语句结果中的pid字段,并执行如下语句释放空闲连接。更多关于参数的说明,请参见参数说明。select pg_cancel_backend(<pid>); -- cancel该连接上的query
select pg_terminate_backend(<pid>); --杀掉对应的后台连接进程
--批量终止后台IDLE连接进程,释放连接
SELECT pg_terminate_backend(pid)
,query
,datname
,usename
,application_name
,client_addr
,client_port
,backend_start
,state
FROM pg_stat_activity
WHERE length(query) > 0
AND pid != pg_backend_pid()
AND backend_type = 'client backend'
AND state = 'idle'
AND application_name != 'hologres'
AND usename != 'holo_admin'
AND query not like '%pg_cancel_backend%';
管理员预留连接
Hologres会为Superuser预留连接,不同的实例规格预留的连接数不同,详情请参见实例规格概述。Superuser预留连接用于在连接数达到最大时对连接进行管理操作(如终止idle连接),普通用户的连接数最大为max_connections减去预留连接。在实践中,不建议普通用户使用Superuser账号操作数据库,否则会导致连接全部占满,且无法通过管理渠道释放连接。
单个用户连接数限制
由于Hologres兼容PostgreSQL,因此支持为单个用户设置连接数上限,以防止某个用户占用过多连接造成资源浪费。
- 限制单个用户在单个接入节点上的最大连接数。
- 语法示例
ALTER ROLE "云账号ID" CONNECTION LIMIT <number>;
- 参数说明
参数 说明 云账号ID 需要限制的账号ID,如果为RAM用户,需要在账号UID前加p4_。更多关于账号的说明,请参见账号概述。 number 限制的连接数个数。 - 使用示例如下示例限制RAM用户283813xxxx一个节点最多只有一个连接。
ALTER ROLE "p4_283813xxxx" CONNECTION LIMIT 1;
- 语法示例
- 您可以执行如下语句查看当前已经为实例用户设置的单节点限制连接数。
查询示例结果如下:SELECT rolname, rolconnlimit FROM pg_roles WHERE rolconnlimit <> -1;
rolname | rolconnlimit ---------------+-------------- p4_283813xxxx | 1 (1 row)
自动释放空闲连接(Beta)
当实例存在连接数长期接近或达到上限值时,可能存在连接泄漏情况,可以开启自动释放空闲连接功能来释放长期不使用连接。当连接空闲时间(无SQL执行)超过设置时间时,连接会自动被断开。
- 使用限制
仅Hologres V0.10.25及以上版本支持自动释放空闲连接功能,如果您的实例是V0.10.25以下版本,请您使用自助升级或加入实时数仓Hologres交流群(钉钉群号:32314975)申请升级实例。加入在线支持钉钉群请参见如何获取更多的在线支持?
- 语法示例
- session级别。
-- 连接在10分钟没有执行sql的情况,会自动断开,单位为毫秒。 SET idle_session_timeout = 600000;
- 数据库级别。
db_name为要开启自动释放空闲连接功能的数据库名称。-- 连接在10分钟没有执行sql的情况,会自动断开,单位为毫秒。 ALTER DATABASE <db_name> SET idle_session_timeout = 600000;
- session级别。
连接数使用最佳实践
- 善用Superuser账号
- 不建议使用Suerpser账号直接操作实例或者连接应用,否则当连接数超过实例默认连接数之后,账号将无法连接实例。
- 建议专门设置一个Superuser账号为运维账号,当出现连接数超过实例默认连接数、Query卡死等紧急情况时,使用该Superuser账号登录HoloWeb管理连接和Query。
- 设置合理的连接池
- 为了业务安全考虑,Hologres不会在后台自动释放连接,建议业务上给应用设置合理的连接池机制,让空闲连接及时释放。
- 定期清理空闲连接,防止空闲连接占用过多连接影响线上业务。
- Hologres连接中,名称为
holo_admin
的连接为后台运维连接,会定期清理,可以忽略。
常见报错
- 错误提示:执行sql时报错:
terminating connection due to idle state timeout
。 - 可能原因:为实例设置了自动释放连接的超时,超过设置的超时时间后,连接就会自动断连,然后出现报错。
- 解决方案:重新连接,或者修改空闲连接超时时间为更大的数值,修改空闲连接超时时间操作请参见连接数管理。