|
|
| Applications |
| Mailing Lists |
| Miscellaneous |
| Operating Systems |
| Programming |
Important
For more information on MySQL Load Balancer, including how to be
included in the beta programme, contact
<enterprise-beta@mysql.com>.
The easiest way to understand MySQL Load Balancer is to look at a typical example of how MySQL Load Balancer can be used to improve the distribution of work to multiple MySQL servers.
Given an existing setup of several replicating MySQL servers, you can set up the MySQL Load Balancer to provide you with replication-aware load distribution.
Suppose you have three slaves replicating from one master, the slaves running on the machines slave-1, slave-2, and slave-3, the master being on master-1. Each MySQL server listens on the default port of 3306.
For client connectivity, typical configurations are in one of two topologies. The first topology uses applications that are aware of multiple clients and choose a MySQL server based either on a random selection or by choosing a slave based on a known quantity, such as user ID.
In this scenario, it is possible for a client application to choose a slave that is unavailable, or in a replication situation, a slave that is not up to date compared to the master, or lagging behind the master in terms of processing replication data such that queries accessing the information would fail to return data, or return data that was out of date. In all these cases, the client would be unable to determine the issue (without checking the situation itself). In the event of a failed server, the connection would timeout and another server could be chosen, but the delay could cause problems in the application.
In this scenario, it is also possible for a single MySQL server to become overloaded with requests. For example, if the application was using an ID-based decision model to choose a MySQL server, then a high number of requests for a given ID could produce a very high load on the chosen server. This could affect the replication thread and place the server further behind compared to the master.
The second topology uses a model where each client has a dedicated MySQL server.
In this scenario, a problem with the MySQL server for an individual client could render the client useless. If the MySQL server is significantly behind the master, you would get out of date or incorrect information. If the MySQL server has failed, the client will be unable to access any information.
Using the MySQL Load Balancer, you can replace the individual connections from the clients to the slaves and instead route the connections through the MySQL Load Balancer. This will distribute the requests over the individual slave servers, automatically taking account of the load, and accounting for problems or delays in the replication of the data from the master.
In the scenario using MySQL Load Balancer, any failure of a single MySQL server automatically removes it from the pool of available servers and distributes the incoming client connection to one of the other, available, servers. Problems with replication are addressed in the same way, redirecting the connection to a server that is up to date with the master. The possibility of overloading a single MySQL server should also be reduced, since the connections would be distributed evenly among each server.
To start the MySQL Load Balancer in this scenario you would specify the configuration of the master and slave servers on the command line when starting mysql-lb:
$ bin/mysql-lb --proxy-backend-addresses=master-1 \ --proxy-read-only-backend-addresses=slave-1:3306 \ --proxy-read-only-backend-addresses=slave-2:3306 \ --proxy-read-only-backend-addresses=slave-3:3306 \ --proxy-lua-script=share/mysql-load-balancer/monitored-ro-balance.lua \ --monitor-lua-script=share/mysql-load-balancer/monitor-backends.lua
This will start the load balancer, which listens for incoming
client connections on port 4040. The monitor component will
connect to each backend MySQL server with the MySQL user
monitor and no password, to be able to execute
queries on them. If you do not have a MySQL user with that name or
have a password set for the user, you can specify those using the
options `--monitor-username` and `--monitor-password`.
The options in this example set the following options:
--proxy-backend-addresses– sets the address and port number of the MySQL master server in the replication structure. This is required so that MySQL Load Balancer can monitor the status of the server and replication and use this to compare against the status of the slave servers. In the event of a problem, the information gained will be used to prioritse connections to the slaves according to which slave is the most up to date.--proxy-read-only-backend-addresses– each one of these options sets the address and port number (separated by a colon), of a backend MySQL server. You can specify as many servers as you like on the command line simply by adding further options.--proxy-lua-script– specifies the Lua script that will be used to manage to the distribution of requests.--monitor-lua-script– specifies the Lua script that will be used to monitor the backends.
To get a list of all the available options, run
$ mysql-lb --help-all
|
Contact Us Legal Notices Order Services Online Pantek Home Privacy Policy IT news Site Map Pantek Library |
User Comments
Add your own comment.