为什么要慎用pg_show_plans?
引言
pg_show_plans这个插件，最近好像有点火，在开源分会上看到好几篇关于pg_show_plans的投稿文章，这个插件出自于Suzuki Hironobu大师，也是The Internals of PostgreSQL一书的作者。诚然，这个插件可以查看运行时的SQL的执行计划，假如生产中有某个SQL跑半年跑不出来，那么我们就可以借助这个插件，结合pg_stat_activity观察运行时的执行计划，比如
postgres=# SELECT
pg_get_userbyid(userid) AS "user",
now() - query_start AS duration,
query,
plan
FROM
pg_show_plans p
JOIN pg_stat_activity a USING (pid)
WHERE
p.pid != pg_backend_pid()
AND datname = current_database()
AND now() - query_start > '0.0001s'::interval
ORDER BY
duration DESC
LIMIT 3;
-[ RECORD 1 ]---------------------------------------------------------------------
user | postgres
duration | 00:00:00.001831
query | UPDATE pgbench_branches SET bbalance = bbalance + 711 WHERE bid = 10;
plan | Update on pgbench_branches (cost=0.00..2.13 rows=1 width=370) +
| -> Seq Scan on pgbench_branches (cost=0.00..2.13 rows=1 width=370) +
| Filter: (bid = 10)
-[ RECORD 2 ]---------------------------------------------------------------------
user | postgres
duration | 00:00:00.001291
query | UPDATE pgbench_branches SET bbalance = bbalance + -2214 WHERE bid = 10;
plan | Update on pgbench_branches (cost=0.00..2.13 rows=1 width=370) +
| -> Seq Scan on pgbench_branches (cost=0.00..2.13 rows=1 width=370) +
| Filter: (bid = 10)
确实是一个很方便的扩展。像诸如pg_store_plans、auto_explain种种，都是跑完之后记录在数据库或日志中，但遗憾的是，这些是事后溯源的一种方式，只有在查询完成后，才可以来分析慢的根本原因。
实现机制
和大多数基于共享内存的插件实现方式类似，pg_show_plans也是基于hook实现的，俗称"钩子"，有了hook机制，可以让用户有机会切入PostgreSQL的内部运行机制中，进行中断、增加或修改原来的程序逻辑，从而实现一些用户自定义的功能。
很多人家里会加自来水的前置过滤器，在要加过滤器的地方切段水管，把过滤器装上后水通过过滤器再往下流。hook在PostgreSQL中也可以看作是一种内置的过滤器，但它是个空壳，过滤器里什么都没有，你可以实现这个hook，也就是往过滤器里加东西。所以，要使用该插件需要在配置文件中指定一下：shared_preload_libraries = 'pg_show_plans'
问题是什么
那么我为什么说要慎用pg_show_plans？直接看一下现象：
[postgres@xiongcc ~]$ psql
psql (13.2)
Type "help" for help.
postgres=# show shared_preload_libraries ; ---未加载pg_show_plans
shared_preload_libraries
--------------------------
(1 row)
postgres=# select txid_current();
txid_current
--------------
11479104
(1 row)
postgres=# select 1;
?column?
----------
1
(1 row)
postgres=# select * from test limit 1;
id
----
1
(1 row)
postgres=# select relname from pg_class limit 1;
relname
-----------------
pg_toast_333812
(1 row)
postgres=# select txid_current();
txid_current
--------------
11479105
(1 row)
首先是未加载pg_show_plans的情况，可以看到两次txid_current的差值是1，对于txid_current这个函数，每调用一次便会分配一个永久事务ID，会使事务号加1。
/*
* txid_current() returns int8
*
*Return the current toplevel transaction ID as TXID
*If the current transaction does not have one, one is assigned.
*
*This value has the epoch as the high 32 bits and the 32-bit xid
*as the low 32 bits.
*/
Datum
txid_current(PG_FUNCTION_ARGS)
{
txidval;
TxidEpochstate;
/*
* Must prevent during recovery because if an xid is not assigned we try
* to assign one, which would fail. Programs already rely on this function
* to always return a valid current xid, so we should not change this to
* return NULL or similar invalid xid.
*/
PreventCommandDuringRecovery("txid_current()");
load_xid_epoch(&state);
val = convert_xid(GetTopTransactionId(), &state);
PG_RETURN_INT64(val);
}
至于中间的那一坨查询语句，是没有获取永久事务ID的，使用的是虚拟事务ID，这个其实之前已经讨论过，在PostgreSQL中，所有的隐式或显式的只读事务都被视作虚拟事务，首先是为了性能，永久事务ID的分配是需要获取XidGenLock这个LWLock的。其次是为了防止事务ID被快速用完，毕竟年龄的回收是一件很耗资源的事情，分配虚拟事务ID的代价比分配永久事务ID要小得多。
那么再看一下使用了pg_show_plans的情况
[postgres@xiongcc ~]$ psql
psql (13.2)
Type "help" for help.
postgres=# show shared_preload_libraries ;
shared_preload_libraries
--------------------------
pg_show_plans
(1 row)
postgres=# select txid_current();
txid_current
--------------
11479106
(1 row)
postgres=# select 1;
?column?
----------
1
(1 row)
postgres=# select * from test limit 1;
id
----
1
(1 row)
postgres=# select relname from pg_class limit 1;
relname
-----------------
pg_toast_333812
(1 row)
postgres=# select txid_current();
txid_current
--------------
11479110
(1 row)
纳尼？我的事务号怎么就涨了4？莫非是普通的select耗费了一个永久事务ID？
postgres=# select txid_current();
txid_current
--------------
11479110
(1 row)
postgres=# select txid_current();
txid_current
--------------
11479111
(1 row)
postgres=# select txid_current();
txid_current
--------------
11479112
(1 row)
postgres=# select 1;
?column?
----------
1
(1 row)
postgres=# select txid_current();
txid_current
--------------
11479114
(1 row)
DBA简直惊呆了，普通的select也会耗费一个永久事务ID，就连select 1也会，本身20亿事务ID就不够用了，现在普通的查询也会消耗年龄，无法想象。
一探究竟
本着不死心的态度，我想一探究竟。
对于事物ID的分配，我们知道，是AssignTransactionId -> GetNewTransactionId这两个函数来做的，用gdb追一下。
[postgres@xiongcc ~]$ psql
psql (13.2)
Type "help" for help.
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
23942
(1 row)
[postgres@xiongcc ~]$ gdb postgres
...
(gdb) attach 23942
...
(gdb) b GetNewTransactionId
Breakpoint 1 at 0x56735b: file varsup.c, line 58.
(gdb) info b
Num Type Disp Enb Address What
1 breakpoint keep y 0x000000000056735b in GetNewTransactionId at varsup.c:58
第一个会话执行
postgres=# select txid_current();
此处卡住
gdb查看，可以看到ShmemVariableCache->nextFullXid是11479131，也就是下一个事务ID是11479131
(gdb) c
Continuing.
Breakpoint 1, GetNewTransactionId (isSubXact=false) at varsup.c:58
58 if (IsInParallelMode())
(gdb) n
65 if (IsBootstrapProcessingMode())
(gdb) n
73 if (RecoveryInProgress())
(gdb) n
76 LWLockAcquire(XidGenLock, LW_EXCLUSIVE);
(gdb) p *ShmemVariableCache
$1 = {nextOid = 333905, oidCount = 8182, nextFullXid = {value = 11479131}, oldestXid = 478, xidVacLimit = 200000478, xidWarnLimit = 2136484125,
xidStopLimit = 2146484125, xidWrapLimit = 2147484125, oldestXidDB = 1, oldestCommitTsXid = 0, newestCommitTsXid = 0,
latestCompletedXid = 11479130, oldestClogXid = 478}
(gdb) p ShmemVariableCache->nextFullXid
$2 = {value = 11479131}
至于后面看到的几个xidVacLimit、xidWarnLimit和xidStopLimit是几个阈值，
* If we're past xidVacLimit, start trying to force autovacuum cycles.
* If we're past xidWarnLimit, start issuing warnings.
* If we're past xidStopLimit, refuse to execute transactions, unless
* we are running in single-user mode (which gives an escape hatch
* to the DBA who somehow got past the earlier defenses).
如果超过了xidVacLimit，开始尝试强制autovacuum
如果超过了xidWarnLimit，开始发出警告
如果超过了xidStopLimit，拒绝执行事务，直至以单用户模式运行.
直接continue
(gdb) p ShmemVariableCache->nextFullXid
$2 = {value = 11479131}
(gdb) c
Continuing.
确实事务号返回的是11479131
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
23942
(1 row)
postgres=# select txid_current();
txid_current
--------------
11479131
(1 row)
继续跑一个只读查询
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
23942
(1 row)
postgres=# select txid_current();
txid_current
--------------
11479131
(1 row)
postgres=# select 1;
此处卡住
不出所料，select 1也卡在了GetNewTransactionId这里，nextFullXid是11479132，事务号加1
Breakpoint 1, GetNewTransactionId (isSubXact=false) at varsup.c:58
58 if (IsInParallelMode())
(gdb) n
65 if (IsBootstrapProcessingMode())
(gdb) n
73 if (RecoveryInProgress())
(gdb) n
76 LWLockAcquire(XidGenLock, LW_EXCLUSIVE);
(gdb) p *ShmemVariableCache
$3 = {nextOid = 333905, oidCount = 8182, nextFullXid = {value = 11479132}, oldestXid = 478, xidVacLimit = 200000478, xidWarnLimit = 2136484125,
xidStopLimit = 2146484125, xidWrapLimit = 2147484125, oldestXidDB = 1, oldestCommitTsXid = 0, newestCommitTsXid = 0,
latestCompletedXid = 11479131, oldestClogXid = 478}
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
23942
(1 row)
postgres=# select txid_current();
txid_current
--------------
11479131
(1 row)
postgres=# select 1; ---消耗了事务号，11479132
?column?
----------
1
(1 row)
postgres=# select txid_current();
txid_current
--------------
11479133
(1 row)
那么对于没有pg_show_plans的看一下情况，经验证，确实如之前所说，对于普通的只读事务，是不会分配永久事务ID的，也不会在AssignTransactionId处卡住
postgres=# show shared_preload_libraries ;
shared_preload_libraries
--------------------------
(1 row)
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
23811
(1 row)
postgres=# select txid_current(); --此处卡住
txid_current
--------------
11479119
(1 row)
postgres=# select 1; --未卡住，没有分配永久事务ID
?column?
----------
1
(1 row)
postgres=# select relname from pg_class limit 1; --未卡住，没有分配永久事务ID
relname
-----------------
pg_toast_333812
(1 row)
看一下pg_show_plans时的堆栈，查询SQL是select * from pg_stat_activity limit 1;
可以看到pgsp_ExecutorStart和entry_store这两个函数，顶层堆栈是GetTopTransactionId和AssignTransactionId
(gdb) bt
#0 GetNewTransactionId (isSubXact=false) at varsup.c:58
#1 0x00000000005681ba in AssignTransactionId (s=0xfd0e80 <TopTransactionStateData>) at xact.c:623
#2 0x0000000000567eae in GetTopTransactionId () at xact.c:398
#3 0x00007fbcef587ab5 in entry_store (
plan=0x15f51b0 "Limit (cost=0.00..0.32 rows=1 width=444)\n -> Nested Loop Left Join (cost=0.00..31.51 rows=100 width=444)\n Join Filter: (s.usesysid = u.oid)\n -> Nested Loop Left Join (cost=0.00..8."..., nested_level=0) at pg_show_plans.c:579
#4 0x00007fbcef58758c in pgsp_ExecutorStart (queryDesc=0x1619ea0, eflags=0) at pg_show_plans.c:393
#5 0x000000000070c124 in ExecutorStart (queryDesc=0x1619ea0, eflags=0) at execMain.c:132
#6 0x00000000009320c6 in PortalStart (portal=0x1542df0, params=0x0, eflags=0, snapshot=0x0) at pquery.c:505
#7 0x000000000092c7c8 in exec_simple_query (query_string=0x14dbe80 "select * from pg_stat_activity limit 1;") at postgres.c:1200
#8 0x00000000009308f3 in PostgresMain (argc=1, argv=0x1506e10, dbname=0x1506cd0 "postgres", username=0x1506ca8 "postgres") at postgres.c:4329
#9 0x00000000008804e5 in BackendRun (port=0x14feda0) at postmaster.c:4526
#10 0x000000000087fcd5 in BackendStartup (port=0x14feda0) at postmaster.c:4210
#11 0x000000000087c393 in ServerLoop () at postmaster.c:1739
#12 0x000000000087bc6a in PostmasterMain (argc=3, argv=0x14d6a20) at postmaster.c:1412
#13 0x00000000007831e6 in main (argc=3, argv=0x14d6a20) at main.c:210
大致看了一下这两个函数，其中entry_store就是插入到哈希表里面去的函数，v13可以直接查询pg_shmem_allocations这个视图
postgres=# select * from pg_shmem_allocations where name like '%plans%';
name | off | size | allocated_size
--------------------+-----------+------+----------------
pg_show_plans | 289878144 | 24 | 128
pg_show_plans hash | 289878272 | 2904 | 2944
(2 rows)
pg_show_plans视图定义如下：
postgres=# \d pg_show_plans
View "public.pg_show_plans"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+---------
pid | bigint | | |
level | bigint | | |
userid | oid | | |
dbid | oid | | |
plan | text | | |
pid：进程的pid
level: the level of the query which runs the query. Top level is 0. For example, if you execute a simple select query, the level of this query's plan is 0. If you execute a function that invokes a select query, level 0 is the plan of the function and level 1 is the plan of the select query invoked by the function.，0就是普通的查询，1是函数体里面涉及的SQL
userid: 用户id
dbid: 数据库id
plan: 执行计划
关键流程如下：
/* Store data into the entry. */
e = (pgspEntry *) entry;
SpinLockAcquire(&e->mutex);
e->userid = GetUserId();
e->dbid = MyDatabaseId;
e->encoding = GetDatabaseEncoding();
if (!RecoveryInProgress())
e->topxid = GetTopTransactionId();
else
/*
* In recovery mode, we use pid as the key instead of txid for garbage
* collection.
*/
e->topxid = (uint32) key.pid;
memcpy(entry->plan, plan, plan_len);
entry->plan_len = plan_len;
entry->plan[plan_len] = '\0';
SpinLockRelease(&e->mutex);
LWLockRelease(pgsp->lock);
}
其中 e->topxid = GetTopTransactionId();，也就是这一步在获取实际的事务ID，
/*
* GetTopTransactionId
*
* This will return the XID of the main transaction, assigning one if
* it's not yet set. Be careful to call this only inside a valid xact.
*/
TransactionId
GetTopTransactionId(void)
{
if (!FullTransactionIdIsValid(XactTopFullTransactionId))
AssignTransactionId(&TopTransactionStateData);
return XidFromFullTransactionId(XactTopFullTransactionId);
}
正如之前所说，对于事物ID的分配，是AssignTransactionId -> GetNewTransactionId来做的。
至此清晰了，获取topxid 插入到哈希表中，这一步会获取一个永久事务ID。意味着每跑一条SQL，都要获取一个永久事务ID。
小结
对于PostgreSQL本身就饱受诟病的年龄问题，假如再引入了pg_show_plans，会导致年龄消耗更加迅速，也会势必导致年龄回收这个动作更加频繁，对于应用影响也就越大。
虽然通过插件，可以不断完善PostgreSQL的功能，但是插件，相对是个黑盒，势必也会引入一些新的问题，所以在引入一款插件之前，足够的调研和实验是必须的。
原文作者：PostgreSQL学徒；如有侵权，请联系管理员删除。
