MIME-Version: 1.0
References: <CAC5iy60Fju3h0qrOS5M70G58fc_d69YTOD20vwr5bWnW1B5AQg@mail.gmail.com>
In-Reply-To: <CAC5iy60Fju3h0qrOS5M70G58fc_d69YTOD20vwr5bWnW1B5AQg@mail.gmail.com>
From: Muhammad Usman Khan <usman.k@bitnine.net>
Date: Fri, 18 Oct 2024 09:02:39 +0500
Message-ID: <CAPnRvGvvtVwHfA+BC0d52-qqYRqqf2RfD7C8YgQhRdTTWOyNxA@mail.gmail.com>
Subject: Re: Performance difference between Primary & Secondary in the query execution
To: Siraj G <tosiraj.g@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000006047310624b86081"
Archived-At: <https://www.postgresql.org/message-id/CAPnRvGvvtVwHfA%2BBC0d52-qqYRqqf2RfD7C8YgQhRdTTWOyNxA%40mail.gmail.com>
Precedence: bulk

--0000000000006047310624b86081
Content-Type: text/plain; charset="UTF-8"

Hi,
You can check and verify the following points:

*Check the cpu resources on both primary and secondary
*Check the execution plans on both the primary and secondary by running
EXPLAIN (ANALYZE, BUFFERS) for the problematic queries like the following
explain (analyze,buffers) select * from test ;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..22.70 rows=1270 width=36) (actual
time=0.013..0.014 rows=1 loops=1)
   Buffers: shared hit=1
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.082 ms
 Execution Time: 0.033 ms
(6 rows)
*check vacuuming and bloating using the following query
select * from pg_stat_user_tables;
select * from pg_stat_all_tables;
*Check the shared_buffers, work_mem, and maintenance_work_mem settings on
both instances.
*Compare the disk I/O performance using the following query
select * from pg_stat_bgwriter;
*Check for any replication lag (pg_stat_replication on the primary) to
ensure the secondary isn't falling behind.

On Thu, 17 Oct 2024 at 19:08, Siraj G <tosiraj.g@gmail.com> wrote:

> Hello Experts!
>
> We have a PgSQL instance running with HA (secondary is being in sync with
> streaming replication). Both the ends, we have same version, but not sure a
> few SQLs behave badly in the secondary:
>
> Primary:
> PostgreSQL 12.20 (Ubuntu 12.20-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu,
> compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit
>
> OS: Ubuntu 20.04.6 LTS \n \l
>
> Secondary:
> ostgreSQL 12.20 (Ubuntu 12.20-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu,
> compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit
>
> OS: Ubuntu 20.04.6 LTS \n \l
>
> The application consumes more data from secondary, hence the server has
> extra vCPUs.
>
> Can you please advise what needs to be checked.
>
> FYI, I am attaching the query with the different execution plans.
>
> Regards
> Siraj
>

--0000000000006047310624b86081
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><div dir=3D"ltr" class=3D"gmail_signature" data-smart=
mail=3D"gmail_signature"><div dir=3D"ltr"><div>Hi,<br>You can check and ver=
ify the following points:<br><br>*Check the cpu resources on both primary a=
nd secondary</div><div>*Check the execution plans on both the primary and s=
econdary by running EXPLAIN (ANALYZE, BUFFERS) for the problematic queries =
like the following</div><div>explain (analyze,buffers) select * from test ;=
<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN<br>------------------------------=
---------------------------------------------------------------------------=
----<br>=C2=A0Seq Scan on test =C2=A0(cost=3D0.00..22.70 rows=3D1270 width=
=3D36) (actual time=3D0.013..0.014 rows=3D1 loops=3D1)<br>=C2=A0 =C2=A0Buff=
ers: shared hit=3D1<br>=C2=A0Planning:<br>=C2=A0 =C2=A0Buffers: shared hit=
=3D1<br>=C2=A0Planning Time: 0.082 ms<br>=C2=A0Execution Time: 0.033 ms<br>=
(6 rows)<br></div><div>*check vacuuming and bloating using the following qu=
ery</div><div>select * from pg_stat_user_tables;<br></div><div>select * fro=
m pg_stat_all_tables;<br></div></div></div></div>*Check the shared_buffers,=
 work_mem, and maintenance_work_mem settings on both instances.<div>*Compar=
e the disk I/O performance using the following query<br>select * from pg_st=
at_bgwriter;<br></div><div>*Check for any replication lag (pg_stat_replicat=
ion on the primary) to ensure the secondary isn&#39;t falling behind.</div>=
</div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">=
On Thu, 17 Oct 2024 at 19:08, Siraj G &lt;<a href=3D"mailto:tosiraj.g@gmail=
.com">tosiraj.g@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmai=
l_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,20=
4,204);padding-left:1ex"><div dir=3D"ltr">Hello Experts!<div><br></div><div=
>We have a PgSQL instance running with HA (secondary is being in sync with =
streaming replication). Both the ends, we have same version, but not sure a=
 few SQLs behave badly in the secondary:</div><div><br></div><div>Primary:<=
/div><div>PostgreSQL 12.20 (Ubuntu 12.20-0ubuntu0.20.04.1) on x86_64-pc-lin=
ux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit</div>=
<div><br></div><div>OS:=C2=A0Ubuntu 20.04.6 LTS \n \l</div><div><br><div>Se=
condary:</div><div>ostgreSQL 12.20 (Ubuntu 12.20-0ubuntu0.20.04.1) on x86_6=
4-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-b=
it<br></div><div><br></div></div><div>OS:=C2=A0Ubuntu 20.04.6 LTS \n \l</di=
v><div><br></div><div>The application consumes more data from secondary, he=
nce the server has extra vCPUs.</div><div><br></div><div>Can you please adv=
ise what needs to be checked.</div><div><br></div><div>FYI, I am attaching =
the query with the different execution plans.</div><div><br></div><div>Rega=
rds</div><div>Siraj</div></div>
</blockquote></div>

--0000000000006047310624b86081--