pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Fix extreme skew detection in Parallel Hash Join. 7+ messages / 1 participants [nested] [flat]
* pgsql: Fix extreme skew detection in Parallel Hash Join. @ 2024-10-17 09:33 Thomas Munro <tmunro@postgresql.org> 0 siblings, 0 replies; 7+ messages in thread From: Thomas Munro @ 2024-10-17 09:33 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix extreme skew detection in Parallel Hash Join. After repartitioning the inner side of a hash join that would have exceeded the allowed size, we check if all the tuples from a parent partition moved to one child partition. That is evidence that it contains duplicate keys and later attempts to repartition will also fail, so we should give up trying to limit memory (for lack of a better fallback strategy). A thinko prevented the check from working correctly in partition 0 (the one that is partially loaded into memory already). After repartitioning, we should check for extreme skew if the *parent* partition's space_exhausted flag was set, not the child partition's. The consequence was repeated futile repartitioning until per-partition data exceeded various limits including "ERROR: invalid DSA memory alloc request size 1811939328", OS allocation failure, or temporary disk space errors. (We could also do something about some of those symptoms, but that's material for separate patches.) This problem only became likely when PostgreSQL 16 introduced support for Parallel Hash Right/Full Join, allowing NULL keys into the hash table. Repartitioning always leaves NULL in partition 0, no matter how many times you do it, because the hash value is all zero bits. That's unlikely for other hashed values, but they might still have caused wasted extra effort before giving up. Back-patch to all supported releases. Reported-by: Craig Milhiser <craig@milhiser.com> Reviewed-by: Andrei Lepikhov <lepihov@gmail.com> Discussion: https://postgr.es/m/CA%2BwnhO1OfgXbmXgC4fv_uu%3DOxcDQuHvfoQ4k0DFeB0Qqd-X-rQ%40mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/98c7c7152d2d4670a349c0fe48f05cf95c0b1185 Modified Files -------------- src/backend/executor/nodeHash.c | 17 ++++++++++++----- 1 file changed, 12 insertions(+), 5 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Fix extreme skew detection in Parallel Hash Join. @ 2024-10-17 09:33 Thomas Munro <tmunro@postgresql.org> 0 siblings, 0 replies; 7+ messages in thread From: Thomas Munro @ 2024-10-17 09:33 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix extreme skew detection in Parallel Hash Join. After repartitioning the inner side of a hash join that would have exceeded the allowed size, we check if all the tuples from a parent partition moved to one child partition. That is evidence that it contains duplicate keys and later attempts to repartition will also fail, so we should give up trying to limit memory (for lack of a better fallback strategy). A thinko prevented the check from working correctly in partition 0 (the one that is partially loaded into memory already). After repartitioning, we should check for extreme skew if the *parent* partition's space_exhausted flag was set, not the child partition's. The consequence was repeated futile repartitioning until per-partition data exceeded various limits including "ERROR: invalid DSA memory alloc request size 1811939328", OS allocation failure, or temporary disk space errors. (We could also do something about some of those symptoms, but that's material for separate patches.) This problem only became likely when PostgreSQL 16 introduced support for Parallel Hash Right/Full Join, allowing NULL keys into the hash table. Repartitioning always leaves NULL in partition 0, no matter how many times you do it, because the hash value is all zero bits. That's unlikely for other hashed values, but they might still have caused wasted extra effort before giving up. Back-patch to all supported releases. Reported-by: Craig Milhiser <craig@milhiser.com> Reviewed-by: Andrei Lepikhov <lepihov@gmail.com> Discussion: https://postgr.es/m/CA%2BwnhO1OfgXbmXgC4fv_uu%3DOxcDQuHvfoQ4k0DFeB0Qqd-X-rQ%40mail.gmail.com Branch ------ REL_17_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/4ac5d33a8b08eac6e1df235eb38b48e18195c7a4 Modified Files -------------- src/backend/executor/nodeHash.c | 17 ++++++++++++----- 1 file changed, 12 insertions(+), 5 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Fix extreme skew detection in Parallel Hash Join. @ 2024-10-17 09:33 Thomas Munro <tmunro@postgresql.org> 0 siblings, 0 replies; 7+ messages in thread From: Thomas Munro @ 2024-10-17 09:33 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix extreme skew detection in Parallel Hash Join. After repartitioning the inner side of a hash join that would have exceeded the allowed size, we check if all the tuples from a parent partition moved to one child partition. That is evidence that it contains duplicate keys and later attempts to repartition will also fail, so we should give up trying to limit memory (for lack of a better fallback strategy). A thinko prevented the check from working correctly in partition 0 (the one that is partially loaded into memory already). After repartitioning, we should check for extreme skew if the *parent* partition's space_exhausted flag was set, not the child partition's. The consequence was repeated futile repartitioning until per-partition data exceeded various limits including "ERROR: invalid DSA memory alloc request size 1811939328", OS allocation failure, or temporary disk space errors. (We could also do something about some of those symptoms, but that's material for separate patches.) This problem only became likely when PostgreSQL 16 introduced support for Parallel Hash Right/Full Join, allowing NULL keys into the hash table. Repartitioning always leaves NULL in partition 0, no matter how many times you do it, because the hash value is all zero bits. That's unlikely for other hashed values, but they might still have caused wasted extra effort before giving up. Back-patch to all supported releases. Reported-by: Craig Milhiser <craig@milhiser.com> Reviewed-by: Andrei Lepikhov <lepihov@gmail.com> Discussion: https://postgr.es/m/CA%2BwnhO1OfgXbmXgC4fv_uu%3DOxcDQuHvfoQ4k0DFeB0Qqd-X-rQ%40mail.gmail.com Branch ------ REL_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/53edc948580ff2d2fdbe1b34ed39aeffa61d6564 Modified Files -------------- src/backend/executor/nodeHash.c | 17 ++++++++++++----- 1 file changed, 12 insertions(+), 5 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Fix extreme skew detection in Parallel Hash Join. @ 2024-10-17 09:34 Thomas Munro <tmunro@postgresql.org> 0 siblings, 0 replies; 7+ messages in thread From: Thomas Munro @ 2024-10-17 09:34 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix extreme skew detection in Parallel Hash Join. After repartitioning the inner side of a hash join that would have exceeded the allowed size, we check if all the tuples from a parent partition moved to one child partition. That is evidence that it contains duplicate keys and later attempts to repartition will also fail, so we should give up trying to limit memory (for lack of a better fallback strategy). A thinko prevented the check from working correctly in partition 0 (the one that is partially loaded into memory already). After repartitioning, we should check for extreme skew if the *parent* partition's space_exhausted flag was set, not the child partition's. The consequence was repeated futile repartitioning until per-partition data exceeded various limits including "ERROR: invalid DSA memory alloc request size 1811939328", OS allocation failure, or temporary disk space errors. (We could also do something about some of those symptoms, but that's material for separate patches.) This problem only became likely when PostgreSQL 16 introduced support for Parallel Hash Right/Full Join, allowing NULL keys into the hash table. Repartitioning always leaves NULL in partition 0, no matter how many times you do it, because the hash value is all zero bits. That's unlikely for other hashed values, but they might still have caused wasted extra effort before giving up. Back-patch to all supported releases. Reported-by: Craig Milhiser <craig@milhiser.com> Reviewed-by: Andrei Lepikhov <lepihov@gmail.com> Discussion: https://postgr.es/m/CA%2BwnhO1OfgXbmXgC4fv_uu%3DOxcDQuHvfoQ4k0DFeB0Qqd-X-rQ%40mail.gmail.com Branch ------ REL_15_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/1831545ca1860bc8d6faf9c55bbae92a9177e57e Modified Files -------------- src/backend/executor/nodeHash.c | 17 ++++++++++++----- 1 file changed, 12 insertions(+), 5 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Fix extreme skew detection in Parallel Hash Join. @ 2024-10-17 09:34 Thomas Munro <tmunro@postgresql.org> 0 siblings, 0 replies; 7+ messages in thread From: Thomas Munro @ 2024-10-17 09:34 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix extreme skew detection in Parallel Hash Join. After repartitioning the inner side of a hash join that would have exceeded the allowed size, we check if all the tuples from a parent partition moved to one child partition. That is evidence that it contains duplicate keys and later attempts to repartition will also fail, so we should give up trying to limit memory (for lack of a better fallback strategy). A thinko prevented the check from working correctly in partition 0 (the one that is partially loaded into memory already). After repartitioning, we should check for extreme skew if the *parent* partition's space_exhausted flag was set, not the child partition's. The consequence was repeated futile repartitioning until per-partition data exceeded various limits including "ERROR: invalid DSA memory alloc request size 1811939328", OS allocation failure, or temporary disk space errors. (We could also do something about some of those symptoms, but that's material for separate patches.) This problem only became likely when PostgreSQL 16 introduced support for Parallel Hash Right/Full Join, allowing NULL keys into the hash table. Repartitioning always leaves NULL in partition 0, no matter how many times you do it, because the hash value is all zero bits. That's unlikely for other hashed values, but they might still have caused wasted extra effort before giving up. Back-patch to all supported releases. Reported-by: Craig Milhiser <craig@milhiser.com> Reviewed-by: Andrei Lepikhov <lepihov@gmail.com> Discussion: https://postgr.es/m/CA%2BwnhO1OfgXbmXgC4fv_uu%3DOxcDQuHvfoQ4k0DFeB0Qqd-X-rQ%40mail.gmail.com Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/20d94899414ac12634152050d5cd4b676fad22d3 Modified Files -------------- src/backend/executor/nodeHash.c | 17 ++++++++++++----- 1 file changed, 12 insertions(+), 5 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Fix extreme skew detection in Parallel Hash Join. @ 2024-10-17 09:34 Thomas Munro <tmunro@postgresql.org> 0 siblings, 0 replies; 7+ messages in thread From: Thomas Munro @ 2024-10-17 09:34 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix extreme skew detection in Parallel Hash Join. After repartitioning the inner side of a hash join that would have exceeded the allowed size, we check if all the tuples from a parent partition moved to one child partition. That is evidence that it contains duplicate keys and later attempts to repartition will also fail, so we should give up trying to limit memory (for lack of a better fallback strategy). A thinko prevented the check from working correctly in partition 0 (the one that is partially loaded into memory already). After repartitioning, we should check for extreme skew if the *parent* partition's space_exhausted flag was set, not the child partition's. The consequence was repeated futile repartitioning until per-partition data exceeded various limits including "ERROR: invalid DSA memory alloc request size 1811939328", OS allocation failure, or temporary disk space errors. (We could also do something about some of those symptoms, but that's material for separate patches.) This problem only became likely when PostgreSQL 16 introduced support for Parallel Hash Right/Full Join, allowing NULL keys into the hash table. Repartitioning always leaves NULL in partition 0, no matter how many times you do it, because the hash value is all zero bits. That's unlikely for other hashed values, but they might still have caused wasted extra effort before giving up. Back-patch to all supported releases. Reported-by: Craig Milhiser <craig@milhiser.com> Reviewed-by: Andrei Lepikhov <lepihov@gmail.com> Discussion: https://postgr.es/m/CA%2BwnhO1OfgXbmXgC4fv_uu%3DOxcDQuHvfoQ4k0DFeB0Qqd-X-rQ%40mail.gmail.com Branch ------ REL_13_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/45329466fdc955b166de186281f191d09d3a1661 Modified Files -------------- src/backend/executor/nodeHash.c | 17 ++++++++++++----- 1 file changed, 12 insertions(+), 5 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Fix extreme skew detection in Parallel Hash Join. @ 2024-10-17 09:34 Thomas Munro <tmunro@postgresql.org> 0 siblings, 0 replies; 7+ messages in thread From: Thomas Munro @ 2024-10-17 09:34 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix extreme skew detection in Parallel Hash Join. After repartitioning the inner side of a hash join that would have exceeded the allowed size, we check if all the tuples from a parent partition moved to one child partition. That is evidence that it contains duplicate keys and later attempts to repartition will also fail, so we should give up trying to limit memory (for lack of a better fallback strategy). A thinko prevented the check from working correctly in partition 0 (the one that is partially loaded into memory already). After repartitioning, we should check for extreme skew if the *parent* partition's space_exhausted flag was set, not the child partition's. The consequence was repeated futile repartitioning until per-partition data exceeded various limits including "ERROR: invalid DSA memory alloc request size 1811939328", OS allocation failure, or temporary disk space errors. (We could also do something about some of those symptoms, but that's material for separate patches.) This problem only became likely when PostgreSQL 16 introduced support for Parallel Hash Right/Full Join, allowing NULL keys into the hash table. Repartitioning always leaves NULL in partition 0, no matter how many times you do it, because the hash value is all zero bits. That's unlikely for other hashed values, but they might still have caused wasted extra effort before giving up. Back-patch to all supported releases. Reported-by: Craig Milhiser <craig@milhiser.com> Reviewed-by: Andrei Lepikhov <lepihov@gmail.com> Discussion: https://postgr.es/m/CA%2BwnhO1OfgXbmXgC4fv_uu%3DOxcDQuHvfoQ4k0DFeB0Qqd-X-rQ%40mail.gmail.com Branch ------ REL_12_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/4fa80a6d7d4bde83065a5e1d84c01522d2fd368c Modified Files -------------- src/backend/executor/nodeHash.c | 17 ++++++++++++----- 1 file changed, 12 insertions(+), 5 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-10-17 09:34 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-10-17 09:33 pgsql: Fix extreme skew detection in Parallel Hash Join. Thomas Munro <tmunro@postgresql.org> 2024-10-17 09:33 pgsql: Fix extreme skew detection in Parallel Hash Join. Thomas Munro <tmunro@postgresql.org> 2024-10-17 09:33 pgsql: Fix extreme skew detection in Parallel Hash Join. Thomas Munro <tmunro@postgresql.org> 2024-10-17 09:34 pgsql: Fix extreme skew detection in Parallel Hash Join. Thomas Munro <tmunro@postgresql.org> 2024-10-17 09:34 pgsql: Fix extreme skew detection in Parallel Hash Join. Thomas Munro <tmunro@postgresql.org> 2024-10-17 09:34 pgsql: Fix extreme skew detection in Parallel Hash Join. Thomas Munro <tmunro@postgresql.org> 2024-10-17 09:34 pgsql: Fix extreme skew detection in Parallel Hash Join. Thomas Munro <tmunro@postgresql.org>
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