Re: Auto-tuning work_mem and maintenance_work_mem

On Sun, Feb 16, 2014 at 09:26:47PM -0500, Robert Haas wrote:
> > 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!

OK, patch attached.

> 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, can you take the lead on these remaining possible changes? We
don't have time for any controversial changes but things everyone can
agree on, like work_mem, should be implemented for 9.4.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +