PostgreSQL运维案例--递归查询死循环

一、问题背景

某日,开发同事上报一sql性能问题,一条查询好似一直跑不出结果,查询了n小时,还未返回结果。比较诡异的是同样的sql,相同的数据量,相同的表大小,且在服务器硬件配置相同的情况下,在另外一套环境查询非常快,毫秒级。

第一时间排查了异常环境的查询进程stack,并抓取了一分钟的strace。从结果得知进程是正常执行的,那么看起来就是查询慢的问题了。

最终发现是递归查询出现了死循环,以下内容均是在个人电脑进行的模拟复现

sql语句如下:

with s as (select * from emp_info where empno='200' and emp_type>'5' and emp_status='Y')
  
  select 
  s.empno as "staffNo",
  s.emp_type as "empType",
  s.emp_tel_info as "empNum",
  a.cust_name as "Name",
  a.cust_position as "Postion",
  a.cust_addr as "Addr",
  a.cust_tel_info as "Mobile",
  (
    with  recursive r as (select f.ctid,f.region_code,f.parent_region_code,f.region_type,f.region_status 
	from region_tbl f where f.region_code=s.region_code union all select f.ctid, f.region_code,f.parent_region_code,
	f.region_type,f.region_status from region_tbl f,r where f.region_code=r.parent_region_code)
	
	select r.region_code as "FirstRegCode" 
	from r where r.region_type='5'
	and r.region_status='Y'
  ),
  (
    with  recursive r as (select f.ctid,f.region_code,f.parent_region_code,f.region_type,f.region_status 
	from region_tbl f where f.region_code=s.region_code union all select f.ctid, f.region_code,f.parent_region_code,
	f.region_type,f.region_status from region_tbl f,r where f.region_code=r.parent_region_code)
	
	select r.region_code as "SecondRegCode" 
	from r where r.region_type='4'
	and r.region_status='Y'
  ),
  (
    with  recursive r as (select f.ctid,f.region_code,f.parent_region_code,f.region_type,f.region_status 
	from region_tbl f where f.region_code=s.region_code union all select f.ctid, f.region_code,f.parent_region_code,
	f.region_type,f.region_status from region_tbl f,r where f.region_code=r.parent_region_code)
	
	select r.region_code as "ThirdRegCode" 
	from r where r.region_type='3'
	and r.region_status='Y'
	
  ),
  (
    with  recursive r as (select f.ctid,f.region_code,f.parent_region_code,f.region_type,f.region_status 
	from region_tbl f where f.region_code=s.region_code union all select f.ctid, f.region_code,f.parent_region_code,
	f.region_type,f.region_status from region_tbl f,r where f.region_code=r.parent_region_code)
	
	select r.region_code as "FurthRegCode" 
	from r where r.region_type='2'
	and r.region_status='Y'
	
  )
  
  from s left join cust_info a on s.empno=a.cust_id;
二、问题分析

对比了两个坏境的执行计划,代价预估及扫描算子、连接算子看起来都是一样的。

执行计划如下:


                                                            QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=8.58..1944.99 rows=1 width=866)
   CTE s
     ->  Index Scan using emp_info_pkey on emp_info  (cost=0.28..8.30 rows=1 width=57)
           Index Cond: ((empno)::text = '200'::text)
           Filter: (((emp_type)::text > '5'::text) AND ((emp_status)::text = 'Y'::text))
   ->  CTE Scan on s  (cost=0.00..0.02 rows=1 width=256)
   ->  Index Scan using cust_info_pkey on cust_info a  (cost=0.28..8.29 rows=1 width=200)
         Index Cond: ((s.empno)::text = (cust_id)::text)
   SubPlan 3
     ->  CTE Scan on r r_1  (cost=479.57..482.09 rows=1 width=118)
           Filter: (((region_type)::text = '5'::text) AND ((region_status)::text = 'Y'::text))
           CTE r
             ->  Recursive Union  (cost=0.28..479.57 rows=101 width=19)
                   ->  Index Scan using region_tbl_pkey on region_tbl f  (cost=0.28..8.29 rows=1 width=19)
                         Index Cond: ((region_code)::text = (s.region_code)::text)
                   ->  Hash Join  (cost=0.33..46.93 rows=10 width=19)
                         Hash Cond: ((f_1.region_code)::text = (r.parent_region_code)::text)
                         ->  Seq Scan on region_tbl f_1  (cost=0.00..39.00 rows=2000 width=19)
                         ->  Hash  (cost=0.20..0.20 rows=10 width=118)
                               ->  WorkTable Scan on r  (cost=0.00..0.20 rows=10 width=118)
   SubPlan 5
     ->  CTE Scan on r r_3  (cost=479.57..482.09 rows=1 width=118)
           Filter: (((region_type)::text = '4'::text) AND ((region_status)::text = 'Y'::text))
           CTE r
             ->  Recursive Union  (cost=0.28..479.57 rows=101 width=19)
                   ->  Index Scan using region_tbl_pkey on region_tbl f_2  (cost=0.28..8.29 rows=1 width=19)
                         Index Cond: ((region_code)::text = (s.region_code)::text)
                   ->  Hash Join  (cost=0.33..46.93 rows=10 width=19)
                         Hash Cond: ((f_3.region_code)::text = (r_2.parent_region_code)::text)
                         ->  Seq Scan on region_tbl f_3  (cost=0.00..39.00 rows=2000 width=19)
                         ->  Hash  (cost=0.20..0.20 rows=10 width=118)
                               ->  WorkTable Scan on r r_2  (cost=0.00..0.20 rows=10 width=118)
   SubPlan 7
     ->  CTE Scan on r r_5  (cost=479.57..482.09 rows=1 width=118)
           Filter: (((region_type)::text = '3'::text) AND ((region_status)::text = 'Y'::text))
           CTE r
             ->  Recursive Union  (cost=0.28..479.57 rows=101 width=19)
                   ->  Index Scan using region_tbl_pkey on region_tbl f_4  (cost=0.28..8.29 rows=1 width=19)
                         Index Cond: ((region_code)::text = (s.region_code)::text)
                   ->  Hash Join  (cost=0.33..46.93 rows=10 width=19)
                         Hash Cond: ((f_5.region_code)::text = (r_4.parent_region_code)::text)
                         ->  Seq Scan on region_tbl f_5  (cost=0.00..39.00 rows=2000 width=19)
                         ->  Hash  (cost=0.20..0.20 rows=10 width=118)
                               ->  WorkTable Scan on r r_4  (cost=0.00..0.20 rows=10 width=118)
   SubPlan 9
     ->  CTE Scan on r r_7  (cost=479.57..482.09 rows=1 width=118)
           Filter: (((region_type)::text = '2'::text) AND ((region_status)::text = 'Y'::text))
           CTE r
             ->  Recursive Union  (cost=0.28..479.57 rows=101 width=19)
                   ->  Index Scan using region_tbl_pkey on region_tbl f_6  (cost=0.28..8.29 rows=1 width=19)
                         Index Cond: ((region_code)::text = (s.region_code)::text)
                   ->  Hash Join  (cost=0.33..46.93 rows=10 width=19)
                         Hash Cond: ((f_7.region_code)::text = (r_6.parent_region_code)::text)
                         ->  Seq Scan on region_tbl f_7  (cost=0.00..39.00 rows=2000 width=19)
                         ->  Hash  (cost=0.20..0.20 rows=10 width=118)
                               ->  WorkTable Scan on r r_6  (cost=0.00..0.20 rows=10 width=118)
(56 rows)


postgres=#

从执行计划来看,代价预估中没有发现非常耗时的步骤。对正常的环境中explain analyze查看实际消耗,实际执行300ms,最终返回了一条数据,和代价预估基本一致。逐步排查,最终将重心放在了递归查询这部分。

递归部分sql:

with recursive r as (select f.ctid,f.region_code,f.parent_region_code,f.region_type,f.region_status
from region_tbl f where f.region_code=s.region_code union all select f.ctid, f.region_code,f.parent_region_code,
f.region_type,f.region_status from region_tbl f,r where f.region_code=r.parent_region_code)

分析sql逻辑,递归条件为f.region_code=r.parent_region_code,并且递归开始的f.region_code字段值为s.region_code=‘1200’,这里的1200是通过对s表进行查询得到的,如下:

postgres=# select * from emp_info where empno='200' and emp_type>'5' and emp_status='Y';
 region_code | emp_type | emp_tel_info |             emp_name             | emp_status | empno
-------------+----------+--------------+----------------------------------+------------+-------
 1200        | 6        | 85192900896  | d7bcf68fc9d88d8b3f5ed6fa2713abcf | Y          | 200
(1 row)


改写递归部分的sql,查看实际执行,并打印了元组的ctid,如下是limit 10的结果:

postgres=# with  recursive r as (select f.ctid,f.region_code,f.parent_region_code,f.region_type,f.region_status from region_tbl f where f.region_code='1200' union all select f.ctid, f.region_code,f.parent_region_code,f.region_type,f.region_status from region_tbl f,r where f.region_code=r.parent_region_code)select * from r limit 10;
  ctid   | region_code | parent_region_code | region_type | region_status
---------+-------------+--------------------+-------------+---------------
 (18,75) | 1200        | 1020               | 5           | Y
 (18,76) | 1020        | 1002               | 4           | Y
 (9,108) | 1002        | 120                | 3           | Y
 (18,79) | 120         | 12                 | 2           | N
 (18,81) | 12          | 1                  | 1           | N
 (0,110) | 1           | 3                  | 3           | N
 (0,108) | 3           | 4                  | 6           | N
 (0,109) | 4           | 3                  | 3           | N
 (0,108) | 3           | 4                  | 6           | N
 (0,109) | 4           | 3                  | 3           | N
(10 rows)

再看limit 15的结果:

postgres=# with  recursive r as (select f.ctid,f.region_code,f.parent_region_code,f.region_type,f.region_status from region_tbl f where f.region_code='1200' union all select f.ctid, f.region_code,f.parent_region_code,f.region_type,f.region_status from region_tbl f,r where f.region_code=r.parent_region_code)select * from r limit 15;
  ctid   | region_code | parent_region_code | region_type | region_status
---------+-------------+--------------------+-------------+---------------
 (18,75) | 1200        | 1020               | 5           | Y
 (18,76) | 1020        | 1002               | 4           | Y
 (9,108) | 1002        | 120                | 3           | Y
 (18,79) | 120         | 12                 | 2           | N
 (18,81) | 12          | 1                  | 1           | N
 (0,110) | 1           | 3                  | 3           | N
 (0,108) | 3           | 4                  | 6           | N
 (0,109) | 4           | 3                  | 3           | N
 (0,108) | 3           | 4                  | 6           | N
 (0,109) | 4           | 3                  | 3           | N
 (0,108) | 3           | 4                  | 6           | N
 (0,109) | 4           | 3                  | 3           | N
 (0,108) | 3           | 4                  | 6           | N
 (0,109) | 4           | 3                  | 3           | N
 (0,108) | 3           | 4                  | 6           | N
(15 rows)


之后还打印了limit 1000,limit 10000的结果。发现一个现象,ctid为(0,108) (0,109)这两条数据一直在交替迭代,所以sql执行慢是一直在交替扫描这两条数据,这条sql在这个环境中是永远都跑不出结果的。

这两条数据很有特点,目前的递归条件为f.region_code=r.parent_region_code,而这两条数据的值刚好形成了一个闭环,导致递归陷入了死循环。

postgres=# select ctid,region_code,parent_region_code from region_tbl where region_code in ('3','4');
  ctid   | region_code | parent_region_code
---------+-------------+--------------------
 (0,108) | 3           | 4
 (0,109) | 4           | 3
(2 rows)

另外一个正常的环境中这两条数据并没有形成闭环,如下:

postgres=# select ctid,region_code,parent_region_code from region_tbl where region_code in ('3','4');
  ctid   | region_code | parent_region_code
---------+-------------+--------------------
 (0,245) | 3           | 0
 (0,246) | 4           | 0
(2 rows)
三、解决方案

1.已知是这两条数据导致的问题,那么可以参照正常环境修改数据值,或者从查询条件中剔除这两条数据。实际执行340ms返回一条数据,如下:

 staffNo | empType |   empNum    |     Name     |            Postion             |        Addr        |   Mobile    | FirstRegCode | SecondRegCode | Thir
dRegCode | FurthRegCode
---------+---------+-------------+--------------+--------------------------------+--------------------+-------------+--------------+---------------+-----
---------+--------------
 200     | 6       | 85192900896 | 运维yuanyuan |  Database administrator | 陕西省西安市高新区 | 13512345678 | 1200         | 1020          | 1002
         |
(1 row)

Time: 339.986 ms

2.为什么要用到递归?递归条件是否可以修改?

sql中一些字段的查询是通过递归完成的,例如SecondRegCode字段值为1020是递归输出的第二条结果,即通过1200递归查询出1020。如果不使用递归,那么只能查询到1200(FirstRegCode字段值),这个字段的值是查不到的。也就是说查询某些字段是依赖递归的。

以当前的sql逻辑,递归条件是无法修改的。

建议:
1)如果sql中继续使用递归,那么对于region_code和parent_region_code字段关系一定要做明确的规则处理,比如建立检查约束,明确region_code大于parent_region_code,这样存入的数据就不会出现闭环。

2)sql作为结构化查询语言对比应用语言,对复杂逻辑的处理存在很多局限性,不如应用代码灵活。因此是否可以考虑将递归处理从sql中拉出来,放到应用代码中处理

  • 3
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
2023年网络建设与运维国赛的Linux部分将涉及大量与Linux操作系统相关的技术和实践。参赛选手需要具备扎实的Linux基础知识、熟悉常用的Linux命令和工具,以及对网络架构和运维流程有深入了解。以下是几个可能出现的考点和相关内容: 1. Linux 系统安装与配置:选手需要了解如何安装各种Linux发行版,并进行基本配置,如网络设置、用户管理和权限控制等。 2. Linux 网络服务:主要考察选手对于各种网络服务的搭建和配置能力,如Web服务器(Apache/Nginx)、数据库服务器(MySQL/PostgreSQL)和邮件服务器(Postfix/Dovecot)等。 3. Linux 系统监控与性能优化:选手需要熟悉Linux系统的监控和调优手段,如使用top、vmstat等工具进行性能监测,优化内存管理、磁盘IO等问题。 4. Linux 安全防护与应急响应:选手需要掌握Linux系统的安全防护策略,如配置防火墙、使用SELinux进行强化等;同时,还需了解常见的攻击方式和应急响应措施。 5. Shell 脚本编写:选手需要具备基本的Shell编程能力,能够使用Shell脚本实现日常运维任务的自动化。 在比赛中,选手需要灵活运用自己的知识和技能,通过实际操作和解决问题的能力展现自己的水平。此外,对于团队竞赛,选手之间的协作能力和分工合作也是考核的重点之一。 要在2023年的网络建设与运维国赛的Linux部分取得好成绩,选手们需要提前系统学习相关知识并进行实践,注重积累经验,不断完善自己的技能和能力。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值