pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres using more memory than it should
10+ messages / 5 participants
[nested] [flat]
* Postgres using more memory than it should
@ 2008-12-03 16:01 Matthew Wakeling <matthew@flymine.org>
2008-12-03 16:14 ` Re: Postgres using more memory than it should Bill Moran <wmoran@collaborativefusion.com>
2008-12-03 16:25 ` Re: Postgres using more memory than it should tv@fuzzy.cz
2008-12-03 17:53 ` Re: Postgres using more memory than it should hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 3 replies; 10+ messages in thread
From: Matthew Wakeling @ 2008-12-03 16:01 UTC (permalink / raw)
To: pgsql-performance
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.
Here is an excerpt from top:
top - 15:54:17 up 57 days, 6:49, 3 users, load average: 20.17, 21.29, 16.31
Tasks: 250 total, 2 running, 248 sleeping, 0 stopped, 0 zombie
Cpu(s): 3.1%us, 2.5%sy, 0.0%ni, 15.2%id, 78.7%wa, 0.0%hi, 0.5%si, 0.0%st
Mem: 32961364k total, 32898588k used, 62776k free, 22440k buffers
Swap: 8096344k total, 8096344k used, 0k free, 6056472k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
27192 postgres 18 0 30.6g 22g 1984 R 31 71.7 32:20.09 postgres: flymine production-release-15.0 192.168.128.84(33736) INSERT
650 root 10 -5 0 0 0 S 5 0.0 13:56.10 [kswapd2]
5513 postgres 15 0 130m 19m 364 S 4 0.1 1067:04 postgres: stats collector process
957 root 10 -5 0 0 0 D 1 0.0 1:39.13 [md2_raid1]
649 root 10 -5 0 0 0 D 1 0.0 14:14.95 [kswapd1]
28599 root 15 0 0 0 0 D 1 0.0 0:01.25 [pdflush]
648 root 10 -5 0 0 0 S 0 0.0 15:10.68 [kswapd0]
2585 root 10 -5 0 0 0 D 0 0.0 67:15.89 [kjournald]
The query that is being run is an INSERT INTO table SELECT a fairly
complex query.
Any ideas why this is going so badly, and what I can do to solve it?
Matthew
--
First law of computing: Anything can go wro
sig: Segmentation fault. core dumped.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Postgres using more memory than it should
2008-12-03 16:01 Postgres using more memory than it should Matthew Wakeling <matthew@flymine.org>
@ 2008-12-03 16:14 ` Bill Moran <wmoran@collaborativefusion.com>
2 siblings, 0 replies; 10+ messages in thread
From: Bill Moran @ 2008-12-03 16:14 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: pgsql-performance
In response to Matthew Wakeling <matthew@flymine.org>:
>
> 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.
If your query it dealing with a lot of data, it could easily use 1G per
sort operation. If there are a lot of sorts (more than 32) you'll end
up with this problem.
1G is probably too much memory to allocate for work_mem.
--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/
wmoran@collaborativefusion.com
Phone: 412-422-3463x4023
****************************************************************
IMPORTANT: This message contains confidential information and is
intended only for the individual named. If the reader of this
message is not an intended recipient (or the individual
responsible for the delivery of this message to an intended
recipient), please be advised that any re-use, dissemination,
distribution or copying of this message is prohibited. Please
notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a
result of e-mail transmission.
****************************************************************
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Postgres using more memory than it should
2008-12-03 16:01 Postgres using more memory than it should Matthew Wakeling <matthew@flymine.org>
@ 2008-12-03 16:25 ` tv@fuzzy.cz
2008-12-03 16:34 ` Re: Postgres using more memory than it should Matthew Wakeling <matthew@flymine.org>
2 siblings, 1 reply; 10+ messages in thread
From: tv@fuzzy.cz @ 2008-12-03 16:25 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: pgsql-performance
>
> 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).
regards
Tomas
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Postgres using more memory than it should
2008-12-03 16:01 Postgres using more memory than it should Matthew Wakeling <matthew@flymine.org>
2008-12-03 16:25 ` Re: Postgres using more memory than it should tv@fuzzy.cz
@ 2008-12-03 16:34 ` Matthew Wakeling <matthew@flymine.org>
2008-12-03 16:48 ` Re: Postgres using more memory than it should Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Matthew Wakeling @ 2008-12-03 16:34 UTC (permalink / raw)
To: tv@fuzzy.cz; +Cc: pgsql-performance
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.
Matthew
--
Failure is not an option. It comes bundled with your Microsoft product.
-- Ferenc Mantfeld
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Postgres using more memory than it should
2008-12-03 16:01 Postgres using more memory than it should Matthew Wakeling <matthew@flymine.org>
2008-12-03 16:25 ` Re: Postgres using more memory than it should tv@fuzzy.cz
2008-12-03 16:34 ` Re: Postgres using more memory than it should Matthew Wakeling <matthew@flymine.org>
@ 2008-12-03 16:48 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-12-03 16:59 ` Re: Postgres using more memory than it should Matthew Wakeling <matthew@flymine.org>
0 siblings, 1 reply; 10+ messages in thread
From: Scott Marlowe @ 2008-12-03 16:48 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: tv@fuzzy.cz; pgsql-performance
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Postgres using more memory than it should
2008-12-03 16:01 Postgres using more memory than it should Matthew Wakeling <matthew@flymine.org>
2008-12-03 16:25 ` Re: Postgres using more memory than it should tv@fuzzy.cz
2008-12-03 16:34 ` Re: Postgres using more memory than it should Matthew Wakeling <matthew@flymine.org>
2008-12-03 16:48 ` Re: Postgres using more memory than it should Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-12-03 16:59 ` Matthew Wakeling <matthew@flymine.org>
2008-12-03 19:14 ` Re: Postgres using more memory than it should Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Matthew Wakeling @ 2008-12-03 16:59 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: tv@fuzzy.cz; pgsql-performance
On Wed, 3 Dec 2008, Scott Marlowe wrote:
>> 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.
Shared buffers are set at 500MB, which is what all the other backends are
holding onto. It's just the one backend that is using 30GB. At the moment,
it is being swapped out, but the system seems responsive. We'll restart
the whole lot some time in the middle of the night when noone minds.
> 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.
It's possible, although I didn't see any relevant memory leaks in the
release notes. This is one of the only machines we have that has not been
upgraded, and it is on our schedule. Because it is running a slightly old
version of RedHat Fedora, upgrading involves more horribleness than our
sysadmin is willing to do on the fly with the server up.
Matthew
--
The email of the species is more deadly than the mail.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Postgres using more memory than it should
2008-12-03 16:01 Postgres using more memory than it should Matthew Wakeling <matthew@flymine.org>
2008-12-03 16:25 ` Re: Postgres using more memory than it should tv@fuzzy.cz
2008-12-03 16:34 ` Re: Postgres using more memory than it should Matthew Wakeling <matthew@flymine.org>
2008-12-03 16:48 ` Re: Postgres using more memory than it should Scott Marlowe <scott.marlowe@gmail.com>
2008-12-03 16:59 ` Re: Postgres using more memory than it should Matthew Wakeling <matthew@flymine.org>
@ 2008-12-03 19:14 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Scott Marlowe @ 2008-12-03 19:14 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: tv@fuzzy.cz; pgsql-performance
On Wed, Dec 3, 2008 at 9:59 AM, Matthew Wakeling <matthew@flymine.org> wrote:
> On Wed, 3 Dec 2008, Scott Marlowe wrote:
>> 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.
>
> It's possible, although I didn't see any relevant memory leaks in the
> release notes. This is one of the only machines we have that has not been
> upgraded, and it is on our schedule. Because it is running a slightly old
> version of RedHat Fedora, upgrading involves more horribleness than our
> sysadmin is willing to do on the fly with the server up.
That makes absolutely no sense. If it's an in house built rpm, you
just create a new one with the same .spec file, if it was built from
source it's a simple ./configure --youroptionshere ;make;make install.
You need a new sysadmin.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Postgres using more memory than it should
2008-12-03 16:01 Postgres using more memory than it should Matthew Wakeling <matthew@flymine.org>
@ 2008-12-03 17:53 ` hubert depesz lubaczewski <depesz@depesz.com>
2008-12-04 11:35 ` Re: Postgres using more memory than it should Matthew Wakeling <matthew@flymine.org>
2 siblings, 1 reply; 10+ messages in thread
From: hubert depesz lubaczewski @ 2008-12-03 17:53 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: pgsql-performance
On Wed, Dec 03, 2008 at 04:01:48PM +0000, Matthew Wakeling wrote:
> The work_mem setting on this machine is 1000MB, running Postgres 8.3.0.
Check bug report from 2008-11-28, by Grzegorz Jaskiewicz:
query failed, not enough memory on 8.3.5
http://archives.postgresql.org/pgsql-bugs/2008-11/msg00180.php
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Postgres using more memory than it should
2008-12-03 16:01 Postgres using more memory than it should Matthew Wakeling <matthew@flymine.org>
2008-12-03 17:53 ` Re: Postgres using more memory than it should hubert depesz lubaczewski <depesz@depesz.com>
@ 2008-12-04 11:35 ` Matthew Wakeling <matthew@flymine.org>
2008-12-04 13:06 ` Re: Postgres using more memory than it should Matthew Wakeling <matthew@flymine.org>
0 siblings, 1 reply; 10+ messages in thread
From: Matthew Wakeling @ 2008-12-04 11:35 UTC (permalink / raw)
To: hubert depesz lubaczewski <depesz@depesz.com>; +Cc: pgsql-performance
On Wed, 3 Dec 2008, hubert depesz lubaczewski wrote:
> Check bug report from 2008-11-28, by Grzegorz Jaskiewicz:
> query failed, not enough memory on 8.3.5
>
> http://archives.postgresql.org/pgsql-bugs/2008-11/msg00180.php
Thanks, that does explain everything. So workmem is not a hard limit on
the amount of memory used per hash. Once the planner has committed to
using a hash (and it can only use estimates to work out whether it will
fit in workmem), then the execution will blindly go ahead and try and fit
absolutely everything in a hash in memory even if it doesn't fit in
workmem.
I agree it would be nice to fix this, but I'm not sure how at the moment.
Matthew
--
To most people, solutions mean finding the answers. But to chemists,
solutions are things that are still all mixed up.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Postgres using more memory than it should
2008-12-03 16:01 Postgres using more memory than it should Matthew Wakeling <matthew@flymine.org>
2008-12-03 17:53 ` Re: Postgres using more memory than it should hubert depesz lubaczewski <depesz@depesz.com>
2008-12-04 11:35 ` Re: Postgres using more memory than it should Matthew Wakeling <matthew@flymine.org>
@ 2008-12-04 13:06 ` Matthew Wakeling <matthew@flymine.org>
0 siblings, 0 replies; 10+ messages in thread
From: Matthew Wakeling @ 2008-12-04 13:06 UTC (permalink / raw)
To: hubert depesz lubaczewski <depesz@depesz.com>; +Cc: pgsql-performance
On Thu, 4 Dec 2008, Matthew Wakeling wrote:
>> http://archives.postgresql.org/pgsql-bugs/2008-11/msg00180.php
>
> Thanks, that does explain everything.
Oh right, yes. It explains everything *except* the fact that the backend
is still holding onto all the RAM after the query is finished. Could the
fact that we SIGINTed it in the middle of the query explain that at all?
Matthew
--
I'd try being be a pessimist, but it probably wouldn't work anyway.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2008-12-04 13:06 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-12-03 16:01 Postgres using more memory than it should Matthew Wakeling <matthew@flymine.org>
2008-12-03 16:14 ` Bill Moran <wmoran@collaborativefusion.com>
2008-12-03 16:25 ` tv@fuzzy.cz
2008-12-03 16:34 ` Matthew Wakeling <matthew@flymine.org>
2008-12-03 16:48 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-12-03 16:59 ` Matthew Wakeling <matthew@flymine.org>
2008-12-03 19:14 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-12-03 17:53 ` hubert depesz lubaczewski <depesz@depesz.com>
2008-12-04 11:35 ` Matthew Wakeling <matthew@flymine.org>
2008-12-04 13:06 ` Matthew Wakeling <matthew@flymine.org>
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