作者
digoal
日期
2021-11-03
标签
PostgreSQL , savepoint , subtransactions
1、每个savepoint都会消耗一个xid
2、每个savepoint消耗8K的会话本地内存(CurTransactionContext)
3、当有并发事务时, 建议每个事务的子事务不要超过64个, 否则会引入SubtransControlLock等待事件, 可能影响性能.
create unlogged table a1 (id int, info text);
\x
do language plpgsql $$
declare
v text := '';
begin
v := 'begin;';
for i in 1..66 loop
v := v||format($_$insert into a1 values (1,'test'); savepoint a%s ;$_$, i);
end loop;
-- v := v|| 'end;';
raise notice '%', v;
end;
$$;
begin;insert into a1 values (1,'test'); savepoint a1 ;insert into a1 values (1,'test'); savepoint a2 ;insert into a1 values (1,'test'); savepoint a3 ;insert into a1 values (1,'test'); savepoint a4 ;insert into a1 values (1,'test'); savepoint a5 ;insert into a1 values (1,'test'); savepoint a6 ;insert into a1 values (1,'test'); savepoint a7 ;insert into a1 values (1,'test'); savepoint a8 ;insert into a1 values (1,'test'); savepoint a9 ;insert into a1 values (1,'test'); savepoint a10 ;
BEGIN
INSERT 0 1
SAVEPOINT
INSERT 0 1
SAVEPOINT
INSERT 0 1
SAVEPOINT
INSERT 0 1
SAVEPOINT
INSERT 0 1
SAVEPOINT
INSERT 0 1
SAVEPOINT
INSERT 0 1
SAVEPOINT
INSERT 0 1
SAVEPOINT
INSERT 0 1
SAVEPOINT
INSERT 0 1
SAVEPOINT
每个savepoint 8K内存(CurTransactionContext), 同时在top 事务中消耗一部分内存.
select * from pg_get_backend_memory_contexts();
name | ident | parent | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes
-----------------------------------------+-------------------------------------------+-----------------------+-------+-------------+---------------+------------+-------------+------------
TopMemoryContext | | | 0 | 351872 | 10 | 54352 | 14 | 297520
Record information cache | | TopMemoryContext | 1 | 8192 | 1 | 1576 | 0 | 6616
pgstat TabStatusArray lookup hash table | | TopMemoryContext | 1 | 8192 | 1 | 1448 | 0 | 6744
TopTransactionContext | | TopMemoryContext | 1 | 16384 | 2 | 3024 | 6 | 13360
CurTransactionContext | | TopTransactionContext | 2 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 3 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 4 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 5 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 6 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 7 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 8 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 9 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 10 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 11 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 12 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 13 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 14 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 15 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 16 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 17 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 18 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 19 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 20 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 21 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 22 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 23 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 24 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 25 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 26 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 27 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 28 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 29 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 30 | 8192 | 1 | 7928 | 0 | 264
CurTransactionContext | | CurTransactionContext | 31 | 8192 | 1 | 7928 | 0 | 264
每个子事务也会消耗一个事务号
BEGIN
INSERT 0 1
SAVEPOINT
INSERT 0 1
SAVEPOINT
INSERT 0 1
SAVEPOINT
INSERT 0 1
SAVEPOINT
INSERT 0 1
SAVEPOINT
INSERT 0 1
SAVEPOINT
INSERT 0 1
SAVEPOINT
INSERT 0 1
SAVEPOINT
INSERT 0 1
SAVEPOINT
INSERT 0 1
SAVEPOINT
postgres=# select ctid,xmin,xmax from a1;
ctid | xmin | xmax
--------+---------+------
(0,1) | 2273563 | 0
(0,2) | 2273564 | 0
(0,3) | 2273565 | 0
(0,4) | 2273566 | 0
(0,5) | 2273567 | 0
(0,6) | 2273568 | 0
(0,7) | 2273569 | 0
(0,8) | 2273570 | 0
(0,9) | 2273571 | 0
(0,10) | 2273572 | 0
(10 rows)
create unlogged table a1 (id int, info text);
\x
do language plpgsql $$
declare
v text := '';
begin
v := 'begin;';
for i in 1..500 loop
v := v||format($_$insert into a1 values (1,'test'); savepoint a%s ;$_$, i);
end loop;
v := v|| 'end;';
raise notice '%', v;
end;
$$;
vi test.sql
...
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 900 -j 900 -T 120
postgres=# select wait_event_type,wait_event,count(*) from pg_stat_activity group by 1,2 order by 3 desc;
wait_event_type | wait_event | count
-----------------+---------------------+-------
Client | ClientRead | 48
| | 36
LWLock | SubtransControlLock | 6
Activity | LogicalLauncherMain | 1
Activity | BgWriterHibernate | 1
Activity | WalWriterMain | 1
Activity | CheckpointerMain | 1
Activity | AutoVacuumMain | 1
LWLock | lock_manager | 1
(9 rows)
参考:
https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/
Implementation of subtransactions
To understand what is going on, we have to understand how transactions and subtransactions are implemented.
Whenever a transaction or subtransaction modifies data, it is assigned a transaction ID. PostgreSQL keeps track of these transaction IDs in the commit log, which is persisted in the pg_xact subdirectory of the data directory.
However, there are some differences between transactions and subtransactions:
- Each subtransaction has a containing transaction or subtransaction (the “parent”)
- Committing a subtransaction does not require a WAL flush
- There can be only one transaction per database session, but many subtransactions
The information which (sub)transaction is the parent of a given subtransaction is persisted in the pg_subtrans subdirectory of the data directory. Since this information becomes obsolete as soon as the containing transaction has ended, this data do not have to be preserved across a shutdown or crash.
Subtransactions and visibility
The visibility of a row version (“tuple”) in PostgreSQL is determined by the xmin and xmax system columns, which contain the transaction ID of the creating and destroying transactions. If the transaction ID stored is that of a subtransaction, PostgreSQL also has to consult the state of the containing (sub)transaction to determine if the transaction ID is valid or not.
To determine which tuples a statement can see, PostgreSQL takes a snapshot of the database at the beginning of the statement (or the transaction). Such a snapshot consists of:
- a maximum transaction ID: everything from that transaction on is invisible
- the list of transactions and subtransactions that were active when the snapshot was taken
- the command number of the earliest visible command in the current (sub)transaction
A snapshot is initialized by looking at the process array, which is stored in shared memory and contains information about all currently running backends. This, of course, contains the current transaction ID of the backend and has room for at most 64 non-aborted subtransactions per session. If there are more than 64 such subtransactions, the snapshot is marked as suboverflowed.
Explaining the test results
A suboverflowed snapshot does not contain all data required to determine visibility, so PostgreSQL will occasionally have to resort to pg_subtrans. These pages are cached in shared buffers, but you can see the overhead of looking them up in the high rank of SimpleLruReadPage_ReadOnly in the perf output. Other transactions have to update pg_subtrans to register subtransactions, and you can see in the perf output how they vie for lightweight locks with the readers.
Diagnosing the problem of too many subtransactions
Apart from looking at “perf top”, there are other symptoms that point at the direction of this problem:
- Your workload performs well when run single-threaded, but badly when run in several concurrent database sessions
- You often see the wait event “SubtransControlLock” in pg_stat_activity
- If you export a snapshot using the “pg_export_snapshot()” function, the resulting file in the pg_snapshots subdirectory of the data directory will contain the line “sof:1” to indicate that the subtransaction array overflowed
Conclusion
Subtransactions are a great tool, but you should use them wisely. If you need concurrency, don’t start more than 64 subtransactions per transaction.
The diagnostics shown in this article should help you determine whether you are suffering from the problem, or not.
Finding the cause of that problem can be tricky. For example, it may not be obvious that a function with an exception handler that you call for each result row of an SQL statement (perhaps in a trigger) starts a new subtransaction.
《PostgreSQL 20200819当天代码 - 14 对比 13 高并发性能优化 数据对比 - get snapshot improve》
https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/
https://github.com/digoal/blog/blob/master/202107/20210728_02.md
https://developer.aliyun.com/learning/course/813
https://developer.aliyun.com/learning/course/813/detail/13918
第四课：PolarDB forPG核心 feature介绍 https://developer.aliyun.com/topic/download?id=7902
期望 PostgreSQL 增加什么功能?
类似Oracle RAC架构的PostgreSQL已开源: 阿里云PolarDB for PostgreSQL云原生分布式开源数据库!
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
