From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Auto-tuning work_mem and maintenance_work_mem |
Date: | 2013-10-10 22:36:17 |
Message-ID: | 20131010223617.GV7092@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Thu, Oct 10, 2013 at 11:18:28AM -0700, Josh Berkus wrote:
> Bruce,
>
> >> That's way low, and frankly it's not worth bothering with this if all
> >> we're going to get is an incremental increase. In that case, let's just
> >> set the default to 4MB like Robert suggested.
> >
> > Uh, well, 100 backends at 6MB gives us 600MB, and if each backend uses
> > 3x work_mem, that gives us 1.8GB for total work_mem. This was based on
> > Andrew's concerns about possible over-commit of work_mem. I can of
> > course adjust that.
>
> That's worst-case-scenario planning -- the 3X work-mem per backend was:
> a) Solaris and
> b) data warehousing
>
> In a normal OLTP application each backend averages something like 0.25 *
> work_mem, since many queries use no work_mem at all.
>
> It also doesn't address my point that, if we are worst-case-scenario
> default-setting, we're going to end up with defaults which aren't
> materially different from the current defaults. In which case, why even
> bother with this whole exercise?
OK, here is an updated patch that is less conservative. FYI, this
thread has gone on for 80 messages, and I assume it will take many more
until we are done:
test=> SHOW shared_buffers;
shared_buffers
----------------
128MB
(1 row)
test=> SHOW work_mem;
work_mem
----------
2621kB
(1 row)
test=> SHOW maintenance_work_mem;
maintenance_work_mem
----------------------
10922kB
(1 row)
---------------------------------------------------------------------------
test=> SHOW shared_buffers;
shared_buffers
----------------
2GB
(1 row)
test=> SHOW work_mem;
work_mem
----------
41943kB
(1 row)
test=> SHOW maintenance_work_mem;
maintenance_work_mem
----------------------
174762kB
(1 row)
---------------------------------------------------------------------------
test=> SHOW shared_buffers;
shared_buffers
----------------
8GB
(1 row)
test=> SHOW work_mem;
work_mem
----------
167772kB
(1 row)
test=> SHOW maintenance_work_mem;
maintenance_work_mem
----------------------
699050kB
(1 row)
Patch attached.
--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
Attachment | Content-Type | Size |
---|---|---|
work_mem.diff | text/x-diff | 8.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2013-10-10 22:40:17 | Re: Auto-tuning work_mem and maintenance_work_mem |
Previous Message | Mark Kirkwood | 2013-10-10 22:32:13 | Re: [PoC] pgstattuple2: block sampling to reduce physical read |