ColdFusion Database Pool Master/Slave(s)

Last week when I looked at databases pools I got a few suggestions re master/slaves databases. In this configuration you set up a single database (called the master) and have it replicate to one or more other databases (called the slaves).

Using database pools in this manner gives you the confidence that one database is always up to date/correct and all other are just copies of it.

It makes modifications to the database (insert/update/deletes) a little simpler in that you only have to update one database and you still can read from any of the slave databases. It also can make code to handle complex transactions simpler. You would also use it were you have data being copied or replicated from elsewhere into your web database (as you would only have to update a single database).

However it does have the advantage that if you master database goes offline you can no longer do updates and depending on how the data is replicated any updates may not be instantly reflected in all the slave databases.

Here's the modified version of last weeks code to use database pools in this way:

DSN.cfc - notice that there are now two method to set the DSNs and the getDNS now takes a parameter called mode:

view plain print about
1<cfcomponent displayname="DSN" extends="singleton">
2    <cfset variables.DSN = arraynew(1)>
3    <cfset variables.DSNs = "">
4    <cfset variables.length = 0>
6    <cffunction name="getDSN" access="public" returntype="string" output="true">
7        <cfargument name="mode" type="string" default="read">
9        <cfscript>
10            var min = 0;
11            var use = 0;
12            var i = 0;
14            if (arguments.mode is "write") {
15                use = 1;
16            }
17            else {
18                for (i=1; i lte variables.length; i=i+1) {
19                    if (variables.DSN[i].count lte min) {
20                        use = i;
21                        min = variables.DSN[i].count;
22                    }
23                }
24            }
25            variables.DSN[use].count = variables.DSN[use].count + 1;
26            return variables.DSN[use].DSN;
28    </cffunction>
30    <cffunction name="freeDSN" access="public" output="false">
31        <cfargument name="DSN" type="string" required="yes">
33        <cfscript>
34            var i = 0;
35            for (i=1; i lt variables.length; i=i+1) {
36                if (variables.DSN[i].DSN is arguments.DSN) {
37                    variables.DSN[i].count = variables.DSN[i].count - 1;
38                    break;
39                }
40            }
41            return;
43    </cffunction>    
45    <cffunction name="setSlaveDSNs" access="public" output="false">
46        <cfargument name="DSNs" type="string" required="yes">
48        <cfscript>
49            var i = 0;
50            for (i=1; i lte listlen(DSNs); i=i+1) {
51                variables.DSN[i+1] = structnew();
52                variables.DSN[i+1].DSN = listgetat(DSNs,i);
53                variables.DSN[i+1].count = 0;
54            }
55            variables.DSNs = listappend(variables.DSN[1].DSN, DSNs);
56            variables.length = listlen(variables.DSNs);    
58    </cffunction>
60    <cffunction name="setMasterDSN" access="public" output="false">
61        <cfargument name="DSN" type="string" required="yes">
63        <cfscript>
64            variables.DSN[1] = structnew();
65            variables.DSN[1].DSN = DSN;
66            variables.DSN[1].count = 0;
67            variables.DSNs = listappend(DSN,variables.DSNs);
68            variables.length = listlen(variables.DSNs);    
70    </cffunction>    

The custom tag dsn.cfm

view plain print about
1<cfif thisTag.executionMode IS "start">
2    <cfparam name="attributes.mode" default="read">
3    <cfset request.currentdsn = caller.dsn.getDSN(attributes.mode)>
4<cfelseif NOT thisTag.hasEndTag>
5 <cfthrow message="Missing end tag.">
6<cfelseif thisTag.executionMode IS "end">
7    <cfset caller.dsn.freeDSN(request.currentdsn)>

And call it like so:

view plain print about
1<cfset dsn = getInstance("dsn")>
3<cfquery name="myquery1" datasource="#request.currentdsn#">
4select ....
7<cf_dsn mode="write">
8<cfquery name="myquery1" datasource="#request.currentdsn#">
9update ....

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
I don't get it. How does this save the amount of heavy lifting done?

a new record is inserted (into the master, natch). you'd expect (no matter what slave) to see that new record asap. that's a lot of replication traffic all the time sync'ing the slaves back to the master.

I can see where you're coming from - it's a lot like having a live database and a reporting one run off a DTS job to keep it up to date.

and it's also good as far as security where your slaves can get knocked over (hacked) with less worry while you protect the master.

the bit I'm not sure about is bringing the replication up to the application level (CF). it strikes me that it'd be more effective (and more abstracted) to have it deep in the data services level (eg: integrating the database servers themselves), perhaps run off connection pooling.

or am I misguided here?

this isn't a flame, rather trying to see the whole picture and what's practical.
# Posted By barry.b | 5/7/07 11:33 PM
Hi Barry. When using Master/Slave databases as described above the replication needs to happen at the database level. This method just gives you better performance at the application level as you can read from multiple databases similar to a mirrored RAID disk drive.

My previous articles on the same subject had the replication being handled by the application rather than the database, which method you use would depend on your application.

I'm assuming that the application does far more reads than writes otherwise there would be no performance gain and not much point in doing this other than for fail over reasons (and the above code would need to be modified to provide automatic fail over).

It is possible to load balance at the database level but it usually requires expensive versions of database servers or 3rd party software and not every project can afford that.

Interesting idea re security BTW I hadn't thought along those lines.
# Posted By Justin Mclean | 5/8/07 12:11 AM