PostgreSQL Administration - CLUSTER using more memory than expected

Author

CLUSTER using more memory than expected

Joshua Marsh

2006-10-26, 12:16 am

The CLUSTER function seems to be using more memory than I expect. Here is
what I get from top and from my config file:

from top:
27589 postgres 25 0 3943m 3.6g 11m R 99.9 61.6 639:19.41 postgres:
postgres data 127.0.0.1(42126) CLUSTER

from postgresql.conf
shared_buffers = 1000 #26214 # min 16, at least
max_connections*2, 8KB each
work_mem = 256000 # min 64, size in KB
maintenance_work_mem
= 2097151 # min 1024, size in KB
#max_stack_depth = 2048 # min 100, size in KB

My version is: 8.0.3

Any ideas why this would be happening. I tried a full restart and the same
thing happens.

Thanks,

Josh

--
This E-mail is covered by the Electronic Communications Privacy Act, 18
U.S.C. 2510-2521 and is legally privileged.

This information is confidential information and is intended only for the
use of the individual or entity named above. If the reader of this message
is not the intended recipient, you are hereby notified that any
dissemination, distribution or copying of this communication is strictly
prohibited.

Tom Lane

2006-10-26, 12:16 am

"Joshua Marsh" <icub3d@gmail.com> writes:
> The CLUSTER function seems to be using more memory than I expect.

CLUSTER will eat maintenance_work_mem
during index rebuilds --- more or
less. You shouldn't expect these numbers to be dead on, particularly
not in older releases. It looks like your 2Gb spec has turned into
3.6Gb actually eaten, which is a bit sloppier accounting than I'd like
but not overly surprising. (FWIW, I think CVS HEAD is better than 8.0
at measuring sort workspace.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Joshua Marsh

2006-10-26, 12:16 am

On 10/25/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>
> CLUSTER will eat maintenance_work_mem
during index rebuilds --- more or
> less. You shouldn't expect these numbers to be dead on, particularly
> not in older releases. It looks like your 2Gb spec has turned into
> 3.6Gb actually eaten, which is a bit sloppier accounting than I'd like
> but not overly surprising. (FWIW, I think CVS HEAD is better than 8.0
> at measuring sort workspace.)
>
> regards, tom lane
>

Thanks for the clarification. It doesn't sound like it's going to jump to
8Gb and hog most of the RAM.

Jim C. Nasby

2006-10-26, 7:25 pm

On Wed, Oct 25, 2006 at 10:13:59AM -0600, Joshua Marsh wrote:
> My version is: 8.0.3

You should really upgrade to 8.0.9; there's crash and data loss bugs
that have been fixed since 8.0.3.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Joshua Marsh

2006-10-26, 7:25 pm

On 10/26/06, Jim C. Nasby <jim@nasby.net> wrote:
>
> On Wed, Oct 25, 2006 at 10:13:59AM -0600, Joshua Marsh wrote:
>
> You should really upgrade to 8.0.9; there's crash and data loss bugs
> that have been fixed since 8.0.3.
> --
> Jim Nasby jim@nasby.net
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>

Eeek! Thanks for the head's up. I'll do that.

Sponsored Links