Tuning MariaDB Galera 10.1
For the past couple of years I have really been a advocate for MariaDB and Galera. I personally don’t think all the details are well documented so I thought I would cover some of the highlights which have helped me tune Galera to eek out the best performance I can.
The following config is for MariaDB 10.1 YMMV:
10.0.0.0/24 - This is my public network 192.168.0.0/24 - This is my private network
According to MariaDB site this is the minimum required to get MariaDB Galera 10.1 cluster up.
bind-address=0.0.0.0 - For security reasons I typically don’t like to use the public NIC for traffic. I would typically recommend binding to the private network NIC.
innodb_flush_log_at_trx_commit=0 - We are making sure InnoDB log buffer is written directly rather then during a commit. We are not necessarily worried about loosing anything. Just make sure your cluster has been properly distributed for fault tolerance.
wsrep_sst_receive_address=192.168.0.10 - Here we are setting the interface the donor node will send its data via. This will default to the primary NIC on the box so I am setting it to our internal LAN. This could be a 3rd NIC if you wanted to really squeeze out a little more performance. If this box was behind a NAT this would be set to it’s public IP.
wsrep_node_address=192.168.0.10 - Now we have set the interface the cluster communication would happen on. Also will default to the primary NIC on that box.
wsrep_retry_autocommit=10 - I typically don’t recommend allowing the application to read/write for all nodes. It will cause deadlocks once and a while which you may not catch(this is a good plug for APM monitoring). What we are doing is allowing the query to be retried up to 10 times. A better method would be to split the reads and writes across different load balancers or in code. Another was is to use another awesome piece of software MaxScale which will route the queries to the correct boxes. More to come on this in a later post.
wsrep_sst_method=xtrabackup-v2 - The default method that most people choose is rsync. It’s build-in to most distributions, it works reasonably well. I however have found that Percona’s XtraBackup is much faster in my testing then a pure rsync, though most people indicate it is slower. More important with large data sets if you have to perform a SST so it’s non-blocking which I have found important.
wsrep_sst_auth=’username:password’ - Make sure you add a user in MySQL to allow this user.
CREATE USER 'sst'@'localhost' IDENTIFIED BY 'somepass'; GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sst'@'localhost';
Brian has spent more than fifteen years as Unix enthusiast, specalising in systems administration and solutions architecture for SMB's to Fortune 100 companies. His career has focused on emerging and niche technologies to major advancements in the industry - most notibly cloud.