pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to speed WAL apply in destination
15+ messages / 8 participants
[nested] [flat]
* How to speed WAL apply in destination
@ 2022-12-09 05:02 SASIKUMAR Devaraj <sashikumard@yahoo.com>
0 siblings, 1 reply; 15+ messages in thread
From: SASIKUMAR Devaraj @ 2022-12-09 05:02 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi All
How to speed up WAL apply process in secondary? Any parameter available from postgres, apart from disk speed?
RegardsSasi
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to speed WAL apply in destination
@ 2022-12-09 08:47 Bryon Roché <kain@kain.org>
parent: SASIKUMAR Devaraj <sashikumard@yahoo.com>
0 siblings, 2 replies; 15+ messages in thread
From: Bryon Roché @ 2022-12-09 08:47 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
There are fsync parameters which can be set to off that may provide a modest speed increase, at the cost of removing crash safety.
On December 9, 2022 5:02:59 AM UTC, SASIKUMAR Devaraj <sashikumard@yahoo.com> wrote:
>Hi All
>How to speed up WAL apply process in secondary? Any parameter available from postgres, apart from disk speed?
>RegardsSasi
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to speed WAL apply in destination
@ 2022-12-09 10:07 SASIKUMAR Devaraj <sashikumard@yahoo.com>
parent: Bryon Roché <kain@kain.org>
1 sibling, 0 replies; 15+ messages in thread
From: SASIKUMAR Devaraj @ 2022-12-09 10:07 UTC (permalink / raw)
To: kain@kain.org, Pgsql-admin <pgsql-admin@lists.postgresql.org>
Thanks Bryon. Will deep dive into this. Any other parameters or parallel writes will help?
Sent from Yahoo Mail on Android
On Fri, 9 Dec 2022 at 3:12 PM, Bryon Roché<kain@kain.org> wrote: There are fsync parameters which can be set to off that may provide a modest speed increase, at the cost of removing crash safety.
On December 9, 2022 5:02:59 AM UTC, SASIKUMAR Devaraj <sashikumard@yahoo.com> wrote:
Hi All
How to speed up WAL apply process in secondary? Any parameter available from postgres, apart from disk speed?
RegardsSasi
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to speed WAL apply in destination
@ 2022-12-09 10:50 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Bryon Roché <kain@kain.org>
1 sibling, 2 replies; 15+ messages in thread
From: Laurenz Albe @ 2022-12-09 10:50 UTC (permalink / raw)
To: Bryon Roché <kain@kain.org>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to speed WAL apply in destination
@ 2022-12-09 10:57 SASIKUMAR Devaraj <sashikumard@yahoo.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 3 replies; 15+ messages in thread
From: SASIKUMAR Devaraj @ 2022-12-09 10:57 UTC (permalink / raw)
To: laurenz.albe@cybertec.at, Bryon Roché <kain@kain.org>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to speed WAL apply in destination
@ 2022-12-09 14:21 Scott Ribe <scott_ribe@elevated-dev.com>
parent: SASIKUMAR Devaraj <sashikumard@yahoo.com>
2 siblings, 1 reply; 15+ messages in thread
From: Scott Ribe @ 2022-12-09 14:21 UTC (permalink / raw)
To: SASIKUMAR Devaraj <sashikumard@yahoo.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Bryon Roché <kain@kain.org>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Dec 9, 2022, at 4:57 AM, SASIKUMAR Devaraj <sashikumard@yahoo.com> 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
- have you checked network throughput between the 2 nodes?
- are the 2 nodes similar hardware configurations?
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to speed WAL apply in destination
@ 2022-12-09 22:52 Rui DeSousa <rui@crazybean.net>
parent: SASIKUMAR Devaraj <sashikumard@yahoo.com>
2 siblings, 0 replies; 15+ messages in thread
From: Rui DeSousa @ 2022-12-09 22:52 UTC (permalink / raw)
To: SASIKUMAR Devaraj <sashikumard@yahoo.com>; +Cc: laurenz.albe@cybertec.at, Bryon Roché <kain@kain.org>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Dec 9, 2022, at 5:57 AM, SASIKUMAR Devaraj <sashikumard@yahoo.com> 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
I assume this is streaming replication? What’s the network configuration between the nodes? When I was replicating across the pond (high latency); I would monitor the lag and if it fell behind by several gigabytes the script would ship the WAL files in parallel to the remote WAL backup server across the pond. It then terminated the streaming replication which would force the DR replica to pull the WAL files from its local backup server which was much faster than streaming replication (a single tcp connection) over a high latency connection.
What’s the replication configuration? Is the replica a hot standby with active queries? What’s the I/o subsystems on both systems?=
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to speed WAL apply in destination
@ 2022-12-10 01:37 SASIKUMAR Devaraj <sashikumard@yahoo.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 15+ messages in thread
From: SASIKUMAR Devaraj @ 2022-12-10 01:37 UTC (permalink / raw)
To: scott_ribe@elevated-dev.com; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Bryon Roché <kain@kain.org>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi
This happens only during heavy data load. Other time it is normal.
Sent from Yahoo Mail on Android
On Fri, Dec 9, 2022 at 7:52 PM, Scott Ribe<scott_ribe@elevated-dev.com> wrote: > On Dec 9, 2022, at 4:57 AM, SASIKUMAR Devaraj <sashikumard@yahoo.com> 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
- have you checked network throughput between the 2 nodes?
- are the 2 nodes similar hardware configurations?
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to speed WAL apply in destination
@ 2022-12-11 20:22 Mladen Gogala <gogala.mladen@gmail.com>
parent: SASIKUMAR Devaraj <sashikumard@yahoo.com>
2 siblings, 1 reply; 15+ messages in thread
From: Mladen Gogala @ 2022-12-11 20:22 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to speed WAL apply in destination
@ 2022-12-11 21:18 Bryon Roché <kain@kain.org>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 2 replies; 15+ messages in thread
From: Bryon Roché @ 2022-12-11 21:18 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On December 9, 2022 10:50:07 AM UTC, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>On Fri, 2022-12-09 at 08:47 +0000, Bryon Roché wrote:
>>
>> 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.
It is situational advice, and depending on your application, not bad advice at all. It is certainly not something you want off by default, or even regularly.
In fact, the PostgreSQL 15 (and earlier) versions cover this in the documentation for the fsync and synchronous_commit parameters in section 20.5, including considerations involved in deciding when to use those arameters, along with further references in the documentation to cover more of the performance, synchronicity, and durability parameters, in detail.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to speed WAL apply in destination
@ 2022-12-12 03:02 srinivas oguri <srinivasoguri7@gmail.com>
parent: Mladen Gogala <gogala.mladen@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: srinivas oguri @ 2022-12-12 03:02 UTC (permalink / raw)
To: Mladen Gogala <gogala.mladen@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi,
We are facing similar problems but the lag is at replay on standby. How can
I troubleshoot the replay lag.
Thanks
Srinivas
On Mon, Dec 12, 2022, 1:52 AM Mladen Gogala <gogala.mladen@gmail.com> wrote:
> 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> <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-1217https://dbwhisperer.wordpress.com
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to speed WAL apply in destination
@ 2022-12-12 03:36 Mladen Gogala <gogala.mladen@gmail.com>
parent: srinivas oguri <srinivasoguri7@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Mladen Gogala @ 2022-12-12 03:36 UTC (permalink / raw)
To: srinivas oguri <srinivasoguri7@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to speed WAL apply in destination
@ 2022-12-12 11:40 Raghvendra Choudhary <raghvendra.choudhary@digivalet.com>
parent: Bryon Roché <kain@kain.org>
1 sibling, 1 reply; 15+ messages in thread
From: Raghvendra Choudhary @ 2022-12-12 11:40 UTC (permalink / raw)
To: Bryon Roché <kain@kain.org>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hello Bryon,
Can you please help me out to install the postgresql cluster steps
*Raghvendra Choudhary*
DevOps Engineer | www.digivalet.com <https://digivalet.com;
[image: Logo]
T: +91.731.6667891
M: +91.96307.90947
E: raghvendra.choudhary@digivalet.com <raghvendra.choudhary@digivalet.com>
[image: Banner]
On Mon, Dec 12, 2022 at 5:08 PM Bryon Roché <kain@kain.org> wrote:
>
>
> On December 9, 2022 10:50:07 AM UTC, Laurenz Albe <
> laurenz.albe@cybertec.at> wrote:
> >On Fri, 2022-12-09 at 08:47 +0000, Bryon Roché wrote:
> >>
> >> 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.
>
> It is situational advice, and depending on your application, not bad
> advice at all. It is certainly not something you want off by default, or
> even regularly.
>
> In fact, the PostgreSQL 15 (and earlier) versions cover this in the
> documentation for the fsync and synchronous_commit parameters in section
> 20.5, including considerations involved in deciding when to use those
> arameters, along with further references in the documentation to cover more
> of the performance, synchronicity, and durability parameters, in detail.
>
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to speed WAL apply in destination
@ 2022-12-12 13:26 Mladen Gogala <gogala.mladen@gmail.com>
parent: Raghvendra Choudhary <raghvendra.choudhary@digivalet.com>
0 siblings, 0 replies; 15+ messages in thread
From: Mladen Gogala @ 2022-12-12 13:26 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 12/12/22 06:40, Raghvendra Choudhary wrote:
> Hello Bryon,
>
> Can you please help me out to install the postgresql cluster steps
>
This is thread hijacking. Please open your own thread and don't do this.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to speed WAL apply in destination
@ 2022-12-20 11:32 SASIKUMAR Devaraj <sashikumard@yahoo.com>
parent: Bryon Roché <kain@kain.org>
1 sibling, 0 replies; 15+ messages in thread
From: SASIKUMAR Devaraj @ 2022-12-20 11:32 UTC (permalink / raw)
To: kain@kain.org, Laurenz Albe <laurenz.albe@cybertec.at>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi all
Issue resolved after increasing memory.
Sent from Yahoo Mail on Android
On Mon, Dec 12, 2022 at 5:09 PM, Bryon Roché<kain@kain.org> wrote:
On December 9, 2022 10:50:07 AM UTC, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>On Fri, 2022-12-09 at 08:47 +0000, Bryon Roché wrote:
>>
>> 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.
It is situational advice, and depending on your application, not bad advice at all. It is certainly not something you want off by default, or even regularly.
In fact, the PostgreSQL 15 (and earlier) versions cover this in the documentation for the fsync and synchronous_commit parameters in section 20.5, including considerations involved in deciding when to use those arameters, along with further references in the documentation to cover more of the performance, synchronicity, and durability parameters, in detail.
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2022-12-20 11:32 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-09 05:02 How to speed WAL apply in destination SASIKUMAR Devaraj <sashikumard@yahoo.com>
2022-12-09 08:47 ` Bryon Roché <kain@kain.org>
2022-12-09 10:07 ` SASIKUMAR Devaraj <sashikumard@yahoo.com>
2022-12-09 10:50 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-12-09 10:57 ` SASIKUMAR Devaraj <sashikumard@yahoo.com>
2022-12-09 14:21 ` Scott Ribe <scott_ribe@elevated-dev.com>
2022-12-10 01:37 ` SASIKUMAR Devaraj <sashikumard@yahoo.com>
2022-12-09 22:52 ` Rui DeSousa <rui@crazybean.net>
2022-12-11 20:22 ` Mladen Gogala <gogala.mladen@gmail.com>
2022-12-12 03:02 ` srinivas oguri <srinivasoguri7@gmail.com>
2022-12-12 03:36 ` Mladen Gogala <gogala.mladen@gmail.com>
2022-12-11 21:18 ` Bryon Roché <kain@kain.org>
2022-12-12 11:40 ` Raghvendra Choudhary <raghvendra.choudhary@digivalet.com>
2022-12-12 13:26 ` Mladen Gogala <gogala.mladen@gmail.com>
2022-12-20 11:32 ` SASIKUMAR Devaraj <sashikumard@yahoo.com>
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