CentOS

If you test Galera synchronous replication with Percona XtraDB Cluster or MariaDB Galera Cluster you must have tried to use a load balancer like HA Proxy or Galera Load Balancer.

On very heavy load, you may have issue with a large amount of TCP in TIME_WAIT like this one:

tcp        0      0 127.0.0.1:59035         127.0.0.1:3306          TIME_WAIT

This can lead to a TCP port exhaustion as explained on this post.

On HA proxy since version 1.4.19, you can use the nolinger option also on TCP backends. This terminate the connection (TCP RST) as soon as the loadbalancer finished the communication.

The counter part is that Aborted_clients status counter in MySQL increases with each connections' end. This counter becomes then useless.

This option is not available on glb (with -l parameter) if you apply the patch attached to this post.

I provide also rpm package with the patch applied :

Name        : glb
Version     : 0.9.2
Release     : 2
Architecture: x86_64
Install Date: (not installed)
Group       : Productivity/Networking/Routing
Size        : 208489
License     : GNU General Public License version 2 or later (GPL v2 or later)
Signature   : (none)
Source RPM  : glb-0.9.2-2.src.rpm
Build Date  : mer 27 fév 2013 17:15:54 CET
Build Host  : percona1
Relocations : (not relocatable)
URL         : http://www.codership.com/products/galera-load-balancer
Summary     : TCP Connection Balancer
Description :
glb is a simple user-space TCP connection balancer made with scalability and
performance in mind. It was inspired by pen, but unlike pen its functionality
is limited only to balancing generic TCP connections.

Features:
* list of backend servers is configurable in runtime.
* supports server "draining", i.e. does not allocate new connections to server,
  but does not kill existing ones, waiting for them to end gracefully.
* on Linux 2.6 and higher glb uses epoll API for ultimate performance.
* glb is multithreaded, so it can utilize multiple CPU cores. In fact even on a
  single core CPU using several threads can significantly improve performance
  when using poll()-based IO.
* connections are distributed proportionally to weights assigned to backend
  servers.
* this is a patched version providing SO_LINGER

Example:

[root@macbookair ~]# glbd -K -l --threads 6 --control 127.0.0.1:4444 127.0.0.1:3308 127.0.0.1:3306
glb v0.9.2 (epoll)
Incoming address:       127.0.0.1:3308 , control FIFO: /tmp/glbd.fifo
Control  address:        127.0.0.1:4444 
Number of threads: 6, max conn: 493, policy: 'least connected', top: NO, nodelay:
 ON, keepalive: OFF, defer accept: OFF, verbose: OFF, linger: ON, daemon: NO
Destinations: 1
   0:       127.0.0.1:3306 , w: 1.000
Router:
------------------------------------------------------
        Address       :   weight   usage    map  conns
      127.0.0.1:3306  :    1.000   0.000    N/A      0
------------------------------------------------------
Destinations: 1, total connections: 0 of 493 max

Pool: connections per thread:     0     0     0     0     0     0

If you test it please post a comment.

Codership released a new version of the load balancer for Galera. I made new rpms but I forgot to share them ;)

Here they are !

This new version provides ] a "single" balancing policy where all connections are directed to a single destination chosen by highest weight, a --top option that forces balancing only between the destinations with the highest weight, and a SO_KEEPALIVE option on destination connections (default: on) for timely detection of the destination failure.

I use Vagrant to test almost everything. But since I upgraded to VirtuabBox 4.2.x and CentOS 6 as guess OS, I had the impression that everything was slower... and I get use to it...

But this week-end while preparing selinux policies for Percona XtraDB Cluster, I noticed that it was really slow.... really really very very slooooow :'-(

And I found the reason ! I first tried to add some kernel parameters like :

noacpi 
noapic 
divider=10 
notsc

But that didn't help.

Then I just enabled IO APIC on the VM's configuration and it worked much faster ! The boot of the machine was faster and in my case loading selinux policies too !

Have a look to the difference:

Without IO APIC:

[root@node2 ~]# time semodule -i percona-xtradb-cluster-full.pp

real	6m3.646s
user	1m34.430s
sys	3m42.805s

With IO APIC:

[root@node2 ~]# time semodule -i percona-xtradb-cluster-full.pp

real	0m14.611s
user	0m13.829s
sys	0m0.769s

To enable IO APIC from Vagrant, these are the parameters to use in your Vagrantfile:

config.vm.customize ["modifyvm", :id, "--memory", "256", "--ioapic", "on"]

2 days ago was released a new version of glb (Galera Load Balancer) that fixes connection problem (see the release note).

If you want to try it on CentOS 6, I built a rpm.

Recently (yesterday), I faced a problem with percona-playback when I tried to use a slow query log (captured on a customer's server) and it fails dramatically. The log files was 2.3G and the second query was already returning an error :-(

I seemed that I hit a bug that Aurimas, my colleague at Percona, already reported (bug 1035217): queries written on multi lines don't work !

My C++ knowledge is very old and also very limited, so I tried to understand and fix this issue myself. Mushu (dbasquare) already faced this problem and started to fix it before giving up as he needed to run it on Slowaris... Gently he sent me his patch that I modified to support my slow query log:

• queries on multi-lines
• queries being only a ; (semi-colon)
• queries finishing by another character than ; (with comments for example)

Attached to this post, you can find the rpm for CentOS 6 including that patch.

Baron announced today the release of Innotop 1.9.0..

Fedora provides Innotop 1.6.0 !

I have then build rpm for CentOS 6 to test it as soon as possible !

[update]: I made a typo (see comment bellow) in the requirement of perl-TermReadKey, I've fixed this in innotop-1.9.0-2.

As I wanted to test in my vagrant environment Yoshinori's MySQL replication booster, I needed to create rpms to be able to install it quickly and easily on those virtual machine without compiling it on the vm's.

So if you are interested in this nice replication solution and you are lazy to compile it, you can use my rpm's listed bellow.

Some links about the project:

• http://yoshinorimatsunobu.blogspot.hu/2011/10/making-slave-pre-fetching-work-better.html
• https://github.com/yoshinorim/replication-booster-for-mysql
• https://code.launchpad.net/mysql-replication-listener

mysql replication listener's rpm was built using modified specs from https://bitbucket.org/winebarrel/ruby-binlog/downloads

Update

I added the src.rpm as requested by nvidhive on #percona freenode.

If you have recently read Stewart's blog post on mysqlperformanceblog, related to Percona Playback and you wanted to test it but you were too lame^H^H^H^Hbusy to compile it (or it was just not an option), I've made an CentOS 6 rpm just for you ;-)

I hope this helps !

PS: those packages have not been tested yet but you can replace mysql-libs by Percona-Server-shared-compat

Since I use puppet to manage my machines (and the machines of customers), I noticed that I had more packages installed then before, I noticed also obviously the same behavior in packages to update and bandwidth consumption during updates.

I realize that on 64bits machines, most of the time, the 32bits version of the packages managed by puppet were also installed.

This is what I did in my recipes before:

    package { "corosync":
        ensure => "installed",
        require => Yumrepo["clusterlabs"];
    }

This kind of package declaration installed then the two version of the package, in this case corosync and the dependencies too.

To avoid this I added the fact hardwaremodel and used the alias to keep my recipes consistent:

    package { "corosync.$hardwaremodel":
        ensure => "installed",
        alias => "corosync",
        require => Yumrepo["clusterlabs"];
    }

Hope this could help people having noticed the same behavior... or not :-)

Since I tested it 4 years ago, I like Open-Xchange (even if I'm not a java app fan). I like the layout and also all the feature it provides. The calendar is very complete.
For a customer where I set it up 4 years ago, I've migrated this service to a cluster running the last version.
The machines are fully installed via kickstart from a pxeboot (using cobbler)

This post describes the solution. The setup is based on CentOS and use the pair corosync / pacemaker as cluster.

The solution consists in two nodes where only one machine provides the service.

The components are :

- one ip balancing between the two nodes
- apache running on the "active/master" server (the server providing the service)
- open-xchange running on one node at the time
- funambol running on one node at the time
- openldap running on both machines in mirroring
- cyrus running on both machines as master/slave
- mysql running on both machines as master/master replication.

This is an overview of the crm:

Most of the needed steps are put in some puppet recipes to help the provisioning (you can find them on my github account)

With the cyrus-imapd delivered by default on redhat/centos, when the cyrus master starts without the slave running, cyrus won't reply for a long time... the bug we are hitting here as been resolved in newer version. I use cyrus-imapd 2.4.6, package from Simon Matter. You can find the source of this package here