Обсуждение: Scalable cluster
I'm looking into setting up an HA scalable DB cluster.
So far my tests with streaming replication proof that it is very very good indeed.
However, problem seems to be on the connection pooling side. Ideally, we would love to have single point of connection to the cluster, but I do realise that it might not be feasible.
So far I've been testing pgpool-II 3.2.3 and 3 DB servers. And as much as I'm impressed by postgresql itself. pgpool simply fails on pretty much every front. That is, in terms of scalability, running dbbench against it, proves to be much slower then direct connection to the master. It also goes into strange states when you overallocate connections, etc. Not really something I'd trust on a production server.
I don't know of any other pooling solution that would be capable of handling the job, but focus only on the task of pooling (pgpool's fault probably is that it is trying to be jack of all trades) in HA replicated scenario.
What is out there, free or paid - that would solve an HA DB Cluster running Postgresql (ideally 9.2), that you guys could suggest ?
Personally, I think that having single connection point DB Cluster is not going to be ideal solution anyway. So question is, how would you guys go about designing a cluster that handles Java/C/C++ applications connecting from some number of servers ?
It has scale (adding more servers, to improve performance, or aid in case storage has become a problem), but also be redundant in case hardware fails.
Thanks.
GJ
On 3/3/2013 1:57 PM, Gregg Jaskiewicz wrote: > I don't know of any other pooling solution that would be capable of > handling the job, but focus only on the task of pooling (pgpool's > fault probably is that it is trying to be jack of all trades) in HA > replicated scenario. did you look at pgbouncer ? thats the simple pooler for postgres, and its quite robust, because its so simple. -- john r pierce 37N 122W somewhere on the middle of the left coast
On 3 March 2013 22:56, John R Pierce <pierce@hogranch.com> wrote:
did you look at pgbouncer ? thats the simple pooler for postgres, and its quite robust, because its so simple.
Yes, it is one of the solutions I do consider. Having applications decide whether they should write to master, or use slaves and/or master for read queries (for instance in case it is a transaction, etc).
I wonder however, how others are handing it. There seems to be nothing out there apart from pgbouncer and pgpool. And only the latter can handle (albeit not really that quick) pooling between master and slaves.
How do you guys go about designing such cluster.
GJ
Le dimanche 03 mars 2013 à 23:29 +0000, Gregg Jaskiewicz a écrit : > > > I wonder however, how others are handing it. There seems to be nothing > out there apart from pgbouncer and pgpool. And only the latter can > handle (albeit not really that quick) pooling between master and > slaves. > > > How do you guys go about designing such cluster. > There is this : http://wiki.postgresql.org/wiki/Postgres-XC [Disclaimer : I can't tell whether it's usable or not; I just know they exist] -- Salutations, Vincent Veyron http://marica.fr/site/demonstration Progiciel de gestion des sinistres assurance et des dossiers contentieux pour le service juridique
On 4 Mar 2013, at 13:21, Vincent Veyron <vv.lists@wanadoo.fr> wrote: > > There is this : > > http://wiki.postgresql.org/wiki/Postgres-XC > > > [Disclaimer : I can't tell whether it's usable or not; I just know they > exist] > Well, I know of its existence too. Question is how production ready is it. And also how far off the 9.2 line is it.
Version 1.1 will be out in this June time frame, with online node addition/removal, Trigger and improved planner. I'm more than happy if you evaluate XC. Regards; ---------- Koichi Suzuki 2013/3/4 Greg Jaskiewicz <gryzman@gmail.com>: > > On 4 Mar 2013, at 13:21, Vincent Veyron <vv.lists@wanadoo.fr> wrote: > >> >> There is this : >> >> http://wiki.postgresql.org/wiki/Postgres-XC >> >> >> [Disclaimer : I can't tell whether it's usable or not; I just know they >> exist] >> > > > Well, I know of its existence too. Question is how production ready is it. And also how far off the 9.2 line is it. > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
One question about Postgres-XC, can it distribute one single query in all nodes ? Example, Nodes: Node1,Node2,Node3 Query:"SELECT sum(foo) FROM bar group by foo" (Obvious: Your data has to be shared between the nodes) Query -> Compiler (in the pool)-> "share the query between the nodes" -> merge -> return I know tools to do it, but don't have full implementation like Netezza. Thank you. On 4 March 2013 13:36, Koichi Suzuki <koichi.szk@gmail.com> wrote: > Version 1.1 will be out in this June time frame, with online node > addition/removal, Trigger and improved planner. I'm more than happy > if you evaluate XC. > > Regards; > ---------- > Koichi Suzuki > > > 2013/3/4 Greg Jaskiewicz <gryzman@gmail.com>: >> >> On 4 Mar 2013, at 13:21, Vincent Veyron <vv.lists@wanadoo.fr> wrote: >> >>> >>> There is this : >>> >>> http://wiki.postgresql.org/wiki/Postgres-XC >>> >>> >>> [Disclaimer : I can't tell whether it's usable or not; I just know they >>> exist] >>> >> >> >> Well, I know of its existence too. Question is how production ready is it. And also how far off the 9.2 line is it. >> >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- -dom -- IBM - Business Analytics Optimization Consultant Daniel Mantovani +5511 8538-9897 XOXO
just fixing: like Netezza which has full implementation to do it.* On 4 March 2013 14:59, Daniel de Oliveira Mantovani <daniel.oliveira.mantovani@gmail.com> wrote: > One question about Postgres-XC, can it distribute one single query in > all nodes ? > > Example, > Nodes: Node1,Node2,Node3 > Query:"SELECT sum(foo) FROM bar group by foo" > > (Obvious: Your data has to be shared between the nodes) > > Query -> Compiler (in the pool)-> "share the query between the nodes" > -> merge -> return > > I know tools to do it, but don't have full implementation like Netezza. > > Thank you. > > On 4 March 2013 13:36, Koichi Suzuki <koichi.szk@gmail.com> wrote: >> Version 1.1 will be out in this June time frame, with online node >> addition/removal, Trigger and improved planner. I'm more than happy >> if you evaluate XC. >> >> Regards; >> ---------- >> Koichi Suzuki >> >> >> 2013/3/4 Greg Jaskiewicz <gryzman@gmail.com>: >>> >>> On 4 Mar 2013, at 13:21, Vincent Veyron <vv.lists@wanadoo.fr> wrote: >>> >>>> >>>> There is this : >>>> >>>> http://wiki.postgresql.org/wiki/Postgres-XC >>>> >>>> >>>> [Disclaimer : I can't tell whether it's usable or not; I just know they >>>> exist] >>>> >>> >>> >>> Well, I know of its existence too. Question is how production ready is it. And also how far off the 9.2 line is it. >>> >>> >>> >>> -- >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > > > -- > > -dom > > -- > > IBM - Business Analytics Optimization Consultant > Daniel Mantovani +5511 8538-9897 > XOXO -- -dom -- IBM - Business Analytics Optimization Consultant Daniel Mantovani +5511 8538-9897 XOXO
On Mon, Mar 4, 2013 at 1:01 PM, Daniel de Oliveira Mantovani <daniel.oliveira.mantovani@gmail.com> wrote:
StormDB
The Database Cloud
just fixing:
like Netezza which has full implementation to do it.*
On 4 March 2013 14:59, Daniel de Oliveira Mantovani<daniel.oliveira.mantovani@gmail.com> wrote:
> One question about Postgres-XC, can it distribute one single query in
> all nodes ?
>
> Example,
> Nodes: Node1,Node2,Node3
> Query:"SELECT sum(foo) FROM bar group by foo"
XC handles queries like this in parallel to a certain extent.
A plain old SELECT SUM(foo) FROM bar will get the sum on all of the nodes, then sum it up again at the coordinator.
Depending on your table distribution scheme, XC will do a pretty good job at pushing down joins so that they occur locally. For example, one table is distributed, and a second is replicated on all nodes. Any join between the two tables can occur locally on each data node in parallel.
You will experience slow query times if you have to join data from one node with data from another node. It all gets shipped to the coordinator for joining.
If you are after query parallelism for a data warehouse and want to be PostgreSQL-based, then I recommend a project called Stado. If you are looking for mainly write scalability where the workload is largely singleton type of statements, then I recommend Postgres-XC.
Yet another solution you can consider is PL/Proxy, which was developed by Skype.
>
> (Obvious: Your data has to be shared between the nodes)
>
> Query -> Compiler (in the pool)-> "share the query between the nodes"
> -> merge -> return
>
> I know tools to do it, but don't have full implementation like Netezza.
>
> Thank you.
>
> On 4 March 2013 13:36, Koichi Suzuki <koichi.szk@gmail.com> wrote:
>> Version 1.1 will be out in this June time frame, with online node
>> addition/removal, Trigger and improved planner. I'm more than happy
>> if you evaluate XC.
>>
>> Regards;
>> ----------
>> Koichi Suzuki
>>
>>
>> 2013/3/4 Greg Jaskiewicz <gryzman@gmail.com>:
>>>
>>> On 4 Mar 2013, at 13:21, Vincent Veyron <vv.lists@wanadoo.fr> wrote:
>>>
>>>>
>>>> There is this :
>>>>
>>>> http://wiki.postgresql.org/wiki/Postgres-XC
>>>>
>>>>
>>>> [Disclaimer : I can't tell whether it's usable or not; I just know they
>>>> exist]
>>>>
>>>
>>>
>>> Well, I know of its existence too. Question is how production ready is it. And also how far off the 9.2 line is it.
>>>
>>>
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
> --
>
> -dom
>
> --
>
> IBM - Business Analytics Optimization Consultant
> Daniel Mantovani +5511 8538-9897
> XOXO
--
-dom
--
IBM - Business Analytics Optimization Consultant
Daniel Mantovani +5511 8538-9897
XOXO
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Mason SharpStormDB
The Database Cloud
Postgres-XC Support and Services