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 <iney...@perceptron.com> wrote: > > > *From:* pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- > ow...@postgresql.org] *On Behalf Of *Charles Nadeau > *Sent:* Monday, July 10, 2017 11:48 AM > *To:* Andreas Kretschmer <andr...@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 < > andr...@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/
