如何在AgentGraph上查找锁等待查询？

Question

我正在忍受AgensGraph处理速度慢的问题。CPU使用率极低。我猜是那种等待锁定的情况。

如何查找锁等待查询？

Answer 1

您可以尝试使用AgensGraph的锁等待日志。

首先，更改"postgresql.conf“上的参数

log_lock_waits = on
deadlock_timeout = 1s

第二步，重启AgensGraph。

$ ag_ctl stop
waiting for server to shut down.... done
server stopped
$ ag_ctl start
server starting

最后，运行查询并检查日志文件。

Session1 :数据块传输

agens=# begin;
BEGIN
agens=# create (:n{id:1});
GRAPH WRITE (INSERT VERTEX 1, INSERT EDGE 0)
agens=# rollback;
ROLLBACK
agens=# 

Session2 :锁-等待事务

agens=# create (:n{id:1});
GRAPH WRITE (INSERT VERTEX 1, INSERT EDGE 0)

检查日志文件中的日志。

LOG:  process 3908 still waiting for ShareLock on transaction 1586 after 1001.058 ms
DETAIL:  Process holding the lock: 3906. Wait queue: 3908.
CONTEXT:  while inserting index tuple (0,7) in relation "n_id_idx"
STATEMENT:  create (:n{id:1});
LOG:  process 3908 acquired ShareLock on transaction 1586 after 4639.630 ms
CONTEXT:  while inserting index tuple (0,7) in relation "n_id_idx"
STATEMENT:  create (:n{id:1});