Official Mafiree Blogs

Keeping you informed about Databases

 Home  /  Proxysql  
ProxySQL Features
By Sukan   |   February 16, 2018   |   Posted in : Proxysql

In my previous blog post, I have explained ProxySQL as a load balancer. In this blog, let's see the features that ProxySQL have :

Features in ProxySQL:

  • Query Caching

  • Query Routing

  • Mirroring

  • 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.

For example, this is a complete view of a credit card information table:

After masking data only a part of credit card numbers can be seen. This is how the masked data looks:




1 Comments


Tamilselvan      March 02, 2018 11:52 am


It is very helpful.

Leave a Comment

Name *

Email *

Comment *



Search All Blogs



Need Support?


Solutions      Services      Resources      About Mafiree

More than 3000 servers monitored

Certified database Administrators

24X7X365 Support

Happy Clients


ENQUIRE NOW
For Sales Related Queries, Please Call Our Sales Experts at

 +91-80-41155993


Meet Mafiree on social networks!

     

PCI Certificate

Copyright © 2019 - All Rights Reserved - Mafiree