Januar 2014 Archive

What is it about?

I used to do some benchmarkstuff and blogged about it on my blog written in German. Im going to do testings and benchmarkings again:)

We are going to have a look into ‘benchmarking’ a 3-node Galera Cluster. The application (sysbench) is on a separate node accessing one node of the cluster. This would be the case in a i.e. VIP setup.


3 Galera Nodes

  • Virtual machines (OpenStack) provided by teuto.net
  • VCPU: 4
  • RAM: 4GB
  • OS: Centos 6.4-x86_64
  • MySQL-server-5.6.14wsrep25.1-1.rhel6.x86_64
  • galera-25.3.2-1.rhel6.x86_64

Separate sysbench node

  • Same specs as the Galera nodes
  • sysbench 0.5
  • oltp test on 5 tables 1000000 rows each (ca. 1.2GB)
  • A run took 60 seconds

MySQL Config

user                          = mysql
binlog_format                 = ROW
default-storage-engine        = innodb

innodb_autoinc_lock_mode      = 2
innodb_flush_log_at_trx_commit= 0
innodb_buffer_pool_size       = 2048M
innodb_log_buffer_size        = 128M
innodb_file_per_table         = 1

query_cache_size              = 0
query_cache_type              = 0
bind-address                  =

init_file                     = /etc/mysql/init
max_connections               = 2000

# Galera

wsrep_provider                = "/usr/lib64/galera/libgalera_smm.so"
wsrep_cluster_name            = deadcandance
wsrep_cluster_address         = "gcomm://$useyourown"/
wsrep_slave_threads           = 
wsrep_certify_nonPK           = 1
wsrep_max_ws_rows             = 131072
wsrep_max_ws_size             = 1073741824
wsrep_sst_method              = rsync



We are running in a hypervisor (and OpenStack) setup. Testing is in a way not reliable. Not only because of the hypervisor. We don’t know how the host, storage and network resources are consumed by other users also. So small variances are statistically irrelevant.

1. test: We use different settings for wsrep_slave_threads

for i in 1 4 8 16 24 32 48 64; do set wsrep_slave_threads=$i and run; done

galera compared

This surprised me as in another test I had different results. Im not sure if it is the oltp test or the “hardware” making a change of wsrep_slave_threads some kind of useless.

2. Test: Setting gcs.fc_limit to 512 (instead the default 16)

We could tune the replication part. See Flow Control.

galera flow_control

Ahh ok this helped. And in our setup it is fine to play with that settings. (Yes there are more. read the link :) But it is true? How does our Flow Control behaved lets hava a look at the WSREP_FLOW_CONTROL_PAUSED status variable:

galera flow_control

Ok there you see the cluster wasn’t paused that often anymore. But the values are still to high. We are going to have a look at this setting in future tests. Right now it is quite likely the machines couldn’t catch up. ‘

3. Test

Now we take one of the Galera runs and compare them with:

  • A stand alone MySQL having the same configuration.
  • A stand alone MySQL with sync_binlog und innodb_flush_log_at_trx_commit=1 set.
  • A stand alone MySQL with sync_binlog und innodb_flush_log_at_trx_commit=1 set with a Semisynchronous Replication running.

Semisynchronous Replication is often used for HA setups. The argument is to make sure the data is on (one) slave at least. As a fact this is wrong. But this is the use case.

galera flow_control

  • We see the Galera Replication ‘overhead’
  • We see the performance drop (overhead) to get some local storage consistency. But still we see Group Commit doing a good job in scaling.
  • We see the Semisynchronous Replication ‘overhead’

Lets see another graph comparing two different Galera runs with the Semisynchronous Replication run.

galera flow_control

Make up your own mind.


  • Galera is faster.
  • Galera is virtual synchron.
  • Galera easy Fail Over implementations because of the Mulit-Master technique.

Fake Semisync

Even it looks like Semisynchronous Replication is good for setups with a higher concurrency. Lets have a look at the RPL_SEMI_SYNC_MASTER_NO_TX status variable I monitored while doing the test.

galera flow_control

So it was no Semisynchronous Replication all the time. It switched back to asynchronous Replication. So Semisynchronous Replication could’t catch the workload either. Dropping back into Asynchronous Replication broke the consistency of the Data in the cluster. Thats where Galera reduce the performance (still higher than Semisynchronous Replication) to provide this consistency :)

Ok thats my friend the end

  • We had a simple setup
  • Different setups, distributions and ‘Hardware’ is going to be used.
  • If you had some ideas, feel free to ping/mail me.
  • As Im missing real(tm) hardware. Feel free to make me happy providing me access to that real hardware:)

Viel Spaß

Erkan :)

Galera Phrases

| Keine Kommentare | Keine TrackBacks

I confess I am a Galera fanboy. This post is going to present two slogans about Galera reminding you about Galera “limitations”. This is for the sake of user experience:)

Galera Phrases:

  • One Cluster
  • Replication

One Cluster

Think about Galera as One Cluster. As every transaction is committed virtually synchronously, the slowest node determines the (DML) speed of the cluster. This is true regarding the network too.


  • Galera is still some kind of replication. So keep in mind to provide PK as for (ROW based) Replication. There is a Featurerequest of mine to have a PK option to enforce creating of Tables with a PK. Please vote for it:)

  • With traditional MySQL Replication the slave might lag. This is not hurting the performance of the master. With Galera there is no lag. So if applying takes time it stalls the cluster (flow control). DDL statements run in Total Order Isolation (TOI) as a default. This will not only block the table. The whole cluster stalls till the DDL finishes.

Of course there is much more to tell. This two slogans are just a result of my experience with customers etc.

Viel Spaß


Regarding virtualization I am a LXC guy. Nevertheless Docker has won a lot of attention and I would like to show how to use MySQL with Docker.

What is Docker?

In fact Docker is a wrapper around LXC. It is fun to use. Docker has the philosophy to virtualize single applications using LXC. So in our example we are going to start a mysqld in a chroot environment encapsulated in his own Namespaces. (You can even set Cgroups resources.) One of the main points regarding Docker is the usage of a union filesystem (aufs). So when you start a Docker Container it gets his aufs mount and only changed data is written down.

Aufs is great for a lot of applications and sufficient for Database testing. I just want to share a simple - more educational, than effective - Dockerfile. Dockerfiles are the buildscripts for the Docker images.

Lets have a look at the Dockerfile:

FROM ubuntu
MAINTAINER erkan yanar <erkan.yanar@linsenraum.de>

ENV DEBIAN_FRONTEND noninteractive
RUN apt-get install -y  python-software-properties
RUN apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
RUN add-apt-repository 'deb http://mirror2.hs-esslingen.de/mariadb/repo/10.0/ubuntu precise main'
RUN apt-get update
RUN apt-get install -y mariadb-server
RUN echo "[mysqld]"                       >/etc/mysql/conf.d/docker.cnf
RUN echo "bind-address   ="      >>/etc/mysql/conf.d/docker.cnf
RUN echo "innodb_flush_method = O_DSYNC" >>/etc/mysql/conf.d/docker.cnf
RUN echo "skip-name-resolve"             >>/etc/mysql/conf.d/docker.cnf
RUN echo "init_file = /etc/mysql/init"   >>/etc/mysql/conf.d/docker.cnf
RUN echo "GRANT ALL ON *.* TO supa@'%' IDENTIFIED BY 'supa';" >/etc/mysql/init

USER mysql

You should change it the way you like. If you understand it, go on and optimize it. I.e. reduce the run stages:)

Lets quick build our image (named mysql)

> cat $DOCKERFILENAME | docker build -t mysql -

Great! Let’s for fun start 51 Containers:

> time for i in $(seq 10 60 ) ; do docker  run -d -p 50$i:3306   mysql ; done                                                              
real    0m27.446s
user    0m0.264s
sys     0m0.211s

All on my laptop. Think about the performance using KVM :)

>  docker ps | grep mysqld |wc -l 
> docker ps | head -2
CONTAINER ID        IMAGE               COMMAND             CREATED              STATUS              PORTS                    NAMES
6d3a5181cd56        mysql:latest        /bin/sh -c mysqld   About a minute ago   Up About a minute>3306/tcp   lonely_pare

Have fun \o/

This is a HOWTO about installing MariaDB Galera Cluster on Debian/Ubuntu.
It is because a lot of people had problems installing MariaDB Galera Cluster.
In the end elenst from #maria on freenode forced me to write a Howto :)
You will find out, installing MariaDB Galera Cluster is in fact quite easy and some kind of boring in the end.
This Howto is written (tested) on Debian 7.1 (Wheezy) and Ubuntu 12.04 (Precise).

What we need

In our setup we assume 3 nodes (node01, node02, node03) with one interface.
We assume following IP addresses:, and We need three packages installed on all nodes:

  • rsync
  • galera
  • mariadb-galera-server

As Galera does not ship with the distribution repositories, go for the repo configurator and follow the instructions to include the repository fitting your system.
Keep in mind to Choose "5.5" in Step 3 (Choose a Version). Doing this you can jump directly to Install Packages

Including Repository

Alternatively you can just take following steps.

Debian Wheezy

 # apt-get install python-software-properties
 # apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
 # add-apt-repository 'deb http://mirror3.layerjet.com/mariadb/repo/5.5/debian wheezy main'
 # apt-get update

Ubuntu Precise

 # apt-get install python-software-properties
 # apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
 # add-apt-repository 'deb http://mirror3.layerjet.com/mariadb/repo/5.5/ubuntu precise main'
 # apt-get update

Yes, they are nearly the same :)

Install Packages

(Just another shortcut for the impatient)

 # DEBIAN_FRONTEND=noninteractive apt-get install -y  rsync galera mariadb-galera-server

After you installed the packages you have a running MariaDB/MySQL on each node. But none of them is configured to run in/as a Galera Cluster.

Configuring Galera

So we got to do some configuration next. There is a MySQL/MariaDB configuration part and one part to configure Galera (starting with wsrep_). As we do the most basic and simple installation in this Howto, it is sufficient you just change the IP's (Remember:,, with your IP's.
This will be needed to define the wsrep_cluster_address Variable. The list of nodes a starting mysql contacts to get into the cluster.

The following configuration file has to be distributed on all nodes. We use a separate configuration file /etc/mysql/conf.d/galera.cnf with following settings:

#mysql settings
#galera settings

FYI: The shared library for wsrep_provider is provided by the installed galera package.
We could also change the cluster name by changing the value of wserp_cluster_name to fit our style.
This setting also works as a shared secret to control the access to the cluster.
With wsrep_cluster_address you see the IP addresses of our setup. The wsrep_sst_method tells what method to use to synchronise the nodes. While there are also mysqldump and xtrabackup available.
I prefer rsync because it is easy to configure (i.e. it does not need any credentials set on the nodes).
There will be a time you are going to consider using the xtrabackup method. Don't forget to install xtrabackup then.

Starting the Galera Cluster

Let us stop mysql on all nodes.

node01# service mysql stop
node02# service mysql stop
node03# service mysql stop

As we got the configuration file (galera.cnf) already distributed to all nodes, we start the first mysqld. This node initialises/starts the cluster (creates a GTID).

node01# service mysql start --wsrep-new-cluster

So just have a look if everything really worked well be checking the cluster size.

node01# mysql -u root -e 'SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size"'
| cluster size |
| 1            |

Ok great thats what we would expect. Ok now as the Cluster already exists, let the next nodes just start and join the cluster.

node2# service mysql start
[ ok ] Starting MariaDB database server: mysqld . . . . . . . . . ..
[info] Checking for corrupt, not cleanly closed and upgrade needing tables..
node01:/home/debian# ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)

We ignore the error for now. This node is still starting fine.
Let's do a quick check. As we run a cluster it is not important if we check on node01 or node02.

node01# mysql -u root -e 'SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size"'
| cluster size |
| 2            |

Very nice. Now lets start the third node:

node3# # service mysql start
[ ok ] Starting MariaDB database server: mysqld . . . . . . . . . ..
[info] Checking for corrupt, not cleanly closed and upgrade needing tables..
node03:/home/debian# ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)

node03# mysql -u root -e 'SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size"'
| cluster size |
| 3            |

Ok we are finished. We got a running MariaDB Galera Cluster \o/

Having fun with Debian/Ubuntu init scripts

But we've got to fix some things because Debian/Ubuntu oddities.
As we saw already the error while starting the node.
What happened? Debian/Ubuntu uses a special user ('debian-sys-maint'@'localhost') in there init script.
Where the credentials for that user are stored in /etc/mysql/debian.cnf.
This user is used to make some checks starting MySQL. Checks I don't think belong into a service script anyway.
We could simply ignore it, but the user user is also used to shutdown the mysqld.
This is also not required, as a SIGTERM is sufficient to shutdown the mysqld :/
As we copied the data from node01 to all other nodes, the credentials in /etc/mysql/debian.cnf are not fitting on node02 and node03.
Thats why we will not be able to shutdown mysql on any of these nodes.

node02# service mysql stop
[FAIL] Stopping MariaDB database server: mysqld failed!

So we've got to fix it, by copying /etc/mysql/debian.cnf from the first node (node01) to all other nodes. So data and configuration file have the same data again.

After that we are able to shutdown the daemon again:

node02# service mysql stop
[ ok ] Stopping MariaDB database server: mysqld.

So if we would have a proper init script the Howto would have been even shorter ;)
Follow the Bug :)

Ok enjoy your MariaDB Galera Cluster and have fun!

Erkan Yanar

Thx to teuto.net for providing me an OpenStack tenant, so I can run the tests for that Howto.

Über dieses Archiv

Diese Seite enthält alle Einträge von erkules del inglese von neu nach alt.

November 2013 ist das vorherige Archiv.

März 2014 ist das nächste Archiv.

Aktuelle Einträge finden Sie auf der Startseite, alle Einträge in den Archiven.


Powered by Movable Type 4.23-en