Обсуждение: 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