Re: [HACKERS] DBT-3 with SF=20 got failed

Tomas Vondra writes: > Anyway, I think you're right we're going in circles here. I think we > both presented all the arguments we had and we still disagree. I'm not > going to continue with this - I'm unlikely to win an argument against > two committers if that

Re: [HACKERS] DBT-3 with SF=20 got failed

On 09/25/2015 02:54 AM, Robert Haas wrote: On Thu, Sep 24, 2015 at 1:58 PM, Tomas Vondra wrote: Meh, you're right - I got the math wrong. It's 1.3% in both cases. However the question still stands - why should we handle the over-estimate in one case and not the

Re: [HACKERS] DBT-3 with SF=20 got failed

Hi, On 09/23/2015 11:25 PM, Tom Lane wrote: Tomas Vondra writes: On 09/11/2015 07:16 PM, Robert Haas wrote: On Fri, Sep 11, 2015 at 1:12 PM, Tomas Vondra wrote: I'm arguing for fixing the existing bug, and then addressing the case

Re: [HACKERS] DBT-3 with SF=20 got failed

On Thu, Sep 24, 2015 at 5:50 AM, Tomas Vondra wrote: > I however quite dislike the dismissal of the possible impact. It should be > the responsibility of the person introducing the change to show that no such > impact actually exists, not just waving it off as

Re: [HACKERS] DBT-3 with SF=20 got failed

On 09/24/2015 01:51 PM, Robert Haas wrote: On Thu, Sep 24, 2015 at 5:50 AM, Tomas Vondra wrote: I however quite dislike the dismissal of the possible impact. It should be the responsibility of the person introducing the change to show that no such impact

Re: [HACKERS] DBT-3 with SF=20 got failed

On Thu, Sep 24, 2015 at 9:49 AM, Tomas Vondra wrote: > So while it does not introduce behavior change in this particular case > (because it fails, as you point out), it introduces a behavior change in > general - it simply triggers behavior that does not happen below

Re: [HACKERS] DBT-3 with SF=20 got failed

Robert Haas writes: > Of course, if we can postpone sizing the hash table until after the > input size is known, as you suggest, then that would be better still > (but not back-patch material). AFAICS, it works that way today as long as the hash fits in memory (ie,

Re: [HACKERS] DBT-3 with SF=20 got failed

On 09/24/2015 05:09 PM, Robert Haas wrote: On Thu, Sep 24, 2015 at 9:49 AM, Tomas Vondra wrote: So while it does not introduce behavior change in this particular case (because it fails, as you point out), it introduces a behavior change in general - it simply

Re: [HACKERS] DBT-3 with SF=20 got failed

On 09/24/2015 07:42 PM, Robert Haas wrote: On Thu, Sep 24, 2015 at 12:40 PM, Tomas Vondra wrote: There are two machines - one with 32GB of RAM and work_mem=2GB, the other one with 256GB of RAM and work_mem=16GB. The machines are hosting about the same data, just

Re: [HACKERS] DBT-3 with SF=20 got failed

On 09/24/2015 07:04 PM, Tom Lane wrote: Tomas Vondra writes: But what about computing the number of expected batches, but always start executing assuming no batching? And only if we actually fill work_mem, we start batching and use the expected number of

Re: [HACKERS] DBT-3 with SF=20 got failed

On 09/24/2015 05:18 PM, Tom Lane wrote: Robert Haas writes: Of course, if we can postpone sizing the hash table until after the input size is known, as you suggest, then that would be better still (but not back-patch material). AFAICS, it works that way today as long

Re: [HACKERS] DBT-3 with SF=20 got failed

Tomas Vondra writes: > But what about computing the number of expected batches, but always > start executing assuming no batching? And only if we actually fill > work_mem, we start batching and use the expected number of batches? Hmm. You would likely be doing

Re: [HACKERS] DBT-3 with SF=20 got failed

On Thu, Sep 24, 2015 at 1:58 PM, Tomas Vondra wrote: > Meh, you're right - I got the math wrong. It's 1.3% in both cases. > > However the question still stands - why should we handle the over-estimate > in one case and not the other? We're wasting the same fraction

Re: [HACKERS] DBT-3 with SF=20 got failed

On Thu, Sep 24, 2015 at 12:40 PM, Tomas Vondra wrote: > There are two machines - one with 32GB of RAM and work_mem=2GB, the other > one with 256GB of RAM and work_mem=16GB. The machines are hosting about the > same data, just scaled accordingly (~8x more data on the

Re: [HACKERS] DBT-3 with SF=20 got failed

Tomas Vondra writes: > On 09/11/2015 07:16 PM, Robert Haas wrote: >> On Fri, Sep 11, 2015 at 1:12 PM, Tomas Vondra >> wrote: >>> I'm arguing for fixing the existing bug, and then addressing the case of >>> over-estimation separately,

Re: [HACKERS] DBT-3 with SF=20 got failed

On Fri, Sep 11, 2015 at 1:12 PM, Tomas Vondra wrote: > I'm arguing for fixing the existing bug, and then addressing the case of > over-estimation separately, with proper analysis. Well, this is part of how we're looking it differently. I think the bug is "we're

Re: [HACKERS] DBT-3 with SF=20 got failed

On 09/11/2015 07:16 PM, Robert Haas wrote: On Fri, Sep 11, 2015 at 1:12 PM, Tomas Vondra wrote: I'm arguing for fixing the existing bug, and then addressing the case of over-estimation separately, with proper analysis. Well, this is part of how we're looking

Re: [HACKERS] DBT-3 with SF=20 got failed

On Wed, Sep 9, 2015 at 11:54 AM, Tomas Vondra wrote: > Secondly, we limit the number of buckets to INT_MAX, so about 16GB (because > buckets are just pointers). No matter how awful estimate you get (or how > insanely high you set work_mem) you can't exceed this. OK,

Re: [HACKERS] DBT-3 with SF=20 got failed

On 09/11/2015 06:55 PM, Robert Haas wrote: On Wed, Sep 9, 2015 at 11:54 AM, Tomas Vondra wrote: Secondly, we limit the number of buckets to INT_MAX, so about 16GB (because buckets are just pointers). No matter how awful estimate you get (or how insanely high you

Re: [HACKERS] DBT-3 with SF=20 got failed

On Tue, Sep 8, 2015 at 5:02 PM, Tomas Vondra wrote: > Also, I'm not sure what other places do you have in mind (could you list > some examples?) but I'd bet we limit the allocation to 1GB because of the > palloc() limit and not because of fear of over-estimates. I

Re: [HACKERS] DBT-3 with SF=20 got failed

On 09/09/2015 03:55 PM, Robert Haas wrote: On Tue, Sep 8, 2015 at 5:02 PM, Tomas Vondra wrote: Also, I'm not sure what other places do you have in mind (could you list some examples?) but I'd bet we limit the allocation to 1GB because of the palloc() limit and not

Re: [HACKERS] DBT-3 with SF=20 got failed

> Hello KaiGai-san, > > I've discovered a bug in the proposed patch - when resetting the hash > table after the first batch, it does this: > > memset(hashtable->buckets, 0, sizeof(nbuckets * sizeof(HashJoinTuple))); > > The first 'sizeof' is bogus, so this only zeroes the first 8 bytes of > the

Re: [HACKERS] DBT-3 with SF=20 got failed

On Tue, Sep 8, 2015 at 8:28 AM, Kouhei Kaigai wrote: >> Hello KaiGai-san, >> >> I've discovered a bug in the proposed patch - when resetting the hash >> table after the first batch, it does this: >> >> memset(hashtable->buckets, 0, sizeof(nbuckets * sizeof(HashJoinTuple)));

Re: [HACKERS] DBT-3 with SF=20 got failed

On 09/08/2015 08:44 PM, Robert Haas wrote: On Tue, Sep 8, 2015 at 8:28 AM, Kouhei Kaigai wrote: Hello KaiGai-san, I've discovered a bug in the proposed patch - when resetting the hash table after the first batch, it does this: memset(hashtable->buckets, 0,

Re: [HACKERS] DBT-3 with SF=20 got failed

Hello KaiGai-san, I've discovered a bug in the proposed patch - when resetting the hash table after the first batch, it does this: memset(hashtable->buckets, 0, sizeof(nbuckets * sizeof(HashJoinTuple))); The first 'sizeof' is bogus, so this only zeroes the first 8 bytes of the array

Re: [HACKERS] DBT-3 with SF=20 got failed

Hello KaiGai-san, On 08/21/2015 02:28 AM, Kouhei Kaigai wrote: ... But what is the impact on queries that actually need more than 1GB of buckets? I assume we'd only limit the initial allocation and still allow the resize based on the actual data (i.e. the 9.5 improvement), so the queries would

Re: [HACKERS] DBT-3 with SF=20 got failed

Hello KaiGai-san, On 08/21/2015 02:28 AM, Kouhei Kaigai wrote: ... But what is the impact on queries that actually need more than 1GB of buckets? I assume we'd only limit the initial allocation and still allow the resize based on the actual data (i.e. the 9.5 improvement), so the

Re: [HACKERS] DBT-3 with SF=20 got failed

On 08/19/2015 03:53 PM, Tom Lane wrote: I don't see anything very wrong with constraining the initial allocation to 1GB, or even less. That will prevent consuming insane amounts of work_mem when the planner's rows estimate is too high rather than too low. And we do have the ability to

Re: [HACKERS] DBT-3 with SF=20 got failed

Hi, On 08/19/2015 03:53 PM, Tom Lane wrote: I don't see anything very wrong with constraining the initial allocation to 1GB, or even less. That will prevent consuming insane amounts of work_mem when the planner's rows estimate is too high rather than too low. And we do have the ability to

Re: [HACKERS] DBT-3 with SF=20 got failed

2015-08-19 20:12 GMT+09:00 Simon Riggs si...@2ndquadrant.com: On 12 June 2015 at 00:29, Tomas Vondra tomas.von...@2ndquadrant.com wrote: I see two ways to fix this: (1) enforce the 1GB limit (probably better for back-patching, if that's necessary) (2) make it work with hash tables

Re: [HACKERS] DBT-3 with SF=20 got failed

On 19 August 2015 at 12:55, Kohei KaiGai kai...@kaigai.gr.jp wrote: 2015-08-19 20:12 GMT+09:00 Simon Riggs si...@2ndquadrant.com: On 12 June 2015 at 00:29, Tomas Vondra tomas.von...@2ndquadrant.com wrote: I see two ways to fix this: (1) enforce the 1GB limit (probably better for

Re: [HACKERS] DBT-3 with SF=20 got failed

Simon Riggs si...@2ndquadrant.com writes: On 19 August 2015 at 12:55, Kohei KaiGai kai...@kaigai.gr.jp wrote: Please don't be rush. :-) Please explain what rush you see? Yours. You appear to be in a hurry to apply patches that there's no consensus on. It is not difficult to replace

Re: [HACKERS] DBT-3 with SF=20 got failed

On 12 June 2015 at 00:29, Tomas Vondra tomas.von...@2ndquadrant.com wrote: I see two ways to fix this: (1) enforce the 1GB limit (probably better for back-patching, if that's necessary) (2) make it work with hash tables over 1GB I'm in favor of (2) if there's a good way to do that.

Re: [HACKERS] DBT-3 with SF=20 got failed

2015-08-19 21:29 GMT+09:00 Simon Riggs si...@2ndquadrant.com: On 19 August 2015 at 12:55, Kohei KaiGai kai...@kaigai.gr.jp wrote: 2015-08-19 20:12 GMT+09:00 Simon Riggs si...@2ndquadrant.com: On 12 June 2015 at 00:29, Tomas Vondra tomas.von...@2ndquadrant.com wrote: I see two ways to

Re: [HACKERS] DBT-3 with SF=20 got failed

On 19 August 2015 at 14:53, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On 19 August 2015 at 12:55, Kohei KaiGai kai...@kaigai.gr.jp wrote: Please don't be rush. :-) Please explain what rush you see? Yours. You appear to be in a hurry to apply patches

Re: [HACKERS] DBT-3 with SF=20 got failed

Hi, On 08/19/2015 01:55 PM, Kohei KaiGai wrote: Merge Join (cost=25374644.08..1160509591.61 rows=60521928028 width=24) (actual time=138347.979..491889.343 rows=776157676 loops=1) Merge Cond: (ws1.ws_order_number = ws2.ws_order_number) Join Filter: (ws1.ws_warehouse_sk

Re: [HACKERS] DBT-3 with SF=20 got failed

Hello KaiGain-san, On 08/19/2015 03:19 PM, Kohei KaiGai wrote: Unless we have no fail-safe mechanism when planner estimated too large number of tuples than actual needs, a strange estimation will consume massive amount of RAMs. It's a bad side effect. My previous patch didn't pay attention to

Re: [HACKERS] DBT-3 with SF=20 got failed

On 12 June 2015 at 02:40, Kohei KaiGai kai...@kaigai.gr.jp wrote: 2015-06-11 23:28 GMT+09:00 Robert Haas robertmh...@gmail.com: On Wed, Jun 10, 2015 at 10:57 PM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: The attached patch replaces this palloc0() by MemoryContextAllocHuge() + memset().

Re: [HACKERS] DBT-3 with SF=20 got failed

Hi, On 08/20/2015 04:15 AM, Tomas Vondra wrote: Hello KaiGain-san, On 08/19/2015 03:19 PM, Kohei KaiGai wrote: Unless we have no fail-safe mechanism when planner estimated too large number of tuples than actual needs, a strange estimation will consume massive amount of RAMs. It's a bad side

Re: [HACKERS] DBT-3 with SF=20 got failed

On 06/11/2015 09:53 AM, Kouhei Kaigai wrote: curious: what was work_mem set to? work_mem=48GB My machine mounts 256GB physical RAM. work_mem can be allocated several times per backend. Nodes like sort and hash_aggregate may each allocate that much. You should set work_mem to a fraction of

Re: [HACKERS] DBT-3 with SF=20 got failed

On Wed, Jun 10, 2015 at 10:57 PM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: The attached patch replaces this palloc0() by MemoryContextAllocHuge() + memset(). Indeed, this hash table is constructed towards the relation with nrows=119994544, so, it is not strange even if hash-slot itself is

Re: [HACKERS] DBT-3 with SF=20 got failed

2015-06-11 23:20 GMT+09:00 Jan Wieck j...@wi3ck.info: On 06/11/2015 09:53 AM, Kouhei Kaigai wrote: curious: what was work_mem set to? work_mem=48GB My machine mounts 256GB physical RAM. work_mem can be allocated several times per backend. Nodes like sort and hash_aggregate may each

Re: [HACKERS] DBT-3 with SF=20 got failed

Tomas Vondra tomas.von...@2ndquadrant.com writes: Interestingly, the hash code checks for INT_MAX overflows on a number of places, but does not check for this ... Yeah, and at least at one time there were checks to prevent the hash table request from exceeding MaxAllocSize. Did those get

Re: [HACKERS] DBT-3 with SF=20 got failed

Hi, On 06/11/15 16:20, Jan Wieck wrote: On 06/11/2015 09:53 AM, Kouhei Kaigai wrote: curious: what was work_mem set to? work_mem=48GB My machine mounts 256GB physical RAM. work_mem can be allocated several times per backend. Nodes like sort and hash_aggregate may each allocate that much.

Re: [HACKERS] DBT-3 with SF=20 got failed

2015-06-11 23:33 GMT+09:00 Tomas Vondra tomas.von...@2ndquadrant.com: Hi, On 06/11/15 16:20, Jan Wieck wrote: On 06/11/2015 09:53 AM, Kouhei Kaigai wrote: curious: what was work_mem set to? work_mem=48GB My machine mounts 256GB physical RAM. work_mem can be allocated several times

Re: [HACKERS] DBT-3 with SF=20 got failed

2015-06-11 23:28 GMT+09:00 Robert Haas robertmh...@gmail.com: On Wed, Jun 10, 2015 at 10:57 PM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: The attached patch replaces this palloc0() by MemoryContextAllocHuge() + memset(). Indeed, this hash table is constructed towards the relation with

Re: [HACKERS] DBT-3 with SF=20 got failed

On Wed, Jun 10, 2015 at 9:57 PM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: Hello, I got the following error during DBT-3 benchmark with SF=20. psql:query21.sql:50: ERROR: invalid memory alloc request size 1073741824 psql:query21.sql:50: ERROR: invalid memory alloc request size

Re: [HACKERS] DBT-3 with SF=20 got failed

To: Kaigai Kouhei(海外 浩平) Cc: pgsql-hackers@postgreSQL.org Subject: Re: [HACKERS] DBT-3 with SF=20 got failed On Wed, Jun 10, 2015 at 9:57 PM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: Hello, I got the following error during DBT-3 benchmark with SF=20. psql:query21.sql:50: ERROR

Re: [HACKERS] DBT-3 with SF=20 got failed

On 06/11/15 16:54, Tom Lane wrote: Tomas Vondra tomas.von...@2ndquadrant.com writes: Interestingly, the hash code checks for INT_MAX overflows on a number of places, but does not check for this ... Yeah, and at least at one time there were checks to prevent the hash table request from

[HACKERS] DBT-3 with SF=20 got failed

Hello, I got the following error during DBT-3 benchmark with SF=20. psql:query21.sql:50: ERROR: invalid memory alloc request size 1073741824 psql:query21.sql:50: ERROR: invalid memory alloc request size 1073741824 It looks to me Hash node tries to 1GB area using palloc0(), but it exceeds