You are here

MySQL Tech-Feed (en)

Making HAProxy High Available for MySQL Galera Cluster

Shinguz - Sun, 2014-12-14 18:37
Taxonomy upgrade extras: HAProxyload balancerGalera ClusterVIPvirtual IPHigh Availabilityha

After properly installing and testing a Galera Cluster we see that the set-up is not finished yet. It needs something in front of the Galera Cluster that balances the load over all nodes.
So we install a load balancer in front of the Galera Cluster. Typically nowadays HAProxy is chosen for this purpose. But then we find, that the whole Galera Cluster is still not high available in case the load balancer fails or dies. So we need a second load balancer for high availability.
But how should we properly fail-over when the HAProxy load balancer dies? For this purpose we put a Virtual IP (VIP) in front of the HAProxy load balancer pair. The Virtual IP is controlled and fail-overed with Keepalived.

Installation of HAProxy and Keepalived

First some preparations: For installing socat we need the repoforge repository:

shell> cd /tmp shell> wget shell> yum localinstall rpmforge-release-0.5.3-1.el6.rf.x86_64.rpm shell> yum update shell> yum install socat

Then we can start installing HAProxy and Keepalived:

shell> yum install haproxy keepalived shell> chkconfig haproxy on shell> chkconfig keepalived on

We can check the installed HAProxy and Keepalived versions as follows:

shell> haproxy -v HA-Proxy version 1.5.2 2014/07/12 shell> keepalived --version Keepalived v1.2.13 (10/15,2014)
Configuration of HAProxy

More details you can find in the HAProxy documentation.

shell> cp /etc/haproxy/haproxy.cfg /etc/haproxy/haproxy.cfg.bak shell> cat << _EOF >/etc/haproxy/haproxy.cfg # # /etc/haproxy/haproxy.cfg # #--------------------------------------------------------------------- # Global settings #--------------------------------------------------------------------- global # to have these messages end up in /var/log/haproxy.log you will # need to: # # 1) configure syslog to accept network log events. This is done # by adding the '-r' option to the SYSLOGD_OPTIONS in # /etc/sysconfig/syslog # # 2) configure local2 events to go to the /var/log/haproxy.log # file. A line like the following can be added to # /etc/sysconfig/syslog # # local2.* /var/log/haproxy.log # log local2 chroot /var/lib/haproxy pidfile /var/run/ maxconn 1020 # See also: ulimit -n user haproxy group haproxy daemon # turn on stats unix socket stats socket /var/lib/haproxy/stats.sock mode 600 level admin stats timeout 2m #--------------------------------------------------------------------- # common defaults that all the 'frontend' and 'backend' sections will # use if not designated in their block #--------------------------------------------------------------------- defaults mode tcp log global option dontlognull option redispatch retries 3 timeout queue 45s timeout connect 5s timeout client 1m timeout server 1m timeout check 10s maxconn 1020 #--------------------------------------------------------------------- # HAProxy statistics backend #--------------------------------------------------------------------- listen haproxy-monitoring *:80 mode http stats enable stats show-legends stats refresh 5s stats uri / stats realm Haproxy\ Statistics stats auth monitor:AdMiN123 stats admin if TRUE frontend haproxy1 # change on 2nd HAProxy bind *:3306 default_backend galera-cluster backend galera-cluster balance roundrobin server nodeA maxconn 151 check server nodeB maxconn 151 check server nodeC maxconn 151 check _EOF
Starting and testing HAProxy

The HAProxy can be started as follows:

shell> service haproxy start

and then be checked either over the socket:

shell> socat /var/lib/haproxy/stats.sock readline prompt > show info > show stat > help

or over your favourite web browser entering the username and password (monitor:AdMiN123) specified in the configuration file above:

To check the application over the load balancer we can run the following command:

shell> mysql --user=app --password=secret --host= --port=3306 --exec="SELECT @@wsrep_node_name;" +-------------------+ | @@wsrep_node_name | +-------------------+ | Node C | +-------------------+ shell> mysql --user=app --password=secret --host= --port=3306 --exec="SELECT @@wsrep_node_name;" +-------------------+ | @@wsrep_node_name | +-------------------+ | Node A | +-------------------+ shell> mysql --user=app --password=secret --host= --port=3306 --exec="SELECT @@wsrep_node_name;" +-------------------+ | @@wsrep_node_name | +-------------------+ | Node B | +-------------------+
Configuration a Virtual IP (VIP) with Keepalived

Now we have 2 HAProxy load balancers. But what happens if one of them fails. Then we do not want to reconfigure our application to work properly again. The fail-over should happen automatically. For this we need a Virtual IP which should automatically fail-over.

More details you can find in the Keepalived documentation and the keepalived user guide.

shell> cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak cat << _EOF >/etc/keepalived/keepalived.conf # # /etc/keepalived/keepalived.conf # global_defs { notification_email { root@localhost } # Change email from on lb2: notification_email_from lb1@haproxy1 router_id HAPROXY } vrrp_script chk_haproxy { script "killall -0 haproxy" interval 2 weight 2 } vrrp_instance GALERA_VIP { interface eth1 virtual_router_id 42 # Higher priority on other node priority 101 # 102 advert_int 1 # notify "/usr/local/bin/" virtual_ipaddress { dev eth1 } track_script { chk_haproxy } authentication { auth_type PASS auth_pass secret } } _EOF
Starting and testing Keepalived

To test the keepalived we can run the following command:

shell> keepalived -f /etc/keepalived/keepalived.conf --dont-fork --log-console --log-detail ^C

To finally start it the following command will serve:

shell> service keepalived start

To check the Virtual IP the following command will help:

shell> ip addr show eth1

And then we can check our application over the VIP:

shell> mysql --user=app --password=secret --host= --port=3306 --exec="SELECT @@wsrep_node_name;"

failed MySQL DDL commands and Galera replication

Shinguz - Tue, 2014-12-09 15:45
Taxonomy upgrade extras: galerareplicationDDLTOIRSU

We have recently seen a case where the following command was executed on a Galera Cluster node:

SQL> GRANT SUPER ON userdb.* TO root@; ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
2014-12-09 14:53:55 7457 [Warning] Did not write failed 'GRANT SUPER ON userdb.* TO root@' into binary log while granting/revoking privileges in databases. 2014-12-09 14:53:55 7457 [ERROR] Slave SQL: Error 'Incorrect usage of DB GRANT and GLOBAL PRIVILEGES' on query. Default database: ''. Query: 'GRANT SUPER ON userdb.* TO root@', Error_code: 1221 2014-12-09 14:53:55 7457 [Warning] WSREP: RBR event 1 Query apply warning: 1, 17 2014-12-09 14:53:55 7457 [Warning] WSREP: Ignoring error for TO isolated action: source: c5e54ef5-7faa-11e4-97b0-5e5c695f08a5 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 4 trx_id: -1 seqnos (l: 4, g: 17, s: 15, d: 15, ts: 113215863294782)

According to the error message it looks like this command is done in Total Order Isolation (TOI) mode during the Rolling Schema Upgrade (RSU).

Only on the nodes which did NOT receive this wrong command the error log message was written and further they have received a GRA_*.log file.

Analysis of the GRA_*.log (failed transactions) files:

hexdump -C GRA_2_16.log 00000000 f3 fe 86 54 02 53 14 00 00 76 00 00 00 76 00 00 |...T.S...v...v..| 00000010 00 00 00 04 00 00 00 00 00 00 00 00 00 00 2a 00 |..............*.| 00000020 00 00 00 00 00 01 00 00 00 40 00 00 00 00 06 03 |.........@......| 00000030 73 74 64 04 21 00 21 00 08 00 0b 04 72 6f 6f 74 |std.!.!.....root| 00000040 09 6c 6f 63 61 6c 68 6f 73 74 00 67 72 61 6e 74 |.localhost.grant| 00000050 20 53 55 50 45 52 20 6f 6e 20 75 73 65 72 64 62 | SUPER on userdb| 00000060 2e 2a 20 74 6f 20 72 6f 6f 74 40 31 32 37 2e 30 |.* to root@127.0| 00000070 2e 30 2e 31 31 31 |.0.111 |
dd if=bin-log.000001 of=binlog.header bs=1 count=120 cat binlog.header GRA_2_17.log > GRA_2_17.binlog_events mysqlbinlog GRA_2_17.binlog_events ... # at 120 #141209 15:04:54 server id 5201 end_log_pos 118 CRC32 0x3432312e Query thread_id=45 exec_time=0 error_code=0 SET TIMESTAMP=1418133894/*!*/; SET @@session.pseudo_thread_id=4/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1073741824/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; grant SUPER on userdb.* to root@ /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

It further looks like this command was issues by Connection ID number 4: conn_id: 4.

Get rid of wrongly deleted InnoDB tables

Shinguz - Wed, 2014-10-22 22:10
Taxonomy upgrade extras: BackupRestoreRecoveryinnodbtable

Precaution: Before you try this out on your production system do a BACKUP first! FromDual Backup Manager can help you with this.


A MySQL user has delete its InnoDB table files for example like this:

shell> rm -f $datadir/test/test.* Analysis

We do some analysis first:

mysql> DROP TABLE test; ERROR 1051 (42S02): Unknown table 'test' mysql> CREATE TABLE test (id INT) ENGINE = InnoDB; ERROR 1050 (42S01): Table '`test`.`test`' already exists

The MySQL error log shows us the following information:

141022 17:09:04 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. 141022 17:09:04 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './test/test.ibd'! InnoDB: Have you moved InnoDB .ibd files around without using the InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? InnoDB: It is also possible that this is a temporary table #sql..., InnoDB: and MySQL removed the .ibd file for this. InnoDB: Please refer to InnoDB: InnoDB: for how to resolve the issue.

User claims that he does NOT need the table and/or the data any more but wants to get rid of the error messages and/or create a new table with the same name.

mysql> CREATE SCHEMA recovery; mysql> use recovery mysql> CREATE TABLE test (id INT) ENGINE = InnoDB; mysql> \! cp $datadir/recovery/test.frm $datadir/test/ mysql> DROP SCHEMA recovery; mysql> use test mysql> DROP TABLE test; Prove

To prove it works we create a new table and fill in some records:


Migration between MySQL/Percona Server and MariaDB

Shinguz - Wed, 2014-10-08 21:40
Taxonomy upgrade extras: migrationsidegradeupgrademysqlmariadbpercona serverconversioncompatibility

This week we did some migrations from MariaDB 10.0 to Percona Server 5.6 at the IT department of a big German bank.

We were perfectly aware that since version 10.0 the MariaDB code base started to diverge slightly away from the MySQL and Percona Server code base which are still pretty close to each other.

Because of the Percona Server option enforce_storage_engine we wanted to do this migration.

We stopped the MariaDB 10.0 server replaced the binaries by the Percona Server 5.6 binaries and started the Percona 5.6 server again. After successfully starting the instance we found some error messages in the MySQL error log. By running the mysql_upgrade command some of the problems were fixed but not all of them. Still left problems were:

  • The MariaDB binary logs provoked some error messages for the Percona Server: [ERROR] Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 25, event_type: -93 [Warning] Error reading GTIDs from binary log: -1 [ERROR] Incorrect definition of table mysql.db: expected column 'User' at position 2 to have type char(16), found type char(80). [ERROR] Incorrect definition of table mysql.event: expected column 'definer' at position 3 to have type char(77), found type char(141). [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set... A purge of the binary logs solved this issue.
  • The tables mysql.event, mysql.innodb_table_stats and mysql.innodb_index_stats where not fixed by mysql_upgrade (a bug to fix for Percona and MySQL/Oracle?). We had to replace those tables manually by copying from an other already working Percona 5.6 Server.

Later in the FromDual technology labs we investigated further and tried the other way from Percona Server 5.6 to MariaDB 10.0. In this direction we found some other errors in the MySQL error log which also where not completely resolved by the mysql_upgrade utility:

  • The mysql.innodb_table_stats and mysql.innodb_index_stats tables where recreated manually (here a bug to fix for the MariaDB people?).
  • All error messages from tables affected by the following message: InnoDB: in InnoDB data dictionary has unknown flags 40/50/52. could be silenced by a run of the OPTIMIZE TABLE command (which can become quite expensive for very big tables).

Sidegrades from MySQL 5.6 to Percona Server 5.6 and back did not provoke any error message written to the MySQL log files. Sidegrades from MariaDB 10.0 to MySQL 5.6 and vice versa behaved exactly the same as MariaDB 10.0 to Percona Server 5.6 and back.

from/to: MySQL 5.6 MariaDB 10.0 Percona Server 5.6 MySQL 5.6 - 2 tables, OPTIMIZE OK MariaDB 10.0 binlog, 3 tables - binlog, 3 tables Percona Server 5.6 OK 2 tables, OPTIMIZE -

During our tests we got rid of the error messages. If they caused any technical harm to the tables or the data we cannot say so far. Further testing and experience from real life is needed. Any feedback is welcome!


It looks like MariaDB 10.0 understands MySQL/Percona Server replication but not the other way around. So replication from MariaDB 10.0 to MySQL 5.6 does probably not work (different implementation of GTID)?


To make sure a sigdegrade between these 3 MySQL branches/forks is seamlessly possible the best method seems to be to dump/restore (NOT xtrabackup!) the data. This can be an issue with huge databases (hundreds of Gbyte).

Further aid

Also have a look at our MySQL compatibility matrix and our MySQL upgrade check-list.

If you need any help to convert MySQL to MariaDB to Percona Server or the other way do not hesitate to contact the FromDual consultancy team. We will be pleased to assist you as a neutral and vendor independent consulting company.

Replication channel fail-over with Galera Cluster for MySQL

Shinguz - Thu, 2014-06-19 07:05
Taxonomy upgrade extras: channelgaleraclusterfail-overreplicationmasterslave

Sometimes it could be desirable to replicate from a Galera Cluster to a single MySQL slave or to an other Galera Cluster. Reasons for this measure could be:

  • An unstable network between two Galera Cluster locations.
  • A separation of a reporting slave and the Galera Cluster so that heavy reports on the slave do not affect the Galera Cluster performance.
  • Mixing different sources in a slave or a Galera Cluster (fan-in replication).

This article is based on earlier research work (see MySQL Cluster - Cluster circular replication with 2 replication channels) and uses the old MySQL replication style (without MySQL GTID).

  • Enable the binary logs on 2 nodes of a Galera Cluster (we call them channel masters) with the log_bin variable.
  • Set log_slave_updates = 1 on ALL Galera nodes.
  • It is recommended to have small binary logs and relay logs in such a situation to reduce overhead of scanning the files (max_binlog_size = 100M).


Let us assume that for some reason the actual channel master of channel 1 breaks. As a consequence the slave of channel 1 does not receive any replication events any more. But we have to keep the replication stream up and running. So we have to switch the replication channel to channel master 2.

Switching replication channel

First for security reasons we should stop the slave of replication channel 1 first:

mysql> STOP SLAVE;

Then we have to find the actual relay log on the slave:

mysql> pager grep Relay_Log_File mysql> SHOW SLAVE STATUS\G mysql> nopager Relay_Log_File: slave-relay-bin.000019

Next we have to find the last applied transaction on the slave:

mysql> SHOW RELAYLOG EVENTS IN 'slave-relay-bin.000019'; | slave-relay-bin.000019 | 3386717 | Query | 5201 | 53745015 | BEGIN | | slave-relay-bin.000019 | 3386794 | Table_map | 5201 | 53745067 | table_id: 72 (test.test) | | slave-relay-bin.000019 | 3386846 | Write_rows | 5201 | 53745142 | table_id: 72 flags: STMT_END_F | | slave-relay-bin.000019 | 3386921 | Xid | 5201 | 53745173 | COMMIT /* xid=1457451 */ | +------------------------+---------+-------------+-----------+-------------+--------------------------------+

This is transaction 1457451 which is the same on all Galera nodes.

On the new channel master of channel 2 we have to find now the matching binary log. This can be done best by matching times between the relay log and the binary log of master of channel 2.

On slave:

shell> ll *relay-bin* -rw-rw---- 1 mysql mysql 336 Mai 22 20:32 slave-relay-bin.000018 -rw-rw---- 1 mysql mysql 3387029 Mai 22 20:37 slave-relay-bin.000019

On master of channel 2:

shell> ll *bin-log* -rw-rw---- 1 mysql mysql 2518737 Mai 22 19:57 bin-log.000072 -rw-rw---- 1 mysql mysql 143 Mai 22 19:57 bin-log.000073 -rw-rw---- 1 mysql mysql 165 Mai 22 20:01 bin-log.000074 -rw-rw---- 1 mysql mysql 62953648 Mai 22 20:40 bin-log.000075

It looks like binary log 75 of master 2 matches to relay log of our slave.

Now we have to find the same transaction on the master of channel 2:

mysql> pager grep -B 6 1457451 mysql> SHOW BINLOG EVENTS IN 'bin-log.000075'; mysql> nopager | bin-log.000075 | 53744832 | Write_rows | 5201 | 53744907 | table_id: 72 flags: STMT_END_F | | bin-log.000075 | 53744907 | Xid | 5201 | 53744938 | COMMIT /* xid=1457450 */ | | bin-log.000075 | 53744938 | Query | 5201 | 53745015 | BEGIN | | bin-log.000075 | 53745015 | Table_map | 5201 | 53745067 | table_id: 72 (test.test) | | bin-log.000075 | 53745067 | Write_rows | 5201 | 53745142 | table_id: 72 flags: STMT_END_F | | bin-log.000075 | 53745142 | Xid | 5201 | 53745173 | COMMIT /* xid=1457451 */ | +----------------+----------+-------------+-----------+-------------+---------------------------------------+

We successfully found the transaction and want the position of the next transaction 53745173 where we should continue replicating.

As a last step we have to set the slave to the master of replication channel 2:

mysql> CHANGE MASTER TO master_host='master2', master_port=3306, master_log_file='bin-log.000075', master_log_pos=53745173; mysql> START SLAVE;

After a while the slave has caught up and is ready for the next fail-over back.


We found during our experiments that an IST of a channel master does not lead to a gap or loss of events in the replication stream. So restarting a channel master does not require a channel fail-over as long as an IST can be used for resyncing the channel master with the Galera Cluster.

The increase of wsrep_cluster_conf_id is NOT an indication that a channel fail-over is required.

A SST resets the binary logs so after the SST a slave will not replicate any more. So using this method should be safe to use. If you find any situation where you experience troubles with channel fail-over please let us know.

Backup Manager for MySQL, MariaDB and Percona Server (mysql_bman)

Shinguz - Tue, 2014-05-06 17:28
Taxonomy upgrade extras: mysqlBackupRestoreRecoverymysql_bmanpitrAbout

The MySQL Backup Manager (mysql_bman) is a wrapper script for standard MySQL backup tools. The Problem with MySQL backup tools is, that they have many options and thus are overcomplicated and errors are easy made.

mysql_bman has the intention to make backups for MySQL easier and technically correct. This means it should per default not allow non-consistent backups or complain if some functions or parameters are used in the wrong way to guarantee proper backups.

In addition it has added some nice features which are missing in standard MySQL backup tools or which are only known from Enterprise backup solutions.

Where to download mysql_bman

The Backup Manager for MySQL (mysql_bman) can be downloaded from our website.

What mysql_bman user say about

Mathias Brem DBA@DBAOnline on LinkedIn:

Ow! Nice!
mysql backup manager is a very nice tool! Congratulations for FromDual! I made a shell script for catalog and maintained backups by xtrabackup, but mysql_bman is the best!

Xtrabackup + mysql_bman!!!!

Where can mysql_bman help you

The intention of mysql_bman is to assist you in bigger MySQL set-ups where you have to follow some backup policies and where you need a serious backup concept.

mysql_bman example

To give you an impression of the power of the MySQL Backup Manager let us have a look at a little example:

shell> mysql_bman --target=bman:secret@ --type=full --mode=logical --policy=daily \ --no-compress --backupdir=/mnt/slowdisk \ --archive --archivedir=/mnt/nfsmount

With this backup method we do a logical full backup (mysqldump is triggered in the background). The backup is stored in the location for backups with the daily policy and is NOT compressed to speed up the backup by saving CPU power AND because the backup device is a de-duplicating drive. Then the backup is archived to and NFS mount.

Backup types

To achieve this we have defined different backup types:

TypeDescriptionfullfull logical backup (mysqldump) of all schemasbinlogbinary-log backupconfigconfiguration file backup (my.cnf)structurestructure backupcleanupclean-up of backup pieces older than n daysschemabackup of one or more schemasprivilegeprivilege dump (SHOW GRANTS FOR)

A backup type is specified with the option --type=<backup_type>.

Backup modes

A backup can either be logical or physical. A logical backup is typically what you do with mysqldump. A physical backup is typically a physical file copy without looking into the data. That is what for example xtrabackup does.

The backup mode is specified with the option --mode=<backup_mode>. The following backup modes are available:

ModeDescriptionlogicaldo a logical backup (mysqldump).physicaldo a physical backup (mysqlbackup/innobackup/xtrabackup)Backup policies

Further we have introduced different backup policies. Policies are there to distinguish how different backups should be treated.

The following backup policies exist:

PolicyDescriptiondailydirectory to store daily backupsweeklydirectory to store weekly backupsmonthlydirectory to store monthly backupsquarterlydirectory to store quarterly backupsyearlydirectory to store yearly backups

For example you could plan to do a daily MySQL backup with binary logs with a retention policy of 7 days. But once a week you want to do a weekly backup consisting of a full backup, a configuration backup and a structure dump. But this weekly backup you want to keep for 6 months. And because of legal reasons you want to do a yearly backup with a retention policy of 10 years.

A backup policy is specified with the --policy=<backup_policy> option. This leads us to the retention time:


The retention time which should be applied to a specific backup policy you can specify with the option --retention=<period_in_days>. The retention option means that a backup is not deleted before this amount of days when you run a clean-up job with mysql_bman.

Let us do an example:

shell> mysql_bman --type=cleanup --policy=daily --retention=30

This means that all backups in the daily policy should be deleted when they are older than 30 days.


With the --target option you specify the connect string to the database to backup. This database can be located either local (all backup types can be used) or remote (only client/server backup types can be used).

A target looks as follows: user/password@host:port (similar to URI specification) whereas you can omit password and port.

Backup location, archiving, compressing and clean-up

The --backupdir option is to control location of the backup files. The policy folders are automatically created under this --backupdir location.
If you have a second layer of backup stores (e.g. tapes or slow backup drives or deduplicated drives or NFS drives) you can use the --archive option to copy your backup files to this second layer storage which is specified with the --archivedir option. For restore performance reasons it is recommended to always keep one or two generations of backups on you fast local drive. If you want to remove the backuped files from the --backupdir destination after the archive job use the --cleanup option.
If you want to omit to compress backups, either to safe time or because your location uses deduplicated drives you can use the --no-compress option.

Per schema backup

Especially for hosting companies a full database backup is typically not the right backup strategy because a restore of one specific customer (= schema) is very complicated. For this case we have the --per-schema option. mysql_bman will do a backup of the whole database schema by schema. Keep in mind: This breaks consistency among schemas!

Sometimes you want to do a schema backup only for some specific schemas for this you can use the --schema option. This option allows you to specify schemas to backup or not to backup. --schema=+a,+b means backup schema a and b. --schema=-a,-b means backup all schemas except a and b.
The second variant is less error prone because you do not forget to backup a new database.

Instance name

MySQL does not know the concept of naming an instance (mysqld). But for bigger environments it could be useful to uniquely name each instance. For this purpose we have introduced the option --instance-name=<give_it_a_name>. This instance name should be unique within your whole company. But we do not enforce it atm. The instance name is used to name backup files and later to identify the backup history of an instance in our backup catalog and to allow us to track the files for restore.

mysql_bman configuration file

Specifying everything on the command line is cumbersome. Thus mysql_bman considers a configuration file specified with the --config=<config_file> option.
A mysql_bman configuration file looks for example as follows:

policy = daily target = root/secret@ type = schema schema = -mysql policy = daily archive = on archivedir = /mnt/tape per-schema = on no-compress = on
Simulate what happens

For the Sissies among us (as for example me) we have the --simulate option. This option simulates nearly all steps as far as possible without executing really anything. This option is either for testing some features or for debugging purposes.


If you want to track your backup history you can specify with the --log option where your mysql_bman log file should be located.

Using Catalog

It will be very useful when you can store your backups metadata in the database so you can check them in the future and to find out the backup criteria (type, mode, instance-name, ... etc) for specific backup processes. This could be achieved by using the catalog feature.

To activate this feature you have to create a database for the catalog "default name is bman_catalog" then create its tables by using the option --create in a special mysql_bman command (check examples below).
Finally, to store your backup metadata in the catalog what you only have to do is adding the option --catalog=catalog_connection_string to the normal mysql_bman command.
Check the examples below for using catalog in mysql_bman.

More help

A little more help you can get with the following command:

shell> mysql_bman --help

Do a full (logical = default) backup and store it in the daily policy folder:

shell> mysql_bman --target=root/secret@ --type=full --policy=daily

Do a full physical backup and store it in the weekly policy folder:

shell> mysql_bman --target=root/secret@ --type=full --mode=physical --policy=weekly

Do a binary-log backup omitting the password in the target and store it in the daily policy folder:

shell> mysql_bman --target=bman@ --type=binlog --policy=daily

Do a MySQL configuration backup and store it in the weekly policy folder:

shell> mysql_bman --target=root/secret@ --type=config --policy=weekly

Do a structure backup and store it in the monthly policy folder and name the file with the instance name:

shell> mysql_bman --target=root/secret@ --type=structure --policy=monthly --instance-name=prod-db

Do a weekly structure backup and archive it to an other backup location:

shell> mysql_bman --target=root/secret@ --type=structure --policy=weekly --archive --archivedir=/mnt/tape

Do a schema backup omitting the mysql schema:

shell> mysql_bman --target=root/secret@ --type=schema --schema=-mysql --policy=daily --archive --archivedir=/mnt/tape

Do a schema backup only of foodmart and world and write it to their own files. Omit compressing these backups because they are located for example on deduplicated drives:

shell> mysql_bman --target=root/secret@ --type=schema --schema=+foodmart,+world --per-schema --policy=daily --no-compress

Creation of a backup catalog (assuming you have created already a catalog database with the default name "bman_catalog"):

shell> mysql_bman --catalog=root/secret@ --create

Backups against catalog:

shell> mysql_bman --target=root/secret@ --catalog=root/secret@ --instance-name=test --type=full --policy=daily

Privilege backup:

shell> mysql_bman --target=root/secret@ --type=privilege --policy=daily --mode=logical

MySQL single query performance - the truth!

Shinguz - Fri, 2013-12-13 17:33
Taxonomy upgrade extras: mysqlperformancePerformance Tuningqueryquery tuningtuningMySQL single query performance - the truth!

As suggested by morgo I did a little test for the same query and the same data-set mentioned in Impact of column types on MySQL JOIN performance but looking into an other dimension: the time (aka MySQL versions).

The answer

To make it short. As a good consultant the answer must be: "It depends!" :-)

The test

The query was again the following:

SELECT * FROM a JOIN b ON b.a_id = WHERE BETWEEN 10000 AND 15000 ;

The Query Execution Plan was the same for all tested releases.

The relevant MySQL variables where used as follows where possible. Should I have considered join buffer, or any other of those local per session buffers (read_buffer_size, read_rnd_buffer_size, join_buffer_size)?

innodb_buffer_pool_size = 768M innodb_buffer_pool_instances = 1 innodb_file_per_table = 1
The results mysql-4.0.30mysql-4.1.25mysql-5.0.96mysql-5.1.73mysql-5.5.35mysql-5.6.15mysql-5.7.3AVG40.8638.683.714.694.647.226.05MEDIAN41.0738.133.694.464.656.326.05STDEV1.512.260.060.340.032.210.03MIN39.2736.993.674.404.596.266.02MAX44.1144.453.865.234.6713.166.10COUNT10.0010.0010.0010.0010.0010.0010.00
galera-5.5.33-23.7.6 / 2.7AVG4.31MEDIAN3.98STDEV1.18MIN3.76MAX8.54COUNT30.00
The Graph


Do not trust benchmarks. They are mostly worthless for your specific workload and pure marketing buzz... Including the one above! ;-)

Database vendors (Oracle/MySQL, Percona, MariaDB) are primarily focussing on throughput and features. In general this is at the costs of single query performance.
MySQL users like Facebook, LinkedIn, Google, Wikpedia,, Yahoo! etc. are more interested in throughput than single query performance (so I assume). But most of the MySQL users (95%) do not have a troughput problem but a single query performance problem (I assume here that this is true also for Oracle, MS-SQL Server, DB2, PostgreSQL, etc.).

So database vendors are not primarily producing for the masses but for some specific users/customers (which possibly pay a hell of money for this).

Back to the data:

My first hypothesis: "The old times were always better" is definitely not true. MySQL 4.0 and 4.1 sucked with this specific query. But since MySQL 5.0 the rough trend is: single query performance becomes worse over time (newer versions). I assume this also true for other databases...

Some claims like: "We have the fastest MySQL" or "We have hired the whole optimizer team" does not necessary reflect in better single query performance. At least not for this specific query.

So in short: If you upgrade or side-grade (MySQL <-> Percona <-> MariaDB), test always very carefully! It is not predictable where the traps are. Newer MySQL release can increase performance of your application or not. Do not trust marketing buzz!


Some artefacts we have already found during this tiny test:

  • In MySQL 5.0 an optimization was introduced (not in the Optimizer!?!) to speed up this specific query dramatically.
  • MariaDB 5.2 and 5.3 were bad for this specific query.
  • I have no clue why Galera Cluster has shown the best results for 5.5. It is no intention or manipulation! It is poor luck. But I like it! :-)
  • MySQL 5.6 seems to have some problems with this query. To much improvement done by Oracle/MySQL?
  • Percona 5.6 sometimes behaves much better with this query than normal MySQL but from time to time something kicks in which makes Percona dramatically slower. Thus the bad results. I have no clue why. I first though about an external influence. But I was capable to reproduce this behaviour (once). So I assume it must be something Percona internally (AHI for example?).

Do not shoot the messenger!

If you want to reproduce the results most information about are already published. If something is missing please let me know.

Please let me know when you do not agree with the results. So I can expand my universe a bit...

It was fun doing this tests today! And MyEnv was a great assistance doing this kind of tests!

If you want us to do such test for you, please let us know. Our consulting team would be happy to assist you with upgrading or side-grading problems.

Impact of column types on MySQL JOIN performance

Shinguz - Wed, 2013-12-11 20:12
Taxonomy upgrade extras: sqlquerytuningmysql

In our MySQL trainings and consulting engagements we tell our customers always to use the smallest possible data type to get better query performance. Especially for the JOIN columns. This advice is supported as well by the MySQL documentation in the chapter Optimizing Data Types:

Use the most efficient (smallest) data types possible. MySQL has many specialized types that save disk space and memory. For example, use the smaller integer types if possible to get smaller tables. MEDIUMINT is often a better choice than INT because a MEDIUMINT column uses 25% less space.

I remember somewhere the JOIN columns where explicitly mentioned but I cannot find it any more.

Test set-up

To get numbers we have created a little test set-up:

CREATE TABLE `a` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT , `data` varchar(64) DEFAULT NULL , `ts` timestamp NOT NULL , PRIMARY KEY (`id`) ) ENGINE=InnoDB CHARSET=latin1  
CREATE TABLE `b` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT , `data` varchar(64) DEFAULT NULL , `ts` timestamp NOT NULL , `a_id` int(10) unsigned DEFAULT NULL , PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1048576 rows 16777216 rows

The following query was used for the test:

EXPLAIN SELECT * FROM a JOIN b ON b.a_id = WHERE BETWEEN 10000 AND 15000; +----+-------------+-------+--------+---------------+---------+---------+-------------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------+----------+-------------+ | 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 16322446 | Using where | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | test.b.a_id | 1 | NULL | +----+-------------+-------+--------+---------------+---------+---------+-------------+----------+-------------+

And yes: I know this query could be more optimal by setting an index on b.a_id.


The whole workload was executed completely in memory and thus CPU bound (we did not want to measure the speed of our I/O system).

SEJOIN columnbytesquery timeGainSpaceCharacter setInnoDBMEDIUMINT35.28 s96%4% faster75%InnoDBINT45.48 s100%100%100%InnoDBBIGINT85.65 s107%7% slower200%InnoDBNUMERIC(7, 2)~46.77 s124%24% slower~100%InnoDBVARCHAR(7)7-86.44 s118%18% slower~200%latin1InnoDBVARCHAR(16)7-86.44 s118%18% slower~200%latin1InnoDBVARCHAR(32)7-86.42 s118%18% slower~200%latin1InnoDBVARCHAR(128)7-86.46 s118%18% slower~200%latin1InnoDBVARCHAR(256)8-96.17 s114%14% slower~225%latin1InnoDBVARCHAR(16)7-86.96 s127%27% slower~200%utf8InnoDBVARCHAR(128)7-86.82 s124%24% slower~200%utf8InnoDBCHAR(16)166.85 s125%25% slower400%latin1InnoDBCHAR(128)1289.68 s177%77% slower3200%latin1InnoDBTEXT8-910.7 s195%95% slower~225%latin1MyISAMINT43.16 s58%42% fasterTokuDBINT44.52 s82%18% faster

Some comments to the tests:

  • MySQL 5.6.13 was used for most of the tests.
  • TokuDB v7.1.0 was tested with MySQL 5.5.30.
  • As results the optimistic cases were taken. In reality the results can be slightly worse.
  • We did not take into consideration that bigger data types will eventually cause more I/O which is very slow!

Galera Cluster 3.1 GA is out!

Shinguz - Wed, 2013-11-13 11:14

Great News: Galera Cluster v3.1 GA for MySQL 5.6 was released at Percona Live London (PLUK) 2013. The information is still a bit hidden...

You can find it here:

Or directly on our download page.

Careful: Online-Upgrade from 5.5 to 5.6 will not work yet. We have to find a work-around...

Murphy’s Law is also valid for Galera Cluster for MySQL

Shinguz - Fri, 2013-10-04 20:10

We had a Galera Cluster support case recently. The customer was drenched in tears because his Galera Cluster did not work any more and he could not make it work any more.

Upsss! What has happened?

A bit of the background of this case: The customer wanted to do a rolling-restart of the Galera Cluster under load because of an Operating System upgrade which requires a reboot of the system.

Lets have a look at the MySQL error log to see what was going on. Customer restarted server with NodeC:

12:20:42 NodeC: normal shutdown --> Group 2/2 12:20:46 NodeC: shutdown complete 12:22:09 NodeC: started 12:22:15 NodeC: start replication 12:22:16 NodeC: CLOSED -> OPEN 12:22:16 all : Group 2/3 component all PRIMARY 12:22:17 NodeC: Gap in state sequence. Need state transfer. 12:22:18 all : Node 1 (NodeC) requested state transfer from '*any*'. Selected 0 (NodeB)(SYNCED) as donor. 12:22:18 NodeB: Shifting SYNCED -> DONOR/DESYNCED (TO: 660966498) 12:22:19 NodeC: Shifting PRIMARY -> JOINER (TO: 660966498) 12:22:19 NodeC: Receiving IST: 14761 writesets, seqnos 660951493-660966254 12:22:21 NodeC: 0 (NodeB): State transfer to 1 (NodeC) complete.

Everything went fine so far NodeC came up again and did an IST as expected. But then the first operational error happened: The customer did not wait to reboot NodeB until NodeC was completely recovered. It seems like NodeC took some time for the IST recovery. This should be checked on all nodes with SHOW GLOBAL STATUS LIKE 'wsrep%';...

12:22:21 NodeC: Member 0 (NodeB) synced with group. 12:22:21 NodeB: Shifting JOINED -> SYNCED (TO: 660966845) 12:22:21 NodeB: Synchronized with group, ready for connections --> NodeC seems not to be ready yet! 12:23:21 NodeB: Normal shutdown 12:23:21 all : Group 1/2 12:23:21 NodeC: Aborted (core dumped)

And now Murphy was acting already the first time: We hit a situation in the Galera Cluster which is not covered as expected. Now we have 2 nodes out of 3 not working. As a result the Cluster gets a quorum loss (non-Primary, more than 50% of nodes disappeared) and does not reply to any SQL queries any more. This is a bug because both nodes left the cluster gracefully. The third node should have stayed primary:

12:23:21 NodeB: Received SELF-LEAVE. Closing connection. 12:23:23 NodeB: Shifting CLOSED -> DESTROYED (TO: 660973981) 12:23:25 NodeB: Shutdown complete 12:23:29 NodeC: mysqld_safe WSREP: sleeping 15 seconds before restart 12:23:37 NodeA: Received NON-PRIMARY. 12:23:44 NodeC: mysqld_safe mysqld restarted 12:23:48 NodeC: Shifting CLOSED -> OPEN 12:23:48 NodeC: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 2 12:23:48 NodeC: Received NON-PRIMARY. 12:23:48 NodeA: New COMPONENT: primary = no, bootstrap = no, my_idx = 1, memb_num = 2 12:23:48 NodeA: Received NON-PRIMARY. 12:24:30 NodeB: mysqld_safe Starting mysqld daemon 12:24:36 NodeB: Start replication 12:24:37 NodeB: Received NON-PRIMARY.

As a result the customer decided to shutdown the whole cluster. Which was not necessary but is a acceptable approach:

12:27:55 NodeB: /usr/sbin/mysqld: Normal shutdown 12:27:58 NodeB: /usr/sbin/mysqld: Shutdown complete 12:28:14 NodeA: /usr/sbin/mysqld: Normal shutdown 12:28:19 NodeA: /usr/sbin/mysqld: Shutdown complete 12:31:45 NodeC: /usr/sbin/mysqld: Normal shutdown 12:31:49 NodeC: /usr/sbin/mysqld: Shutdown complete

We experience a complete cluster outage now. An then the next operational error happened: The customer has chosen the node (NodeC) with the worst (= oldest) data as the starting node for the new Cluster:

12:31:55 NodeC: Starting mysqld daemon 12:31:58 NodeC: PRIMARY, 1/1 12:31:58 NodeC: /usr/sbin/mysqld: ready for connections. 12:33:29 NodeB: mysqld_safe Starting mysqld daemon 12:33:33 NodeB: PRIMARY, 1/2

An alternative approach would have been to run the command SET GLOBAL wsrep_provider_options='pc.bootstrap=yes'; on the node (NodeA) with the most recent data...
After connecting NodeB (with the newer state) requested an state transfer from the older NodeC:

12:33:35 all : Node 1 (NodeB) requested state transfer from '*any*'. Selected 0 (NodeC)(SYNCED) as donor. 12:33:35 NodeC: Shifting SYNCED -> DONOR/DESYNCED (TO: 660982149) 12:33:35 NodeC: IST request --> Should be SST, why IST? 12:33:35 NodeB: Shifting PRIMARY -> JOINER (TO: 660982149) 12:33:35 NodeB: Receiving IST: 7914 writesets, seqnos 660973981-660981895 12:33:36 NodeB: Slave SQL: Could not execute Write_rows event on table test.test; Duplicate entry '8994678' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 102, Error_code: 1062

And now Mister Murphy is acting a second time: We hit another situation: The newer node requests an IST from the older node which has progressed in the meanwhile to an even newer state. So the newer joiner node receives data from the older donor node which causes an AUTO_INCREMENT Primary Key violation. As a consequence the node crashes:

12:33:36 NodeB: receiving IST failed, node restart required: Failed to apply app buffer: äJR#, seqno: 660974010, status: WSREP_FATAL 12:33:36 NodeB: Closed send monitor. 12:33:37 NodeB: Closing slave action queue. 12:33:37 NodeB: Aborted (core dumped) 12:33:37 NodeC: PRIMARY 1/1 12:33:44 NodeC: Shifting DONOR/DESYNCED -> JOINED (TO: 660983204) 12:33:59 NodeB: mysqld_safe mysqld restarted 12:34:04 NodeB: Shifting CLOSED -> OPEN 12:34:07 NodeB: Aborted (core dumped) ... Loop

This situation keeps the node NodeB now in a crashing loop. Restarted by the mysqld_safe process requesting an IST. This is another bug which is fixed in a newer Galera MySQL (5.5.33). And now the next operational error happened: Instead of killing NodeB and forcing an SST by deleting the grastat.dat file They started the third node as well...

12:37:12 NodeA: mysqld_safe Starting mysqld daemon ... --> code dumped ... Loop

NodeB and NodeA both have the same problem now...

As a result: Node NodeA and NodeB are now looping in a crash. But at least the node NodeC was up and running all the time.

  • Most important: Have an ntpd service running on all Cluster nodes to not mess up the times on different nodes while investigating in errors. This makes problem solving much easier...
  • In case of split-brain or quorum loss choose the node with the most recent data as your initial Cluster node.
  • If you have a Cluster in split-brain you do not have to restart it. You can bring the node out of split-brain with pc.bootstrap=yes variable if you found out which node is the most recent one.
  • Analyse error log files carefully to understand what went wrong. Forcing an SST only takes a few seconds.
  • Upgrade your software regularly to not hit old known bugs. The rule Do not touch a running system! does not apply here because we are already touching the running system! So regular upgrade from time to time can be very helpful!
  • Be familiar with operational issues of your Cluster software. A Cluster does not only mean high-availability. It means also you have to train your staff to handle it.
  • It is always valuable to have support for your business critical systems.

Huge amount of TIME_WAIT connections

Shinguz - Sat, 2013-09-21 11:11

In MySQL we have the typical behaviour that we open and close connections very often and rapidly. So we have very short-living connections to the server. This can lead in extreme cases to the situation that the maximum number of TCP ports are exhausted.

The maximum number of TCP ports we can find with:

# cat /proc/sys/net/ipv4/ip_local_port_range 32768 61000

In this example we can have in maximum (61000 - 32768 = 28232) connections concurrently open.

When a TCP connections closes the port cannot be reused immediately afterwards because the Operating System has to wait for the duration of the TIME_WAIT interval (maximum segment lifetime, MSL). This we can see with the command:

# netstat -nat Active Internet connections (servers and established) Proto Recv-Q Send-Q Local Address Foreign Address State tcp 0 0* LISTEN tcp 0 0* LISTEN tcp 0 0 TIME_WAIT tcp 0 0 TIME_WAIT tcp 0 0 ESTABLISHED tcp 0 0 TIME_WAIT

The reason for waiting is that packets may arrive out of order or be retransmitted after the connection has been closed. CLOSE_WAIT indicates that the other side of the connection has closed the connection. TIME_WAIT indicates that this side has closed the connection. The connection is being kept around so that any delayed packets can be matched to the connection and handled appropriately.

The Maximum Segment Lifetime can be found as follows:

# cat /proc/sys/net/ipv4/tcp_fin_timeout 60

This basically means your system cannot guarantee more than ((61000 - 32768) / 60 = 470) ports at any given time.


There are several strategies out of this problem:

  • Open less frequently connections to your MySQL database. Put more payload into one connection. Often Connection Pooling is used to achieve this.
  • Increasing the port range. Setting the range to 15000 61000 is pretty common these days (extreme tuning: 1024 - 65535).
  • Increase the availability by decreasing the FIN timeout.

Those values can be changed online with:

# echo 30 > /proc/sys/net/ipv4/tcp_fin_timeout # echo 15000 65000 > /proc/sys/net/ipv4/ip_local_port_range

Or permanently by adding it to /etc/sysctl.conf

An other possibility to change this behaviour is to use tcp_tw_recycle and tcp_tw_reuse. By default they are disabled:

# cat /proc/sys/net/ipv4/tcp_tw_recycle 0 # cat /proc/sys/net/ipv4/tcp_tw_reuse 0

These parameters allow fast cycling of sockets in TIME_WAIT state and re-using them. But before you do this change make sure that this does not conflict with the protocols that you would use for the application that needs these ports.

The tcp_tw_recycle could cause some problems when using load balancers:

tcp_tw_reuse Allow to reuse TIME_WAIT sockets for new connections when it is safe from protocol viewpoint. Default value is 0.
It should not be changed without advice/request of technical experts. tcp_tw_recycle Enable fast recycling TIME_WAIT sockets. Default value is 0. It should not be changed without advice/request of technical experts.

Galera Arbitrator (garbd)

Shinguz - Sat, 2013-08-17 16:59
Taxonomy upgrade extras: galerastartstop

It took me quite a while to find out how the beast Galera Arbitrator (garbd) works. To safe your time here a short summary:

How to start Galera Arbitrator (garbd) shell> ./garbd --address gcomm://, --group "Our Galera Cluster" --log /tmp/garbd.log --daemon
How to stop Galera Arbitrator (gardb) shell> killall garbd
How to start Galera Arbitrator (garbd) with a configuration file shell>./garbd --cfg /tmp/garb.cnf --daemon

The configuration file looks as follows:

# # /etc/mysql/garb.cnf # address = gcomm://,, group = Our Galera Cluster options = gmcast.listen_addr=tcp:// log = /tmp/garbd.log

A service start/stop script can be found at: galera-src/garb/files/ and galera-src/garb/files/garb.cnf

Galera Cluster for MySQL and hardware load balancer

Shinguz - Wed, 2013-08-14 21:30

Our bigger customers where we help to deploy Galera Cluster for MySQL set-ups have some commercial hardware (e.g. F5 or Cisco) for load balancing instead of software load balancers.

For those hardware load balancer it is not possible to see if a Galera node is available or not because the MySQL daemon is still running and responding on port 3306 but the service is not available nonetheless.
So the load balancer still serves the Galera node while he feeds for example a joiner node with a SST. This would lead to application errors which is unlovely.

One can try somehow to teach the load balancer to find out if a Galera Cluster node is really available or not. But this requires a more sophisticated load balancer, know-how how to teach the load balancer the new behaviour and possible interaction between the MySQL node and the load balancer. See our other discussion for this mater.

An other concept we hit on this week is that we could also block the port 3306 of the MySQL node with firewall rules (iptables). Then the hardware load balancer does not see anybody listening on port 3306 any more and assumes that this IP address should not be served any more.

We also learned this week that the REJECT rule is better than the DROP rule when we want to have fast response time for immediate elimination of traffic.

The script has to be hooked as before into the wsrep_notify_cmd variable and an additional sudoers rule has to be added for the mysql user.

# # /etc/sudoers.d/mysql # chmod 0440 # mysql ALL = (root) NOPASSWD: /sbin/iptables

We are interested to hear your experience and your opinion about this approach.

To UNION or not to UNION...

Shinguz - Sun, 2013-07-28 12:42

Recently a forum question [ 1 ] got my attention:

Is there any performance issue with Union?

I used union all sometime back and it was performance issue just to make an opinion that we should used union in query.

The question itself was not too interesting because the answer is easy: It depends. But I wanted to see if there was an improvement in this common problem over time in MySQL.

Test set-up

So I prepared a little test to simulate some of the possible scenarios:

CREATE TABLE `u` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `a` int(10) unsigned DEFAULT NULL, `b` int(10) unsigned DEFAULT NULL, `c` int(10) unsigned DEFAULT NULL, `d` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `b` (`b`), KEY `c` (`c`), KEY `d` (`d`) ) ENGINE=InnoDB ; INSERT INTO u SELECT NULL, ROUND(RAND()*10, 0), ROUND(RAND()*10, 0), ROUND(RAND()*1000000, 0), ROUND(RAND()*1000000, 0); INSERT INTO u SELECT NULL, ROUND(RAND()*10, 0), ROUND(RAND()*10, 0), ROUND(RAND()*1000000, 0), ROUND(RAND()*1000000, 0) FROM u; ... 1 mio rows ANALYZE TABLE u;

With this table we can simulate the OR problem with low and high selectivity.

Running the tests

We did the tests with MySQL (5.0 - 5.7), Percona Server (5.6) and MariaDB (5.5, 10.0) for the following queries:

EXPLAIN SELECT * FROM u WHERE a = 5 OR b = 5; EXPLAIN SELECT * FROM u WHERE a = 5 OR c = 500001; EXPLAIN SELECT * FROM u WHERE c = 500001 OR d = 500001;

We are interested in what the optimizer is doing and what the performance of the queries is. The following results came out:

Query 1 Query 2 Query 3 Database version rows avg. time QEP rows avg. time QEP rows avg. time QEP MySQL 5.0.92 194402 390 ms 1 104876 230 ms 2 6 < 10 ms 3 MySQL 5.1.66 194402 410 ms 1 104876 240 ms 2 6 < 10 ms 3 MySQL 5.5.24 194402 420 ms 1 104876 370 ms 1 6 < 10 ms 3 MariaDB 5.5.32 194402 460 ms 1 104876 420 ms 1 6 < 10 ms 3 MySQL 5.6.12 194402 440 ms 2 104876 240 ms 2 6 < 10 ms 3 Percona 5.6.12-60.40 194402 450 ms 2 104876 240 ms 2 6 < 10 ms 3 MySQL 5.7.1 194402 420 ms 2 104876 220 ms 2 6 < 10 ms 3 MariaDB 10.0.3 194402 450 ms 1 104876 400 ms 1 6 < 10 ms 3 Different Query Execution Plans (QEP)
  • QEP 1: +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | u | ALL | a,b | NULL | NULL | NULL | 1049134 | Using where | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
  • QEP 2: +----+-------------+-------+-------------+---------------+------+---------+------+--------+-------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------+---------------+------+---------+------+--------+-------------------------------+ | 1 | SIMPLE | u | index_merge | a,c | a,c | 5,5 | NULL | nnnnnn | Using union(a,c); Using where | +----+-------------+-------+-------------+---------------+------+---------+------+--------+-------------------------------+
  • QEP 3: +----+-------------+-------+-------------+---------------+------+---------+------+------+-------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------+---------------+------+---------+------+------+-------------------------------+ | 1 | SIMPLE | u | index_merge | c,d | c,d | 5,5 | NULL | n | Using union(c,d); Using where | +----+-------------+-------+-------------+---------------+------+---------+------+------+-------------------------------+
  • Conclusion
    • Single query performance went down from 5 - 50% (in one case increased by 5%) over time (MySQL releases). But we can see some impacts on optimizer improvements.
    • Newer MySQL releases are not necessarily faster for single-query performance than older ones. Most of the MySQL users are not running more than 1 or 2 concurrent queries. For them scalability improvements are not really an issue.
    • There seems to be some changes in the Optimizer some for good, some for bad, depending on the release or branch/fork you are using. So test carefully when you change the release or branch/fork.
    • And: Do not believe the whole marketing yelling but do your own testing...

MySQL and Secure Linux (SELinux)

Shinguz - Thu, 2013-06-20 16:40

Maybe you experienced some strange behaviour with MySQL: Everything is installed correctly and should work. But it does not.

Symptoms we have seen:

  • MySQL starts/stops properly when started/stopped with service mysqld restart but MySQL does not start when a server is rebooted.
  • Or after upgrading MySQL binaries mysqld will not start at all any more.
  • Or after relocating MySQL datadir or changing default port MySQL does not start any more.

shell> service mysqld start MySQL Daemon failed to start. Starting mysqld: [FAILED] shell> grep mysqld /var/log/boot.log Starting mysqld: [FAILED]

If you are lucky you get some error message like: ERROR! The server quit without updating PID file (/data/mysql/ or:

130620 9:49:14 [ERROR] Can't start server : Bind on unix socket: Permission denied 130620 9:49:14 [ERROR] Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ? 130620 9:49:14 [ERROR] Aborting

This typically happens when you relocate the MySQL data files (datadir), change port, socket, log file, pid file or similar.

The reason for this problem is not too easy to find. You see some traces in /var/log/boot.log. And if you know where to look for you will find something in /var/log/audit/audit.log. But without knowing where to look and what to look for it is quite hard.
If you are lucky the setroubleshoot utility is installed. This will report problems in the syslog (/var/log/messages).

The cause of this problem might be the Secure Linux (SELinux) feature!

SELinux [1], [2], [3] is typically used in Red Hat, CentOS and Fedora Linux. On Debian, Ubuntu and SuSE you have a similar solution called AppArmor.

To see if SELinux is enabled just run the following command:

shell> sestatus SELinux status: enabled SELinuxfs mount: /selinux Current mode: enforcing Mode from config file: enforcing Policy version: 24 Policy from config file: targeted

To disable SELinux you just have to run the following command:

shell> setenforce 0

And to make this change persistent you have to change it in the following configuration file:

# # /etc/selinux/config # # This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: # enforcing - SELinux security policy is enforced. # permissive - SELinux prints warnings instead of enforcing. # disabled - No SELinux policy is loaded. SELINUX=permissive # SELINUXTYPE= can take one of these two values: # targeted - Targeted processes are protected, # mls - Multi Level Security protection. SELINUXTYPE=targeted

But possibly you want to move the MySQL datadir to an other location without disabling SELinux? To achieve this proceed with the following steps:

The simple way

If you have just moved datadir or the MySQL port the Blog article SELinux and MySQL of Jeremy Smyth is a good starting point.

Complicated way

If you want to create an other or a new MySQL instance or do some other stuff you have to do some more things manually (possibly there is also an automated way?):

First it is recommended to install the setroubleshoot utility. Then with the command:

shell> tail /var/log/messages Jun 20 09:38:53 ip-10-39-25-184 setroubleshoot: SELinux is preventing /bin/mkdir from write access on the directory /var/lib. For complete SELinux messages. run sealert -l ef8eae63-7ec3-4b22-87e0-5774120726c3

You will find what is going wrong. Follow the instructions:

shell> sealert -l ef8eae63-7ec3-4b22-87e0-5774120726c3 SELinux is preventing /bin/mkdir from write access on the directory /var/lib. ***** Plugin catchall_labels (83.8 confidence) suggests ******************** If you want to allow mkdir to have write access on the lib directory Then you need to change the label on /var/lib Do # semanage fcontext -a -t FILE_TYPE '/var/lib' where FILE_TYPE is one of the following: var_log_t, mysqld_var_run_t, mysqld_db_t, root_t. Then execute: restorecon -v '/var/lib' ***** Plugin catchall (17.1 confidence) suggests *************************** If you believe that mkdir should be allowed write access on the lib directory by default. Then you should report this as a bug. You can generate a local policy module to allow this access. Do allow this access for now by executing: # grep mkdir /var/log/audit/audit.log | audit2allow -M mypol # semodule -i mypol.pp

until MySQL starts properly. And also test a reboot of the machine!

Unbreakable MySQL Cluster with Galera and Linux Virtual Server (LVS)

Shinguz - Thu, 2013-06-13 17:13
Taxonomy upgrade extras: galeraclusterMySQL ClusterHigh Availability

Recently we had to set-up a 3-node Galera Cluster with a Load Balancer in front of it. Because Galera Cluster nodes (mysqld) still reply to TCP requests on port 3306 when they are expelled from the Cluster it is not sufficient to just leave it to the Load Balancer to check the port if a Galera node is properly running or not.

We used the wsrep_notify_cmd variable to hook our own script into the Galera Cluster which disables each Node on the Load Balancer when its state changed.

# my.cnf # [mysqld] wsrep_notify_cmd = /usr/local/bin/

The whole Galera Cluster Architecture looks as follows:

As Load Balancer we used the IPVS Load Balancer from the Linux Virtual Server (LVS) Project. This Load Balancer was made highly available with keepalived.

Our script to take a Galera Node out of the Load Balancer was the following:

#!/bin/bash -eu # # /etc/mysql/conf.d/wsrep.cnf # # [mysqld] # wsrep_notify_cmd = /usr/local/bin/ # LOG="/tmp/lvs_control.log" LBIP="" VIP="" PORT="3306" LBUSER="galera" LBUSER="root" ETC="/etc/mysql/conf.d/wsrep.cnf" ETC="/home/mysql/data/mysql-5.5-wsrep-23.7-a/my.cnf" MYIP='' WEIGHT="100" DATE=$(date '+%Y-%m-%d %H:%M:%S') echo $DATE >>$LOG regex='^.*=\s*([0-9]+.[0-9]+.[0-9]+.[0-9]+).*' str=$(grep "^wsrep_node_incoming_address" $ETC 2>>$LOG) if [[ $str =~ $regex ]] ; then MYIP=${BASH_REMATCH[1]} else echo "Cannot find IP address in $str" >>$LOG exit 1 fi while [ $# -gt 0 ] ; do case $1 in --status) STATUS=$2 shift ;; --uuid) CLUSTER_UUID=$2 shift ;; --primary) PRIMARY=$2 shift ;; --index) INDEX=$2 shift ;; --members) MEMBERS=$2 shift ;; esac shift done # echo $* >> $LOG echo $STATUS >> $LOG # Undefined means node is shutting down # Synced means node is ready again if [ "$STATUS" != "Synced" ] ; then cmd="ssh $LBUSER@$LBIP 'sudo /sbin/ipvsadm -e -t $VIP:$PORT -r $MYIP -w 0'" else cmd="ssh $LBUSER@$LBIP 'sudo /sbin/ipvsadm -e -t $VIP:$PORT -r $MYIP -w $WEIGHT'" fi echo $cmd >>$LOG eval $cmd >>$LOG 2>&1 echo "ret=$?" >>$LOG exit 0

We assume that the same script can be used with little modifications for the Galera Load Balancer as well.

We need you: MySQL DBA for FromDual Support line

Shinguz - Tue, 2013-04-02 11:17

FromDual is looking for professional, enthusiastic and experienced people who:

  • Know MySQL, Percona Server or MariaDB extensively
  • Are Familiar with the open source eco-system
  • Know how to operate database systems, as a DBA or a DevOps
  • Understand what can go wrong in operating a database
  • Are happy to work autonomously, remotely and to communicate with IRC, Skype, Mail and Phone
  • Are comfortable on Linux systems
  • Are team players, keen to contribute to the growth of the company
  • Are Comfortable dealing direct with clients and
  • Look for new challenges

Job description

We are looking for full-time MySQL support engineers (female or male) to primarily take care of our MySQL support services and help our customers operating their MySQL databases (remote-DBA and emergency interventions).

You are well trained and have good experience in:

  • Operating critical highly available MySQL production databases mostly on Linux.
  • Running MySQL-Replication in all variants is your daily business.
  • The working of the most used MySQL HA set-up's and how to fix them efficiently if problems occur. (If you are already experienced in running Galera Cluster this would be a plus!
  • Open Source Technologies (LAMP stack, etc.)
  • Bash scripting and you can do some simple programs in at least one popular programming/scripting language (Perl, PHP, ...).

You will be in direct contact with the customers and you need good antennae to listen to them, know how to respond and get the answers to their real problems. You also have to be proactive when something goes wrong and direct the customer back to the right track.

You need to have good communication skills and be an active team player.

To fulfil your job you have to work in European Time Zones. You can organize your working time flexible within certain ranges. Participating in the on call duty is expected. FromDual is a completely virtual company and relocation is not needed (home office). Good English verbally and in writing is a must. Most of our current customers speak German and having German skills is a plus.

Beside being our support engineer we expect you to improve your knowledge and skills and to contribute to improving our monitoring solution, our database controlling solution and our other tools. Further, we expect that you write regular technical articles and give help wherever it is needed or requested...

You should be prepared to work, think and act autonomously most of the time and to teach yourself (using Google, MySQL documentation, testing etc.). If you are ever stuck, your colleagues at FromDual will assist you.

If you need somebody holding your hand all the time, FromDual is not a good choice for you.

Who is FromDual?

FromDual is the leading independent and professional MySQL database consulting and service company in Europe with its Headquarters in Switzerland.

Our customers are mostly located in Europe and range from small start-up companies to some of the top-500 companies of Europe.

You will be joining us at an exciting time. We are growing and we need like-minded people to grow with us, individually and collectively. As our horizons expand, we need our team to expand in its skills, knowledge and expertise.

Applying to join FromDual could be the best decision you make.

How to continue

If you are interested in this opportunity and if you feel you are a good "fit" (we know that there will not be a 100% match!) we would be glad to hear from you.

Please send your true CV with your salary expectation and a list of your open source involvements, blog articles, slides, tweets etc. to If you want to know more about this job opportunity or if you want to speak with me, please call me at +41 79 830 09 33 (Oli Sennhauser, CTO). Only candidates, NO head hunters please!

After we received and screened your CV we will invite you to prove your technical skills by taking an exam in operating MySQL. If you pass the exam you will be invited for the final interviews.

This job opportunity is open until May 31st 2013.

Switching from MySQL/MyISAM to Galera Cluster

Shinguz - Tue, 2013-03-12 08:23
Taxonomy upgrade extras: galeramyisaminnodbstorage engine

Switching from MySQL/MyISAM to Galera Cluster requires that all tables (except those from the mysql, information_schema and performance_schema) are using the InnoDB Storage Engine.

For altering the Storage Engine of the tables we wrote a script ( long time ago already. Because we have made many of those switches recently we have extended its functionality.

New features
  • Recognizes VIEW's and does NOT try to alter their Storage Engine (bug).
  • Script is MySQL version aware. Complain if too old MySQL version is used.
  • Find tables without a Primary Key.
  • Check for too long InnoDB Primary Keys
  • Check for FULLTEXT indexes in MySQL 5.1 and 5.5 and write a note if version is older.
Example ./ User [root] : Password [] : secret Schema from (or all) [test] : all Engine to [InnoDB] : Version is : 5.6.10 MR Version is: 050610 The following tables might not have a Primary Key: +--------------+----------------------+ | table_schema | table_name | +--------------+----------------------+ | test | innodb_table_monitor | | test | log_event | | test | parent | | test | t | +--------------+----------------------+ The tables above not having a Primary Key will negatively affect perfor- mance and data consistency in MySQL Master/Slave replication and Galera Cluster replication. The following tables might have a too long Primary Key for InnoDB (> 767 bytes): +--------------+------------+-------------+ | table_schema | table_name | column_name | +--------------+------------+-------------+ | test | test | data | +--------------+------------+-------------+ The following tables might have a FULLTEXT index (which is only supported in MySQL 5.6 and newer): +--------------+------------+-------------+ | table_schema | table_name | column_name | +--------------+------------+-------------+ | test | test | data | +--------------+------------+-------------+ Output written to /tmp/alter_table_all.sql After reviewing it you can apply it with mysql --user=root --password=secret

Block MySQL traffic for maintenance windows

Shinguz - Mon, 2013-02-18 22:32

From time to time some maintenance work on the MySQL database has to be done. During the maintenance window we do not want to have application traffic on the database.

Sometimes it is hard to shut down all applications spread over the whole company. Or we want to allow only some specific hosts to access mysql from remote (for example the monitoring system or the backup server).

For this purpose we can use the Linux packet filtering.

To see what packet filtering rules are available we can run the following command:

iptables -L INPUT -v

To close the MySQL port on all interfaces we use:

iptables -A INPUT -p tcp --dport mysql -j DROP

and to open the MySQL port again after the maintenance window:

iptables -D INPUT -p tcp --dport mysql -j DROP

With the -i option we can restrict the rule to a specific interface for example eth0 and with the option -s we can specify a specific source only. Or with a ! -s we can implement an inverse rule (all but).

Bootstrapping Galera Cluster the new way

Shinguz - Tue, 2013-02-05 19:18
Taxonomy upgrade extras: galeracluster

A while ago it was pretty inconvenient to start a complete Galera Cluster from scratch. Rolling restart an such things are already working well but bootstrapping was a pain.

With Galera v2.2 new functionality came in. We tried it out and it did not work as documented. :-( Thanks to Teemu's help we found there was a documentation bug in the Galera documentation.

The settings which were working for us are:

wsrep_cluster_address = "gcomm://,"

And when all 3 nodes of the Galera Cluster are started and ready to join you can run:

SET GLOBAL wsrep_provider_options="pc.bootstrap=1";

I hope we can go life on Thursday with the new Telco VoIP Cluster for 2500 employees...

Have fun and enjoy an even better Galera Cluster for MySQL!


Subscribe to FromDual aggregator - MySQL Tech-Feed (en)