Re: Performance tuning/Response times in Postgres 11.2

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
>