Skip site navigation (1) Skip section navigation (2)

maintenance_work_mem memory constraint?

From: Bernd Helmle <mailings(at)oopsware(dot)de>
To: pgsql-hackers(at)postgresql(dot)org
Subject: maintenance_work_mem memory constraint?
Date: 2007-11-26 17:32:39
Message-ID: 9B33E4187A68C7C9F3F5667D@imhotep.credativ.de (view raw or flat)
Thread:
Lists: pgsql-hackers
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

Attachment: tuplesort.diff
Description: text/x-diff (1.5 KB)

Responses

pgsql-hackers by date

Next:From: Gregory StarkDate: 2007-11-26 17:40:34
Subject: Re: 8.3devel slower than 8.2 under read-only load
Previous:From: mac_man2005Date: 2007-11-26 17:12:58
Subject: Fw: Replacement Selection

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group