Re: problem with large maintenance_work_mem settings and - Mailing list pgsql-hackers

From Michael Paesold
Subject Re: problem with large maintenance_work_mem settings and
Date
Msg-id 030201c63f9f$0b78e4a0$67dc8380@zaphod
Whole thread Raw
In response to problem with large maintenance_work_mem settings and CREATE INDEX  (Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>)
Responses Re: problem with large maintenance_work_mem settings and
List pgsql-hackers
Stefan Kaltenbrunner wrote:
> hubert depesz lubaczewski wrote:
>> On 3/4/06, Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> wrote:
>>
>>>forgot to mention that this is 8.1.3 compiled from source. Further
>>>testing shows that not only CREATE INDEX has some issue with large
>>>maintenance_work_mem settings :
>>
>> what does it show:
>> cat /proc/sys/kernel/shmmax
>
> 1421326592
>
> not that I think it is related to the problem at all.

I can second that. Maintenance work mem is not allocated in shared memory.

> It looks like I'm
> hitting the MaxAllocSize Limit in src/include/utils/memutils.h.

There are two issues you have mentioned. This one is more obvious: the 
limitation of the memory that can be allocated.

The other one is the very bad performance for index creation. I can only 
guess, but is sound like this is related to the recent discussion on hackers 
about issues with the qsort performance. If the theory is true, your index 
creation should be much faster with a much lower setting for 
maintenance_work_mem, so that it uses external sort.

See the discussion starting here:
http://archives.postgresql.org/pgsql-hackers/2006-02/msg00590.php

Best Regards,
Michael Paesold 




pgsql-hackers by date:

Previous
From: Stefan Kaltenbrunner
Date:
Subject: Re: problem with large maintenance_work_mem settings and
Next
From: Tom Lane
Date:
Subject: Re: Foreign keys for non-default datatypes