Maxscale
MariaDB Maxscale
Introduction
Since long HAproxy is the standard loadbalancing solution for people who want a simple userspace daemon for MySQL loadbalancing. However with the availability of Maxscale we have a tool which has been specifically designed to loadbalance for MySQL instances (Galera clusters and/or Master-Slave systems) and offers some attractive features. Performance-wise we have not finished out testing, but first experiments indicate that we do not observe performance regressions from HAproxy to Maxscale.
Maxscale comes with built-in MySQL server node status monitoring, thus no external health check service is required, which simplifies the setup significantly.
Links:
- MaxScale documentation on GitHub: https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Documentation-Contents.md
- Complete list of configuration options: https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Getting-Started/Configuration-Guide.md
Installation
Follow instructions on https://mariadb.com/kb/en/mariadb-enterprise/maxscale-21-installation-guide/ to install the software on your system.
Configuration
Maxscale requires a user for monitoring et al.
CREATE USER 'maxscale'@'loadbalancer.fqdn' IDENTIFIED BY 'sitpavOfEyt5'; GRANT SELECT on mysql.user TO 'maxscale'@'loadbalancer.fqdn'; GRANT SELECT ON mysql.db TO 'maxscale'@'loadbalancer.fqdn'; GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'loadbalancer.fqdn'; GRANT SHOW DATABASES ON *.* TO 'maxscale'@'loadbalancer.fqdn'; GRANT REPLICATION CLIENT on *.* TO 'maxscale'@'loadbalancer.fqdn'; FLUSH PRIVILEGES;
The config file we use is given below. Since OX App Suite has its own built-in read/write separation, we don't need Maxscale's Read-Write Split feature; rather, we configure a Read Service with round-robin semantics, and a Write Service with a persistent active-passive behavior.
Do not confuse the wording Replication Monitor below with OX's builtin Replication Monitor (which we disable for fully synchronous Galera). It is just an identifier for the definition of Maxscale's built-in health check which is in this case checking Galera nodes (module=galeramon).
The Read Service and Write Service are different by their router_options -- effectively we define that for the Read Service all nodes which are synced are available, while for the for the Write Service only the node considered by Maxscale to be the master is being used.
The behavior which node is considered master can be changed by some configuration variables, most notably root_node_as_master which defines that onle the root node, i.e. the node with wsrep_local_index=0 will be used as master node. This setting makes sense if multiple distributed Maxscale instances are to be used, since it avoids the otherwise potential danger that different nodes pick different masters. However if you use only one (active) Maxscale instance per target cluster at a time (maybe with some active-passive HA setup), it might be beneficial to not use root_node_as_master, but rather disable_master_failback to reduce the number of master switches / failovers.
[maxscale] threads=4 # Server definitions # # Set the address of the server to the network # address of a MySQL server. # [db1] type=server address=db1.fqdn port=3306 protocol=MySQLBackend [db2] type=server address=db2.fqdn port=3306 protocol=MySQLBackend [db3] type=server address=db3.fqdn port=3306 protocol=MySQLBackend # Monitor for the servers # # This will keep MaxScale aware of the state of the servers. # MySQL Monitor documentation: # https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Monitors/MySQL-Monitor.md [Replication Monitor] type=monitor module=galeramon servers=db1, db2, db3 user=maxscale passwd=sitpavOfEyt5 monitor_interval=10000 # Service definitions # # Service Definition for a read-only service and # a read/write splitting service. # # ReadConnRoute documentation: # https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadConnRoute.md [Read Service] type=service router=readconnroute servers=db1, db2, db3 user=maxscale passwd=sitpavOfEyt5 router_options=synced [Write Service] type=service router=readconnroute servers=db1, db2, db3 user=maxscale passwd=sitpavOfEyt5 router_options=master # This service enables the use of the MaxAdmin interface # MaxScale administration guide: # https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Reference/MaxAdmin.md [MaxAdmin Service] type=service router=cli # Listener definitions for the services # # These listeners represent the ports the # services will listen on. # [Read Listener] type=listener service=Read Service protocol=MySQLClient port=4306 [Write Listener] type=listener service=Write Service protocol=MySQLClient port=4307 [MaxAdmin Listener] type=listener service=MaxAdmin Service protocol=maxscaled socket=default
Operations
Maxscale offers a command line tool maxadmin offering an impressive set of features for operations.