Content-Type: multipart/alternative;
 boundary="------------RAQcmPOeO9JacnYfdzvju6EQ"
Message-ID: <771c8913-f10e-892d-7b00-d71ca84ce7b5@gmail.com>
Date: Sun, 11 Dec 2022 22:36:29 -0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.5.1
Subject: Re: How to speed WAL apply in destination
To: srinivas oguri <srinivasoguri7@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
References: <364439211.2445507.1670562179127.ref@mail.yahoo.com>
 <364439211.2445507.1670562179127@mail.yahoo.com>
 <1E6A35F6-6866-4FDB-8CE8-38C328FF1865@kain.org>
 <44581ceb3c9c96f48e131ed3de33389db07317e9.camel@cybertec.at>
 <203270855.2602687.1670583423055@mail.yahoo.com>
 <31247466-cdde-71d4-2beb-ffcf7d7fe4db@gmail.com>
 <CADfH0ys8afO8Ck-nke8Qrn=aZqKEBb__XCo4QSE+=J2x4PuNCA@mail.gmail.com>
Content-Language: en-US
From: Mladen Gogala <gogala.mladen@gmail.com>
In-Reply-To: 
 <CADfH0ys8afO8Ck-nke8Qrn=aZqKEBb__XCo4QSE+=J2x4PuNCA@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/771c8913-f10e-892d-7b00-d71ca84ce7b5%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------RAQcmPOeO9JacnYfdzvju6EQ
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 12/11/22 22:02, srinivas oguri wrote:
> Hi,
>
> We are facing similar problems but the lag is at replay on standby. 
> How can I troubleshoot the replay lag.
>
> Thanks
> Srinivas

Well, replay uses CPU and disk I/O. You should monitor both using the OS 
tools. On Linux, Slowaris and AIX, that would be top, atop, iotop, sar, 
iostat, nmon, dstat and alike. Also perf tools "perf top" are useful. On 
Windows, that would be the performance monitor. You first have to figure 
out where exactly is the bottleneck. Then I would go about looking for 
newer version that has improved on WAL replay. For instance, PostgreSQL 
15 can do parallel replay, which should speed things up, at least a bit. 
Then, you should test the new version. After that you should decide 
whether to beef up the hardware with or without upgrade. At any rate, 
you should treat that like a project. Create a project plan, define 
milestones, define how you test and what you test and give it a go. You 
didn't specify the Postgres version or the OS, so a generic answer like 
this is the best I can come up with. Second best would be the answer to 
the question of life, universe and everything. Good luck!

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--------------RAQcmPOeO9JacnYfdzvju6EQ
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 12/11/22 22:02, srinivas oguri
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CADfH0ys8afO8Ck-nke8Qrn=aZqKEBb__XCo4QSE+=J2x4PuNCA@mail.gmail.com">Hi, 
      <div dir="auto"><br>
      </div>
      <div dir="auto">We are facing similar problems but the lag is at
        replay on standby. How can I troubleshoot the replay lag.</div>
      <div dir="auto"><br>
      </div>
      <div dir="auto">Thanks</div>
      <div dir="auto">Srinivas</div>
    </blockquote>
    <p>Well, replay uses CPU and disk I/O. You should monitor both using
      the OS tools. On Linux, Slowaris and AIX, that would be top, atop,
      iotop, sar, iostat, nmon, dstat and alike. Also perf tools "perf
      top" are useful. On Windows, that would be the performance
      monitor. You first have to figure out where exactly is the
      bottleneck. Then I would go about looking for newer version that
      has improved on WAL replay. For instance, PostgreSQL 15 can do
      parallel replay, which should speed things up, at least a bit.
      Then, you should test the new version. After that you should
      decide whether to beef up the hardware with or without upgrade. At
      any rate, you should treat that like a project. Create a project
      plan, define milestones, define how you test and what you test and
      give it a go. You didn't specify the Postgres version or the OS,
      so a generic answer like this is the best I can come up with.
      Second best would be the answer to the question of life, universe
      and everything. Good luck!<br>
    </p>
    <pre class="moz-signature" cols="72">-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
<a class="moz-txt-link-freetext" href="https://dbwhisperer.wordpress.com">https://dbwhisperer.wordpress.com</a>
</pre>
  </body>
</html>

--------------RAQcmPOeO9JacnYfdzvju6EQ--