网站页面一般做多大,wordpress ftp配置,wordpress文章表,邢台关键词优化公司文章目录 什么是subtransaction使用子事务PL/pgSQL 中的子事务与其他数据库的兼容性运行性能测试Subtransaction的实现子事务和可见性解释测试结果诊断子事务过多的问题结论 什么是subtransaction
在 PostgreSQL 中#xff0c;当处于自动提交模式时#xff0c;必须使用 BEGI… 文章目录 什么是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;
COMMITROLLBACK 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 (fillfactor50);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-graphfp --dsos/usr/pgsql-12/bin/postgres 显示的内容 1.86% [.] tbm_iterate1.77% [.] hash_search_with_hash_value1.75% [.] AllocSetAlloc1.36% [.] pg_qsort1.12% [.] base_yyparse1.10% [.] TransactionIdIsCurrentTransactionId0.96% [.] heap_hot_search_buffer0.96% [.] LWLockAttemptLock0.85% [.] HeapTupleSatisfiesVisibility0.82% [.] heap_page_prune0.81% [.] ExecInterpExpr0.80% [.] SearchCatCache10.79% [.] BitmapHeapNext0.64% [.] LWLockRelease0.62% [.] MemoryContextAllocZeroAligned0.55% [.] _bt_checkkeys 0.54% [.] hash_any0.52% [.] _bt_compare0.51% [.] ExecScan测试 290 个子事务
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-graphfp --dsos/usr/pgsql-12/bin/postgres”的内容 10.59% [.] LWLockAttemptLock7.12% [.] LWLockRelease2.70% [.] LWLockAcquire2.40% [.] SimpleLruReadPage_ReadOnly1.30% [.] TransactionIdIsCurrentTransactionId1.26% [.] tbm_iterate1.22% [.] hash_search_with_hash_value1.08% [.] AllocSetAlloc0.77% [.] heap_hot_search_buffer0.72% [.] pg_qsort0.72% [.] base_yyparse0.66% [.] SubTransGetParent0.62% [.] HeapTupleSatisfiesVisibility0.54% [.] ExecInterpExpr0.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 语句的每个结果行调用的包含异常处理程序的函数可能在触发器中会启动一个新的子事务。
