Percona Live MySQL Conference & Expo 2013

For my first participation to THE MySQL event of the year, I have the honour of delivering a full day tutorial on Opetational DBA in a nutshell with my two Belgian colleagues (call us the be-team ;) ) and a lighting talk related to MySQL database schema migration: how to version them and manage those operations. If you don't want to miss THE essential event of the MySQL ecosystem, use code "SpeakMySQL" at checkout for 15% off! Register here: http://bit.ly/14au67Z. See you soon there !

nolinger option for Galera Load Balancer (glb)

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.

Percona XtraDB Cluster “bootstrap” option for init script

If you followed previous post about init script (here and here) for Percona XtraDB Cluster, I proposed a new solution to standardize the bootstrap of the cluster in debian/ubuntu and redhat/centos instead of using 2 different ways. You can read about this on launchpad bug #1152764. So now to start the first node of a cluster when no other node is already running, you just need to use bootstrap command with the init script like bellow:
# /etc/init.d/mysql bootstrap
Starting MySQL (Percona XtraDB Cluster)..... SUCCESS!
I attached the patches here too.

Percona XtraDB Cluster init script for Debian/Ubuntu patch (version 2)

Recently I wrote a blog post with a patch for Percona XtraDB Cluster Init script. But this was more a dirty hack then a real debian/ubuntu patch as Jan pointed it to me. So I rewrote that patch to not work as the init script of RedHat/CentOS but more like it should work on Debian, Now to initialize the cluster, on the very first node that will start the cluster, you need to run the following command:
root@percona1:/etc/init.d# /etc/init.d/mysql initialize
 * Starting MySQL (Percona XtraDB Cluster) database server and initialize the cluster mysqld                                  [ OK ] 
 * Checking for corrupt, not cleanly closed and upgrade needing tables.
Please note, the new command initialize. I hope this patch is better for Debian afficionados ;-)

Galera Load Balancer: new rpm for glb 0.9.2

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.

CentOS 6 very slow with Vagrant

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"]

Percona XtraDB Cluster init script for Debian/Ubuntu patch

On mysqlperformanceblog, I wrote on what was an easy solution to initialize a PXC cluster without modifying the configuration file (my.cnf) but only by providing a parameter to the init script. (link to that post) Unfortunately, this works only on RedHat systems (including CentOS, Fedora... For Debian/Ubuntu, I wrote a minimal patch allowing that. The patch is attached to this post. Enjoy !

hamster-time-tracker for Fedora 18

To track my time I used hamster-applet with the previous version of Fedora and I really liked it. It integrates perfectly with gtg. This project hasn't be ported to Fedora 18, see bug 882788. I decided then to package the latest version of the project. The git repo is available here. You can see some screenshots here. If you enjoy or discover this cool project, I'm happy to make it available for Fedora 18. [UPDATE] I added the packages for F19 ;-)

GTG 0.3

Getting Things GNOME! has been released on November 6th but I was able to test it only tonight ;-) So far, I'm very pleased by this release and to be honest this seems to be the first time synchronization with Remember the Milk is working (I used it on my n900 and I plan to use it know on my android.... but I need to wait 22h before next sync) I've also installed the integration with gnome-shell very quickly and it seems awesome ! The development team as done a very nice job... If you plan to test it, I've packaged this release for Fedora 17.

Percona Playback – new non official rpm for CentOS

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.

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.