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:

In full:

[mysqld]
#mysql settings
bind-address=192.168.0.10
default-storage-engine=innodb
binlog_format=ROW
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=2
innodb_doublewrite=1
query_cache_size=0

#galera settings
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_node_name=db1
wsrep_cluster_name=artspan_cluster
wsrep_sst_receive_address=192.168.0.10
wsrep_node_address=192.168.0.10
wsrep_cluster_address=gcomm://10.0.0.10,10.0.0.20.10.0.0.30
wsrep_retry_autocommit=10
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth='username:password'
wsrep_slave_threads=8
wsrep_provider_options=gcache.size=512M

Network setup

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.

wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_doublewrite=1
query_cache_size=0
wsrep_on=ON

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';