Menu
▾
▴
postgres-xc-developers
|
From: Shankar H. <har...@ya...> - 2012-07-06 04:35:42
|
Hello, Am trying out XC 1.0 in the following configuraiton. Node 1 - Coord1, Datanode1, gtm-proxy1 Node 2- Coord2, Datanode2, gtm-proxy2 Node 3- Datanode3, gtm I setup all nodes but forgot to add Coord1 to Coord2 and vice versa. In addition I missed the pg_hba edit as well. So the first table T1 that I created for distribution from Coord1 was not "visible| from Coord2 but was on all the data nodes. I tried to get Coord2 backinto business in various ways but the first table I created refused to show up on Coord2 : - edit pg_hba and add node on both coord1 and 2. Then run select pgxc_pool_reload(); - restart coord 1 and 2 - drop node c2 from c1 and c1 from c2 and add them back followed by select pgxc_pool_reload(); So I tried to create the same table T1 from Coord2 to observe behavior and it did not like it clearly as all nodes it "wrote" to reported that the table already existed which was good. At this point I could understand that Coord2 and Coord1 are not talking alright so I created a new table from coord1 with replication. This table was visible from both now. Question is should I expect to see the first table, let me call it T1 after a while from Coord2 also? thanks, Shankar |
|
From: Shankar H. <har...@ya...> - 2012-07-06 04:38:25
|
Follow up to earlier email. In the setup described below, can I avoid using a gtm-proxy? That is, can I just simply point coordinators to the one gtm running on node 3 ? My initial plan was to just run the gtm on node 3 then I thought I could try a datanode without a local coordinator which was why I put these two together on node 3. thanks, Shankar ________________________________ From: Shankar Hariharan <har...@ya...> To: "pos...@li..." <pos...@li...> Sent: Thursday, July 5, 2012 11:35 PM Subject: Question on multiple coordinators Hello, Am trying out XC 1.0 in the following configuraiton. Node 1 - Coord1, Datanode1, gtm-proxy1 Node 2- Coord2, Datanode2, gtm-proxy2 Node 3- Datanode3, gtm I setup all nodes but forgot to add Coord1 to Coord2 and vice versa. In addition I missed the pg_hba edit as well. So the first table T1 that I created for distribution from Coord1 was not "visible| from Coord2 but was on all the data nodes. I tried to get Coord2 backinto business in various ways but the first table I created refused to show up on Coord2 : - edit pg_hba and add node on both coord1 and 2. Then run select pgxc_pool_reload(); - restart coord 1 and 2 - drop node c2 from c1 and c1 from c2 and add them back followed by select pgxc_pool_reload(); So I tried to create the same table T1 from Coord2 to observe behavior and it did not like it clearly as all nodes it "wrote" to reported that the table already existed which was good. At this point I could understand that Coord2 and Coord1 are not talking alright so I created a new table from coord1 with replication. This table was visible from both now. Question is should I expect to see the first table, let me call it T1 after a while from Coord2 also? thanks, Shankar |
|
From: Michael P. <mic...@gm...> - 2012-07-06 05:01:18
|
On Fri, Jul 6, 2012 at 1:38 PM, Shankar Hariharan < har...@ya...> wrote: > Follow up to earlier email. In the setup described below, can I avoid > using a gtm-proxy? That is, can I just simply point coordinators to the one > gtm running on node 3 ? > My initial plan was to just run the gtm on node 3 then I thought I could > try a datanode without a local coordinator which was why I put these two > together on node 3. > thanks, > GTM proxy is not a mandatory element in an XC cluster. So yes, you can connect directly a Coordinator or a Datanode to a GTM. -- Michael Paquier http://michael.otacoo.com |
|
From: Shankar H. <har...@ya...> - 2012-07-06 13:24:29
|
Thanks Michael. Based on your experience, is that good behavior to have a GTM shared across multiple coordinators/datanodes? I do plan to run some tests to observe behavior but would like to understand if there are some metrics around this already. thanks, Shankar ________________________________ From: Michael Paquier <mic...@gm...> To: Shankar Hariharan <har...@ya...> Cc: "pos...@li..." <pos...@li...> Sent: Friday, July 6, 2012 12:00 AM Subject: Re: [Postgres-xc-developers] Question on gtm-proxy On Fri, Jul 6, 2012 at 1:38 PM, Shankar Hariharan <har...@ya...> wrote: Follow up to earlier email. In the setup described below, can I avoid using a gtm-proxy? That is, can I just simply point coordinators to the one gtm running on node 3 ? >My initial plan was to just run the gtm on node 3 then I thought I could try a datanode without a local coordinator which was why I put these two together on node 3. >thanks, GTM proxy is not a mandatory element in an XC cluster. So yes, you can connect directly a Coordinator or a Datanode to a GTM. -- Michael Paquier http://michael.otacoo.com |
|
From: Ashutosh B. <ash...@en...> - 2012-07-06 05:25:46
|
Hi Shankar, Running gtm-proxy has shown to improve the performance, because it lessens the load on GTM, by serving requests locally. Why do you want the coordinators to connect directly to the GTM? Are you seeing any performance improvement from doing that? On Fri, Jul 6, 2012 at 10:08 AM, Shankar Hariharan < har...@ya...> wrote: > Follow up to earlier email. In the setup described below, can I avoid > using a gtm-proxy? That is, can I just simply point coordinators to the one > gtm running on node 3 ? > My initial plan was to just run the gtm on node 3 then I thought I could > try a datanode without a local coordinator which was why I put these two > together on node 3. > thanks, > Shankar > > ------------------------------ > *From:* Shankar Hariharan <har...@ya...> > *To:* "pos...@li..." < > pos...@li...> > *Sent:* Thursday, July 5, 2012 11:35 PM > *Subject:* Question on multiple coordinators > > Hello, > > Am trying out XC 1.0 in the following configuraiton. > Node 1 - Coord1, Datanode1, gtm-proxy1 > Node 2- Coord2, Datanode2, gtm-proxy2 > Node 3- Datanode3, gtm > > I setup all nodes but forgot to add Coord1 to Coord2 and vice versa. In > addition I missed the pg_hba edit as well. So the first table T1 that I > created for distribution from Coord1 was not "visible| from Coord2 but > was on all the data nodes. > I tried to get Coord2 backinto business in various ways but the first > table I created refused to show up on Coord2 : > - edit pg_hba and add node on both coord1 and 2. Then run select > pgxc_pool_reload(); > - restart coord 1 and 2 > - drop node c2 from c1 and c1 from c2 and add them back followed by select > pgxc_pool_reload(); > > So I tried to create the same table T1 from Coord2 to observe behavior > and it did not like it clearly as all nodes it "wrote" to reported that the > table already existed which was good. At this point I could understand that > Coord2 and Coord1 are not talking alright so I created a new table from > coord1 with replication. This table was visible from both now. > > Question is should I expect to see the first table, let me call it T1 > after a while from Coord2 also? > > > thanks, > Shankar > > > > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > -- Best Wishes, Ashutosh Bapat EntepriseDB Corporation The Enterprise Postgres Company |
|
From: Shankar H. <har...@ya...> - 2012-07-06 13:22:20
|
Hi Ashutosh, I was trying to size the load on a server and was wondering if a GTM could be shared w/o much performance overhead between a small number of datanodes and coordinators. I will post my findings here. thanks, Shankar ________________________________ From: Ashutosh Bapat <ash...@en...> To: Shankar Hariharan <har...@ya...> Cc: "pos...@li..." <pos...@li...> Sent: Friday, July 6, 2012 12:25 AM Subject: Re: [Postgres-xc-developers] Question on gtm-proxy Hi Shankar, Running gtm-proxy has shown to improve the performance, because it lessens the load on GTM, by serving requests locally. Why do you want the coordinators to connect directly to the GTM? Are you seeing any performance improvement from doing that? On Fri, Jul 6, 2012 at 10:08 AM, Shankar Hariharan <har...@ya...> wrote: Follow up to earlier email. In the setup described below, can I avoid using a gtm-proxy? That is, can I just simply point coordinators to the one gtm running on node 3 ? >My initial plan was to just run the gtm on node 3 then I thought I could try a datanode without a local coordinator which was why I put these two together on node 3. >thanks, >Shankar > > > >________________________________ > From: Shankar Hariharan <har...@ya...> >To: "pos...@li..." <pos...@li...> >Sent: Thursday, July 5, 2012 11:35 PM >Subject: Question on multiple coordinators > > >Hello, > > >Am trying out XC 1.0 in the following configuraiton. >Node 1 - Coord1, Datanode1, gtm-proxy1 >Node 2- Coord2, Datanode2, gtm-proxy2 >Node 3- Datanode3, gtm > > >I setup all nodes but forgot to add Coord1 to Coord2 and vice versa. In addition I missed the pg_hba edit as well. So the first table T1 that I created for distribution from Coord1 was not "visible| from Coord2 but was on all the data nodes. >I tried to get Coord2 backinto business in various ways but the first table I created refused to show up on Coord2 : >- edit pg_hba and add node on both coord1 and 2. Then run select pgxc_pool_reload(); >- restart coord 1 and 2 >- drop node c2 from c1 and c1 from c2 and add them back followed by select pgxc_pool_reload(); > > >So I tried to create the same table T1 from Coord2 to observe behavior and it did not like it clearly as all nodes it "wrote" to reported that the table already existed which was good. At this point I could understand that Coord2 and Coord1 are not talking alright so I created a new table from coord1 with replication. This table was visible from both now. > > >Question is should I expect to see the first table, let me call it T1 after a while from Coord2 also? > > > > >thanks, >Shankar > > >------------------------------------------------------------------------------ >Live Security Virtual Conference >Exclusive live event will cover all the ways today's security and >threat landscape has changed and how IT managers can respond. Discussions >will include endpoint security, mobile security and the latest in malware >threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ >_______________________________________________ >Postgres-xc-developers mailing list >Pos...@li... >https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > -- Best Wishes, Ashutosh Bapat EntepriseDB Corporation The Enterprise Postgres Company |
|
From: Andrei M. <and...@gm...> - 2012-07-06 14:18:11
|
Hi Shankar, You can safely clone Coordinators by plain copying of the data directory and adjusting configuration afterwards. That approach may be used to fix your problem or to add a coordinator to existing cluster. 1. Stop all cluster components. 2. Copy coordinator database to the new location. 3. Start GTM, GTM proxy if appropriate and Coordinator specifying -D <new datadir location>. Make sure you are not running coordinator on the master copy of the data directory, they share the same name yet, GTM would not allow that. 4. Connect psql or other client to the coordinator and create a record in pgxc_node table for future "self" entry using CREATE NODE command. You may need to adjust connection info for current self, which will be pointing to the original coordinator, since the view may be different from the new location, like you may want to replace host='localhost' with host = '<IP adress>' 5. Adjust configuration of the new coordinator, you must change pgxc_node_name so it is unique in the cluster; if the new location is on the same box you may need to change port to listen on for client connections and pooler port. The configuration should match the "self" entry you created on previous step. 6. Restart the new coordinator, start other cluster components. 7. Connect to old coordinators and use CREATE NODE command to make them aware of new coordinator. 8. Enjoy. 2012/7/6 Shankar Hariharan <har...@ya...> > Hello, > > Am trying out XC 1.0 in the following configuraiton. > Node 1 - Coord1, Datanode1, gtm-proxy1 > Node 2- Coord2, Datanode2, gtm-proxy2 > Node 3- Datanode3, gtm > > I setup all nodes but forgot to add Coord1 to Coord2 and vice versa. In > addition I missed the pg_hba edit as well. So the first table T1 that I > created for distribution from Coord1 was not "visible| from Coord2 but > was on all the data nodes. > I tried to get Coord2 backinto business in various ways but the first > table I created refused to show up on Coord2 : > - edit pg_hba and add node on both coord1 and 2. Then run select > pgxc_pool_reload(); > - restart coord 1 and 2 > - drop node c2 from c1 and c1 from c2 and add them back followed by select > pgxc_pool_reload(); > > So I tried to create the same table T1 from Coord2 to observe behavior > and it did not like it clearly as all nodes it "wrote" to reported that the > table already existed which was good. At this point I could understand that > Coord2 and Coord1 are not talking alright so I created a new table from > coord1 with replication. This table was visible from both now. > > Question is should I expect to see the first table, let me call it T1 > after a while from Coord2 also? > > > thanks, > Shankar > > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > -- Andrei Martsinchyk StormDB - http://www.stormdb.com The Database Cloud |
|
From: Shankar H. <har...@ya...> - 2012-07-06 15:04:40
|
Thanks Andrei. I will try this out. Appreciate the help. ________________________________ From: Andrei Martsinchyk <and...@gm...> To: Shankar Hariharan <har...@ya...> Cc: "pos...@li..." <pos...@li...> Sent: Friday, July 6, 2012 9:18 AM Subject: Re: [Postgres-xc-developers] Question on multiple coordinators Hi Shankar, You can safely clone Coordinators by plain copying of the data directory and adjusting configuration afterwards. That approach may be used to fix your problem or to add a coordinator to existing cluster. 1. Stop all cluster components. 2. Copy coordinator database to the new location. 3. Start GTM, GTM proxy if appropriate and Coordinator specifying -D <new datadir location>. Make sure you are not running coordinator on the master copy of the data directory, they share the same name yet, GTM would not allow that. 4. Connect psql or other client to the coordinator and create a record in pgxc_node table for future "self" entry using CREATE NODE command. You may need to adjust connection info for current self, which will be pointing to the original coordinator, since the view may be different from the new location, like you may want to replace host='localhost' with host = '<IP adress>' 5. Adjust configuration of the new coordinator, you must change pgxc_node_name so it is unique in the cluster; if the new location is on the same box you may need to change port to listen on for client connections and pooler port. The configuration should match the "self" entry you created on previous step. 6. Restart the new coordinator, start other cluster components. 7. Connect to old coordinators and use CREATE NODE command to make them aware of new coordinator. 8. Enjoy. 2012/7/6 Shankar Hariharan <har...@ya...> Hello, > > >Am trying out XC 1.0 in the following configuraiton. >Node 1 - Coord1, Datanode1, gtm-proxy1 >Node 2- Coord2, Datanode2, gtm-proxy2 >Node 3- Datanode3, gtm > > >I setup all nodes but forgot to add Coord1 to Coord2 and vice versa. In addition I missed the pg_hba edit as well. So the first table T1 that I created for distribution from Coord1 was not "visible| from Coord2 but was on all the data nodes. >I tried to get Coord2 backinto business in various ways but the first table I created refused to show up on Coord2 : >- edit pg_hba and add node on both coord1 and 2. Then run select pgxc_pool_reload(); >- restart coord 1 and 2 >- drop node c2 from c1 and c1 from c2 and add them back followed by select pgxc_pool_reload(); > > >So I tried to create the same table T1 from Coord2 to observe behavior and it did not like it clearly as all nodes it "wrote" to reported that the table already existed which was good. At this point I could understand that Coord2 and Coord1 are not talking alright so I created a new table from coord1 with replication. This table was visible from both now. > > >Question is should I expect to see the first table, let me call it T1 after a while from Coord2 also? > > > > >thanks, >Shankar >------------------------------------------------------------------------------ >Live Security Virtual Conference >Exclusive live event will cover all the ways today's security and >threat landscape has changed and how IT managers can respond. Discussions >will include endpoint security, mobile security and the latest in malware >threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ >_______________________________________________ >Postgres-xc-developers mailing list >Pos...@li... >https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > -- Andrei Martsinchyk StormDB - http://www.stormdb.com/ The Database Cloud |
|
From: Nikhil S. <ni...@st...> - 2012-07-06 16:34:55
|
Hi Shankar, Yeah, the GTM might be able to scale a bit to some level, but after that having the proxies around on each node makes much more sense. It also helps reduce the direct CPU load on the GTM node. And the proxies shouldn't consume that much CPU by themselves too. Unless you are trying a CPU intensive benchmark, but most benchmarks try to churn up IO.. Regards, Nikhils On Fri, Jul 6, 2012 at 9:22 AM, Shankar Hariharan <har...@ya...> wrote: > Hi Ashutosh, > I was trying to size the load on a server and was wondering if a GTM could > be shared w/o much performance overhead between a small number of datanodes > and coordinators. I will post my findings here. > thanks, > Shankar > > ________________________________ > From: Ashutosh Bapat <ash...@en...> > To: Shankar Hariharan <har...@ya...> > Cc: "pos...@li..." > <pos...@li...> > Sent: Friday, July 6, 2012 12:25 AM > Subject: Re: [Postgres-xc-developers] Question on gtm-proxy > > Hi Shankar, > Running gtm-proxy has shown to improve the performance, because it lessens > the load on GTM, by serving requests locally. Why do you want the > coordinators to connect directly to the GTM? Are you seeing any performance > improvement from doing that? > > On Fri, Jul 6, 2012 at 10:08 AM, Shankar Hariharan > <har...@ya...> wrote: > > Follow up to earlier email. In the setup described below, can I avoid using > a gtm-proxy? That is, can I just simply point coordinators to the one gtm > running on node 3 ? > My initial plan was to just run the gtm on node 3 then I thought I could try > a datanode without a local coordinator which was why I put these two > together on node 3. > thanks, > Shankar > > ________________________________ > From: Shankar Hariharan <har...@ya...> > To: "pos...@li..." > <pos...@li...> > Sent: Thursday, July 5, 2012 11:35 PM > Subject: Question on multiple coordinators > > Hello, > > Am trying out XC 1.0 in the following configuraiton. > Node 1 - Coord1, Datanode1, gtm-proxy1 > Node 2- Coord2, Datanode2, gtm-proxy2 > Node 3- Datanode3, gtm > > I setup all nodes but forgot to add Coord1 to Coord2 and vice versa. In > addition I missed the pg_hba edit as well. So the first table T1 that I > created for distribution from Coord1 was not "visible| from Coord2 but was > on all the data nodes. > I tried to get Coord2 backinto business in various ways but the first table > I created refused to show up on Coord2 : > - edit pg_hba and add node on both coord1 and 2. Then run select > pgxc_pool_reload(); > - restart coord 1 and 2 > - drop node c2 from c1 and c1 from c2 and add them back followed by select > pgxc_pool_reload(); > > So I tried to create the same table T1 from Coord2 to observe behavior and > it did not like it clearly as all nodes it "wrote" to reported that the > table already existed which was good. At this point I could understand that > Coord2 and Coord1 are not talking alright so I created a new table from > coord1 with replication. This table was visible from both now. > > Question is should I expect to see the first table, let me call it T1 after > a while from Coord2 also? > > > thanks, > Shankar > > > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > > > > -- > Best Wishes, > Ashutosh Bapat > EntepriseDB Corporation > The Enterprise Postgres Company > > > > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > -- StormDB - http://www.stormdb.com The Database Cloud |
|
From: Shankar H. <har...@ya...> - 2012-07-06 20:15:03
|
As planned I ran some tests using PGBench on this setup : Node 1 - Coord1, Datanode1, gtm-proxy1 Node 2- Coord2, Datanode2, gtm-proxy2 Node 3- Datanode3, gtm I was connecting via Coord1 for these tests: - scale factor of 30 used - tests run using the following input parameters for pgbench: ClientsThreadsDurationTransactions 111006204 221009960 4410012880 6610016768 8810019758 101010021944 121210020674 The run went well until the 8 clients. I started seeing errors on 10 clients onwards and eventually the 14 client run has been hanging around for over an hour now. The errors I have been seeing on console are the following : pgbench console : Client 8 aborted in state 12: ERROR: GTM error, could not obtain snapshot Client 0 aborted in state 13: ERROR: maximum number of prepared transactions reached Client 7 aborted in state 13: ERROR: maximum number of prepared transactions reached Client 11 aborted in state 13: ERROR: maximum number of prepared transactions reached Client 9 aborted in state 13: ERROR: maximum number of prepared transactions reached node console: ERROR: GTM error, could not obtain snapshot STATEMENT: INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (253, 26, 1888413, -817, CURRENT_TIMESTAMP); ERROR: maximum number of prepared transactions reached HINT: Increase max_prepared_transactions (currently 10). STATEMENT: PREPARE TRANSACTION 'T201428' ERROR: maximum number of prepared transactions reached STATEMENT: END; ERROR: maximum number of prepared transactions reached STATEMENT: END; ERROR: maximum number of prepared transactions reached STATEMENT: END; ERROR: maximum number of prepared transactions reached STATEMENT: END; ERROR: GTM error, could not obtain snapshot STATEMENT: INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (140, 29, 2416403, -4192, CURRENT_TIMESTAMP); I was also watching the processes on each node and see the following for the 14 client run: Node1 : postgres 25571 10511 0 04:41 ? 00:00:02 postgres: postgres postgres ::1(33481) TRUNCATE TABLE waiting postgres 25620 11694 0 04:46 ? 00:00:00 postgres: postgres postgres pgbench-address (50388) TRUNCATE TABLE Node2: postgres 10979 9631 0 Jul05 ? 00:00:42 postgres: postgres postgres coord1-address(57357) idle in transaction Node3: postgres 20264 9911 0 08:35 ? 00:00:05 postgres: postgres postgres coord1-address(51406) TRUNCATE TABLE waiting I was going to restart the processes on all nodes and start over but did not want to lose this data as it could be useful information. Any explanation on the above issue is much appreciated. I will try the next run with a higher value set for max_prepared_transactions. Any recommendations for a good value on this front? thanks, Shankar ________________________________ From: Shankar Hariharan <har...@ya...> To: Ashutosh Bapat <ash...@en...> Cc: "pos...@li..." <pos...@li...> Sent: Friday, July 6, 2012 8:22 AM Subject: Re: [Postgres-xc-developers] Question on gtm-proxy Hi Ashutosh, I was trying to size the load on a server and was wondering if a GTM could be shared w/o much performance overhead between a small number of datanodes and coordinators. I will post my findings here. thanks, Shankar ________________________________ From: Ashutosh Bapat <ash...@en...> To: Shankar Hariharan <har...@ya...> Cc: "pos...@li..." <pos...@li...> Sent: Friday, July 6, 2012 12:25 AM Subject: Re: [Postgres-xc-developers] Question on gtm-proxy Hi Shankar, Running gtm-proxy has shown to improve the performance, because it lessens the load on GTM, by serving requests locally. Why do you want the coordinators to connect directly to the GTM? Are you seeing any performance improvement from doing that? On Fri, Jul 6, 2012 at 10:08 AM, Shankar Hariharan <har...@ya...> wrote: Follow up to earlier email. In the setup described below, can I avoid using a gtm-proxy? That is, can I just simply point coordinators to the one gtm running on node 3 ? >My initial plan was to just run the gtm on node 3 then I thought I could try a datanode without a local coordinator which was why I put these two together on node 3. >thanks, >Shankar > > > >________________________________ > From: Shankar Hariharan <har...@ya...> >To: "pos...@li..." <pos...@li...> >Sent: Thursday, July 5, 2012 11:35 PM >Subject: Question on multiple coordinators > > >Hello, > > >Am trying out XC 1.0 in the following configuraiton. >Node 1 - Coord1, Datanode1, gtm-proxy1 >Node 2- Coord2, Datanode2, gtm-proxy2 >Node 3- Datanode3, gtm > > >I setup all nodes but forgot to add Coord1 to Coord2 and vice versa. In addition I missed the pg_hba edit as well. So the first table T1 that I created for distribution from Coord1 was not "visible| from Coord2 but was on all the data nodes. >I tried to get Coord2 backinto business in various ways but the first table I created refused to show up on Coord2 : >- edit pg_hba and add node on both coord1 and 2. Then run select pgxc_pool_reload(); >- restart coord 1 and 2 >- drop node c2 from c1 and c1 from c2 and add them back followed by select pgxc_pool_reload(); > > >So I tried to create the same table T1 from Coord2 to observe behavior and it did not like it clearly as all nodes it "wrote" to reported that the table already existed which was good. At this point I could understand that Coord2 and Coord1 are not talking alright so I created a new table from coord1 with replication. This table was visible from both now. > > >Question is should I expect to see the first table, let me call it T1 after a while from Coord2 also? > > > > >thanks, >Shankar > > >------------------------------------------------------------------------------ >Live Security Virtual Conference >Exclusive live event will cover all the ways today's security and >threat landscape has changed and how IT managers can respond. Discussions >will include endpoint security, mobile security and the latest in malware >threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ >_______________________________________________ >Postgres-xc-developers mailing list >Pos...@li... >https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > -- Best Wishes, Ashutosh Bapat EntepriseDB Corporation The Enterprise Postgres Company |
|
From: Michael P. <mic...@gm...> - 2012-07-06 23:19:08
|
On 2012/07/06, at 23:18, Andrei Martsinchyk <and...@gm...> wrote: > Hi Shankar, > > You can safely clone Coordinators by plain copying of the data directory and adjusting configuration afterwards. That approach may be used to fix your problem or to add a coordinator to existing cluster. > > 1. Stop all cluster components. > 2. Copy coordinator database to the new location. > 3. Start GTM, GTM proxy if appropriate and Coordinator specifying -D <new datadir location>. Make sure you are not running coordinator on the master copy of the data directory, they share the same name yet, GTM would not allow that. > 4. Connect psql or other client to the coordinator and create a record in pgxc_node table for future "self" entry using CREATE NODE command. You may need to adjust connection info for current self, which will be pointing to the original coordinator, since the view may be different from the new location, like you may want to replace host='localhost' with host = '<IP adress>' > 5. Adjust configuration of the new coordinator, you must change pgxc_node_name so it is unique in the cluster; if the new location is on the same box you may need to change port to listen on for client connections and pooler port. The configuration should match the "self" entry you created on previous step. > 6. Restart the new coordinator, start other cluster components. > 7. Connect to old coordinators and use CREATE NODE command to make them aware of new coordinator. > 8. Enjoy. Pinpoint here is that we shouldn't have to stop cluster for a coordinator addition. You need to protect your cluster from ddl intrusion while copying catalog data to the new coordinator. > > 2012/7/6 Shankar Hariharan <har...@ya...> > Hello, > > Am trying out XC 1.0 in the following configuraiton. > Node 1 - Coord1, Datanode1, gtm-proxy1 > Node 2- Coord2, Datanode2, gtm-proxy2 > Node 3- Datanode3, gtm > > I setup all nodes but forgot to add Coord1 to Coord2 and vice versa. In addition I missed the pg_hba edit as well. So the first table T1 that I created for distribution from Coord1 was not "visible| from Coord2 but was on all the data nodes. > I tried to get Coord2 backinto business in various ways but the first table I created refused to show up on Coord2 : > - edit pg_hba and add node on both coord1 and 2. Then run select pgxc_pool_reload(); > - restart coord 1 and 2 > - drop node c2 from c1 and c1 from c2 and add them back followed by select pgxc_pool_reload(); > > So I tried to create the same table T1 from Coord2 to observe behavior and it did not like it clearly as all nodes it "wrote" to reported that the table already existed which was good. At this point I could understand that Coord2 and Coord1 are not talking alright so I created a new table from coord1 with replication. This table was visible from both now. > > Question is should I expect to see the first table, let me call it T1 after a while from Coord2 also? > > > thanks, > Shankar > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > > > > -- > Andrei Martsinchyk > > StormDB - http://www.stormdb.com > The Database Cloud > > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers |
|
From: Andrei M. <and...@gm...> - 2012-07-06 23:34:14
|
2012/7/6 Michael Paquier <mic...@gm...> > > On 2012/07/06, at 23:18, Andrei Martsinchyk <and...@gm...> > wrote: > > Hi Shankar, > > You can safely clone Coordinators by plain copying of the data directory > and adjusting configuration afterwards. That approach may be used to fix > your problem or to add a coordinator to existing cluster. > > 1. Stop all cluster components. > 2. Copy coordinator database to the new location. > 3. Start GTM, GTM proxy if appropriate and Coordinator specifying -D <new > datadir location>. Make sure you are not running coordinator on the master > copy of the data directory, they share the same name yet, GTM would not > allow that. > 4. Connect psql or other client to the coordinator and create a record in > pgxc_node table for future "self" entry using CREATE NODE command. You may > need to adjust connection info for current self, which will be pointing to > the original coordinator, since the view may be different from the new > location, like you may want to replace host='localhost' with host = '<IP > adress>' > 5. Adjust configuration of the new coordinator, you must change > pgxc_node_name so it is unique in the cluster; if the new location is on > the same box you may need to change port to listen on for client > connections and pooler port. The configuration should match the "self" > entry you created on previous step. > 6. Restart the new coordinator, start other cluster components. > 7. Connect to old coordinators and use CREATE NODE command to make them > aware of new coordinator. > 8. Enjoy. > > Pinpoint here is that we shouldn't have to stop cluster for a coordinator > addition. You need to protect your cluster from ddl intrusion while copying > catalog data to the new coordinator. > > Agree, but master coordinator has to be stopped when copy is started for the first time, otherwise GTM would not allow it to connect. Though there is a workaround with another temporary GTM for the copy, if it is not a production cluster it is simpler just to stop everything. > > 2012/7/6 Shankar Hariharan <har...@ya...> > >> Hello, >> >> Am trying out XC 1.0 in the following configuraiton. >> Node 1 - Coord1, Datanode1, gtm-proxy1 >> Node 2- Coord2, Datanode2, gtm-proxy2 >> Node 3- Datanode3, gtm >> >> I setup all nodes but forgot to add Coord1 to Coord2 and vice versa. In >> addition I missed the pg_hba edit as well. So the first table T1 that I >> created for distribution from Coord1 was not "visible| from Coord2 but >> was on all the data nodes. >> I tried to get Coord2 backinto business in various ways but the first >> table I created refused to show up on Coord2 : >> - edit pg_hba and add node on both coord1 and 2. Then run select >> pgxc_pool_reload(); >> - restart coord 1 and 2 >> - drop node c2 from c1 and c1 from c2 and add them back followed by select >> pgxc_pool_reload(); >> >> So I tried to create the same table T1 from Coord2 to observe behavior >> and it did not like it clearly as all nodes it "wrote" to reported that the >> table already existed which was good. At this point I could understand that >> Coord2 and Coord1 are not talking alright so I created a new table from >> coord1 with replication. This table was visible from both now. >> >> Question is should I expect to see the first table, let me call it T1 >> after a while from Coord2 also? >> >> >> thanks, >> Shankar >> >> >> ------------------------------------------------------------------------------ >> Live Security Virtual Conference >> Exclusive live event will cover all the ways today's security and >> threat landscape has changed and how IT managers can respond. Discussions >> will include endpoint security, mobile security and the latest in malware >> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ >> _______________________________________________ >> Postgres-xc-developers mailing list >> Pos...@li... >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >> >> > > > -- > Andrei Martsinchyk > > StormDB - http://www.stormdb.com > The Database Cloud > > > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > -- Andrei Martsinchyk StormDB - http://www.stormdb.com The Database Cloud |
|
From: Nikhil S. <ni...@st...> - 2012-07-07 05:27:48
|
> Any explanation on the above issue is much appreciated. I will try the next > run with a higher value set for max_prepared_transactions. Any > recommendations for a good value on this front? > How many clients you want to run with this eventually? That will determine a decent value for max_prepared_transactions. Note that max_prepared_transactions takes a wee bit more of shared memory per prepared transaction. But it's ok to set it high proportionate to the max_connections value. Regards, Nikhils > thanks, > Shankar > > > ________________________________ > From: Shankar Hariharan <har...@ya...> > To: Ashutosh Bapat <ash...@en...> > Cc: "pos...@li..." > <pos...@li...> > Sent: Friday, July 6, 2012 8:22 AM > > Subject: Re: [Postgres-xc-developers] Question on gtm-proxy > > Hi Ashutosh, > I was trying to size the load on a server and was wondering if a GTM could > be shared w/o much performance overhead between a small number of datanodes > and coordinators. I will post my findings here. > thanks, > Shankar > > ________________________________ > From: Ashutosh Bapat <ash...@en...> > To: Shankar Hariharan <har...@ya...> > Cc: "pos...@li..." > <pos...@li...> > Sent: Friday, July 6, 2012 12:25 AM > Subject: Re: [Postgres-xc-developers] Question on gtm-proxy > > Hi Shankar, > Running gtm-proxy has shown to improve the performance, because it lessens > the load on GTM, by serving requests locally. Why do you want the > coordinators to connect directly to the GTM? Are you seeing any performance > improvement from doing that? > > On Fri, Jul 6, 2012 at 10:08 AM, Shankar Hariharan > <har...@ya...> wrote: > > Follow up to earlier email. In the setup described below, can I avoid using > a gtm-proxy? That is, can I just simply point coordinators to the one gtm > running on node 3 ? > My initial plan was to just run the gtm on node 3 then I thought I could try > a datanode without a local coordinator which was why I put these two > together on node 3. > thanks, > Shankar > > ________________________________ > From: Shankar Hariharan <har...@ya...> > To: "pos...@li..." > <pos...@li...> > Sent: Thursday, July 5, 2012 11:35 PM > Subject: Question on multiple coordinators > > Hello, > > Am trying out XC 1.0 in the following configuraiton. > Node 1 - Coord1, Datanode1, gtm-proxy1 > Node 2- Coord2, Datanode2, gtm-proxy2 > Node 3- Datanode3, gtm > > I setup all nodes but forgot to add Coord1 to Coord2 and vice versa. In > addition I missed the pg_hba edit as well. So the first table T1 that I > created for distribution from Coord1 was not "visible| from Coord2 but was > on all the data nodes. > I tried to get Coord2 backinto business in various ways but the first table > I created refused to show up on Coord2 : > - edit pg_hba and add node on both coord1 and 2. Then run select > pgxc_pool_reload(); > - restart coord 1 and 2 > - drop node c2 from c1 and c1 from c2 and add them back followed by select > pgxc_pool_reload(); > > So I tried to create the same table T1 from Coord2 to observe behavior and > it did not like it clearly as all nodes it "wrote" to reported that the > table already existed which was good. At this point I could understand that > Coord2 and Coord1 are not talking alright so I created a new table from > coord1 with replication. This table was visible from both now. > > Question is should I expect to see the first table, let me call it T1 after > a while from Coord2 also? > > > thanks, > Shankar > > > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > > > > -- > Best Wishes, > Ashutosh Bapat > EntepriseDB Corporation > The Enterprise Postgres Company > > > > > > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > -- StormDB - http://www.stormdb.com The Database Cloud |
|
From: Shankar H. <har...@ya...> - 2012-07-07 23:53:06
|
Thanks Nikhil. I have set both to 100 for my next run. I have another question, if I create a table w/o specifying the distribution strategy i still see that the data is distributed across the nodes. What is the default distribution strategy?
I did run some tests across 3 nodes and noticed that the data is not distributed equally all times. For instance, when I first inserted 10 records (all integer values) i noticed that data node 1 just got one record into node 1 while the other two nodes were almost equal. However after 2 more inserts of 10 records each all 3 nodes were almost at the same load leve (w.r.t. number of records).
Table used for test:
Column | Type | Modifiers
--------+---------+-------------------------------------------------------
id | integer | not null default nextval('test_id_seq'::regclass)
num | integer |
thanks,
Shankar
________________________________
From: Nikhil Sontakke <ni...@st...>
To: Shankar Hariharan <har...@ya...>
Cc: Ashutosh Bapat <ash...@en...>; "pos...@li..." <pos...@li...>
Sent: Saturday, July 7, 2012 12:27 AM
Subject: Re: [Postgres-xc-developers] Question on gtm-proxy
> Any explanation on the above issue is much appreciated. I will try the next
> run with a higher value set for max_prepared_transactions. Any
> recommendations for a good value on this front?
>
How many clients you want to run with this eventually? That will
determine a decent value for max_prepared_transactions. Note that
max_prepared_transactions takes a wee bit more of shared memory per
prepared transaction. But it's ok to set it high proportionate to the
max_connections value.
Regards,
Nikhils
> thanks,
> Shankar
>
>
> ________________________________
> From: Shankar Hariharan <har...@ya...>
> To: Ashutosh Bapat <ash...@en...>
> Cc: "pos...@li..."
> <pos...@li...>
> Sent: Friday, July 6, 2012 8:22 AM
>
> Subject: Re: [Postgres-xc-developers] Question on gtm-proxy
>
> Hi Ashutosh,
> I was trying to size the load on a server and was wondering if a GTM could
> be shared w/o much performance overhead between a small number of datanodes
> and coordinators. I will post my findings here.
> thanks,
> Shankar
>
> ________________________________
> From: Ashutosh Bapat <ash...@en...>
> To: Shankar Hariharan <har...@ya...>
> Cc: "pos...@li..."
> <pos...@li...>
> Sent: Friday, July 6, 2012 12:25 AM
> Subject: Re: [Postgres-xc-developers] Question on gtm-proxy
>
> Hi Shankar,
> Running gtm-proxy has shown to improve the performance, because it lessens
> the load on GTM, by serving requests locally. Why do you want the
> coordinators to connect directly to the GTM? Are you seeing any performance
> improvement from doing that?
>
> On Fri, Jul 6, 2012 at 10:08 AM, Shankar Hariharan
> <har...@ya...> wrote:
>
> Follow up to earlier email. In the setup described below, can I avoid using
> a gtm-proxy? That is, can I just simply point coordinators to the one gtm
> running on node 3 ?
> My initial plan was to just run the gtm on node 3 then I thought I could try
> a datanode without a local coordinator which was why I put these two
> together on node 3.
> thanks,
> Shankar
>
> ________________________________
> From: Shankar Hariharan <har...@ya...>
> To: "pos...@li..."
> <pos...@li...>
> Sent: Thursday, July 5, 2012 11:35 PM
> Subject: Question on multiple coordinators
>
> Hello,
>
> Am trying out XC 1.0 in the following configuraiton.
> Node 1 - Coord1, Datanode1, gtm-proxy1
> Node 2- Coord2, Datanode2, gtm-proxy2
> Node 3- Datanode3, gtm
>
> I setup all nodes but forgot to add Coord1 to Coord2 and vice versa. In
> addition I missed the pg_hba edit as well. So the first table T1 that I
> created for distribution from Coord1 was not "visible| from Coord2 but was
> on all the data nodes.
> I tried to get Coord2 backinto business in various ways but the first table
> I created refused to show up on Coord2 :
> - edit pg_hba and add node on both coord1 and 2. Then run select
> pgxc_pool_reload();
> - restart coord 1 and 2
> - drop node c2 from c1 and c1 from c2 and add them back followed by select
> pgxc_pool_reload();
>
> So I tried to create the same table T1 from Coord2 to observe behavior and
> it did not like it clearly as all nodes it "wrote" to reported that the
> table already existed which was good. At this point I could understand that
> Coord2 and Coord1 are not talking alright so I created a new table from
> coord1 with replication. This table was visible from both now.
>
> Question is should I expect to see the first table, let me call it T1 after
> a while from Coord2 also?
>
>
> thanks,
> Shankar
>
>
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EntepriseDB Corporation
> The Enterprise Postgres Company
>
>
>
>
>
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>
--
StormDB - http://www.stormdb.com
The Database Cloud |
|
From: Mason S. <ma...@st...> - 2012-07-08 16:09:33
|
On Sat, Jul 7, 2012 at 7:52 PM, Shankar Hariharan <har...@ya...> wrote: > Thanks Nikhil. I have set both to 100 for my next run. I have another > question, if I create a table w/o specifying the distribution strategy i > still see that the data is distributed across the nodes. What is the default > distribution strategy? It tries to use the first column of a primary key or unique index, if specified in the CREATE TABLE statement, or the first column of a foreign key. If not available, it uses the first column with a reasonable data type (ie, not BYTEA, not BOOLEAN). > I did run some tests across 3 nodes and noticed that the data is not > distributed equally all times. For instance, when I first inserted 10 > records (all integer values) i noticed that data node 1 just got one record > into node 1 while the other two nodes were almost equal. However after 2 > more inserts of 10 records each all 3 nodes were almost at the same load > leve (w.r.t. number of records). Yes, I think there were just too few rows in your sample data set. As it gets big, it will even out. -- Mason Sharp StormDB - http://www.stormdb.com The Database Cloud |
|
From: Michael P. <mic...@gm...> - 2012-07-08 23:36:40
|
Just giving some precision here. In XC we use an internal 2PC process at transaction commit if this transaction involves more than 2 nodes in a write operation (DML or DDL). So for each connection of your application at a Coordinator, you might finish with a 2PC transaction being run. Hence, the maximum value of max_prepared_transactions with which you will be sure that this error will not come out is the sum of the max_connections of all the Coordinators of your cluster. However you are more or less sure that you won't have a 2PC occurring on a Datanode at the same time for all the backends of Coordinators, so usually max_prepared_transactions could be set sefely at 30%~40% of the sum of Coordinators' max_connections. Check with your application. On Sat, Jul 7, 2012 at 2:27 PM, Nikhil Sontakke <ni...@st...> wrote: > > Any explanation on the above issue is much appreciated. I will try the > next > > run with a higher value set for max_prepared_transactions. Any > > recommendations for a good value on this front? > > > > How many clients you want to run with this eventually? That will > determine a decent value for max_prepared_transactions. Note that > max_prepared_transactions takes a wee bit more of shared memory per > prepared transaction. But it's ok to set it high proportionate to the > max_connections value. > > Regards, > Nikhils > > > thanks, > > Shankar > > > > > > ________________________________ > > From: Shankar Hariharan <har...@ya...> > > To: Ashutosh Bapat <ash...@en...> > > Cc: "pos...@li..." > > <pos...@li...> > > Sent: Friday, July 6, 2012 8:22 AM > > > > Subject: Re: [Postgres-xc-developers] Question on gtm-proxy > > > > Hi Ashutosh, > > I was trying to size the load on a server and was wondering if a GTM > could > > be shared w/o much performance overhead between a small number of > datanodes > > and coordinators. I will post my findings here. > > thanks, > > Shankar > > > > ________________________________ > > From: Ashutosh Bapat <ash...@en...> > > To: Shankar Hariharan <har...@ya...> > > Cc: "pos...@li..." > > <pos...@li...> > > Sent: Friday, July 6, 2012 12:25 AM > > Subject: Re: [Postgres-xc-developers] Question on gtm-proxy > > > > Hi Shankar, > > Running gtm-proxy has shown to improve the performance, because it > lessens > > the load on GTM, by serving requests locally. Why do you want the > > coordinators to connect directly to the GTM? Are you seeing any > performance > > improvement from doing that? > > > > On Fri, Jul 6, 2012 at 10:08 AM, Shankar Hariharan > > <har...@ya...> wrote: > > > > Follow up to earlier email. In the setup described below, can I avoid > using > > a gtm-proxy? That is, can I just simply point coordinators to the one gtm > > running on node 3 ? > > My initial plan was to just run the gtm on node 3 then I thought I could > try > > a datanode without a local coordinator which was why I put these two > > together on node 3. > > thanks, > > Shankar > > > > ________________________________ > > From: Shankar Hariharan <har...@ya...> > > To: "pos...@li..." > > <pos...@li...> > > Sent: Thursday, July 5, 2012 11:35 PM > > Subject: Question on multiple coordinators > > > > Hello, > > > > Am trying out XC 1.0 in the following configuraiton. > > Node 1 - Coord1, Datanode1, gtm-proxy1 > > Node 2- Coord2, Datanode2, gtm-proxy2 > > Node 3- Datanode3, gtm > > > > I setup all nodes but forgot to add Coord1 to Coord2 and vice versa. In > > addition I missed the pg_hba edit as well. So the first table T1 that I > > created for distribution from Coord1 was not "visible| from Coord2 but > was > > on all the data nodes. > > I tried to get Coord2 backinto business in various ways but the first > table > > I created refused to show up on Coord2 : > > - edit pg_hba and add node on both coord1 and 2. Then run select > > pgxc_pool_reload(); > > - restart coord 1 and 2 > > - drop node c2 from c1 and c1 from c2 and add them back followed by > select > > pgxc_pool_reload(); > > > > So I tried to create the same table T1 from Coord2 to observe behavior > and > > it did not like it clearly as all nodes it "wrote" to reported that the > > table already existed which was good. At this point I could understand > that > > Coord2 and Coord1 are not talking alright so I created a new table from > > coord1 with replication. This table was visible from both now. > > > > Question is should I expect to see the first table, let me call it T1 > after > > a while from Coord2 also? > > > > > > thanks, > > Shankar > > > > > > > > > ------------------------------------------------------------------------------ > > Live Security Virtual Conference > > Exclusive live event will cover all the ways today's security and > > threat landscape has changed and how IT managers can respond. Discussions > > will include endpoint security, mobile security and the latest in malware > > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > > _______________________________________________ > > Postgres-xc-developers mailing list > > Pos...@li... > > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > > > > > > > > > -- > > Best Wishes, > > Ashutosh Bapat > > EntepriseDB Corporation > > The Enterprise Postgres Company > > > > > > > > > > > > > > > ------------------------------------------------------------------------------ > > Live Security Virtual Conference > > Exclusive live event will cover all the ways today's security and > > threat landscape has changed and how IT managers can respond. Discussions > > will include endpoint security, mobile security and the latest in malware > > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > > _______________________________________________ > > Postgres-xc-developers mailing list > > Pos...@li... > > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > > > > > -- > StormDB - http://www.stormdb.com > The Database Cloud > > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > -- Michael Paquier http://michael.otacoo.com |
|
From: Nikhil S. <ni...@st...> - 2012-07-09 03:46:49
|
> Hence, the maximum value of max_prepared_transactions with which you will be > sure that this error will not come out is the sum of the max_connections of > all the Coordinators of your cluster. However you are more or less sure that > you won't have a 2PC occurring on a Datanode at the same time for all the > backends of Coordinators, so usually max_prepared_transactions could be set > sefely at 30%~40% of the sum of Coordinators' max_connections. Check with > your application. > Why is that 30-40% value safe? Why are we sure that we wont have a 2PC occurring on a datanode at the same time for the coordinator backends? He is using PGBENCH which is tpcB and it's 100% updates. Now we don't know how his tables are laid out, but I am guessing that it will surely cause lots of 2PCs. So you are right, the value of max_prepared_transactions on datanodes will have to consider typical concurrent connections that might come from all the coordinator nodes. Regards, Nikhils > > On Sat, Jul 7, 2012 at 2:27 PM, Nikhil Sontakke <ni...@st...> wrote: >> >> > Any explanation on the above issue is much appreciated. I will try the >> > next >> > run with a higher value set for max_prepared_transactions. Any >> > recommendations for a good value on this front? >> > >> >> How many clients you want to run with this eventually? That will >> determine a decent value for max_prepared_transactions. Note that >> max_prepared_transactions takes a wee bit more of shared memory per >> prepared transaction. But it's ok to set it high proportionate to the >> max_connections value. >> >> Regards, >> Nikhils >> >> > thanks, >> > Shankar >> > >> > >> > ________________________________ >> > From: Shankar Hariharan <har...@ya...> >> > To: Ashutosh Bapat <ash...@en...> >> > Cc: "pos...@li..." >> > <pos...@li...> >> > Sent: Friday, July 6, 2012 8:22 AM >> > >> > Subject: Re: [Postgres-xc-developers] Question on gtm-proxy >> > >> > Hi Ashutosh, >> > I was trying to size the load on a server and was wondering if a GTM >> > could >> > be shared w/o much performance overhead between a small number of >> > datanodes >> > and coordinators. I will post my findings here. >> > thanks, >> > Shankar >> > >> > ________________________________ >> > From: Ashutosh Bapat <ash...@en...> >> > To: Shankar Hariharan <har...@ya...> >> > Cc: "pos...@li..." >> > <pos...@li...> >> > Sent: Friday, July 6, 2012 12:25 AM >> > Subject: Re: [Postgres-xc-developers] Question on gtm-proxy >> > >> > Hi Shankar, >> > Running gtm-proxy has shown to improve the performance, because it >> > lessens >> > the load on GTM, by serving requests locally. Why do you want the >> > coordinators to connect directly to the GTM? Are you seeing any >> > performance >> > improvement from doing that? >> > >> > On Fri, Jul 6, 2012 at 10:08 AM, Shankar Hariharan >> > <har...@ya...> wrote: >> > >> > Follow up to earlier email. In the setup described below, can I avoid >> > using >> > a gtm-proxy? That is, can I just simply point coordinators to the one >> > gtm >> > running on node 3 ? >> > My initial plan was to just run the gtm on node 3 then I thought I could >> > try >> > a datanode without a local coordinator which was why I put these two >> > together on node 3. >> > thanks, >> > Shankar >> > >> > ________________________________ >> > From: Shankar Hariharan <har...@ya...> >> > To: "pos...@li..." >> > <pos...@li...> >> > Sent: Thursday, July 5, 2012 11:35 PM >> > Subject: Question on multiple coordinators >> > >> > Hello, >> > >> > Am trying out XC 1.0 in the following configuraiton. >> > Node 1 - Coord1, Datanode1, gtm-proxy1 >> > Node 2- Coord2, Datanode2, gtm-proxy2 >> > Node 3- Datanode3, gtm >> > >> > I setup all nodes but forgot to add Coord1 to Coord2 and vice versa. In >> > addition I missed the pg_hba edit as well. So the first table T1 that I >> > created for distribution from Coord1 was not "visible| from Coord2 but >> > was >> > on all the data nodes. >> > I tried to get Coord2 backinto business in various ways but the first >> > table >> > I created refused to show up on Coord2 : >> > - edit pg_hba and add node on both coord1 and 2. Then run select >> > pgxc_pool_reload(); >> > - restart coord 1 and 2 >> > - drop node c2 from c1 and c1 from c2 and add them back followed by >> > select >> > pgxc_pool_reload(); >> > >> > So I tried to create the same table T1 from Coord2 to observe behavior >> > and >> > it did not like it clearly as all nodes it "wrote" to reported that the >> > table already existed which was good. At this point I could understand >> > that >> > Coord2 and Coord1 are not talking alright so I created a new table from >> > coord1 with replication. This table was visible from both now. >> > >> > Question is should I expect to see the first table, let me call it T1 >> > after >> > a while from Coord2 also? >> > >> > >> > thanks, >> > Shankar >> > >> > >> > >> > >> > ------------------------------------------------------------------------------ >> > Live Security Virtual Conference >> > Exclusive live event will cover all the ways today's security and >> > threat landscape has changed and how IT managers can respond. >> > Discussions >> > will include endpoint security, mobile security and the latest in >> > malware >> > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ >> > _______________________________________________ >> > Postgres-xc-developers mailing list >> > Pos...@li... >> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >> > >> > >> > >> > >> > -- >> > Best Wishes, >> > Ashutosh Bapat >> > EntepriseDB Corporation >> > The Enterprise Postgres Company >> > >> > >> > >> > >> > >> > >> > >> > ------------------------------------------------------------------------------ >> > Live Security Virtual Conference >> > Exclusive live event will cover all the ways today's security and >> > threat landscape has changed and how IT managers can respond. >> > Discussions >> > will include endpoint security, mobile security and the latest in >> > malware >> > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ >> > _______________________________________________ >> > Postgres-xc-developers mailing list >> > Pos...@li... >> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >> > >> >> >> >> -- >> StormDB - http://www.stormdb.com >> The Database Cloud >> >> >> ------------------------------------------------------------------------------ >> Live Security Virtual Conference >> Exclusive live event will cover all the ways today's security and >> threat landscape has changed and how IT managers can respond. Discussions >> will include endpoint security, mobile security and the latest in malware >> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ >> _______________________________________________ >> Postgres-xc-developers mailing list >> Pos...@li... >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > > > > -- > Michael Paquier > http://michael.otacoo.com -- StormDB - http://www.stormdb.com The Database Cloud |
|
From: Michael P. <mic...@gm...> - 2012-07-09 04:02:42
|
On Mon, Jul 9, 2012 at 12:46 PM, Nikhil Sontakke <ni...@st...>wrote: > > Hence, the maximum value of max_prepared_transactions with which you > will be > > sure that this error will not come out is the sum of the max_connections > of > > all the Coordinators of your cluster. However you are more or less sure > that > > you won't have a 2PC occurring on a Datanode at the same time for all the > > backends of Coordinators, so usually max_prepared_transactions could be > set > > sefely at 30%~40% of the sum of Coordinators' max_connections. Check with > > your application. > > > > Why is that 30-40% value safe? > This estimation is based on my long experience testing XC with several benchmarks. > Why are we sure that we wont have a 2PC occurring on a datanode at the > same time for the coordinator backends? > Possible, it depends on application. > > He is using PGBENCH which is tpcB and it's 100% updates. Now we don't > know how his tables are laid out, but I am guessing that it will > surely cause lots of 2PCs. So you are right, the value of > max_prepared_transactions on datanodes will have to consider typical > concurrent connections that might come from all the coordinator nodes. > As I would imagine that this pgbench is going to be slow on XC as there will be a lot of concurrent updates. So yeah, here you need perhaps a higher value, so I just mean that this setting depends on network, machines, cluster configuration (Co/Dn together/separated) and application used. -- Michael Paquier http://michael.otacoo.com |
|
From: Koichi S. <koi...@gm...> - 2012-07-09 00:28:49
|
The most overhead associated with GTM is the network communication. As you know, every transaction needs at least a GXID and a snapshot from GTM, which leads to enormous amount of network interactions, not necessary the size of data. If a transaction isolation mode is read committed, each statement needs separate snapshot, which adds much more network workload to GTM. GTM proxy improves this. GTM Proxy groups multiple requirement from backends to single message, reducing the number of interactions. Moreover, multiple snapshot requirement is reduced into single snapshot requirement. One snapshot from GTM is copied to multiple backends. This reduces size of the message. Of course, GTM proxy reduces the number of GTM threads, which reduces the chance of lock conflicts but I've not evaluated this yet. Also, I've not evaluated how much GTM cpu is saved by GTM proxy yet. Regards; ---------- Koichi Suzuki 2012/7/7 Nikhil Sontakke <ni...@st...>: > Hi Shankar, > > Yeah, the GTM might be able to scale a bit to some level, but after > that having the proxies around on each node makes much more sense. It > also helps reduce the direct CPU load on the GTM node. And the proxies > shouldn't consume that much CPU by themselves too. Unless you are > trying a CPU intensive benchmark, but most benchmarks try to churn up > IO.. > > Regards, > Nikhils > > On Fri, Jul 6, 2012 at 9:22 AM, Shankar Hariharan > <har...@ya...> wrote: >> Hi Ashutosh, >> I was trying to size the load on a server and was wondering if a GTM could >> be shared w/o much performance overhead between a small number of datanodes >> and coordinators. I will post my findings here. >> thanks, >> Shankar >> >> ________________________________ >> From: Ashutosh Bapat <ash...@en...> >> To: Shankar Hariharan <har...@ya...> >> Cc: "pos...@li..." >> <pos...@li...> >> Sent: Friday, July 6, 2012 12:25 AM >> Subject: Re: [Postgres-xc-developers] Question on gtm-proxy >> >> Hi Shankar, >> Running gtm-proxy has shown to improve the performance, because it lessens >> the load on GTM, by serving requests locally. Why do you want the >> coordinators to connect directly to the GTM? Are you seeing any performance >> improvement from doing that? >> >> On Fri, Jul 6, 2012 at 10:08 AM, Shankar Hariharan >> <har...@ya...> wrote: >> >> Follow up to earlier email. In the setup described below, can I avoid using >> a gtm-proxy? That is, can I just simply point coordinators to the one gtm >> running on node 3 ? >> My initial plan was to just run the gtm on node 3 then I thought I could try >> a datanode without a local coordinator which was why I put these two >> together on node 3. >> thanks, >> Shankar >> >> ________________________________ >> From: Shankar Hariharan <har...@ya...> >> To: "pos...@li..." >> <pos...@li...> >> Sent: Thursday, July 5, 2012 11:35 PM >> Subject: Question on multiple coordinators >> >> Hello, >> >> Am trying out XC 1.0 in the following configuraiton. >> Node 1 - Coord1, Datanode1, gtm-proxy1 >> Node 2- Coord2, Datanode2, gtm-proxy2 >> Node 3- Datanode3, gtm >> >> I setup all nodes but forgot to add Coord1 to Coord2 and vice versa. In >> addition I missed the pg_hba edit as well. So the first table T1 that I >> created for distribution from Coord1 was not "visible| from Coord2 but was >> on all the data nodes. >> I tried to get Coord2 backinto business in various ways but the first table >> I created refused to show up on Coord2 : >> - edit pg_hba and add node on both coord1 and 2. Then run select >> pgxc_pool_reload(); >> - restart coord 1 and 2 >> - drop node c2 from c1 and c1 from c2 and add them back followed by select >> pgxc_pool_reload(); >> >> So I tried to create the same table T1 from Coord2 to observe behavior and >> it did not like it clearly as all nodes it "wrote" to reported that the >> table already existed which was good. At this point I could understand that >> Coord2 and Coord1 are not talking alright so I created a new table from >> coord1 with replication. This table was visible from both now. >> >> Question is should I expect to see the first table, let me call it T1 after >> a while from Coord2 also? >> >> >> thanks, >> Shankar >> >> >> >> ------------------------------------------------------------------------------ >> Live Security Virtual Conference >> Exclusive live event will cover all the ways today's security and >> threat landscape has changed and how IT managers can respond. Discussions >> will include endpoint security, mobile security and the latest in malware >> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ >> _______________________________________________ >> Postgres-xc-developers mailing list >> Pos...@li... >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >> >> >> >> >> -- >> Best Wishes, >> Ashutosh Bapat >> EntepriseDB Corporation >> The Enterprise Postgres Company >> >> >> >> >> ------------------------------------------------------------------------------ >> Live Security Virtual Conference >> Exclusive live event will cover all the ways today's security and >> threat landscape has changed and how IT managers can respond. Discussions >> will include endpoint security, mobile security and the latest in malware >> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ >> _______________________________________________ >> Postgres-xc-developers mailing list >> Pos...@li... >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >> > > > > -- > StormDB - http://www.stormdb.com > The Database Cloud > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers |
|
From: Ashutosh B. <ash...@en...> - 2012-07-09 04:30:46
|
Hi Shankar, You have got answers to the prepared transaction problem, I guess. I have something else below. On Sat, Jul 7, 2012 at 1:44 AM, Shankar Hariharan < har...@ya...> wrote: > As planned I ran some tests using PGBench on this setup : > > Node 1 - Coord1, Datanode1, gtm-proxy1 > Node 2- Coord2, Datanode2, gtm-proxy2 > Node 3- Datanode3, gtm > > I was connecting via Coord1 for these tests: > - scale factor of 30 used > - tests run using the following input parameters for pgbench: > Try connecting to both the coordinators, it should give you better performance, esp, when you are using distributed tables. With distributed tables, coordinator gets involved in query execution more than that in the case of replicated tables. So, balancing load across two coordinators would help. > > Clients Threads Duration Transactions > 1 1 100 6204 > 2 2 100 9960 > 4 4 100 12880 > 6 6 100 1676 > > 8 > 8 8 100 19758 > 10 10 100 21944 > 12 12 100 20674 > > The run went well until the 8 clients. I started seeing errors on 10 > clients onwards and eventually the 14 client run has been hanging around > for over an hour now. The errors I have been seeing on console are the > following : > > pgbench console : > Client 8 aborted in state 12: ERROR: GTM error, could not obtain snapshot > Client 0 aborted in state 13: ERROR: maximum number of prepared > transactions reached > Client 7 aborted in state 13: ERROR: maximum number of prepared > transactions reached > Client 11 aborted in state 13: ERROR: maximum number of prepared > transactions reached > Client 9 aborted in state 13: ERROR: maximum number of prepared > transactions reached > > node console: > ERROR: GTM error, could not obtain snapshot > STATEMENT: INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) > VALUES (253, 26, 1888413, -817, CURRENT_TIMESTAMP); > ERROR: maximum number of prepared transactions reached > HINT: Increase max_prepared_transactions (currently 10). > STATEMENT: PREPARE TRANSACTION 'T201428' > ERROR: maximum number of prepared transactions reached > STATEMENT: END; > ERROR: maximum number of prepared transactions reached > STATEMENT: END; > ERROR: maximum number of prepared transactions reached > STATEMENT: END; > ERROR: maximum number of prepared transactions reached > STATEMENT: END; > ERROR: GTM error, could not obtain snapshot > STATEMENT: INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) > VALUES (140, 29, 2416403, -4192, CURRENT_TIMESTAMP); > > I was also watching the processes on each node and see the following for > the 14 client run: > > > Node1 : > postgres 25571 10511 0 04:41 ? 00:00:02 postgres: postgres > postgres ::1(33481) TRUNCATE TABLE waiting > postgres 25620 11694 0 04:46 ? 00:00:00 postgres: postgres > postgres pgbench-address (50388) TRUNCATE TABLE > > Node2: > postgres 10979 9631 0 Jul05 ? 00:00:42 postgres: postgres > postgres coord1-address(57357) idle in transaction > > Node3: > postgres 20264 9911 0 08:35 ? 00:00:05 postgres: postgres > postgres coord1-address(51406) TRUNCATE TABLE waiting > > > I was going to restart the processes on all nodes and start over but did > not want to lose this data as it could be useful information. > > Any explanation on the above issue is much appreciated. I will try the > next run with a higher value set for max_prepared_transactions. Any > recommendations for a good value on this front? > > thanks, > Shankar > > > ------------------------------ > *From:* Shankar Hariharan <har...@ya...> > *To:* Ashutosh Bapat <ash...@en...> > *Cc:* "pos...@li..." < > pos...@li...> > *Sent:* Friday, July 6, 2012 8:22 AM > > *Subject:* Re: [Postgres-xc-developers] Question on gtm-proxy > > Hi Ashutosh, > I was trying to size the load on a server and was wondering if a GTM > could be shared w/o much performance overhead between a small number of > datanodes and coordinators. I will post my findings here. > thanks, > Shankar > > ------------------------------ > *From:* Ashutosh Bapat <ash...@en...> > *To:* Shankar Hariharan <har...@ya...> > *Cc:* "pos...@li..." < > pos...@li...> > *Sent:* Friday, July 6, 2012 12:25 AM > *Subject:* Re: [Postgres-xc-developers] Question on gtm-proxy > > Hi Shankar, > Running gtm-proxy has shown to improve the performance, because it lessens > the load on GTM, by serving requests locally. Why do you want the > coordinators to connect directly to the GTM? Are you seeing any performance > improvement from doing that? > > On Fri, Jul 6, 2012 at 10:08 AM, Shankar Hariharan < > har...@ya...> wrote: > > Follow up to earlier email. In the setup described below, can I avoid > using a gtm-proxy? That is, can I just simply point coordinators to the one > gtm running on node 3 ? > My initial plan was to just run the gtm on node 3 then I thought I could > try a datanode without a local coordinator which was why I put these two > together on node 3. > thanks, > Shankar > > ------------------------------ > *From:* Shankar Hariharan <har...@ya...> > *To:* "pos...@li..." < > pos...@li...> > *Sent:* Thursday, July 5, 2012 11:35 PM > *Subject:* Question on multiple coordinators > > Hello, > > Am trying out XC 1.0 in the following configuraiton. > Node 1 - Coord1, Datanode1, gtm-proxy1 > Node 2- Coord2, Datanode2, gtm-proxy2 > Node 3- Datanode3, gtm > > I setup all nodes but forgot to add Coord1 to Coord2 and vice versa. In > addition I missed the pg_hba edit as well. So the first table T1 that I > created for distribution from Coord1 was not "visible| from Coord2 but > was on all the data nodes. > I tried to get Coord2 backinto business in various ways but the first > table I created refused to show up on Coord2 : > - edit pg_hba and add node on both coord1 and 2. Then run select > pgxc_pool_reload(); > - restart coord 1 and 2 > - drop node c2 from c1 and c1 from c2 and add them back followed by select > pgxc_pool_reload(); > > So I tried to create the same table T1 from Coord2 to observe behavior > and it did not like it clearly as all nodes it "wrote" to reported that the > table already existed which was good. At this point I could understand that > Coord2 and Coord1 are not talking alright so I created a new table from > coord1 with replication. This table was visible from both now. > > Question is should I expect to see the first table, let me call it T1 > after a while from Coord2 also? > > > thanks, > Shankar > > > > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > > > > -- > Best Wishes, > Ashutosh Bapat > EntepriseDB Corporation > The Enterprise Postgres Company > > > > > > -- Best Wishes, Ashutosh Bapat EntepriseDB Corporation The Enterprise Postgres Company |
|
From: Nikhil S. <ni...@st...> - 2012-07-09 03:58:11
|
Yeah, thanks for bringing out the networking (more important) aspect here. So it might help to have the GTM traffic happening on a different private network (separate from the client traffic) for performance. > Of course, GTM proxy reduces the number of GTM threads, which reduces > the chance of lock conflicts but I've not evaluated this yet. Also, > I've not evaluated how much GTM cpu is saved by GTM proxy yet. > I did some runs on a 4 node setup of mine and having proxies does seem to help in reducing the CPU overhead on the GTM node. I had not maxed out the GTM or anything though, but having proxies around seemed to help. Regards, Nikhils > Regards; > ---------- > Koichi Suzuki > > > 2012/7/7 Nikhil Sontakke <ni...@st...>: >> Hi Shankar, >> >> Yeah, the GTM might be able to scale a bit to some level, but after >> that having the proxies around on each node makes much more sense. It >> also helps reduce the direct CPU load on the GTM node. And the proxies >> shouldn't consume that much CPU by themselves too. Unless you are >> trying a CPU intensive benchmark, but most benchmarks try to churn up >> IO.. >> >> Regards, >> Nikhils >> >> On Fri, Jul 6, 2012 at 9:22 AM, Shankar Hariharan >> <har...@ya...> wrote: >>> Hi Ashutosh, >>> I was trying to size the load on a server and was wondering if a GTM could >>> be shared w/o much performance overhead between a small number of datanodes >>> and coordinators. I will post my findings here. >>> thanks, >>> Shankar >>> >>> ________________________________ >>> From: Ashutosh Bapat <ash...@en...> >>> To: Shankar Hariharan <har...@ya...> >>> Cc: "pos...@li..." >>> <pos...@li...> >>> Sent: Friday, July 6, 2012 12:25 AM >>> Subject: Re: [Postgres-xc-developers] Question on gtm-proxy >>> >>> Hi Shankar, >>> Running gtm-proxy has shown to improve the performance, because it lessens >>> the load on GTM, by serving requests locally. Why do you want the >>> coordinators to connect directly to the GTM? Are you seeing any performance >>> improvement from doing that? >>> >>> On Fri, Jul 6, 2012 at 10:08 AM, Shankar Hariharan >>> <har...@ya...> wrote: >>> >>> Follow up to earlier email. In the setup described below, can I avoid using >>> a gtm-proxy? That is, can I just simply point coordinators to the one gtm >>> running on node 3 ? >>> My initial plan was to just run the gtm on node 3 then I thought I could try >>> a datanode without a local coordinator which was why I put these two >>> together on node 3. >>> thanks, >>> Shankar >>> >>> ________________________________ >>> From: Shankar Hariharan <har...@ya...> >>> To: "pos...@li..." >>> <pos...@li...> >>> Sent: Thursday, July 5, 2012 11:35 PM >>> Subject: Question on multiple coordinators >>> >>> Hello, >>> >>> Am trying out XC 1.0 in the following configuraiton. >>> Node 1 - Coord1, Datanode1, gtm-proxy1 >>> Node 2- Coord2, Datanode2, gtm-proxy2 >>> Node 3- Datanode3, gtm >>> >>> I setup all nodes but forgot to add Coord1 to Coord2 and vice versa. In >>> addition I missed the pg_hba edit as well. So the first table T1 that I >>> created for distribution from Coord1 was not "visible| from Coord2 but was >>> on all the data nodes. >>> I tried to get Coord2 backinto business in various ways but the first table >>> I created refused to show up on Coord2 : >>> - edit pg_hba and add node on both coord1 and 2. Then run select >>> pgxc_pool_reload(); >>> - restart coord 1 and 2 >>> - drop node c2 from c1 and c1 from c2 and add them back followed by select >>> pgxc_pool_reload(); >>> >>> So I tried to create the same table T1 from Coord2 to observe behavior and >>> it did not like it clearly as all nodes it "wrote" to reported that the >>> table already existed which was good. At this point I could understand that >>> Coord2 and Coord1 are not talking alright so I created a new table from >>> coord1 with replication. This table was visible from both now. >>> >>> Question is should I expect to see the first table, let me call it T1 after >>> a while from Coord2 also? >>> >>> >>> thanks, >>> Shankar >>> >>> >>> >>> ------------------------------------------------------------------------------ >>> Live Security Virtual Conference >>> Exclusive live event will cover all the ways today's security and >>> threat landscape has changed and how IT managers can respond. Discussions >>> will include endpoint security, mobile security and the latest in malware >>> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ >>> _______________________________________________ >>> Postgres-xc-developers mailing list >>> Pos...@li... >>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >>> >>> >>> >>> >>> -- >>> Best Wishes, >>> Ashutosh Bapat >>> EntepriseDB Corporation >>> The Enterprise Postgres Company >>> >>> >>> >>> >>> ------------------------------------------------------------------------------ >>> Live Security Virtual Conference >>> Exclusive live event will cover all the ways today's security and >>> threat landscape has changed and how IT managers can respond. Discussions >>> will include endpoint security, mobile security and the latest in malware >>> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ >>> _______________________________________________ >>> Postgres-xc-developers mailing list >>> Pos...@li... >>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >>> >> >> >> >> -- >> StormDB - http://www.stormdb.com >> The Database Cloud >> >> ------------------------------------------------------------------------------ >> Live Security Virtual Conference >> Exclusive live event will cover all the ways today's security and >> threat landscape has changed and how IT managers can respond. Discussions >> will include endpoint security, mobile security and the latest in malware >> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ >> _______________________________________________ >> Postgres-xc-developers mailing list >> Pos...@li... >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers -- StormDB - http://www.stormdb.com The Database Cloud |
|
From: Koichi S. <koi...@gm...> - 2012-07-09 04:58:00
|
In the case of DBT-1 benchmark, we had up to 12,000 TPS, with ten coordinators and ten datanodes. In this situation, GTM read/wrote about 60MB/sec, which is about 50% of the network max capacity. (sorry again, I'm not quire sure about the exact figure now, but this is very good estimation). Proxies reduced this amount to about a half. As you suggested, yes, there could be a chance to extend GTM scalability a bit more if GTM is equipped with more than one NIC and each NIC is connected to different servers via L2 switch. This will reduce the communication overhead for each communication channel. I've not tried this yet but this sounds interesting to me. Regards; ---------- Koichi Suzuki 2012/7/9 Nikhil Sontakke <ni...@st...>: > Yeah, thanks for bringing out the networking (more important) aspect > here. So it might help to have the GTM traffic happening on a > different private network (separate from the client traffic) for > performance. > >> Of course, GTM proxy reduces the number of GTM threads, which reduces >> the chance of lock conflicts but I've not evaluated this yet. Also, >> I've not evaluated how much GTM cpu is saved by GTM proxy yet. >> > > I did some runs on a 4 node setup of mine and having proxies does seem > to help in reducing the CPU overhead on the GTM node. I had not maxed > out the GTM or anything though, but having proxies around seemed to > help. > > Regards, > Nikhils > >> Regards; >> ---------- >> Koichi Suzuki >> >> >> 2012/7/7 Nikhil Sontakke <ni...@st...>: >>> Hi Shankar, >>> >>> Yeah, the GTM might be able to scale a bit to some level, but after >>> that having the proxies around on each node makes much more sense. It >>> also helps reduce the direct CPU load on the GTM node. And the proxies >>> shouldn't consume that much CPU by themselves too. Unless you are >>> trying a CPU intensive benchmark, but most benchmarks try to churn up >>> IO.. >>> >>> Regards, >>> Nikhils >>> >>> On Fri, Jul 6, 2012 at 9:22 AM, Shankar Hariharan >>> <har...@ya...> wrote: >>>> Hi Ashutosh, >>>> I was trying to size the load on a server and was wondering if a GTM could >>>> be shared w/o much performance overhead between a small number of datanodes >>>> and coordinators. I will post my findings here. >>>> thanks, >>>> Shankar >>>> >>>> ________________________________ >>>> From: Ashutosh Bapat <ash...@en...> >>>> To: Shankar Hariharan <har...@ya...> >>>> Cc: "pos...@li..." >>>> <pos...@li...> >>>> Sent: Friday, July 6, 2012 12:25 AM >>>> Subject: Re: [Postgres-xc-developers] Question on gtm-proxy >>>> >>>> Hi Shankar, >>>> Running gtm-proxy has shown to improve the performance, because it lessens >>>> the load on GTM, by serving requests locally. Why do you want the >>>> coordinators to connect directly to the GTM? Are you seeing any performance >>>> improvement from doing that? >>>> >>>> On Fri, Jul 6, 2012 at 10:08 AM, Shankar Hariharan >>>> <har...@ya...> wrote: >>>> >>>> Follow up to earlier email. In the setup described below, can I avoid using >>>> a gtm-proxy? That is, can I just simply point coordinators to the one gtm >>>> running on node 3 ? >>>> My initial plan was to just run the gtm on node 3 then I thought I could try >>>> a datanode without a local coordinator which was why I put these two >>>> together on node 3. >>>> thanks, >>>> Shankar >>>> >>>> ________________________________ >>>> From: Shankar Hariharan <har...@ya...> >>>> To: "pos...@li..." >>>> <pos...@li...> >>>> Sent: Thursday, July 5, 2012 11:35 PM >>>> Subject: Question on multiple coordinators >>>> >>>> Hello, >>>> >>>> Am trying out XC 1.0 in the following configuraiton. >>>> Node 1 - Coord1, Datanode1, gtm-proxy1 >>>> Node 2- Coord2, Datanode2, gtm-proxy2 >>>> Node 3- Datanode3, gtm >>>> >>>> I setup all nodes but forgot to add Coord1 to Coord2 and vice versa. In >>>> addition I missed the pg_hba edit as well. So the first table T1 that I >>>> created for distribution from Coord1 was not "visible| from Coord2 but was >>>> on all the data nodes. >>>> I tried to get Coord2 backinto business in various ways but the first table >>>> I created refused to show up on Coord2 : >>>> - edit pg_hba and add node on both coord1 and 2. Then run select >>>> pgxc_pool_reload(); >>>> - restart coord 1 and 2 >>>> - drop node c2 from c1 and c1 from c2 and add them back followed by select >>>> pgxc_pool_reload(); >>>> >>>> So I tried to create the same table T1 from Coord2 to observe behavior and >>>> it did not like it clearly as all nodes it "wrote" to reported that the >>>> table already existed which was good. At this point I could understand that >>>> Coord2 and Coord1 are not talking alright so I created a new table from >>>> coord1 with replication. This table was visible from both now. >>>> >>>> Question is should I expect to see the first table, let me call it T1 after >>>> a while from Coord2 also? >>>> >>>> >>>> thanks, >>>> Shankar >>>> >>>> >>>> >>>> ------------------------------------------------------------------------------ >>>> Live Security Virtual Conference >>>> Exclusive live event will cover all the ways today's security and >>>> threat landscape has changed and how IT managers can respond. Discussions >>>> will include endpoint security, mobile security and the latest in malware >>>> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ >>>> _______________________________________________ >>>> Postgres-xc-developers mailing list >>>> Pos...@li... >>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >>>> >>>> >>>> >>>> >>>> -- >>>> Best Wishes, >>>> Ashutosh Bapat >>>> EntepriseDB Corporation >>>> The Enterprise Postgres Company >>>> >>>> >>>> >>>> >>>> ------------------------------------------------------------------------------ >>>> Live Security Virtual Conference >>>> Exclusive live event will cover all the ways today's security and >>>> threat landscape has changed and how IT managers can respond. Discussions >>>> will include endpoint security, mobile security and the latest in malware >>>> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ >>>> _______________________________________________ >>>> Postgres-xc-developers mailing list >>>> Pos...@li... >>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >>>> >>> >>> >>> >>> -- >>> StormDB - http://www.stormdb.com >>> The Database Cloud >>> >>> ------------------------------------------------------------------------------ >>> Live Security Virtual Conference >>> Exclusive live event will cover all the ways today's security and >>> threat landscape has changed and how IT managers can respond. Discussions >>> will include endpoint security, mobile security and the latest in malware >>> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ >>> _______________________________________________ >>> Postgres-xc-developers mailing list >>> Pos...@li... >>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > > > -- > StormDB - http://www.stormdb.com > The Database Cloud |
|
From: Shankar H. <har...@ya...> - 2012-07-09 16:12:04
|
Thanks Ashutosh. You are right, while running this test i just had pgbench running against one coordinator. Looks like pgbench by itself may not be an apt tool for this kind of testing, I will instead run pgbench's underlying sql script from cmdline against either coordinators. Thanks for that tip. I got a lot of input on my problem from a lot of folks on the list, the feedback is much appreciated. Thanks everybody! On max_prepared_transactions, I will factor in the number of coordinators and the max_connections on each coordinator while arriving at a figure. Will also try out Koichi Suzuki's suggestion to have multiple NICs on the GTM. I will post my findings here for the same cluster configuration as before. thanks, Shankar ________________________________ From: Ashutosh Bapat <ash...@en...> To: Shankar Hariharan <har...@ya...> Cc: "pos...@li..." <pos...@li...> Sent: Sunday, July 8, 2012 11:02 PM Subject: Re: [Postgres-xc-developers] Question on gtm-proxy Hi Shankar, You have got answers to the prepared transaction problem, I guess. I have something else below. On Sat, Jul 7, 2012 at 1:44 AM, Shankar Hariharan <har...@ya...> wrote: As planned I ran some tests using PGBench on this setup : > > >Node 1 - Coord1, Datanode1, gtm-proxy1 >Node 2- Coord2, Datanode2, gtm-proxy2 >Node 3- Datanode3, gtm > >I was connecting via Coord1 for these tests: >- scale factor of 30 used >- tests run using the following input parameters for pgbench: Try connecting to both the coordinators, it should give you better performance, esp, when you are using distributed tables. With distributed tables, coordinator gets involved in query execution more than that in the case of replicated tables. So, balancing load across two coordinators would help. > >ClientsThreadsDurationTransactions >111006204 >221009960 >4410012880 >661001676 8 >8810019758 >101010021944 >121210020674 > > >The run went well until the 8 clients. I started seeing errors on 10 clients onwards and eventually the 14 client run has been hanging around for over an hour now. The errors I have been seeing on console are the following : > > >pgbench console : >Client 8 aborted in state 12: ERROR: GTM error, could not obtain snapshot > >Client 0 aborted in state 13: ERROR: maximum number of prepared transactions reached >Client 7 aborted in state 13: ERROR: maximum number of prepared transactions reached >Client 11 aborted in state 13: ERROR: maximum number of prepared transactions reached >Client 9 aborted in state 13: ERROR: maximum number of prepared transactions reached > > >node console: >ERROR: GTM error, could not obtain snapshot >STATEMENT: INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (253, 26, 1888413, -817, CURRENT_TIMESTAMP); >ERROR: maximum number of prepared transactions reached >HINT: Increase max_prepared_transactions (currently 10). >STATEMENT: PREPARE TRANSACTION 'T201428' >ERROR: maximum number of prepared transactions reached >STATEMENT: END; >ERROR: maximum number of prepared transactions reached >STATEMENT: END; >ERROR: maximum number of prepared transactions reached >STATEMENT: END; >ERROR: maximum number of prepared transactions reached >STATEMENT: END; >ERROR: GTM error, could not obtain snapshot >STATEMENT: INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (140, 29, 2416403, -4192, CURRENT_TIMESTAMP); > > >I was also watching the processes on each node and see the following for the 14 client run: > > > > >Node1 : >postgres 25571 10511 0 04:41 ? 00:00:02 postgres: postgres postgres ::1(33481) TRUNCATE TABLE waiting >postgres 25620 11694 0 04:46 ? 00:00:00 postgres: postgres postgres pgbench-address (50388) TRUNCATE TABLE > > >Node2: >postgres 10979 9631 0 Jul05 ? 00:00:42 postgres: postgres postgres coord1-address(57357) idle in transaction > > > >Node3: > >postgres 20264 9911 0 08:35 ? 00:00:05 postgres: postgres postgres coord1-address(51406) TRUNCATE TABLE waiting > > > > > >I was going to restart the processes on all nodes and start over but did not want to lose this data as it could be useful information. > > >Any explanation on the above issue is much appreciated. I will try the next run with a higher value set for max_prepared_transactions. Any recommendations for a good value on this front? > > > >thanks, >Shankar > > > > > >________________________________ > From: Shankar Hariharan <har...@ya...> >To: Ashutosh Bapat <ash...@en...> >Cc: "pos...@li..." <pos...@li...> >Sent: Friday, July 6, 2012 8:22 AM > >Subject: Re: [Postgres-xc-developers] Question on gtm-proxy > > > >Hi Ashutosh, >I was trying to size the load on a server and was wondering if a GTM could be shared w/o much performance overhead between a small number of datanodes and coordinators. I will post my findings here. >thanks, >Shankar > > > >________________________________ > From: Ashutosh Bapat <ash...@en...> >To: Shankar Hariharan <har...@ya...> >Cc: "pos...@li..." <pos...@li...> >Sent: Friday, July 6, 2012 12:25 AM >Subject: Re: [Postgres-xc-developers] Question on gtm-proxy > > >Hi Shankar, >Running gtm-proxy has shown to improve the performance, because it lessens the load on GTM, by serving requests locally. Why do you want the coordinators to connect directly to the GTM? Are you seeing any performance improvement from doing that? > > >On Fri, Jul 6, 2012 at 10:08 AM, Shankar Hariharan <har...@ya...> wrote: > >Follow up to earlier email. In the setup described below, can I avoid using a gtm-proxy? That is, can I just simply point coordinators to the one gtm running on node 3 ? >>My initial plan was to just run the gtm on node 3 then I thought I could try a datanode without a local coordinator which was why I put these two together on node 3. >>thanks, >>Shankar >> >> >> >>________________________________ >> From: Shankar Hariharan <har...@ya...> >>To: "pos...@li..." <pos...@li...> >>Sent: Thursday, July 5, 2012 11:35 PM >>Subject: Question on multiple coordinators >> >> >>Hello, >> >> >>Am trying out XC 1.0 in the following configuraiton. >>Node 1 - Coord1, Datanode1, gtm-proxy1 >>Node 2- Coord2, Datanode2, gtm-proxy2 >>Node 3- Datanode3, gtm >> >> >>I setup all nodes but forgot to add Coord1 to Coord2 and vice versa. In addition I missed the pg_hba edit as well. So the first table T1 that I created for distribution from Coord1 was not "visible| from Coord2 but was on all the data nodes. >>I tried to get Coord2 backinto business in various ways but the first table I created refused to show up on Coord2 : >>- edit pg_hba and add node on both coord1 and 2. Then run select pgxc_pool_reload(); >>- restart coord 1 and 2 >>- drop node c2 from c1 and c1 from c2 and add them back followed by select pgxc_pool_reload(); >> >> >>So I tried to create the same table T1 from Coord2 to observe behavior and it did not like it clearly as all nodes it "wrote" to reported that the table already existed which was good. At this point I could understand that Coord2 and Coord1 are not talking alright so I created a new table from coord1 with replication. This table was visible from both now. >> >> >>Question is should I expect to see the first table, let me call it T1 after a while from Coord2 also? >> >> >> >> >>thanks, >>Shankar >> >> >>------------------------------------------------------------------------------ >>Live Security Virtual Conference >>Exclusive live event will cover all the ways today's security and >>threat landscape has changed and how IT managers can respond. Discussions >>will include endpoint security, mobile security and the latest in malware >>threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ >>_______________________________________________ >>Postgres-xc-developers mailing list >>Pos...@li... >>https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >> >> > > >-- >Best Wishes, >Ashutosh Bapat >EntepriseDB Corporation >The Enterprise Postgres Company > > > > > > -- Best Wishes, Ashutosh Bapat EntepriseDB Corporation The Enterprise Postgres Company |
|
From: Ashutosh B. <ash...@en...> - 2012-07-10 04:25:26
|
Hi Shankar, Will it be possible for you to change the pgbench code to dynamically fire on all available coordinators? Since we use modified DBT-1 for our benchmarking, we haven't got to the point where we can modify pg_bench to suite XC. But that's something, we will welcome if anybody is interested. On Mon, Jul 9, 2012 at 9:41 PM, Shankar Hariharan < har...@ya...> wrote: > Thanks Ashutosh. You are right, while running this test i just had pgbench > running against one coordinator. Looks like pgbench by itself may not be an > apt tool for this kind of testing, I will instead run pgbench's underlying > sql script from cmdline against either coordinators. Thanks for that tip. > > I got a lot of input on my problem from a lot of folks on the list, the > feedback is much appreciated. Thanks everybody! > > On max_prepared_transactions, I will factor in the number of coordinators > and the max_connections on each coordinator while arriving at a figure. > Will also try out Koichi Suzuki's suggestion to have multiple NICs on the > GTM. I will post my findings here for the same cluster configuration as > before. > > thanks, > Shankar > > ------------------------------ > *From:* Ashutosh Bapat <ash...@en...> > *To:* Shankar Hariharan <har...@ya...> > *Cc:* "pos...@li..." < > pos...@li...> > *Sent:* Sunday, July 8, 2012 11:02 PM > > *Subject:* Re: [Postgres-xc-developers] Question on gtm-proxy > > Hi Shankar, > You have got answers to the prepared transaction problem, I guess. I have > something else below. > > On Sat, Jul 7, 2012 at 1:44 AM, Shankar Hariharan < > har...@ya...> wrote: > > As planned I ran some tests using PGBench on this setup : > > Node 1 - Coord1, Datanode1, gtm-proxy1 > Node 2- Coord2, Datanode2, gtm-proxy2 > Node 3- Datanode3, gtm > > I was connecting via Coord1 for these tests: > - scale factor of 30 used > - tests run using the following input parameters for pgbench: > > > Try connecting to both the coordinators, it should give you better > performance, esp, when you are using distributed tables. With distributed > tables, coordinator gets involved in query execution more than that in the > case of replicated tables. So, balancing load across two coordinators would > help. > > > > Clients Threads Duration Transactions > 1 1 100 6204 > 2 2 100 9960 > 4 4 100 12880 > 6 6 100 1676 > > > > 8 > 8 8 100 19758 > 10 10 100 21944 > 12 12 100 20674 > > The run went well until the 8 clients. I started seeing errors on 10 > clients onwards and eventually the 14 client run has been hanging around > for over an hour now. The errors I have been seeing on console are the > following : > > pgbench console : > Client 8 aborted in state 12: ERROR: GTM error, could not obtain snapshot > Client 0 aborted in state 13: ERROR: maximum number of prepared > transactions reached > Client 7 aborted in state 13: ERROR: maximum number of prepared > transactions reached > Client 11 aborted in state 13: ERROR: maximum number of prepared > transactions reached > Client 9 aborted in state 13: ERROR: maximum number of prepared > transactions reached > > node console: > ERROR: GTM error, could not obtain snapshot > STATEMENT: INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) > VALUES (253, 26, 1888413, -817, CURRENT_TIMESTAMP); > ERROR: maximum number of prepared transactions reached > HINT: Increase max_prepared_transactions (currently 10). > STATEMENT: PREPARE TRANSACTION 'T201428' > ERROR: maximum number of prepared transactions reached > STATEMENT: END; > ERROR: maximum number of prepared transactions reached > STATEMENT: END; > ERROR: maximum number of prepared transactions reached > STATEMENT: END; > ERROR: maximum number of prepared transactions reached > STATEMENT: END; > ERROR: GTM error, could not obtain snapshot > STATEMENT: INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) > VALUES (140, 29, 2416403, -4192, CURRENT_TIMESTAMP); > > I was also watching the processes on each node and see the following for > the 14 client run: > > > Node1 : > postgres 25571 10511 0 04:41 ? 00:00:02 postgres: postgres > postgres ::1(33481) TRUNCATE TABLE waiting > postgres 25620 11694 0 04:46 ? 00:00:00 postgres: postgres > postgres pgbench-address (50388) TRUNCATE TABLE > > Node2: > postgres 10979 9631 0 Jul05 ? 00:00:42 postgres: postgres > postgres coord1-address(57357) idle in transaction > > Node3: > postgres 20264 9911 0 08:35 ? 00:00:05 postgres: postgres > postgres coord1-address(51406) TRUNCATE TABLE waiting > > > I was going to restart the processes on all nodes and start over but did > not want to lose this data as it could be useful information. > > Any explanation on the above issue is much appreciated. I will try the > next run with a higher value set for max_prepared_transactions. Any > recommendations for a good value on this front? > > thanks, > Shankar > > > ------------------------------ > *From:* Shankar Hariharan <har...@ya...> > *To:* Ashutosh Bapat <ash...@en...> > *Cc:* "pos...@li..." < > pos...@li...> > *Sent:* Friday, July 6, 2012 8:22 AM > > *Subject:* Re: [Postgres-xc-developers] Question on gtm-proxy > > Hi Ashutosh, > I was trying to size the load on a server and was wondering if a GTM > could be shared w/o much performance overhead between a small number of > datanodes and coordinators. I will post my findings here. > thanks, > Shankar > > ------------------------------ > *From:* Ashutosh Bapat <ash...@en...> > *To:* Shankar Hariharan <har...@ya...> > *Cc:* "pos...@li..." < > pos...@li...> > *Sent:* Friday, July 6, 2012 12:25 AM > *Subject:* Re: [Postgres-xc-developers] Question on gtm-proxy > > Hi Shankar, > Running gtm-proxy has shown to improve the performance, because it lessens > the load on GTM, by serving requests locally. Why do you want the > coordinators to connect directly to the GTM? Are you seeing any performance > improvement from doing that? > > On Fri, Jul 6, 2012 at 10:08 AM, Shankar Hariharan < > har...@ya...> wrote: > > Follow up to earlier email. In the setup described below, can I avoid > using a gtm-proxy? That is, can I just simply point coordinators to the one > gtm running on node 3 ? > My initial plan was to just run the gtm on node 3 then I thought I could > try a datanode without a local coordinator which was why I put these two > together on node 3. > thanks, > Shankar > > ------------------------------ > *From:* Shankar Hariharan <har...@ya...> > *To:* "pos...@li..." < > pos...@li...> > *Sent:* Thursday, July 5, 2012 11:35 PM > *Subject:* Question on multiple coordinators > > Hello, > > Am trying out XC 1.0 in the following configuraiton. > Node 1 - Coord1, Datanode1, gtm-proxy1 > Node 2- Coord2, Datanode2, gtm-proxy2 > Node 3- Datanode3, gtm > > I setup all nodes but forgot to add Coord1 to Coord2 and vice versa. In > addition I missed the pg_hba edit as well. So the first table T1 that I > created for distribution from Coord1 was not "visible| from Coord2 but > was on all the data nodes. > I tried to get Coord2 backinto business in various ways but the first > table I created refused to show up on Coord2 : > - edit pg_hba and add node on both coord1 and 2. Then run select > pgxc_pool_reload(); > - restart coord 1 and 2 > - drop node c2 from c1 and c1 from c2 and add them back followed by select > pgxc_pool_reload(); > > So I tried to create the same table T1 from Coord2 to observe behavior > and it did not like it clearly as all nodes it "wrote" to reported that the > table already existed which was good. At this point I could understand that > Coord2 and Coord1 are not talking alright so I created a new table from > coord1 with replication. This table was visible from both now. > > Question is should I expect to see the first table, let me call it T1 > after a while from Coord2 also? > > > thanks, > Shankar > > > > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > > > > -- > Best Wishes, > Ashutosh Bapat > EntepriseDB Corporation > The Enterprise Postgres Company > > > > > > > > > -- > Best Wishes, > Ashutosh Bapat > EntepriseDB Corporation > The Enterprise Postgres Company > > > > -- Best Wishes, Ashutosh Bapat EntepriseDB Corporation The Enterprise Postgres Company |
|
From: Koichi S. <koi...@gm...> - 2012-07-10 04:51:43
|
Yes. Although we don't have to care application partitioning based upon the distribution key, it's a good idea to make all the coordinator workload as even as possible. In the case of DBT-1, we ran several DBT-1 process, each produces random transaction but goes to specific coordinator. I think pgbench can do the similar. Regards; ---------- Koichi Suzuki 2012/7/10 Ashutosh Bapat <ash...@en...>: > Hi Shankar, > Will it be possible for you to change the pgbench code to dynamically fire > on all available coordinators? > > Since we use modified DBT-1 for our benchmarking, we haven't got to the > point where we can modify pg_bench to suite XC. But that's something, we > will welcome if anybody is interested. > > > On Mon, Jul 9, 2012 at 9:41 PM, Shankar Hariharan > <har...@ya...> wrote: >> >> Thanks Ashutosh. You are right, while running this test i just had pgbench >> running against one coordinator. Looks like pgbench by itself may not be an >> apt tool for this kind of testing, I will instead run pgbench's underlying >> sql script from cmdline against either coordinators. Thanks for that tip. >> >> I got a lot of input on my problem from a lot of folks on the list, the >> feedback is much appreciated. Thanks everybody! >> >> On max_prepared_transactions, I will factor in the number of coordinators >> and the max_connections on each coordinator while arriving at a figure. >> Will also try out Koichi Suzuki's suggestion to have multiple NICs on the >> GTM. I will post my findings here for the same cluster configuration as >> before. >> >> thanks, >> Shankar >> >> ________________________________ >> From: Ashutosh Bapat <ash...@en...> >> To: Shankar Hariharan <har...@ya...> >> Cc: "pos...@li..." >> <pos...@li...> >> Sent: Sunday, July 8, 2012 11:02 PM >> >> Subject: Re: [Postgres-xc-developers] Question on gtm-proxy >> >> Hi Shankar, >> You have got answers to the prepared transaction problem, I guess. I have >> something else below. >> >> On Sat, Jul 7, 2012 at 1:44 AM, Shankar Hariharan >> <har...@ya...> wrote: >> >> As planned I ran some tests using PGBench on this setup : >> >> Node 1 - Coord1, Datanode1, gtm-proxy1 >> Node 2- Coord2, Datanode2, gtm-proxy2 >> Node 3- Datanode3, gtm >> >> I was connecting via Coord1 for these tests: >> - scale factor of 30 used >> - tests run using the following input parameters for pgbench: >> >> >> Try connecting to both the coordinators, it should give you better >> performance, esp, when you are using distributed tables. With distributed >> tables, coordinator gets involved in query execution more than that in the >> case of replicated tables. So, balancing load across two coordinators would >> help. >> >> >> >> Clients Threads Duration Transactions >> 1 1 100 6204 >> 2 2 100 9960 >> 4 4 100 12880 >> 6 6 100 1676 >> >> >> >> 8 >> 8 8 100 19758 >> 10 10 100 21944 >> 12 12 100 20674 >> >> The run went well until the 8 clients. I started seeing errors on 10 >> clients onwards and eventually the 14 client run has been hanging around for >> over an hour now. The errors I have been seeing on console are the following >> : >> >> pgbench console : >> Client 8 aborted in state 12: ERROR: GTM error, could not obtain snapshot >> Client 0 aborted in state 13: ERROR: maximum number of prepared >> transactions reached >> Client 7 aborted in state 13: ERROR: maximum number of prepared >> transactions reached >> Client 11 aborted in state 13: ERROR: maximum number of prepared >> transactions reached >> Client 9 aborted in state 13: ERROR: maximum number of prepared >> transactions reached >> >> node console: >> ERROR: GTM error, could not obtain snapshot >> STATEMENT: INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) >> VALUES (253, 26, 1888413, -817, CURRENT_TIMESTAMP); >> ERROR: maximum number of prepared transactions reached >> HINT: Increase max_prepared_transactions (currently 10). >> STATEMENT: PREPARE TRANSACTION 'T201428' >> ERROR: maximum number of prepared transactions reached >> STATEMENT: END; >> ERROR: maximum number of prepared transactions reached >> STATEMENT: END; >> ERROR: maximum number of prepared transactions reached >> STATEMENT: END; >> ERROR: maximum number of prepared transactions reached >> STATEMENT: END; >> ERROR: GTM error, could not obtain snapshot >> STATEMENT: INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) >> VALUES (140, 29, 2416403, -4192, CURRENT_TIMESTAMP); >> >> I was also watching the processes on each node and see the following for >> the 14 client run: >> >> >> Node1 : >> postgres 25571 10511 0 04:41 ? 00:00:02 postgres: postgres >> postgres ::1(33481) TRUNCATE TABLE waiting >> postgres 25620 11694 0 04:46 ? 00:00:00 postgres: postgres >> postgres pgbench-address (50388) TRUNCATE TABLE >> >> Node2: >> postgres 10979 9631 0 Jul05 ? 00:00:42 postgres: postgres >> postgres coord1-address(57357) idle in transaction >> >> Node3: >> postgres 20264 9911 0 08:35 ? 00:00:05 postgres: postgres >> postgres coord1-address(51406) TRUNCATE TABLE waiting >> >> >> I was going to restart the processes on all nodes and start over but did >> not want to lose this data as it could be useful information. >> >> Any explanation on the above issue is much appreciated. I will try the >> next run with a higher value set for max_prepared_transactions. Any >> recommendations for a good value on this front? >> >> thanks, >> Shankar >> >> >> ________________________________ >> From: Shankar Hariharan <har...@ya...> >> To: Ashutosh Bapat <ash...@en...> >> Cc: "pos...@li..." >> <pos...@li...> >> Sent: Friday, July 6, 2012 8:22 AM >> >> Subject: Re: [Postgres-xc-developers] Question on gtm-proxy >> >> Hi Ashutosh, >> I was trying to size the load on a server and was wondering if a GTM >> could be shared w/o much performance overhead between a small number of >> datanodes and coordinators. I will post my findings here. >> thanks, >> Shankar >> >> ________________________________ >> From: Ashutosh Bapat <ash...@en...> >> To: Shankar Hariharan <har...@ya...> >> Cc: "pos...@li..." >> <pos...@li...> >> Sent: Friday, July 6, 2012 12:25 AM >> Subject: Re: [Postgres-xc-developers] Question on gtm-proxy >> >> Hi Shankar, >> Running gtm-proxy has shown to improve the performance, because it lessens >> the load on GTM, by serving requests locally. Why do you want the >> coordinators to connect directly to the GTM? Are you seeing any performance >> improvement from doing that? >> >> On Fri, Jul 6, 2012 at 10:08 AM, Shankar Hariharan >> <har...@ya...> wrote: >> >> Follow up to earlier email. In the setup described below, can I avoid >> using a gtm-proxy? That is, can I just simply point coordinators to the one >> gtm running on node 3 ? >> My initial plan was to just run the gtm on node 3 then I thought I could >> try a datanode without a local coordinator which was why I put these two >> together on node 3. >> thanks, >> Shankar >> >> ________________________________ >> From: Shankar Hariharan <har...@ya...> >> To: "pos...@li..." >> <pos...@li...> >> Sent: Thursday, July 5, 2012 11:35 PM >> Subject: Question on multiple coordinators >> >> Hello, >> >> Am trying out XC 1.0 in the following configuraiton. >> Node 1 - Coord1, Datanode1, gtm-proxy1 >> Node 2- Coord2, Datanode2, gtm-proxy2 >> Node 3- Datanode3, gtm >> >> I setup all nodes but forgot to add Coord1 to Coord2 and vice versa. In >> addition I missed the pg_hba edit as well. So the first table T1 that I >> created for distribution from Coord1 was not "visible| from Coord2 but was >> on all the data nodes. >> I tried to get Coord2 backinto business in various ways but the first >> table I created refused to show up on Coord2 : >> - edit pg_hba and add node on both coord1 and 2. Then run select >> pgxc_pool_reload(); >> - restart coord 1 and 2 >> - drop node c2 from c1 and c1 from c2 and add them back followed by select >> pgxc_pool_reload(); >> >> So I tried to create the same table T1 from Coord2 to observe behavior and >> it did not like it clearly as all nodes it "wrote" to reported that the >> table already existed which was good. At this point I could understand that >> Coord2 and Coord1 are not talking alright so I created a new table from >> coord1 with replication. This table was visible from both now. >> >> Question is should I expect to see the first table, let me call it T1 >> after a while from Coord2 also? >> >> >> thanks, >> Shankar >> >> >> >> >> ------------------------------------------------------------------------------ >> Live Security Virtual Conference >> Exclusive live event will cover all the ways today's security and >> threat landscape has changed and how IT managers can respond. Discussions >> will include endpoint security, mobile security and the latest in malware >> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ >> _______________________________________________ >> Postgres-xc-developers mailing list >> Pos...@li... >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >> >> >> >> >> -- >> Best Wishes, >> Ashutosh Bapat >> EntepriseDB Corporation >> The Enterprise Postgres Company >> >> >> >> >> >> >> >> >> -- >> Best Wishes, >> Ashutosh Bapat >> EntepriseDB Corporation >> The Enterprise Postgres Company >> >> >> > > > > -- > Best Wishes, > Ashutosh Bapat > EntepriseDB Corporation > The Enterprise Postgres Company > > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X