Re: Very poor read performance, query independent

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(at)perceptron(dot)com> wrote:

>
>
> *From:* pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-
> owner(at)postgresql(dot)org] *On Behalf Of *Charles Nadeau
> *Sent:* Monday, July 10, 2017 11:48 AM
> *To:* Andreas Kretschmer <andreas(at)a-kretschmer(dot)de>
> *Cc:* pgsql-performance(at)postgresql(dot)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(at)a-kretschmer(dot)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/