From: Joe Conway <mail@joeconway.com>
To: Joshua D. Drake <jd@commandprompt.com>
To: Kaijiang Chen <chenkaijiang@gmail.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Do work_mem and shared buffers have 1g or 2g limit on 64 bit linux?
Date: Sat, 13 Jun 2015 11:10:27 -0700
Message-ID: <557C7213.8000704@joeconway.com> (raw)
In-Reply-To: <557C6BA8.7030805@commandprompt.com>
References: <CAAkGvS-Ck3NoMz0i7Y__+2u2LOZzZexznRiZYy1eKdrEW5qU6Q@mail.gmail.com>
<557C6BA8.7030805@commandprompt.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
-----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-----
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mail@joeconway.com, jd@commandprompt.com, chenkaijiang@gmail.com
Subject: Re: Do work_mem and shared buffers have 1g or 2g limit on 64 bit linux?
In-Reply-To: <557C7213.8000704@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox