Re: [HACKERS] maintenance_work_mem memory constraint?
--On Montag, November 26, 2007 21:41:33 +0100 I wrote: --On Montag, November 26, 2007 13:02:14 -0500 Tom Lane [EMAIL PROTECTED] wrote: Bernd Helmle [EMAIL PROTECTED] writes: ... But isn't it worth to special case the code in grow_memtuples() (and maybe other places where sort is likely to use more RAM), so that we can remove this constraint on 64-Bit systems with many RAM built in? Or am I missing something very important?. AFAICS this patch can increase the number of sortable tuples by at most 2X (less one). That doesn't seem worth getting very worked up about ... regards, tom lane That's true. Well, i haven't meant the diff as a discussable patch at all. It's just what i've done to understand why we have this limit for tuplesort. afaics, the main constraint here is MaxAllocSize, and i just wonder if that doesn't introduce unnecessary limits on systems which can use many RAM for index creation and wether we can be more generous here. So one idea could be to allow larger allocation requests during sorting on systems where we know that this is likely to work. And, to complete my concerns, if i can afford to give maintenance_work_mem 10GB and the system just uses 2GB this is somewhere near a bug. -- Thanks Bernd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] maintenance_work_mem memory constraint?
While supporting a customer to increase recovery performance from its
backups i just realized that PostgreSQL never uses big maintenance_work_mem
settings. Even giving 10GB of RAM to maintenance_work_mem results in using
a fraction of memory (it switches to external sort after using around 2
GB). I think the culprit ist the following code in tuplesort.c,
grow_memtuples(), as the comments there let assume already:
/*
* On a 64-bit machine, allowedMem could be high enough to get us
into
* trouble with MaxAllocSize, too.
*/
if ((Size) (state-memtupsize * 2) = MaxAllocSize /
sizeof(SortTuple))
return false;
While i understand, that doubling the memtuples array is more efficient
than increasing the array in smaller steps, i think we give away usable
memory, because we never consider using memory up to the upper limit given
by MaxAllocSize. Modifying the code in that way results in a sightly better
memory usage, but far away from what the system is able to use on such a
machine (see the diff attached, a very crude experimental code).
I've played around with increasing the MaxAllocSize as well and got the
backend to use up to 6GB maintenance_work_mem during creating an index with
80.000.000 integer tuples. That way the backend was able to sort the tuples
entirely in memory, speeding up the creation of the index from 200s to 80s.
I understand that we have to handle MaxAllocSize very carefully, since it's
involved in many cases in the code. But isn't it worth to special case the
code in grow_memtuples() (and maybe other places where sort is likely to
use more RAM), so that we can remove this constraint on 64-Bit systems with
many RAM built in? Or am I missing something very important?.
--
Thanks
Bernd*** tuplesort.c 2007-11-26 18:30:21.0 +0100
--- tuplesort.c.new 2007-11-26 18:30:11.0 +0100
***
*** 810,820
* On a 64-bit machine, allowedMem could be high enough to get us into
* trouble with MaxAllocSize, too.
*/
if ((Size) (state-memtupsize * 2) = MaxAllocSize / sizeof(SortTuple))
! return false;
FREEMEM(state, GetMemoryChunkSpace(state-memtuples));
- state-memtupsize *= 2;
state-memtuples = (SortTuple *)
repalloc(state-memtuples,
state-memtupsize * sizeof(SortTuple));
--- 810,837
* On a 64-bit machine, allowedMem could be high enough to get us into
* trouble with MaxAllocSize, too.
*/
+
+ if ((Size) (state-memtupsize) + 1 = MaxAllocSize / sizeof(SortTuple))
+ {
+ elog(DEBUG1, memtupsize %d reached MaxAllocSize %lu,
+ state-memtupsize, MaxAllocSize);
+ /* max alloc size already reached */
+ return false;
+ }
+
if ((Size) (state-memtupsize * 2) = MaxAllocSize / sizeof(SortTuple))
! {
! /* use the maximum allowed alloc size */
! state-memtupsize = (MaxAllocSize / sizeof(SortTuple) - 1);
! elog(DEBUG1, memtupsize %d exceeds MaxAllocSize %lu, readjusted,
! state-memtupsize * 2, MaxAllocSize);
! }
! else
! {
! state-memtupsize *= 2;
! }
FREEMEM(state, GetMemoryChunkSpace(state-memtuples));
state-memtuples = (SortTuple *)
repalloc(state-memtuples,
state-memtupsize * sizeof(SortTuple));
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] maintenance_work_mem memory constraint?
Bernd Helmle [EMAIL PROTECTED] writes: ... But isn't it worth to special case the code in grow_memtuples() (and maybe other places where sort is likely to use more RAM), so that we can remove this constraint on 64-Bit systems with many RAM built in? Or am I missing something very important?. AFAICS this patch can increase the number of sortable tuples by at most 2X (less one). That doesn't seem worth getting very worked up about ... regards, tom lane ---(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
Re: [HACKERS] maintenance_work_mem memory constraint?
--On Montag, November 26, 2007 13:02:14 -0500 Tom Lane [EMAIL PROTECTED] wrote: Bernd Helmle [EMAIL PROTECTED] writes: ... But isn't it worth to special case the code in grow_memtuples() (and maybe other places where sort is likely to use more RAM), so that we can remove this constraint on 64-Bit systems with many RAM built in? Or am I missing something very important?. AFAICS this patch can increase the number of sortable tuples by at most 2X (less one). That doesn't seem worth getting very worked up about ... regards, tom lane That's true. Well, i haven't meant the diff as a discussable patch at all. It's just what i've done to understand why we have this limit for tuplesort. afaics, the main constraint here is MaxAllocSize, and i just wonder if that doesn't introduce unnecessary limits on systems which can use many RAM for index creation and wether we can be more generous here. So one idea could be to allow larger allocation requests during sorting on systems where we know that this is likely to work. -- Thanks Bernd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
