Re: Identify transactions causing highest wal generation
On Fri, Mar 8, 2024 at 9:10 PM Tomas Vondra 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, 100) i; SELECT 100 postgres=# update foo set i = i +1 where i%2 = 0; UPDATE 50 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
Re: Identify transactions causing highest wal generation
On Fri, Mar 8, 2024, at 12:40 PM, Tomas Vondra 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. You can also use pg_stat_statements to obtain this information. postgres=# select * from pg_stat_statements order by wal_bytes desc; -[ RECORD 1 ]--+--- --- --- - userid | 10 dbid | 16385 toplevel | t queryid| -8403979585082616547 query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2 plans | 0 total_plan_time| 0 min_plan_time | 0 max_plan_time | 0 mean_plan_time | 0 stddev_plan_time | 0 calls | 238260 total_exec_time| 4642.59929618 min_exec_time | 0.011094 max_exec_time | 0.872748 mean_exec_time | 0.01948543312347807 stddev_exec_time | 0.006370786385582063 rows | 238260 . . . wal_records| 496659 wal_fpi| 19417 wal_bytes | 208501147 . . . -- Euler Taveira EDB https://www.enterprisedb.com/
Re: Identify transactions causing highest wal generation
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. I don't know what exactly is your goal, 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. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Identify transactions causing highest wal generation
Hello Team, Can you help me with steps to identify transactions which caused wal generation to surge ? Regards, Gayatri.