Re: Postgres using more memory than it should

On Wed, 3 Dec 2008, tv(at)fuzzy(dot)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.

Matthew

--
Failure is not an option. It comes bundled with your Microsoft product.
-- Ferenc Mantfeld