Thread: How to speed WAL apply in destination
Hi All
How to speed up WAL apply process in secondary? Any parameter available from postgres, apart from disk speed?
Regards
Sasi
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 AllHow to speed up WAL apply process in secondary? Any parameter available from postgres, apart from disk speed?RegardsSasi
Thanks Bryon. Will deep dive into this. Any other parameters or parallel writes will help?
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 AllHow to speed up WAL apply process in secondary? Any parameter available from postgres, apart from disk speed?RegardsSasi
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 crashsafety. That is spectacularly bad advice. Is the disk the bottleneck at all? Yours, Laurenz Albe
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
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
> 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 byatleast 30 mins. Other time it is normal - have you checked network throughput between the 2 nodes? - are the 2 nodes similar hardware configurations?
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?
Hi
This happens only during heavy data load. Other time it is normal.
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?
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 normalOn 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
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 crashsafety. > >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 wantoff by default, or even regularly. In fact, the PostgreSQL 15 (and earlier) versions cover this in the documentation for the fsync and synchronous_commit parametersin section 20.5, including considerations involved in deciding when to use those arameters, along with furtherreferences in the documentation to cover more of the performance, synchronicity, and durability parameters, in detail.
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 normalOn 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 AlbeYou 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
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.ThanksSrinivas
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
Hello Bryon,
Can you please help me out to install the postgresql cluster steps
| Raghvendra Choudhary DevOps Engineer | www.digivalet.com | |
| T: +91.731.6667891 M: +91.96307.90947 |
 | |
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.
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
Hi all
Issue resolved after increasing memory.
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.