From: Stephen Frost <sfrost@snowman.net>
To: Robert Schnabel <schnabelr@missouri.edu>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Allow sorts to use more available memory
Date: Mon, 12 Sep 2011 21:13:27 -0400
Message-ID: <20110913011327.GT12765@tamriel.snowman.net> (raw)
In-Reply-To: <4E6E830E.30701@missouri.edu>
References: <4E6E426D.7060405@missouri.edu>
<CAOR=d=1WgWOPz+8t9FqqNbbabNKu6DOm-z0S1MJ0jGe+FsRGfw@mail.gmail.com>
<4E6E830E.30701@missouri.edu>
* Robert Schnabel (schnabelr@missouri.edu) wrote:
> And getting back to the to-do list entry and reading the related
> posts, it appears that even if you could set work_mem that high it
> would only use 2GB anyway. I guess that was the second part of my
> question. Is that true?
Yes and no. work_mem is used by the planner to figure out what kind of
plan to use. The planner plans things based off of statistics, but it's
not perfect, especially on large tables with lots of data which have
dependent data between columns.
Where the 2GB limit comes into play is when you end up with a plan that
does, say, a large sort. PG will use memory for the sort up to
work_mem, or 2GB, whichever is lower, and spill to disk after that. I
don't believe it has such a limit for a hash table, due to how the data
structures for the hash table are allocated (and I recall seeing single
PG queries that use hash tables getting into the 30+GB range, of course,
I had work_mem set upwards of 100GB on a 32GB box... :).
So, if you're doing data warehousing, and you're pretty much the only
user (or there's only one at a time), setting it up pretty high is
acceptable, but you do need to watch the box and make sure you don't run
it out of memory. Also, make sure you have things configured correctly,
if you're using Linux, to prevent the OOM killer from kicking in. Also,
as I suggested before, set it to a reasonable level for the 'default'
and just up it for specific queries that may benefit from it.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 197B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sfrost@snowman.net, schnabelr@missouri.edu, scott.marlowe@gmail.com
Subject: Re: Allow sorts to use more available memory
In-Reply-To: <20110913011327.GT12765@tamriel.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox