Skip site navigation (1) Skip section navigation (2)

Auto-tuning work_mem and maintenance_work_mem

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Cc: Josh Berkus <josh(at)agliodbs(dot)com>
Subject: Auto-tuning work_mem and maintenance_work_mem
Date: 2013-10-09 14:30:46
Message-ID: 20131009143046.GT22450@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Josh Berkus suggested here that work_mem and maintenance_work_mem could
be auto-tuned like effective_cache_size:

	http://www.postgresql.org/message-id/50ECCF93.3060101@agliodbs.com

The attached patch implements this, closely matching the default values
for the default shared_buffers value:

	test=> SHOW shared_buffers;
	 shared_buffers
	----------------
	 128MB
	(1 row)
	
	test=> SHOW work_mem;
	 work_mem
	----------
	 1310kB
	(1 row)
	
	test=> SHOW maintenance_work_mem;
	 maintenance_work_mem
	----------------------
	 20971kB
	(1 row)

Previous defaults were 1MB and 16MB, but the new defaults don't match
exactly because our max_connections is a power of 10 (100), not a power
of 2 (128).  Of course, if shared_buffer is 10x larger, those defaults
become 10x larger.

FYI, I based maintenance_work_mem's default on shared_buffers, not on
work_mem because it was too complex to change maintenance_work_mem when
someone changes work_mem.

I will work on auto-tuning temp_buffers next.  Any other suggestions? 
wal_buffers is already auto-tuned.

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

  + Everyone has their own god. +

Attachment: work_mem.diff
Description: text/x-diff (8.0 KB)

Responses

pgsql-hackers by date

Next:From: Andres FreundDate: 2013-10-09 14:32:44
Subject: Re: Auto-tuning work_mem and maintenance_work_mem
Previous:From: Pavel StehuleDate: 2013-10-09 14:19:36
Subject: Re: WITHIN GROUP patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group