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 <tosira...@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
>
