Igor, I set shared_buffers to 24 GB and effective_cache_size to 64GB and I can see that the queries are faster due to the fact that the index are used more often. Knowing I have 72GB of RAM and the server is exclusively dedicated to Postgresql, what could be the maximum value for effective_cache? Thanks!
Charles On Tue, Jul 11, 2017 at 5:16 PM, Igor Neyman <iney...@perceptron.com> wrote: > > > *From:* pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- > ow...@postgresql.org] *On Behalf Of *Igor Neyman > *Sent:* Tuesday, July 11, 2017 10:34 AM > *To:* Charles Nadeau <charles.nad...@gmail.com> > *Cc:* pgsql-performance@postgresql.org > *Subject:* Re: [PERFORM] Very poor read performance, query independent > > > > *From:* Charles Nadeau [mailto:charles.nad...@gmail.com > <charles.nad...@gmail.com>] > *Sent:* Tuesday, July 11, 2017 6:43 AM > *To:* Igor Neyman <iney...@perceptron.com> > *Cc:* Andreas Kretschmer <andr...@a-kretschmer.de>; > pgsql-performance@postgresql.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 > > > > Also, maybe it’s time to look at execution plans (explain analyze) of > specific slow queries, instead of trying to solve the problem “in general”. > > > > Igor > > > -- Charles Nadeau Ph.D. http://charlesnadeau.blogspot.com/
