Add wal_fpi_bytes_[un]compressed to pg_stat_wal - Mailing list pgsql-hackers

From Shinya Kato
Subject Add wal_fpi_bytes_[un]compressed to pg_stat_wal
Date
Msg-id CAOzEurQtZEAfg6P0kU3Wa-f9BWQOi0RzJEMPN56wNTOmJLmfaQ@mail.gmail.com
Whole thread Raw
Responses Re: Add wal_fpi_bytes_[un]compressed to pg_stat_wal
List pgsql-hackers
Hi hackers,

I am proposing a patch that adds wal_fpi_bytes_[un]compressed columns
to pg_stat_wal. These columns help us calculate WAL FPI (full page
image) compression rates, confirm the usefulness of wal_compression
and determine which compression algorithms are most effective.

Currently, we must use cumbersome methods to compute the WAL compression rate:
1.  Run the same benchmark twice (once with wal_compression enabled
and one disabled) and compare the wal_bytes values in pg_stat_wal.
However, this value reflects the total WAL reduction, not just the
reduction from full page images. (pg_waldump --stats can provide
similar data but it also requires direct access to WAL files and must
be run on the server.)

2. Run pg_waldump --fullpage and compare the reported compressed size
against the calculated uncompressed size (e.g. 8192 bytes -
hole_length). This computation is inconvenient and also requires WAL
access on the server.

With these patches applied, we can easily compute the FPI compression
rate with the following SQL:
=# SELECT wal_fpi_bytes_compressed / wal_fpi_bytes_uncompressed * 100
AS wal_compression_rate FROM pg_stat_wal;
  wal_compression_rate
-------------------------
 34.07161865906799706100
(1 row)

The 0001 patch adds these columns to pg_stat_wal. The 0002 and 0003
patches add this information to EXPLAIN (WAL) and pg_stat_statements,
respectively. I don't think these additions (0002 and 0003) are
mandatory, so I suggest we focus the discussion on the 0001 patch
first.

Thoughts?

--
Best regards,
Shinya Kato
NTT OSS Center

Attachment

pgsql-hackers by date:

Previous
From: Nazir Bilal Yavuz
Date:
Subject: Re: Improve docs syntax checking and enable it in the meson build
Next
From: jian he
Date:
Subject: Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement