Hi,
I would like to give you an overview regarding all my talks till june.

  • MySQL@Ceph (Ceph Day Frankfurt ./. 27.02.2014)
    Yes you missed that already :)
  • MySQL: PerformanceSchema (DOAG SIG - MySQL ./. 27.03.2014)
  • Galera Cluster für MySQL (DOAG SIG - MySQL ./. 27.03.2014)
  • Docker++ ./. Containervirtualisierung von Applikationen mit Merhwert (Linuxtag ./. 08.05.2014)
  • Docker: Not even a Hypervisor (Containers for OpenStack) (Linuxtag ./. 09.05.2014
  • Docker: LXC Applikationskontainer für jedermann (SLAC ./. 13.05.2014)

There is also a training about LXC and “newer” Linuxfeatures (Systemd, Upstart, Namespaces..) im giving at the Linuxhotal early may.

Viel Spaß :)

Erkan

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.

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

[mysqld]
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                  = 0.0.0.0

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

Tests

Reminder

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.

So

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

Replication

  • 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ß

Erkan

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   = 0.0.0.0"      >>/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

EXPOSE 3306
USER mysql
ENTRYPOINT mysqld

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 
51
> 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   0.0.0.0:5060->3306/tcp   lonely_pare

Have fun \o/
Erkan


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: 172.16.8.5, 172.16.8.6 and 172.16.8.4. 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: 172.16.8.5, 172.16.8.6, 172.16.8.4) 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:

[mysqld]
#mysql settings
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0
#galera settings
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="my_wsrep_cluster"
wsrep_cluster_address="gcomm://172.16.8.5,172.16.8.6,172.16.8.4"
wsrep_sst_method=rsync

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.

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

Hi,
Im honored to give a training (german) at the well known Linuxhotel
There are two trainings.
The first one is about newer techniques shipped with Linux Linux Admin Update). 
The other one is about LXC \o/
While attending the first one is fine, it is recommended to attend the Admin Update T
training if you go for LXC. 


Talks at DOAG 2013

| Keine Kommentare | Keine TrackBacks
Ahoi,
Attending DOAG 2013 next week, Im going to give two talks (Applikationsvirtualisierung mit LXC and MySQL Replikation).
Also Im invited to attend a MySQL Expert Panel, where you can let a bunch of MySQL Experts discuss your questions ;)
DOAG is afaik the biggest Oracle Event (and even not driven by Oracle) in Europe.
A good place to have nice discussions and learn a lot of new stuff :)

Hope to see you
Erkan Yanar



5.6.Debian Installing MySQL 5.6 using the package provided via dev.mysql.com is some kind of silly.
Using a package you expect the packagemanager to solve conflicts and dependencies and you expect to have a running application after install.
But ..
I downloaded the Debian Package.

root@debianmy56:/var/tmp# dpkg -i mysql.deb 
Selecting previously deselected package mysql.
(Reading database ... 8799 files and directories currently installed.)
Unpacking mysql (from mysql.deb) ...
Setting up mysql (5.6.14) ...
Ok - beside the unusual name for the package - everything worked fine. Lets see if we got mysql user and group:
root@debianmy56:/var/tmp# getent passwd mysql
root@debianmy56:/var/tmp# getent group mysql
Oha, is there an init-script?
root@debianmy56:/var/tmp# ls /etc/init.d/| grep -i mysql
Ok thats consequent and of course the package will not set the PATH for you:
root@debianmy56:/var/tmp# PATH=$PATH:/opt/mysql/server-5.6/bin/
We also get dependencies to solve and don't expect the database to be installed:
root@debianmy56:/var/tmp# mysqld --console
mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
root@debianmy56:/var/tmp# apt-get install libaio1
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following NEW packages will be installed:
  libaio1
0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
Need to get 0 B/7928 B of archives.
After this operation, 73.7 kB of additional disk space will be used.
debconf: delaying package configuration, since apt-utils is not installed
Selecting previously deselected package libaio1.
(Reading database ... 18390 files and directories currently installed.)
Unpacking libaio1 (from .../libaio1_0.3.107-7_amd64.deb) ...
Setting up libaio1 (0.3.107-7) ...
root@debianmy56:/var/tmp# mysqld --console
2013-10-12 22:52:57 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-10-12 22:52:57 307 [Warning] Can't create test file /opt/mysql/server-5.6/data/debianmy56.lower-test
2013-10-12 22:52:57 307 [Warning] Can't create test file /opt/mysql/server-5.6/data/debianmy56.lower-test
mysqld: Can't change dir to '/opt/mysql/server-5.6/data/' (Errcode: 2 - No such file or directory)
2013-10-12 22:52:57 307 [ERROR] Aborting
And so on.
You use packages to solve some of the problems you got using only tarballs:
# dpkg-query -W -f='${Provides}\n${Depends}\n${Conflicts}\n' mysql



#
But the Debian Package from dev.mysql.com is just a tarball in disguise.
Packages - requiring mysqld - are going to tell the packetmanager to install mysqld.
Have fun :/

Regards
Erkan

Im a Pacemaker fanboy for sure.
If you use the OCF please don't take the default value for the mysqld binary.
The setting
OCF_RESKEY_binary_default="/usr/bin/safe_mysqld"
is wrong. 
Why?
As it interfere with the acounting pacemaker is already doing. 
Take the path to your mysqld binary.

Enjoy
Erkan

Being a fanboy of Spider for a long time. Hoping to see in shipped with MySQL. Now Im glad to see Spider shipping with MariaDB 10.0.4. It is not activated/installed per default, so as it is a SE plugin just do a

mysql> INSTALL PLUGIN spider SONAME 'ha_spider.so';

As Spider provides some funcitions and does some accounting in his own tables you also have got to

# cat /usr/share/mysql/install</em>spider.sql| mysql -u root

There you go. There is an older (german) presentation about Spider from me.
Frederic did on too :)

Let the Spider begin \o/