We spent several months moving to a database replicated system. There
are MANY issues that make it more complicated than just a
dsconfig.ini type of setup, although it would seem that way.
The biggest issue, is latency for replication. There are some apps,
that need access to data, immediately after insert or update. For
this reason, you cannot just say, do all writes on DBMain, and all
reads on other(s). You have to either architect all your applications
to allow for this latency, or in our case, choose which functions of
your application(s) will use the replicated servers to read from.
The goal of replication is to gain performance by load balancing
interaction with a database, or to gain redundancy.
We accomplish both goals in completely different ways.
1. Redundancy: we can quickly switch all dsns on witango machines to
do all reads and writes to a slave database. This should only be done
in a complete failure type of scenario, cuz then you have serious re-
synchonization issues.
2. Performance: we went through our applications and chose the areas
that would not suffer from the latency issue, and and put a heavy
read load on the database server, and moved only those functions to
read from slaves. This works very well.
Being more specific, on eventpix, we moved all of the event/image
VIEWING to the slaves. This is the most used function of eventpix,
and is hammered constantly, and completely frees the main db server
up. Also, it is not an issue that a user looks for an event, and a
slave is a minute behind the main server.
Keep in mind, that you also have to incorporate that user session
into your equation.
So in a database, we store records for each slave, and an integer for
its priority. So if there are 3 slaves, db2, db3, and db4, with
prioritys of 100, and 100, and 200 respectively....
Then db4 would get 2x the action of db2 and db3, I think you get the
picture. When an server is started, these values are stored in a
global custom scope. When a user hits one of our balanced functions,
it calls a tcf method, to "checkout" a dsn. The method gives it the
dsn, and also knows to keep giving the SAME dsn to the same user. You
don't want to have a single user switching from one slave to the other.
If there is a failure in a slave, we can go to our custom server
administration console, and change the database entries of priority.
Say if db3 goes down, its priority is 0. Then we trip a function that
reloads this db data into the global scope. The tcf method in this
case WILL give the user process a new DSN, cuz db3 is now down. So
the tcf NEVER hits the DB for the values, only the global scope in
memory, so the tcf only adds about 1ms of latency to this entire
process. When db3 is back up, we change the values in the db, then
reload that data into the global scope, and all of the applications
do the right thing on the fly.
As a footnote, we have been absolutely blown away by the performance
of primebase in this type of situation. The main database is
accepting over 20k images a day, and replicating to 2 slaves, and the
slaves are never more than a few seconds behind, even during the most
trafficed periods of the day.
This works with DirectDBMS actions and regular search actions.
I hope that all makes sense, I am typing this while on a boring
support call...
Lastly, I did not make any feature requests for v6 because we are in
the process of moving everything to Zend/PHP and have no intention of
migrating to v6. The logic will be the same.
--
Robert Garcia
President - BigHead Technology
VP Application Development - eventpix.com
13653 West Park Dr
Magalia, Ca 95954
ph: 530.645.4040 x222 fax: 530.645.4040
[EMAIL PROTECTED] - [EMAIL PROTECTED]
http://bighead.net/ - http://eventpix.com/
On Jan 13, 2006, at 10:27 AM, William M Conlon wrote:
ok, I see how you would abstract this and replace DirectDBMS, for
example, with a method call specifying a DSN, query, and pass a
variable to receive the resultset. It seems like you also need to
tell the object whether it's a read or write query so it doesn't
have to parse the request.
Or maybe you only use this for the slaves (reading)? Because part
of the complexity is working around the inability of <@BIND> to
insert/update columns larger than 32k. So far I can only get this
to work with Insert/Update actions.
Since you've implemented this, have you suggested any feature
enhancements for version 6? It really seems to me that the app
should be insulated from the db servers, so in the ideal case,
witango would manage a DSN pool, each with a connection pool.
bill
On Jan 13, 2006, at 10:13 AM, Robert Garcia wrote:
We have this setup using primebase replication. We wrote our own
code in witango, to manage it, it is held in a custom global
scope, and allows us to change the reads on the fly accross
applications in case a slave dies.
--
Robert Garcia
President - BigHead Technology
VP Application Development - eventpix.com
13653 West Park Dr
Magalia, Ca 95954
ph: 530.645.4040 x222 fax: 530.645.4040
[EMAIL PROTECTED] - [EMAIL PROTECTED]
http://bighead.net/ - http://eventpix.com/
On Jan 13, 2006, at 9:41 AM, William M Conlon wrote:
Yeah, I was thinking would have to set up two DSNs and have my
code explicitly choose the master for writing. But selecting a
slave for reading should be handled by something that manages DSN
connections. Maybe it shouldn't be witango, but rather an
enhanced DB connector (J/ODBC), so when there are many slaves
it's transparent to the application -- kind of like multiple
witango servers.
On Jan 13, 2006, at 9:29 AM, John McGowan wrote:
I was thinking about using my L4 load balancer to handle this
type of stuff, but of course the load balancer can't really tell
the difference between a read and a write. So I was thinking
that I might have to setup two DSN's if I wanted this type of
support. one that would be load balanced, and another that would
only go to the master.
Of course that would require me to do some rewriting or search
and replacing of my existing code.
/John
William M Conlon wrote:
I was reading up on mySQL database replication (master and
slaves), and was curious whether witango had any facilities to
support replicated db clusters. I was thinking something in
the dsconfig.ini, maybe that would specify the master (for
writes) and the slaves (for reads).
Then we could just refer to our DSN and let witango figure out
where to connect.
__________________________________________________________________
______
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/
maillist.taf
___________________________________________________________________
_____
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
Bill
William M. Conlon, P.E., Ph.D.
To the Point
345 California Avenue Suite 2
Palo Alto, CA 94306
vox: 650.327.2175 (direct)
fax: 650.329.8335
mobile: 650.906.9929
e-mail: mailto:[EMAIL PROTECTED]
web: http://www.tothept.com
____________________________________________________________________
____
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
_____________________________________________________________________
___
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
Bill
William M. Conlon, P.E., Ph.D.
To the Point
345 California Avenue Suite 2
Palo Alto, CA 94306
vox: 650.327.2175 (direct)
fax: 650.329.8335
mobile: 650.906.9929
e-mail: mailto:[EMAIL PROTECTED]
web: http://www.tothept.com
______________________________________________________________________
__
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
