当前位置：首页 > news >正文

postgresql subtransaction以及他的效能

news 文章来源：https://blog.csdn.net/weixin_43230594/article/details/143189618 2025/4/21 13:39:29

文章目录

什么是subtransaction
使用子事务
PL/pgSQL 中的子事务
与其他数据库的兼容性
运行性能测试
Subtransaction的实现
子事务和可见性
解释测试结果
诊断子事务过多的问题
结论

什么是subtransaction

在 PostgreSQL 中，当处于自动提交模式时，必须使用 BEGIN 或 START TRANSACTION 显式启动一个跨多个语句的事务，并用 END 或 COMMIT 结束它。如果使用 ROLLBACK 中止事务（或者在不提交的情况下结束数据库会话），事务内所做的所有工作将会被撤销。
子事务允许你撤销在事务中所做部分工作的操作。可以使用标准 SQL 语句在事务内部启动子事务：

SAVEPOINT name;

“name” 是子事务的标识符（不加单引号！）。不能在 SQL 中提交子事务（它会随包含它的事务自动提交），但可以使用以下命令回滚它：

ROLLBACK TO SAVEPOINT name;

使用子事务

子事务在较长的事务中很有用。在 PostgreSQL 中，事务内部的任何错误都将中止事务：

PgSQL
test=> BEGIN;
BEGIN
test=*> SELECT 'Some work is done';?column?      
-------------------Some work is done
(1 row)test=*> SELECT 12 / (factorial(0) - 1);
ERROR:  division by zero
test=!> SELECT 'try to do more work';
ERROR:  current transaction is aborted, commands ignored until end of transaction block
test=!> COMMIT;
ROLLBACK

如果一个事务做了很多工作，但中途失败了，这很麻烦，因为之前做的所有工作都会丢失。子事务可以帮助你从这种情况中恢复，避免重新做所有工作。

PgSQL
test=> BEGIN;
BEGIN
test=*> SELECT 'Some work is done';?column?      
-------------------Some work is done
(1 row)test=*> SAVEPOINT a;
SAVEPOINT
test=*> SELECT 12 / (factorial(0) - 1);
ERROR:  division by zero
test=!> ROLLBACK TO SAVEPOINT a;
ROLLBACK
test=*> SELECT 'try to do more work';?column?       
---------------------try to do more work
(1 row)test=*> COMMIT;
COMMIT

ROLLBACK TO SAVEPOINT 在回滚旧的子事务时，会启动另一个名为 a 的子事务。

PL/pgSQL 中的子事务

即使你从未使用过 SAVEPOINT 语句，也可能已经遇到过子事务。在 PL/pgSQL 中，上面中的代码看起来像这样：

PgSQL
BEGINPERFORM 'Some work is done';BEGIN  -- a block inside a blockPERFORM 12 / (factorial(0) - 1);EXCEPTIONWHEN division_by_zero THENNULL;  -- ignore the errorEND;PERFORM 'try to do more work';
END;

每次进入带有 EXCEPTION 子句的代码块时，都会启动一个新的子事务。离开该代码块时，子事务会被提交，而当进入异常处理器时，子事务则会被回滚。

与其他数据库的兼容性

许多其他数据库在事务内处理错误的方式不同。它们不会中止整个事务，而是仅回滚导致错误的语句，并保持事务本身处于活跃状态。
当从这样的数据库迁移或移植到 PostgreSQL 时，你可能会倾向于将每个语句都包装在一个子事务中，以模拟上述行为。
PostgreSQL 的 JDBC 驱动程序甚至有一个名为 autosave 的连接参数，你可以将其设置为 always，从而在每个语句之前自动设置一个保存点，并在出现故障时回滚。
然而，正如接下来将展示的那样，这种诱人的技巧会导致严重的性能问题。

性能测试用例
为了演示过度使用子事务所带来的问题，这里创建了一个测试表：

CREATE UNLOGGED TABLE contend (id integer PRIMARY KEY,val integer NOT NULL
)
WITH (fillfactor='50');INSERT INTO contend (id, val)
SELECT i, 0
FROM generate_series(1, 10000) AS i;VACUUM (ANALYZE) contend;

该表是一个小表，unlogged，并且设置了较低的填充因子，以尽可能减少所需的 I/O。这样，我可以更好地观察子事务的影响。
我将使用 pgbench，这是随 PostgreSQL 提供的基准测试工具，来运行以下自定义 SQL 脚本：
测试1将运行的脚本，这个脚本将产生10个savepoint(测试1中6个并行会话将产生60个子事务）

BEGIN;
PREPARE sel(integer) ASSELECT count(*)FROM contendWHERE id BETWEEN $1 AND $1 + 100;
PREPARE upd(integer) ASUPDATE contend SET val = val + 1WHERE id IN ($1, $1 + 10, $1 + 20, $1 + 30);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);DEALLOCATE ALL;
COMMIT;

测试2中的脚本，这个脚本将产生15个savepoint(测试2中6个并行会话将产生90个子事务）

BEGIN;
PREPARE sel(integer) ASSELECT count(*)FROM contendWHERE id BETWEEN $1 AND $1 + 100;
PREPARE upd(integer) ASUPDATE contend SET val = val + 1WHERE id IN ($1, $1 + 10, $1 + 20, $1 + 30);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);SAVEPOINT a;
set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);DEALLOCATE ALL;
COMMIT;

该脚本在测试1中将设置60个savepoint，在测试2中将设置90个savepoint。它使用prepare statement来最小化查询解析的开销。
pgbench 会将 :client_id 替换为每个数据库会话的唯一编号。因此，只要客户端数不超过10，每个客户端的 UPDATE 操作就不会与其他客户端发生冲突，但它们会 SELECT 彼此的行。

运行性能测试

由于我的机器有8个核心，我将使用6个并发客户端运行测试，持续十分钟。
为了在使用“perf top”时看到有意义的信息，需要安装 PostgreSQL 的debugging symbols。在生产系统上推荐使用。
Test 1 (60 subtransactions)

pgbench -f subtrans.sql -n -c 6 -T 600transaction type: subtrans.sql
scaling factor: 1
query mode: simple
number of clients: 6
number of threads: 1
duration: 600 s
number of transactions actually processed: 100434
latency average = 35.846 ms
tps = 167.382164 (including connections establishing)
tps = 167.383187 (excluding connections establishing)

这是在测试运行时，perf top --no-children --call-graph=fp --dsos=/usr/pgsql-12/bin/postgres 显示的内容：

+    1.86%  [.] tbm_iterate
+    1.77%  [.] hash_search_with_hash_value1.75%  [.] AllocSetAlloc
+    1.36%  [.] pg_qsort
+    1.12%  [.] base_yyparse
+    1.10%  [.] TransactionIdIsCurrentTransactionId
+    0.96%  [.] heap_hot_search_buffer
+    0.96%  [.] LWLockAttemptLock
+    0.85%  [.] HeapTupleSatisfiesVisibility
+    0.82%  [.] heap_page_prune
+    0.81%  [.] ExecInterpExpr
+    0.80%  [.] SearchCatCache1
+    0.79%  [.] BitmapHeapNext
+    0.64%  [.] LWLockRelease
+    0.62%  [.] MemoryContextAllocZeroAligned
+    0.55%  [.] _bt_checkkeys                                 0.54%  [.] hash_any
+    0.52%  [.] _bt_compare0.51%  [.] ExecScan

测试 2（90 个子事务）

pgbench -f subtrans.sql -n -c 6 -T 600
transaction type: subtrans.sql
scaling factor: 1
query mode: simple
number of clients: 6
number of threads: 1
duration: 600 s
number of transactions actually processed: 41400
latency average = 86.965 ms
tps = 68.993634 (including connections establishing)
tps = 68.993993 (excluding connections establishing)

“perf top --no-children --call-graph=fp --dsos=/usr/pgsql-12/bin/postgres”的内容：

+   10.59%  [.] LWLockAttemptLock
+    7.12%  [.] LWLockRelease
+    2.70%  [.] LWLockAcquire
+    2.40%  [.] SimpleLruReadPage_ReadOnly
+    1.30%  [.] TransactionIdIsCurrentTransactionId
+    1.26%  [.] tbm_iterate
+    1.22%  [.] hash_search_with_hash_value
+    1.08%  [.] AllocSetAlloc
+    0.77%  [.] heap_hot_search_buffer
+    0.72%  [.] pg_qsort
+    0.72%  [.] base_yyparse
+    0.66%  [.] SubTransGetParent
+    0.62%  [.] HeapTupleSatisfiesVisibility
+    0.54%  [.] ExecInterpExpr
+    0.51%  [.] SearchCatCache1

即使考虑到测试2中的事务比测试1多执行了一次，这仍然是一个性能下降，与测试1相比，性能回退了60%。

Subtransaction的实现

每当一个事务或子事务修改数据时，它会被分配一个事务 ID。PostgreSQL 在clog中跟踪这些事务 ID，这些日志持久保存在数据目录的 pg_xact 子目录中。
事务和子事务之间有一些区别：

每个子事务都有一个包含它的事务或子事务（即“父事务”）。
提交一个子事务不需要进行 WAL 刷新。
每个数据库会话只能有一个事务，但可以有多个子事务。
哪个（子）事务是给定子事务的父事务的信息保存在数据目录的 pg_subtrans 子目录中。由于这个信息在包含事务结束后就变得无效，因此不需要在关机或崩溃期间保留这些数据。

子事务和可见性

在 PostgreSQL 中，行版本（“元组”）的可见性由 xmin 和 xmax 系统列决定，这些列包含创建和销毁事务的事务 ID。如果存储的事务 ID 是一个子事务的 ID，如果存储的事务 ID 是子事务的，PostgreSQL 还需要查询包含该子事务的父事务或(父)子事务的状态，以确定该事务 ID 是否有效。。

为了确定一个语句可以看到哪些元组，PostgreSQL 在语句（或事务）开始时会对数据库进行快照。这样的快照由以下几个部分组成：

最大事务 ID：在该事务之后的所有内容都是不可见的。
当快照被拍摄时处于活动状态的事务和子事务列表。
当前（子）事务中最早可见命令的命令号。
快照的初始化通过查看存储在共享内存中的进程数组来完成，该数组包含有关所有当前运行的后台进程的信息。这个数组当然包含后台进程的当前事务 ID，并且每个会话最多可以容纳 64 个未中止的子事务。如果存在超过 64 个这样的子事务，快照将被标记为“suboverflowed”。

解释测试结果

一个子溢出的快照不包含确定可见性所需的所有数据，因此 PostgreSQL 有时不得不依赖 pg_subtrans。这些页面被缓存到共享缓冲区中，但可以在性能输出中看到查找它们的开销，这体现在 SimpleLruReadPage_ReadOnly 的高排名上。其他事务必须更新 pg_subtrans 以注册子事务，可以在性能输出中看到它们如何与读取者争夺轻量级锁。

诊断子事务过多的问题

除了查看“perf top”，还有其他症状可以指向这个问题的方向：
当在单线程运行时，你的工作负载表现良好，但在多个并发数据库会话中运行时表现不佳。
经常在 pg_stat_activity 中看到等待事件“SubtransSLRU”（在早期版本中称为“SubtransControlLock”）。
从 PostgreSQL v13 开始，可以查看监控视图 pg_stat_slru，检查名为 ‘Subtrans’ 的行中的 blks_read 是否持续增长。这表明 PostgreSQL 需要读取磁盘页面，因为它需要访问不再缓存的子事务。
如果使用 “pg_export_snapshot()” 函数导出快照，结果文件在数据目录的 pg_snapshots 子目录中将包含 “sof:1” 行，以指示子事务数组溢出。
从 PostgreSQL v16 开始，可以调用函数 pg_stat_get_backend_subxact(integer) 返回后端进程的子事务数量，以及子事务缓存是否溢出。

结论

子事务是一个很好的工具，但你应该明智地使用它们。如果需要并发，不要在每个事务中启动超过 64 个子事务。
找出该问题的原因可能会很棘手。例如，可能不明显的是，为 SQL 语句的每个结果行调用的包含异常处理程序的函数（可能在触发器中）会启动一个新的子事务。