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: Ahsan H. <ahs...@en...> - 2013-07-16 06:53:12
|
On Fri, Jul 12, 2013 at 6:11 PM, Pavan Deolasee <pav...@gm...>wrote: > > > > > Thanks Pavan, it does seem like a bug in FQS with joins. It is most > likely a new bug in 1.1 with left join optimization. > > > > Do you think you can have a go at submitting a patch for this? It will > be good to fix this for 1.1 GA. > > I would have loved to do that, but I may not find time because of other > obligations. > Can you please open a sourceforge ticket for this bug. > > Thanks, > Pavan -- Ahsan Hadi Snr Director Product Development EnterpriseDB Corporation The Enterprise Postgres Company Phone: +92-51-8358874 Mobile: +92-333-5162114 Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message. |
|
From: Adam D. <ada...@gm...> - 2013-07-16 05:45:35
|
---------- Forwarded message ---------- From: Adam Dec <ada...@gm...> Date: 2013/7/16 Subject: Re: [Postgres-xc-developers] Data distribution by hash - general question To: Ashutosh Bapat <ash...@en...> Cool! Thanks...but I was just wondering if there is some example DDL that I could use? Any examples? 2013/7/16 Ashutosh Bapat <ash...@en...> > HI Adam, > To answer your particular question about distribution of tables for > effective joins, please watch my presentation here > http://www.youtube.com/watch?v=g_9CYat8nkY > The slides can be found at > http://wiki.postgresql.org/images/f/f6/PGXC_Scalability_PGOpen2012.pdf. > > HTH. > > > On Mon, Jul 15, 2013 at 6:33 PM, Adam Dec <ada...@gm...> wrote: > >> Hi! >> >> My topology: 2 machines (on each machine 1 master coordinator and 1 >> master datannode) >> >> Lets say that I have a table A which has >> - one-to-many relation with table B >> - one-to-many relation to table C >> - one-to-one relation with table D >> >> I would like to distribute it in the cluster. All I have to do is to put >> DISTRIBUTE BY HASH(id); in each of the tables while creating them? >> id - primary key >> >> In my example Table A is like a root of the graph. How to create such a >> "graph of tables" to be shure >> that when I will invoke a select with joins all the proceesin will be >> done only on the one node. >> I do not want to replicate all the data. >> >> Where can I read about data distribution in Postgres XC? Do you have any >> examples that I could look at? >> >> >> Regards, >> >> Adam Dec >> >> >> ------------------------------------------------------------------------------ >> See everything from the browser to the database with AppDynamics >> Get end-to-end visibility with application monitoring from AppDynamics >> Isolate bottlenecks and diagnose root cause in seconds. >> Start your free trial of AppDynamics Pro today! >> >> http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk >> _______________________________________________ >> Postgres-xc-developers mailing list >> Pos...@li... >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >> >> > > > -- > Best Wishes, > Ashutosh Bapat > EntepriseDB Corporation > The Postgres Database Company > |
|
From: Adam D. <ada...@gm...> - 2013-07-16 05:44:23
|
OK so all i need is to use pg_dumpall in psql in any of the coordinators (this will store my DDL in the file). When I want to restore it is just like to connect to anyy of the coordinator, execute psql and load the DDL file using -f option or \i in interactive mode? 2013/7/16 Ashutosh Bapat <ash...@en...> > Hi Adam, > You do not need to follow these steps, if you are configuring your cluster > the first time. You may follow installation steps there. > > > On Mon, Jul 15, 2013 at 5:44 PM, Adam Dec <ada...@gm...> wrote: > >> Hi! >> My topology is like this: >> I have to machines. On each machine I have 1 master coordinator and 1 >> master datanode, >> >> I have created a backup (only DDL as I read from here: >> http://postgres-xc.sourceforge.net/docs/1_1_beta/add-node-datanode.html) >> process, please advice me if this does make any sense to you: >> >> 1. Creating backup >> >> - Connect to the any of the coordinators (lets say the first one): >> >> ./bin/psql -p 20011 -h 192.168.123.195 -d sts -U sts >> >> - Lock the cluster for backup, do not close this session >> >> select pgxc_lock_for_backup(); >> >> More: >> http://postgres-xc.sourceforge.net/docs/1_1_beta/functions-admin.html#FUNCTIONS-PGXC-ADD-NEW-NODE >> >> - Connect to the any of the coordinators and backup the data: >> >> ./pg_dumpall -p 20011 -s --include-nodes --dump-nodes --file= >> /opt/backup/sts_ddl_backup.sql >> >> Only schema (i.e. no data) is to be dumped. Note the use of >> --include-nodes, so that the CREATE TABLE contains TO NODE clause. >> >> Similarly --dump-nodes ensures that the dump does contain existing nodes >> and node groups. >> 2. Loading backup >> >> - Stop all the coordinators: >> >> ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/coord1/ -Z coordinator -l >> /opt/postgres-xc-1.1/logs/coord1.log -mf & >> ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/coord2/ -Z coordinator -l >> /opt/postgres-xc-1.1/logs/coord2.log -mf & >> >> - Stop all the datanodes: >> >> ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/datanode1/ -Z datanode -l >> /opt/postgres-xc-1.1/logs/datanode1.log -mf & >> ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/datanode2/ -Z datanode -l >> /opt/postgres-xc-1.1/logs/datanode2.log -mf & >> >> - Start the coordinators in restore mode: >> >> ./bin/pg_ctl start -Z restoremode -D /opt/postgres-xc-1.1/coord1/ -l >> /opt/postgres-xc-1.1/logs/coord1.log & >> ./bin/pg_ctl start -Z restoremode -D /opt/postgres-xc-1.1/coord2/ -l >> /opt/postgres-xc-1.1/logs/coord2.log & >> >> - Start the datanodes in restore mode: >> >> ./bin/pg_ctl start -Z restoremode -D /opt/postgres-xc-1.1/datanode1/ -l >> /opt/postgres-xc-1.1/logs/datanode1.log & >> ./bin/pg_ctl start -Z restoremode -D /opt/postgres-xc-1.1/datanode2/ -l >> /opt/postgres-xc-1.1/logs/datanode2.log & >> >> - Connect to the any of the coordinators (lets say the first one): >> >> ./bin/psql -p 20011 -h 192.168.123.195 -d sts -U sts >> >> - Restore DDL data: >> >> ./bin/psql -p 20011 -h 192.168.123.195 -d sts -U sts -f >> /opt/backup/sts_ddl_backup.sql >> >> - Stop the coordinators ( this will unlock the cluster -> select >> pgxc_lock_for_backup(); ): >> >> ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/coord1/ -Z coordinator -l >> /opt/postgres-xc-1.1/logs/coord1.log -mf & >> ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/coord2/ -Z coordinator -l >> /opt/postgres-xc-1.1/logs/coord2.log -mf & >> >> - Stop the datanodes >> >> ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/datanode1/ -Z datanode -l >> /opt/postgres-xc-1.1/logs/datanode1.log -mf & >> ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/datanode2/ -Z datanode -l >> /opt/postgres-xc-1.1/logs/datanode2.log -mf & >> >> - Start the coordinators: >> >> ./bin/pg_ctl start -D /opt/postgres-xc-1.1/coord1/ -Z coordinator -l >> /opt/postgres-xc-1.1/logs/coord1.log & >> ./bin/pg_ctl start -D /opt/postgres-xc-1.1/coord2/ -Z coordinator -l >> /opt/postgres-xc-1.1/logs/coord2.log & >> >> - Start the datanodes: >> >> ./bin/pg_ctl start -D /opt/postgres-xc-1.1/datanode1/ -Z datanode -l >> /opt/postgres-xc-1.1/logs/datanode1.log & >> ./bin/pg_ctl start -D /opt/postgres-xc-1.1/datanode2/ -Z datanode -l >> /opt/postgres-xc-1.1/logs/datanode2.log & >> >> >> >> >> ------------------------------------------------------------------------------ >> See everything from the browser to the database with AppDynamics >> Get end-to-end visibility with application monitoring from AppDynamics >> Isolate bottlenecks and diagnose root cause in seconds. >> Start your free trial of AppDynamics Pro today! >> >> http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk >> _______________________________________________ >> Postgres-xc-developers mailing list >> Pos...@li... >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >> >> > > > -- > Best Wishes, > Ashutosh Bapat > EntepriseDB Corporation > The Postgres Database Company > |
|
From: Ashutosh B. <ash...@en...> - 2013-07-16 05:24:34
|
HI Adam, To answer your particular question about distribution of tables for effective joins, please watch my presentation here http://www.youtube.com/watch?v=g_9CYat8nkY The slides can be found at http://wiki.postgresql.org/images/f/f6/PGXC_Scalability_PGOpen2012.pdf. HTH. On Mon, Jul 15, 2013 at 6:33 PM, Adam Dec <ada...@gm...> wrote: > Hi! > > My topology: 2 machines (on each machine 1 master coordinator and 1 master > datannode) > > Lets say that I have a table A which has > - one-to-many relation with table B > - one-to-many relation to table C > - one-to-one relation with table D > > I would like to distribute it in the cluster. All I have to do is to put > DISTRIBUTE BY HASH(id); in each of the tables while creating them? > id - primary key > > In my example Table A is like a root of the graph. How to create such a > "graph of tables" to be shure > that when I will invoke a select with joins all the proceesin will be done > only on the one node. > I do not want to replicate all the data. > > Where can I read about data distribution in Postgres XC? Do you have any > examples that I could look at? > > > Regards, > > Adam Dec > > > ------------------------------------------------------------------------------ > See everything from the browser to the database with AppDynamics > Get end-to-end visibility with application monitoring from AppDynamics > Isolate bottlenecks and diagnose root cause in seconds. > Start your free trial of AppDynamics Pro today! > http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > -- Best Wishes, Ashutosh Bapat EntepriseDB Corporation The Postgres Database Company |
|
From: Ashutosh B. <ash...@en...> - 2013-07-16 05:20:09
|
Hi Adam, You do not need to follow these steps, if you are configuring your cluster the first time. You may follow installation steps there. On Mon, Jul 15, 2013 at 5:44 PM, Adam Dec <ada...@gm...> wrote: > Hi! > My topology is like this: > I have to machines. On each machine I have 1 master coordinator and 1 > master datanode, > > I have created a backup (only DDL as I read from here: > http://postgres-xc.sourceforge.net/docs/1_1_beta/add-node-datanode.html) > process, please advice me if this does make any sense to you: > > 1. Creating backup > > - Connect to the any of the coordinators (lets say the first one): > > ./bin/psql -p 20011 -h 192.168.123.195 -d sts -U sts > > - Lock the cluster for backup, do not close this session > > select pgxc_lock_for_backup(); > > More: > http://postgres-xc.sourceforge.net/docs/1_1_beta/functions-admin.html#FUNCTIONS-PGXC-ADD-NEW-NODE > > - Connect to the any of the coordinators and backup the data: > > ./pg_dumpall -p 20011 -s --include-nodes --dump-nodes --file= > /opt/backup/sts_ddl_backup.sql > > Only schema (i.e. no data) is to be dumped. Note the use of > --include-nodes, so that the CREATE TABLE contains TO NODE clause. > > Similarly --dump-nodes ensures that the dump does contain existing nodes > and node groups. > 2. Loading backup > > - Stop all the coordinators: > > ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/coord1/ -Z coordinator -l > /opt/postgres-xc-1.1/logs/coord1.log -mf & > ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/coord2/ -Z coordinator -l > /opt/postgres-xc-1.1/logs/coord2.log -mf & > > - Stop all the datanodes: > > ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/datanode1/ -Z datanode -l > /opt/postgres-xc-1.1/logs/datanode1.log -mf & > ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/datanode2/ -Z datanode -l > /opt/postgres-xc-1.1/logs/datanode2.log -mf & > > - Start the coordinators in restore mode: > > ./bin/pg_ctl start -Z restoremode -D /opt/postgres-xc-1.1/coord1/ -l > /opt/postgres-xc-1.1/logs/coord1.log & > ./bin/pg_ctl start -Z restoremode -D /opt/postgres-xc-1.1/coord2/ -l > /opt/postgres-xc-1.1/logs/coord2.log & > > - Start the datanodes in restore mode: > > ./bin/pg_ctl start -Z restoremode -D /opt/postgres-xc-1.1/datanode1/ -l > /opt/postgres-xc-1.1/logs/datanode1.log & > ./bin/pg_ctl start -Z restoremode -D /opt/postgres-xc-1.1/datanode2/ -l > /opt/postgres-xc-1.1/logs/datanode2.log & > > - Connect to the any of the coordinators (lets say the first one): > > ./bin/psql -p 20011 -h 192.168.123.195 -d sts -U sts > > - Restore DDL data: > > ./bin/psql -p 20011 -h 192.168.123.195 -d sts -U sts -f > /opt/backup/sts_ddl_backup.sql > > - Stop the coordinators ( this will unlock the cluster -> select > pgxc_lock_for_backup(); ): > > ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/coord1/ -Z coordinator -l > /opt/postgres-xc-1.1/logs/coord1.log -mf & > ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/coord2/ -Z coordinator -l > /opt/postgres-xc-1.1/logs/coord2.log -mf & > > - Stop the datanodes > > ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/datanode1/ -Z datanode -l > /opt/postgres-xc-1.1/logs/datanode1.log -mf & > ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/datanode2/ -Z datanode -l > /opt/postgres-xc-1.1/logs/datanode2.log -mf & > > - Start the coordinators: > > ./bin/pg_ctl start -D /opt/postgres-xc-1.1/coord1/ -Z coordinator -l > /opt/postgres-xc-1.1/logs/coord1.log & > ./bin/pg_ctl start -D /opt/postgres-xc-1.1/coord2/ -Z coordinator -l > /opt/postgres-xc-1.1/logs/coord2.log & > > - Start the datanodes: > > ./bin/pg_ctl start -D /opt/postgres-xc-1.1/datanode1/ -Z datanode -l > /opt/postgres-xc-1.1/logs/datanode1.log & > ./bin/pg_ctl start -D /opt/postgres-xc-1.1/datanode2/ -Z datanode -l > /opt/postgres-xc-1.1/logs/datanode2.log & > > > > > ------------------------------------------------------------------------------ > See everything from the browser to the database with AppDynamics > Get end-to-end visibility with application monitoring from AppDynamics > Isolate bottlenecks and diagnose root cause in seconds. > Start your free trial of AppDynamics Pro today! > http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > -- Best Wishes, Ashutosh Bapat EntepriseDB Corporation The Postgres Database Company |
|
From: Koichi S. <koi...@gm...> - 2013-07-16 04:59:04
|
Many of the presentation materials available from Postgres-XC Wiki page will help. Please visit http://postgresxc.wikia.com/wiki/Postgres-XC_Wiki PGCon material in 2010 will come with table distribution in DBT-1 benchmark. Regards; ---------- Koichi Suzuki 2013/7/15 Adam Dec <ada...@gm...> > Hi! > > My topology: 2 machines (on each machine 1 master coordinator and 1 master > datannode) > > Lets say that I have a table A which has > - one-to-many relation with table B > - one-to-many relation to table C > - one-to-one relation with table D > > I would like to distribute it in the cluster. All I have to do is to put > DISTRIBUTE BY HASH(id); in each of the tables while creating them? > id - primary key > > In my example Table A is like a root of the graph. How to create such a > "graph of tables" to be shure > that when I will invoke a select with joins all the proceesin will be done > only on the one node. > I do not want to replicate all the data. > > Where can I read about data distribution in Postgres XC? Do you have any > examples that I could look at? > > > Regards, > > Adam Dec > > > ------------------------------------------------------------------------------ > See everything from the browser to the database with AppDynamics > Get end-to-end visibility with application monitoring from AppDynamics > Isolate bottlenecks and diagnose root cause in seconds. > Start your free trial of AppDynamics Pro today! > http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > |
|
From: Koichi S. <koi...@gm...> - 2013-07-16 04:52:38
|
You don't need to issue "select pgxc_lock_for_backup();". This function blocks DDL and is used when you add new coordinator/datanode dynamically. You don't have to restart coordinator/datanode. When you issue pg_dump/pg_dumpall, you just call it from any coordinator. You don't have to stop coordinator/datanode. This is a logical database backup. Regards; ---------- Koichi Suzuki 2013/7/15 Adam Dec <ada...@gm...> > Hi! > My topology is like this: > I have to machines. On each machine I have 1 master coordinator and 1 > master datanode, > > I have created a backup (only DDL as I read from here: > http://postgres-xc.sourceforge.net/docs/1_1_beta/add-node-datanode.html) > process, please advice me if this does make any sense to you: > > 1. Creating backup > > - Connect to the any of the coordinators (lets say the first one): > > ./bin/psql -p 20011 -h 192.168.123.195 -d sts -U sts > > - Lock the cluster for backup, do not close this session > > select pgxc_lock_for_backup(); > > More: > http://postgres-xc.sourceforge.net/docs/1_1_beta/functions-admin.html#FUNCTIONS-PGXC-ADD-NEW-NODE > > - Connect to the any of the coordinators and backup the data: > > ./pg_dumpall -p 20011 -s --include-nodes --dump-nodes --file= > /opt/backup/sts_ddl_backup.sql > > Only schema (i.e. no data) is to be dumped. Note the use of > --include-nodes, so that the CREATE TABLE contains TO NODE clause. > > Similarly --dump-nodes ensures that the dump does contain existing nodes > and node groups. > 2. Loading backup > > - Stop all the coordinators: > > ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/coord1/ -Z coordinator -l > /opt/postgres-xc-1.1/logs/coord1.log -mf & > ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/coord2/ -Z coordinator -l > /opt/postgres-xc-1.1/logs/coord2.log -mf & > > - Stop all the datanodes: > > ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/datanode1/ -Z datanode -l > /opt/postgres-xc-1.1/logs/datanode1.log -mf & > ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/datanode2/ -Z datanode -l > /opt/postgres-xc-1.1/logs/datanode2.log -mf & > > - Start the coordinators in restore mode: > > ./bin/pg_ctl start -Z restoremode -D /opt/postgres-xc-1.1/coord1/ -l > /opt/postgres-xc-1.1/logs/coord1.log & > ./bin/pg_ctl start -Z restoremode -D /opt/postgres-xc-1.1/coord2/ -l > /opt/postgres-xc-1.1/logs/coord2.log & > > - Start the datanodes in restore mode: > > ./bin/pg_ctl start -Z restoremode -D /opt/postgres-xc-1.1/datanode1/ -l > /opt/postgres-xc-1.1/logs/datanode1.log & > ./bin/pg_ctl start -Z restoremode -D /opt/postgres-xc-1.1/datanode2/ -l > /opt/postgres-xc-1.1/logs/datanode2.log & > > - Connect to the any of the coordinators (lets say the first one): > > ./bin/psql -p 20011 -h 192.168.123.195 -d sts -U sts > > - Restore DDL data: > > ./bin/psql -p 20011 -h 192.168.123.195 -d sts -U sts -f > /opt/backup/sts_ddl_backup.sql > > - Stop the coordinators ( this will unlock the cluster -> select > pgxc_lock_for_backup(); ): > > ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/coord1/ -Z coordinator -l > /opt/postgres-xc-1.1/logs/coord1.log -mf & > ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/coord2/ -Z coordinator -l > /opt/postgres-xc-1.1/logs/coord2.log -mf & > > - Stop the datanodes > > ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/datanode1/ -Z datanode -l > /opt/postgres-xc-1.1/logs/datanode1.log -mf & > ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/datanode2/ -Z datanode -l > /opt/postgres-xc-1.1/logs/datanode2.log -mf & > > - Start the coordinators: > > ./bin/pg_ctl start -D /opt/postgres-xc-1.1/coord1/ -Z coordinator -l > /opt/postgres-xc-1.1/logs/coord1.log & > ./bin/pg_ctl start -D /opt/postgres-xc-1.1/coord2/ -Z coordinator -l > /opt/postgres-xc-1.1/logs/coord2.log & > > - Start the datanodes: > > ./bin/pg_ctl start -D /opt/postgres-xc-1.1/datanode1/ -Z datanode -l > /opt/postgres-xc-1.1/logs/datanode1.log & > ./bin/pg_ctl start -D /opt/postgres-xc-1.1/datanode2/ -Z datanode -l > /opt/postgres-xc-1.1/logs/datanode2.log & > > > > > ------------------------------------------------------------------------------ > See everything from the browser to the database with AppDynamics > Get end-to-end visibility with application monitoring from AppDynamics > Isolate bottlenecks and diagnose root cause in seconds. > Start your free trial of AppDynamics Pro today! > http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > |
|
From: 鈴木 幸市 <ko...@in...> - 2013-07-16 02:25:23
|
I added this to the bug tracker, ID = 3614714. Regards; --- Koichi Suzuki On 2013/07/11, at 16:04, Pavan Deolasee <pav...@gm...> wrote: > Hello All, > > I wonder how do we currently handle node failures in XC setup. I understand, we recommend setting up replicated slaves for all the datanodes and adjust the coordinator node information in case of fail over, say by using ALTER NODE. What I am more curious is about working at a reduced availability. Let me give an example: > > Say I have a table distributed by HASH on 4 nodes. If one of those nodes go down, I would still like to use the table by somehow adjusting the metadata at the coordinator. The data stored on the failed node will not be accessible, but I should be able to query the data from the remaining nodes. So SELECTs should not fail or at the least SELECT with a qualification which filters out data from the failed node should not fail. Also, INSERT/UPDATE/DELETE should succeed as long as the target node is up and running. > > I tried to run a few queries to see if this is possible, but could not get the desired result. I only had limited success with replicated tables, in a sense that I could query the table after dropping the failed node at the coordinator. But even that looks fragile. > > Are there any action items around this area targeted in the immediate roadmap for XC ? > > Thanks, > Pavan > > > -- > Pavan Deolasee > http://www.linkedin.com/in/pavandeolasee > ------------------------------------------------------------------------------ > See everything from the browser to the database with AppDynamics > Get end-to-end visibility with application monitoring from AppDynamics > Isolate bottlenecks and diagnose root cause in seconds. > Start your free trial of AppDynamics Pro today! > http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk_______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers |
|
From: Adam D. <ada...@gm...> - 2013-07-15 13:03:55
|
Hi! My topology: 2 machines (on each machine 1 master coordinator and 1 master datannode) Lets say that I have a table A which has - one-to-many relation with table B - one-to-many relation to table C - one-to-one relation with table D I would like to distribute it in the cluster. All I have to do is to put DISTRIBUTE BY HASH(id); in each of the tables while creating them? id - primary key In my example Table A is like a root of the graph. How to create such a "graph of tables" to be shure that when I will invoke a select with joins all the proceesin will be done only on the one node. I do not want to replicate all the data. Where can I read about data distribution in Postgres XC? Do you have any examples that I could look at? Regards, Adam Dec |
|
From: Adam D. <ada...@gm...> - 2013-07-15 12:19:30
|
Hi! In order to make work this new Postgres XC tool we have to setup autlogin (key based auth for ssh). This is how I done it for OracleLinux: - First login to host (where you have pgxc_ctl) as user postgres and run: ssh-keygen (user postgres must be setup in the all machines that you want to configure). - Everything should be default so just hit ENTER couple of times (especially for no password if you want to login only by key without being asked of anything). - This should result with key pair generated in ~/.ssh directory. - Now you need to copy the public key to each of the hosts you want to login: ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@host1 ... ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@hostN ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@localhost You're good to login via private key |
|
From: Adam D. <ada...@gm...> - 2013-07-15 12:14:41
|
Hi! My topology is like this: I have to machines. On each machine I have 1 master coordinator and 1 master datanode, I have created a backup (only DDL as I read from here: http://postgres-xc.sourceforge.net/docs/1_1_beta/add-node-datanode.html) process, please advice me if this does make any sense to you: 1. Creating backup - Connect to the any of the coordinators (lets say the first one): ./bin/psql -p 20011 -h 192.168.123.195 -d sts -U sts - Lock the cluster for backup, do not close this session select pgxc_lock_for_backup(); More: http://postgres-xc.sourceforge.net/docs/1_1_beta/functions-admin.html#FUNCTIONS-PGXC-ADD-NEW-NODE - Connect to the any of the coordinators and backup the data: ./pg_dumpall -p 20011 -s --include-nodes --dump-nodes --file= /opt/backup/sts_ddl_backup.sql Only schema (i.e. no data) is to be dumped. Note the use of --include-nodes, so that the CREATE TABLE contains TO NODE clause. Similarly --dump-nodes ensures that the dump does contain existing nodes and node groups. 2. Loading backup - Stop all the coordinators: ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/coord1/ -Z coordinator -l /opt/postgres-xc-1.1/logs/coord1.log -mf & ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/coord2/ -Z coordinator -l /opt/postgres-xc-1.1/logs/coord2.log -mf & - Stop all the datanodes: ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/datanode1/ -Z datanode -l /opt/postgres-xc-1.1/logs/datanode1.log -mf & ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/datanode2/ -Z datanode -l /opt/postgres-xc-1.1/logs/datanode2.log -mf & - Start the coordinators in restore mode: ./bin/pg_ctl start -Z restoremode -D /opt/postgres-xc-1.1/coord1/ -l /opt/postgres-xc-1.1/logs/coord1.log & ./bin/pg_ctl start -Z restoremode -D /opt/postgres-xc-1.1/coord2/ -l /opt/postgres-xc-1.1/logs/coord2.log & - Start the datanodes in restore mode: ./bin/pg_ctl start -Z restoremode -D /opt/postgres-xc-1.1/datanode1/ -l /opt/postgres-xc-1.1/logs/datanode1.log & ./bin/pg_ctl start -Z restoremode -D /opt/postgres-xc-1.1/datanode2/ -l /opt/postgres-xc-1.1/logs/datanode2.log & - Connect to the any of the coordinators (lets say the first one): ./bin/psql -p 20011 -h 192.168.123.195 -d sts -U sts - Restore DDL data: ./bin/psql -p 20011 -h 192.168.123.195 -d sts -U sts -f /opt/backup/sts_ddl_backup.sql - Stop the coordinators ( this will unlock the cluster -> select pgxc_lock_for_backup(); ): ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/coord1/ -Z coordinator -l /opt/postgres-xc-1.1/logs/coord1.log -mf & ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/coord2/ -Z coordinator -l /opt/postgres-xc-1.1/logs/coord2.log -mf & - Stop the datanodes ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/datanode1/ -Z datanode -l /opt/postgres-xc-1.1/logs/datanode1.log -mf & ./bin/pg_ctl stop -D /opt/postgres-xc-1.1/datanode2/ -Z datanode -l /opt/postgres-xc-1.1/logs/datanode2.log -mf & - Start the coordinators: ./bin/pg_ctl start -D /opt/postgres-xc-1.1/coord1/ -Z coordinator -l /opt/postgres-xc-1.1/logs/coord1.log & ./bin/pg_ctl start -D /opt/postgres-xc-1.1/coord2/ -Z coordinator -l /opt/postgres-xc-1.1/logs/coord2.log & - Start the datanodes: ./bin/pg_ctl start -D /opt/postgres-xc-1.1/datanode1/ -Z datanode -l /opt/postgres-xc-1.1/logs/datanode1.log & ./bin/pg_ctl start -D /opt/postgres-xc-1.1/datanode2/ -Z datanode -l /opt/postgres-xc-1.1/logs/datanode2.log & |
|
From: Adam D. <ada...@gm...> - 2013-07-15 10:12:01
|
That helped, thanks a lot. 2013/7/14 Pavan Deolasee <pav...@gm...> > > > > On Fri, Jul 12, 2013 at 2:21 PM, Adam Dec <ada...@gm...> wrote: > >> >> But I am having error: >> >> ERROR: No Datanode defined in cluster >> HINT: You need to define at least 1 Datanode with CREATE NODE. >> >> >> Am I doing something wrong here? >> > First time, you may need to call pgxc_pool_reload() to reload the pooler > information. > > Thanks, > Pavan > > -- > Pavan Deolasee > http://www.linkedin.com/in/pavandeolasee > |
|
From: Pavan D. <pav...@gm...> - 2013-07-14 17:48:18
|
On Fri, Jul 12, 2013 at 2:21 PM, Adam Dec <ada...@gm...> wrote: > > But I am having error: > > ERROR: No Datanode defined in cluster > HINT: You need to define at least 1 Datanode with CREATE NODE. > > > Am I doing something wrong here? > First time, you may need to call pgxc_pool_reload() to reload the pooler information. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee |
|
From: Pavan D. <pav...@gm...> - 2013-07-12 13:11:59
|
> > Thanks Pavan, it does seem like a bug in FQS with joins. It is most likely a new bug in 1.1 with left join optimization. > > Do you think you can have a go at submitting a patch for this? It will be good to fix this for 1.1 GA. I would have loved to do that, but I may not find time because of other obligations. Thanks, Pavan |
|
From: Adam D. <ada...@gm...> - 2013-07-12 08:51:20
|
Hi! I have got two machines: Machine 1: coord1 datanode1 Machine2: coord2 datanode2 I ma using pgpool to load balance connections between coordinators. I would like to configure coord1 to see datanode1 and datanode2 and coord2 to see datanode1 and datanode2 This is what I did: Coordinator 1: ./psql -p 20011 -h 192.168.0.195 postgres CREATE NODE data_node1 WITH (TYPE = 'datanode', HOST = '192.168.0.195', PORT = 20012); CREATE NODE data_coord2 WITH (TYPE = 'coordinator', HOST = '192.168.0.196', PORT = 20011); Coordinator 2: ./psql -p 20011 -h 192.168.0.196 postgres CREATE NODE data_node2 WITH (TYPE = 'datanode', HOST = '192.168.0.196', PORT = 20012); CREATE NODE data_coord1 WITH (TYPE = 'coordinator', HOST = '192.168.0.195', PORT = 20011); No I would like to create user and db: CREATE USER user WITH PASSWORD 'password'; CREATE DATABASE test OWNER user; But I am having error: ERROR: No Datanode defined in cluster HINT: You need to define at least 1 Datanode with CREATE NODE. Am I doing something wrong here? |
|
From: Ahsan H. <ahs...@en...> - 2013-07-12 05:50:08
|
On Thu, Jul 11, 2013 at 1:48 PM, Pavan Deolasee <pav...@gm...>wrote: > Hello All, > > The following case shows a bug in the FQS logic. I am using fairly recent > master branch (may be a few days old). So if this has been fixed recently, > please let me know: > > test=# CREATE TABLE ltab_repl (a int, b char(10)) DISTRIBUTE BY > REPLICATION; > CREATE TABLE > test=# CREATE TABLE rtab (ar int, br char(10)); > CREATE TABLE > test=# INSERT INTO ltab_repl SELECT generate_series(1,5), 'foo'; > INSERT 0 5 > test=# INSERT INTO rtab SELECT generate_series(1,4), 'bar'; > INSERT 0 4 > test=# SELECT * FROM ltab_repl ; > a | b > ---+------------ > 1 | foo > 2 | foo > 3 | foo > 4 | foo > 5 | foo > (5 rows) > > test=# SELECT * FROM rtab; > ar | br > ----+------------ > 1 | bar > 2 | bar > 3 | bar > 4 | bar > (4 rows) > > > test=# set enable_fast_query_shipping TO off; > SET > test=# SELECT * FROM ltab_repl LEFT JOIN rtab ON (a = ar); > a | b | ar | br > ---+------------+----+------------ > 1 | foo | 1 | bar > 2 | foo | 2 | bar > 3 | foo | 3 | bar > 4 | foo | 4 | bar > 5 | foo | | > (5 rows) > > test=# set enable_fast_query_shipping TO on; > SET > test=# SELECT * FROM ltab_repl LEFT JOIN rtab ON (a = ar); > a | b | ar | br > ---+------------+----+------------ > 1 | foo | 1 | bar > 2 | foo | 2 | bar > 3 | foo | | > 4 | foo | | > 5 | foo | | > 1 | foo | | > 2 | foo | | > 3 | foo | 3 | bar > 4 | foo | 4 | bar > 5 | foo | | > (10 rows) > > test=# EXPLAIN VERBOSE SELECT * FROM ltab_repl LEFT JOIN rtab ON (a = ar); > QUERY > PLAN > > ---------------------------------------------------------------------------------------------------------------------------------------------- > Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0) > Output: ltab_repl.a, ltab_repl.b, rtab.ar, rtab.br > Node/s: d1, d2 > Remote query: SELECT ltab_repl.a, ltab_repl.b, rtab.ar, rtab.br FROM > (public.ltab_repl LEFT JOIN public.rtab ON ((ltab_repl.a = rtab.ar))) > (4 rows) > > > As you would see the query is giving a wrong result when FQS is turned ON. > I don't think its correct to push down the query as it is to the datanodes. > What's happening really is that each datanode is returning 5 rows each > (since ltab_repl is a replicated table, contains 5 rows and is at the left > side of the left join) and they are being appended together. > Thanks Pavan, it does seem like a bug in FQS with joins. It is most likely a new bug in 1.1 with left join optimization. Do you think you can have a go at submitting a patch for this? It will be good to fix this for 1.1 GA. > > I think I'd suggested in the past to run regression by turning these > various optimization GUCs on/off and comparing the results. While we might > see some row ordering issues when these GUCs are turned on/off, the final > result should remain the same. Such an exercise will help to uncover such > bugs. > > I totally agree we need to run regression with different setting for various optimization GUC's as-well as running regression with different number of datanodes. We recently discovered a bug in regression that only shows up when running with 1 coordinator and 1 data-node. Additionally the regression needs to be enhanced to test more cluster features. We haven't been able to invest much time on improving regression. We would like to do that soon, it will also be good if someone from community can help with that. > Thanks, > Pavan > > -- > Pavan Deolasee > http://www.linkedin.com/in/pavandeolasee > > > ------------------------------------------------------------------------------ > See everything from the browser to the database with AppDynamics > Get end-to-end visibility with application monitoring from AppDynamics > Isolate bottlenecks and diagnose root cause in seconds. > Start your free trial of AppDynamics Pro today! > http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > -- Ahsan Hadi Snr Director Product Development EnterpriseDB Corporation The Enterprise Postgres Company Phone: +92-51-8358874 Mobile: +92-333-5162114 Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message. |
|
From: Koichi S. <koi...@gm...> - 2013-07-11 15:09:41
|
I reviewed the patch and found it is not sufficient. The problem is it
opened the file with "w" mode even when it doesn't write. In this case,
the control file will remain empty. Instead, we should have opened the
file only when it writes.
Will change the code with this regard.
Regards;
----------
Koichi Suzuki
2013/7/11 Koichi Suzuki <koi...@gm...>
> Thanks for pointing out. This will be committed both in REL1_1_STABLE
> and master.
>
>
> ----------
> Koichi Suzuki
>
>
> 2013/7/11 Himpich, Stefan <Ste...@se...>
>
> Hi there,
>>
>> while testing 1.1-beta, I ran into a lot of gtm crashes, "too many open
>> files".
>>
>> I tracked it down to a missing fclose in gtm_backup.c, see diff / patch
>> below.
>>
>> Greetings,
>> Stefan Himpich
>>
>>
>>
>> -------------------------------------------------------------------------------------
>> --- postgres-xc/src/gtm/main/gtm_backup.c.orig 2013-07-11
>> 16:16:24.751750009 +0200
>> +++ postgres-xc/src/gtm/main/gtm_backup.c 2013-07-11
>> 16:11:48.707056211 +0200
>> @@ -27,6 +27,7 @@
>> if (!gtm_need_bkup)
>> {
>> GTM_RWLockRelease(>m_bkup_lock);
>> + fclose(f);
>> return;
>> }
>> gtm_need_bkup = FALSE;
>>
>>
>> ------------------------------------------------------------------------------
>> See everything from the browser to the database with AppDynamics
>> Get end-to-end visibility with application monitoring from AppDynamics
>> Isolate bottlenecks and diagnose root cause in seconds.
>> Start your free trial of AppDynamics Pro today!
>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Postgres-xc-developers mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>
>
>
|
|
From: Koichi S. <koi...@gm...> - 2013-07-11 14:59:37
|
Thanks for pointing out. This will be committed both in REL1_1_STABLE and
master.
----------
Koichi Suzuki
2013/7/11 Himpich, Stefan <Ste...@se...>
> Hi there,
>
> while testing 1.1-beta, I ran into a lot of gtm crashes, "too many open
> files".
>
> I tracked it down to a missing fclose in gtm_backup.c, see diff / patch
> below.
>
> Greetings,
> Stefan Himpich
>
>
>
> -------------------------------------------------------------------------------------
> --- postgres-xc/src/gtm/main/gtm_backup.c.orig 2013-07-11
> 16:16:24.751750009 +0200
> +++ postgres-xc/src/gtm/main/gtm_backup.c 2013-07-11
> 16:11:48.707056211 +0200
> @@ -27,6 +27,7 @@
> if (!gtm_need_bkup)
> {
> GTM_RWLockRelease(>m_bkup_lock);
> + fclose(f);
> return;
> }
> gtm_need_bkup = FALSE;
>
>
> ------------------------------------------------------------------------------
> See everything from the browser to the database with AppDynamics
> Get end-to-end visibility with application monitoring from AppDynamics
> Isolate bottlenecks and diagnose root cause in seconds.
> Start your free trial of AppDynamics Pro today!
> http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>
|
|
From: Himpich, S. <Ste...@se...> - 2013-07-11 14:32:49
|
Hi there,
while testing 1.1-beta, I ran into a lot of gtm crashes, "too many open files".
I tracked it down to a missing fclose in gtm_backup.c, see diff / patch below.
Greetings,
Stefan Himpich
-------------------------------------------------------------------------------------
--- postgres-xc/src/gtm/main/gtm_backup.c.orig 2013-07-11 16:16:24.751750009 +0200
+++ postgres-xc/src/gtm/main/gtm_backup.c 2013-07-11 16:11:48.707056211 +0200
@@ -27,6 +27,7 @@
if (!gtm_need_bkup)
{
GTM_RWLockRelease(>m_bkup_lock);
+ fclose(f);
return;
}
gtm_need_bkup = FALSE;
|
|
From: Pavan D. <pav...@gm...> - 2013-07-11 10:07:18
|
Hello All,
I wonder if we should add appropriate dependencies in pg_depend so that a
user should not be able to DROP a NODE without first changing the
distribution information of the tables that are currently using that NODE.
I could very quickly try out a case where I deleted a NODE and the queries
on the table started returning wrong results or INSERTs started to a wrong
node.
test=# SELECT * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary |
nodeis_preferred | node_id
-----------+-----------+-----------+-----------+----------------+------------------+------------
c2 | C | 6433 | localhost | f | f
| 558734100
d2 | D | 7433 | localhost | f | f
| 823431008
C1 | C | 6432 | localhost | f | f
| -551723744
d1 | D | 7432 | localhost | f | f
| 342786568
(4 rows)
test=# CREATE TABLE test_nodedel(a int) DISTRIBUTE BY HASH(a);
CREATE TABLE
test=# INSERT INTO test_nodedel VALUES (1), (2), (3);
INSERT 0 3
test=# SELECT *, xc_node_id FROM test_nodedel ;
a | xc_node_id
---+------------
1 | 342786568
2 | 342786568
3 | 823431008
(3 rows)
test=# DROP NODE d1;
DROP NODE
test=# SELECT pgxc_pool_reload();
pgxc_pool_reload
------------------
t
(1 row)
test=# SELECT *, xc_node_id FROM test_nodedel WHERE a = 1;
a | xc_node_id
---+------------
(0 rows)
test=# SELECT *, xc_node_id FROM test_nodedel WHERE a = 2;
a | xc_node_id
---+------------
(0 rows)
Since both these rows are on datanode d1 and I just dropped that node, may
be we are not getting those rows back. Fair enough.
test=# SELECT *, xc_node_id FROM test_nodedel WHERE a = 3;
ERROR: Invalid Datanode number
Here I would have expected to get the row because d2 is still running and
is configured on the coordinator. At the least, we should give a better
error.
test=# INSERT INTO test_nodedel VALUES (1), (2);
INSERT 0 2
This INSERT works OK though and the rows get inserted without any error.
test=# CREATE NODE d1 WITH (type = datanode, port = 7432);
CREATE NODE
Now I recreated the node with the same information.
test=# SELECT pgxc_pool_reload();
pgxc_pool_reload
------------------
t
(1 row)
test=# SELECT *, xc_node_id FROM test_nodedel;
a | xc_node_id
---+------------
1 | 342786568
2 | 342786568
3 | 823431008
1 | 823431008
2 | 823431008
(5 rows)
But now we have a problem. The last two tuples inserted went into a wrong
node. Note that the xc_node_id value is different from the same value of
column 'a' which is the hash distribution column.
I haven't looked at the code to see if this can be fixed easily, but looks
like a bug to me. I am not sure if the node management code is smart enough
to deal with scenarios like these. Or do we expect the DBAs to take care of
such issues i.e. expect them to take extreme care while dealing with node
management ? Can adding appropriate dependencies between objects could
solve this ?
Thanks,
Pavan
--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee
|
|
From: Pavan D. <pav...@gm...> - 2013-07-11 09:44:27
|
Hello All, I noticed that a few newly added functions in tablecmds.c are named in a slightly wrong way: static void AtExecDistributeBy(Relation rel, DistributeBy *options); static void AtExecSubCluster(Relation rel, PGXCSubCluster *options); static void AtExecAddNode(Relation rel, List *options); static void AtExecDeleteNode(Relation rel, List *options); All of these functions should start with AT (instead of At). AT stands for Alter Table. Please see other functions in the same file and how they are named. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee |
|
From: Pavan D. <pav...@gm...> - 2013-07-11 08:48:37
|
Hello All,
The following case shows a bug in the FQS logic. I am using fairly recent
master branch (may be a few days old). So if this has been fixed recently,
please let me know:
test=# CREATE TABLE ltab_repl (a int, b char(10)) DISTRIBUTE BY REPLICATION;
CREATE TABLE
test=# CREATE TABLE rtab (ar int, br char(10));
CREATE TABLE
test=# INSERT INTO ltab_repl SELECT generate_series(1,5), 'foo';
INSERT 0 5
test=# INSERT INTO rtab SELECT generate_series(1,4), 'bar';
INSERT 0 4
test=# SELECT * FROM ltab_repl ;
a | b
---+------------
1 | foo
2 | foo
3 | foo
4 | foo
5 | foo
(5 rows)
test=# SELECT * FROM rtab;
ar | br
----+------------
1 | bar
2 | bar
3 | bar
4 | bar
(4 rows)
test=# set enable_fast_query_shipping TO off;
SET
test=# SELECT * FROM ltab_repl LEFT JOIN rtab ON (a = ar);
a | b | ar | br
---+------------+----+------------
1 | foo | 1 | bar
2 | foo | 2 | bar
3 | foo | 3 | bar
4 | foo | 4 | bar
5 | foo | |
(5 rows)
test=# set enable_fast_query_shipping TO on;
SET
test=# SELECT * FROM ltab_repl LEFT JOIN rtab ON (a = ar);
a | b | ar | br
---+------------+----+------------
1 | foo | 1 | bar
2 | foo | 2 | bar
3 | foo | |
4 | foo | |
5 | foo | |
1 | foo | |
2 | foo | |
3 | foo | 3 | bar
4 | foo | 4 | bar
5 | foo | |
(10 rows)
test=# EXPLAIN VERBOSE SELECT * FROM ltab_repl LEFT JOIN rtab ON (a = ar);
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: ltab_repl.a, ltab_repl.b, rtab.ar, rtab.br
Node/s: d1, d2
Remote query: SELECT ltab_repl.a, ltab_repl.b, rtab.ar, rtab.br FROM
(public.ltab_repl LEFT JOIN public.rtab ON ((ltab_repl.a = rtab.ar)))
(4 rows)
As you would see the query is giving a wrong result when FQS is turned ON.
I don't think its correct to push down the query as it is to the datanodes.
What's happening really is that each datanode is returning 5 rows each
(since ltab_repl is a replicated table, contains 5 rows and is at the left
side of the left join) and they are being appended together.
I think I'd suggested in the past to run regression by turning these
various optimization GUCs on/off and comparing the results. While we might
see some row ordering issues when these GUCs are turned on/off, the final
result should remain the same. Such an exercise will help to uncover such
bugs.
Thanks,
Pavan
--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee
|
|
From: Koichi S. <koi...@gm...> - 2013-07-11 07:16:51
|
Unfortunately, we don't have this at our focus now. This is executor/planner improvement, i think. I hope some of them can be focust in the next development round. Anyway, if the planner cannot identify specific datanodes to go, coordinator has to go to all the datanodes, which will fail if any one of the datanodes is down. Up to now, XC development was focused on the feature where all the nodes are up. XC architecture allows any transactions to be successful as long as the failed node is not involved. I understand that XC behavior has something to improve in the case where some nodes are gone. Your input is very valuable and helpful. We can share this and discuss how to solve them. Regards; ---------- Koichi Suzuki 2013/7/11 Pavan Deolasee <pav...@gm...> > Hello All, > > I wonder how do we currently handle node failures in XC setup. I > understand, we recommend setting up replicated slaves for all the datanodes > and adjust the coordinator node information in case of fail over, say by > using ALTER NODE. What I am more curious is about working at a reduced > availability. Let me give an example: > > Say I have a table distributed by HASH on 4 nodes. If one of those nodes > go down, I would still like to use the table by somehow adjusting the > metadata at the coordinator. The data stored on the failed node will not be > accessible, but I should be able to query the data from the remaining > nodes. So SELECTs should not fail or at the least SELECT with a > qualification which filters out data from the failed node should not fail. > Also, INSERT/UPDATE/DELETE should succeed as long as the target node is up > and running. > > I tried to run a few queries to see if this is possible, but could not get > the desired result. I only had limited success with replicated tables, in a > sense that I could query the table after dropping the failed node at the > coordinator. But even that looks fragile. > > Are there any action items around this area targeted in the immediate > roadmap for XC ? > > Thanks, > Pavan > > > -- > Pavan Deolasee > http://www.linkedin.com/in/pavandeolasee > > > ------------------------------------------------------------------------------ > See everything from the browser to the database with AppDynamics > Get end-to-end visibility with application monitoring from AppDynamics > Isolate bottlenecks and diagnose root cause in seconds. > Start your free trial of AppDynamics Pro today! > http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > |
|
From: Pavan D. <pav...@gm...> - 2013-07-11 07:04:37
|
Hello All, I wonder how do we currently handle node failures in XC setup. I understand, we recommend setting up replicated slaves for all the datanodes and adjust the coordinator node information in case of fail over, say by using ALTER NODE. What I am more curious is about working at a reduced availability. Let me give an example: Say I have a table distributed by HASH on 4 nodes. If one of those nodes go down, I would still like to use the table by somehow adjusting the metadata at the coordinator. The data stored on the failed node will not be accessible, but I should be able to query the data from the remaining nodes. So SELECTs should not fail or at the least SELECT with a qualification which filters out data from the failed node should not fail. Also, INSERT/UPDATE/DELETE should succeed as long as the target node is up and running. I tried to run a few queries to see if this is possible, but could not get the desired result. I only had limited success with replicated tables, in a sense that I could query the table after dropping the failed node at the coordinator. But even that looks fragile. Are there any action items around this area targeted in the immediate roadmap for XC ? Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee |
|
From: Koichi S. <koi...@gm...> - 2013-07-10 06:59:41
|
---------- Koichi Suzuki ---------- Forwarded message ---------- From: Koichi Suzuki <koi...@gm...> Date: 2013/7/5 Subject: Postgres-XC 1.1 beta is out To: pos...@li..., postgres-xc-general < pos...@li...>, pgsql-announce < pgs...@po...> Postgres-XC 1.1 beta is now out. Thank you very much for waiting long. Source tarball is available at https://sourceforge.net/projects/postgres-xc/files/Version_1.1/ Document hage is at http://postgres-xc.sourceforge.net/docs/1_1_beta/ Release note is at http://postgres-xc.sourceforge.net/docs/1_1_beta/release-xc-1-1.html Enjoy. ---------- Koichi Suzuki |
261 messages has been excluded from this view by a project administrator.
