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

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

From

Kaijiang Chen

Date:

13 June 2015, 20:27:59

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?

Thanks and regards,

Kaijiang

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

From

"Joshua D. Drake"

Date:

13 June 2015, 20:43:09

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?

Shared Buffers is not limited.

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.

JD


>
> Thanks and regards,
> Kaijiang
>


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.

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

From

Joe Conway

Date:

13 June 2015, 21:10:32

-----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-----

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

From

Kaijiang Chen

Date:

15 June 2015, 06:44:47

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-----
>

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

From

Tomas Vondra

Date:

15 June 2015, 17:58:08

On 06/15/15 05:44, Kaijiang Chen wrote:
> 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...

That's not true. This only limits the size of 'memtuples' array, which
only stores pointer to the actual tuple, and some additional data. The
tuple itself is not counted against MaxAllocSize directly. The SortTuple
structure has ~24B which means you can track 33M tuples in that array,
and the tuples may take a lot more space.

regards

--
Tomas Vondra                   http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services