pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance tuning/Response times in Postgres 11.2
2+ messages / 2 participants
[nested] [flat]
* Performance tuning/Response times in Postgres 11.2
@ 2019-07-17 16:53 Srikar Vankadaru <srikar@opsveda.com>
0 siblings, 1 reply; 2+ messages in thread
From: Srikar Vankadaru @ 2019-07-17 16:53 UTC (permalink / raw)
To: pgsql-admin
Hello Experts,
We are running our application on PostgreSQL version 11.2 on Centos 7 OS.
Have updated below performance related parameters for a 36 vCPU and 72 GB
memory instance and recorded response times.
shared_buffer=14GB
effective_cache_size=50GB
random_page_cost=1.2
work_mem=128MB
maintenance_work_mem=512MB
Now I have upgraded the machine to 72 vCPU and 144 GB memory with below
parameters:
shared_buffer=36GB
effective_cache_size=108GB
random_page_cost=1.2
work_mem=256MB
maintenance_work_mem=1024MB
And performed the same testing and to my surprise the response times are
almost similar and there is no improvement even I am running on the bigger
machine.
I want to understand if I am missing any basic settings that is causing the
slow response times ? Please shed some light here.
Thanks,
Srikar
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Performance tuning/Response times in Postgres 11.2
@ 2019-07-17 18:20 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Srikar Vankadaru <srikar@opsveda.com>
0 siblings, 0 replies; 2+ messages in thread
From: MichaelDBA @ 2019-07-17 18:20 UTC (permalink / raw)
To: Srikar Vankadaru <srikar@opsveda.com>; +Cc: pgsql-admin
Hi Srikar,
Well, you haven't supplied much information about "what" is slow or the
same, just "application testing". More memory and more CPU power might
not affect much at all depending on your resource consumption during
your normal SQL workload. Memory might have been sufficient both before
and after upgrading, and you shouldn't assume that you should increase
shared_buffers automatically just because you have more memory
available. You could actually start another problem by increasing
shared_buffers too much: double-buffering. With regard to more CPUs,
unless you are also increasing the number of parallel workers, more CPU
power might not change anything either since each connection has one CPU
binding, and if your CPU load is low, more CPUs won't change much
either. Turn on "log_temp_files". If you see queries spilling over to
disk, increase work_mem further from 256MB, not too much at one time
though or you could exceed memory availability and start gettting malloc
errors in PG log file.
Upgrade your minor version soon since a major security patch was pushed
out recently.
Regards,
Michael Vitale
I recommend that you zero in on any problematic queries that are slow so
that you/we can do further analysis.
Srikar Vankadaru wrote on 7/17/2019 12:53 PM:
>
> Hello Experts,
>
> We are running our application on PostgreSQL version 11.2 on Centos 7 OS.
>
> Have updated below performance related parameters for a 36 vCPU and 72
> GB memory instance and recorded response times.
>
> shared_buffer=14GB
>
> effective_cache_size=50GB
>
> random_page_cost=1.2
>
> work_mem=128MB
>
> maintenance_work_mem=512MB
>
> Now I have upgraded the machine to 72 vCPU and 144 GB memory with
> below parameters:
>
> shared_buffer=36GB
>
> effective_cache_size=108GB
>
> random_page_cost=1.2
>
> work_mem=256MB
>
> maintenance_work_mem=1024MB
>
> And performed the same testing and to my surprise the response times
> are almost similar and there is no improvement even I am running on
> the bigger machine.
>
> I want to understand if I am missing any basic settings that is
> causing the slow response times ? Please shed some light here.
>
> Thanks,
>
> Srikar
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2019-07-17 18:20 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-07-17 16:53 Performance tuning/Response times in Postgres 11.2 Srikar Vankadaru <srikar@opsveda.com>
2019-07-17 18:20 ` MichaelDBA <MichaelDBA@sqlexec.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox