Re: Auto-tuning work_mem and maintenance_work_mem

On Sat, Oct 12, 2013 at 3:07 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> On Oct 11, 2013 10:23 PM, "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
>
> Uh, those are there to limit io and not memory, right? More memory isn't the
> reason to increase them, more io is. For people deploying on modern server
> hardware then yes it's often low, but for all those deploying in virtualized
> environments with io performance reminding you of the 1990ies, I'm not so
> sure it is...

bgwriter_lru_maxpages is clearly related to the size of
shared_buffers, although confusingly it is expressed as a number of
buffers, while shared_buffers is expressed as a quantity of memory. I
think we might have done better to call the GUC
bgwriter_lru_maxpercent and make it a percentage of shared buffers.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company