public inbox for [email protected]
help / color / mirror / Atom feedFrom: Bharath Rupireddy <[email protected]>
To: Tomas Vondra <[email protected]>
Cc: Gayatri Singh <[email protected]>
Cc: [email protected]
Subject: Re: Identify transactions causing highest wal generation
Date: Fri, 8 Mar 2024 22:08:43 +0530
Message-ID: <CALj2ACWfxRdL2Gaa8SdYcEuoAae8PjYNaXicVokE3M5KFyjfmg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CABDbDcbgo8dz73fsjt5JFc19BLR2h9JNwou7KxWwKEpJc0hAAw@mail.gmail.com>
<[email protected]>
On Fri, Mar 8, 2024 at 9:10 PM Tomas Vondra
<[email protected]> wrote:
>
> On 3/8/24 15:50, Gayatri Singh wrote:
> > Hello Team,
> >
> > Can you help me with steps to identify transactions which caused wal
> > generation to surge ?
> >
>
> You should probably take a look at pg_waldump, which prints information
> about WAL contents, including which XID generated each record.
Right. pg_walinspect too can help get the same info for the available
WAL if you are on a production database with PG15 without any access
to the host instance.
> I don't know what exactly is your goal,
Yeah, it's good to know the use-case if possible.
> but sometimes it's not entirely
> direct relationship.For example, a transaction may delete a record,
> which generates just a little bit of WAL. But then after a checkpoint a
> VACUUM comes around, vacuums the page to reclaim the space of the entry,
> and ends up writing FPI (which is much larger). You could argue this WAL
> is also attributable to the original transaction, but that's not what
> pg_waldump will allow you to do. FPIs in general may inflate the numbers
> unpredictably, and it's not something the original transaction can
> affect very much.
Nice. If one knows the fact that there can be WAL generated without
associated transaction (no XID), there won't be surprises when the
amount of WAL generated by all transactions is compared against the
total WAL on the database.
Alternatively, one can get the correct amount of WAL generated
including the WAL without XID before and after doing some operations
as shown below:
postgres=# SELECT pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/52EB488
(1 row)
postgres=# create table foo as select i from generate_series(1, 1000000) i;
SELECT 1000000
postgres=# update foo set i = i +1 where i%2 = 0;
UPDATE 500000
postgres=# SELECT pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/D2B8000
(1 row)
postgres=# SELECT pg_wal_lsn_diff('0/D2B8000', '0/52EB488');
pg_wal_lsn_diff
-----------------
134007672
(1 row)
postgres=# SELECT pg_size_pretty(pg_wal_lsn_diff('0/D2B8000', '0/52EB488'));
pg_size_pretty
----------------
128 MB
(1 row)
--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
view thread (5+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Identify transactions causing highest wal generation
In-Reply-To: <CALj2ACWfxRdL2Gaa8SdYcEuoAae8PjYNaXicVokE3M5KFyjfmg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox