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@xxxxxxxxxxxxx> 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----- > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
