Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rid9o-000gbZ-Ih for pgsql-hackers@arkaria.postgresql.org; Fri, 08 Mar 2024 16:33:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rid9n-00EDte-0Q for pgsql-hackers@arkaria.postgresql.org; Fri, 08 Mar 2024 16:33:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rid9m-00EDtV-2H for pgsql-hackers@lists.postgresql.org; Fri, 08 Mar 2024 16:33:15 +0000 Received: from fhigh4-smtp.messagingengine.com ([103.168.172.155]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rid9i-003Ss9-Sg for pgsql-hackers@lists.postgresql.org; Fri, 08 Mar 2024 16:33:13 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailfhigh.nyi.internal (Postfix) with ESMTP id 7E12511400A0; Fri, 8 Mar 2024 11:33:08 -0500 (EST) Received: from imap49 ([10.202.2.99]) by compute2.internal (MEProxy); Fri, 08 Mar 2024 11:33:08 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=eulerto.com; h= cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm2; t=1709915588; x=1710001988; bh=MXWlPSvomn 0VpII03TafKpCG0QtNL2+1vCs7XmZe+3g=; b=qKxi0h/igkVGjn8VaWo5AUn+Xw v1deUyVo4LE5lbV7yrCTIveSBqvz75u2sP2mujPh79BQNgZt3LpYYPd4R/jr1B4f bSA5B9+WZEB8qbAnrT2bJOQQnTfvuxOEz0nfqPxuyUGoeFrAan11DIpAVC5fiDep 3LP0/sWUxzUC99Q6ZcLp8NviD93lvOeEWTKYOD/Fd7V5XGzhR8aH6Ax7VNsOCTAr Ehm0Oxe3JXDhl7S7mRfdIsmroPp9VyoCeDtKtsRpo59m6wQEKjr3wXsQYka0pcMn iwfL7mDiF3TVfsq5Y/R9yUMy6HfSSbyuSAXIG2holuosCtXaRe1jPcNI+PWQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s= fm1; t=1709915588; x=1710001988; bh=MXWlPSvomn0VpII03TafKpCG0QtN L2+1vCs7XmZe+3g=; b=RiEEwJ1vqqhiGdqnmmvkCA0CHE6CIWFaaHeJ0SDyhwYt PVb9z7Zi8BQTNG777vUyKs+PzSD8iTkTX9Isy+/AvPeDT/VV0Lx75JNu2pPVUph7 0Njku42RqtXUcTaFMMmIfjEcB0qZHJSSJnyR0kmf/KktPSkGs+NWru7Jwm9Uw5h8 rcq2ky7qWryGecUD1s1QRTNJIarff+pd11wsuzqof9tetSsLxbyZu42cAq2MgvGe rLc3BHJQMqmnA5mA+0FFR7xikZ9yvTid0dptx28gBmV1UdnCy0LTNy0Nr3N7sJgu CaoMsQHeFY88zNzBva28Ky6u2GzyX1C0FZlz/ZnV8A== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrieehgdeklecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefofgggkfgjfhffhffvufgtsegrtderreerredtnecuhfhrohhmpedfgfhulhgv rhcuvfgrvhgvihhrrgdfuceovghulhgvrhesvghulhgvrhhtohdrtghomheqnecuggftrf grthhtvghrnhepjeethfeuvdfgffdtteevudeftdejueffkeelveekhfevkeegleefhfej hedtjeegnecuffhomhgrihhnpegvnhhtvghrphhrihhsvggusgdrtghomhenucevlhhush htvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegvuhhlvghrsegvuhhl vghrthhordgtohhm X-ME-Proxy: Feedback-ID: i0c21471d:Fastmail Received: by mailuser.nyi.internal (Postfix, from userid 501) id 15EB815A0092; Fri, 8 Mar 2024 11:33:07 -0500 (EST) X-Mailer: MessagingEngine.com Webmail Interface User-Agent: Cyrus-JMAP/3.11.0-alpha0-251-g8332da0bf6-fm-20240305.001-g8332da0b MIME-Version: 1.0 Message-Id: <184db881-c676-4212-a035-6c5da5dc7053@app.fastmail.com> In-Reply-To: References: Date: Fri, 08 Mar 2024 13:32:47 -0300 From: "Euler Taveira" To: "Tomas Vondra" , "Gayatri Singh" , pgsql-hackers@lists.postgresql.org Subject: Re: Identify transactions causing highest wal generation Content-Type: multipart/alternative; boundary=d9d91e457f11449c97a700433a62f1ce List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --d9d91e457f11449c97a700433a62f1ce Content-Type: text/plain 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.599296000018 min_exec_time | 0.011094999999999999 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/ --d9d91e457f11449c97a700433a62f1ce Content-Type: text/html Content-Transfer-Encoding: quoted-printable
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 pr= ints information
about WAL contents, including which XID g= enerated each record.

You can = also use pg_stat_statements to obtain this information.
postgres=3D# select * from pg_stat_statements order by wal_= bytes desc;
-[ RECORD 1 ]----------+----------------------= ------------------------------------------------------------------------= -----------------
----------------------------------------= ------------------------------------------------------------------------= -----------------------
----------------------------------= ------------------------------------------------------------------------= -----------------------------
----------------------------= ---------------------------------------------------------
= userid           =       | 10
dbid   =             =     | 16385
toplevel    = ;           | t
queryid         &nb= sp;      | -8403979585082616547
q= uery           &n= bsp;      | UPDATE pgbench_accounts SET abalanc= e =3D abalance + $1 WHERE aid =3D $2
plans  &nbs= p;           &nbs= p;   | 0
total_plan_time    =     | 0
min_plan_time   &nbs= p;      | 0
max_plan_time &n= bsp;        | 0
mean_pl= an_time         | 0
stddev_plan_time       | 0
calls           =        | 238260
total_exec_t= ime        | 4642.599296000018
min_exec_time        &nb= sp; | 0.011094999999999999
max_exec_time   =        | 0.872748
mean_exec_= time         | 0.019485433123478= 07
stddev_exec_time       | = 0.006370786385582063
rows     &nb= sp;           &nb= sp; | 238260
.
.
.
wal_records         &nbs= p;  | 496659
wal_fpi     &nb= sp;          | 19417
wal_bytes         &= nbsp;    | 208501147
.
.
<= /div>
.


=
--
Euler Tave= ira
EDB   https://www.enterprisedb.com/

--d9d91e457f11449c97a700433a62f1ce--