Dear community:
Recently，we come up with a same problem，when the database make the checkpoint,the size of wal directory increase very fast and take one hundred percent disk usage,we found the problem of pg wal write amplification is very serious. By analyzing the wal log, in some scenarios, most of the space is wasted when maintaining the full page of the index. Has the community considered modifying this part of the relevant code to make the full page better,The setting of FPW function is divided into two parts, the full-page write of the data record and the full-page write of the index block, and the switch functions can be set separately. By default, index full-page write is disabled. If the index is broken, you can rebuild the index to solve it. Although the security of index data cannot be guaranteed, this setting can greatly reduce the problem of storage space explosion caused by wal write amplification.
The following is some example,we can find that the FPW of Btree take over 83.84 percent size of the wal log.
Type N (%) Record size (%) FPI size (%) Combined size (%)
---- - --- ----------- --- -------- --- ------------- ---
XLOG 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Transaction 3064 ( 9.87) 104176 ( 2.67) 0 ( 0.00) 104176 ( 0.08)
Storage 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
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 1 (0.00) 50 (0.00) 0 ( 0.00) 50 (0.00)
Heap2 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Heap 3062 ( 9.86) 526664 ( 13.49) 0 ( 0.00) 526664 ( 0.40)
Btree 24924 ( 80.27) 3273484 ( 83.84) 127027284 (100.00)
Postgres Version: pg14.4
OS: SUSE15SP3
Thank you,
Best
DawnYu.
Type N (%) Record size (%) FPI size (%) Combined size (%)
---- - --- ----------- --- -------- --- ------------- ---
XLOG 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Transaction 3064 ( 9.87) 104176 ( 2.67) 0 ( 0.00) 104176 ( 0.08)
Storage 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
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 1 (0.00) 50 (0.00) 0 ( 0.00) 50 (0.00)
Heap2 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Heap 3062 ( 9.86) 526664 ( 13.49) 0 ( 0.00) 526664 ( 0.40)
Btree 24924 ( 80.27) 3273484 ( 83.84) 127027284 (100.00) 130300768 (99.52)
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)
Generic 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
LogicalMessage 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
-------- -------- -------- --------
Total 31051 3904374 [2.98%] 127027284 [97.02%] 130931658 [100%]
