Query Broadcast Load Balancing

From: Bruce Momjian <bruce@momjian.us>
Message-Id: <200611150110.kAF1ASa11108@momjian.us>
Subject: Re: [HACKERS] Replication documentation addition
In-Reply-To: 
 <Pine.LNX.4.64.0611141613010.23695@discord.home.frostconsultingllc.com>
To: Jeff Frost <jeff@frostconsultingllc.com>
Date: Tue, 14 Nov 2006 20:10:28 -0500 (EST)
CC: Chris Browne <cbbrowne@acm.org>, pgsql-docs@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="ELM1163553028-23851-0_"
Content-Transfer-Encoding: 7bit


--ELM1163553028-23851-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="US-ASCII"

Jeff Frost wrote:
> On Tue, 14 Nov 2006, Bruce Momjian wrote:
> 
> >> "In clustering, each server can accept write requests, and these write
> >> requests are broadcast from the original server to all other servers before
> >> each transaction commits."
> >>
> >> Unfortunately, I can't seem to come up with anything more clever.
> >
> > Basically, when you are broadcasting outside the server, you are
> > broadcasting SQL queries, and those queries do not have information
> > about non-deterministic functions and have issues with universal commits
> > on all node.
> 
> Ahh..I like this explanation, because the inter-server communication in 
> clustering is not necessarily SQL queries.

OK, I have updated the documentation with the attached patch, which
clarifies SQL broadcast vs. modified row propogation.  Current version
is at:

	http://momjian.us/main/writings/pgsql/sgml/failover.html

-- 
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

--ELM1163553028-23851-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/x-diff
Content-Disposition: inline; filename="/bjm/diff"

Index: doc/src/sgml/failover.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/failover.sgml,v
retrieving revision 1.5
diff -c -c -r1.5 failover.sgml
*** doc/src/sgml/failover.sgml	14 Nov 2006 22:25:15 -0000	1.5
--- doc/src/sgml/failover.sgml	15 Nov 2006 01:06:42 -0000
***************
*** 149,171 ****
    <title>Query Broadcast Load Balancing</title>
  
    <para>
!    Query broadcast load balancing is accomplished by having a program
!    intercept every query and send it to all servers.  Read-only queries can
!    be sent to a single server because there is no need for all servers to
!    process it.  This is unusual because most replication solutions have
!    each write server propagate its changes to the other servers.  With
!    query broadcasting, each server operates independently.
    </para>
  
    <para>
!    Because each server operates independently, functions like
     <function>random()</>, <function>CURRENT_TIMESTAMP</>, and
!    sequences can have different values on different servers.  If
!    this is unacceptable, applications must query such values from
!    a single server and then use those values in write queries.
!    Also, care must also be taken that all transactions either commit
!    or abort on all servers  Pgpool is an example of this type of
!    replication.
    </para>
   </sect1>
  
--- 149,173 ----
    <title>Query Broadcast Load Balancing</title>
  
    <para>
!    Query broadcast load balancing is accomplished by having a
!    program intercept every SQL query and send it to all servers.
!    This is unique because most replication solutions have the write
!    server propagate its changes to the other servers.  With query
!    broadcasting, each server operates independently.  Read-only
!    queries can be sent to a single server because there is no need
!    for all servers to process it.
    </para>
  
    <para>
!    One limitation of this solution is that functions like
     <function>random()</>, <function>CURRENT_TIMESTAMP</>, and
!    sequences can have different values on different servers.  This
!    is because each server operates independently, and because SQL
!    queries are broadcast (and not actual modified rows).  If this
!    is unacceptable, applications must query such values from a
!    single server and then use those values in write queries.  Also,
!    care must be taken that all transactions either commit or abort
!    on all servers  Pgpool is an example of this type of replication.
    </para>
   </sect1>
  
***************
*** 173,186 ****
    <title>Clustering For Load Balancing</title>
  
    <para>
!    In clustering, each server can accept write requests, and these
!    write requests are broadcast from the original server to all
!    other servers before each transaction commits.  Heavy write
!    activity can cause excessive locking, leading to poor performance.
!    In fact, write performance is often worse than that of a single
     server.  Read requests can be sent to any server.  Clustering
     is best for mostly read workloads, though its big advantage is
!    that any server can accept write requests --- there is no need
     to partition workloads between read/write and read-only servers.
    </para>
  
--- 175,188 ----
    <title>Clustering For Load Balancing</title>
  
    <para>
!    In clustering, each server can accept write requests, and modified
!    data is transmitted from the original server to every other
!    server before each transaction commits.  Heavy write activity
!    can cause excessive locking, leading to poor performance.  In
!    fact, write performance is often worse than that of a single
     server.  Read requests can be sent to any server.  Clustering
     is best for mostly read workloads, though its big advantage is
!    that any server can accept write requests &mdash; there is no need
     to partition workloads between read/write and read-only servers.
    </para>
  

--ELM1163553028-23851-0_--