public inbox for [email protected]
help / color / mirror / Atom feedFrom: Igor Neyman <[email protected]>
To: Charles Nadeau <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Very poor read performance, query independent
Date: Tue, 11 Jul 2017 15:16:52 +0000
Message-ID: <DM5PR07MB2810B33E4FB7E55B60832597DAAE0@DM5PR07MB2810.namprd07.prod.outlook.com> (raw)
In-Reply-To: <DM5PR07MB281050555F2D7BFDD66A37BADAAE0@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>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
From: [email protected] [mailto:[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]]
Sent: Tuesday, July 11, 2017 6:43 AM
To: Igor Neyman <[email protected]<mailto:[email protected]>>
Cc: Andreas Kretschmer <[email protected]<mailto:[email protected]>>; [email protected]<mailto:[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
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: <DM5PR07MB2810B33E4FB7E55B60832597DAAE0@DM5PR07MB2810.namprd07.prod.outlook.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