Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p4SqH-0005ly-Fj for pgsql-admin@arkaria.postgresql.org; Sun, 11 Dec 2022 20:22:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p4SqG-0001RS-Ct for pgsql-admin@arkaria.postgresql.org; Sun, 11 Dec 2022 20:22:32 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p4SqF-0001RJ-Ul for pgsql-admin@lists.postgresql.org; Sun, 11 Dec 2022 20:22:32 +0000 Received: from mail-qt1-x836.google.com ([2607:f8b0:4864:20::836]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p4SqD-0004gN-FY for pgsql-admin@lists.postgresql.org; Sun, 11 Dec 2022 20:22:30 +0000 Received: by mail-qt1-x836.google.com with SMTP id c15so7522255qtw.8 for ; Sun, 11 Dec 2022 12:22:29 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=LZO5R76PnaSnBbwMtuaGLVEsLWkE966gyeYlv+8u+vU=; b=UNRPBG4xoRkq3fcJxLNGiThAbJfH5tONvAUJKegezvyxqzsCbfO1DYM7JoSsKmV4gT F/0+uSExQNfxN8eYX+sxqZjTXmKyepnEyamnbT4+LUuo2fu6p2VBg//65LXNVf1ckt8U c8/osEzSuBdZd+1JUIkPs6ULHqIZci5WAkmy4YMvVrl8ayNapcQEPw2LqyJ+UBvKVa9X 0EfKhf6UGG/O42i1382m/ue6Uyl8xBtf348GFHrFoqjioIxZYkm8St4oMz3zrCxkV9Uc nh9GoOjfVFcLimCsHzCMaHyKmqs9gDcF4TficjJRm4/YkZkwG1Td7fG8BHCmfB1IVhwf 5PiQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=LZO5R76PnaSnBbwMtuaGLVEsLWkE966gyeYlv+8u+vU=; b=7Bz6jOH+BbPnZqfGsRhFO+g01yE0lsuyi48DR++npMXepOGLsFdYw3GC9rK/nYp1BW Ujwvz4A6K/eeJSfgp0RvYixElVjCnulJPRI8cqLoirI6UZg3qn4Quwx4XkgdmkDgaFy/ X/fQ2gY1Eyh09FEAJEIx1NgsGtQyPxOeplH1RF7jaup2/T3j0QH0qFvmiCK9i//YWRGw 1ypw+oycJXC1+VFLX0AGvaxnW9flBKwYHB3d9CNRRtnFoBPDElVYo9SUBAqsQ/fCijQf d5BA6Fn+MgOaVSTM+bF1t3Dg0vYlyq7i2op2guA+n1fXNfAWNPUN+9C4bG0Nh3CVekFl XqQw== X-Gm-Message-State: ANoB5pkVtMUckXsoZ6oUR3DTJYpxGzfu6nYkKay5+anky3mAYtf9iE/Y glALkCqvO3G6TCuRDBn/isD3lYnuA5yNhQ== X-Google-Smtp-Source: AA0mqf57vH7eH0EgjhHeO0swY95W+C1Y3ZmdSizH5TQz6izAu3+swkyEv2pYBUvl1OAfVrla8au2wg== X-Received: by 2002:a05:622a:1cc5:b0:3a4:bc76:969c with SMTP id bc5-20020a05622a1cc500b003a4bc76969cmr17588289qtb.55.1670790148140; Sun, 11 Dec 2022 12:22:28 -0800 (PST) Received: from [192.168.2.100] (pool-72-79-30-4.nwrknj.east.verizon.net. [72.79.30.4]) by smtp.gmail.com with ESMTPSA id x18-20020ac87312000000b003a50c9993e1sm4659194qto.16.2022.12.11.12.22.27 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sun, 11 Dec 2022 12:22:27 -0800 (PST) Content-Type: multipart/alternative; boundary="------------zGGi0dct0ycvzKjIe8b0RwNY" Message-ID: <31247466-cdde-71d4-2beb-ffcf7d7fe4db@gmail.com> Date: Sun, 11 Dec 2022 15:22:26 -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 Content-Language: en-US To: 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> From: Mladen Gogala In-Reply-To: <203270855.2602687.1670583423055@mail.yahoo.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------zGGi0dct0ycvzKjIe8b0RwNY Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 > > > On Fri, Dec 9, 2022 at 4:20 PM, Laurenz Albe > wrote: > On Fri, 2022-12-09 at 08:47 +0000, Bryon Roché wrote: > > > On December 9, 2022 5:02:59 AM UTC, SASIKUMAR Devaraj > 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 --------------zGGi0dct0ycvzKjIe8b0RwNY Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
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 


On Fri, Dec 9, 2022 at 4:20 PM, Laurenz Albe
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
--------------zGGi0dct0ycvzKjIe8b0RwNY--