Обсуждение: postgresql scalability, active-active cluster

Are there any built in tools or 3rd party tools for distributing a postgresql database?  I need an active active configuration; master-master with fail over.  The project I am working needs to support a very large number of transactions a second. It will eventually require a main frame, or some absurd hardware.  It makes much more sense to consider a clustered configuration.  DB requests come in from a row of application servers.  It would be nice if these requests could be distributed.

thanks,
skye

---

Food fight? Enjoy some healthy debate
in the Yahoo! Answers Food & Drink Q&A.

On Sun, Jan 21, 2007 at 06:55:56AM -0800, brian stone wrote:
> Are there any built in tools or 3rd party tools for distributing a postgresql database?  I need an active active
configuration;master-master with fail over.  The project I am working needs to support a very large number of
transactionsa second. It will eventually require a main frame, or some absurd hardware.  It makes much more sense to
considera clustered configuration.  DB requests come in from a row of application servers.  It would be nice if these
requestscould be distributed. 

Take a look at Postgres-R or Continuent (which is commercial, but they
have an open-source version).
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

On Sun, 2007-01-21 at 06:55 -0800, brian stone wrote:
> Are there any built in tools or 3rd party tools for distributing a
> postgresql database?  I need an active active configuration; master-
> master with fail over.  The project I am working needs to support a
> very large number of transactions a second. It will eventually require
> a main frame, or some absurd hardware.  It makes much more sense to
> consider a clustered configuration.  DB requests come in from a row of
> application servers.  It would be nice if these requests could be
> distributed.
>

Consider pgpool, and look at the partitioning feature (which uses
different machines for different records, allowing writes to happen very
quickly).

Also, depending on what your needs are, a relational database might not
be right for you. You say you need transactions, but if you're comparing
to MySQL's master-master, I don't think that even supports ACID
transactions. Do you actually need transactions, or just many writes per
second (perhaps to memory rather than disk)?

Regards,
    Jeff Davis

I never considered MySQL because I really DO need transactions.  MySQL also lacks many enterprise features we need; well they say they have them but from my testing they are a bit under-cooked.

I need atomic actions across an N number of application servers.  The goal here is scalability, which is why I brought up clustering.  Unfortunately, we really do need those transactions :( 

Thought has gone into solutions other than a relational database but they all ended up at the same place ... we started to design a database.  Basically, we need the features of a relational database so other solutions started to look like one. 

thanks for the help,
skye


Jeff Davis <pgsql@j-davis.com> wrote:

On Sun, 2007-01-21 at 06:55 -0800, brian stone wrote:
> Are there any built in tools or 3rd party tools for distributing a
> postgresql database? I need an active active configuration; master-
> master with fail over. The project I am working needs to support a
> very large number of transactions a second. It will eventually require
> a main frame, or some absurd hardware. It makes much more sense to
> consider a clustered configuration. DB requests come in from a row of
> application servers. It would be nice if these requests could be
> distributed.
>

Consider pgpool, and look at the partitioning feature (which uses
different machines for different records, allowing writes to happen very
quickly).

Also, depending on what your needs are, a relational database might not
be right for you. You say you need transactions, but if you're comparing
to MySQL's master-master, I don't think that even supports ACID
transactions. Do you actually need transactions, or just many writes per
second (perhaps to memory rather than disk)?

Regards,
Jeff Davis

---

Need a quick answer? Get one in minutes from people who know. Ask your question on Yahoo! Answers.

brian stone wrote:
> I never considered MySQL because I really DO need transactions.  MySQL
> also lacks many enterprise features we need; well they say they have
> them but from my testing they are a bit under-cooked.
>
> I need atomic actions across an N number of application servers.  The
> goal here is scalability, which is why I brought up clustering.
> Unfortunately, we really do need those transactions :(

Maybe two-phase commit can help you here?  (see the COMMIT PREPARED
docs)

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Mon, 2007-01-22 at 17:37 -0300, Alvaro Herrera wrote:
> brian stone wrote:
> > I never considered MySQL because I really DO need transactions.  MySQL
> > also lacks many enterprise features we need; well they say they have
> > them but from my testing they are a bit under-cooked.
> >
> > I need atomic actions across an N number of application servers.  The
> > goal here is scalability, which is why I brought up clustering.
> > Unfortunately, we really do need those transactions :(
>
> Maybe two-phase commit can help you here?  (see the COMMIT PREPARED
> docs)
>

How much of a concern are deadlocks?

Is the correct way to do this to serially execute all the statements on
Node1, then Node2, ... NodeN (in order) to avoid deadlocks?

If you used async execution on all nodes at once, and then re-issued any
deadlocked transactions, how rare would deadlocks need to be for that to
be faster?

Regards,
    Jeff Davis