On 10/14/13 8:18 AM, Robert Haas wrote:
> On Sat, Oct 12, 2013 at 3:07 AM, Magnus Hagander <magnus@hagander.net> wrote:
>> On Oct 11, 2013 10:23 PM, "Josh Berkus" <josh@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
>>
>> 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.
>
Also, more memory generally means more filesystem cache which means you can do more vacuum work per round.
FWIW, on our 512G servers...
cnuapp_prod@postgres11.obr=# select name, setting from pg_settings where name ~ 'vacuum_cost'; name
| setting
------------------------------+--------- autovacuum_vacuum_cost_delay | 10 autovacuum_vacuum_cost_limit | -1
vacuum_cost_delay | 10 vacuum_cost_limit | 2000 vacuum_cost_page_dirty | 10
vacuum_cost_page_hit | 1 vacuum_cost_page_miss | 10
(7 rows)
The page_hit cost is intentionally the same as the page_dirty limit because writes to the SAN are generally far cheaper
thanreads that actually hit spindles. Of course with the amount of FS cache we have (512G-8G shared buffers at most)
readsare often very likely to hit the FS cache, but tuning of these settings while watching IO stats has shown these
settingsto be minimally disruptive.
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net