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