由pg_xlogdump统计信息想到的问题
最近深入理解了Checkpoint的相关逻辑,再来看WAL日志的一些设置,又有了新的收获。
1.回顾pg_xlogdump出来的wal日志信息;
2.wal中FPI的占比很高问题分析;
3.重申full_page_writes的作用;
4.解决wal日志很大的问题,使用压缩更靠谱。不建议将full_page_writes设置为false来提升性能,就像把fsync设置为false提升性能一样是不合理的。
先来看一段WAL日志记录,我们通过pg_xlogdump来解析:
apple=# create table test(id int); CREATE TABLE execute query success, query is create table test(id int); apple=# select pg_current_xlog_location(); pg_current_xlog_location -------------------------- 0/8004B738 (1 row) apple=# insert into test values(1); INSERT 0 1 apple=# insert into test values(2); INSERT 0 1 apple=# insert into test values(3); INSERT 0 1 execute query success, query is select pg_current_xlog_location(); apple=# checkpoint; CHECKPOINT apple=# select pg_current_xlog_location(); pg_current_xlog_location -------------------------- 0/8004B8E0 (1 row) apple=# \q appledeMacBook-Pro-2:pg_xlog apple$ pg_xlogdump -p ./ -s 0/8004B738 -e 0/8004B8E0 rmgr: Heap len (rec/tot): 3/ 59, tx: 718239, lsn: 0/8004B738, prev 0/8004B710, desc: INSERT+INIT off 1, blkref #0: rel 1663/16384/53523 blk 0 rmgr: Transaction len (rec/tot): 8/ 34, tx: 718239, lsn: 0/8004B778, prev 0/8004B738, desc: COMMIT 2019-03-28 11:02:44.115417 CST rmgr: Heap len (rec/tot): 3/ 59, tx: 718240, lsn: 0/8004B7A0, prev 0/8004B778, desc: INSERT off 2, blkref #0: rel 1663/16384/53523 blk 0 rmgr: Transaction len (rec/tot): 8/ 34, tx: 718240, lsn: 0/8004B7E0, prev 0/8004B7A0, desc: COMMIT 2019-03-28 11:02:46.022003 CST rmgr: Heap len (rec/tot): 3/ 59, tx: 718241, lsn: 0/8004B808, prev 0/8004B7E0, desc: INSERT off 3, blkref #0: rel 1663/16384/53523 blk 0 rmgr: Transaction len (rec/tot): 8/ 34, tx: 718241, lsn: 0/8004B848, prev 0/8004B808, desc: COMMIT 2019-03-28 11:02:48.293447 CST rmgr: XLOG len (rec/tot): 80/ 106, tx: 0, lsn: 0/8004B870, prev 0/8004B848, desc: CHECKPOINT_ONLINE redo 0/8004B870; tli 1; prev tli 1; fpw true; xid 0/718242; oid 61709; multi 1; offset 0; oldest xid 931 in DB 19697; oldest multi 1 in DB 19697; oldest/newest commit timestamp xid: 0/0; oldest running xid 0; online
解释一下pg_xlogdump各个字段的意思:
rmgr: Heap PostgreSQL内部将WAL日志归类到20多种不同的资源管理器。这条WAL记录所属资源管理器为Heap,即堆表。除了Heap还有Btree,Transaction等。 len (rec/tot): 3/ 59 WAL记录的总长度是59字节,其中main data部分是3字节(只计数main data可能并不合理,本文的后面会有说明)。 tx: 718239 事务号 lsn: 0/8004B738 本WAL记录的LSN prev 0/8004B710 上条WAL记录的LSN desc: INSERT+INIT off 1 这是一条insert类型的记录(每个资源管理器最多包含16种不同的WAL记录类型,),tuple在page中的位置为1。 blkref #0: rel 1663/16384/53523 blk 0 引用的第一个page所属的对表文件为1663/16384/53523,块号为0(即ctid的前半部分)。通过oid2name可以查到是哪个堆表。 apple=# select pg_relation_filepath('test'); pg_relation_filepath ---------------------- base/16384/53523 (1 row)
再回顾一下pg_xlogdump的使用方法:
appledeMacBook-Pro-2:service_list apple$ pg_xlogdump --help pg_xlogdump decodes and displays PostgreSQL transaction logs for debugging. Usage: pg_xlogdump [OPTION]... [STARTSEG [ENDSEG]] Options: -b, --bkp-details output detailed information about backup blocks -e, --end=RECPTR stop reading at log position RECPTR -f, --follow keep retrying after reaching end of WAL -n, --limit=N number of records to display -p, --path=PATH directory in which to find log segment files (default: ./pg_xlog) -r, --rmgr=RMGR only show records generated by resource manager RMGR use --rmgr=list to list valid resource manager names -s, --start=RECPTR start reading at log position RECPTR -t, --timeline=TLI timeline from which to read log records (default: 1 or the value used in STARTSEG) -V, --version output version information, then exit -x, --xid=XID only show records with TransactionId XID -z, --stats[=record] show statistics instead of records (optionally, show per-record statistics) -?, --help show this help, then exit
--对-b说明一下:Data portion of XLOG record is classified into either backup block (entire page) or non-backup block (different data by operation).
我们再关注一下对WAL的统计信息:
appledeMacBook-Pro-2:data apple$ pg_xlogdump -p pg_xlog -z -s 0/80000098 -e 0/8001B9D0 Type N (%) Record size (%) FPI size (%) Combined size (%) ---- - --- ----------- --- -------- --- ------------- --- XLOG 1 ( 1.08) 28 ( 1.01) 0 ( 0.00) 28 ( 0.03) Transaction 5 ( 5.38) 216 ( 7.76) 0 ( 0.00) 216 ( 0.20) Storage 1 ( 1.08) 40 ( 1.44) 0 ( 0.00) 40 ( 0.04) CLOG 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) Database 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) Tablespace 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) MultiXact 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) RelMap 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) Standby 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) Heap2 2 ( 2.15) 64 ( 2.30) 6236 ( 5.89) 6300 ( 5.80) Heap 57 ( 61.29) 1734 ( 62.28) 37060 ( 35.00) 38794 ( 35.69) Btree 27 ( 29.03) 702 ( 25.22) 62604 ( 59.12) 63306 ( 58.25) Hash 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) Gin 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) Gist 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) Sequence 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) SPGist 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) BRIN 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) CommitTs 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) ReplicationOrigin 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) -------- -------- -------- -------- Total 93 2784 [2.56%] 105900 [97.44%] 108684 [100%]
各个列的解释:
各个列的解释: Type:xlog中的日志类型 N: 次数及占比 Record size:记录的大小及占比 FPI size: full page image大小及占比 combined size: 记录和FPI的大小总和
我们看到WAL中的FPI占比非常大,到了97.44%,真正记录Record的信息才2.56%,那么为什么会这样呢,这样合理吗?
FPI的来源是设置了full_page_writes=on,每个Checkpoint之后的修改的数据页都会记录到wal中,导致wal中的FPI占比就很大。
因此,有人就说加大两个Checkpoint点之间的时间间隔,可以减少FPI的占比,这是不合理的,官网上说是每个Checkpoint之后改变的page都会写入到wal,而很多人理解为Checkpoint之后,第一个被修改的page才会写入到wal,这是错误的的理解:
When this parameter is on, the PostgreSQL server writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint.
那么这里又引申出为什么要设置full_page_writes=on?
当一个崩溃发生在Checkpoint过程中,导致部分page中数据已经刷新了,某些page中的数据可能才写了一半。那么我们在重启数据库进行恢复时,会从上一个完成的Checkpoint点开始回放,如果没有FPI,则会基于已经刷新的page进行修改,这是错误的。
这是典型的部分写问题,我们应该从两个方面理解部分写:
1)某个page只写了一部分,就发生了crash;
2)这一批要刷新的page,只有一部分被更新了,仍然有一部分没有写入。
针对1)中的情况,我们可以选用支持原子写的文件系统来解决,我们保证写page的操作是原子的,要么失败,要么成功。目前有ZFS文件系统,ext4的事务写也可以解决,设置data=journal。
针对2)中的情况,单纯使用文件系统的原子写还是无法解决,因此只有通过full_page_writes=on来解决。
针对2)的情况,可以使用集群的方式,从备库进行恢复。
那么说到最后,我们可以这么总结吗:如果有高可用集群,那么我们可以将full_page_writes设置为false来提升性能???
下面是一个非官方的统计:设置了full_page_writes=on,数据库的TPS会减少30%以上,WAL日志大小会增加500%以上,对page更新越稀疏,问题越严重。
针对WAL日志增加太多的情况,我们可以使用wal_compression=on来进行压缩,一般可以减少50%左右,且TPS也有少量的提升。
再次强调,设置checkpoint_timeout没有用处,因为每个被改变的page都会写入到wal中。
后续引申出Checkpoint的工作原理、fsync的设置问题、double write来替代full_page_writes,找机会再记录一下。