pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedA Question about FPW and WAL
4+ messages / 3 participants
[nested] [flat]
* A Question about FPW and WAL
@ 2023-04-03 06:51 Dawn Yu <boyyuxiao1987@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Dawn Yu @ 2023-04-03 06:51 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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%]
Attachments:
[text/plain] waldump.txt (3.7K, ../../CALiuKDHL-5h4DWYKiCXpQHJ8UHEG7mKj38eJVGWeT=nf604bLQ@mail.gmail.com/3-waldump.txt)
download | inline:
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%]
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: A Question about FPW and WAL
@ 2023-04-03 09:38 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Dawn Yu <boyyuxiao1987@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2023-04-03 09:38 UTC (permalink / raw)
To: Dawn Yu <boyyuxiao1987@gmail.com>; pgsql-admin@lists.postgresql.org
On Mon, 2023-04-03 at 14:51 +0800, Dawn Yu wrote:
> 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.
I would feel funny about a parameter like that.
There are a couple of things you can do today:
- increase "max_wal_size" and "checkpoint_timeout" to get checkpoints less often
- activate "wal_compression", so that the FPW are compressed. Best is to use v15,
where you can use "lz4" or "zstd" compression, which are much faster.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: A Question about FPW and WAL
@ 2023-04-03 14:30 Rui DeSousa <rui@crazybean.net>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 4+ messages in thread
From: Rui DeSousa @ 2023-04-03 14:30 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Dawn Yu <boyyuxiao1987@gmail.com>; pgsql-admin@lists.postgresql.org
> On Apr 3, 2023, at 5:38 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
>> 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.
>
> I would feel funny about a parameter like that.
I agree. To allow for indexes to become corrupt by design would be a bad idea. Rebuilding indexes is a not a solution as some index rebuilds takes hours on very large tables. Dealing with identifying corrupted indexes and the issues caused by them would be a nightmare to deal with and not very ACID compliant.=
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: A Question about FPW and WAL
@ 2023-04-11 05:53 Dawn Yu <boyyuxiao1987@gmail.com>
parent: Rui DeSousa <rui@crazybean.net>
0 siblings, 0 replies; 4+ messages in thread
From: Dawn Yu @ 2023-04-11 05:53 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org
Ok, thanks.
On Mon, 3 Apr 2023 at 22:30, Rui DeSousa <rui@crazybean.net> wrote:
>
>
> On Apr 3, 2023, at 5:38 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> 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.
>
>
> I would feel funny about a parameter like that.
>
>
> I agree. To allow for indexes to become corrupt by design would be a bad
> idea. Rebuilding indexes is a not a solution as some index rebuilds takes
> hours on very large tables. Dealing with identifying corrupted indexes and
> the issues caused by them would be a nightmare to deal with and not very
> ACID compliant.
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-04-11 05:53 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-03 06:51 A Question about FPW and WAL Dawn Yu <boyyuxiao1987@gmail.com>
2023-04-03 09:38 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-04-03 14:30 ` Rui DeSousa <rui@crazybean.net>
2023-04-11 05:53 ` Dawn Yu <boyyuxiao1987@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox