SQL语句执行卡住问题部分排查思路
平时正常执行的SQL,某个时刻执行了很长时间仍未返回结果。通常有以下几种情况:
1)SQL语句执行可能涉及到不同粒度级别的锁申请和对象锁定,也就是说不同的SQL语句执行时,如果访问相同对象可能会冲突,需要先排查锁冲突情况,SQL被哪些会话阻塞了,,表现为有阻塞会话(pid大于0);
2)SQL语句可能被2pc两阶段事务阻塞,表现为阻塞会话pid=0;
3)SQL语句可能被同步事务阻塞,在配置了同步备机,但同步备机有异常或延迟的情况,表现为会话等待事件类型为SyncRep;
4)SQL语句没有被阻塞,可能因统计信息不准确而生成了差的执行计划,执行时间太长。
TeleDB分布式架构,在CN节点上显示为运行中的SQL,可能会在某个DN节点上执行被阻塞,所以需要检查每个DN节点该SQL的执行情况。
SQL语句被其它SQL语句阻塞问题
问题描述
平时正常执行的SQL,执行了很长时间仍未返回结果,通过pg_blocking_pids(pid)得到有阻塞的会话pid,且pid大于0。
每个CN、DN主节点上执行以下SQL,检查是否有会话阻塞:
select pid,pg_blocking_pids(pid),EXTRACT(EPOCH FROM (now()-query_start)),
wait_event_type,wait_event,query
from pg_stat_activity
where wait_event_type ='Lock' and pid!=pg_backend_pid();
返回如下示例内容,pg_blocking_pids 返回非0,则表示该进程阻塞了当前SQL;
pid | pg_blocking_pids | date_part | wait_event_type | wait_event | query
------+------------------+-----------+-----------------+---------------+---------------------------------------
1302 | {11582} | 102.134252 | Lock | transactionid | INSERT INTO test (id, c1) VALUES
(1, 1)
(1 row)
可能影响
SQL执行时间很长未返回结果,导致相关业务受影响。
解决步骤
- 执行以下SQL,进一步检查阻塞会话ID对应的SQL语句,是否被其它会话阻塞;
select pid,pg_blocking_pids(pid),EXTRACT(EPOCH FROM (now()-query_start)),
wait_event_type,wait_event,query,query
from pg_stat_activity
where pid=11582;
- 与业务侧确认,阻塞的会话是否可以停掉;如果可以停掉,执行以下SQL停掉阻塞的会话:
取消会话,执行以下SQL,可能不成功,其中xxx为需要停掉的SQL会话的PID:
select pg_cancel_backend(xxx);
如果取消会话不成功,执行以下SQL,其中xxx为需要停掉的SQL会话的PID:
select pg_terminate_backend(xxx);
- 常见的SQL阻塞场景:执行DDL语句,例如TRUNCATE TABLE清空表、ALTER TABLE修改表结构,会申请独占锁,如果有会话在访问这个表,那么DDL语句就会被阻塞,等待这个表上的会话结束;后续这个表上的SELECT语句又会被DDL语句阻塞;造成更大面积的阻塞。
针对此类场景,有如下建议:
1)DDL语句属于表结构变更语句,生产环境中不应随意执行,需要申请维护窗口;
2)在执行DDL语句前,可以先检查该该表上是否有会话,在允许的前提下先清理会话,再执行DDL语句;
3)所有DDL语句执行产,建议会话级设置lock_timeout参数,在一段时间内如果没有申请到锁就退出,避免造成大面积SQL阻塞;例如 set lock_timeout='10s',那么DDL语句如果10秒没有获取到独占锁,就会退出。
SQL语句被2pc事务阻塞问题
问题描述
在配置了同步备机,同步备机有异常或延迟的情况下,SQL语句被同步事务阻塞,表现为阻塞会话pid=0;
每个CN、D主节点上执行以下SQL,检查是否为同步复制:
select pid,pg_blocking_pids(pid),EXTRACT(EPOCH FROM (now()-query_start)),
wait_event_type,wait_event,query
from pg_stat_activity
where wait_event_type ='Lock' and pid!=pg_backend_pid();
返回以下如内容,pg_blocking_pids=0,则说明被2pc事务阻塞。
datname | pid | pg_blocking_pids | date_part | wait_event_type | wait_event | query
----------+-------+------------------+-------------+-----------------+---------------+----------------
teledb | 31742 | {0} | 1385.124791 | Lock | transactionid | Remote Subplan
(1 row)
可能影响
发现SQL长时间被2pc事务阻塞,说明有2pc残留,需要及时处理,否则会导致相关SQL持续被阻塞。
解决步骤
参考2pc残留类问题处理,清理2pc残留。
SQL语句被同步事务阻塞问题
问题描述
在配置了同步备机,同步备机有异常或延迟的情况下,SQL语句被同步事务阻塞,表现为会话等待事件类型为SyncRep;
每个CN、D主节点上执行以下SQL,检查是否为同步复制:
teledb=# select current_setting('synchronous_commit') as synchronous_commit,
current_setting('synchronous_standby_names') as synchronous_standby_names,
array((select sync_state from pg_stat_replication)) as sync_state;
synchronous_commit | synchronous_standby_names | sync_state
--------------------+------------------------------+------------
on | FIRST 1 (172.16.16.16:11300) | {sync}
(1 row)
如果返回值为synchronous_commit = on、synchronous_standby_names 不为空则表示主备为同步复制模式,此时sync_state 值必需为 sync,否则无法执行 ddl 或者更新的 dml 操作。
每个CN、D主节点上执行以下SQL,检查是否有同步复制等待:
select query,wait_event_type,wait_event,EXTRACT(EPOCH FROM (now()-query_start)) from
pg_stat_activity where wait_event='SyncRep';
如果返回有wait_event='SyncRep'的记录,说明SQL在等待同步复制备机响应。
可能影响
同步复制备机异常或延迟,导致DDL、DML、COMMIT语句等待处理等待状态。
解决步骤
- 检查同步复制备机状态,尝试修复;
- 如果不能快速修复,在与业务侧沟通取得确认后,可暂时将同步复制模式更为异步复制;
修改为异步方法参考:修改主节点配置文件postgresql.conf.user 中以下参数:
synchronous_commit =local
synchronous_standby_names=''
配置文件修改完成后,执行select pg_reload_conf();重新加载配置;
- 修复同步复制备机,恢复同步复制模式。
SQL语句执行慢的问题
问题描述
平时正常执行的SQL,某个时刻执行了很长时间仍未返回结果,但未找到阻塞的会话,也没有同步事务阻塞,正常执行中,仅是执行慢。
可能因统计信息不准确而生成了差的执行计划,执行时间太长。
可能影响
平时正常执行的SQL,某个时刻执行了很长时间仍未返回结果,导致相应业务受影响。
解决方案
- 手动执行explain SQL查看执行计划,确认执行计划是否符合预期;
- 有条件的情况,可以执行explain (analyze,verbose,buffers) SQL查看实际执行计划,找到具体慢的位置,并进行相应优化;
- 对于应用调用SQL使用绑定变量的情况,需要核实传入的变量类型与字段类型是否匹配,是否有隐式类型转换导致分布键、索引失效;
- 是否有truncate+insert等大批量数据变更,导致统计信息不准确;可以尝试手动收集该表的统计信息,然后再执行SQL语句;
- 借助一些插件,观察应用调用的SQL语句执行过程中的执行计划,验证执行计划是否是符合预期的。