Re: Auto-tuning work_mem and maintenance_work_mem

On 17/02/14 15:26, Robert Haas wrote:
> On Thu, Feb 13, 2014 at 3:34 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> On Fri, Oct 11, 2013 at 03:39:51PM -0700, Kevin Grittner wrote:
>>> Josh Berkus <josh(at)agliodbs(dot)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.
>
Processors have been getting faster, relative to spinning rust, over the
years. So it puzzles me why anybody would want to raise the
cpu_tuple_cost! Possibly, the various costs should change if the
database is on SSD's? Of course, I have the implicit assumption that
cost factors like 'cpu_tuple_cost' have more than just a vague relation
to the semantics implied by their naming!

It would be good, if can we get some clarity on what these various cost
factors are actually meant to do and how they relate to each other.

Cheers,
Gavin

Cheers,
Gavin