In the database world, it is crucial to keep our databases always highly available with better performance. When we deploy any new application or database, we would get the desired response time but as the traffic/connection/data size increases, the response time goes down. Now the challenge for any DBA is to scale up.
Let us say, DBA has added couple of slaves and routed the read traffic to the slaves and brought back the response time. Next challenge would be routing the traffic when there is replication delay or server crash? Routing manually can still impact the business badly. ProxySQL can address such challenges.
What is a Load Balancer?
Those who know about LB, please move on to “What is ProxySQL”.
Before knowing more about ProxySQL, let’s learn what is LB(Load Balancer). To be simple, a Load balancer distributes the network or application traffic among the available servers. This helps in attaining a better efficiency, reliability, availability & performance of applications by increasing the concurrent users and improving the hardware utilization efficiency.
There are two types of LBs, hardware and software load balancers. Hardware load balancer has a software component which processes the packet. Software load balancer includes a server component build over the network and with a combination of one or more scheduling algorithms. Load balancers hence also reduce the complexity at the application level when there is a DB IP change. Please see below for better clarity.
What is ProxySQL?
ProxySQL is one of the advanced software load balancer that we have in the market. ProxySQL has an inbuilt ability to identify the reads, writes and route the write traffic to master and read traffic between the available slaves. One occurrence of a Replication delay or server crash or replication breakage, read traffic will be redirected to different slave server automatically. ProxySQL can do much more which I will explain in my next blog. In this blog, I will explain you more about the Load Balancing. Yes, it is open source and free.
ProxySQL accepts incoming traffic from an application and forwards it to backend MySQL / MariaDB / Percona servers. In case of any failure in the nodes(servers), ProxySQL understands the issue and routes the traffic to another highly available node. Thus ProxySQL ensures that there is no single point of failure. The application continues to work since the crashed server traffic would be routed to another node by ProxySQL. Using an appropriate load balancing algorithm it helps to maintain an optimal and efficient utilization of the resources.
Let’s consider an example of a sample, ProxySQL with 3 MySQL replicas through which the applications hits are being distributed.
ProxySQL directs writes to master and reads to all 3 replicas equally.
Let’s consider MySQL slave 1 is down.
This will be identified by ProxySQL and will route the traffic to slave 2 and 3. ProxySQL will mark Slave 1 as a failure node and keep out of the active node lists.
The interesting part is that ProxySQL will monitor the failed node even after the failure case. Once the node comes up, it will be automatically added to the active node list and traffic will be routed to all 3 servers.
All these are happening without making a single change in the application.
Takeaways:
- ProxySQL is a software Load Balancer.
- Read / Write hits will be split equally, based on rations and it is configurable
- Node failure / Re-joining on the group is automatic.
