From: Mladen Gogala <gogala.mladen@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: How to speed WAL apply in destination
Date: Sun, 11 Dec 2022 15:22:26 -0500
Message-ID: <31247466-cdde-71d4-2beb-ffcf7d7fe4db@gmail.com> (raw)
In-Reply-To: <203270855.2602687.1670583423055@mail.yahoo.com>
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>
On 12/9/22 05:57, SASIKUMAR Devaraj wrote:
> During certain interval there is huge WAL file generation f3om primary
> around 300gb/hr which delays sync in secondary by atleast 30 mins.
> Other time it is normal
>
> Sent from Yahoo Mail on Android
> <https://go.onelink.me/107872968?pid=InProduct&c=Global_Internal_YGrowth_AndroidEmailSig__Android...;
>
> On Fri, Dec 9, 2022 at 4:20 PM, Laurenz Albe
> <laurenz.albe@cybertec.at> wrote:
> On Fri, 2022-12-09 at 08:47 +0000, Bryon Roché wrote:
>
> > On December 9, 2022 5:02:59 AM UTC, SASIKUMAR Devaraj
> <sashikumard@yahoo.com> wrote:
> > > How to speed up WAL apply process in secondary? Any parameter
> available from postgres, apart from disk speed?
> >
> > There are fsync parameters which can be set to off that may
> provide a modest speed increase, at the cost of removing crash safety.
>
>
> That is spectacularly bad advice.
>
> Is the disk the bottleneck at all?
>
> Yours,
> Laurenz Albe
>
>
>
You can use "ionice" and "chrt" to make WAL writer and WAL sender real
time I/O priority. Also, you should consider increasing the network
throughput between the primary and the secondary. What kind of network
is there? Is any of the servers a virtual machine? Most of the
hypervisors throttle network adapters of the virtual machines. Default
network speed for VMWare VMs is 1Gb/sec. I think that the same is true
for KVM. You should check the disk bandwidth consumption by using sar -d
or atop -d. The first step would be to use iperf3 to check the network
throughput.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
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: pgsql-admin@postgresql.org
Cc: gogala.mladen@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: How to speed WAL apply in destination
In-Reply-To: <31247466-cdde-71d4-2beb-ffcf7d7fe4db@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox