Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hnoXz-0002TX-0i for pgsql-admin@arkaria.postgresql.org; Wed, 17 Jul 2019 18:20:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hnoXx-000400-SG for pgsql-admin@arkaria.postgresql.org; Wed, 17 Jul 2019 18:20:57 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hnoXx-0003zl-F1 for pgsql-admin@lists.postgresql.org; Wed, 17 Jul 2019 18:20:57 +0000 Received: from mout.perfora.net ([74.208.4.194]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1hnoXr-0007dh-Dp for pgsql-admin@postgresql.org; Wed, 17 Jul 2019 18:20:56 +0000 Received: from [192.168.1.101] ([98.169.208.73]) by mrelay.perfora.net (mreueus004 [74.208.5.2]) with ESMTPSA (Nemesis) id 1M424C-1hnoXn0XZ2-0006q5; Wed, 17 Jul 2019 20:20:47 +0200 Subject: Re: Performance tuning/Response times in Postgres 11.2 To: Srikar Vankadaru Cc: 'pgsql-admin' References: <005201d53cc0$1da752b0$58f5f810$@opsveda.com> From: MichaelDBA Message-ID: Date: Wed, 17 Jul 2019 14:20:43 -0400 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 PostboxApp/6.1.18 MIME-Version: 1.0 In-Reply-To: <005201d53cc0$1da752b0$58f5f810$@opsveda.com> Content-Type: multipart/alternative; boundary="------------BFEAB314C929F21E9ED9363B" Content-Language: en-US X-Provags-ID: V03:K1:lJdII4EjML7yJ4rQhorCwNBd3VxpDSmIePOQlx4sa7eH2cQ9JON +Opvz9u5h4DhsPLacS+fw8qhOgH80EImVyNr5QOwU9GVALxEfV7ZboAUZHABhKmHwPGMUcL N0HSbnCktLJD48vzF0ekUDpQpkd5ouPmzZ8iEGe2Oliz5z8omwLQ/qReZp0DuJmptNX+iI/ sPo+6n9m/S6RKEFLaUHfw== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:KNR/W0j8i2E=:B4qAyyohgY4ral59Ss3zsM 9eebUgky3TXiRhe7x5dIxBrVu3nalz45fv2LGR8qZ4smMom1bkc61V1OW3gHPRxNZ1bUE0sKB 4ircv3VE4YqFqcaTJiUnEokihNS1SAxFgbAxwPNq5HeG1v1QxtousexxQTnQ4pxpzXtxt68iL e/HmvRHRYSKw6EqsjH4fVKpmJBjJ42M0Y8muu1lTGP7hgBHBl0snpDXBbdUZ64Lgs3NiQ4b8Q mqNgfOHpe+adi1R0CN83AA5HpCBGy6CaBF2MPMrDoqP6ZAs8sLLXKlZ14miw3xMh5hDJ/KqPD I8oZ50/2j02fC2hqMTgii3a0JiHiZAIwewQbCVPv99c0iNU1/+PFT23s5PjHAEZ67u30BI3nv sCVop587ZwC+uKCnXEKlhiqlmpj0Ce9aI0glg9pBYRD84vUtg26Y2zHOs/gKp4PIFZuklxzpU 6AIybvYEhPg4Lu1SOwIzLQiD/QDvQtXCG+KFvxpCtexBqEuFGvLF4tlu+QhY3wVhYVNRUslEl zfFy8lWj2Gf/MAkxUKZj4JTo7U4W5wHcXhCTU+IPkcfvSySb1bqb19s/FXe+f/eW54semF2Ax sqMbfJp8j9aTc98uJQRFbaXMn7iY7feYi7SOgcE49QaSH7Rezh20d2dM3laBWFPgjXz9EeZMF iR5VeJqK0ThV1WYjoRKlwuXePy75sNc9Y4z9Zj0i0tMbO7RtrPUlrVGPgSHpGifBEB1zFRxR5 xz0dUXzRdodddYmCSZBdfa17/XEblF9zayAzgaEPnpQ56DzzxJ0Rf7Wzv9o= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------BFEAB314C929F21E9ED9363B Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 8bit 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 > --------------BFEAB314C929F21E9ED9363B Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: 8bit 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


--------------BFEAB314C929F21E9ED9363B--