标签
PostgreSQL , Greenplum , 锁 , SEGMENT不一致 , gp_session_role=utility , gp_dist_random
背景
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 ;
AI 代码解读
但是本案例通过这个方法，你会发现，只有未赋予的等待，没有已赋予的灵异事件。原因是这个查询没有反馈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 |
AI 代码解读
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)
AI 代码解读
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);
AI 代码解读
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)
AI 代码解读
灵异锁等待事件排查手段二 - 使用(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
AI 代码解读
直连到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)
AI 代码解读
杀掉所有segment的持锁会话后，恢复业务。
postgres=# select pg_terminate_backend(77961);
pg_terminate_backend
----------------------
t
(1 row)
AI 代码解读
参考
《PostgreSQL 锁等待监控 珍藏级SQL - 谁堵塞了谁》
PGOPTIONS="-c gp_session_role=utility" psql -d dbname -h hostname -p port -U user
AI 代码解读
https://www.postgresql.org/docs/8.2/static/runtime-config-developer.html
