This article will explain how to setup HA for MySQL Router as described in a previous article about where should the router stand. For this setup, I will use Pacemaker (part of RedHat High Availability Add-on and available on RHEL,…
In this article, I will show you how it’s possible to perform an online migration from a 3 members Galera cluster setup (in this case I’m using PXC 5.7.14) to a 3 members MySQL Group Replication cluster setup (MySQL Community…
Since yesterday, the new Percona Server (5.5.37-35.0) includes an audit plugin. This is the announcement on mysqlperformance blog. I needed to have this feature for a customer running an older version of Percona Server (5.5.33-31.1), so I back ported the plugin (revision 654) removing some extra info that is not available on previous version of Percona Server. The information above in struct mysql_event_general (include/mysql/plugin_audit.h) were not yet present:
MYSQL_LEX_STRING general_host; MYSQL_LEX_STRING general_sql_command; MYSQL_LEX_STRING general_external_user; MYSQL_LEX_STRING general_ip;Additionally, as I needed to parse the audit log quickly, I added a new format (CSV) to replace both implementation of XML output (OLD & NEW). This is an example of the new output:
AUDIT_RECORD - Audit::35531_2014-05-07T06:15:32::2014-05-07T06:15:36 UTC::5.5.33-31.1::--basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/percona1.err --pid-file=/var/lib/mysql/percona1.pid::x86_64-Linux AUDIT_RECORD - Connect,35532_2014-05-07T06:15:32,2014-05-07T06:16:04 UTC,1,0,root,root,,,localhost, AUDIT_RECORD - Query::35533_2014-05-07T06:15:32::2014-05-07T06:16:04 UTC::1::0::select @@version_comment limit 1::root[root] @ localhost  AUDIT_RECORD - Query::35534_2014-05-07T06:15:32::2014-05-07T06:16:20 UTC::1::0::SELECT DATABASE()::root[root] @ localhost  AUDIT_RECORD - Init DB::35535_2014-05-07T06:15:32::2014-05-07T06:16:20 UTC::1::0::(null)::root[root] @ localhost  AUDIT_RECORD - Query::35536_2014-05-07T06:15:32::2014-05-07T06:16:20 UTC::1::0::show databases::root[root] @ localhost  AUDIT_RECORD - Query::35537_2014-05-07T06:15:32::2014-05-07T06:16:20 UTC::1::0::show tables::root[root] @ localhost  AUDIT_RECORD - Field List::35538_2014-05-07T06:15:32::2014-05-07T06:16:20 UTC::1::0::::root[root] @ localhost  AUDIT_RECORD - Field List::35539_2014-05-07T06:15:32::2014-05-07T06:16:20 UTC::1::0::::root[root] @ localhost  AUDIT_RECORD - Query::35540_2014-05-07T06:15:32::2014-05-07T06:16:23 UTC::1::0::show databases::root[root] @ localhost  AUDIT_RECORD - Query::35541_2014-05-07T06:15:32::2014-05-07T06:16:27 UTC::1::0::SELECT DATABASE()::root[root] @ localhost  AUDIT_RECORD - Init DB::35542_2014-05-07T06:15:32::2014-05-07T06:16:27 UTC::1::0::(null)::root[root] @ localhost  AUDIT_RECORD - Query::35543_2014-05-07T06:15:32::2014-05-07T06:16:29 UTC::1::0::show tables::root[root] @ localhost  AUDIT_RECORD - Query::35544_2014-05-07T06:15:32::2014-05-07T06:16:34 UTC::1::0::select * from t::root[root] @ localhost The patch is available below and the compiled plugin for Centos 6 x86_64 for Percona Server 5.5.33-rel31. Update: I've now added the code in launchpad and changed the default audit format to CVS, so the first time the plugin is loaded, CVS is used and no need to restart mysqld to enable CVS.
If you want to be able to use an iPhone with Fedora 20 to copy photos or songs, you will need to upgrade libimobiledevice to 1.1.6. If you don't on the phone you will be constantly prompted to trust or not the computer: You can find the rpms for libimobiledevice 1.1.6 here.
I recreated rpms for the latest revision (119) of sysbench. I made them available for Fedora 19 and CentOS 6. Enjoy !
My colleague Kenny hit a bug with Innotop and MySQL 5.6. He submitted a bug with a patch. This is a new pre-release rpm of Innotop (1.9.0-3) that includes that patch. Enjoy it ! [UPDATE] New rpms are now available directly on Innotop's download page
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_WAITThis 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_LINGERExample:
[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 0If 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 notscBut 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.805sWith IO APIC:
[root@node2 ~]# time semodule -i percona-xtradb-cluster-full.pp real 0m14.611s user 0m13.829s sys 0m0.769sTo 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)