Database Clustering Tutorial 10 – ProxySQL Load Balancer

In this article, I’m going to talk about load balancers and guide you through installing ProxySQL. Now, this is only a light introduction with just enough information to get you started.

The Purpose of a Load Balancer

A load balancer is like another addition to your database that adds a layer of magic.  The load balancer is a piece of software that will automatically balance how much each node is being hit to protect from overworking a particular node and slowing down the entire database cluster.

If you do not have a load balancer, and you were writing a software application, you would manually decide what nodes to read and write from.  This is not always good because you might not know ahead of time how well a particular node is going to keep up with your demands.



With a load balancer, you make every piece of code talk to the load balancer, and then the load balancer will distribute the demands across the nodes.

The load balancer of choice is ProxySQL.


This article is really going to show how great ClusterControl is for automating things, because we can set up ProxySQL in a few clicks.

Installing ProxySQL

On your cluster overview, click the little menu button on the far right.  Then click add load balancer.

It will come up with five options of load balancers to choose from, but it will go to ProxySQL by default. We’re going to stick with ProxySQL.

Now, we are going to want to install ProxySQL on the Controller node (the node ClusterControl is installed on). Why? Well, imagine what would happen if a node happened to go down and it was the same node that your load balancer is on. Since you communicate with the load balancer to talk to your entire cluster, you would now have big a problem.

This is why it is best to install it on the controller node.

Now, under ProxySQL User Credentials give an administration password and monitor password. 

For the Add Database User part, we are just going to ignore that part for now. We can do this very easily later on, so don’t worry about that right now. I will be instructing you through configuring this once we have the load balancer set up. 

For Implicit Transactions, I am going to click No.  If you Google implicit transactions and Severalnines, you’ll probably find an article discussing ProxySQL and how this option affects things, but I’m not going to delve into that right now.

Now click Deploy ProxySQL, and then go to the activity viewer to view the job’s progress. 

Once the job is done, you can click the Full Job Details to get connection details and ports. When you communicate to this proxy, you are going to use the IP address and port that it will show you.  You will use this within the connection string for your application.

Now, go to the cluster overview and click the Nodes tab. In the left panel you will see ProxySQL and under it will be the node that is part of it. Click that node and then you will see some fancy graphs and stuff.

Before we move on, I want to discuss the ProxySQL Host Groups.

There are two hostgroups to choose from: Hostgroup 20 and Hostgroup 10.

The difference between the two is that hostgroup 10 are readers and writers, which means that the databases in it can be read from or written to, while hostgroup 20 are only readers, which means you may only read from them. This information will be helpful when we go to create a new user.

Creating a New User

To create a new user, first click the Users tab.

Then click the Add New User button on the far right.

Now under the Create a new user tab, give it a username and password.

For the Default Hostgroup, we will want to choose hostgroup 10. Remember that hostgroup 10 are readers and writers.

Leave the Default Schema set at Null.

Give a database name.

Now, for the Grants, it is asking what privileges you want this user to have. Set it to All Priviledges (for learning sake).

The way ProxySQL works is that when you try to connect to the database using the proxy, ProxySQL is going to authenticate the user. Once the user is authenticated, then ProxySQL is going to go to the database. Because ProxySQL does its own authentication, the user must exist in ProxySQL and the database.

Now, to finish, click Add User.

Conclusion

Now obviously, I did not explain everything. I could make an entire series just on load balancers, so I understand that my explanations were a bit thin. That’s OK. You now have the basic understanding upon which you can learn more on your own.

Leave a Reply

Your email address will not be published. Required fields are marked *