Forum Discussion

notscud's avatar
notscud
Icon for Nimbostratus rankNimbostratus
Feb 21, 2011

irule to send database read requests to one database and database write requests to another

Hi.

 

 

I was tasked with writing an iRule to send database read requests to one "pool" and database write requests to another. I created a class called ws_read_ops which contains all of the available database read operations and if the class is matched, the read_only pool is assigned. If not, then the connection defaults to the write pool. The issue that I am having is that if there is a read request immediately followed by a write request, the new request is not re-evaluated by the iRule as a load balancing decision has already been made. I need to modify my iRule to have it be evaluated each time there is a request. BTW, the traffic is SOAP on port 80. Thanks in advance,

 

 

Peter

 

 

when CLIENT_ACCEPTED {

 

TCP::collect

 

}

 

 

when CLIENT_DATA {

 

set app_pool [class match -value [TCP::payload] contains ws_read_ops]

 

log local0. "pool is $app_pool"

 

log local0. "Got request on [TCP::payload]"

 

if {$app_pool ne ""} {

 

pool $app_pool

 

} else {

 

pool ssoe_websvc_readwrite

 

}

 

TCP::release

 

}

 

 

 

application delivery
dev
devops
iRules
  • hooleylist's avatar
    hooleylist
    Icon for Cirrostratus rankCirrostratus
    Feb 23, 2011
    Hi Pete,

    I'm not sure this is as simple as looking for read keywords. Would you want a query like this being sent to a read pool or write pool? 

    UPDATE t1
SET s1 = "something"
WHERE column_name IN (
   SELECT s2 FROM t2
);

    I think it might be safer to look for DDL keywords indicating a write/modification. However even doing that doesn't account for invalid matching like this:

    SELECT c1 FROM t1 WHERE c2 LIKE 'some string with update or create in it';

    Anyhow to address your question, I think you'd need to continue collecting and releasing the payload. But this isn't something I've tested with TCP. Is this SOAP over HTTP? If so, you could potentially add an HTTP profile and then use HTTP::collect on each HTTP_REQUEST.

    Aaron