Top ProxySQL Features Every DBA Should Know

Features in ProxySQL

• Query Caching
• Query Routing
• Mirroing
• Query Rewrite
• Data Masking

Query Caching

Query caching is a feature to store the results of the repeated set of queries so that results can be returned quickly to the application/end user and can reduce the disk seek time. You may raise a question, why not MySQL query cache? Its because MySQL cannot scale to a certain level due to its locking and invalidation algorithms.

ProxySQL address this issue MySQL has and its caching technique are entirely different from MySQL. Caching mechanism works on basis of query rules that we set. DBA can decide which query to cache and which not to. Caching is based on in-memory key/value.

Query Routing

Query Routing feature is used to identify the type (read or write) of hits and route the traffic accordingly to DB servers. Rules can be defined in ProxySQL on how to route the traffic to DB servers. No application changes required for read and write split.

Let’s see some sample rules;

- Can define rules such a way that reads can go to slave and writes to master.
- In case, Galera or Group replication we can share reads / writes to all the nodes or writes to one node and reads to all nodes.
- Can set the weight of traffic to DB servers like 50:50 ratios, 30:70 ratios etc.
- Let us say, we have reporting queries which needs to be pointed to a seperate server. It can be done.
- Basically, query routing (QR) can be based on host name, usernames, regular expressions and digest.
 

Mirroring

Query Mirroring is about sending the queries or traffic to the muliple backend. We can mirror the queries to different servers added in a hostgroup. The question comes why do we need to mirror, when there is already query routing in place. Let’s say that you are having an old version of MySQL, and you have a plan to upgrade it to a latest version but not sure how the latest version is performing. In these cases, query mirroring will be helpful as it mirrors the live traffic without making changes in the application end

Work flow: When ProxySQL receives a query, it will get passed to query processor which identifies whether the queries is mirrored, if so then it duplicates the whole MySQL session and associates it to a mirror queue. If the pool is free then the query will be processed else it stays in the queue. If the queue is full then the query will be lost.This will help if you want to check the query performance across different versions of MySQL without disturbing the application. Do not use this feature for replicating data as this cannot maintain consistency.

Query Rewrite

Quite often we have seen that very bad queries hitting the db and causing the entire db to slow down or sometimes bring the server down! Trying to make the changes for these offending queries takes a bit of time and sometimes forever.

ProxySQL query rewrite enables to rewrite a query dynamically. When a replace pattern is added to a match_pattern, ProxySQL will rewrite the query dynamically by replacing the orginal pattern with the modifed one.

Data Masking

This is an important features of ProxySQL that helps in masking of data. Certain high secured data can be kept hidden. It generally involves restricting the user privileges for a particular user to whom the data must be hidden. ProxySQL masks the confidential data, by not exposing them to the users who have the access to the data. Let’s consider an example by which some of the credit card details are hidden from the users by masking them.

These are some of the features of ProxySQL. There are lot more advantages of ProxySQL such as

• Connection multiplexing
• Dynamic Query routing
• Audit log support
• LDAP!! [Expected]

Conclusion

ProxySQL provides DBAs with greater control over how MySQL traffic and queries are handled. Features such as query routing, caching, mirroring, rewriting, and data masking can help improve performance while reducing the need for application-side changes.

At Mafiree, we work with ProxySQL to help teams manage MySQL traffic more effectively, optimize query performance, and handle changing database workloads without unnecessary application changes.