Dynamic query routing with ProxySQL

Load balancers are becoming an integral part in the database world. Load balancer is an intermediate service that understands the application traffic and distributes its across available database instances.

ProxySQL is one of the advanced load balancers used in the MySQL database environment.

Here are some of the benefits of ProxySQL in the DB layer.

• Provides intelligent load balancing between applications to multiple databases.
• It understands database topology and monitors the health of the instances, whether the instances are up or down.
• ProxySQL can understand the traffic that passes through it, and can split reads and writes. This is very useful in a replica setup.
• It provides administrators with a lot of control mechanisms like query cache in the proxysql layer for quick access, Query re-routing, re-written bad queries and graceful switchover.
• Overall, it empowers the administrator to maintain efficient operations of the entire infrastructure.

For more information, please visit our blog

This blog is about a use case, how ProxySQL helps in resolving one of the major issues we have experienced in the database.

Our setup is with Master and Replica running MySQL 5.7 version, with ProxySQL serving both reads and writes to the master DB. Replica is used for failover and backup.

 

Database is optimized with all the slow queries tuned, fair to say that CPU usage never crossed more than 10%. Suddenly we are noticing severe performance issues in the application, immediately we can see that CPU usage hit 100%.

On troubleshooting this, we can see that QPS tripled as can be reflected in the below chart.

This was confirmed by the application team that a new release was done, upon troubleshooting the slow query logs we are able to see one query which doesn’t have the index hitting the database very frequently. Unfortunately this is one of the biggest tables that we can’t afford to have the index right away. Apart from that, the application team was worried about rolling back the release due to business implications.

DBA has to add the index to solve the problem but that would be costly in this point which has live traffic and table size. Thankfully we have a load balancer sitting on top of database servers, we have used ProxySQL to route the query to replica and reduced the load on the master DB.

Attaching the same query:

1. select id, user_id, product_name from product where created_on between '2020-01-01 00:00:00' and '2020-09-14 23:59:59'

It scans almost ~15 Lakhs records everytime and the occurrence of the query is very high.

As mentioned earlier, ProxySQL has provision for query re-routing and we decided to move the specified query to the slave server. Query digest / pattern needs to be identified and can be configured in ProxySQL for query-rerouting.

1.              rule_id: 10
2.                active: 1
3.                digest: 0xCFF0EF06A44DA55E
4. destination_hostgroup: 11
5.                 apply: 1

Once these changes are done, immediately queries are moved to the replica and master DB breaths normal.

Following chart is the indication of CPU usage in master and replica after moving out the culprit queries to the replica.

After Making the changes:

 

Key Takeaways

• Optimize the queries with DBA before every release cycle.
• Use Load balancers, especially the one which is SQL aware.
• Queries can be re-routed, replaced or even blocked from the database without making any application changes.