From: Bill Moran <wmoran@collaborativefusion.com>
To: Campbell, Lance <lance@uiuc.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: work_mem
Date: Sun, 28 Jan 2007 17:11:18 -0500
Message-ID: <20070128171118.03fa29db.wmoran@collaborativefusion.com> (raw)
In-Reply-To: <A3AC4FA47DC0B1458C3E5396E685E63302395C68@SAB-DC1.sab.uiuc.edu>
References: <A3AC4FA47DC0B1458C3E5396E685E63302395C68@SAB-DC1.sab.uiuc.edu>
"Campbell, Lance" <lance@uiuc.edu> wrote:
>
> I have been researching how to improve my overall performance of
> postgres. I am a little confused on the reasoning for how work_mem is
> used in the postgresql.conf file. The way I understand the
> documentation is you define with work_mem how much memory you want to
> allocate per search. Couldn't you run out of memory? This approach
> seems kind of odd to me. How do you tell the system not to allocate too
> much memory if you all of the sudden got hit with a heavier number of
> queries?
work_mem tells PostgreSQL how much memory to use for each sort/join.
If a sort/join exceeds that amount, PostgreSQL uses temp files on the
disk instead of memory to do the work.
If you want a query to complete, you've got to allow Postgres to finish
it. The work_mem setting gives Postgres information on how to go about
doing that in the best way.
If you want to guarantee that individual processes can't suck up tons of
memory, use your operating system's ulimit or equivalent functionality.
That's one of the advantages of the forking model, it allows the operating
system to enforce a certain amount of policy an a per-connection basis.
HTH,
Bill
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: wmoran@collaborativefusion.com, lance@uiuc.edu
Subject: Re: work_mem
In-Reply-To: <20070128171118.03fa29db.wmoran@collaborativefusion.com>
* 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