This article is outdated as MaxScale has matured a lot since the article was written and it does install to MariaDB 10 branches perfectly now. Please refer to MaxScale Getting Started page for further instructions.
Introducing SkySQL MaxScale Proxy
MaxScale is an open-source, database-centric proxy that works with MariaDB and MySQL®.
It has a pluggable architecture designed to increase flexibility and aid customisation as well as a lightweight, high-speed networking core designed to facilitate throughput. MaxScale runs between the client application and the database cluster and currently offers connection and statement-based load balancing.
Hardly can put it any better than they've done at SkySQL. Maybe what is missing is that it also works HA solution as it monitors each instance for replication lag heartbeat and will error report if node is unreachable.
Prerequisites and Installation
This is partially a continuation to earlier post how to install MariaDB 10 Galera Cluster on MySQL Sandbox. The Galera Cluster from that setup is used when configuring MaxScale
If you have had a glance with README file of MaxScale sources you might have noted the section where it requires three rpm packages from 5.5.* tree. Tried and tested, you'll end up still missing one file which is the errmsg.sys. The errmsg file from 10 tree does not work due to different file headers. This is, unless you are running 5.5.* on your system.
There is easier way which requires one extra download and sources tarball from MariaDB and am going through this path. The prerequisite steps:
Download latest version of MySQL Sandbox https://launchpad.net/mysql-sandbox/+download or install with CPAN. Installation instructions: http://search.cpan.org/~gmax/MySQL-Sandbox-3.0.44/lib/MySQL/Sandbox/Recipes.pm#Installing_MySQL::Sandbox
Download latest MariaDB 5 Sources from https://downloads.mariadb.org/. The file what we are looking for has pattern mariadb-5.5.*-linux-x86_64.tar.gz
Download latest MaxScale Sources from https://downloads.skysql.com/files/SkySQL/MaxScale or clone/fork git repository from https://github.com/skysql/MaxScale
Making MariaDB Sandbox
After installing MySQL Sandbox, find your way to directory where the downloaded MariaDB sources exist. To make sandbox from sources simply run next command as your user, not root:
make_sandbox mariadb-5.5...tar.gz --export_binaries
This will create new directories ~/sandboxes and ~/sandboxes/msb_5_5_38 (minor version number is the same as with the tarball). You may browse to sandbox directory to stop the sandbox as it gets started with the creation. Also MSB (MySQL Sandbox) extracts the binaries to directory 5.5.38 in to directory where you run the make_sandbox command. This is the directory we want to be using with the MaxScale installation, the actual sandbox can be removed simply by rm -fr ~/sandboxes/msb_5_5_38 as it is not needed at all.
Changing Build Configuration Paths
If using MaxScale sources tarball, extract the tarball and enter directory maxscale-0.*.* where obviously version number matches the tarball versioning. Enter directory and open build_gateway.inc with your favourite text editor. The changes we will be making is to direct all paths to proper places. The automatic values do not work when using extracted binaries with MSB.
Set ROOT_PATH to point where you have MaxScale sources
Set MYSQL_ROOT to point the extracted binary include directory
Set EMBEDDED_LIB to point lib directory under extracted binary
Set ERRMSG to point share/english under extracted binary
Building the Sources
Now that configuration is edited to match the paths, building will be easy. Just follow these simple steps
If all went nicely without errors now you should have ~/usr/local/skysql/ directory created which has MaxScale build under it. MaxScale includes Documentation directory which has related documents for debugging and diagnostics, configuration, and release notes/changelog. The configuration document has all possible values available and some use cases.
Environment and Configuring MaxScale
For these tests, I've created Galera Cluster with three nodes, all nodes run on localhost with ports 18112,18113, and 18114. This is something normally would not be seen and frankly it is completely pointless to run multiple nodes as cluster on same computer. That just ends up's filled up memory and poor performance. So please do disregard that fact, this is just a tutorial. Just pointing out as the node addresses are needed soonish.
The configuration file lies under MaxScale/etc/ and is named MaxScale.cnf. There is template file under the directory that could be used for configuration just by copying it and changing the server addresses and username/password combinations.
First let set threads
MaxScale documentation states that it can handle great amount of connections with single thread. If you find issues arising please do increase the value.
Servers, change the addresses and ports to those you are using.
[server1] type=server address=127.0.0.1 port=18112 protocol=MySQLBackend [server2] type=server address=127.0.0.1 port=18113 protocol=MySQLBackend [server3] type=server address=127.0.0.1 port=18114 protocol=MySQLBackend
Now comes the fiddly bits, setting up the listeners and monitor. Not really though, the MaxScale configuration ends up being easier than MySQL configuration to be honest. The basic configuration for Write Split and Read Connection Routers are simple:
[RW Split Router] type=service router=readwritesplit servers=server1,server2,server3 # Avoid spaces, there is feature request open for trimming of spaces user=root passwd=msandbox [Read Connection Router] type=service router=readconnroute servers=server1,server2,server3 user=root passwd=msandbox
Change the username and password to match your environment. Preferrably would recommed to create user specified for the task. The user only needs privilege to SELECT from INFORMATION_SCHEMA.GLOBAL_STATUS. I'm using the SMB as base so usernames and passwords are well known fact and these servers do not see the internet nor other way around.
Then to configure listeners.
[RW Split Listener] type=listener service=RW Split Router protocol=MySQLClient port=18118 [Read Connection Listener] type=listener service=Read Connection Router protocol=MySQLClient port=18119
Note that both are using different ports. If you want to use both routers, you need to modify your application to support writes to different port. This obviously is rather hard usually. To avoid the problem, you can leave out the Read Connection Listener all together and just use R/W splitter. Also as addition, could recommend to add config variable
router_options=synced to RW splitter. This follows the galera cluster and issues queries only to synced slaves.
Lastly lets add Galeramon monitor
[MySQL Monitor] type=monitor module=galeramon servers=server1,server2,server3 user=root passwd=msandbox
This is the basic configuration at it's best. This enables MaxScale to run smoothly, last thing we need to add is start script.
Browse your way to ~/usr/local/skysql/ and create new file start.sh with your favourite editor. Copy paste next bash script to file and save:
#!/bin/bash # export MAXSCALE_HOME=/home/admin/usr/local/skysql/MaxScale $MAXSCALE_HOME/../bin/maxscale
Obviously you could store environment variable MAXSCALE_HOME and simply call /home/admin/usr/local/skysql/bin/maxscale to fire up maxscale. Now you can run the start script with sh start.sh this will output similar to next outcome if everything goes like planned:
SkySQL MaxScale 0.7.0 Tue Jun 17 13:35:37 2014 ------------------------------------------------------ Info : MaxScale will be run in a daemon process. See the log from the following log files : Error log : /home/user/usr/local/skysql/MaxScale/log/skygw_err1.log Message log : /home/user/usr/local/skysql/MaxScale/log/skygw_msg1.log Trace log : /home/user/usr/local/skysql/MaxScale/log/skygw_trace1.log Debug log : /home/user/usr/local/skysql/MaxScale/log/skygw_debug1.log Listening MySQL connections at 0.0.0.0:18118 Listening MySQL connections at 0.0.0.0:18119 # if got read connection listener setup also
If the listeners do not wake up, check the skygw_err1.log for possible errors.
Testing the Setup
To test setup you can use the test database which usually comes with all MySQL instances. First lets create table to be used:
mysql -u msandbox -pmsandbox -h127.0.0.1 --port=18118 test -e"CREATE TABLE demo(id int auto_increment, serverid varchar(32), PRIMARY KEY (id));"
Then run next command few times around:
mysql -u msandbox -pmsandbox -h127.0.0.1 --port=18118 test -e"INSERT INTO demo (serverid) VALUES(@@server_id)";
Lets see the results then:
mysql -u msandbox -pmsandbox -h127.0.0.1 --port=18118 test -e"SELECT *, @@server_id FROM demo";
With good luck you'll see @@server_id to change between tries and serverid to stay the same through all rows. With galeramon it does not change that often so balancing works perfectly. To test the balancing I used one of my demo sites and once again using siege with four concurrent users for 30 seconds with urls file generated before hand. With one page making multiple heavy quetions. MaxScale has chosen server2 to be master in my case. Following the Questions status variable: approx values due to monitor rising it slowly.
Bit uneven distribution between server 1 and 3. But clearly shows that server2 was completely skipped from the test run.
There are loads more options with MaxScale. I do recommend to browse your way to Documents directory or read the same document online from http://www.skysql.com/sites/default/files/MaxScale-Configuration-Guide.pdf
MaxScale is definitely a great piece of software still missing some features. Like stopping/restarting the MaxScale server does not seem to be available so you need to kill the process manually and fire the server again. This is quite annoying when doing configuration changes. But no doubt this will be included with the first GA release which I would guess by from version number is still some time away in the future.
What else, even the install was quite tedious on the first go I have not found bugs from MaxScale yet. Query All Filter is something I would not use right now as it creates log file per connection, where I'd rather have log file per server. Otherwise great filter as it logs all queries that go through MaxScale. Connecting PHP application to MaxScale went seamlessly and no problems with PDO like there shouldn't be.