Re: Very poor read performance, query independent

Igor,

The sum of effective_cache_size and shared_buffer will be higher than the
physical memory I have. Is it OK?
Thanks!

Charles

On Tue, Jul 11, 2017 at 4:34 PM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:

>
>
> *From:* Charles Nadeau [mailto:charles(dot)nadeau(at)gmail(dot)com]
> *Sent:* Tuesday, July 11, 2017 6:43 AM
> *To:* Igor Neyman <ineyman(at)perceptron(dot)com>
> *Cc:* Andreas Kretschmer <andreas(at)a-kretschmer(dot)de>;
> pgsql-performance(at)postgresql(dot)org
> *Subject:* Re: [PERFORM] Very poor read performance, query independent
>
>
>
> 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
>
>
>
> No, they should not be equal.
>
> From the docs:
>
>
>
> effective_cache_size (integer)
>
> Sets the planner's assumption about the effective size of the disk cache
> that is available to a single query. This is factored into estimates of the
> cost of using an index; a higher value makes it more likely index scans
> will be used, a lower value makes it more likely sequential scans will be
> used. When setting this parameter you should consider both PostgreSQL's
> shared buffers and the portion of the kernel's disk cache that will be used
> for PostgreSQL data files. Also, take into account the expected number of
> concurrent queries on different tables, since they will have to share the
> available space. This parameter has no effect on the size of shared memory
> allocated by PostgreSQL, nor does it reserve kernel disk cache; it is used
> only for estimation purposes. The system also does not assume data remains
> in the disk cache between queries. The default is 4 gigabytes (4GB).
>
> So, I’d set shared_buffers at 24GB and effective_cache_size at 64GB.
>
>
>
> Regards,
>
> Igor
>
>
>

--
Charles Nadeau Ph.D.
http://charlesnadeau.blogspot.com/