The MySQL database proxy is a world of its own.
Part of what makes it so interesting is the numerous configurations and deployments available. In this blog, we’ll talk about Continuent Tungsten but, the best practices are not specific to Tungsten Proxy (aka “the Connector”) and may serve to provide general guidance for anyone using MySQL or MariaDB for business-critical or mission-critical purposes.
For a brief review of why to use a MySQL Database Proxy, check out my blog about connection basics for zero downtime MySQL. (Also don’t miss the link to the “Intro to (MySQL) Proxies” webinar hosted by the original developer of Tungsten Proxy!)
The database proxy “sits” between your application(s) and your MySQL or MariaDB database layer, and it can simply and quickly route connections as fast as possible, and for Tungsten it can also be used to optimize the relationship between your application and database service in a variety of manners.
Either way, just like any other piece of your business-critical MySQL system, it should be designed and deployed for fault-tolerance. So when deciding where to deploy a MySQL or MariaDB database proxy for high availability clusters, make sure you’ve checked the best practices and spoken to experts (if you’re a Continuent customer, reach out to Support).
Before we dive into where to deploy the MySQL database proxy (or proxies), we must take into consideration how many MySQL proxies you need?
The bare minimum - for a simple 3-node HA cluster - should be two (2) Proxies.
While you can have just one (1) proxy, that leaves it as a SPOF (single point of failure) in your database system - if something happens to that single proxy there are no others around to continue managing application-to-database connections!
For Continuent Tungsten, there’s no reason to be conservative about the number of Proxies because it does not affect your price at all (nor compute resources much). Tungsten Proxy is a lightweight Java process that doesn’t consume much compute resources. So you should focus on getting the optimal configuration.
In fact, some customers create a “farm” of proxies, with hundreds of Tungsten Proxies - but talk to Support if you’re interested in configuring something like this! Again - it makes no difference for your Tungsten price - we just want you to have the right configuration.
Once you’ve determined the right number of MySQL database proxies, you have a few options for where to install them:
- On application servers.
- On dedicated Proxy servers.
- On database servers - not recommended for production systems.
The decision to place the proxies on dedicated servers versus application servers depends on a few factors, including the compute resources available to you, the complexity of your database deployment and number of application servers.
If you have less than ten (10) app servers, then you may deploy on application servers, but keep in mind it depends on what else is making connections; app servers aren’t the only things that connect to databases - and for example, you wouldn’t necessarily want a setup where internal clients go through an external app server to connect to MySQL. On that note, what’s also super important is to make sure every single piece of software, even scripts, hit the proxies; that’s why we recommend changing the default database port and assigning it to the proxies. In summary, deploying on app servers makes for easy management and configuration, and also, there are no additional resources needed, so it’s cost-effective from an infrastructure standpoint too.
If you have lots of app servers, especially if you have hundreds of app servers, you might want to consider a small proxy farm because it’s easier to manage a few dedicated proxy servers in a farm versus installing on app servers. The key to keep in mind is that you’d need a load balancer in front of the proxies, either software like HA Proxy or hardware such as F5’s, and you’d need to configure the proxy liveness (health checks) inside the load balancers. Here at Continuent we have example scripts that can help with this and Support can make recommendations. Customers, if you’re interested, our Support team is happy to help you get your proxy farm up and running!
It is not recommended to deploy the database proxy on database servers because in the event that you suffer a hardware or other failure on the database server, you’ll lose the proxy, thus breaking connections from the app servers connected to that proxy. In addition, installing the proxy on the database nodes adds some additional load on the database servers, and it’s usually best practice to use dedicated database servers. Thus for highly available MySQL clusters, keep your database proxies decoupled from your database infrastructure!
Customers, please reach out to Support if you have any questions about what’s right for you!
If you’re not yet a customer, during a POC our Solution Architects work closely with your team to decide the optimal number of connectors and where to install them as part of the POC process. Feel free to submit a webform if you are interested to learn more about Tungsten and what benefits there might be using the Tungsten Proxy in your environment!