Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

Greenplum segment级锁问题排查方法 - 阿里云HybridDB for PostgreSQL最佳实践

5 minute read

背景

Greenplum(GPDB)是一个分布式数据库，分布式数据库的锁管理比单机更加复杂。例如在加锁时，需要对所有节点加锁（包括MASTER和所有的SEGMENT节点），在释放锁时，则需要释放所有节点的锁。

如果在释放过程中，MASTER的锁释放了，而SEGMENT锁没有释放，会造成什么问题呢？

不用说，会有很诡异的问题出现。例如某个会话锁了某一张表，但是会话退出时，主节点的锁释放了，SEGNEMT节点的锁没有释放。

那么用户在发起新的会话后，如果加载与之冲突的锁，当然要等待了。但是在MASTER节点你观察不到到底它在等待谁，你只能观察到它在等待。是不是很诡异呢？

锁查看方法

当你遇到堵塞时，可以另外开启一个会话查看是谁堵塞了谁？

《PostgreSQL 锁等待监控 珍藏级SQL - 谁堵塞了谁》

with      
t_wait as      
(      
  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,     
  a.objid,a.objsubid,a.pid,a.transactionid,a.mppsessionid,a.mppiswriter,a.gp_segment_id,       
  b.procpid,b.sess_id,b.waiting_reason,b.current_query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name     
    from pg_locks a,pg_stat_activity b where a.mppsessionid=b.sess_id and not a.granted     
),     
t_run as     
(     
  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,     
  a.objid,a.objsubid,a.pid,a.transactionid,a.mppsessionid,a.mppiswriter,a.gp_segment_id,       
  b.procpid,b.sess_id,b.waiting_reason,b.current_query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name     
    from pg_locks a,pg_stat_activity b where a.mppsessionid=b.sess_id and a.granted     
),     
t_overlap as     
(     
  select r.* from t_wait w join t_run r on     
  (     
    r.locktype is not distinct from w.locktype and     
    r.database is not distinct from w.database and     
    r.relation is not distinct from w.relation and     
    r.page is not distinct from w.page and     
    r.tuple is not distinct from w.tuple and     
    r.transactionid is not distinct from w.transactionid and     
    r.classid is not distinct from w.classid and     
    r.objid is not distinct from w.objid and     
    r.objsubid is not distinct from w.objsubid and     
    r.mppsessionid <> w.mppsessionid     
  )      
),      
t_unionall as      
(      
  select r.* from t_overlap r      
  union all      
  select w.* from t_wait w      
)      
select locktype,datname,relation::regclass,page,tuple,textin(xidout(transactionid)),classid::regclass,objid,objsubid,     
string_agg(     
'Gp_Segment_Id: '||case when gp_segment_id is null then 'NULL' else gp_segment_id::text end||chr(10)||   
'MppIsWriter: '||case when mppiswriter is null then 'NULL' when mppiswriter is true then 'TRUE' else 'FALSE' end||chr(10)||   
'MppSessionId: '||case when mppsessionid is null then 'NULL' else mppsessionid::text end||chr(10)||   
'ProcPid: '||case when procpid is null then 'NULL' else procpid::text end||chr(10)||   
'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||     
'Lock_Granted: '||case when granted is null then 'NULL' when granted is true then 'TRUE' else 'FALSE' end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , Waiting_Reason: '||case when waiting_reason is null then 'NULL' else waiting_reason::text end||chr(10)||     
'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)||      
'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)||      
'SQL (Current SQL in Transaction): '||chr(10)||    
case when current_query is null then 'NULL' else current_query::text end,      
chr(10)||'--------'||chr(10)      
order by      
  (  case mode      
    when 'INVALID' then 0     
    when 'AccessShareLock' then 1     
    when 'RowShareLock' then 2     
    when 'RowExclusiveLock' then 3     
    when 'ShareUpdateExclusiveLock' then 4     
    when 'ShareLock' then 5     
    when 'ShareRowExclusiveLock' then 6     
    when 'ExclusiveLock' then 7     
    when 'AccessExclusiveLock' then 8     
    else 0     
  end  ) desc,     
  (case when granted then 0 else 1 end)    
) as lock_conflict    
from t_unionall     
group by     
locktype,datname,relation::regclass,page,tuple,textin(xidout(transactionid)),classid::regclass,objid,objsubid ;

但是本案例通过这个方法，你会发现，只有未赋予的等待，没有已赋予的灵异事件。原因是这个查询没有反馈SEGMENT上的锁等待。查询的是GPDB主节点的pg_locks。

正常情况下通过这种方法很容易排查问题，灵异事件需要特殊对待。

灵异锁等待事件排查手段一 - 通过(gp_dist_random)在主节点发起请求，在所有segment节点单独执行

Greenplum提供了一个函数接口gp_dist_random，当调用这个函数时，会下发到所有segment执行。

gp_dist_random函数的参数是对象名，换句话说说，会在所有segment查询这个对象。在select子句中可以输入一些函数调用，也会下发到SEGMENT节点执行。

我们在GPDB的源码中，可以看到大量gp_dist_random的使用。

进入排查阶段。

假设digoal.test这张表的truncate被堵塞了，通过前面的锁SQL，没有找到堵塞对象。所以我们需要通过gp_dist_random接口，去SEGMENT里面找找锁堵塞的原因。

1、到所有segment执行，找到堵塞digoal.test的QUERY。

SQL如下，发现有大量的copy to stdou的查询，看样子是用户断开了master节点的COPY操作，但是SEGMENT节点的COPY还在继续。并且这个事务是2天前发起的，期间还不知道锁了多少其他对象呢。它就是堵塞digoal.test的罪魁祸首。

digoal=# select gp_execution_dbid(),   -- 返回segment的dbid，对应gp_segment_configuration.dbid里可以得到SEGMENT。  
        inet_server_addr(),  -- 这个并不是segment IP，这个函数没有下推  
        inet_server_port(),  -- 这个并不是segment PORT，这个函数没有下推  
        *    
      from gp_dist_random('pg_stat_activity')   -- 查询pg_stat_activity视图  
      where procpid in   
        ( select pid from gp_dist_random('pg_locks')   -- 查询pg_locks视图，并找到锁digoal.test的PID  
	     where relation='digoal.test'::regclass  
	);   
  
结果如下：  
  
-[ RECORD 1 ]----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
datid            | 17159  
datname          | digoal  
procpid          | 39312  
sess_id          | 80714  
usesysid         | 10  
usename          | digoal_user  
current_query    | COPY digoal.test_1_prt_p20170819 (xxx,xxx,xxx....) TO stdout IGNORE EXTERNAL PARTITIONS;  
waiting          | f  
query_start      | 2017-08-22 12:32:14.674691+08  
backend_start    | 2017-08-20 22:06:03.1238+08  
client_addr      |   
client_port      | -1  
application_name |   
xact_start       | 2017-08-20 22:06:03.129544+08  
waiting_reason   |   
.........  
-[ RECORD 8 ]----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
datid            | 17159  
datname          | digoal  
procpid          | 52074  
sess_id          | 80714  
usesysid         | 10  
usename          | digoal_user  
current_query    | COPY dw.t_zhuanpan_1_prt_p20170214 (xxx,xxx,xxx....) TO stdout IGNORE EXTERNAL PARTITIONS;  
waiting          | f  
query_start      | 2017-08-22 12:30:03.907998+08  
backend_start    | 2017-08-20 22:06:03.134764+08  
client_addr      |   
client_port      | -1  
application_name |   
xact_start       | 2017-08-20 22:06:03.157087+08  
waiting_reason   |

2、查看SEGMENT配置，通过dbid字段和gp_execution_dbid可以匹配到对应的SEGMENT。

digoal=# select * from gp_segment_configuration where role='p';  
 dbid | content | role | preferred_role | mode | status | port |       hostname       |       address        | replication_port | san_mounts   
------+---------+------+----------------+------+--------+------+----------------------+----------------------+------------------+------------  
    1 |      -1 | p    | p              | s    | u      | xxxx | xxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxx |                  |   
    2 |       0 | p    | p              | s    | u      | xxxx | xxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxx |             xxxx |   
    3 |       1 | p    | p              | s    | u      | xxxx | xxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxx |             xxxx |   
    4 |       2 | p    | p              | s    | u      | xxxx | xxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxx |             xxxx |   
    5 |       3 | p    | p              | s    | u      | xxxx | xxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxx |             xxxx |   
    6 |       4 | p    | p              | s    | u      | xxxx | xxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxx |             xxxx |   
    7 |       5 | p    | p              | s    | u      | xxxx | xxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxx |             xxxx |   
    8 |       6 | p    | p              | s    | u      | xxxx | xxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxx |             xxxx |   
    9 |       7 | p    | p              | s    | u      | xxxx | xxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxx |             xxxx |   
(9 rows)

3、观察一下这些PID都锁了哪些对象。

select gp_execution_dbid(), pid, relation::regclass, locktype, mode, granted  
  from gp_dist_random('pg_locks')   
  where pid in   
    (select pid from gp_dist_random('pg_locks') where relation='digoal.test'::regclass);

4、杀死这些SEGMENT上的PROCPID。

注意，数据会重分布到所有节点后再执行pg_terminate_backend，所以pid很可能被重分布到另一个SEGMENT，然后调用pg_terminate_backend，因为PID不是当前SEGMENT的PID，所以根本杀不掉。

所以手段一仅仅适合排查问题，不能解决问题。

select gp_execution_dbid() dbid,   
       pg_terminate_backend(procpid)  -- 杀杀杀，但是数据会重分布到所有节点执行，因此不是当前节点的procpid也会被pg_terminate_backend  
from gp_dist_random('pg_stat_activity')   
where procpid in   
  (select pid from gp_dist_random('pg_locks') where relation='digoal.test'::regclass and granted);  
  
WARNING:  PID 77961 is not a PostgreSQL server process  (seg21 slice3 xxxxxxxxx:25453 pid=128069)  
 dbid | pg_terminate_backend   
------+----------------------  
   23 | f  
(1 row)

灵异锁等待事件排查手段二 - 使用(gp_session_role=utility)直接连接SEGMENT

segment节点是不能直接连接的，需要设置一个参数，就可以连接了。

手段一帮助我们找到了持锁的segment，手段二则登陆对应的主机，直连segment去terminate对应的process。

PGOPTIONS="-c gp_session_role=utility" psql -d dbname -h hostname -p port -U user

直连到SEGMENT后，可以在这里看到持锁的SQL，这个SQL是COY TO STDOU，显然是MASTER中断这个SQL后，而segment没有中断造成的。属于GPDB本身的BUG，需要修复。

digoal=# \x  
Expanded display is on.  
digoal=# select array_agg(' usename: '||usename||' datname: '||datname||' current_query: '||coalesce(current_query,'')||' xact_start: '||coalesce(xact_start,'1970-01-01')||' backend_start: '||coalesce(backend_start,'1970-01-01')) from pg_stat_activity where procpid in (select pid from pg_locks where relation='digoal.test'::regclass);  
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
array_agg | {" usename: digoal_user datname: digoal current_query: COPY digoal.test_1_prt_p20170213 (xxxx,xxxx......) TO stdout IGNORE EXTERNAL PARTITIONS; xact_start: 2017-08-20 22:06:03.157087+08 backend_start: 2017-08-20 22:06:03.134764+08"}  
  
  
postgres=# select usename,datname,xact_start,current_query from pg_stat_activity ;  
  usename  | datname  |          xact_start           |                                                                                             current_query                                                                              
                   
-----------+----------+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
-----------------  
 digoal_user | digoal      |                               | <IDLE>  
 digoal_user | digoal      | 2017-08-20 22:06:03.157087+08 | COPY digoal.test_1_prt_p20170203 (xxx,xxx,......) TO stdout IGNORE EXTE  
RNAL PARTITIONS;  
 digoal_user | digoal      |                               | <IDLE>  
 aurora    | postgres | 2017-08-22 11:59:43.652306+08 | select usename,datname,xact_start,current_query from pg_stat_activity ;  
 dw        | digoal      | 2017-08-22 11:59:37.1828+08   | INSERT INTO digoal.test\r                                                                                                                                                             
                   
                                                      : SELECT  * from xxx.xxx017082222115801;  
 dw        | digoal      |                               | <IDLE>  
(6 rows)

杀掉所有segment的持锁会话后，恢复业务。

postgres=# select pg_terminate_backend(77961);  
 pg_terminate_backend   
----------------------  
 t  
(1 row)

参考

《PostgreSQL 锁等待监控 珍藏级SQL - 谁堵塞了谁》

PGOPTIONS="-c gp_session_role=utility" psql -d dbname -h hostname -p port -U user

https://www.postgresql.org/docs/8.2/static/runtime-config-developer.html

《Greenplum通过gp_dist_random(‘gp_id’) 在所有节点调用某个函数》

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...