On Thu, Feb 13, 2014 at 3:34 PM, Bruce Momjian <br...@momjian.us> wrote: > On Fri, Oct 11, 2013 at 03:39:51PM -0700, Kevin Grittner wrote: >> Josh Berkus <j...@agliodbs.com> wrote: >> > On 10/11/2013 01:11 PM, Bruce Momjian wrote: >> >> In summary, I think we need to: >> >> >> >> * decide on new defaults for work_mem and maintenance_work_mem >> >> * add an initdb flag to allow users/packagers to set shared_bufffers? >> >> * add an autovacuum_work_mem setting? >> >> * change the default for temp_buffers? >> > >> > If we're changing defaults, bgwriter_lru_maxpages and vacuum_cost_limit >> > could also use a bump; those thresholds were set for servers with < 1GB >> > of RAM. >> >> +1 on those. >> >> Also, I have often had to bump cpu_tuple_cost into the 0.03 to 0.05 >> range to get a good plan. In general, this makes the exact >> settings of *_page_cost less fussy, and I have hit situations where >> I was completely unable to get a good plan to emerge without >> bumping cpu_tuple_cost relative to the other cpu costs. I know that >> it's possible to engineer a workload that shows any particular cost >> adjustment to make things worse, but in real-life production >> environments I have never seen an increase in this range make plan >> choice worse. > > So, would anyone like me to create patches for any of these items before > we hit 9.4 beta? We have added autovacuum_work_mem, and increasing > work_mem and maintenance_work_mem by 4x is a simple operation. Not sure > about the others. Or do we just keep this all for 9.5?
I don't think anyone objected to increasing the defaults for work_mem and maintenance_work_mem by 4x, and a number of people were in favor, so I think we should go ahead and do that. If you'd like to do the honors, by all means! The current bgwriter_lru_maxpages value limits the background writer to a maximum of 4MB/s. If one imagines shared_buffers = 8GB, that starts to seem rather low, but I don't have a good feeling for what a better value would be. The current vacuum cost delay settings limit autovacuum to about 2.6MB/s. I am inclined to think we need a rather large bump there, like 10x, but maybe it would be more prudent to do a smaller bump, like say 4x, to avoid changing the default behavior too dramatically between releases. IOW, I guess I'm proposing raising vacuum_cost_limit from 200 to 800. I don't really know about cpu_tuple_cost. Kevin's often advocated raising it, but I haven't heard anyone else advocate for that. I think we need data points from more people to know whether or not that's a good idea in general. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers