Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dVBzb-0003fM-Al for pgsql-performance@arkaria.postgresql.org; Wed, 12 Jul 2017 07:23:27 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dVBza-0007AH-9q for pgsql-performance@arkaria.postgresql.org; Wed, 12 Jul 2017 07:23:26 +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_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dVBxn-000426-6u for pgsql-performance@postgresql.org; Wed, 12 Jul 2017 07:21:35 +0000 Received: from mail-qt0-x229.google.com ([2607:f8b0:400d:c0d::229]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dVBxj-0007bR-5c for pgsql-performance@postgresql.org; Wed, 12 Jul 2017 07:21:34 +0000 Received: by mail-qt0-x229.google.com with SMTP id 32so11777334qtv.1 for ; Wed, 12 Jul 2017 00:21:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=m/0EVmD0yj9O4LSRz4KwcreDB/n5BgPtE8XC9fqg3oo=; b=uBQJX/wkCO0g5hhu8IKGMqS8abSl1CDmMHuoxtJViJuLajWPT3npTM0pT/cq6a85it Lm1v9e/C33h2lyG/BWakCiyOi+624OTX0A8xgvS8hjNk4j+Sgfkva98dqBrAOIj7bUGN RYLyAnQG7erl+p9EsS0XyjRE9IdD87F1eDGOOWptJ2cdrLvcqfR3Z8SFXo5cijVf9CWG XPVykjlXPGgKPOGW+iYE9GmSBgUUTpxlkMmX7b2loAoVGeMbjLkvOlbKvmOdWVFEJYpE W2pjZLEGBwuDd65jMmNKmqb9dYSVL+XGrapYs1raKTtpR1ATKOjGQx/UqIkwZsjxiWQk TAtQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=m/0EVmD0yj9O4LSRz4KwcreDB/n5BgPtE8XC9fqg3oo=; b=BHLD4Q/dKz96zt14dUeQ65HBP9hBJYf8jGwamjxeBgyOE0chmqn65A/rosfVHei7Zp g3KQezY3YLLK4lREKsJVfXczR1YFC3hXFa7sCJd4xnE0WOQrT0B5eAo0nXYsEPun3qa3 vjneTFcWXPh4P3bE3PQ/gTpDiHE9+F0ZYb2A8Ui2KqvR2BHLid47WKeByVjYSn7ohRxK /wlby5Bh3J2Fl1CFhwpLVynlc0e3GQSmb1Kh+Z+IrFqs+VOW3UPp764O11KC7p3YcCzZ wfKjFYAZhjSOpaWJnC7d9qvBsYITCVWQTdslbzAaK21FflKN0VyGQH6t3tVmxSYJHe0F xPcw== X-Gm-Message-State: AIVw111PH/XQ8zIVm+U9BohWH9T3KbjL+LyMsnLTgP6ExIm+F6f5Mjqr lKDfhBDtS/b4JeHyV26JGgGlyvEfeA== X-Received: by 10.237.39.135 with SMTP id a7mr4578834qtd.164.1499844089693; Wed, 12 Jul 2017 00:21:29 -0700 (PDT) MIME-Version: 1.0 Received: by 10.12.157.11 with HTTP; Wed, 12 Jul 2017 00:21:09 -0700 (PDT) In-Reply-To: References: From: Charles Nadeau Date: Wed, 12 Jul 2017 09:21:09 +0200 Message-ID: Subject: Re: Very poor read performance, query independent To: Igor Neyman Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="f403045ef9062f4d3e055419aae6" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --f403045ef9062f4d3e055419aae6 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Igor, I set shared_buffers to 24 GB and effective_cache_size to 64GB and I can see that the queries are faster due to the fact that the index are used more often. Knowing I have 72GB of RAM and the server is exclusively dedicated to Postgresql, what could be the maximum value for effective_cache? Thanks! Charles On Tue, Jul 11, 2017 at 5:16 PM, Igor Neyman wrote= : > > > *From:* pgsql-performance-owner@postgresql.org [mailto:pgsql-performance- > owner@postgresql.org] *On Behalf Of *Igor Neyman > *Sent:* Tuesday, July 11, 2017 10:34 AM > *To:* Charles Nadeau > *Cc:* pgsql-performance@postgresql.org > *Subject:* Re: [PERFORM] Very poor read performance, query independent > > > > *From:* Charles Nadeau [mailto:charles.nadeau@gmail.com > ] > *Sent:* Tuesday, July 11, 2017 6:43 AM > *To:* Igor Neyman > *Cc:* Andreas Kretschmer ; > pgsql-performance@postgresql.org > *Subject:* Re: [PERFORM] Very poor read performance, query independent > > > > Igor, > > > > I reduced the value of random_page_cost to 4 but the read speed remains > low. > > Regarding effective_cache_size and shared_buffer, do you mean they should > be both equal to 64GB? > > Thanks for suggestions! > > > > Charles > > > > No, they should not be equal. > > From the docs: > > > > effective_cache_size (integer) > > Sets the planner's assumption about the effective size of the disk cache > that is available to a single query. This is factored into estimates of t= he > cost of using an index; a higher value makes it more likely index scans > will be used, a lower value makes it more likely sequential scans will be > used. When setting this parameter you should consider both PostgreSQL's > shared buffers and the portion of the kernel's disk cache that will be us= ed > for PostgreSQL data files. Also, take into account the expected number of > concurrent queries on different tables, since they will have to share the > available space. This parameter has no effect on the size of shared memor= y > allocated by PostgreSQL, nor does it reserve kernel disk cache; it is use= d > only for estimation purposes. The system also does not assume data remain= s > in the disk cache between queries. The default is 4 gigabytes (4GB). > > So, I=E2=80=99d set shared_buffers at 24GB and effective_cache_size at 64= GB. > > > > Regards, > > Igor > > > > Also, maybe it=E2=80=99s time to look at execution plans (explain analyze= ) of > specific slow queries, instead of trying to solve the problem =E2=80=9Cin= general=E2=80=9D. > > > > Igor > > > --=20 Charles Nadeau Ph.D. http://charlesnadeau.blogspot.com/ --f403045ef9062f4d3e055419aae6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Igor,

I set shared_buffers t= o 24 GB and effective_cache_size to 64GB and I can see that the queries are= faster due to the fact that the index are used more often. Knowing I have = 72GB of RAM and the server is exclusively dedicated to Postgresql, what cou= ld be the maximum value for effective_cache?
Thanks!
Charles

On Tue, Jul 11, 2017 at 5:16 PM, Igor Neyman <ineyma= n@perceptron.com> wrote:

=C2=A0

From: pgsql-performance-owner@= postgresql.org [mailto:pgsql-performance-owner@postgresql.org= ] On Behalf Of Igor Neyman
Sent: Tuesday, July 11, 2017 10:34 AM
To: Charles Nadeau <charles.nadeau@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Very poor read performance, query independent=

=C2=A0

From: Charles Nadeau [mailto:charles.nadeau@gmail= .com]
Sent: Tuesday, July 11, 2017 6:43 AM
To: Igor Neyman <ineyman@perceptron.com>
Cc: Andreas Kretschmer <andreas@a-kretschmer.de>; pgsql= -performance@postgresql.org
Subject: Re: [PERFORM] Very poor read performance, query independent=

=C2=A0

Igor,

=C2=A0

I reduced the value of random_page_cost to 4 but the= read speed remains low.

Regarding effective_cache_size and shared_buffer, do= you mean they should be both equal to 64GB?

Thanks for suggestions!

=C2=A0

Charles

=C2=A0

No, they should not be equal.<= u>

From the docs:

=C2=A0

effective_cache_size (integer)

Sets the planner's assumption about the effective size of the disk cach= e that is available to a single query. This is factored into estimates of t= he cost of using an index; a higher value makes it more likely index scans = will be used, a lower value makes it more likely sequential scans will be used. When setting this parameter you= should consider both PostgreSQL's shared buffers and the portion of th= e kernel's disk cache that will be used for PostgreSQL data files. Also= , take into account the expected number of concurrent queries on different tables, since they will have to share t= he available space. This parameter has no effect on the size of shared memo= ry allocated by PostgreSQL, nor does it reserve kernel disk cache; it is us= ed only for estimation purposes. The system also does not assume data remains in the disk cache between que= ries. The default is 4 gigabytes (4GB).

So, I=E2=80=99d set shared_buffers at= 24GB and effective_cache_size at 64GB.

=C2=A0

Regards,

Igo= r

=C2=A0

Also, maybe it=E2=80=99s time = to look at execution plans (explain analyze) of specific slow queries, inst= ead of trying to solve the problem =E2=80=9Cin general=E2=80=9D.

=

=C2=A0

Igor

=C2=A0




--
Charles Nadeau Ph.D= .
http:= //charlesnadeau.blogspot.com/
--f403045ef9062f4d3e055419aae6--