Hi all! I need your help. I want to balance traffic over MySQL databases. I will have two virtual servers, one for read operations and other for write operations. Virtual server's functionality will be redirect traffic to its pool. I have never worked with MySQL traffic in BIG-IP LTM, so I do not know if there will be problems, for example within authentication process. Have you ever worked in this scene? If someone has worked in this scene, what monitor have you used to? Thanks in advance.

Hi Pepi, I don't think it's quite feasible to do this for production databases with an iRule (yet?). You could contact F5 Support and ask them to open a request for enhancement for you with details of what you're trying to accomplish. Aaron

Hi Aaron! I don't know if I've explained well. The BIG-IP LTM only balances the traffic to have high availability. The traffic would not be analyzed by BIG-IP LTM. I mean, once the TCP connection is created, the MySQL traffic transparently passes through the BIG-IP LTM. Is this scene possible? Do you know if this scene has any problems? Thanks.

Do you want to split out read requests to one server and write requests to another? If so, I don't think this is really feasible. Just about the only SQL "load balancing" I've seen done on LTM is to always use one server unless it's down and then only use that server even if the first server comes back up. That solution is described here: http://devcentral.f5.com/wiki/default.aspx/iRules/SingleNodePersistence Aaron

The client is an application server. This application server divides read and write requests between two virtual servers, one that handles only the read requests and another only write operations. Each virtual server has a pool with two nodes, so if one falls, the other node serving requests.

Balancing MySQL Traffic

12 Replies

hooleylist
Cirrostratus
Jun 28, 2011
Hi Pepi,

I don't think it's quite feasible to do this for production databases with an iRule (yet?). You could contact F5 Support and ask them to open a request for enhancement for you with details of what you're trying to accomplish.

Aaron
Pepi_71346
Nimbostratus
Jun 28, 2011
Hi Aaron!

I don't know if I've explained well. The BIG-IP LTM only balances the traffic to have high availability. The traffic would not be analyzed by BIG-IP LTM. I mean, once the TCP connection is created, the MySQL traffic transparently passes through the BIG-IP LTM. Is this scene possible? Do you know if this scene has any problems?

Thanks.
hooleylist
Cirrostratus
Jun 28, 2011
Do you want to split out read requests to one server and write requests to another? If so, I don't think this is really feasible. Just about the only SQL "load balancing" I've seen done on LTM is to always use one server unless it's down and then only use that server even if the first server comes back up. That solution is described here:

http://devcentral.f5.com/wiki/default.aspx/iRules/SingleNodePersistence

Aaron
Pepi_71346
Nimbostratus
Jun 28, 2011
The client is an application server. This application server divides read and write requests between two virtual servers, one that handles only the read requests and another only write operations. Each virtual server has a pool with two nodes, so if one falls, the other node serving requests.
Pepi_71346
Nimbostratus
Jun 28, 2011
The client is an application server. This application server divides read and write requests between two virtual servers, one that handles only the read requests and another only write operations. Each virtual server has a pool with two nodes, so if one falls, the other node serving requests.
hooleylist
Cirrostratus
Jun 29, 2011
Thanks for explaining this. The Codeshare example should work fine for your scenario.

Aaron
Pepi_71346
Nimbostratus
Jun 29, 2011
Hi Aaron!

Sorry, but I've not understood you. What do you mean with the Codeshare example? Do you mean the scene itself that I've explained?

Thanks
Colin_Walker_12
Historic F5 Account
Jun 29, 2011
So what is it exactly you want to do with the virtuals, if anything? It sounds like all you want are two simple virtuals, one for reads and one for writes, that simply forward traffic back to the servers in each pool. If that's the case, then you wouldn't need any kind of special configuration or iRule at all. Am I missing something? Is there something else you wanted to do to the traffic mid-stream?

Colin
Pepi_71346
Nimbostratus
Jun 30, 2011
Hi Colin!

You are right. Do you know the code for a specific monitor for database (with SQL queries)?

Thanks
Colin_Walker_12
Historic F5 Account
Jun 30, 2011
It depends on what kind of monitor you want. This almost certainly wouldn't be via iRule, you'd want to do it via an external monitor most likely, and have a script execute from the BIG-IP to check the DBs to determine their status. Does that sound like what you're looking for?

Colin