PostgreSQL 未来OLTP场景优化 - 包括walinsert、buffer manage、async protocol、 CSN 优化原理 - 高并发场景优化

原创 digoal 2022-01-20

823

作者

digoal

日期

2020-08-12

为什么要优化?
- 现在的机器核数都很多, 用户并发也很高, 由于每次获取事务快照都要掉用GetSnapshotData, 对procArray加共享锁, 遍历procArray后释放. 并发越高, procArray越大, 越耗费CPU.
- 事务结束时要对procArray加排他锁, 如果有高并发的查询, 会导致排他与共享锁冲突概率增加, 从而影响性能. 所以高并发小事务混合读写场景性能影响较为严重.

《PostgreSQL 14 GetSnapshotData 高并发优化》
《PostgreSQL 20200819当天代码 - 14 对比 13 高并发性能优化数据对比 - get snapshot improve》

CSN 优化原理:

https://postgrespro.ru/media/2019/10/26/future_is_csn.pdf

Array of active transaction ids is stored in shared memory.
GetSnapshotData() scans all the active xids while holding shared ProcArrayLock.
Assigning of new xid doesn’t require ProcArrayLock.
Clearing active xid requires exclusive ProcArrayLock.
9.6 comes with “group clear xid” optimization.
Multiple xids of finished transactions could be cleared using single exclusive ProcArrayLock.

Nowadays multi-core systems running can run thousands of backends simultaneously. For short queries GetSnapshotData() becomes just CPU expensive.
LWLock subsystem is just not designed for high concurrency. In particular, exclusive lock waits could have infinite starvation. Therefore, it’s impossible to connect while there is high flow of short readonly queries.
In the mixed read-write workload, ProcArrayLock could become a bottleneck.

Taking snapshots is cheaper. It’s even possible to make it lockless.
CSN snapshots are more friendly to distributed systems. Distributed visibility techniques like incremental snapshots or Clock-SI assumes that snapshot is represented by single number.

Make both XID and CSN 64-bit
Add 64-bit xid_epoch, multixact_epoch and csn_epoch to page header.
Allocate high bit of xmin and xmax for CSN flag.
Actual xid or csn stored in xmin or xmax should be found as corresponding epoch plus xmin or xmax.
We still can address 2^31 xids from xmin and xmax as we did before.
Wraparound is possible only inside single page. And it could be resolved by single page freeze.

Rewrite XID to CSN instead of seƫng “committed” hint bit.
Lockless snapshot taking

Buffer manager – slow hash-table, pin, locks etc.
Synchronous protocol.
Executor.
Slow xid allocation – a lot of locks.

SELECT val FROM t WHERE id IN (:id1, ... :id10) –  
150K per second = 1.5M key-value pairs per second, no gain.  
Bottleneck in buffer manager.  
SELECT 1 with CSN-rewrite patch – 3.9M queries per second.  
Bottleneck in Protocol and executor are bottlenecks.  
SELECT txid_current() – 390K per second.   
Bottleneck in locks.