Postgres Pro
Downloads
Home   >   mailing lists

Re: Performance difference between Primary & Secondary in the query execution - Mailing list pgsql-general

From Muhammad Usman Khan
Subject Re: Performance difference between Primary & Secondary in the query execution
Date
Msg-id CAPnRvGvvtVwHfA+BC0d52-qqYRqqf2RfD7C8YgQhRdTTWOyNxA@mail.gmail.com
Whole thread Raw
List pgsql-general
Tree view
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

pgsql-general by date:

Previous
From: Venkata Nori
Date:
Subject: Postgres installation is failing in mac
Next
From: Daniel Gustafsson
Date:
Subject: Re: Postgres installation is failing in mac