Re: [PERFORM] Do work_mem and shared buffers have 1g or 2g limit on 64 bit linux?
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Do work_mem and shared buffers have 1g or 2g limit on 64 bit linux?
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 m...@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- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Do work_mem and shared buffers have 1g or 2g limit on 64 bit linux?
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: [PERFORM] Do work_mem and shared buffers have 1g or 2g limit on 64 bit linux?
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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Do work_mem and shared buffers have 1g or 2g limit on 64 bit linux?
-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
