postgres-xc-general — General info and messages

Re: [Postgres-xc-general] ERROR: Failed to get pooled connections

[Andrei M. <and...@gm...>]

2012/10/24 Vladimir Stavrinov <vst...@gm...>

> On Wed, Oct 24, 2012 at 06:25:56PM +0300, Andrei Martsinchyk wrote:
>
> >    I guess you got familiar with other solutions out there and trying
> >    to find in XC somesing similar. But XC is different.  The main goal
> >    of XC is scalability, not HA.
>
> Despite of its name or goal XC is distributed database only.
>
> >    But it looks like we understand "scalability" differently too.
>
> The difference is that You narrow its meaning.
>
> >    What would a classic database owner do if he is not satisfied with
> >    the performance of his database? He would move to better hardware!
> >    That basically what we mean by "scalability".
>
> If You purchase more powerful hardware to replace old one
> no matter it is database server or Your desktop machine it is not
> scalability it is rather upgrade or stepping up to happy future.
>
>
That is the reason to buy latest IPhone. Some servers run for years without
even reboot. Usually people are replacing servers only if they really need
to do that.



> >    However in case of classic single-server DBMS you would notice,
> >    that hardware cost grows exponentially. With XC you may scale
> >    linearly - if you run XC, for example, on 8 node cluster you may
> >    add 8 more and get 2 times more TPS. That is because XC is able to
> >    intellegently split your data on your nodes. If you have one huge
> >    table on N nodes you can write data N times faster, since each
> >    particular row goes to one node and each node processes 1/Nth of
> >    total requests. Read is scaling either - if you search by key each
> >    node will search only local part of data, wich is N times smaller
> >    then entire table, and all nodes search in parallel. More, if the
> >    search key is the same as distribution key only one node will
> >    search, that one where rows may be located perfect if there are
> >    multiple concurrent searchers.
>
> Thank You for long explanation, but it is excess. I was aware when
> wrote ... But it nothing changes.
>
> >    You mentioned adding nodes online. That feature is not *yet*
> >    implemented in XC. I would not call it "scalability" though. I
> >    would call it flexibility.
>
> It is very polite definition if we remember that it is alternative to
> recreating entire cluster from scratch.
>
>
Nobody upgrades daily. I think it is not a lot of trouble to recreate
cluster once per few years.


> >    That approach is not good for HA: redundancy is needed for HA, XC
> >    is not redundant if you lost one node you lost part of data. XC
> >    will still live in that case and it would be even able to serve
> >    some queries. But query that needs lost
>
> No, it stops working at all. (To be sure: this was tested against 1.0.0,
> but 1.0.1)
>
>
I think your test was incorrect. It works.


> >    node would fail. However XC supports Postgres replication, you may
> >    configure replicas of your datanodes and switch to slave if master
> >    fails. Currently an external solution is required to build such
> >    kind of system. I do not think this is a problem. Nobody needs pure
> >    DBMS anyway, at least frontend is needed. XC is a good brick to
> >    build system that perfectly fulfill customer requirements.
>
> I already wrote: any external solution doubles hardware park and add
> complexity of the system.
>
>
Why it doubles hardware park, multiple components may share same hardware.
HA solution means extra complexity either it external or internal. There
are people out there who do not want that complexity, they are happy with
just performance scalability. They could use XC as is. If there is demand
of HA on market, other developers may create XC-based solutions, more or
less integrated. Consumers may choose one of those solutions. Everybody
wins.
If XC integrates one approach it will lose flexibility in this area.


> >    And about transparency. Application sees XC as a generic DBMS and
> >    can access it using generic SQL. Even CREATE TABLE without
> >    DISTRIBUTE BY clause is supported. But like with any other DBMS
>
> In this case by default it will be "BY REPLICATION" and as result it
> looses main XC feature: write scalability.
>
>
The criteria is pretty complex. However HASH distribution takes priority.


>
> >    database architect must know DBMS internals well and use provided
>
> But he could not know how much nodes You have or You will have and what
> other databases are there running and how existing data already
> distributed. DBMS internals is not transparency related issue at all,
> because there are always difference what for You are writing Your
> application: for mysql, for porstgresql, for oracle or for all of them.
>
>
I did not quite understand what you mean here. There are a lot of important
for system design things along all the hardware and software stack. The
more is known to developers the better result will be. One may design
database on XC if he does know anything about it at all, with pure SQL, and
the database will work. But much better result can be achieved if database
is designed consciously.
Number of nodes does not matter for distribution planning, btw.


> >    tools, like SQL extensions to tune up specific database for
> >    application. XC is capable to achieve much better then linear
> >    performance when it is optimized.
>
> It is acceptable in specific cases, and should be considered as
> customization. But in most cases we need common solution.
>
> --
>
> ***************************
> ##  Vladimir Stavrinov
> ##  vst...@gm...
> ***************************
>
>


-- 
Andrei Martsinchyk

StormDB - http://www.stormdb.com
The Database Cloud

Re: [Postgres-xc-general] ERROR: Failed to get pooled connections

[Jim M. <ji...@gm...>]

On Wed, Oct 24, 2012 at 12:53 PM, Vladimir Stavrinov
<vst...@gm...> wrote:
> On Wed, Oct 24, 2012 at 11:42:43AM -0400, Jim Mlodgenski wrote:
>
>> That's not actually the case. XC will automatically distribute the
>> table even if the DISTRIBUTE BY clause is not in the CREATE TABLE
>
> In this case by default it will be "BY REPLICATION" and as result it
> looses main XC feature: write scalability.

The default will to distribute by HASH if it has some sort of valid
column to use. If there is no way to determine which column to use, it
will fall back and use a round robin distribution. It never uses "BY
REPLICATION" by default.

>
> --
>
> ***************************
> ##  Vladimir Stavrinov
> ##  vst...@gm...
> ***************************
>

Re: [Postgres-xc-general] ERROR: Failed to get pooled connections

[Vladimir S. <vst...@gm...>]

On Wed, Oct 24, 2012 at 11:42:43AM -0400, Jim Mlodgenski wrote:

> That's not actually the case. XC will automatically distribute the
> table even if the DISTRIBUTE BY clause is not in the CREATE TABLE

In this case by default it will be "BY REPLICATION" and as result it
looses main XC feature: write scalability.

-- 

***************************
##  Vladimir Stavrinov
##  vst...@gm...
***************************

Re: [Postgres-xc-general] ERROR: Failed to get pooled connections

[Vladimir S. <vst...@gm...>]

On Wed, Oct 24, 2012 at 06:25:56PM +0300, Andrei Martsinchyk wrote:

>    I guess you got familiar with other solutions out there and trying
>    to find in XC somesing similar. But XC is different.  The main goal
>    of XC is scalability, not HA.

Despite of its name or goal XC is distributed database only.

>    But it looks like we understand "scalability" differently too.

The difference is that You narrow its meaning.

>    What would a classic database owner do if he is not satisfied with
>    the performance of his database? He would move to better hardware!
>    That basically what we mean by "scalability".

If You purchase more powerful hardware to replace old one
no matter it is database server or Your desktop machine it is not
scalability it is rather upgrade or stepping up to happy future.

>    However in case of classic single-server DBMS you would notice,
>    that hardware cost grows exponentially. With XC you may scale
>    linearly - if you run XC, for example, on 8 node cluster you may
>    add 8 more and get 2 times more TPS. That is because XC is able to
>    intellegently split your data on your nodes. If you have one huge
>    table on N nodes you can write data N times faster, since each
>    particular row goes to one node and each node processes 1/Nth of
>    total requests. Read is scaling either - if you search by key each
>    node will search only local part of data, wich is N times smaller
>    then entire table, and all nodes search in parallel. More, if the
>    search key is the same as distribution key only one node will
>    search, that one where rows may be located perfect if there are
>    multiple concurrent searchers.

Thank You for long explanation, but it is excess. I was aware when
wrote ... But it nothing changes.

>    You mentioned adding nodes online. That feature is not *yet*
>    implemented in XC. I would not call it "scalability" though. I
>    would call it flexibility.

It is very polite definition if we remember that it is alternative to
recreating entire cluster from scratch.

>    That approach is not good for HA: redundancy is needed for HA, XC
>    is not redundant if you lost one node you lost part of data. XC
>    will still live in that case and it would be even able to serve
>    some queries. But query that needs lost

No, it stops working at all. (To be sure: this was tested against 1.0.0,
but 1.0.1)

>    node would fail. However XC supports Postgres replication, you may
>    configure replicas of your datanodes and switch to slave if master
>    fails. Currently an external solution is required to build such
>    kind of system. I do not think this is a problem. Nobody needs pure
>    DBMS anyway, at least frontend is needed. XC is a good brick to
>    build system that perfectly fulfill customer requirements.

I already wrote: any external solution doubles hardware park and add
complexity of the system.

>    And about transparency. Application sees XC as a generic DBMS and
>    can access it using generic SQL. Even CREATE TABLE without
>    DISTRIBUTE BY clause is supported. But like with any other DBMS

In this case by default it will be "BY REPLICATION" and as result it
looses main XC feature: write scalability.


>    database architect must know DBMS internals well and use provided

But he could not know how much nodes You have or You will have and what
other databases are there running and how existing data already
distributed. DBMS internals is not transparency related issue at all,
because there are always difference what for You are writing Your
application: for mysql, for porstgresql, for oracle or for all of them.

>    tools, like SQL extensions to tune up specific database for
>    application. XC is capable to achieve much better then linear
>    performance when it is optimized.

It is acceptable in specific cases, and should be considered as
customization. But in most cases we need common solution.

-- 

***************************
##  Vladimir Stavrinov
##  vst...@gm...
***************************

Re: [Postgres-xc-general] ERROR: Failed to get pooled connections

[Jim M. <ji...@gm...>]

On Wed, Oct 24, 2012 at 11:13 AM, Vladimir Stavrinov
<vst...@gm...> wrote:
> On Wed, Oct 24, 2012 at 07:40:33PM +0530, Nikhil Sontakke wrote:
>
>>    "While many standard MySQL schemas and applications can work using
>>    MySQL Cluster, it is also true that unmodified applications and
>>    database schemas may be slightly incompatible or have suboptimal
>>    performance when run using MySQL Cluster"
>
> I was aware of this when wrote previous message.
>
>>    So transparency might come at a cost in the case of MySQL cluster as well.
>
> It is rare and specific cases and absolutely different thing then we have with
> XC. In XC we must take care about "CREATE TABLE ... DISTRIBUTE BY ..."
> EVERYWHERE and ALWAYS.

That's not actually the case. XC will automatically distribute the
table even if the DISTRIBUTE BY clause is not in the CREATE TABLE
statement. It uses the primary key and foreign keys information to
determine a distribution key if one is not provided. In many cases
this is perfectly acceptable and completely transparent to the
application. I've moved over several websites to XC never needing to
touch the DDL.

>
>
>>    In general Postgres has all along believed that the user is more
>>    intelligent and will take the pains to understand the nuances of
>>    their use case and configure the database accordingly. That's why
>
> Again it is different things. It is not configuration of database. It
> is rewriting installation sql scripts. Imagine if You need install third
> party application. What about upgrade? And what about lot of such
> applications? No, it is not acceptable for production.
>
> This is example of core of my claims here: You don't think about real life
> and production environment.
>
>
>>    perhaps even the stock postgresql.conf configuration file is pretty
>>    conservative and users tweak it as per their requirements.
>
> To edit configuration file postgresql.conf is good idea, but rewriting
> installation sql script every time is very bad idea.
>
>>      Impossibility to extend cluster online means it is not scalable.
>>
>>    As you rightly mention below, this is indeed a "young" project and IMHO it's maturing along proper lines.
>
> Good news. News is that You agree with me in something.
>
>>      Again: it should not be external tool, it should be internal,
>>      integral, essential feature.
>>
>>    Some people will say exactly the opposite. Why add something
>
> Didn't hear.
>
>>    minimal internal support. Like for example the Corosync/Pacemaker
>>    LinuxHA product maybe along with some of the tools that Suzuki san
>
> That is exactly what I am using. But it is not an alternative for internal
> solution.
>
>>    applications, the XC cluster continues to function.  As long as
>>    datanodes are equipped with replication and an HA strategy is in
>>    place to handle datanodes going down and failing over to a promoted
>>    standby,  then again the cluster continues to function.
>
> Good. But bad thing is that with any external solution You should twice
> Your hardware park for data nodes, because only half of them will be
> under work load. This is essential and main reason why solution should
> be internal. The next one is manageability and complexity of whole
> system.
>
>>    Here seems to be the fundamental difference between mysql cluster
>>    and PGXC. Everything appears to be "replicated" in MySQL cluster
>>    and all nodes are mirror images of each other. In PGXC, data can be
>>    partitioned across nodes as well. It is for this that we provide
>>    the flexibility to the user via the DISTRIBUTE BY clause.
>
> It seems only, but is not true. All data are distributed between groups
> of data nodes. Replicas are inside group only.
>
>>    AIUI, all Mysql nodes are images of each other. While that's good
>>    for reads, that is not so good for writes, no?
>
> No, see above.
>
>>    Data node addition is a work in progress in XC currently.
>
> I saw already:
>
> http://postgres-xc.sourceforge.net/roadmap.html
>
> But it is issue of priority.
>
> --
>
> ***************************
> ##  Vladimir Stavrinov
> ##  vst...@gm...
> ***************************
>
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://p.sf.net/sfu/appdyn_sfd2d_oct
> _______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general

Re: [Postgres-xc-general] ERROR: Failed to get pooled connections

[Andrei M. <and...@gm...>]

Hi Vladimir,

I guess you got familiar with other solutions out there and trying to find
in XC somesing similar. But XC is different. The main goal of XC is
scalability, not HA.
But it looks like we understand "scalability" differently too.
What would a classic database owner do if he is not satisfied with the
performance of his database? He would move to better hardware! That
basically what we mean by "scalability".
However in case of classic single-server DBMS you would notice, that
hardware cost grows exponentially. With XC you may scale linearly - if you
run XC, for example, on 8 node cluster you may add 8 more and get 2 times
more TPS. That is because XC is able to intellegently split your data on
your nodes. If you have one huge table on N nodes you can write data N
times faster, since each particular row goes to one node and each node
processes 1/Nth of total requests. Read is scaling either - if you search
by key each node will search only local part of data, wich is N times
smaller then entire table, and all nodes search in parallel. More, if the
search key is the same as distribution key only one node will search, that
one where rows may be located perfect if there are multiple concurrent
searchers.
You mentioned adding nodes online. That feature is not *yet* implemented in
XC. I would not call it "scalability" though. I would call it flexibility.
That approach is not good for HA: redundancy is needed for HA, XC is not
redundant if you lost one node you lost part of data. XC will still live in
that case and it would be even able to serve some queries. But query that
needs lost node would fail. However XC supports Postgres replication, you
may configure replicas of your datanodes and switch to slave if master
fails. Currently an external solution is required to build such kind of
system. I do not think this is a problem. Nobody needs pure DBMS anyway, at
least frontend is needed. XC is a good brick to build system that perfectly
fulfill customer requirements.
And about transparency. Application sees XC as a generic DBMS and can
access it using generic SQL. Even CREATE TABLE without DISTRIBUTE BY clause
is supported. But like with any other DBMS database architect must know
DBMS internals well and use provided tools, like SQL extensions to tune up
specific database for application. XC is capable to achieve much better
then linear performance when it is optimized.


2012/10/24 Vladimir Stavrinov <vst...@gm...>

> On Wed, Oct 24, 2012 at 08:08:32PM +0900, Michael Paquier wrote:
>
> >    Sure, XC provides thanks to its architecture naturally transparency
> and scalability.
>
> What does XC provides? My two rhetorical questions above imply answers
> "NO".  Necessity to adapt application means cluster is not transparent.
> Impossibility to extend cluster online means it is not scalable.
>
> More over, this two issues are interrelated, because You should rewrite
> "CREATE TABLE" statement every time you expand (read: recreate) Your
> cluster. But this issue looks much worse if node fails containing tables
> with different distributed schemas. This is uncontrollable model.
>
> >    Load balancing can be provided between Coordinator and Datanodes
> >    depending on applications, or at Coordinator level
>
> It should not depend on application, it should be an cluster's global
> function.
>
> >    For HA, Koichi is currently working on some tools to provide that,
>
> Again: it should not be external tool, it should be internal, integral,
> essential feature.
>
> >    I am not sure you can that easily compare XC and mysql cluster,
> >    both share the same architectures, but once of the main
>
> I don't know what there is "the same", but in functionality it is
> totally different. Mysql cluster has the precise and clear clustering
> model:
>
> 1. If some nodes fail cluster continues to work as soon as there remains
> at least one healthy node in every group.
>
> 2. No "CREATE TABLE ... DISTRIBUTE BY ..." statement. You just define
> the number of replicas at configuration level. Yes, now there are only
> one option is available that make sense with two replicas, but it is
> enough.
>
> 3. Read and write scalability (i.e. LB) at the same time for all tables
> (i.e. on the cluster level).
>
> 4. You can add data node online, i.e. without restarting (not to mention
> "recreating" as for XC) cluster. Yes, only new data will go to the new
> node in this case. But You can totally redistribute it with restart.
>
> So it is full flagged cluster, that's not true for XC and it's a pity.
>
> >    differences coming to my mind is that XC is far more flexible in
> >    terms of license (BSD and not GPL), and like PostgreSQL, no company
> >    has the control of its code like mysql products which Oracle relies
>
> Yes, and this is why I am persuading all developers migrate to
> Postgresql. But it is off topic here where we are discussing
> functionality, but not an licence issues.
>
> Be tolerant to my criticism, I wouldn't say You made bad thing, I was
> amazing when first read "write-scalable, synchronous multi-master,
> transparent PostgreSQL cluster" in Your description that I completely
> and exactly copied into description of my debian package, but I was
> notably disappointed after my first test showing me that it is odd with
> reality. It would not be so bad itself, as soon as it is young project,
> but much worse that this discussion shows there are something wrong with
> Your priorities and fundamental approach.
>
> --
>
> ***************************
> ##  Vladimir Stavrinov
> ##  vst...@gm...
> ***************************
>
>
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://p.sf.net/sfu/appdyn_sfd2d_oct
> _______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>



-- 
Andrei Martsinchyk

StormDB - http://www.stormdb.com
The Database Cloud

Re: [Postgres-xc-general] ERROR: Failed to get pooled connections

[Vladimir S. <vst...@gm...>]

On Wed, Oct 24, 2012 at 07:40:33PM +0530, Nikhil Sontakke wrote:

>    "While many standard MySQL schemas and applications can work using
>    MySQL Cluster, it is also true that unmodified applications and
>    database schemas may be slightly incompatible or have suboptimal
>    performance when run using MySQL Cluster"

I was aware of this when wrote previous message.

>    So transparency might come at a cost in the case of MySQL cluster as well.

It is rare and specific cases and absolutely different thing then we have with
XC. In XC we must take care about "CREATE TABLE ... DISTRIBUTE BY ..."
EVERYWHERE and ALWAYS.


>    In general Postgres has all along believed that the user is more
>    intelligent and will take the pains to understand the nuances of
>    their use case and configure the database accordingly. That's why

Again it is different things. It is not configuration of database. It
is rewriting installation sql scripts. Imagine if You need install third
party application. What about upgrade? And what about lot of such
applications? No, it is not acceptable for production.

This is example of core of my claims here: You don't think about real life
and production environment.


>    perhaps even the stock postgresql.conf configuration file is pretty
>    conservative and users tweak it as per their requirements.

To edit configuration file postgresql.conf is good idea, but rewriting
installation sql script every time is very bad idea.

>      Impossibility to extend cluster online means it is not scalable.
> 
>    As you rightly mention below, this is indeed a "young" project and IMHO it's maturing along proper lines.

Good news. News is that You agree with me in something.

>      Again: it should not be external tool, it should be internal,
>      integral, essential feature.
> 
>    Some people will say exactly the opposite. Why add something

Didn't hear.

>    minimal internal support. Like for example the Corosync/Pacemaker
>    LinuxHA product maybe along with some of the tools that Suzuki san

That is exactly what I am using. But it is not an alternative for internal
solution.

>    applications, the XC cluster continues to function.  As long as
>    datanodes are equipped with replication and an HA strategy is in
>    place to handle datanodes going down and failing over to a promoted
>    standby,  then again the cluster continues to function.

Good. But bad thing is that with any external solution You should twice
Your hardware park for data nodes, because only half of them will be
under work load. This is essential and main reason why solution should
be internal. The next one is manageability and complexity of whole
system.

>    Here seems to be the fundamental difference between mysql cluster
>    and PGXC. Everything appears to be "replicated" in MySQL cluster
>    and all nodes are mirror images of each other. In PGXC, data can be
>    partitioned across nodes as well. It is for this that we provide
>    the flexibility to the user via the DISTRIBUTE BY clause.

It seems only, but is not true. All data are distributed between groups
of data nodes. Replicas are inside group only.

>    AIUI, all Mysql nodes are images of each other. While that's good
>    for reads, that is not so good for writes, no?

No, see above.

>    Data node addition is a work in progress in XC currently.

I saw already:

http://postgres-xc.sourceforge.net/roadmap.html

But it is issue of priority.

-- 

***************************
##  Vladimir Stavrinov
##  vst...@gm...
***************************

Re: [Postgres-xc-general] ERROR: Failed to get pooled connections

[Nikhil S. <ni...@st...>]

Hi Vladimir,

Thanks for your very frank perspectives on PGXC.


> >    Sure, XC provides thanks to its architecture naturally transparency
> and scalability.
>
> What does XC provides? My two rhetorical questions above imply answers
> "NO".  Necessity to adapt application means cluster is not transparent.
>

Well even the MySQL cluster documentation states the below:

"While many standard MySQL schemas and applications can work using MySQL
Cluster, it is also true that unmodified applications and database schemas
may be slightly incompatible or have suboptimal performance when run using
MySQL Cluster"

So transparency might come at a cost in the case of MySQL cluster as well.

In general Postgres has all along believed that the user is more
intelligent and will take the pains to understand the nuances of their use
case and configure the database accordingly. That's why perhaps even the
stock postgresql.conf configuration file is pretty conservative and users
tweak it as per their requirements.


> Impossibility to extend cluster online means it is not scalable.
>
>
As you rightly mention below, this is indeed a "young" project and IMHO
it's maturing along proper lines.


> More over, this two issues are interrelated, because You should rewrite
> "CREATE TABLE" statement every time you expand (read: recreate) Your
> cluster. But this issue looks much worse if node fails containing tables
> with different distributed schemas. This is uncontrollable model.
>
> >    Load balancing can be provided between Coordinator and Datanodes
> >    depending on applications, or at Coordinator level
>
> It should not depend on application, it should be an cluster's global
> function.
>
> >    For HA, Koichi is currently working on some tools to provide that,
>
> Again: it should not be external tool, it should be internal, integral,
> essential feature.
>
>
Some people will say exactly the opposite. Why add something internal when
off-the-shelf technologies can help with minimal internal support. Like for
example the Corosync/Pacemaker LinuxHA product maybe along with some of the
tools that Suzuki san has provided can be combined with the replication
capabilities that Postgres (and PGXC) provides.

Infact with synchronous replication in place, a properly architected
solution using the above packages can provide very good HA capabilities.
There's already redundancy for the coordinator nodes.

>    I am not sure you can that easily compare XC and mysql cluster,
> >    both share the same architectures, but once of the main
>
> I don't know what there is "the same", but in functionality it is
> totally different. Mysql cluster has the precise and clear clustering
> model:
>
> 1. If some nodes fail cluster continues to work as soon as there remains
> at least one healthy node in every group.
>
>
As long as one coordinator node is around and reachable to applications,
the XC cluster continues to function.

As long as datanodes are equipped with replication and an HA strategy is in
place to handle datanodes going down and failing over to a promoted
standby,  then again the cluster continues to function.


> 2. No "CREATE TABLE ... DISTRIBUTE BY ..." statement. You just define
> the number of replicas at configuration level. Yes, now there are only
> one option is available that make sense with two replicas, but it is
> enough.
>
>
Here seems to be the fundamental difference between mysql cluster and PGXC.
Everything appears to be "replicated" in MySQL cluster and all nodes are
mirror images of each other. In PGXC, data can be partitioned across nodes
as well. It is for this that we provide the flexibility to the user via the
DISTRIBUTE BY clause.


> 3. Read and write scalability (i.e. LB) at the same time for all tables
> (i.e. on the cluster level).
>
>
AIUI, all Mysql nodes are images of each other. While that's good for
reads, that is not so good for writes, no?


> 4. You can add data node online, i.e. without restarting (not to mention
> "recreating" as for XC) cluster. Yes, only new data will go to the new
> node in this case. But You can totally redistribute it with restart.
>
> So it is full flagged cluster, that's not true for XC and it's a pity.
>
>
Data node addition is a work in progress in XC currently.


>  >    differences coming to my mind is that XC is far more flexible in
> >    terms of license (BSD and not GPL), and like PostgreSQL, no company
> >    has the control of its code like mysql products which Oracle relies
>
> Yes, and this is why I am persuading all developers migrate to
> Postgresql. But it is off topic here where we are discussing
> functionality, but not an licence issues.
>
> Be tolerant to my criticism, I wouldn't say You made bad thing, I was
> amazing when first read "write-scalable, synchronous multi-master,
> transparent PostgreSQL cluster" in Your description that I completely
> and exactly copied into description of my debian package, but I was
> notably disappointed after my first test showing me that it is odd with
> reality. It would not be so bad itself, as soon as it is young project,
> but much worse that this discussion shows there are something wrong with
> Your priorities and fundamental approach.
>
>
Criticism is welcome. It helps us improve the product as we go along :)

Regards,
Nikhils
-- 
StormDB - http://www.stormdb.com
The Database Cloud
Postgres-XC Support and Service

Re: [Postgres-xc-general] ERROR: Failed to get pooled connections

[Vladimir S. <vst...@gm...>]

On Wed, Oct 24, 2012 at 08:08:32PM +0900, Michael Paquier wrote:

>    Sure, XC provides thanks to its architecture naturally transparency and scalability.

What does XC provides? My two rhetorical questions above imply answers
"NO".  Necessity to adapt application means cluster is not transparent.
Impossibility to extend cluster online means it is not scalable.

More over, this two issues are interrelated, because You should rewrite
"CREATE TABLE" statement every time you expand (read: recreate) Your
cluster. But this issue looks much worse if node fails containing tables
with different distributed schemas. This is uncontrollable model.

>    Load balancing can be provided between Coordinator and Datanodes
>    depending on applications, or at Coordinator level

It should not depend on application, it should be an cluster's global function.

>    For HA, Koichi is currently working on some tools to provide that,

Again: it should not be external tool, it should be internal, integral,
essential feature.

>    I am not sure you can that easily compare XC and mysql cluster,
>    both share the same architectures, but once of the main

I don't know what there is "the same", but in functionality it is
totally different. Mysql cluster has the precise and clear clustering
model:

1. If some nodes fail cluster continues to work as soon as there remains
at least one healthy node in every group.

2. No "CREATE TABLE ... DISTRIBUTE BY ..." statement. You just define
the number of replicas at configuration level. Yes, now there are only
one option is available that make sense with two replicas, but it is
enough.

3. Read and write scalability (i.e. LB) at the same time for all tables
(i.e. on the cluster level).

4. You can add data node online, i.e. without restarting (not to mention
"recreating" as for XC) cluster. Yes, only new data will go to the new
node in this case. But You can totally redistribute it with restart.

So it is full flagged cluster, that's not true for XC and it's a pity.

>    differences coming to my mind is that XC is far more flexible in
>    terms of license (BSD and not GPL), and like PostgreSQL, no company
>    has the control of its code like mysql products which Oracle relies

Yes, and this is why I am persuading all developers migrate to
Postgresql. But it is off topic here where we are discussing
functionality, but not an licence issues. 

Be tolerant to my criticism, I wouldn't say You made bad thing, I was
amazing when first read "write-scalable, synchronous multi-master,
transparent PostgreSQL cluster" in Your description that I completely
and exactly copied into description of my debian package, but I was
notably disappointed after my first test showing me that it is odd with
reality. It would not be so bad itself, as soon as it is young project,
but much worse that this discussion shows there are something wrong with
Your priorities and fundamental approach.

-- 

***************************
##  Vladimir Stavrinov
##  vst...@gm...
***************************

Re: [Postgres-xc-general] ERROR: Failed to get pooled connections

[Michael P. <mic...@gm...>]

On Wed, Oct 24, 2012 at 5:38 PM, Vladimir Stavrinov <vst...@gm...>wrote:

> On Wed, Aug 01, 2012 at 06:22:27PM +0900, Michael Paquier wrote:
>
> >>      I thought HA & LB is main feature of any cluster.
> >
> >    Transparency and scalability are even more important.
>
> Really? If You should rewrite every "CREATE TABLE" statement, when You
> need write scalability, is it "Transparency"? And if You should recreate
> entire cluster from scratch, when You need to add a node, is it
> "scalability"?
>
> It is hard to imagine who will use in production environment such
> cluster without HA, LB, transparency and scalability. I don't want to
> compare Your creature with mysql cluster at least because it is in
> memory database, but it has all of those features, absolutely
> necessary for any cluster.
>
Sure, XC provides thanks to its architecture naturally transparency and
scalability.
Load balancing can be provided between Coordinator and Datanodes depending
on applications, or at Coordinator level using an extra layer providing
this functionality.
For HA, Koichi is currently working on some tools to provide that, tools
you can find here:
https://github.com/koichi-szk/PGXC-Tools

Like Postgres, you need an external application to provide it.

I am not sure you can that easily compare XC and mysql cluster, both share
the same architectures, but once of the main differences coming to my mind
is that XC is far more flexible in terms of license (BSD and not GPL), and
like PostgreSQL, no company has the control of its code like mysql products
which Oracle relies on.
-- 
Michael Paquier
http://michael.otacoo.com

Re: [Postgres-xc-general] INSERT.. RETURNING not supported?

[Abbas B. <abb...@en...>]

On Wed, Oct 24, 2012 at 2:14 PM, Paulo Pires <pj...@ub...> wrote:

>
> On 03/10/12 00:25, Michael Paquier wrote:
>
>
>
> On Wed, Oct 3, 2012 at 2:27 AM, Paulo Pires <pj...@ub...> wrote:
>
>>
>>
>> On Tue Sep 18 11:21:21 2012, Michael Paquier wrote:
>> >
>> >
>> > On Tue, Sep 18, 2012 at 5:43 PM, Paulo Pires <pj...@ub...
>>  > <mailto:pj...@ub...>> wrote:
>> >
>> >     Hi Michael,
>> >
>> >     Thanks so much for your support.
>> >
>> >     Could you tell me if I should follow postgres-xc-developers@ or
>> >     postgres-xc-committers in order to be notified as soon as
>> >     RETURNING support is implemented?
>> >
>> > Sure.
>> > - The mailing list developper is used for conversations about features
>> > and patches, so you will be notified about the commit in GIT with that.
>> > - The commit mailing list is a mailing list where an email is sent
>> > automatically where there is a commit in GIT. So you should follow it.
>> >
>> > You can also follow this twitter bot: https://twitter.com/PostgresXCBot
>> .
>> > This bot sends a tweet automatically when a commit is made with the
>> > header of the commit.
>> > You can also see the list of commits on Github:
>> > https://github.com/postgres-xc/postgres-xc/commits/master
>>
>>  Last updates are from Sep 16th, so I think you don't make any "beta"
>> available. Am I wrong?
>>
> Well, I am working on the implementation of triggers, which is not that
> straightforward.
> So it requires time. The next beta of XC is planned for March or April.
>
>
>>
>> >
>> > I also don't mind dropping an email on the thread where you discussed
>> > about RETURNING on general mailing list once the stuff is committed.
>>
>>  That would be awesome. I'm really needing this to continue my tests.
>>
> Regarding RETURNING, Abbas is working on that.
> He just needs more time to stabilize his patch as he is cleaning a lot of
> things, including things planner for remote query generation.
>
> Is this landing on master? I have been unable to find other development
> branches and even Abbas. I can only see you and Ashutosh as committers.
>

Yes it will land on the master, there are no other dev branches.


>
>
>    --
> Michael Paquier
> http://michael.otacoo.com
>
>
> --
> Paulo Pires
>
>
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://p.sf.net/sfu/appdyn_sfd2d_oct
> _______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>
>


-- 
--
Abbas
Architect
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

Phone: 92-334-5100153

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of
the individual or entity to whom it is addressed. This message
contains information from EnterpriseDB Corporation that may be
privileged, confidential, or exempt from disclosure under applicable
law. If you are not the intended recipient or authorized to receive
this for the intended recipient, any use, dissemination, distribution,
retention, archiving, or copying of this communication is strictly
prohibited. If you have received this e-mail in error, please notify
the sender immediately by reply e-mail and delete this message.

Re: [Postgres-xc-general] INSERT.. RETURNING not supported?

[Michael P. <mic...@gm...>]

On Wed, Oct 24, 2012 at 6:14 PM, Paulo Pires <pj...@ub...> wrote:

>
> On 03/10/12 00:25, Michael Paquier wrote:
>
>
>
> On Wed, Oct 3, 2012 at 2:27 AM, Paulo Pires <pj...@ub...> wrote:
>
>>
>>
>> On Tue Sep 18 11:21:21 2012, Michael Paquier wrote:
>> >
>> >
>> > On Tue, Sep 18, 2012 at 5:43 PM, Paulo Pires <pj...@ub...
>>  > <mailto:pj...@ub...>> wrote:
>> >
>> >     Hi Michael,
>> >
>> >     Thanks so much for your support.
>> >
>> >     Could you tell me if I should follow postgres-xc-developers@ or
>> >     postgres-xc-committers in order to be notified as soon as
>> >     RETURNING support is implemented?
>> >
>> > Sure.
>> > - The mailing list developper is used for conversations about features
>> > and patches, so you will be notified about the commit in GIT with that.
>> > - The commit mailing list is a mailing list where an email is sent
>> > automatically where there is a commit in GIT. So you should follow it.
>> >
>> > You can also follow this twitter bot: https://twitter.com/PostgresXCBot
>> .
>> > This bot sends a tweet automatically when a commit is made with the
>> > header of the commit.
>> > You can also see the list of commits on Github:
>> > https://github.com/postgres-xc/postgres-xc/commits/master
>>
>>  Last updates are from Sep 16th, so I think you don't make any "beta"
>> available. Am I wrong?
>>
> Well, I am working on the implementation of triggers, which is not that
> straightforward.
> So it requires time. The next beta of XC is planned for March or April.
>
>
>>
>> >
>> > I also don't mind dropping an email on the thread where you discussed
>> > about RETURNING on general mailing list once the stuff is committed.
>>
>>  That would be awesome. I'm really needing this to continue my tests.
>>
> Regarding RETURNING, Abbas is working on that.
> He just needs more time to stabilize his patch as he is cleaning a lot of
> things, including things planner for remote query generation.
>
> Is this landing on master? I have been unable to find other development
> branches and even Abbas. I can only see you and Ashutosh as committers.
>
Yes this is planned for master. He is working on it and there will be more
stuff soon.
Abbas is also a committer, just that he is not really committing a lot of
things these days.
-- 
Michael Paquier
http://michael.otacoo.com

Re: [Postgres-xc-general] INSERT.. RETURNING not supported?

[Paulo P. <pj...@ub...>]

On 03/10/12 00:25, Michael Paquier wrote:
>
>
> On Wed, Oct 3, 2012 at 2:27 AM, Paulo Pires <pj...@ub...
> <mailto:pj...@ub...>> wrote:
>
>
>
>     On Tue Sep 18 11:21:21 2012, Michael Paquier wrote:
>     >
>     >
>     > On Tue, Sep 18, 2012 at 5:43 PM, Paulo Pires
>     <pj...@ub... <mailto:pj...@ub...>
>     > <mailto:pj...@ub... <mailto:pj...@ub...>>> wrote:
>     >
>     >     Hi Michael,
>     >
>     >     Thanks so much for your support.
>     >
>     >     Could you tell me if I should follow postgres-xc-developers@ or
>     >     postgres-xc-committers in order to be notified as soon as
>     >     RETURNING support is implemented?
>     >
>     > Sure.
>     > - The mailing list developper is used for conversations about
>     features
>     > and patches, so you will be notified about the commit in GIT
>     with that.
>     > - The commit mailing list is a mailing list where an email is sent
>     > automatically where there is a commit in GIT. So you should
>     follow it.
>     >
>     > You can also follow this twitter bot:
>     https://twitter.com/PostgresXCBot.
>     > This bot sends a tweet automatically when a commit is made with the
>     > header of the commit.
>     > You can also see the list of commits on Github:
>     > https://github.com/postgres-xc/postgres-xc/commits/master
>
>     Last updates are from Sep 16th, so I think you don't make any "beta"
>     available. Am I wrong?
>
> Well, I am working on the implementation of triggers, which is not
> that straightforward.
> So it requires time. The next beta of XC is planned for March or April.
>  
>
>
>     >
>     > I also don't mind dropping an email on the thread where you
>     discussed
>     > about RETURNING on general mailing list once the stuff is committed.
>
>     That would be awesome. I'm really needing this to continue my tests.
>
> Regarding RETURNING, Abbas is working on that.
> He just needs more time to stabilize his patch as he is cleaning a lot
> of things, including things planner for remote query generation.
Is this landing on master? I have been unable to find other development
branches and even Abbas. I can only see you and Ashutosh as committers.

> -- 
> Michael Paquier
> http://michael.otacoo.com

-- 
Paulo Pires

Re: [Postgres-xc-general] ERROR: Failed to get pooled connections

[Vladimir S. <vst...@gm...>]

On Wed, Aug 01, 2012 at 06:22:27PM +0900, Michael Paquier wrote:

>>      I thought HA & LB is main feature of any cluster.
> 
>    Transparency and scalability are even more important.

Really? If You should rewrite every "CREATE TABLE" statement, when You
need write scalability, is it "Transparency"? And if You should recreate
entire cluster from scratch, when You need to add a node, is it
"scalability"?

It is hard to imagine who will use in production environment such
cluster without HA, LB, transparency and scalability. I don't want to
compare Your creature with mysql cluster at least because it is in
memory database, but it has all of those features, absolutely
necessary for any cluster.

-- 

***************************
##  Vladimir Stavrinov
##  vst...@gm...
***************************

Re: [Postgres-xc-general] php pdo driver?

[Michael P. <mic...@gm...>]

On Wed, Oct 17, 2012 at 3:01 PM, Roger Mayes <rog...@gm...>wrote:

> Ok, I put this together in a test action, completely separate from
> anything to do with the rest of my code base, just to be sure it's pdo..
>
>         // $conn = Zend_Db::factory('Pdo_Pgsql', $connArgs);
>         $conn = new PDO('pgsql:host=localhost;dbname=devdb', 'postgres',
> '');
>         $statement = $conn->query("select now()");
>         echo "<pre>";
>         echo print_r($statement->fetchAll(), true);
>         echo "</pre>";
>         $conn = null;
>
> That script doesn't  ...
>
> Eh, hmm.  Nope, that script works fine.  Poo.  So the problem is with my
> code, somehow.  Awesome.  Meh, yeah, it works with Zend_Db, etc., too.
>  Well, on the bright side, now zend / php / pdo has been specifically
> tested.
>
Nice to know it works normally. Thanks.
-- 
Michael Paquier
http://michael.otacoo.com

Re: [Postgres-xc-general] php pdo driver?

[Roger M. <rog...@gm...>]

Ok, I put this together in a test action, completely separate from anything
to do with the rest of my code base, just to be sure it's pdo..

        // $conn = Zend_Db::factory('Pdo_Pgsql', $connArgs);
        $conn = new PDO('pgsql:host=localhost;dbname=devdb', 'postgres',
'');
        $statement = $conn->query("select now()");
        echo "<pre>";
        echo print_r($statement->fetchAll(), true);
        echo "</pre>";
        $conn = null;

That script doesn't  ...

Eh, hmm.  Nope, that script works fine.  Poo.  So the problem is with my
code, somehow.  Awesome.  Meh, yeah, it works with Zend_Db, etc., too.
 Well, on the bright side, now zend / php / pdo has been specifically
tested.



On Tue, Oct 16, 2012 at 8:35 PM, Michael Paquier
<mic...@gm...>wrote:

>
>
> On Wed, Oct 17, 2012 at 12:09 PM, Shavais Zarathustra <sh...@gm...>wrote:
>
>>
>> I've been trying to get the php pdo driver for postgres (php extension
>> pdo_pgsql.so) to work with postgres-xc today, so far with no luck.  It
>> seems to give me a valid connection, but when I try to use
>> $conn->query($sql) or $stmt = $conn->prepare(..)  .. $stmt->execute(), no
>> exceptions are thrown, even for obviously incorrect sql, and no results are
>> fetchable even for trivial sql.  From the terminal, psql is working fine, I
>> can connect and run sql statements and so on, so it seems like the cluster
>> is operating and so forth.
>>
> I haven't used php with XC for a certain amount of time, but as XC uses
> the same connection interface as Postgres, so I do not see why it shouldn't
> work. For example I did some tests with jdbc and perl drivers this week and
> it was working correctly.
> Could you give an example of the SQLs that are not working? Or even a
> script?
> --
> Michael Paquier
> http://michael.otacoo.com
>

Re: [Postgres-xc-general] php pdo driver?

[Michael P. <mic...@gm...>]

On Wed, Oct 17, 2012 at 12:09 PM, Shavais Zarathustra <sh...@gm...>wrote:

>
> I've been trying to get the php pdo driver for postgres (php extension
> pdo_pgsql.so) to work with postgres-xc today, so far with no luck.  It
> seems to give me a valid connection, but when I try to use
> $conn->query($sql) or $stmt = $conn->prepare(..)  .. $stmt->execute(), no
> exceptions are thrown, even for obviously incorrect sql, and no results are
> fetchable even for trivial sql.  From the terminal, psql is working fine, I
> can connect and run sql statements and so on, so it seems like the cluster
> is operating and so forth.
>
I haven't used php with XC for a certain amount of time, but as XC uses the
same connection interface as Postgres, so I do not see why it shouldn't
work. For example I did some tests with jdbc and perl drivers this week and
it was working correctly.
Could you give an example of the SQLs that are not working? Or even a
script?
-- 
Michael Paquier
http://michael.otacoo.com

[Postgres-xc-general] php pdo driver?

[Shavais Z. <sh...@gm...>]

I've been trying to get the php pdo driver for postgres (php extension
pdo_pgsql.so) to work with postgres-xc today, so far with no luck.  It
seems to give me a valid connection, but when I try to use
$conn->query($sql) or $stmt = $conn->prepare(..)  .. $stmt->execute(), no
exceptions are thrown, even for obviously incorrect sql, and no results are
fetchable even for trivial sql.  From the terminal, psql is working fine, I
can connect and run sql statements and so on, so it seems like the cluster
is operating and so forth.

Is pdo_pgsql.so working for you guys?

Re: [Postgres-xc-general] Question about executing queries against Datanode.

[Tomonari K. <kat...@po...>]

Hi, Michael

Thank you for answer.

 >> I've understood that it's safe to execute against datanode on higher
 >> version of Postgres-XC 1.0.1.
 >> Is this right?
 >>
 > Yes. The read queries are made MVCC-safe when launched directly to
 > Datanodes, so you can have a direct access to the statistics of datanodes
 > by directly connecting to them or monitor them with things like 
Nagios. But
 > you have to be aware that write queries are not blocked in 1.0.X releases
 > when launched directly to Datanodes and this is extremely dangerous 
as you
 > might break data consistency in cluster. Use only read queries!
 >
Alright.
I'll be careful of breaking data consistency.

 > The code on master is made safer and blocks write queries when an
 > application connects to a Datanode that tries to launch a query that may
 > change Database, like a Postgres HOT standby would do.
 >
It's good news.
I'll check the master code.

Thank you again.

regards,
--------
Tomonari Katsumata

Re: [Postgres-xc-general] Question about executing queries against Datanode.

[Michael P. <mic...@gm...>]

On Thu, Oct 11, 2012 at 9:12 PM, Tomonari Katsumata <
kat...@po...> wrote:

> Hi,
>
> When I use Postgres-XC 1.0.0 and execute a query against datanode,
> I get below WARNING messages.
> ------------------------------------
> Do not have a GTM snapshot available
> ------------------------------------
>
> When I use Postgres-XC 1.0.1 and execute a query against datanode,
> I don't any WARNING messages.
>
> I think it's because of below changes.
> ---
> ref.)Release Note
> - Fix snapshot management on slave nodes and on Datanodes accessed
> directly by applications (Michael Paquier)
>

> A slave node session acquires its snapshot directly from WAL like
> PostgreSQL does. Also, a Datanode is now able to get a global snapshot
> directly from GTM, avoiding WARNING messages that a snapshot is not
> available.
>
> Queries interacting with remote nodes running on slave nodes are blocked.
> Such queries need a global snapshot and a new transaction ID, but
> sessions on
> slave nodes cannot acquire that.
> ---
>
> I've understood that it's safe to execute against datanode on higher
> version of Postgres-XC 1.0.1.
> Is this right?
>
Yes. The read queries are made MVCC-safe when launched directly to
Datanodes, so you can have a direct access to the statistics of datanodes
by directly connecting to them or monitor them with things like Nagios. But
you have to be aware that write queries are not blocked in 1.0.X releases
when launched directly to Datanodes and this is extremely dangerous as you
might break data consistency in cluster. Use only read queries!

The code on master is made safer and blocks write queries when an
application connects to a Datanode that tries to launch a query that may
change Database, like a Postgres HOT standby would do.
-- 
Michael Paquier
http://michael.otacoo.com

[Postgres-xc-general] Question about executing queries against Datanode.

[Tomonari K. <kat...@po...>]

Hi,

When I use Postgres-XC 1.0.0 and execute a query against datanode,
I get below WARNING messages.
------------------------------------
Do not have a GTM snapshot available
------------------------------------

When I use Postgres-XC 1.0.1 and execute a query against datanode,
I don't any WARNING messages.

I think it's because of below changes.
---
ref.)Release Note
- Fix snapshot management on slave nodes and on Datanodes accessed
directly by applications (Michael Paquier)

A slave node session acquires its snapshot directly from WAL like
PostgreSQL does. Also, a Datanode is now able to get a global snapshot
directly from GTM, avoiding WARNING messages that a snapshot is not
available.

Queries interacting with remote nodes running on slave nodes are blocked.
Such queries need a global snapshot and a new transaction ID, but
sessions on
slave nodes cannot acquire that.
---

I've understood that it's safe to execute against datanode on higher
version of Postgres-XC 1.0.1.
Is this right?

---------
Tomonari Katsumata

Re: [Postgres-xc-general] INSERT.. RETURNING not supported?

[Michael P. <mic...@gm...>]

On Wed, Oct 3, 2012 at 2:27 AM, Paulo Pires <pj...@ub...> wrote:

>
>
> On Tue Sep 18 11:21:21 2012, Michael Paquier wrote:
> >
> >
> > On Tue, Sep 18, 2012 at 5:43 PM, Paulo Pires <pj...@ub...
> > <mailto:pj...@ub...>> wrote:
> >
> >     Hi Michael,
> >
> >     Thanks so much for your support.
> >
> >     Could you tell me if I should follow postgres-xc-developers@ or
> >     postgres-xc-committers in order to be notified as soon as
> >     RETURNING support is implemented?
> >
> > Sure.
> > - The mailing list developper is used for conversations about features
> > and patches, so you will be notified about the commit in GIT with that.
> > - The commit mailing list is a mailing list where an email is sent
> > automatically where there is a commit in GIT. So you should follow it.
> >
> > You can also follow this twitter bot: https://twitter.com/PostgresXCBot.
> > This bot sends a tweet automatically when a commit is made with the
> > header of the commit.
> > You can also see the list of commits on Github:
> > https://github.com/postgres-xc/postgres-xc/commits/master
>
> Last updates are from Sep 16th, so I think you don't make any "beta"
> available. Am I wrong?
>
Well, I am working on the implementation of triggers, which is not that
straightforward.
So it requires time. The next beta of XC is planned for March or April.


>
> >
> > I also don't mind dropping an email on the thread where you discussed
> > about RETURNING on general mailing list once the stuff is committed.
>
> That would be awesome. I'm really needing this to continue my tests.
>
Regarding RETURNING, Abbas is working on that.
He just needs more time to stabilize his patch as he is cleaning a lot of
things, including things planner for remote query generation.
-- 
Michael Paquier
http://michael.otacoo.com

Re: [Postgres-xc-general] coordinator deaths

[Shavais Z. <sh...@gm...>]

Hello,

This looks like it has a lot of helpful stuff in it, it can dissipate a
fair amount of fogginess by providing an example.

One of the things I'll want to do is create a script in /etc/init.d that I
can use with chkconfig, so that things shutdown and start up cleanly on
server reboot.  It seems like it would be nice to have a configuration file
under /etc/config.d, or in /etc/postgresql-xc or something, that the init.d
script would read in order to know everything it needed to know, like
whether or not a gtm is present, and what coordinators and datanodes are
present and where their data directories are, and what their names and
ports are and such.


Shavais

On Tue, Sep 18, 2012 at 6:28 PM, Koichi Suzuki <ko...@in...>wrote:

> Hi,
>
> Maybe coordinator or datanode is trying to register itself doubly to gtm.
>
> To solve this problem, please try the following:
>
> 1. Stop gtm, coordinator and datanode,
> 2. Visit gtm's work directory (you should have specified by -D option with
> gtm_ctl)
> 3. Rename the file register.node to something else
> 4. Restart everything.
>
> BTW, I've posted bash script pgxclocal which takes care of XC
> installation, start and stop for local installation just for test.
>
> This is available from https://github.com/koichi-szk/PGXC-Tools.git
> Current version is attached.
>
> This will help to configure XC in local environment.   The script consists
> of two coordinators and two datanodes.   You can rewrite this section as
> you like.   Also, this comes with  a set of functions which shows what you
> should do to run your XC cluster.
>
> Good luck;
> ---
> Koichi Suzuki
> # I'm rewriting this into general Postgres-XC operating utility called
> pgxc_ctl.
>
> On Tue, 18 Sep 2012 12:51:57 -0700
> Roger Mayes <rog...@gm...> wrote:
>
> > Hello,
> >
> > I'm new to pg-xc, I'm just working on getting off the ground using pg-xc
> > 1.0.1 as the back end for a new project.  I would've searched prior posts
> > for similar ones before posting, but I don't see any way to do that?
> >
> > I'm using CentOS 6.3, I've used the "short version" installation to set
> up
> > a gtm, a single coordinator, and a single data node on my local host.
>  Here
> > are the contents of my pgxc_node table:
> >
> > -bash-4.1$ psql -U postgres
> > psql (PGXC 1.0.1, based on PG 9.1.5)
> > Type "help" for help.
> > postgres=# select * from pgxc_node;
> >  node_name | node_type | node_port | node_host | nodeis_primary |
> > nodeis_preferred |  node_id
> >
> -----------+-----------+-----------+-----------+----------------+------------------+------------
> >  coord1    | C         |      5432 | localhost | f              | f
> >        | 1885696643
> >  datanode1 | D         |     15432 | localhost | t              | t
> >        |  888802358
> >
> >
> > The problem I'm having is that after maybe 60 to 90 seconds or so after
> > starting, the coordinator dies with a log message like
> >
> > FATAL:  Can not register Coordinator on GTM
> >
> > The gtm is definitely running, I can see it in the process list.  The
> only
> > changes to the default postgresql.conf's I've made is to specify the port
> > of 15432 for datanode1, and (as part of an effort to fix this problem)
> > uncomment the host ('localhost') and port (6666) of the gtm in all 3
> > postgresql.conf files.
> >
> > Another thing I did to try to fix this was:
> >
> > alter node datanode1 with (primary = true, preferred = true);
> >
> > As you can see, that has affected the pgxc_node contents.  But that
> didn't
> > have any impact on this crashing behavior.
> >
> > I tried starting using the method presented in the short version install
> to
> > start everything, and I also tried starting up like this:
> >
> > gtm_ctl -Z gtm -D data_gtm -l gtm.log start
> > sleep 1
> > pg_ctl -Z coordinator -D data_coord1 -l coord1.log start
> > sleep 1
> > pg_ctl -Z datanode -D data_datanode1 -l datanode1.log start
> >
> > ..from the /usr/local/pgsql directory.  (I also tried specifying absolute
> > paths.)
> >
> > I've tried using kill to clear everything out of the process table, I've
> > tried making sure the last shutdown was clean for everything, using
> pg_ctl
> > and gtm_ctl to shutdown, and then starting up fresh from having shutdown
> > cleanly.
> >
> > SELinux is disabled, the local firewall is disabled.
> >
> > Everything works for about 60 to 90 seconds, then the coordinator dies
> with
> > the message above.  Any help would be greatly appreciated,
> >
> > Shavais
>
>
> ------------------------------------------------------------------------------
> 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-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>
>

Re: [Postgres-xc-general] coordinator deaths

[Shavais Z. <sh...@gm...>]

When you want to add a port number with pg_ctl, be sure to use quotes with
> -o, like:
> -o "-p $PORT"
>
> The XC documentation is not perfect, if you could point to the page and
> the paragraph where you had doubts I can try to have a look and correct the
> doc if necessary.
>

After looking at the docs of alternatives to XC that I've looked at, I
appreciate the XC docs a lot.

The pg_ctl --help screen says  "-o OPTIONS".  I feel a bit dumb for not
considering that the options probably need to be in quotes.


>
>
>> The pgbench utility wasn't installed, but I finally found it and
>> installed it.  I notice that it somehow left a bunch of idle connection
>> processes behind after exiting.  Would those get reused, or is that a
>> problem with pgbench?  (I restarted postgres-xc and they went away, but.
>>  Just wondering.)
>>
> The connection pooling of XC is made to maintain connections between nodes
> as it consumes a bunch of ressources to create connections on the fly for
> each transaction.
> You can however drop those connections with CLEAN CONNECTION, command
> specific to XC:
> http://postgres-xc.sourceforge.net/docs/1_0/sql-cleanconnection.html
>
>
Nice..

Re: [Postgres-xc-general] coordinator deaths

[Michael P. <mic...@gm...>]

On Wed, Sep 19, 2012 at 10:12 AM, Shavais Zarathustra <sh...@gm...>wrote:

> Hello,
>
> I've been using a computer on which gmail was already logged in.  I
> mistakenly assumed it was logged into my account.  Sorry about that.
>
Don't worry :)

>
> The pgxc_node_name setting was set to 'coord1' and 'datanode1' in
> data_coord1/postgresql.conf and data_datanode1/postgersql.conf,
> respectively.
>
OK, nice.

>
> I reinitialized the install, as you suggested (except I just shut
> everything down, blew away the data_* folders in /usr/local/pgsql, and
> reused those instead of using $HOME/pgsql/data_*), and, so far, that seems
> to have fixed the problem.  (I guess probably those data_* folders should
> be under /var somewhere..)
>
> I'm not sure what the problem was.  I think I recall having attempted to
> issue a sql command to create a coordinator node, the previous time around.
>  I notice your steps don't include that step?  I wonder if somehow that
> affected things?
>
When you initialized a node with initdb, --nodename is a mandatory option.
This option value is used to set up pgxc_node_name automatically and
initialize the catalog pgxc_node with the data of the node itself.
You need to create a Coordinator with CREATE NODE on a Coordinator only if
there is at least 2 Coordinators in your cluster.


> The reason I had set the port in datanode1's postgresql.conf was because I
> would like to use gtm_ctl and pg_ctl to start up and shutdown, using an
> init.d script, (ala service postgresql-xc start / restart / stop) and I
> haven't been able to get pg_ctl to pass the port parameter to postgres.
> (pg_ctl ... -o -p 15432 doesn't work; apparently I'm misinterpreting the
> usage docs).  After putting that setting back in again, I'm using gtm_ctl
> and pg_ctl to start and stop it all at this point, and it seems to working
> fine.
>
Nice to hear that it is working. I am sure you missed something at
initialization.
When you want to add a port number with pg_ctl, be sure to use quotes with
-o, like:
-o "-p $PORT"

The XC documentation is not perfect, if you could point to the page and the
paragraph where you had doubts I can try to have a look and correct the doc
if necessary.


> The pgbench utility wasn't installed, but I finally found it and installed
> it.  I notice that it somehow left a bunch of idle connection processes
> behind after exiting.  Would those get reused, or is that a problem with
> pgbench?  (I restarted postgres-xc and they went away, but.  Just
> wondering.)
>
The connection pooling of XC is made to maintain connections between nodes
as it consumes a bunch of ressources to create connections on the fly for
each transaction.
You can however drop those connections with CLEAN CONNECTION, command
specific to XC:
http://postgres-xc.sourceforge.net/docs/1_0/sql-cleanconnection.html
-- 
Michael Paquier
http://michael.otacoo.com