Re: Very poor read performance, query independent

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Charles Nadeau <[email protected]>
To: Igor Neyman <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Very poor read performance, query independent
Date: Wed, 12 Jul 2017 09:21:09 +0200
Message-ID: <CADFyZw6+EEovy7wagTQSdUW4Mk4cbj-Yo+K9MS+Vn2i-HQys1g@mail.gmail.com> (raw)
In-Reply-To: <DM5PR07MB2810B33E4FB7E55B60832597DAAE0@DM5PR07MB2810.namprd07.prod.outlook.com>
References: <CADFyZw7aGoD0AaStxdyHByR5Qta=M5wx0v=iptKLhPUp+EOKvA@mail.gmail.com>
	<[email protected]>
	<CADFyZw4UanW5TbFajWKWhN9XcW+8gtCXw+kssHo47Wpr1A=zJw@mail.gmail.com>
	<DM5PR07MB28103FD558CB6628CECE07BEDAA90@DM5PR07MB2810.namprd07.prod.outlook.com>
	<CADFyZw5ucD=KNrmnxz23NBW5_YJXW731E4LDp=+LjycjnmBRNw@mail.gmail.com>
	<DM5PR07MB281050555F2D7BFDD66A37BADAAE0@DM5PR07MB2810.namprd07.prod.outlook.com>
	<DM5PR07MB2810B33E4FB7E55B60832597DAAE0@DM5PR07MB2810.namprd07.prod.outlook.com>
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>

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 <[email protected]> wrote:

>
>
> *From:* [email protected] [mailto:pgsql-performance-
> [email protected]] *On Behalf Of *Igor Neyman
> *Sent:* Tuesday, July 11, 2017 10:34 AM
> *To:* Charles Nadeau <[email protected]>
> *Cc:* [email protected]
> *Subject:* Re: [PERFORM] Very poor read performance, query independent
>
>
>
> *From:* Charles Nadeau [mailto:[email protected]
> <[email protected]>]
> *Sent:* Tuesday, July 11, 2017 6:43 AM
> *To:* Igor Neyman <[email protected]>
> *Cc:* Andreas Kretschmer <[email protected]>;
> [email protected]
> *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 the
> 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 used
> 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 memory
> allocated by PostgreSQL, nor does it reserve kernel disk cache; it is used
> only for estimation purposes. The system also does not assume data remains
> in the disk cache between queries. The default is 4 gigabytes (4GB).
>
> So, I’d set shared_buffers at 24GB and effective_cache_size at 64GB.
>
>
>
> Regards,
>
> Igor
>
>
>
> Also, maybe it’s time to look at execution plans (explain analyze) of
> specific slow queries, instead of trying to solve the problem “in general”.
>
>
>
> Igor
>
>
>



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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Very poor read performance, query independent
  In-Reply-To: <CADFyZw6+EEovy7wagTQSdUW4Mk4cbj-Yo+K9MS+Vn2i-HQys1g@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox