Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dUuaS-0001tD-Sk for pgsql-performance@arkaria.postgresql.org; Tue, 11 Jul 2017 12:48:21 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dUuaR-0002Zp-6A for pgsql-performance@arkaria.postgresql.org; Tue, 11 Jul 2017 12:48:19 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dUuYf-0007vA-20 for pgsql-performance@postgresql.org; Tue, 11 Jul 2017 12:46:29 +0000 Received: from mail-qk0-x22c.google.com ([2607:f8b0:400d:c09::22c]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dUuYb-0001wd-CE for pgsql-performance@postgresql.org; Tue, 11 Jul 2017 12:46:27 +0000 Received: by mail-qk0-x22c.google.com with SMTP id v143so102623126qkb.0 for ; Tue, 11 Jul 2017 05:46:25 -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=SrFbGV0JuEfAlo6ly4SYptEsJC8La2cEh2DZa6yvJ7c=; b=pzW0pTebyvb01RN6riAM2SHIadd90/Uy3IBXmRaMCWFd7iY51Lyw6cunbibB1dbfNA Bom/yzJsQhh5pPQKhVd6YmsByyrZndm8RomX+y/j/S4iPr0eCpDHRnp5B+dEE6MJymzI vcGfw6ATUr06YMOHI3fk+J5lTUI3urUXV1bZwbOFDtF3ryQXJ52ghFImmlKxKAYgBCYl GM6K4Cfwer98Hg/CWN6jYxdylEPHoOzLKbeiGebRMYEzVoJmtm2QSxuVVMf4ks0s04Nv PDwStA9obg5WPK2IhTEXVNY/Suuplg09EnuKTOOyrWXABHRBIu7ecWgouaxLBjIzDEar uwYg== 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=SrFbGV0JuEfAlo6ly4SYptEsJC8La2cEh2DZa6yvJ7c=; b=jXg7FdvBfyfPJUikESFfZabxnbilH6UESO4bGn+SNdX1BjPyqX6so0ClCySwjz0HOj 4xz8IOXI+8Vh79HdCcFO/JiuVJZFyCm4PhSnt5488f7TVRvdNmIVunid7EAjsf61pCvt O4Ikq+1u/+i/v1X3P+N0iaWv+sOcBQZM3hEcoRHqHDFBnsgEXWXe5Y6BRbP1Xy5rp1Z+ mAGlD50omdoyLiKY+d1b4qRJNIOFXCwfULu4wFtmPPiHim97NzWWqii3x8RVfZIdU+Gp LAaPDhfHLMqtXmb1ZHL4SQjGrZ87hxDmjkSfx5w7By5IcnMfauaVtGIeto+CQPiFommk gRSQ== X-Gm-Message-State: AIVw110t2MKYavSUlpL9p7b15YspYj1gmMxrIzeDwdc3+W7wqoXftrxR BLHx0K/pQl5iddq0W7jaTE8gUBdY8Q== X-Received: by 10.55.107.134 with SMTP id g128mr9406035qkc.166.1499777184323; Tue, 11 Jul 2017 05:46:24 -0700 (PDT) MIME-Version: 1.0 Received: by 10.12.157.11 with HTTP; Tue, 11 Jul 2017 05:46:03 -0700 (PDT) In-Reply-To: References: From: Charles Nadeau Date: Tue, 11 Jul 2017 14:46:03 +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="001a114fe738505cdc05540a1605" 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 --001a114fe738505cdc05540a1605 Content-Type: text/plain; charset="UTF-8" After reducing random_page_cost to 4 and testing more, I can report that the aggregate read throughput for parallel sequential scan is about 90MB/s. However the throughput for sequential scan is still around 4MB/s. One more question: if a query uses more than one table, can more than one table be read through a parallel sequential scan? I have many queries joining tables and I noticed that there was never more than one table read in parallel. Thanks! 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/ --001a114fe738505cdc05540a1605 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
After reducing=C2=A0random_page_cost to 4 and= testing more, I can report that the aggregate read throughput for parallel= sequential scan is about 90MB/s. However the throughput for sequential sca= n is still around 4MB/s.

One more question: if a query uses more than one table, can more= than one table be read through a parallel sequential scan? I have many que= ries joining =C2=A0tables and I noticed that there was never more than one = table read in parallel.
Thanks!
<= div>
Charles

On Mon, = Jul 10, 2017 at 8:35 PM, Igor Neyman <ineyman@perceptron.com><= /span> 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/
--001a114fe738505cdc05540a1605--