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 1hnnBB-0006xz-Gv for pgsql-admin@arkaria.postgresql.org; Wed, 17 Jul 2019 16:53:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hnnB9-0001jQ-Ux for pgsql-admin@arkaria.postgresql.org; Wed, 17 Jul 2019 16:53:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hnnB9-0001jJ-81 for pgsql-admin@lists.postgresql.org; Wed, 17 Jul 2019 16:53:19 +0000 Received: from mail-pl1-x629.google.com ([2607:f8b0:4864:20::629]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hnnB5-0003Jb-DN for pgsql-admin@postgresql.org; Wed, 17 Jul 2019 16:53:18 +0000 Received: by mail-pl1-x629.google.com with SMTP id y8so12265623plr.12 for ; Wed, 17 Jul 2019 09:53:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=opsveda.com; s=ov; h=from:to:subject:date:message-id:mime-version:thread-index :content-language; bh=tOZF45+xsLu0eoWnMNyyOFhS4jxXjV/5YoylH5kYEFU=; b=JIoLmWyFto4uIBsqUTwpojmr1Rh9fT5IntpZnYmvvVzzN2BxtHbq75n4ZMNJN5p5TI wICikbBN3bz6vqQYwU0hpsZp00pzbQuOpRUyIzbIe06RAvoNVk+/t3Zul2niiMw4BnLL apL3dN7ZR+DpwxmsC0hosV2B4Bn7fBJGpJUD8= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:subject:date:message-id:mime-version :thread-index:content-language; bh=tOZF45+xsLu0eoWnMNyyOFhS4jxXjV/5YoylH5kYEFU=; b=IbRSbsfrUXeg9hW4Jm88NYynqBvGbpq/ai7u36mKqu75y0bSiFbswsgfGs3GiH6FI0 QdFH2epaA5YKrDqO0x0SHNMp+GEeOnFgrnIzthMdBgTwMeJnFoMNihZj75dRbOqXfCSM hhyvbopo+6tHsslaCCGryTsOkhsul4j6C3molX4EvFut5GybdrvKu7JsMJ/0YPegvYUQ vJT36U7fHyoFsGYoN2fp/MWEPJOCx/hq3ojcq9sBUttizhGhvbPT9KLDKB+ixSkxk4Np uf/goBZ8BP/y0EX2EEA0B2DiYvAMDwc3otrD8fNVa6oU0wNX1qaWckO0d+fpSCmVEE8s QRTw== X-Gm-Message-State: APjAAAUqJ6raX2ExMvP4jHyCjvrlcKxOV77pI7gicH4q8jo39lRjwcXD 1iHR2sPE+StpRJzDRJQ7RbQNVG4pOF0= X-Google-Smtp-Source: APXvYqyC38ASyYREoO8ldxwxYXrJojK6bjVfqoVNX/UTa0nP/vrSv6BaGD0pjUjGP73TNm3l57dD6g== X-Received: by 2002:a17:902:b789:: with SMTP id e9mr43273269pls.294.1563382391998; Wed, 17 Jul 2019 09:53:11 -0700 (PDT) Received: from OVALL142 ([171.61.101.201]) by smtp.gmail.com with ESMTPSA id v13sm30100473pfn.109.2019.07.17.09.53.10 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 17 Jul 2019 09:53:11 -0700 (PDT) From: "Srikar Vankadaru" To: "'pgsql-admin'" Subject: Performance tuning/Response times in Postgres 11.2 Date: Wed, 17 Jul 2019 22:23:06 +0530 Message-ID: <005201d53cc0$1da752b0$58f5f810$@opsveda.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0053_01D53CEE.37616370" X-Mailer: Microsoft Outlook 15.0 Thread-Index: AdU8vwUzwsLCCQzpTSSFG588097kQg== Content-Language: en-us List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0053_01D53CEE.37616370 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_0053_01D53CEE.37616370 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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=3D14GB

effective_cache_size=3D50GB

random_page_cost=3D1.2

work_mem=3D128MB

maintenance_work_mem=3D512MB

 

Now I have = upgraded the machine to 72 vCPU and 144 GB memory with below = parameters:

 

shared_buffer=3D36GB

effective_cache_size=3D108GB

random_page_cost=3D1.2

work_mem=3D256MB

maintenance_work_mem=3D1024MB

 

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

------=_NextPart_000_0053_01D53CEE.37616370--