public inbox for [email protected]
help / color / mirror / Atom feedFrom: Muhammad Usman Khan <[email protected]>
To: Siraj G <[email protected]>
Cc: [email protected]
Subject: Re: Performance difference between Primary & Secondary in the query execution
Date: Fri, 18 Oct 2024 09:02:39 +0500
Message-ID: <CAPnRvGvvtVwHfA+BC0d52-qqYRqqf2RfD7C8YgQhRdTTWOyNxA@mail.gmail.com> (raw)
In-Reply-To: <CAC5iy60Fju3h0qrOS5M70G58fc_d69YTOD20vwr5bWnW1B5AQg@mail.gmail.com>
References: <CAC5iy60Fju3h0qrOS5M70G58fc_d69YTOD20vwr5bWnW1B5AQg@mail.gmail.com>
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 <[email protected]> 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
>
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Performance difference between Primary & Secondary in the query execution
In-Reply-To: <CAPnRvGvvtVwHfA+BC0d52-qqYRqqf2RfD7C8YgQhRdTTWOyNxA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox