Re: Do work_mem and shared buffers have 1g or 2g limit on 64 bit linux?

I've checked the source codes in postgresql 9.2.4. In function
static bool
grow_memtuples(Tuplesortstate *state)

the codes:
    /*
     * 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;

Note that MaxAllocSize == 1GB - 1
that means, at least for sorting, it uses at most 1GB work_mem! And
setting larger work_mem has no use at all...

In 9.4, they have a MemoryContextAllocHuge, which allows to allocate
memory with any 64-bit size. So, it improves the performance.



On 6/14/15, Joe Conway <mail@joeconway.com> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 06/13/2015 10:43 AM, Joshua D. Drake wrote:
>>
>> On 06/13/2015 10:27 AM, Kaijiang Chen wrote:
>>> Hi, I am using postgresql 9.2.10 on centos 6.2, 64 bit version.
>>> The server has 512 GB mem.
>>>
>>> The jobs are mainly OLAP like. So I need larger work_mem and
>>> shared buffers. From the source code, there is a constant
>>> MaxAllocSize==1GB. So, I wonder whether work_mem and shared
>>> buffers can exceed 2GB in the 64 bit Linux server?
>
>> Work_mem IIRC can go past 2GB but has never been proven to be
>> effective after that.
>>
>> It does depend on the version you are running.
>
> Starting with 9.4 work_mem and maintenance_work_mem can be usefully
> set to > 2 GB.
>
> I've done testing with index creation, for example, and you can set
> maintenance_work_mem high enough (obviously depending on how much RAM
> you have and how big the sort memory footprint is) to get the entire
> sort to happen in memory without spilling to disk. In some of those
> cases I saw time required to create indexes drop by a factor of 3 or
> more...YMMV.
>
> I have not tested with large work_mem to encourage hash aggregate
> plans, but I suspect there is a lot to be gained there as well.
>
> HTH,
>
> Joe
>
>
> - --
> Joe Conway
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v2.0.22 (GNU/Linux)
>
> iQIcBAEBAgAGBQJVfHITAAoJEDfy90M199hlGvcP/ijyCsXnWZAeZSUAW4qb20YJ
> AHKn0Gl8D9mH9cfPfJeCO+60dcWINzUE6l7qOWWN8JtT6pgbRPGvQsCkx9xRzq+V
> aXv/d/r5wW4g06krcootliQJ1TWnLbPBCQiqmI27HSvnEgDKmJ3kOdDji1FMrcdm
> tuBdNxppoSx0sIFMJ6Xe/brt9O8wG/a81E0lAnsyh2nncaaXba96ldIhUbKvU0ie
> 7In88Rn1UYZDXnoQEtZLmF6ArdTN5dQZkyEZvNKR0CHrPVddVYXP/gMWm/XwnOu6
> k3Rg/evCY2yCyxveuQXU5AZhDFXB/VLoOQoZ5MhLxnoLCNDJrqJzymE1shsgIIji
> i8PfXkKU92/N2kxfDBGwO0LdBpjZzzgg8zMHBsk8FIpXiJvVQKtAfCxYpYkSaL8y
> L0g4Qi16s2/fFZcn1ORH23BaBlcmS1cnRWWyx/amyqPHX0v4XZvp3/kSj2jCSw+E
> V7HD8qLut4rEAxwA5AGCy+9iugZp8DKQUUNiXOYbuysAdjceAa9LzPE0BbB4kuFC
> OfOOjRstr97RyDKwRHjfGs2EnJSENGGcPdGz2HYgup0d4DlIctKww8xeSo55Khp/
> HhBjtk7rpnqqEmEeA8+N8w5Z60x4mK900Anr1xhX2x4ETTIG2g9mYkEEZL/OZRUC
> lihTXLyUhvd57/v7li5p
> =s0U8
> -----END PGP SIGNATURE-----
>