Improve locktree query
Improve locktree query
Testing the locktree query I found that result output contains duplicated rows. Most columns are omitted for simplicity, 'top_blocker_pid' added for convenience. Could be reproduced with well-known pgbench workload.
top_blocker_pid | pid | blocked_by | path | blkd | query
-----------------+---------+------------+---------------------------+------+--------------------------------------------------------------------------------------
1996054 | 1996054 | {} | {1996054} | 3 | [1996054] END;
1996054 | 1996719 | {1996054} | {1996054,1996719} | 1 | [1996719] . UPDATE pgbench_branches SET bbalance = bbalance + 3472 WHERE bid = 19;
1996054 | 1996721 | {1996054} | {1996054,1996721} | 1 | [1996721] . UPDATE pgbench_tellers SET tbalance = tbalance + 548 WHERE tid = 78;
1996054 | 1996650 | {1996725} | {1996054,1996721,1996650} | 0 | [1996650] .. UPDATE pgbench_tellers SET tbalance = tbalance + -1041 WHERE tid = 23;
1996054 | 1996650 | {1996725} | {1996054,1996719,1996650} | 0 | [1996650] .. UPDATE pgbench_tellers SET tbalance = tbalance + -1041 WHERE tid = 23;
1996653 | 1996653 | {} | {1996653} | 2 | [1996653] END;
1996653 | 1996722 | {1996653} | {1996653,1996722} | 1 | [1996722] . UPDATE pgbench_branches SET bbalance = bbalance + -1603 WHERE bid = 4;
1996653 | 1996650 | {1996725} | {1996653,1996722,1996650} | 0 | [1996650] .. UPDATE pgbench_tellers SET tbalance = tbalance + -1041 WHERE tid = 23;
1996718 | 1996718 | {} | {1996718} | 2 | [1996718] END;
1996718 | 1996725 | {1996718} | {1996718,1996725} | 1 | [1996725] . UPDATE pgbench_tellers SET tbalance = tbalance + 2558 WHERE tid = 23;
1996718 | 1996650 | {1996725} | {1996718,1996725,1996650} | 0 | [1996650] .. UPDATE pgbench_tellers SET tbalance = tbalance + -1041 WHERE tid = 23;
Process with pid 1996650 is duplicated and "belongs" to processes which are not blocked it. Such duplicates bloat the outout and could mislead end user. The main goal of MR is removing duplicates. Note, this query is not widely battle-tested yet.
Also new version has few improvements:
- Replace state_change to pg_locks.waitstart - this is more precise way to show waiting time.
- Extend 'state' field with fake 'waiting' state to distinct active and waiting processes.
- Replace backend_xid, backend_xmin with ages. xid/xmin values show big numbers and it's quite difficult to understand what it mean. xid_age shows xact's age in xids, xmin_ttf shows how many xids remain before wraparound. BUT, IMHO these two fields are not important here and could be omitted, because the main aim of query is detecting waitings and not causes of bloat.
- Increase padding when prints PID in 'query' column. On my Ubuntu 20.04 PIDs consist of 7 digits and truncated.
Edited by Alexey Lesovsky