Menu
▾
▴
postgres-xc-general
|
From: Aaron J. <aja...@re...> - 2014-04-29 05:07:32
|
I have a table that I've distributed by some key K. When I want to query by some other dimension Q, the coordinator explain plan indicates that it does a Data Node Scan on *table* "_REMOTE_TABLE_QUERY" Now what I've noticed is that if I have 4 nodes, the coordinator based scan may take 65 seconds, however, the individual date nodes usually finish within 5-10 seconds. The individual explain plains from each data node reveal nothing. So my question is, does the coordinator execute the data node scan serially or in parallel - and if it's serially, is there any thought around how to make it parallel? In the event it is already parallel, is the time differential I'm seeing simply attributed to the coordinator gathering results in preparation to return to the requesting client? Thanks |
|
From: Aaron J. <aja...@re...> - 2014-04-29 05:16:09
|
Also, if it helps, this is actually an operational query, not a data warehouse type query. In this case, we have an object that owns a considerable amount of data below it (in this case, 1:N:M relationships where rows in table A have children in table B and grandchildren in table C). The operation I want to perform is a scalable clone of the data. The actually query looks more like the following: INSERT INTO MyTable SELECT 500 as Id, Foo, Bar from MyTable WHERE Id = $1 For testing purposes (I'm simply tinkering), the data is distributed on Foo and indexed on Id. So in this case I'm copying the "values" from the old entity to the new entity. The plan appears to require the data to be retrieved into the coordinator and then dispersed back down into the data nodes. An optimization for this specific problem would be to push the 'INSERT INTO ... SELECT' directly down into the data nodes since there isn't any inherent benefit to the coordinator consuming the data. The distribution key is identical - so the data will be sent right back to the data node it came from. Any other thoughts on how to make this performant - if not, I'll go back to my tinkering table. ________________________________ From: Aaron Jackson [aja...@re...] Sent: Tuesday, April 29, 2014 12:06 AM To: pos...@li... Subject: [Postgres-xc-general] Data Node Scan Performance I have a table that I've distributed by some key K. When I want to query by some other dimension Q, the coordinator explain plan indicates that it does a Data Node Scan on *table* "_REMOTE_TABLE_QUERY" Now what I've noticed is that if I have 4 nodes, the coordinator based scan may take 65 seconds, however, the individual date nodes usually finish within 5-10 seconds. The individual explain plains from each data node reveal nothing. So my question is, does the coordinator execute the data node scan serially or in parallel - and if it's serially, is there any thought around how to make it parallel? In the event it is already parallel, is the time differential I'm seeing simply attributed to the coordinator gathering results in preparation to return to the requesting client? Thanks |
|
From: amul s. <sul...@ya...> - 2014-04-29 05:23:42
|
>On Tuesday, 29 April 2014 10:38 AM, Aaron Jackson <aja...@re...> wrote: > my question is, does the coordinator execute the data node scan serially >or in parallel - and if it's serially, >is there any thought around how to make it parallel? IMO, scan on data happens independently i.e parallel, the scan result is collected at co-ordinator and returned to client. Referring distributed table using other than distribution key(in your case it Q instead of k), has little penalty. Regards, Amul Sul |
|
From: Aaron J. <aja...@re...> - 2014-04-29 05:30:21
|
Interesting, So, I wonder why I am seeing query times that are more than the sum of the total times required to perform the process without the coordinator. For example, let's say the query was 'SELECT 500 as Id, Foo, Bar from MyTable WHERE Id = 186' - I could perform this query at all 4 nodes and they would take no more than 10 seconds to run individually. However, when performed against the coordinator, this same query takes 65 seconds. That's more than the total aggregate of all data nodes. Any thoughts - is it completely attributed to the coordinator? ________________________________________ From: amul sul [sul...@ya...] Sent: Tuesday, April 29, 2014 12:23 AM To: Aaron Jackson; pos...@li... Subject: Re: [Postgres-xc-general] Data Node Scan Performance >On Tuesday, 29 April 2014 10:38 AM, Aaron Jackson <aja...@re...> wrote: > my question is, does the coordinator execute the data node scan serially >or in parallel - and if it's serially, >is there any thought around how to make it parallel? IMO, scan on data happens independently i.e parallel, the scan result is collected at co-ordinator and returned to client. Referring distributed table using other than distribution key(in your case it Q instead of k), has little penalty. Regards, Amul Sul |
|
From: amul s. <sul...@ya...> - 2014-04-29 06:01:50
|
On Tuesday, 29 April 2014 10:58 AM, Aaron Jackson <aja...@re...> wrote: Interesting, >Any thoughts - is it completely attributed to the coordinator? I am not sure, but in your example, MyTable is distributed on foo and search on ID, if somehow you able to add distribution key search in WHERE condition (eg. ....WHERE Id = 186 AND Foo=xyz ), planner would get help to locate candidate tuple easily. Current situation it scanning all the datanodes & combine result at coordinator. Dose ID is unique in MyTable ? if so can't you can distribute on ID? Regards, Amul Sul |
|
From: Ashutosh B. <ash...@en...> - 2014-04-29 06:05:17
|
Hi Aaron, Can you please take the timing of executing "EXECUTE DIRECT <query to the datanode>" to some datanode. I suspect that the delay you are seeing is added by the sheer communication between coord and datanode. Some of that would be libpq overhead and some of it will be network overhead. On Tue, Apr 29, 2014 at 10:58 AM, Aaron Jackson <aja...@re...>wrote: > Interesting, > > So, I wonder why I am seeing query times that are more than the sum of the > total times required to perform the process without the coordinator. For > example, let's say the query was 'SELECT 500 as Id, Foo, Bar from MyTable > WHERE Id = 186' - I could perform this query at all 4 nodes and they would > take no more than 10 seconds to run individually. However, when performed > against the coordinator, this same query takes 65 seconds. That's more > than the total aggregate of all data nodes. > > Any thoughts - is it completely attributed to the coordinator? > > > ________________________________________ > From: amul sul [sul...@ya...] > Sent: Tuesday, April 29, 2014 12:23 AM > To: Aaron Jackson; pos...@li... > Subject: Re: [Postgres-xc-general] Data Node Scan Performance > > >On Tuesday, 29 April 2014 10:38 AM, Aaron Jackson <aja...@re...> > wrote: > > my question is, does the coordinator execute the data node scan serially > >or in parallel - and if it's serially, > >is there any thought around how to make it parallel? > > IMO, scan on data happens independently i.e parallel, the scan result is > collected at co-ordinator and returned to client. > > Referring distributed table using other than distribution key(in your case > it Q instead of k), has little penalty. > > Regards, > Amul Sul > > > ------------------------------------------------------------------------------ > "Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE > Instantly run your Selenium tests across 300+ browser/OS combos. Get > unparalleled scalability from the best Selenium testing platform available. > Simple to use. Nothing to install. Get started now for free." > http://p.sf.net/sfu/SauceLabs > _______________________________________________ > Postgres-xc-general mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-general > -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company |
|
From: Aaron J. <aja...@re...> - 2014-04-29 06:16:52
|
That was my thinking as well. I indirectly executed the queries independently. Basically, each query takes about 5-10 seconds each for 4 data nodes - specifically, I used psql -h <datanode> -p <port> - to time the individual data node performance individually. So you figure, worst case = 10 seconds x 4 nodes = 40 seconds of aggregate time on a serial request. But I'm seeing 65 seconds which means there's some other overhead that I'm missing. The 65 second aggregate is also the reason why I asked if the requests were parallel or serial because it *feels* serial though it could be other factors. I'll retest and update. ________________________________ From: Ashutosh Bapat [ash...@en...] Sent: Tuesday, April 29, 2014 1:05 AM To: Aaron Jackson Cc: amul sul; pos...@li... Subject: Re: [Postgres-xc-general] Data Node Scan Performance Hi Aaron, Can you please take the timing of executing "EXECUTE DIRECT <query to the datanode>" to some datanode. I suspect that the delay you are seeing is added by the sheer communication between coord and datanode. Some of that would be libpq overhead and some of it will be network overhead. On Tue, Apr 29, 2014 at 10:58 AM, Aaron Jackson <aja...@re...<mailto:aja...@re...>> wrote: Interesting, So, I wonder why I am seeing query times that are more than the sum of the total times required to perform the process without the coordinator. For example, let's say the query was 'SELECT 500 as Id, Foo, Bar from MyTable WHERE Id = 186' - I could perform this query at all 4 nodes and they would take no more than 10 seconds to run individually. However, when performed against the coordinator, this same query takes 65 seconds. That's more than the total aggregate of all data nodes. Any thoughts - is it completely attributed to the coordinator? ________________________________________ From: amul sul [sul...@ya...<mailto:sul...@ya...>] Sent: Tuesday, April 29, 2014 12:23 AM To: Aaron Jackson; pos...@li...<mailto:pos...@li...> Subject: Re: [Postgres-xc-general] Data Node Scan Performance >On Tuesday, 29 April 2014 10:38 AM, Aaron Jackson <aja...@re...<mailto:aja...@re...>> wrote: > my question is, does the coordinator execute the data node scan serially >or in parallel - and if it's serially, >is there any thought around how to make it parallel? IMO, scan on data happens independently i.e parallel, the scan result is collected at co-ordinator and returned to client. Referring distributed table using other than distribution key(in your case it Q instead of k), has little penalty. Regards, Amul Sul ------------------------------------------------------------------------------ "Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE Instantly run your Selenium tests across 300+ browser/OS combos. Get unparalleled scalability from the best Selenium testing platform available. Simple to use. Nothing to install. Get started now for free." http://p.sf.net/sfu/SauceLabs _______________________________________________ Postgres-xc-general mailing list Pos...@li...<mailto:Pos...@li...> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company |
Thanks for helping keep SourceForge clean.
X