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