Setup Highly Available MySQL Cluster with ProxySQL

  • Read/Write split
  • Load balancing with hostgroup weights

Multiplexing

When it comes to scaling high traffic applications, mainly those which require many connections to Databases, one of the bottlenecks we hit is the max connections limit available on the MySQL servers. MySQL max-connection limit is determined by the resources (Memory, CPU) available on the server, so we can’t tweak this configuration beyond one limit. Also increasing this limit might result in poor performance as MySQL will create one thread per connection and if we get too many connections on the server then MySQL server will be busy scheduling all the threads rather than executing queries.

Issues with Above Architecture:

Everything works fine with this design until you scale your application servers for serving more traffic on your application. You will eventually hit the resource limits on ProxySQL. Also in the above topology you are increasing latency for each database connection as you need to connect to ProxySQL on the network each time a new connection is required. It is considered a good practice to keep the ProxySQL instances close to the application servers, in fact it is advised to run a ProxySQL instance locally on each application server. When you have your proxy instance running locally, your application can communicate with it using a unix socket which is way faster than normal network connections. If you run ProxySQL on application server, your architecture design should look similar to given below:

Query Caching:

ProxySQL provides a query caching mechanism out of the box. You can add query rules in proxy to notify it on what type of queries to cache and for what time. ProxySQL provides many stats to help you better decide which query we can cache. Executing below query will provide the list of query patterns executed by ProxySQL along with the number of times the query got executed and sum of time spent for those queries.

SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest_reset ORDER BY sum_time DESC;
INSERT INTO mysql_query_rules (active,digest,cache_ttl,apply) VALUES (1,’0xE8930CB2CC9E68D7',2000,1);

Why do we need a centralized ProxySQL instance?

In the above architecture we have a proxysql instance running locally on each application server and each instance connects to all configured backend servers. So for example, if we have one MySQL master server, two MySQL slave servers and three application servers with ProxySQL instances running locally then there will be 9 connections to the backend servers, each proxy instance will connect to one master server and two slave servers. It is known that with increase in network connection we expose more loopholes to the hackers, so higher security risk. It is a good practice to keep all our backend servers behind one proxysql instance configured with proper security and all ProxySQL instances running locally should connect to this centralized proxy server.

Single Point of Failure!!

Wait a minute, with centralized ProxySQL running we have introduced a hop with Single Point of Failure in our architecture!! If somehow centralized proxy goes down then our entire application won’t be able to connect to our DB servers and the entire application will go down. When we have SPoF in our architecture it is normally considered a bad design. To solve this we can utilize Read/Write split feature along with weighted traffic routing to backend servers. In ProxySQL each backend server can be categorized between hostgroups and in each hostgroup we can assign weight to each backend server for traffic routing. So with this feature at our disposal we can run two centralized proxysql instances as shown below, with failover configured.

Conclusion

In this article I have defined some of the best practices to follow with ProxySQL servers to achieve High Availability and better scalability for MySQL clusters. For small applications this architecture is overkill but you can architect your own design based on the requirements of your application with trade offs. For applications serving very high load and having high SLA, this looks the ideal architecture to implement.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store