Menu
▾
▴
postgres-xc-developers — Postgres-XC hackers and developers
You can subscribe to this list here.
| 2010 |
Jan
|
Feb
|
Mar
|
Apr
(10) |
May
(17) |
Jun
(3) |
Jul
|
Aug
|
Sep
(8) |
Oct
(18) |
Nov
(51) |
Dec
(74) |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2011 |
Jan
(47) |
Feb
(44) |
Mar
(44) |
Apr
(102) |
May
(35) |
Jun
(25) |
Jul
(56) |
Aug
(69) |
Sep
(32) |
Oct
(37) |
Nov
(31) |
Dec
(16) |
| 2012 |
Jan
(34) |
Feb
(127) |
Mar
(218) |
Apr
(252) |
May
(80) |
Jun
(137) |
Jul
(205) |
Aug
(159) |
Sep
(35) |
Oct
(50) |
Nov
(82) |
Dec
(52) |
| 2013 |
Jan
(107) |
Feb
(159) |
Mar
(118) |
Apr
(163) |
May
(151) |
Jun
(89) |
Jul
(106) |
Aug
(177) |
Sep
(49) |
Oct
(63) |
Nov
(46) |
Dec
(7) |
| 2014 |
Jan
(65) |
Feb
(128) |
Mar
(40) |
Apr
(11) |
May
(4) |
Jun
(8) |
Jul
(16) |
Aug
(11) |
Sep
(4) |
Oct
(1) |
Nov
(5) |
Dec
(16) |
| 2015 |
Jan
(5) |
Feb
|
Mar
(2) |
Apr
(5) |
May
(4) |
Jun
(12) |
Jul
|
Aug
|
Sep
|
Oct
|
Nov
|
Dec
(4) |
| 2019 |
Jan
|
Feb
|
Mar
|
Apr
|
May
|
Jun
|
Jul
(2) |
Aug
|
Sep
|
Oct
|
Nov
|
Dec
|
|
From: Eli S. <eli...@gr...> - 2019-07-02 07:50:01
|
hey, I would like to know what is the status of the support for LISTEN & NOTIFY commands - and future plans. If i understand correctly - currently, it only works if the server that notifies of the event is the same server that the client is listening on. Of course this will not work in a 2+ nodes cluster. So in order to support this feature, we need to make sure that the notifications are sent to all servers, which in turn will send the notifications to the clients listening on the specific server queue. thank you -- Eli Shemer | Software Engineer | HexaTier |Office: +972-3-687-0033 | Mobile:+ 972-54-617-5724| eli...@he... <eli...@gr...> www. <http://www.greensql.com/>hexatier <eli...@gr...>.com |
|
From: Eli S. <eli...@gr...> - 2019-07-02 07:45:43
|
hey, I would like to know what is the status of the support for LISTEN & NOTIFY commands - and future plans. If i understand correctly - currently, it only works if the server that notifies of the event is the same server that the client is listening on. Of course this will not work in a 2+ nodes cluster. So in order to support this feature, we need to make sure that the notifications are sent to all servers, which in turn will send the notifications to the clients listening on the specific server queue. thank you -- Eli Shemer | Software Engineer | HexaTier |Office: +972-3-687-0033 | Mobile:+ 972-54-617-5724| eli...@he... <eli...@gr...> www. <http://www.greensql.com/>hexatier <eli...@gr...>.com |
|
From: Koichi S. <koi...@gm...> - 2015-12-15 05:00:50
|
If any one of the tables is distributed, NodeA must have a subset of such table raws and they were lost. You need to restore them. With there could be a way to do this manually, although it is tricky and you need to be very familiar with XC internals. Unfortunately, XC does not provide such built-in feature. Thank you; --- Koichi Suzuki https://www.linkedin.com/in/koichidbms 2015-12-15 7:20 GMT+09:00 Timur Allamiyarov <tim...@gm...>: > Hi guys, > > Say I have 3 nodes in my cluster: NodeA (which runs GTM, GTM_proxy, 1 > datanode and 1 coordinator), NodeB and Node C, each running GTM_Proxy, 1 > datanode and 1 coordinator, and > GTM Standby. > > For some reason main NodeA goes down and becomes physically unavailable > (someone shuts it down by mistake or something). > > Is there any way for nodes B and C to negotiate of who's going to become > the main node - promote GTM standy and restart GTM_Proxy? > Does Postgres-XC have any consensus algorithm implemented that could help > solve this issue? > > Thank you. > -Tim > > > > > ------------------------------------------------------------------------------ > > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > |
|
From: Timur A. <tim...@gm...> - 2015-12-14 22:20:20
|
Hi guys, Say I have 3 nodes in my cluster: NodeA (which runs GTM, GTM_proxy, 1 datanode and 1 coordinator), NodeB and Node C, each running GTM_Proxy, 1 datanode and 1 coordinator, and GTM Standby. For some reason main NodeA goes down and becomes physically unavailable (someone shuts it down by mistake or something). Is there any way for nodes B and C to negotiate of who's going to become the main node - promote GTM standy and restart GTM_Proxy? Does Postgres-XC have any consensus algorithm implemented that could help solve this issue? Thank you. -Tim |
|
From: Koichi S. <koi...@gm...> - 2015-12-03 23:10:10
|
What symptom did you have? Didn't DML work against tables? Could you have table's grant list with \z command in psql? If you find that the table is not read only, could you try to create new table for the test? Another information will be found in $PGDATA/postgresql.conf at each node. If you have rebooted your machine several time, each node should have done crash-recovery process and you may find what happened in that log at each node, including both datanode and coordinator. GTM and GTM_Proxy does not need any recovery actions so you may not find useful information in these logs. BTY, current head of Postgres-XC REL1_2_STABLE contains many more fixes for stabilization. I'm about to publish release 1.2.2. All the code is ready and now I'm preparing release note and merging document updates from PostgreSQL minor releases. It is at https://github.com/postgres-x2/postgres-x2. Thank you very much and good luck. --- Koichi Suzuki --- Koichi Suzuki https://www.linkedin.com/in/koichidbms 2015-12-03 14:18 GMT-08:00 Timur Allamiyarov <tim...@gm...>: > Good day, > > I have recently switched to postgresxc v1.2.1, and I find it great. In my > test scenario, I have created GTM, GTM Proxy, Coordinator and Datanode all > running on one server. I then created a new database: > > # create database test_db encoding='UFT8'; > > +several simple tables. All works like a charm. > > However, what I discovered is that for some reason when I re-booted my > server several times (trying to imitate some abnormal conditions) all my > tables in that particular database (test_db) had become read-only. > > Initially I though - may be postgres is in the recovery mode, so I ran: > > postgres=# SELECT pg_is_in_recovery(); > pg_is_in_recovery > ------------------- > f > (1 row) > > I then executed 'SET transaction_read_only = off' and it didn't help > either. > > Any idea what could cause that problem? What will be the solution to fix > it should I face with that issue again? > Thank you. > > -Timur > > > ------------------------------------------------------------------------------ > Go from Idea to Many App Stores Faster with Intel(R) XDK > Give your users amazing mobile app experiences with Intel(R) XDK. > Use one codebase in this all-in-one HTML5 development environment. > Design, debug & build mobile apps & 2D/3D high-impact games for multiple > OSs. > http://pubads.g.doubleclick.net/gampad/clk?id=254741911&iu=/4140 > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > |
|
From: Timur A. <tim...@gm...> - 2015-12-03 22:18:11
|
Good day, I have recently switched to postgresxc v1.2.1, and I find it great. In my test scenario, I have created GTM, GTM Proxy, Coordinator and Datanode all running on one server. I then created a new database: # create database test_db encoding='UFT8'; +several simple tables. All works like a charm. However, what I discovered is that for some reason when I re-booted my server several times (trying to imitate some abnormal conditions) all my tables in that particular database (test_db) had become read-only. Initially I though - may be postgres is in the recovery mode, so I ran: postgres=# SELECT pg_is_in_recovery(); pg_is_in_recovery ------------------- f (1 row) I then executed 'SET transaction_read_only = off' and it didn't help either. Any idea what could cause that problem? What will be the solution to fix it should I face with that issue again? Thank you. -Timur |
|
From: Manikandan S. <ma...@vt...> - 2015-06-04 09:10:27
|
Thanks Abbas and Ashutosh! I'll look into the options that you provided to improve the performance here.
Thanks
Mani
On Thu, Jun 4, 2015 at 1:53 AM -0700, "Abbas Butt" <abb...@en...> wrote:
On Wed, Jun 3, 2015 at 8:57 PM, Manikandan Soundarapandian <ma...@vt...> wrote:
Hi, Please see below
\d+ Table1
Table "Table1"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+-----------------------+-----------+----------+--------------+-------------
id | numeric | | main | 1000 |
age | numeric | | main | 1000 |
gender | numeric | | main | 1000 |
col1 | character varying(5) | | extended | 1000 |
Indexes:
"index_age_table1" btree (age)
"index_col1_table1" btree (col1)
"index_gender_table1" btree (gender)
"index_id_table1" btree (id)
Has OIDs: no
Distribute By: HASH(id)
Location Nodes: ALL DATANODES
\d+ Table2
Table "Table2"
Column | Type | Modifiers | Storage | Stats target | Description
-----------------+---------+-----------+---------+--------------+-------------
id | numeric | | main | 1000 |
rep | numeric | | main | 1000 |
time | numeric | | main | 1000 |
Indexes:
"index_time_table2" btree (time)
"index_id_table2" btree (id)
"index_rep_table2" btree (rep)
Has OIDs: no
Distribute By: HASH(id)
Location Nodes: ALL DATANODES
Explain verbose of the query:
GroupAggregate (cost=0.07..0.10 rows=1 width=56)
Output: d.col1, round(((count(d.id) / 10))::double precision)
-> Sort (cost=0.07..0.08 rows=1 width=56)
Output: d.col1, d.id
Sort Key: d.col1
-> Hash Join (cost=0.01..0.06 rows=1 width=56)
Output: d.col1, d.id
Hash Cond: (d.id = i.id)
-> Data Node Scan on Table1 "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=1000 width=56)
Output: d.col1, d.id
Node/s: datanode_c2_d1, datanode_c3_d1, datanode_c4_d1, datanode_c5_d1
Remote query: SELECT col1, id FROM ONLY Table1 d WHERE true
-> Hash (cost=0.00..0.00 rows=1000 width=32)
Output: i.id
-> Data Node Scan on Table2 "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=1000 width=32)
Output: i.id
Node/s: datanode_c2_d1, datanode_c3_d1, datanode_c4_d1, datanode_c5_d1
Remote query: SELECT id, time FROM ONLY Table2 i WHERE true
Coordinator quals: (((i.time)::double precision <= trunc(((('now'::cstring)::date - to_date('01 Jan 2000'::text, 'DD Mon YYYY'::text)))::double precis
ion)) AND ((i.time)::double precision >= trunc(((((('now'::cstring)::date - to_date('01 Jan 2000'::text, 'DD Mon YYYY'::text)) + 1) - 365))::double precision)))
Also, one weird thing that I noticed is if I don't give the 'trunc()' date manipulation in the query and just gave some valid numbers in the between clause like 'between 5266 and 5632', am getting a different query plan and the query executes in just a few seconds.
The reason is that in this case the join gets pushed down to datanodes, hence the major computation is done in parallel and only qualifying results are transferred to the coordinator. Like Ashutosh has suggested you can look to revise the planning of mutable functions in this case to get better performance.
Another option is to rewrite the query in the following manner
Get current date in a variable curDate
Compute TRUNC(curDate - TO_DATE('01 Jan 2000','DD Mon YYYY')+1-365) and save in startDate variable
Compute TRUNC(curDate - TO_DATE('01 Jan 2000','DD Mon YYYY')) and save the result in endDate
then re-write your query using the already computed values.
You can use libpq based C program to do this.
The following is the query planner output when I give the numbers directly in the between clause.
GroupAggregate (cost=49.83..57.35 rows=1 width=56)
Output: d.col1, round(((count((count(d.id))) / 10))::double precision)
-> Sort (cost=49.83..52.33 rows=1000 width=56)
Output: d.col1, (count(d.id))
Sort Key: d.col1
-> Data Node Scan on "__REMOTE_GROUP_QUERY__" (cost=0.00..0.00 rows=1000 width=56)
Output: d.col1, (count(d.id))
Node/s: datanode_c2_d1, datanode_c3_d1, datanode_c4_d1, datanode_c5_d1
Remote query: SELECT l.a_1, count(l.a_2) FROM ((SELECT d.col1, d.id FROM ONLY Table1 d WHERE true) l(a_1, a_2) JOIN (SELECT i.id FROM ONLY Table2 i WHERE ((i.time >= 5266::numeric) AND (i.time <= 5632::numeric))) r(a_1) ON (true)) WHERE (l.a_2 = r.a_1) GROUP BY 1 ORDER BY 1 DESC
But my requirement is with the trunc() date manipulation.
Thanks
On Wed, 2015-06-03 at 18:08 -0700, Abbas Butt wrote:
On Wed, Jun 3, 2015 at 5:10 PM, Manikandan Soundarapandian <ma...@vt...> wrote:
Hi Abbas,
I just looked at the planner for the query that am running. This is the query that am running,
SELECT d.col1 RVALUE, ROUND(COUNT(d.col2)/10) COUNT FROM Table1 d, Table2 i WHERE d.id=i.id AND i.time BETWEEN TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')+1-365) AND TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')) GROUP BY d.col1 ORDER BY d.col1 DESC;
And the query plan for a configuration with 4 datanodes and 1 coordinator,
GroupAggregate (cost=0.07..0.10 rows=1 width=56)
-> Sort (cost=0.07..0.08 rows=1 width=56)
Sort Key: d.col1
-> Hash Join (cost=0.01..0.06 rows=1 width=56)
Hash Cond: (d.id = i.id)
-> Data Node Scan on Table1 "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=1000 width=56)
Node/s: datanode_c2_d1, datanode_c3_d1, datanode_c4_d1, datanode_c5_d1
-> Hash (cost=0.00..0.00 rows=1000 width=32)
-> Data Node Scan on Table2 "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=1000 width=32)
Node/s: datanode_c2_d1, datanode_c3_d1, datanode_c4_d1, datanode_c5_d1
Coordinator quals: (((i.time)::double precision <= trunc(((('now'::cstring)::date - to_date('01 Jan 2000'::text, 'DD Mon YYYY'::text)))::double precisio
n)) AND ((i.time)::double precision >= trunc(((((('now'::cstring)::date - to_date('01 Jan 2000'::text, 'DD Mon YYYY'::text)) + 1) - 365))::double precision)))
Table1 contains 22 million records and Table2 contains 10 million records.
This query took several minutes (almost 35) to get results and one third of that time is spent in FetchTuple. Do you see something wrong with the query plan?
Can you provide output of
\d+ Table1
\d+ Table2
I am interested in the distribution column of the tables.
Also please provide the output of EXPLAIN VERBOSE on the query.
Since the multiplexed IO isn't the real bottleneck here, my proposal would be to change the way how populating the TupleStore at the coordinator is working and introduce parallelism there. Is this a right direction? Please let me know your thoughts.
Thanks
Mani
On Wed, 2015-06-03 at 01:49 -0700, Abbas Butt wrote:
On Tue, Jun 2, 2015 at 10:17 PM, Manikandan Soundarapandian <ma...@vt...> wrote:
Hi Abbas,
Thanks for the reply. I understand how pgxc_node_receive is working to
get incoming data from the datanodes.
Yes, I have made experiments to study the postgres-xc system in a setup
with 12 datanodes and a coordinator and performed join, groupby and
order by operations on two tables with around 20 million and 10 million
records respectively. I have found that around 20-30% of the time is
spent in the 'FetchTuple' method which reads into the provided tupleslot
one datarow at a time from the combiner's buffer which is in turn filled
by the pgxc_node_receive method.
XC planner pushes parts of query processing to datanodes to speed things up.
In the queries you tried, did the query planner perform this optimization?
If not then you can gain advantage by improving the planner to generate optimized plans that avoid UN-necessary materialization at the coordinator.
This will bring the FetchTuple time down since there will be less tuples to fetch.
If the planner is already producing optimized plans, then what's your proposal?
Thanks
On Wed, 2015-06-03 at 09:45 +0500, Abbas Butt wrote:
>
>
> On Wed, Jun 3, 2015 at 4:11 AM, Manikandan Soundarapandian
> <ma...@vt...> wrote:
> Hi,
>
>
> I am a graduate student working on my research in parallel
> databases. I would like to know how the postgres-xc
> coordinator works. I understand that the datanodes run the
> query in parallel and the results are collected by the
> coordinator which runs any more computation that is required
> or just provides the output to the client that requested the
> query. I would like to know whether the coordinator does this
> data collection from datanodes in a sequential fashion?
>
>
> The coordinator uses multiplexed IO using select on all fds of
> datanodes. For more details please see pgxc_node_receive function in
> pgxcnode.c. The loop for reading data on all set fds is sequential,
> but the coordinator does not wait for data from the datanode to which
> the coordinator had sent the query first.
>
> For example, lets consider we want to run the query on table
> table_x which is hash distributed among 10 datanodes,
> select count(*) from table_x;
> Each datanode will run the query and give their local counts
> and the coordinator has to collect the individual counts and
> come up with the final count before sending the output. Is the
> data collection process at the coordinator done in a
> sequential fashion? I am actually looking to introduce some
> kind of parallelism in this data collection if it is
> sequential and do performance studies. Please clarify.
>
>
> To improve performance of any system, first study the bottleneck, and
> target to widen that. Have you done any study of Postgres-XC to find
> where the performance bottleneck is?
>
>
>
> --
> Thanks
> Mani
> Department of Computer Science
> Virginia Tech
>
> ------------------------------------------------------------------------------
>
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>
>
>
>
>
> --
> --
> Abbas
> Architect
>
>
> Ph: 92.334.5100153
>
> Skype ID: gabbasb
>
> www.enterprisedb.com
>
> Follow us on Twitter
> @EnterpriseDB
>
> Visit EnterpriseDB for tutorials, webinars, whitepapers and more
>
--
--
Abbas
Architect
Ph: 92.334.5100153
Skype ID: gabbasb
www.enterprisedb.com
Follow us on Twitter
@EnterpriseDB
Visit EnterpriseDB for tutorials, webinars, whitepapers and more
--
--
Abbas
Architect
Ph: 92.334.5100153
Skype ID: gabbasb
www.enterprisedb.com
Follow us on Twitter
@EnterpriseDB
Visit EnterpriseDB for tutorials, webinars, whitepapers and more
--
--
Abbas
Architect
Ph: 92.334.5100153
Skype ID: gabbasb
www.enterprisedb.com
Follow us on Twitter
@EnterpriseDB
Visit EnterpriseDB for tutorials, webinars, whitepapers and more |
|
From: Abbas B. <abb...@en...> - 2015-06-04 08:53:41
|
On Wed, Jun 3, 2015 at 8:57 PM, Manikandan Soundarapandian <ma...@vt...>
wrote:
> Hi, Please see below
>
> \d+ Table1
> Table "Table1"
> Column | Type | Modifiers | Storage | Stats
> target | Description
>
> --------------+-----------------------+-----------+----------+--------------+-------------
> id | numeric | | main | 1000
> |
> age | numeric | | main |
> 1000 |
> gender | numeric | | main |
> 1000 |
> col1 | character varying(5) | | extended | 1000 |
> Indexes:
> "index_age_table1" btree (age)
> "index_col1_table1" btree (col1)
> "index_gender_table1" btree (gender)
> "index_id_table1" btree (id)
> Has OIDs: no
> Distribute By: HASH(id)
> Location Nodes: ALL DATANODES
>
> \d+ Table2
> Table "Table2"
> Column | Type | Modifiers | Storage | Stats target |
> Description
>
> -----------------+---------+-----------+---------+--------------+-------------
> id | numeric | | main | 1000 |
> rep | numeric | | main | 1000 |
> time | numeric | | main | 1000 |
>
> Indexes:
> "index_time_table2" btree (time)
> "index_id_table2" btree (id)
> "index_rep_table2" btree (rep)
> Has OIDs: no
> Distribute By: HASH(id)
> Location Nodes: ALL DATANODES
>
> Explain verbose of the query:
> GroupAggregate (cost=0.07..0.10 rows=1 width=56)
> Output: d.col1, round(((count(d.id) / 10))::double precision)
> -> Sort (cost=0.07..0.08 rows=1 width=56)
> Output: d.col1, d.id
> Sort Key: d.col1
> -> Hash Join (cost=0.01..0.06 rows=1 width=56)
> Output: d.col1, d.id
> Hash Cond: (d.id = i.id)
> -> Data Node Scan on Table1 "_REMOTE_TABLE_QUERY_"
> (cost=0.00..0.00 rows=1000 width=56)
> Output: d.col1, d.id
> Node/s: datanode_c2_d1, datanode_c3_d1,
> datanode_c4_d1, datanode_c5_d1
> Remote query: SELECT col1, id FROM ONLY Table1 d
> WHERE true
> -> Hash (cost=0.00..0.00 rows=1000 width=32)
> Output: i.id
> -> Data Node Scan on Table2 "_REMOTE_TABLE_QUERY_"
> (cost=0.00..0.00 rows=1000 width=32)
> Output: i.id
> Node/s: datanode_c2_d1, datanode_c3_d1,
> datanode_c4_d1, datanode_c5_d1
> Remote query: SELECT id, time FROM ONLY Table2
> i WHERE true
> Coordinator quals: (((i.time)::double precision
> <= trunc(((('now'::cstring)::date - to_date('01 Jan 2000'::text, 'DD Mon
> YYYY'::text)))::double precis
> ion)) AND ((i.time)::double precision >= trunc(((((('now'::cstring)::date
> - to_date('01 Jan 2000'::text, 'DD Mon YYYY'::text)) + 1) - 365))::double
> precision)))
>
> Also, one weird thing that I noticed is if I don't give the 'trunc()' date
> manipulation in the query and just gave some valid numbers in the between
> clause like 'between 5266 and 5632', am getting a different query plan
> and the query executes in just a few seconds.
>
The reason is that in this case the join gets pushed down to datanodes,
hence the major computation is done in parallel and only qualifying results
are transferred to the coordinator. Like Ashutosh has suggested you can
look to revise the planning of mutable functions in this case to get better
performance.
Another option is to rewrite the query in the following manner
Get current date in a variable curDate
Compute TRUNC(curDate - TO_DATE('01 Jan 2000','DD Mon YYYY')+1-365) and
save in startDate variable
Compute TRUNC(curDate - TO_DATE('01 Jan 2000','DD Mon YYYY')) and save the
result in endDate
then re-write your query using the already computed values.
You can use libpq based C program to do this.
> The following is the query planner output when I give the numbers directly
> in the between clause.
> GroupAggregate (cost=49.83..57.35 rows=1 width=56)
> Output: d.col1, round(((count((count(d.id))) / 10))::double precision)
> -> Sort (cost=49.83..52.33 rows=1000 width=56)
> Output: d.col1, (count(d.id))
> Sort Key: d.col1
> -> Data Node Scan on "__REMOTE_GROUP_QUERY__" (cost=0.00..0.00
> rows=1000 width=56)
> Output: d.col1, (count(d.id))
> Node/s: datanode_c2_d1, datanode_c3_d1, datanode_c4_d1,
> datanode_c5_d1
> Remote query: SELECT l.a_1, count(l.a_2) FROM ((SELECT
> d.col1, d.id FROM ONLY Table1 d WHERE true) l(a_1, a_2) JOIN (SELECT i.id
> FROM ONLY Table2 i WHERE ((i.time >= 5266::numeric) AND (i.time <=
> 5632::numeric))) r(a_1) ON (true)) WHERE (l.a_2 = r.a_1) GROUP BY 1 ORDER
> BY 1 DESC
>
> But my requirement is with the trunc() date manipulation.
>
> Thanks
>
>
> On Wed, 2015-06-03 at 18:08 -0700, Abbas Butt wrote:
>
>
>
> On Wed, Jun 3, 2015 at 5:10 PM, Manikandan Soundarapandian <ma...@vt...>
> wrote:
>
> Hi Abbas,
>
> I just looked at the planner for the query that am running. This is the
> query that am running,
>
> *SELECT d.col1 RVALUE, ROUND(COUNT(d.col2)/10) COUNT FROM Table1 d, Table2
> i WHERE d.id <http://d.id>=i.id <http://i.id> AND i.time BETWEEN
> TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')+1-365) AND
> TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')) GROUP BY d.col1
> ORDER BY d.col1 DESC;*
>
> And the query plan for a configuration with 4 datanodes and 1 coordinator,
>
> *GroupAggregate (cost=0.07..0.10 rows=1 width=56)*
> * -> Sort (cost=0.07..0.08 rows=1 width=56)*
> * Sort Key: d.col1*
> * -> Hash Join (cost=0.01..0.06 rows=1 width=56)*
> * Hash Cond: (d.id <http://d.id> = i.id <http://i.id>)*
> * -> Data Node Scan on Table1 "_REMOTE_TABLE_QUERY_"
> (cost=0.00..0.00 rows=1000 width=56)*
> * Node/s: datanode_c2_d1, datanode_c3_d1,
> datanode_c4_d1, datanode_c5_d1*
> * -> Hash (cost=0.00..0.00 rows=1000 width=32)*
> * -> Data Node Scan on Table2 "_REMOTE_TABLE_QUERY_"
> (cost=0.00..0.00 rows=1000 width=32)*
> * Node/s: datanode_c2_d1, datanode_c3_d1,
> datanode_c4_d1, datanode_c5_d1*
> * Coordinator quals: (((i.time)::double
> precision <= trunc(((('now'::cstring)::date - to_date('01 Jan 2000'::text,
> 'DD Mon YYYY'::text)))::double precisio*
> *n)) AND ((i.time)::double precision >= trunc(((((('now'::cstring)::date -
> to_date('01 Jan 2000'::text, 'DD Mon YYYY'::text)) + 1) - 365))::double
> precision)))*
>
>
> Table1 contains 22 million records and Table2 contains 10 million records.
> This query took several minutes (almost 35) to get results and one third
> of that time is spent in FetchTuple. Do you see something wrong with the
> query plan?
>
>
>
> Can you provide output of
>
> \d+ Table1
> \d+ Table2
>
> I am interested in the distribution column of the tables.
>
>
> Also please provide the output of EXPLAIN VERBOSE on the query.
>
>
>
>
> Since the multiplexed IO isn't the real bottleneck here, my proposal would
> be to change the way how populating the TupleStore at the coordinator is
> working and introduce parallelism there. Is this a right direction? Please
> let me know your thoughts.
>
> Thanks
> Mani
>
>
>
> On Wed, 2015-06-03 at 01:49 -0700, Abbas Butt wrote:
>
>
>
> On Tue, Jun 2, 2015 at 10:17 PM, Manikandan Soundarapandian <ma...@vt...>
> wrote:
>
> Hi Abbas,
>
> Thanks for the reply. I understand how pgxc_node_receive is working to
> get incoming data from the datanodes.
> Yes, I have made experiments to study the postgres-xc system in a setup
> with 12 datanodes and a coordinator and performed join, groupby and
> order by operations on two tables with around 20 million and 10 million
> records respectively. I have found that around 20-30% of the time is
> spent in the 'FetchTuple' method which reads into the provided tupleslot
> one datarow at a time from the combiner's buffer which is in turn filled
> by the pgxc_node_receive method.
>
>
> XC planner pushes parts of query processing to datanodes to speed things
> up.
>
> In the queries you tried, did the query planner perform this optimization?
>
> If not then you can gain advantage by improving the planner to generate
> optimized plans that avoid UN-necessary materialization at the coordinator.
>
> This will bring the FetchTuple time down since there will be less tuples
> to fetch.
>
>
> If the planner is already producing optimized plans, then what's your
> proposal?
>
>
>
>
> Thanks
>
> On Wed, 2015-06-03 at 09:45 +0500, Abbas Butt wrote:
> >
> >
> > On Wed, Jun 3, 2015 at 4:11 AM, Manikandan Soundarapandian
> > <ma...@vt...> wrote:
> > Hi,
> >
> >
> > I am a graduate student working on my research in parallel
> > databases. I would like to know how the postgres-xc
> > coordinator works. I understand that the datanodes run the
> > query in parallel and the results are collected by the
> > coordinator which runs any more computation that is required
> > or just provides the output to the client that requested the
> > query. I would like to know whether the coordinator does this
> > data collection from datanodes in a sequential fashion?
> >
> >
> > The coordinator uses multiplexed IO using select on all fds of
> > datanodes. For more details please see pgxc_node_receive function in
> > pgxcnode.c. The loop for reading data on all set fds is sequential,
> > but the coordinator does not wait for data from the datanode to which
> > the coordinator had sent the query first.
> >
> > For example, lets consider we want to run the query on table
> > table_x which is hash distributed among 10 datanodes,
> > select count(*) from table_x;
> > Each datanode will run the query and give their local counts
> > and the coordinator has to collect the individual counts and
> > come up with the final count before sending the output. Is the
> > data collection process at the coordinator done in a
> > sequential fashion? I am actually looking to introduce some
> > kind of parallelism in this data collection if it is
> > sequential and do performance studies. Please clarify.
> >
> >
> > To improve performance of any system, first study the bottleneck, and
> > target to widen that. Have you done any study of Postgres-XC to find
> > where the performance bottleneck is?
> >
> >
> >
> > --
> > Thanks
> > Mani
> > Department of Computer Science
> > Virginia Tech
> >
> >
> ------------------------------------------------------------------------------
> >
> > _______________________________________________
> > Postgres-xc-developers mailing list
> > Pos...@li...
> >
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
> >
> >
> >
> >
> >
> > --
> > --
> > Abbas
> > Architect
> >
> >
> > Ph: 92.334.5100153
> >
> > Skype ID: gabbasb
> >
> > www.enterprisedb.com
> >
> > Follow us on Twitter
> > @EnterpriseDB
> >
> > Visit EnterpriseDB for tutorials, webinars, whitepapers and more
> >
>
>
>
>
>
>
> --
> --
> *Abbas*
> Architect
>
>
> Ph: 92.334.5100153
>
> Skype ID: gabbasb
>
> www.enterprisedb.co <http://www.enterprisedb.com/>m
> <http://www.enterprisedb.com/>
>
> *Follow us on Twitter*
> @EnterpriseDB
>
> Visit EnterpriseDB for tutorials, webinars, whitepapers
> <http://www.enterprisedb.com/resources-community> and more
> <http://www.enterprisedb.com/resources-community>
>
>
>
>
>
>
> --
>
> --
> *Abbas*
>
> Architect
>
>
> Ph: 92.334.5100153
>
> Skype ID: gabbasb
>
> www.enterprisedb.co <http://www.enterprisedb.com/>m
> <http://www.enterprisedb.com/>
>
> *Follow us on Twitter*
> @EnterpriseDB
>
> Visit EnterpriseDB for tutorials, webinars, whitepapers
> <http://www.enterprisedb.com/resources-community> and more
> <http://www.enterprisedb.com/resources-community>
>
>
>
--
--
*Abbas*
Architect
Ph: 92.334.5100153
Skype ID: gabbasb
www.enterprisedb.co <http://www.enterprisedb.com/>m
<http://www.enterprisedb.com/>
*Follow us on Twitter*
@EnterpriseDB
Visit EnterpriseDB for tutorials, webinars, whitepapers
<http://www.enterprisedb.com/resources-community> and more
<http://www.enterprisedb.com/resources-community>
|
|
From: Manikandan S. <ma...@vt...> - 2015-06-04 06:42:04
|
Yes, both the tables are distributed by 'id'.
On Thu, 2015-06-04 at 12:08 +0530, Ashutosh Bapat wrote:
>
>
> On Thu, Jun 4, 2015 at 6:38 AM, Abbas Butt
> <abb...@en...> wrote:
>
>
> On Wed, Jun 3, 2015 at 5:10 PM, Manikandan Soundarapandian
> <ma...@vt...> wrote:
> Hi Abbas,
>
> I just looked at the planner for the query that am
> running. This is the query that am running,
>
> SELECT d.col1 RVALUE, ROUND(COUNT(d.col2)/10) COUNT
> FROM Table1 d, Table2 i WHERE d.id=i.id AND i.time
> BETWEEN TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD
> Mon YYYY')+1-365) AND TRUNC(CURRENT_DATE-TO_DATE('01
> Jan 2000','DD Mon YYYY')) GROUP BY d.col1 ORDER BY
> d.col1 DESC;
>
> And the query plan for a configuration with 4
> datanodes and 1 coordinator,
>
> GroupAggregate (cost=0.07..0.10 rows=1 width=56)
> -> Sort (cost=0.07..0.08 rows=1 width=56)
> Sort Key: d.col1
> -> Hash Join (cost=0.01..0.06 rows=1
> width=56)
> Hash Cond: (d.id = i.id)
>
>
>
> Are both the tables distributed by id?
>
> The clauses with mutable functions like current_date are being pushded
> down to individual relations. That prevents join from being pushed
> down to the datanodes if id is distribution key. I think that should
> be fixed. The clause movement should be reversed to the Join node.
>
>
> -> Data Node Scan on Table1
> "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=1000
> width=56)
> Node/s: datanode_c2_d1,
> datanode_c3_d1, datanode_c4_d1, datanode_c5_d1
> -> Hash (cost=0.00..0.00 rows=1000
> width=32)
> -> Data Node Scan on Table2
> "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=1000
> width=32)
> Node/s: datanode_c2_d1,
> datanode_c3_d1, datanode_c4_d1, datanode_c5_d1
> Coordinator quals:
> (((i.time)::double precision <=
> trunc(((('now'::cstring)::date - to_date('01 Jan
> 2000'::text, 'DD Mon YYYY'::text)))::double precisio
> n)) AND ((i.time)::double precision >=
> trunc(((((('now'::cstring)::date - to_date('01 Jan
> 2000'::text, 'DD Mon YYYY'::text)) + 1) -
> 365))::double precision)))
>
>
> Table1 contains 22 million records and Table2 contains
> 10 million records.
> This query took several minutes (almost 35) to get
> results and one third of that time is spent in
> FetchTuple. Do you see something wrong with the query
> plan?
>
>
>
> Can you provide output of
>
> \d+ Table1
> \d+ Table2
>
> I am interested in the distribution column of the tables.
>
>
> Also please provide the output of EXPLAIN VERBOSE on the
> query.
>
>
>
>
> Since the multiplexed IO isn't the real bottleneck
> here, my proposal would be to change the way how
> populating the TupleStore at the coordinator is
> working and introduce parallelism there. Is this a
> right direction? Please let me know your thoughts.
>
> Thanks
> Mani
>
>
> On Wed, 2015-06-03 at 01:49 -0700, Abbas Butt wrote:
> >
> >
> > On Tue, Jun 2, 2015 at 10:17 PM, Manikandan
> > Soundarapandian <ma...@vt...> wrote:
> > Hi Abbas,
> >
> > Thanks for the reply. I understand how
> > pgxc_node_receive is working to
> > get incoming data from the datanodes.
> > Yes, I have made experiments to study the
> > postgres-xc system in a setup
> > with 12 datanodes and a coordinator and
> > performed join, groupby and
> > order by operations on two tables with
> > around 20 million and 10 million
> > records respectively. I have found that
> > around 20-30% of the time is
> > spent in the 'FetchTuple' method which reads
> > into the provided tupleslot
> > one datarow at a time from the combiner's
> > buffer which is in turn filled
> > by the pgxc_node_receive method.
> >
> > XC planner pushes parts of query processing to
> > datanodes to speed things up.
> >
> > In the queries you tried, did the query planner
> > perform this optimization?
> >
> > If not then you can gain advantage by improving the
> > planner to generate optimized plans that avoid
> > UN-necessary materialization at the coordinator.
> >
> > This will bring the FetchTuple time down since there
> > will be less tuples to fetch.
> >
> >
> > If the planner is already producing optimized plans,
> > then what's your proposal?
> >
> >
> >
> >
> > Thanks
> >
> > On Wed, 2015-06-03 at 09:45 +0500, Abbas
> > Butt wrote:
> > >
> > >
> > > On Wed, Jun 3, 2015 at 4:11 AM, Manikandan
> > Soundarapandian
> > > <ma...@vt...> wrote:
> > > Hi,
> > >
> > >
> > > I am a graduate student working on
> > my research in parallel
> > > databases. I would like to know
> > how the postgres-xc
> > > coordinator works. I understand
> > that the datanodes run the
> > > query in parallel and the results
> > are collected by the
> > > coordinator which runs any more
> > computation that is required
> > > or just provides the output to the
> > client that requested the
> > > query. I would like to know
> > whether the coordinator does this
> > > data collection from datanodes in
> > a sequential fashion?
> > >
> > >
> > > The coordinator uses multiplexed IO using
> > select on all fds of
> > > datanodes. For more details please see
> > pgxc_node_receive function in
> > > pgxcnode.c. The loop for reading data on
> > all set fds is sequential,
> > > but the coordinator does not wait for data
> > from the datanode to which
> > > the coordinator had sent the query first.
> > >
> > > For example, lets consider we want
> > to run the query on table
> > > table_x which is hash distributed
> > among 10 datanodes,
> > > select count(*) from table_x;
> > > Each datanode will run the query
> > and give their local counts
> > > and the coordinator has to collect
> > the individual counts and
> > > come up with the final count
> > before sending the output. Is the
> > > data collection process at the
> > coordinator done in a
> > > sequential fashion? I am actually
> > looking to introduce some
> > > kind of parallelism in this data
> > collection if it is
> > > sequential and do performance
> > studies. Please clarify.
> > >
> > >
> > > To improve performance of any system,
> > first study the bottleneck, and
> > > target to widen that. Have you done any
> > study of Postgres-XC to find
> > > where the performance bottleneck is?
> > >
> > >
> > >
> > > --
> > > Thanks
> > > Mani
> > > Department of Computer Science
> > > Virginia Tech
> > >
> > >
> > ------------------------------------------------------------------------------
> > >
> > >
> > _______________________________________________
> > > Postgres-xc-developers mailing
> > list
> > >
> > Pos...@li...
> > >
> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
> > >
> > >
> > >
> > >
> > >
> > > --
> > > --
> > > Abbas
> > > Architect
> > >
> > >
> > > Ph: 92.334.5100153
> > >
> > > Skype ID: gabbasb
> > >
> > > www.enterprisedb.com
> > >
> > > Follow us on Twitter
> > > @EnterpriseDB
> > >
> > > Visit EnterpriseDB for tutorials,
> > webinars, whitepapers and more
> > >
> >
> >
> >
> >
> >
> >
> > --
> > --
> > Abbas
> > Architect
> >
> >
> > Ph: 92.334.5100153
> >
> > Skype ID: gabbasb
> >
> > www.enterprisedb.com
> >
> > Follow us on Twitter
> > @EnterpriseDB
> >
> > Visit EnterpriseDB for tutorials, webinars,
> > whitepapers and more
> >
>
>
>
>
>
> --
> --
> Abbas
> Architect
>
>
> Ph: 92.334.5100153
>
> Skype ID: gabbasb
>
> www.enterprisedb.com
>
> Follow us on Twitter
> @EnterpriseDB
>
> Visit EnterpriseDB for tutorials, webinars, whitepapers and
> more
>
>
> ------------------------------------------------------------------------------
>
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>
|
|
From: Ashutosh B. <ash...@en...> - 2015-06-04 06:38:24
|
On Thu, Jun 4, 2015 at 6:38 AM, Abbas Butt <abb...@en...> wrote: > > > On Wed, Jun 3, 2015 at 5:10 PM, Manikandan Soundarapandian <ma...@vt...> > wrote: > >> Hi Abbas, >> >> I just looked at the planner for the query that am running. This is the >> query that am running, >> >> *SELECT d.col1 RVALUE, ROUND(COUNT(d.col2)/10) COUNT FROM Table1 d, >> Table2 i WHERE d.id <http://d.id>=i.id <http://i.id> AND i.time BETWEEN >> TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')+1-365) AND >> TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')) GROUP BY d.col1 >> ORDER BY d.col1 DESC;* >> >> And the query plan for a configuration with 4 datanodes and 1 coordinator, >> >> *GroupAggregate (cost=0.07..0.10 rows=1 width=56)* >> * -> Sort (cost=0.07..0.08 rows=1 width=56)* >> * Sort Key: d.col1* >> * -> Hash Join (cost=0.01..0.06 rows=1 width=56)* >> * Hash Cond: (d.id <http://d.id> = i.id <http://i.id>)* >> > Are both the tables distributed by id? The clauses with mutable functions like current_date are being pushded down to individual relations. That prevents join from being pushed down to the datanodes if id is distribution key. I think that should be fixed. The clause movement should be reversed to the Join node. > * -> Data Node Scan on Table1 "_REMOTE_TABLE_QUERY_" >> (cost=0.00..0.00 rows=1000 width=56)* >> * Node/s: datanode_c2_d1, datanode_c3_d1, >> datanode_c4_d1, datanode_c5_d1* >> * -> Hash (cost=0.00..0.00 rows=1000 width=32)* >> * -> Data Node Scan on Table2 >> "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=1000 width=32)* >> * Node/s: datanode_c2_d1, datanode_c3_d1, >> datanode_c4_d1, datanode_c5_d1* >> * Coordinator quals: (((i.time)::double >> precision <= trunc(((('now'::cstring)::date - to_date('01 Jan 2000'::text, >> 'DD Mon YYYY'::text)))::double precisio* >> *n)) AND ((i.time)::double precision >= trunc(((((('now'::cstring)::date >> - to_date('01 Jan 2000'::text, 'DD Mon YYYY'::text)) + 1) - 365))::double >> precision)))* >> >> >> Table1 contains 22 million records and Table2 contains 10 million records. >> This query took several minutes (almost 35) to get results and one third >> of that time is spent in FetchTuple. Do you see something wrong with the >> query plan? >> > > Can you provide output of > \d+ Table1 > \d+ Table2 > I am interested in the distribution column of the tables. > > Also please provide the output of EXPLAIN VERBOSE on the query. > > > >> Since the multiplexed IO isn't the real bottleneck here, my proposal >> would be to change the way how populating the TupleStore at the coordinator >> is working and introduce parallelism there. Is this a right direction? >> Please let me know your thoughts. >> >> Thanks >> Mani >> >> >> On Wed, 2015-06-03 at 01:49 -0700, Abbas Butt wrote: >> >> >> >> On Tue, Jun 2, 2015 at 10:17 PM, Manikandan Soundarapandian < >> ma...@vt...> wrote: >> >> Hi Abbas, >> >> Thanks for the reply. I understand how pgxc_node_receive is working to >> get incoming data from the datanodes. >> Yes, I have made experiments to study the postgres-xc system in a setup >> with 12 datanodes and a coordinator and performed join, groupby and >> order by operations on two tables with around 20 million and 10 million >> records respectively. I have found that around 20-30% of the time is >> spent in the 'FetchTuple' method which reads into the provided tupleslot >> one datarow at a time from the combiner's buffer which is in turn filled >> by the pgxc_node_receive method. >> >> >> XC planner pushes parts of query processing to datanodes to speed >> things up. >> >> In the queries you tried, did the query planner perform this >> optimization? >> >> If not then you can gain advantage by improving the planner to generate >> optimized plans that avoid UN-necessary materialization at the coordinator. >> >> This will bring the FetchTuple time down since there will be less >> tuples to fetch. >> >> >> If the planner is already producing optimized plans, then what's your >> proposal? >> >> >> >> >> Thanks >> >> >> On Wed, 2015-06-03 at 09:45 +0500, Abbas Butt wrote: >> > >> > >> > On Wed, Jun 3, 2015 at 4:11 AM, Manikandan Soundarapandian >> > <ma...@vt...> wrote: >> > Hi, >> > >> > >> > I am a graduate student working on my research in parallel >> > databases. I would like to know how the postgres-xc >> > coordinator works. I understand that the datanodes run the >> > query in parallel and the results are collected by the >> > coordinator which runs any more computation that is required >> > or just provides the output to the client that requested the >> > query. I would like to know whether the coordinator does this >> > data collection from datanodes in a sequential fashion? >> > >> > >> > The coordinator uses multiplexed IO using select on all fds of >> > datanodes. For more details please see pgxc_node_receive function in >> > pgxcnode.c. The loop for reading data on all set fds is sequential, >> > but the coordinator does not wait for data from the datanode to which >> > the coordinator had sent the query first. >> > >> > For example, lets consider we want to run the query on table >> > table_x which is hash distributed among 10 datanodes, >> > select count(*) from table_x; >> > Each datanode will run the query and give their local counts >> > and the coordinator has to collect the individual counts and >> > come up with the final count before sending the output. Is the >> > data collection process at the coordinator done in a >> > sequential fashion? I am actually looking to introduce some >> > kind of parallelism in this data collection if it is >> > sequential and do performance studies. Please clarify. >> > >> > >> > To improve performance of any system, first study the bottleneck, and >> > target to widen that. Have you done any study of Postgres-XC to find >> > where the performance bottleneck is? >> > >> > >> > >> > -- >> > Thanks >> > Mani >> > Department of Computer Science >> > Virginia Tech >> > >> > >> ------------------------------------------------------------------------------ >> > >> > _______________________________________________ >> > Postgres-xc-developers mailing list >> > Pos...@li... >> > >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >> > >> > >> > >> > >> > >> > -- >> > -- >> > Abbas >> > Architect >> > >> > >> > Ph: 92.334.5100153 >> > >> > Skype ID: gabbasb >> > >> > www.enterprisedb.com >> > >> > Follow us on Twitter >> > @EnterpriseDB >> > >> > Visit EnterpriseDB for tutorials, webinars, whitepapers and more >> > >> >> >> >> >> >> >> -- >> >> -- >> *Abbas* >> >> Architect >> >> >> Ph: 92.334.5100153 >> >> Skype ID: gabbasb >> >> www.enterprisedb.co <http://www.enterprisedb.com/>m >> <http://www.enterprisedb.com/> >> >> *Follow us on Twitter* >> @EnterpriseDB >> >> Visit EnterpriseDB for tutorials, webinars, whitepapers >> <http://www.enterprisedb.com/resources-community> and more >> <http://www.enterprisedb.com/resources-community> >> >> >> > > > -- > -- > *Abbas* > Architect > > Ph: 92.334.5100153 > Skype ID: gabbasb > www.enterprisedb.co <http://www.enterprisedb.com/>m > <http://www.enterprisedb.com/> > > *Follow us on Twitter* > @EnterpriseDB > > Visit EnterpriseDB for tutorials, webinars, whitepapers > <http://www.enterprisedb.com/resources-community> and more > <http://www.enterprisedb.com/resources-community> > > > ------------------------------------------------------------------------------ > > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company |
|
From: Manikandan S. <ma...@vt...> - 2015-06-04 03:57:26
|
Hi, Please see below
\d+ Table1
Table "Table1"
Column | Type | Modifiers | Storage | Stats
target | Description
--------------+-----------------------+-----------+----------+--------------+-------------
id | numeric | | main | 1000
|
age | numeric | | main | 1000
|
gender | numeric | | main | 1000
|
col1 | character varying(5) | | extended | 1000
|
Indexes:
"index_age_table1" btree (age)
"index_col1_table1" btree (col1)
"index_gender_table1" btree (gender)
"index_id_table1" btree (id)
Has OIDs: no
Distribute By: HASH(id)
Location Nodes: ALL DATANODES
\d+ Table2
Table "Table2"
Column | Type | Modifiers | Storage | Stats target |
Description
-----------------+---------+-----------+---------+--------------+-------------
id | numeric | | main | 1000 |
rep | numeric | | main | 1000 |
time | numeric | | main | 1000 |
Indexes:
"index_time_table2" btree (time)
"index_id_table2" btree (id)
"index_rep_table2" btree (rep)
Has OIDs: no
Distribute By: HASH(id)
Location Nodes: ALL DATANODES
Explain verbose of the query:
GroupAggregate (cost=0.07..0.10 rows=1 width=56)
Output: d.col1, round(((count(d.id) / 10))::double precision)
-> Sort (cost=0.07..0.08 rows=1 width=56)
Output: d.col1, d.id
Sort Key: d.col1
-> Hash Join (cost=0.01..0.06 rows=1 width=56)
Output: d.col1, d.id
Hash Cond: (d.id = i.id)
-> Data Node Scan on Table1 "_REMOTE_TABLE_QUERY_"
(cost=0.00..0.00 rows=1000 width=56)
Output: d.col1, d.id
Node/s: datanode_c2_d1, datanode_c3_d1,
datanode_c4_d1, datanode_c5_d1
Remote query: SELECT col1, id FROM ONLY Table1 d
WHERE true
-> Hash (cost=0.00..0.00 rows=1000 width=32)
Output: i.id
-> Data Node Scan on Table2 "_REMOTE_TABLE_QUERY_"
(cost=0.00..0.00 rows=1000 width=32)
Output: i.id
Node/s: datanode_c2_d1, datanode_c3_d1,
datanode_c4_d1, datanode_c5_d1
Remote query: SELECT id, time FROM ONLY
Table2 i WHERE true
Coordinator quals: (((i.time)::double
precision <= trunc(((('now'::cstring)::date - to_date('01 Jan
2000'::text, 'DD Mon YYYY'::text)))::double precis
ion)) AND ((i.time)::double precision >=
trunc(((((('now'::cstring)::date - to_date('01 Jan 2000'::text, 'DD Mon
YYYY'::text)) + 1) - 365))::double precision)))
Also, one weird thing that I noticed is if I don't give the 'trunc()'
date manipulation in the query and just gave some valid numbers in the
between clause like 'between 5266 and 5632', am getting a different
query plan and the query executes in just a few seconds. The following
is the query planner output when I give the numbers directly in the
between clause.
GroupAggregate (cost=49.83..57.35 rows=1 width=56)
Output: d.col1, round(((count((count(d.id))) / 10))::double
precision)
-> Sort (cost=49.83..52.33 rows=1000 width=56)
Output: d.col1, (count(d.id))
Sort Key: d.col1
-> Data Node Scan on "__REMOTE_GROUP_QUERY__"
(cost=0.00..0.00 rows=1000 width=56)
Output: d.col1, (count(d.id))
Node/s: datanode_c2_d1, datanode_c3_d1, datanode_c4_d1,
datanode_c5_d1
Remote query: SELECT l.a_1, count(l.a_2) FROM ((SELECT
d.col1, d.id FROM ONLY Table1 d WHERE true) l(a_1, a_2) JOIN (SELECT
i.id FROM ONLY Table2 i WHERE ((i.time >= 5266::numeric) AND (i.time <=
5632::numeric))) r(a_1) ON (true)) WHERE (l.a_2 = r.a_1) GROUP BY 1
ORDER BY 1 DESC
But my requirement is with the trunc() date manipulation.
Thanks
On Wed, 2015-06-03 at 18:08 -0700, Abbas Butt wrote:
>
>
>
>
> On Wed, Jun 3, 2015 at 5:10 PM, Manikandan Soundarapandian
> <ma...@vt...> wrote:
>
> Hi Abbas,
>
> I just looked at the planner for the query that am running.
> This is the query that am running,
>
> SELECT d.col1 RVALUE, ROUND(COUNT(d.col2)/10) COUNT FROM
> Table1 d, Table2 i WHERE d.id=i.id AND i.time BETWEEN
> TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')+1-365)
> AND TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY'))
> GROUP BY d.col1 ORDER BY d.col1 DESC;
>
> And the query plan for a configuration with 4 datanodes and 1
> coordinator,
>
> GroupAggregate (cost=0.07..0.10 rows=1 width=56)
> -> Sort (cost=0.07..0.08 rows=1 width=56)
> Sort Key: d.col1
> -> Hash Join (cost=0.01..0.06 rows=1 width=56)
> Hash Cond: (d.id = i.id)
> -> Data Node Scan on Table1
> "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=1000 width=56)
> Node/s: datanode_c2_d1, datanode_c3_d1,
> datanode_c4_d1, datanode_c5_d1
> -> Hash (cost=0.00..0.00 rows=1000 width=32)
> -> Data Node Scan on Table2
> "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=1000 width=32)
> Node/s: datanode_c2_d1,
> datanode_c3_d1, datanode_c4_d1, datanode_c5_d1
> Coordinator quals:
> (((i.time)::double precision <= trunc(((('now'::cstring)::date
> - to_date('01 Jan 2000'::text, 'DD Mon YYYY'::text)))::double
> precisio
> n)) AND ((i.time)::double precision >=
> trunc(((((('now'::cstring)::date - to_date('01 Jan
> 2000'::text, 'DD Mon YYYY'::text)) + 1) - 365))::double
> precision)))
>
>
> Table1 contains 22 million records and Table2 contains 10
> million records.
> This query took several minutes (almost 35) to get results and
> one third of that time is spent in FetchTuple. Do you see
> something wrong with the query plan?
>
>
>
> Can you provide output of
>
> \d+ Table1
> \d+ Table2
>
> I am interested in the distribution column of the tables.
>
>
> Also please provide the output of EXPLAIN VERBOSE on the query.
>
>
>
>
>
>
> Since the multiplexed IO isn't the real bottleneck here, my
> proposal would be to change the way how populating the
> TupleStore at the coordinator is working and introduce
> parallelism there. Is this a right direction? Please let me
> know your thoughts.
>
> Thanks
> Mani
>
>
>
> On Wed, 2015-06-03 at 01:49 -0700, Abbas Butt wrote:
>
> >
> >
> > On Tue, Jun 2, 2015 at 10:17 PM, Manikandan Soundarapandian
> > <ma...@vt...> wrote:
> >
> > Hi Abbas,
> >
> > Thanks for the reply. I understand how
> > pgxc_node_receive is working to
> > get incoming data from the datanodes.
> > Yes, I have made experiments to study the
> > postgres-xc system in a setup
> > with 12 datanodes and a coordinator and performed
> > join, groupby and
> > order by operations on two tables with around 20
> > million and 10 million
> > records respectively. I have found that around
> > 20-30% of the time is
> > spent in the 'FetchTuple' method which reads into
> > the provided tupleslot
> > one datarow at a time from the combiner's buffer
> > which is in turn filled
> > by the pgxc_node_receive method.
> >
> >
> > XC planner pushes parts of query processing to datanodes to
> > speed things up.
> >
> > In the queries you tried, did the query planner perform this
> > optimization?
> >
> > If not then you can gain advantage by improving the planner
> > to generate optimized plans that avoid UN-necessary
> > materialization at the coordinator.
> >
> > This will bring the FetchTuple time down since there will be
> > less tuples to fetch.
> >
> >
> > If the planner is already producing optimized plans, then
> > what's your proposal?
> >
> >
> >
> >
> >
> > Thanks
> >
> > On Wed, 2015-06-03 at 09:45 +0500, Abbas Butt wrote:
> > >
> > >
> > > On Wed, Jun 3, 2015 at 4:11 AM, Manikandan
> > Soundarapandian
> > > <ma...@vt...> wrote:
> > > Hi,
> > >
> > >
> > > I am a graduate student working on my
> > research in parallel
> > > databases. I would like to know how the
> > postgres-xc
> > > coordinator works. I understand that the
> > datanodes run the
> > > query in parallel and the results are
> > collected by the
> > > coordinator which runs any more
> > computation that is required
> > > or just provides the output to the client
> > that requested the
> > > query. I would like to know whether the
> > coordinator does this
> > > data collection from datanodes in a
> > sequential fashion?
> > >
> > >
> > > The coordinator uses multiplexed IO using select
> > on all fds of
> > > datanodes. For more details please see
> > pgxc_node_receive function in
> > > pgxcnode.c. The loop for reading data on all set
> > fds is sequential,
> > > but the coordinator does not wait for data from
> > the datanode to which
> > > the coordinator had sent the query first.
> > >
> > > For example, lets consider we want to run
> > the query on table
> > > table_x which is hash distributed among 10
> > datanodes,
> > > select count(*) from table_x;
> > > Each datanode will run the query and give
> > their local counts
> > > and the coordinator has to collect the
> > individual counts and
> > > come up with the final count before
> > sending the output. Is the
> > > data collection process at the coordinator
> > done in a
> > > sequential fashion? I am actually looking
> > to introduce some
> > > kind of parallelism in this data
> > collection if it is
> > > sequential and do performance studies.
> > Please clarify.
> > >
> > >
> > > To improve performance of any system, first study
> > the bottleneck, and
> > > target to widen that. Have you done any study of
> > Postgres-XC to find
> > > where the performance bottleneck is?
> > >
> > >
> > >
> > > --
> > > Thanks
> > > Mani
> > > Department of Computer Science
> > > Virginia Tech
> > >
> > >
> > ------------------------------------------------------------------------------
> > >
> > >
> > _______________________________________________
> > > Postgres-xc-developers mailing list
> > >
> > Pos...@li...
> > >
> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
> > >
> > >
> > >
> > >
> > >
> > > --
> > > --
> > > Abbas
> > > Architect
> > >
> > >
> > > Ph: 92.334.5100153
> > >
> > > Skype ID: gabbasb
> > >
> > > www.enterprisedb.com
> > >
> > > Follow us on Twitter
> > > @EnterpriseDB
> > >
> > > Visit EnterpriseDB for tutorials, webinars,
> > whitepapers and more
> > >
> >
> >
> >
> >
> >
> >
> >
> > --
> > --
> > Abbas
> > Architect
> >
> >
> > Ph: 92.334.5100153
> >
> > Skype ID: gabbasb
> >
> > www.enterprisedb.com
> >
> > Follow us on Twitter
> > @EnterpriseDB
> >
> > Visit EnterpriseDB for tutorials, webinars, whitepapers and
> > more
> >
>
>
>
>
>
>
>
> --
>
> --
> Abbas
>
> Architect
>
>
> Ph: 92.334.5100153
>
> Skype ID: gabbasb
>
>
> www.enterprisedb.com
>
> Follow us on Twitter
> @EnterpriseDB
>
> Visit EnterpriseDB for tutorials, webinars, whitepapers and more
>
|
|
From: Abbas B. <abb...@en...> - 2015-06-04 01:08:44
|
On Wed, Jun 3, 2015 at 5:10 PM, Manikandan Soundarapandian <ma...@vt...> wrote: > Hi Abbas, > > I just looked at the planner for the query that am running. This is the > query that am running, > > *SELECT d.col1 RVALUE, ROUND(COUNT(d.col2)/10) COUNT FROM Table1 d, Table2 > i WHERE d.id <http://d.id>=i.id <http://i.id> AND i.time BETWEEN > TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')+1-365) AND > TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')) GROUP BY d.col1 > ORDER BY d.col1 DESC;* > > And the query plan for a configuration with 4 datanodes and 1 coordinator, > > *GroupAggregate (cost=0.07..0.10 rows=1 width=56)* > * -> Sort (cost=0.07..0.08 rows=1 width=56)* > * Sort Key: d.col1* > * -> Hash Join (cost=0.01..0.06 rows=1 width=56)* > * Hash Cond: (d.id <http://d.id> = i.id <http://i.id>)* > * -> Data Node Scan on Table1 "_REMOTE_TABLE_QUERY_" > (cost=0.00..0.00 rows=1000 width=56)* > * Node/s: datanode_c2_d1, datanode_c3_d1, > datanode_c4_d1, datanode_c5_d1* > * -> Hash (cost=0.00..0.00 rows=1000 width=32)* > * -> Data Node Scan on Table2 "_REMOTE_TABLE_QUERY_" > (cost=0.00..0.00 rows=1000 width=32)* > * Node/s: datanode_c2_d1, datanode_c3_d1, > datanode_c4_d1, datanode_c5_d1* > * Coordinator quals: (((i.time)::double > precision <= trunc(((('now'::cstring)::date - to_date('01 Jan 2000'::text, > 'DD Mon YYYY'::text)))::double precisio* > *n)) AND ((i.time)::double precision >= trunc(((((('now'::cstring)::date - > to_date('01 Jan 2000'::text, 'DD Mon YYYY'::text)) + 1) - 365))::double > precision)))* > > > Table1 contains 22 million records and Table2 contains 10 million records. > This query took several minutes (almost 35) to get results and one third > of that time is spent in FetchTuple. Do you see something wrong with the > query plan? > Can you provide output of \d+ Table1 \d+ Table2 I am interested in the distribution column of the tables. Also please provide the output of EXPLAIN VERBOSE on the query. > Since the multiplexed IO isn't the real bottleneck here, my proposal would > be to change the way how populating the TupleStore at the coordinator is > working and introduce parallelism there. Is this a right direction? Please > let me know your thoughts. > > Thanks > Mani > > > On Wed, 2015-06-03 at 01:49 -0700, Abbas Butt wrote: > > > > On Tue, Jun 2, 2015 at 10:17 PM, Manikandan Soundarapandian < > ma...@vt...> wrote: > > Hi Abbas, > > Thanks for the reply. I understand how pgxc_node_receive is working to > get incoming data from the datanodes. > Yes, I have made experiments to study the postgres-xc system in a setup > with 12 datanodes and a coordinator and performed join, groupby and > order by operations on two tables with around 20 million and 10 million > records respectively. I have found that around 20-30% of the time is > spent in the 'FetchTuple' method which reads into the provided tupleslot > one datarow at a time from the combiner's buffer which is in turn filled > by the pgxc_node_receive method. > > > XC planner pushes parts of query processing to datanodes to speed things > up. > > In the queries you tried, did the query planner perform this > optimization? > > If not then you can gain advantage by improving the planner to generate > optimized plans that avoid UN-necessary materialization at the coordinator. > > This will bring the FetchTuple time down since there will be less tuples > to fetch. > > > If the planner is already producing optimized plans, then what's your > proposal? > > > > > Thanks > > > On Wed, 2015-06-03 at 09:45 +0500, Abbas Butt wrote: > > > > > > On Wed, Jun 3, 2015 at 4:11 AM, Manikandan Soundarapandian > > <ma...@vt...> wrote: > > Hi, > > > > > > I am a graduate student working on my research in parallel > > databases. I would like to know how the postgres-xc > > coordinator works. I understand that the datanodes run the > > query in parallel and the results are collected by the > > coordinator which runs any more computation that is required > > or just provides the output to the client that requested the > > query. I would like to know whether the coordinator does this > > data collection from datanodes in a sequential fashion? > > > > > > The coordinator uses multiplexed IO using select on all fds of > > datanodes. For more details please see pgxc_node_receive function in > > pgxcnode.c. The loop for reading data on all set fds is sequential, > > but the coordinator does not wait for data from the datanode to which > > the coordinator had sent the query first. > > > > For example, lets consider we want to run the query on table > > table_x which is hash distributed among 10 datanodes, > > select count(*) from table_x; > > Each datanode will run the query and give their local counts > > and the coordinator has to collect the individual counts and > > come up with the final count before sending the output. Is the > > data collection process at the coordinator done in a > > sequential fashion? I am actually looking to introduce some > > kind of parallelism in this data collection if it is > > sequential and do performance studies. Please clarify. > > > > > > To improve performance of any system, first study the bottleneck, and > > target to widen that. Have you done any study of Postgres-XC to find > > where the performance bottleneck is? > > > > > > > > -- > > Thanks > > Mani > > Department of Computer Science > > Virginia Tech > > > > > ------------------------------------------------------------------------------ > > > > _______________________________________________ > > Postgres-xc-developers mailing list > > Pos...@li... > > > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > > > > > > > > > > > -- > > -- > > Abbas > > Architect > > > > > > Ph: 92.334.5100153 > > > > Skype ID: gabbasb > > > > www.enterprisedb.com > > > > Follow us on Twitter > > @EnterpriseDB > > > > Visit EnterpriseDB for tutorials, webinars, whitepapers and more > > > > > > > > > -- > > -- > *Abbas* > > Architect > > > Ph: 92.334.5100153 > > Skype ID: gabbasb > > www.enterprisedb.co <http://www.enterprisedb.com/>m > <http://www.enterprisedb.com/> > > *Follow us on Twitter* > @EnterpriseDB > > Visit EnterpriseDB for tutorials, webinars, whitepapers > <http://www.enterprisedb.com/resources-community> and more > <http://www.enterprisedb.com/resources-community> > > > -- -- *Abbas* Architect Ph: 92.334.5100153 Skype ID: gabbasb www.enterprisedb.co <http://www.enterprisedb.com/>m <http://www.enterprisedb.com/> *Follow us on Twitter* @EnterpriseDB Visit EnterpriseDB for tutorials, webinars, whitepapers <http://www.enterprisedb.com/resources-community> and more <http://www.enterprisedb.com/resources-community> |
|
From: Manikandan S. <ma...@vt...> - 2015-06-04 00:10:29
|
Hi Abbas,
I just looked at the planner for the query that am running. This is the
query that am running,
SELECT d.col1 RVALUE, ROUND(COUNT(d.col2)/10) COUNT FROM Table1 d,
Table2 i WHERE d.id=i.id AND i.time BETWEEN
TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')+1-365) AND
TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')) GROUP BY d.col1
ORDER BY d.col1 DESC;
And the query plan for a configuration with 4 datanodes and 1
coordinator,
GroupAggregate (cost=0.07..0.10 rows=1 width=56)
-> Sort (cost=0.07..0.08 rows=1 width=56)
Sort Key: d.col1
-> Hash Join (cost=0.01..0.06 rows=1 width=56)
Hash Cond: (d.id = i.id)
-> Data Node Scan on Table1 "_REMOTE_TABLE_QUERY_"
(cost=0.00..0.00 rows=1000 width=56)
Node/s: datanode_c2_d1, datanode_c3_d1,
datanode_c4_d1, datanode_c5_d1
-> Hash (cost=0.00..0.00 rows=1000 width=32)
-> Data Node Scan on Table2 "_REMOTE_TABLE_QUERY_"
(cost=0.00..0.00 rows=1000 width=32)
Node/s: datanode_c2_d1, datanode_c3_d1,
datanode_c4_d1, datanode_c5_d1
Coordinator quals: (((i.time)::double
precision <= trunc(((('now'::cstring)::date - to_date('01 Jan
2000'::text, 'DD Mon YYYY'::text)))::double precisio
n)) AND ((i.time)::double precision >= trunc(((((('now'::cstring)::date
- to_date('01 Jan 2000'::text, 'DD Mon YYYY'::text)) + 1) -
365))::double precision)))
Table1 contains 22 million records and Table2 contains 10 million
records.
This query took several minutes (almost 35) to get results and one third
of that time is spent in FetchTuple. Do you see something wrong with the
query plan?
Since the multiplexed IO isn't the real bottleneck here, my proposal
would be to change the way how populating the TupleStore at the
coordinator is working and introduce parallelism there. Is this a right
direction? Please let me know your thoughts.
Thanks
Mani
On Wed, 2015-06-03 at 01:49 -0700, Abbas Butt wrote:
>
>
>
>
> On Tue, Jun 2, 2015 at 10:17 PM, Manikandan Soundarapandian
> <ma...@vt...> wrote:
>
> Hi Abbas,
>
> Thanks for the reply. I understand how pgxc_node_receive is
> working to
> get incoming data from the datanodes.
> Yes, I have made experiments to study the postgres-xc system
> in a setup
> with 12 datanodes and a coordinator and performed join,
> groupby and
> order by operations on two tables with around 20 million and
> 10 million
> records respectively. I have found that around 20-30% of the
> time is
> spent in the 'FetchTuple' method which reads into the provided
> tupleslot
> one datarow at a time from the combiner's buffer which is in
> turn filled
> by the pgxc_node_receive method.
>
>
>
> XC planner pushes parts of query processing to datanodes to speed
> things up.
>
> In the queries you tried, did the query planner perform this
> optimization?
>
> If not then you can gain advantage by improving the planner to
> generate optimized plans that avoid UN-necessary materialization at
> the coordinator.
>
> This will bring the FetchTuple time down since there will be less
> tuples to fetch.
>
>
> If the planner is already producing optimized plans, then what's your
> proposal?
>
>
>
>
>
> Thanks
>
>
> On Wed, 2015-06-03 at 09:45 +0500, Abbas Butt wrote:
> >
> >
> > On Wed, Jun 3, 2015 at 4:11 AM, Manikandan Soundarapandian
> > <ma...@vt...> wrote:
> > Hi,
> >
> >
> > I am a graduate student working on my research in
> parallel
> > databases. I would like to know how the postgres-xc
> > coordinator works. I understand that the datanodes
> run the
> > query in parallel and the results are collected by
> the
> > coordinator which runs any more computation that is
> required
> > or just provides the output to the client that
> requested the
> > query. I would like to know whether the coordinator
> does this
> > data collection from datanodes in a sequential
> fashion?
> >
> >
> > The coordinator uses multiplexed IO using select on all fds
> of
> > datanodes. For more details please see pgxc_node_receive
> function in
> > pgxcnode.c. The loop for reading data on all set fds is
> sequential,
> > but the coordinator does not wait for data from the datanode
> to which
> > the coordinator had sent the query first.
> >
> > For example, lets consider we want to run the query
> on table
> > table_x which is hash distributed among 10
> datanodes,
> > select count(*) from table_x;
> > Each datanode will run the query and give their
> local counts
> > and the coordinator has to collect the individual
> counts and
> > come up with the final count before sending the
> output. Is the
> > data collection process at the coordinator done in a
> > sequential fashion? I am actually looking to
> introduce some
> > kind of parallelism in this data collection if it is
> > sequential and do performance studies. Please
> clarify.
> >
> >
> > To improve performance of any system, first study the
> bottleneck, and
> > target to widen that. Have you done any study of Postgres-XC
> to find
> > where the performance bottleneck is?
> >
> >
> >
> > --
> > Thanks
> > Mani
> > Department of Computer Science
> > Virginia Tech
> >
> >
> ------------------------------------------------------------------------------
> >
> > _______________________________________________
> > Postgres-xc-developers mailing list
> > Pos...@li...
> >
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
> >
> >
> >
> >
> >
> > --
> > --
> > Abbas
> > Architect
> >
> >
> > Ph: 92.334.5100153
> >
> > Skype ID: gabbasb
> >
> > www.enterprisedb.com
> >
> > Follow us on Twitter
> > @EnterpriseDB
> >
> > Visit EnterpriseDB for tutorials, webinars, whitepapers and
> more
> >
>
>
>
>
>
>
>
> --
>
> --
> Abbas
>
> Architect
>
>
> Ph: 92.334.5100153
>
> Skype ID: gabbasb
>
>
> www.enterprisedb.com
>
> Follow us on Twitter
> @EnterpriseDB
>
> Visit EnterpriseDB for tutorials, webinars, whitepapers and more
>
|
|
From: Abbas B. <abb...@en...> - 2015-06-03 08:49:53
|
On Tue, Jun 2, 2015 at 10:17 PM, Manikandan Soundarapandian <ma...@vt...> wrote: > Hi Abbas, > > Thanks for the reply. I understand how pgxc_node_receive is working to > get incoming data from the datanodes. > Yes, I have made experiments to study the postgres-xc system in a setup > with 12 datanodes and a coordinator and performed join, groupby and > order by operations on two tables with around 20 million and 10 million > records respectively. I have found that around 20-30% of the time is > spent in the 'FetchTuple' method which reads into the provided tupleslot > one datarow at a time from the combiner's buffer which is in turn filled > by the pgxc_node_receive method. > XC planner pushes parts of query processing to datanodes to speed things up. In the queries you tried, did the query planner perform this optimization? If not then you can gain advantage by improving the planner to generate optimized plans that avoid UN-necessary materialization at the coordinator. This will bring the FetchTuple time down since there will be less tuples to fetch. If the planner is already producing optimized plans, then what's your proposal? > Thanks > > On Wed, 2015-06-03 at 09:45 +0500, Abbas Butt wrote: > > > > > > On Wed, Jun 3, 2015 at 4:11 AM, Manikandan Soundarapandian > > <ma...@vt...> wrote: > > Hi, > > > > > > I am a graduate student working on my research in parallel > > databases. I would like to know how the postgres-xc > > coordinator works. I understand that the datanodes run the > > query in parallel and the results are collected by the > > coordinator which runs any more computation that is required > > or just provides the output to the client that requested the > > query. I would like to know whether the coordinator does this > > data collection from datanodes in a sequential fashion? > > > > > > The coordinator uses multiplexed IO using select on all fds of > > datanodes. For more details please see pgxc_node_receive function in > > pgxcnode.c. The loop for reading data on all set fds is sequential, > > but the coordinator does not wait for data from the datanode to which > > the coordinator had sent the query first. > > > > For example, lets consider we want to run the query on table > > table_x which is hash distributed among 10 datanodes, > > select count(*) from table_x; > > Each datanode will run the query and give their local counts > > and the coordinator has to collect the individual counts and > > come up with the final count before sending the output. Is the > > data collection process at the coordinator done in a > > sequential fashion? I am actually looking to introduce some > > kind of parallelism in this data collection if it is > > sequential and do performance studies. Please clarify. > > > > > > To improve performance of any system, first study the bottleneck, and > > target to widen that. Have you done any study of Postgres-XC to find > > where the performance bottleneck is? > > > > > > > > -- > > Thanks > > Mani > > Department of Computer Science > > Virginia Tech > > > > > ------------------------------------------------------------------------------ > > > > _______________________________________________ > > Postgres-xc-developers mailing list > > Pos...@li... > > > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > > > > > > > > > > > -- > > -- > > Abbas > > Architect > > > > > > Ph: 92.334.5100153 > > > > Skype ID: gabbasb > > > > www.enterprisedb.com > > > > Follow us on Twitter > > @EnterpriseDB > > > > Visit EnterpriseDB for tutorials, webinars, whitepapers and more > > > > > -- -- *Abbas* Architect Ph: 92.334.5100153 Skype ID: gabbasb www.enterprisedb.co <http://www.enterprisedb.com/>m <http://www.enterprisedb.com/> *Follow us on Twitter* @EnterpriseDB Visit EnterpriseDB for tutorials, webinars, whitepapers <http://www.enterprisedb.com/resources-community> and more <http://www.enterprisedb.com/resources-community> |
|
From: Abbas B. <abb...@en...> - 2015-06-03 05:41:35
|
On Wed, Jun 3, 2015 at 4:11 AM, Manikandan Soundarapandian <ma...@vt...> wrote: > Hi, > > I am a graduate student working on my research in parallel databases. I > would like to know how the postgres-xc coordinator works. I understand that > the datanodes run the query in parallel and the results are collected by > the coordinator which runs any more computation that is required or just > provides the output to the client that requested the query. I would like to > know whether the coordinator does this data collection from datanodes in a > sequential fashion? > The coordinator uses multiplexed IO using select on all fds of datanodes. For more details please see pgxc_node_receive function in pgxcnode.c. The loop for reading data on all set fds is sequential, but the coordinator does not wait for data from the datanode to which the coordinator had sent the query first. > For example, lets consider we want to run the query on table *table_x* > which is hash distributed among 10 datanodes, > *select count(*) from table_x;* > Each datanode will run the query and give their local counts and the > coordinator has to collect the individual counts and come up with the final > count before sending the output. Is the data collection process at the > coordinator done in a sequential fashion? I am actually looking to > introduce some kind of parallelism in this data collection if it is > sequential and do performance studies. Please clarify. > To improve performance of any system, first study the bottleneck, and target to widen that. Have you done any study of Postgres-XC to find where the performance bottleneck is? > > -- > Thanks > Mani > Department of Computer Science > Virginia Tech > > > ------------------------------------------------------------------------------ > > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > -- -- *Abbas* Architect Ph: 92.334.5100153 Skype ID: gabbasb www.enterprisedb.co <http://www.enterprisedb.com/>m <http://www.enterprisedb.com/> *Follow us on Twitter* @EnterpriseDB Visit EnterpriseDB for tutorials, webinars, whitepapers <http://www.enterprisedb.com/resources-community> and more <http://www.enterprisedb.com/resources-community> |
|
From: Manikandan S. <ma...@vt...> - 2015-06-03 05:18:11
|
Hi Abbas, Thanks for the reply. I understand how pgxc_node_receive is working to get incoming data from the datanodes. Yes, I have made experiments to study the postgres-xc system in a setup with 12 datanodes and a coordinator and performed join, groupby and order by operations on two tables with around 20 million and 10 million records respectively. I have found that around 20-30% of the time is spent in the 'FetchTuple' method which reads into the provided tupleslot one datarow at a time from the combiner's buffer which is in turn filled by the pgxc_node_receive method. Thanks On Wed, 2015-06-03 at 09:45 +0500, Abbas Butt wrote: > > > On Wed, Jun 3, 2015 at 4:11 AM, Manikandan Soundarapandian > <ma...@vt...> wrote: > Hi, > > > I am a graduate student working on my research in parallel > databases. I would like to know how the postgres-xc > coordinator works. I understand that the datanodes run the > query in parallel and the results are collected by the > coordinator which runs any more computation that is required > or just provides the output to the client that requested the > query. I would like to know whether the coordinator does this > data collection from datanodes in a sequential fashion? > > > The coordinator uses multiplexed IO using select on all fds of > datanodes. For more details please see pgxc_node_receive function in > pgxcnode.c. The loop for reading data on all set fds is sequential, > but the coordinator does not wait for data from the datanode to which > the coordinator had sent the query first. > > For example, lets consider we want to run the query on table > table_x which is hash distributed among 10 datanodes, > select count(*) from table_x; > Each datanode will run the query and give their local counts > and the coordinator has to collect the individual counts and > come up with the final count before sending the output. Is the > data collection process at the coordinator done in a > sequential fashion? I am actually looking to introduce some > kind of parallelism in this data collection if it is > sequential and do performance studies. Please clarify. > > > To improve performance of any system, first study the bottleneck, and > target to widen that. Have you done any study of Postgres-XC to find > where the performance bottleneck is? > > > > -- > Thanks > Mani > Department of Computer Science > Virginia Tech > > ------------------------------------------------------------------------------ > > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > > > > > -- > -- > Abbas > Architect > > > Ph: 92.334.5100153 > > Skype ID: gabbasb > > www.enterprisedb.com > > Follow us on Twitter > @EnterpriseDB > > Visit EnterpriseDB for tutorials, webinars, whitepapers and more > |
|
From: Manikandan S. <ma...@vt...> - 2015-06-02 23:11:35
|
Hi, I am a graduate student working on my research in parallel databases. I would like to know how the postgres-xc coordinator works. I understand that the datanodes run the query in parallel and the results are collected by the coordinator which runs any more computation that is required or just provides the output to the client that requested the query. I would like to know whether the coordinator does this data collection from datanodes in a sequential fashion? For example, lets consider we want to run the query on table *table_x* which is hash distributed among 10 datanodes, *select count(*) from table_x;* Each datanode will run the query and give their local counts and the coordinator has to collect the individual counts and come up with the final count before sending the output. Is the data collection process at the coordinator done in a sequential fashion? I am actually looking to introduce some kind of parallelism in this data collection if it is sequential and do performance studies. Please clarify. -- Thanks Mani Department of Computer Science Virginia Tech |
|
From: Koichi S. <koi...@gm...> - 2015-05-18 06:41:21
|
Thanks Ashutosh for the reply. --- Koichi Suzuki 2015-05-15 16:13 GMT+09:00 Ashutosh Bapat <ash...@en...>: > > > On Fri, May 15, 2015 at 12:13 PM, Manikandan Soundarapandian < > ma...@vt...> wrote: > >> Hi, >> >> Am trying to understand how the coordinator node collects query results >> from the datanodes. I see the method handle_response() >> in ../backend/pgxc/pool/execRemote.c is called for every connection >> handle. In an infinite for loop, it collects messages from the data >> nodes and combines the results. I am confused between the two case >> statements 'D' and 'd'. What is the difference between these two cases? >> What exactly is the difference between the methods 'HandleDataRow' and >> 'HandleCopyDataRow' and can you give an example when each of these >> methods are invoked? Please help. This will help in my research work. >> >> > I guess HandleDataRow handles "data" row of a query result. The other > function handles the output of COPY command. These two have different forms. > > >> Thanks >> Mani >> >> >> >> ------------------------------------------------------------------------------ >> One dashboard for servers and applications across Physical-Virtual-Cloud >> Widest out-of-the-box monitoring support with 50+ applications >> Performance metrics, stats and reports that give you Actionable Insights >> Deep dive visibility with transaction tracing using APM Insight. >> http://ad.doubleclick.net/ddm/clk/290420510;117567292;y >> _______________________________________________ >> Postgres-xc-developers mailing list >> Pos...@li... >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >> > > > > -- > Best Wishes, > Ashutosh Bapat > EnterpriseDB Corporation > The Postgres Database Company > > > ------------------------------------------------------------------------------ > One dashboard for servers and applications across Physical-Virtual-Cloud > Widest out-of-the-box monitoring support with 50+ applications > Performance metrics, stats and reports that give you Actionable Insights > Deep dive visibility with transaction tracing using APM Insight. > http://ad.doubleclick.net/ddm/clk/290420510;117567292;y > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > |
|
From: Manikandan S. <ma...@vt...> - 2015-05-15 07:19:28
|
Hi Ashutosh, Thanks for the reply. Do you mean the COPY command like the following that we use to copy query result to a file, COPY (select * from xxx) to '/home/xx.txt' Thanks Mani On Fri, 2015-05-15 at 12:43 +0530, Ashutosh Bapat wrote: > > > On Fri, May 15, 2015 at 12:13 PM, Manikandan Soundarapandian > <ma...@vt...> wrote: > Hi, > > Am trying to understand how the coordinator node collects > query results > from the datanodes. I see the method handle_response() > in ../backend/pgxc/pool/execRemote.c is called for every > connection > handle. In an infinite for loop, it collects messages from the > data > nodes and combines the results. I am confused between the two > case > statements 'D' and 'd'. What is the difference between these > two cases? > What exactly is the difference between the methods > 'HandleDataRow' and > 'HandleCopyDataRow' and can you give an example when each of > these > methods are invoked? Please help. This will help in my > research work. > > > > I guess HandleDataRow handles "data" row of a query result. The other > function handles the output of COPY command. These two have different > forms. > > > Thanks > Mani > > > ------------------------------------------------------------------------------ > One dashboard for servers and applications across > Physical-Virtual-Cloud > Widest out-of-the-box monitoring support with 50+ applications > Performance metrics, stats and reports that give you > Actionable Insights > Deep dive visibility with transaction tracing using APM > Insight. > http://ad.doubleclick.net/ddm/clk/290420510;117567292;y > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > > > -- > Best Wishes, > Ashutosh Bapat > EnterpriseDB Corporation > The Postgres Database Company > |
|
From: Ashutosh B. <ash...@en...> - 2015-05-15 07:14:00
|
On Fri, May 15, 2015 at 12:13 PM, Manikandan Soundarapandian <ma...@vt...> wrote: > Hi, > > Am trying to understand how the coordinator node collects query results > from the datanodes. I see the method handle_response() > in ../backend/pgxc/pool/execRemote.c is called for every connection > handle. In an infinite for loop, it collects messages from the data > nodes and combines the results. I am confused between the two case > statements 'D' and 'd'. What is the difference between these two cases? > What exactly is the difference between the methods 'HandleDataRow' and > 'HandleCopyDataRow' and can you give an example when each of these > methods are invoked? Please help. This will help in my research work. > > I guess HandleDataRow handles "data" row of a query result. The other function handles the output of COPY command. These two have different forms. > Thanks > Mani > > > > ------------------------------------------------------------------------------ > One dashboard for servers and applications across Physical-Virtual-Cloud > Widest out-of-the-box monitoring support with 50+ applications > Performance metrics, stats and reports that give you Actionable Insights > Deep dive visibility with transaction tracing using APM Insight. > http://ad.doubleclick.net/ddm/clk/290420510;117567292;y > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company |
|
From: Manikandan S. <ma...@vt...> - 2015-05-15 06:43:25
|
Hi, Am trying to understand how the coordinator node collects query results from the datanodes. I see the method handle_response() in ../backend/pgxc/pool/execRemote.c is called for every connection handle. In an infinite for loop, it collects messages from the data nodes and combines the results. I am confused between the two case statements 'D' and 'd'. What is the difference between these two cases? What exactly is the difference between the methods 'HandleDataRow' and 'HandleCopyDataRow' and can you give an example when each of these methods are invoked? Please help. This will help in my research work. Thanks Mani |
|
From: Koichi S. <koi...@gm...> - 2015-04-28 01:16:33
|
Thanks a lot for your interest in XC. 1.2.1 is the latest. It is available from http://sourceforge.net/projects/postgres-xc/files/Version_1.2/ You can find the onlinbe document at http://postgres-xc.sourceforge.net/docs/1_2_1/ If you prefer 1.1, it is also available at http://sourceforge.net/projects/postgres-xc/files/Version_1.1/ and its online document is at http://postgres-xc.sourceforge.net/docs/1_1/ (1.1.1 contains only bug fix and no change in the document). Regards; --- Koichi Suzuki 2015-04-28 9:29 GMT+09:00 Joakim Lundgren <lun...@gm...>: > Hi! > > I'm new to Postgres-xc. We have decided to use pgxc in a project that I'm > attending. I am therefore planning to download the latest, stable version > and set up a three node cluster for further evaluation. > > Am I right when I assume that 1.0.4 is the latest version, recommended to > use? There is also a version 1.2.1 dated the day before 1.0.4. This makes me > a bit confused which one to use. > > I would be more than happy if can get an advice by somebody, which version > to use > > One more thing that puzzles me some, is that the date of both 1.0.4 and > 1.2.1 is more than one year old. I would therefore be happy to understand > how active this project have been recently (and even more important, for the > future) in order to build a strategic product upon. Any information about > this would also be very helpful! > > Thanks! > > Joakim > > > > ------------------------------------------------------------------------------ > One dashboard for servers and applications across Physical-Virtual-Cloud > Widest out-of-the-box monitoring support with 50+ applications > Performance metrics, stats and reports that give you Actionable Insights > Deep dive visibility with transaction tracing using APM Insight. > http://ad.doubleclick.net/ddm/clk/290420510;117567292;y > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > |
|
From: Joakim L. <lun...@gm...> - 2015-04-28 00:29:17
|
Hi! I'm new to Postgres-xc. We have decided to use pgxc in a project that I'm attending. I am therefore planning to download the latest, stable version and set up a three node cluster for further evaluation. Am I right when I assume that 1.0.4 is the latest version, recommended to use? There is also a version 1.2.1 dated the day before 1.0.4. This makes me a bit confused which one to use. I would be more than happy if can get an advice by somebody, which version to use One more thing that puzzles me some, is that the date of both 1.0.4 and 1.2.1 is more than one year old. I would therefore be happy to understand how active this project have been recently (and even more important, for the future) in order to build a strategic product upon. Any information about this would also be very helpful! Thanks! Joakim |
|
From: 鈴木 幸市 <ko...@in...> - 2015-04-13 06:55:18
|
For materials, please visit Postgres-XC site at sourceforge (postgres-xc.org<http://postgres-xc.org> will bring you to the site). You will find Wiki page entry from the page. There will be many entries to the material. Pgxc documentation site will be from there. I’m planning to publish another writing on PGXC implementation, describing its architecture and internals. Regards; --- Koichi Suzuki NTT DATA Intellilink Corp. ko...@in...<mailto:ko...@in...>.jp 2015/04/10 19:22、Rajeev rastogi <raj...@hu...<mailto:raj...@hu...>> のメール： Hi, I am newbie to this open source group and would like to contribute to Postgres-XC. I have knowledge of PostgreSQL and basic concept of Postgres-XC but in order to contribute in terms of code, if I can get some direction like 1. List of all latest document including architecture document, design or any other corresponding document to understand code. 2. Set-up related information 3. Anything else which you feel important. I have got few document by googling but it is better If I can get something from developers of this project. Thanks and Regards, Kumar Rajeev Rastogi ------------------------------------------------------------------------------ BPM Camp - Free Virtual Workshop May 6th at 10am PDT/1PM EDT Develop your own process in accordance with the BPMN 2 standard Learn Process modeling best practices with Bonita BPM through live exercises http://www.bonitasoft.com/be-part-of-it/events/bpm-camp-virtual- event?utm_ source=Sourceforge_BPM_Camp_5_6_15&utm_medium=email&utm_campaign=VA_SF_______________________________________________ Postgres-xc-developers mailing list Pos...@li...<mailto:Pos...@li...> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers |
|
From: Rajeev r. <raj...@hu...> - 2015-04-10 10:32:18
|
Hi, I am newbie to this open source group and would like to contribute to Postgres-XC. I have knowledge of PostgreSQL and basic concept of Postgres-XC but in order to contribute in terms of code, if I can get some direction like 1. List of all latest document including architecture document, design or any other corresponding document to understand code. 2. Set-up related information 3. Anything else which you feel important. I have got few document by googling but it is better If I can get something from developers of this project. Thanks and Regards, Kumar Rajeev Rastogi |
261 messages has been excluded from this view by a project administrator.