public inbox for [email protected]
help / color / mirror / Atom feedFrom: Charles Nadeau <[email protected]>
To: Igor Neyman <[email protected]>
Cc: Andreas Kretschmer <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Very poor read performance, query independent
Date: Tue, 11 Jul 2017 12:42:35 +0200
Message-ID: <CADFyZw5ucD=KNrmnxz23NBW5_YJXW731E4LDp=+LjycjnmBRNw@mail.gmail.com> (raw)
In-Reply-To: <DM5PR07MB28103FD558CB6628CECE07BEDAA90@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>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
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
On Mon, Jul 10, 2017 at 8:35 PM, Igor Neyman <[email protected]> wrote:
>
>
> *From:* [email protected] [mailto:pgsql-performance-
> [email protected]] *On Behalf Of *Charles Nadeau
> *Sent:* Monday, July 10, 2017 11:48 AM
> *To:* Andreas Kretschmer <[email protected]>
> *Cc:* [email protected]
> *Subject:* Re: [PERFORM] Very poor read performance, query independent
>
>
>
> Andreas,
>
>
>
> Because the ratio between the Sequential IOPS and Random IOPS is about 29.
> Taking into account that part of the data is in RAM, I obtained an
> "effective" ratio of about 22.
>
> Thanks!
>
>
>
> Charles
>
>
>
> On Mon, Jul 10, 2017 at 5:35 PM, Andreas Kretschmer <
> [email protected]> wrote:
>
>
>
> Am 10.07.2017 um 16:03 schrieb Charles Nadeau:
>
> random_page_cost | 22
>
>
>
> why such a high value for random_page_cost?
>
> Regards, Andreas
>
> --
> 2ndQuadrant - The PostgreSQL Support Company.
> www.2ndQuadrant.com
>
>
> --
>
> Charles Nadeau Ph.D.
> http://charlesnadeau.blogspot.com/
>
>
>
>
>
> Considering RAM size of 72 GB and your database size of ~225GB, and also
> the fact that Postgres is the only app running on the server, probably 1/3
> of your database resides in memory, so random_page_cost = 22 looks
> extremely high, probably it completely precludes index usage in your
> queries.
>
>
>
> You should try this setting at least at its default value:
> random_page_cost =4, and probably go even lower.
>
> Also, effective_cache_size is at least as big as your shared_buffers.
> Having 72GB RAM t effective_cache_size should be set around 64GB (again
> considering that Postgres is the only app running on the server).
>
>
>
> Regards,
>
> Igor Neyman
>
>
>
>
>
>
>
>
>
--
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], [email protected]
Subject: Re: Very poor read performance, query independent
In-Reply-To: <CADFyZw5ucD=KNrmnxz23NBW5_YJXW731E4LDp=+LjycjnmBRNw@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