There are other techniques to balance the load of the database calls so that
some go to one box and some to others, yet keep the data in synch...
Continuent makes a commercial p/cluster product as well as an open source
product called Sequoia that sit in the JDBC layer and direct traffic and
control the load balancing.
We use continuent at work (albeit for mysql...) on a three node cluster.
In their default setup they use what's called "strict" querying. By which
they mean any read to any node will first check to make sure there aren't
any pending writes before continuing.
The other mode is "weak". Where reads don't bother checking to see if
there are any pending writes before doing their read. In this mode, we
run the risk that a read might happen before a previous write it's
depending on. In our tests though the time for replication was really
small (don't remember offhand) but certainly much much smaller than a
round trip request for an end user would be.
That all said, we had to go with weak because strict killed performance.
Absolutely killed it.
Anyway, just something to keep in mind. Kind of like raid... you lose in
one area to gain in another....
-philip
Jeff Amiel
Leonardo Francalanci wrote:
Hi,
I still don't understand how replication can be used in web applications.
Given this scenario:
1) user updates his profile -> update to the db (master)
2) web app redirects to the "profile page" -> select from db (slave)
Since (2) is a select it is issued to the slave.
How can one be sure that the master propagates the update (1) to the slave
before data is requested from the slave (2)?
And: suppose there is a method to understand that the user made a change to
the db in the web request (as above) so that we have to issue all queries
of the same web request to the master, that is:
1) user updates his profile -> update to the db (master)
2) web app redirects to the "profile page" -> select from db (master again
because in this web-request user made a change to the db)
what if the user ask AGAIN for the "profile page" BEFORE write propagates
to the slave:
3) User ask for a refresh of the "profile page" -> select -> slave (because
user didn't make any change during THIS web request)
???
In other words: how can asynchronous replication be used in an
application???
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
