Re: How to speed WAL apply in destination

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__AndroidUsers&af_wl=ym&af_sub1=Internal&af_sub2=Global_YGrowth&af_sub3=EmailSignature>
>
> On Fri, Dec 9, 2022 at 4:20 PM, Laurenz Albe
> <laurenz(dot)albe(at)cybertec(dot)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(at)yahoo(dot)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