On Wed, Dec 3, 2008 at 9:34 AM, Matthew Wakeling <matthew@xxxxxxxxxxx> wrote: > On Wed, 3 Dec 2008, tv@xxxxxxxx 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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
