Setup Highly Available MySQL Cluster with ProxySQL

As defined in the official documentation, ProxySQL is a high performance, high availability, protocol aware proxy for MySQL and forks (like Percona Server and MariaDB). As the name suggests ProxySQL proxies traffic coming to it to backend MySQL clusters. As ProxySQL is developed by DBAs for DBAs, it is not limited to just proxy pass incoming traffic. ProxySQL helps DBAs to solve their daily issues easily with many available features like Read/Write split, Multiplexing, Query Caching and many more to provide high availability and scalability for the MySQL clusters.

Here we will see how ProxySQL features can help us to make our MySQL clusters highly available. We will mainly focus on the given features of ProxySQL to design the architecture:

  • Multiplexing
  • Read/Write split
  • Load balancing with hostgroup weights

Multiplexing

To solve this problem we often introduce proxies with connection pooling enabled between application servers and our MySQL servers. ProxySQL provides the same functionality with efficient implementation than most of the proxies available in the market. ProxySQL provides a multiplexing feature which uses thread pool to connect to backend servers. So whenever any request is completed, ProxySQL’s hostgroup manager checks if the connection can be reused; If connection can be reused (no transaction is active on the connection, no temp table is created or no session variable is set) and the connection pool isn’t full then the hostgroup manager puts the connection back into the pool to serve the next request. With use of multiplexing we can scale hundreds of thousands incoming connection requests to a few hundred backend connections. When we introduce ProxySQL in our architecture it should look like this:

Image for post
Image for post

Issues with Above Architecture:

Image for post
Image for post

Query Caching:

SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest_reset ORDER BY sum_time DESC;

Once the queries to cache are identified, we can add query rules with cache ttl to apply for query caching for the given query pattern or query digest.

INSERT INTO mysql_query_rules (active,digest,cache_ttl,apply) VALUES (1,’0xE8930CB2CC9E68D7',2000,1);

With a proxy instance running locally and query caching enabled, no connection will be made to backend servers for executing cached queries and result will be returned from in memory cache. There are some known limitations with query caching in proxysql. You can read more about this here: https://github.com/sysown/proxysql/wiki/Query-Cache

Why do we need a centralized ProxySQL instance?

Image for post
Image for post

Centralized ProxySQL helps us to keep all our MySQL backend servers behind the proxy and reduces the attack risk. It is also helpful with metrics as well, as we have aggregated query statistics for all queries executed on our backend servers on our centralized ProxySQL instance. Also with centralized proxy we are limiting connections going to backend servers in limit which helps in scaling our applications better in future.

Single Point of Failure!!

Image for post
Image for post

As shown in above architecture, we configure all ProxySQL instances running locally (AP1, AP2 and AP3) to route 99% write traffic to centralized ProxySQL instance CP1, by assigning weight to 99 for it, and 1% write traffic to another centralized ProxySQL server CP2 (assigned weight for CP2 will be 1). In this case if one of the centralized proxies go down then that proxy will be shunned and the entire traffic will be served by another centralized ProxySQL instance. We need to configure read traffic split similar to above but in vise versa manner. We can configure all local ProxySQL instances to route 99% of read traffic, by assigning appropriate weight, through CP2 server and another 1% read traffic through CP1. In this architecture, if any centralized ProxySQL server goes down then it will be shunned in all ProxySQL instances running on application servers and entire read and write traffic will be served by another running instance of centralized ProxySQL server.

Conclusion

If you liked this article please clap and share it with your colleagues and friends.

Software Engineer at Endurance International Group, India.

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