Hi
I am trying to introduce some load balancing (more like request traffic shaping) into my environment.
Overview:
I have 2 mySQL Community 5.5 servers running master<->master replication (and working very well)
My problem is these are used for high read and write transactions, and I cannot get optimum performance for the application from them, every improvment on write performance seems to reduce the read performance.
What I am looking to do is, setup Slave<- Master<->Master-> Slave behind a load balancer, and define rules based on the type of request at the LB (which database, which user/host, to define the mySQL server to use
As I have previous experiance with the Riverbed Traffic Manager from other applications I would like to continue to use this platform. I have managed to get to the point where I can connect and make that 'routing' decision based on the user/host however I am failing to keep the connection open. I believe it could be related to the capability flags not setting interactive. As the LB fake sets these to the client in the connection and I think I am setting them incorrectly.
The documentation for doing this with Riverbed is very good,
https://splash.riverbed.com/docs/DOC-1512 . But Doesnt explain the setting of the capbility flags used in the sample code.
I have checked the documentation for the capability flags but am stuck.
Could anyone help in providing some insight into how these flags should be set in the handshake; sample below
$server_hs = "\0\0\0\0" . # length - fill in below
"\012" . # protocol version
"Stingray Proxy v0.9\0" . # server version
"\01\0\0\0" . # thread 1
$salt1."\0" . # salt(1)
"\054\242" . # Capabilities
"\010\02\0" . # Lang and status
"\0\0\0\0\0\0\0\0\0\0\0\0\0" . # Unused
$salt2."\0"; # salt(2)
The capabilities are set \054\242 , but I dont understand how this is derived from the mySQL internals documentation:
http://dev.mysql.com/doc/internals/en/capability-flags.html
Any help would be greatly appreciated
Regards
Mitchell