Re: [PERFORM] Very poor read performance, query independent - Mailing list pgsql-performance

From Charles Nadeau
Subject Re: [PERFORM] Very poor read performance, query independent
Date
Msg-id CADFyZw6JrhsLR_eYOeCjjiQMzz5bepk6AfMRBu0hnaQg+vN-=A@mail.gmail.com
Whole thread Raw
In response to Re: [PERFORM] Very poor read performance, query independent  (Igor Neyman <ineyman@perceptron.com>)
Responses Re: [PERFORM] Very poor read performance, query independent
List pgsql-performance
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 <ineyman@perceptron.com> 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 <andreas@a-kretschmer.de>
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

 

 

 

 




--

pgsql-performance by date:

Previous
From: Charles Nadeau
Date:
Subject: Re: [PERFORM] Very poor read performance, query independent
Next
From: Igor Neyman
Date:
Subject: Re: [PERFORM] Very poor read performance, query independent