Re: Very poor read performance, query independent

[Igor Neyman <ineyman@xxxxxxxxxxxxxx>]

 

From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Charles Nadeau
Sent: Monday, July 10, 2017 11:48 AM
To: Andreas Kretschmer <andreas@xxxxxxxxxxxxxxx>
Cc: pgsql-performance@xxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxx> 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