Обсуждение: 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 specificallyto 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 thePostgreSQL implementation in particular. The whole point of using it is to ensure that transactions have an additionallevel of durability. The master must wait for confirmation from the standby before indicating the transaction committed, so latency/distance becomesa real concern. Uniquely, the PostgreSQL implementation doesn't require that all transactions be synchronous or asynchronous; you can dynamicallychange that right down to the transaction level (or the client, or the database...). You might like to make reallyimportant transactions alone be replicated synchronously for super-durability. Many applications that use this featureonly 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 specificallyto 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