Posted

Jun 17, 2014

Tags

MariaDB, MaxScale, HA

Setting Up SkySQL MaxScale for MariaDB 10

[Outdated] Testing SkySQL MaxScale with MariaDB 10.0.11 Galera Cluster. Setup, prerequisites, building, configuration and verifying that everything works like should.

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.
Source: http://www.skysql.com/downloads/maxscale-mariadb-mysql-query-tool-downloads

 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:

  1. 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

  2. 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

  3. 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.

  1. Set ROOT_PATH to point where you have MaxScale sources
    example: /home/user/MaxScale

  2. Set MYSQL_ROOT to point the extracted binary include directory
    example: /usr/local/src/5.5.38/include

  3. Set EMBEDDED_LIB to point lib directory under extracted binary
    example: /usr/local/src/5.5.38/lib

  4. Set ERRMSG to point share/english under extracted binary
    example: /usr/local/src/5.5.38/share/english

  5. Save

Building the Sources

Now that configuration is edited to match the paths, building will be easy. Just follow these simple steps

  1. make depend

  2. make

  3. make install

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]
threads=1

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.

 

server1

server2

server3

Questions before

~1833

~1228

~1629

Questions after

22549

1463

12678

Bit uneven distribution between server 1 and 3. But clearly shows that server2 was completely skipped from the test run.

Closure

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.

Links to Articles and Demos

Share your Thoughts

0 Responses

Your email address will not be published

Please enter your name.
Please enter valid email address.
Please enter your website address.
Please enter your message.
TwitterX Error: Could not load tweets as Twitter responded with the error: 'Invalid or expired token.'.
-->

Contact me with email or add my account to skype

the_dunnock@outlook.com

the_dunnock@outlook.com