On Wed, Dec 3, 2008 at 9:34 AM, Matthew Wakeling <matthew@flymine.org> wrote:
> On Wed, 3 Dec 2008, tv@fuzzy.cz wrote:
>>>
>>> Hi. I have a problem on one of our production servers. A fairly
>>> complicated query is running, and the backend process is using 30 GB of
>>> RAM. The machine only has 32GB, and is understandably swapping like
>>> crazy.
>>> My colleague is creating swap files as quickly as it can use them up.
>>>
>>> The work_mem setting on this machine is 1000MB, running Postgres 8.3.0.
>>
>> Are you aware that this is a per-session / per-sort settings? That means,
>> if you have 10 sessions, each of them running query with 2 sort steps in
>> the plan, it may occupy up to 20 GB of RAM (if both sorts use the whole
>> 1GB of RAM).
>
> Quite aware, thanks.
>
> Having sent the process a SIGINT and inspected the logs, I now have a query
> to explain. Looking at it, there is one single sort, and ten hash
> operations, which would equate to 10GB, not 30GB. What is more worrying is
> that now that the query has been stopped, the backend process is still
> hanging onto the RAM.
What's your setting for share_buffers, as that's likely what the
backend is holding onto.
Also, you should REALLY update to 8.3.5 as there are some nasty bugs
fixed from 8.3.0 you don't want to run into. Who knows, you might be
being bitten by one right now. Unlike other bits of software floating
around, pgsql updates are bug fix / security fix only, with no major
code changes allowed, since those go into the next release which is
usually ~1 year later anyway.