Identify proxy server

On demand query routing - how to prevent downtime on your main MySQL server

routingIn a I wrote about how read/write split is implemented in ProxySQL, and what I believe is the best practice for read/write split on top of MySQL servers:

  • start sending all traffic to MySQL master
  • identify what queries can be routed to the MySQL slaves
  • create rules to route these queries

I believe I am remarking that in ProxySQL read/write split is a special case of query routing.
ProxySQL doesn't try to magically perform read/write split for every use case scenarios, but instead it gives you the tools and the power to route traffic dynamically, on demand, through a simple and powerful Admin interface.
It is a tool for DBAs that need to maintain a farm of MySQL servers, and guarantee uptime, high availability, and performance.

cpu beforeIn this blog post I will present a use case on how ProxySQL runtime reconfiguration is extremely useful to implement query routing when needed.

Imagine you have a classic MySQL asynchronous replication with one master (db02) and 2 slaves (db01 and db03).
You also have 15 proxysql instances managing all the traffic from all your webnodes.
Because most of the application cannot tolerate to read stale data, you have configured all the proxysql instances to send most of the traffic to the master, and only few expensive and long running queries to the slaves.
MHA is configured to automatically perform a failover in case of master failure, and ProxySQL is correctly configured to check the read_only variable of the servers automatically reconfigure servers in the read and write hostgroups.
The system is in place for several months, not a problem. Until one day, in the middle of the night, you get a page about high load on master.
You check trending graphs and indeed there is an increased load on the master. You can quickly figure out that if you don't take immediate action, the master will soon become so overloaded that will provide terrible response time, so you need to identify the offensive queries and move them away from the master.

load beforeYou start checking slow query log, Anemometer, table stats_mysql_query_digest in ProxySQL itself, or any other tool you currently have deployed (PMM, ClusterControl, VividCortex, SolarWinds, etc etc), and you are able to identify that there are no new queries. Neither is increased the volume of the queries that are normally running.
You will realize that data distribution in some tables have changed, and some queries have become several times slower.
You also know that no additional index will improve the performance of the queries that are particularly slow.
You know that it is late at night, and even if you escalate the issue to developers, no developer will be able to come with a quick fix to change the application logic behind these queries.

Based on historical data, you know that these type of queries were normally running in 5-10 seconds, while now it is running for 60-600 seconds.
Because of the nature of the queries you know that is perfectly fine to send them to slaves. So here is your action plan:

  • using stats_mysql_query_digest in ProxySQL's Admin you are able to identify the digests of these queries
  • in only one of proxysql instance you create 2 new rules to send these queries to slaves
  • once you verify that the routing works as expected, you configure all the proxysql instances with the new query rules

In details:

Identify the queries

I won't go into the details of this specific case, but my on read/write split describes how to identify digest in ProxySQL's stats_mysql_query_digest. Once you identified them, you can move to the next step, creating new rules.


Related posts:

  1. Virtual proxy server
  2. Check my proxy server
  3. Best Windows proxy server
  4. Windows Web proxy server