Is Synchronous Postgresql Replication Slower Than Asynchronous?
Is synchronous postgresql replication slower than asynchronous? If so, how much? I am looking into database replication for a phone system, so the response time is of concern.
Thanks,
Jerry
On 01/20/12 12:31 PM, Jerry Richards wrote:
Is synchronous postgresql replication slower than asynchronous? If
so, how much? I am looking into database replication for a phone
system, so the response time is of concern.
when a client issues a COMMIT on the master, synchronous by definition
has to wait for the replication to be committed on the slave. this will
of course be slower, how much slower depends on far too many variables
to give an estimate, including things like speed of both servers disk
systems, speed of the network communications, data volume and
transaction rate.
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
On 20 January 2012 21:17, John R Pierce <pierce@hogranch.com> wrote:
On 01/20/12 12:31 PM, Jerry Richards wrote:
Is synchronous postgresql replication slower than asynchronous? If so,
how much? I am looking into database replication for a phone system, so the
response time is of concern.when a client issues a COMMIT on the master, synchronous by definition has
to wait for the replication to be committed on the slave. this will of
course be slower, how much slower depends on far too many variables to give
an estimate, including things like speed of both servers disk systems, speed
of the network communications, data volume and transaction rate.
Synchronous replication is, by its very nature, slower than
asynchronous replication, and that has nothing to do with the
PostgreSQL implementation in particular. The whole point of using it
is to ensure that transactions have an additional level of durability.
The master must wait for confirmation from the standby before
indicating the transaction committed, so latency/distance becomes a
real concern.
Uniquely, the PostgreSQL implementation doesn't require that all
transactions be synchronous or asynchronous; you can dynamically
change that right down to the transaction level (or the client, or the
database...). You might like to make really important transactions
alone be replicated synchronously for super-durability. Many
applications that use this feature only use it for a small though
critical subset of transactions, like financial transactions.
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
Peter,
I noticed there are several synchronous implementations (Postgre-XC, PGCluster, pgpool, rubyrep, built-in streaming, etc.). When you say, " you can dynamically change that right down to the transaction level...", are you referring specifically to one of these implementations?
By the way
Thanks,
Jerry
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Peter Geoghegan
Sent: Friday, January 20, 2012 1:37 PM
To: John R Pierce
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Is Synchronous Postgresql Replication Slower Than Asynchronous?
On 20 January 2012 21:17, John R Pierce <pierce@hogranch.com> wrote:
On 01/20/12 12:31 PM, Jerry Richards wrote:
Is synchronous postgresql replication slower than asynchronous? If
so, how much? I am looking into database replication for a phone
system, so the response time is of concern.when a client issues a COMMIT on the master, synchronous by definition
has to wait for the replication to be committed on the slave. this
will of course be slower, how much slower depends on far too many
variables to give an estimate, including things like speed of both
servers disk systems, speed of the network communications, data volume and transaction rate.
Synchronous replication is, by its very nature, slower than asynchronous replication, and that has nothing to do with the PostgreSQL implementation in particular. The whole point of using it is to ensure that transactions have an additional level of durability.
The master must wait for confirmation from the standby before indicating the transaction committed, so latency/distance becomes a real concern.
Uniquely, the PostgreSQL implementation doesn't require that all transactions be synchronous or asynchronous; you can dynamically change that right down to the transaction level (or the client, or the database...). You might like to make really important transactions alone be replicated synchronously for super-durability. Many applications that use this feature only use it for a small though critical subset of transactions, like financial transactions.
--
Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 20 January 2012 21:53, Jerry Richards <jerry.richards@teotech.com> wrote:
I noticed there are several synchronous implementations (Postgre-XC, PGCluster, pgpool, rubyrep, built-in streaming, etc.). When you say, " you can dynamically change that right down to the transaction level...", are you referring specifically to one of these implementations?
I refer specifically to the synchronous replication feature that was
introduced into PostgreSQL itself, in the current 9.1 release:
http://www.postgresql.org/docs/9.1/static/warm-standby.html#SYNCHRONOUS-REPLICATION
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
On Sat, Jan 21, 2012 at 3:31 AM, Jerry Richards
<jerry.richards@teotech.com> wrote:
Is synchronous postgresql replication slower than asynchronous? If so, how
much? I am looking into database replication for a phone system, so the
response time is of concern.
You might want to investigate pgpool-ii. It sits as a proxy between
the client and the databases, and as queries are executed
simultaneously, a synchronous replication setup should be just as fast
as an unreplicated setup.
--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/
On Mon, Jan 23, 2012 at 2:30 PM, Stuart Bishop <stuart@stuartbishop.net> wrote:
On Sat, Jan 21, 2012 at 3:31 AM, Jerry Richards
<jerry.richards@teotech.com> wrote:Is synchronous postgresql replication slower than asynchronous? If so, how
much? I am looking into database replication for a phone system, so the
response time is of concern.You might want to investigate pgpool-ii. It sits as a proxy between
the client and the databases, and as queries are executed
simultaneously, a synchronous replication setup should be just as fast
as an unreplicated setup.
Can you share your actual results on that?
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On Mon, Jan 23, 2012 at 9:37 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
On Mon, Jan 23, 2012 at 2:30 PM, Stuart Bishop <stuart@stuartbishop.net> wrote:
On Sat, Jan 21, 2012 at 3:31 AM, Jerry Richards
<jerry.richards@teotech.com> wrote:Is synchronous postgresql replication slower than asynchronous? If so, how
much? I am looking into database replication for a phone system, so the
response time is of concern.You might want to investigate pgpool-ii. It sits as a proxy between
the client and the databases, and as queries are executed
simultaneously, a synchronous replication setup should be just as fast
as an unreplicated setup.Can you share your actual results on that?
No. This is based on my assumptions from the design, not from actual
tests. I'm currently asynchronously replicated with Slony-I and
looking at PG 9.1 builtin replication for our simpler clusters.
--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/
On Mon, Jan 23, 2012 at 3:13 PM, Stuart Bishop <stuart@stuartbishop.net> wrote:
On Mon, Jan 23, 2012 at 9:37 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
On Mon, Jan 23, 2012 at 2:30 PM, Stuart Bishop <stuart@stuartbishop.net> wrote:
On Sat, Jan 21, 2012 at 3:31 AM, Jerry Richards
<jerry.richards@teotech.com> wrote:Is synchronous postgresql replication slower than asynchronous? If so, how
much? I am looking into database replication for a phone system, so the
response time is of concern.You might want to investigate pgpool-ii. It sits as a proxy between
the client and the databases, and as queries are executed
simultaneously, a synchronous replication setup should be just as fast
as an unreplicated setup.Can you share your actual results on that?
No. This is based on my assumptions from the design, not from actual
tests. I'm currently asynchronously replicated with Slony-I and
looking at PG 9.1 builtin replication for our simpler clusters.
Sync rep 9.1 allows you to have >2 servers involved, which is really
necessary for availability and robustness.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services