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.