Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dUsdD-0002CS-Lf for pgsql-performance@arkaria.postgresql.org; Tue, 11 Jul 2017 10:43:03 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dUsdD-0002KT-8D for pgsql-performance@arkaria.postgresql.org; Tue, 11 Jul 2017 10:43:03 +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 1dUsdB-0002J2-VK for pgsql-performance@postgresql.org; Tue, 11 Jul 2017 10:43:02 +0000 Received: from mail-qk0-x232.google.com ([2607:f8b0:400d:c09::232]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dUsd7-0006Lk-AV for pgsql-performance@postgresql.org; Tue, 11 Jul 2017 10:43:01 +0000 Received: by mail-qk0-x232.google.com with SMTP id d78so97485321qkb.1 for ; Tue, 11 Jul 2017 03:42:56 -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=qvB4lgNy4Jib/e4mcI2PFzGQ38q4LVIr3JQLq1XRCeU=; b=Lwv73EsMH4YbWzoWCg8OYrXPJRIzTH+71WNQgrWk7nY5CwIyYFLEvIT9+MqpoS3vfm AoAQh5TSwaxwVyL7FdsNcGfSCOFy2v357C/QIdc7w+h7A6/AbEr+HVrhIYT5FEY9uqcz Nb3h7F+27mwCgW3r2qqafvXPcZkfH2eC1H3Dbw7GaEv0a6Nj1oXkgRWeesBPZcHUALsq bcz0s3cZCId0b+50r7kihvfKNiUSCenATsnWX00muFsNP1v2eVDwWmU+ZIIeqqSFWHxY R3jM+SSkLdT4Y6wQ151XKWhxhtKb9w6OEyKjXvK1ER0vy9A7HzvwyL5svNIsM23lonYn V35Q== 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=qvB4lgNy4Jib/e4mcI2PFzGQ38q4LVIr3JQLq1XRCeU=; b=m30H3PbLP53hUu039YKvhXqXNGfRSagBi9WaymJSMtpW5gLg11PEF+2DGIjdAW8Y/0 baawdnVWvC5wUhlbtUKPHUghtpTamBq7jbPg0fDczJUY8YpFS/hKZ3dt0aEY85AFL8GZ CxKYzDzrXEXFQvjYPQH8bdrRjl4H+CF3d6I1QYhkcWg/v+Fu4G9x5Q7qfeSKuKVQ1iXA 0gFgRnjV4+k5vpouxdxAevYwkvkqckT2V1S1+6oRpzcpDt6qmcjCSFeou1eKj7j0a92k /vY6V/IvObAN7iZ3IXpy1NZneAfjUm4nXypvqcMvunMZi7Fh3e4vbWSQVDv9X6xotyXv sw7A== X-Gm-Message-State: AIVw110/OpeepB44Gv94kLv/d4XhHz24K9kmZFLtiPaBqfbXjyM4Wc0x TWRHVb2xOpnU3zDVAyuLCMiu6bFWo4PP X-Received: by 10.55.107.134 with SMTP id g128mr8799748qkc.166.1499769775684; Tue, 11 Jul 2017 03:42:55 -0700 (PDT) MIME-Version: 1.0 Received: by 10.12.157.11 with HTTP; Tue, 11 Jul 2017 03:42:35 -0700 (PDT) In-Reply-To: References: From: Charles Nadeau Date: Tue, 11 Jul 2017 12:42:35 +0200 Message-ID: Subject: Re: Very poor read performance, query independent To: Igor Neyman Cc: Andreas Kretschmer , "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="001a114fe738b982590554085cd8" 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 --001a114fe738b982590554085cd8 Content-Type: text/plain; charset="UTF-8" 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 wrote: > > > *From:* pgsql-performance-owner@postgresql.org [mailto:pgsql-performance- > owner@postgresql.org] *On Behalf Of *Charles Nadeau > *Sent:* Monday, July 10, 2017 11:48 AM > *To:* Andreas Kretschmer > *Cc:* pgsql-performance@postgresql.org > *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 < > andreas@a-kretschmer.de> 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/ --001a114fe738b982590554085cd8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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 equ= al to 64GB?
Thanks for suggestions!

Char= les

On= Mon, Jul 10, 2017 at 8:35 PM, Igor Neyman <ineyman@perceptron.com> wrote:

=C2=A0

From: pgsql-performance-owner@= postgresql.org [mailto:pgsql-performance-owner@postgresql.org= ] On Behalf Of Charles Nadeau
Sent: Monday, July 10, 2017 11:48 AM
To: Andreas Kretschmer <andreas@a-kretschmer.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Very poor read performance, query independent=

=C2=A0

Andreas,

=C2=A0

Because the ratio between the Sequential IOPS and Ra= ndom IOPS is about 29. Taking into account that part of the data is in RAM,= I obtained an "effective" ratio of about 22.

Thanks!

=C2=A0

Charles

=C2=A0

On Mon, Jul 10, 2017 at 5:35 PM, Andreas Kretschmer = <andreas@a-= kretschmer.de> 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 Su= pport Company.
www.2ndQuadrant.com


--

Charles Nadeau Ph.D.
http://cha= rlesnadeau.blogspot.com/

= =C2=A0

=C2=A0

Considering RAM size of 72 GB and you= r 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 =3D 22 looks extremely high, probab= ly it completely precludes index usage in your queries.

=C2=A0

You should try this setting at least = at its default value: random_page_cost =3D4, and probably go even lower.=

Also, effective_cache_size is at leas= t as big as your shared_buffers. Having 72GB RAM t effective_cache_size should be set around 64GB (again consid= ering that Postgres is the only app running on the server).

=C2=A0

Regards,

Igor Neyman

=C2=A0

=C2=A0

=C2=A0

=C2=A0




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