Official Mafiree Blogs

Keeping you informed about Databases

Significance of using Invisible Primary key (GIPK) with MySQL 8.0
By Sukan   |   February 21, 2023   |   Posted in : MySQL
How Generated Invisible Primary Keys (GIPK) Can Boost Your Database Efficiency

Achieving High Availability Using Log Shipping
By Sujith   |   February 21, 2018   |   Posted in : SQL Server
Here we will get the detailed explanation of how we can achieve HA using Log Shipping.

SQL Server On Linux
By Sujith   |   March 30, 2019   |   Posted in : SQL Server
SQL Server 2017 brings the best features of the Microsoft relational database engine to the enterprise Linux ecosystem, including SQL Server Agent, Azure Active Directory (Azure AD) authentication, best-in-class high availability/ disaster recovery, and unparalleled data security.

Types of SQL Server Replication
By Sujith   |   February 19, 2018   |   Posted in : SQL Server
Understanding on the types of replication in SQL Server and the type apt for your traffic pattern.

Data File Splitting in SQL Server
By Murali   |   March 15, 2021   |   Posted in : SQL Server
Huge data blocks resides under single MDF file might cause a performance of the query and impact the application services. This blog help you to understand how we can the split the single MDF file into multiple data files.

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

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.



0 Comments


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