News

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.

Learnings

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

FromDual has the pleasure to announce the release of the new version 1.0 of its popular multi-instance MySQL Environment MyEnv.

Thanks to the countless feedback of many big customers and the community we finally feel ready to publish version 1.0. A major step forward from the last version 0.4 (dated March 2011) and not published version 0.5 (dated August 2013).

What is MyEnv?

MyEnv is a CLI environment for operating multiple MySQL instances on the same server. If you are using mysqld_multi or/and if you have set-up multiple mysqld instances on the same machine you should really have a look at MyEnv.

If you consider to consolidate your MySQL databases on one machine but if you do not want to pack everything in one instance you are a candidate for MyEnv as well...

You can download MyEnv from here.

In the inconceivable case that you find a bug in MyEnv please report it in our Bugtracker.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Upgrade from 0.x to 1.0

Package php-posix needs to be installed.

If the following manual steps are too complicated just run the installer installMyEnv.sh and copy your old myenv.conf to /etc/myenv.

# cd /home/mysql/product # tar xf /tmp/myenv-1.0.tar.gz # sudo mkdir /etc/myenv # sudo chown mysql: /etc/myenv # echo 'export MYENV_BASE=/home/mysql/product/myenv' > /etc/myenv/MYENV_BASE # cp /home/mysql/product/myenv/etc/myenv.conf /etc/myenv/ # rm -f myenv # ln -s myenv-1.y myenv # cp myenv/etc/aliases.conf.template /etc/myenv/aliases.conf # cp myenv/etc/variables.conf.template /etc/myenv/variables.conf # cat > ~/.bash_profile # BEGIN MyEnv # Written by the MyEnv installMyEnv.php script. . /etc/myenv/MYENV_BASE MYENV_PWD=`pwd` cd $MYENV_BASE/bin . myenv.profile cd $MYENV_PWD # END MyEnv _EOF
Changes in MyEnv 1.0 MyEnv

• cd abc xyz alias failed when one of the patterns contained a space. This is fixed now (Bug #10).
• Different smaller bug fixes.
• timezone added to MyEnv to avoid nasty error messages.
• Old path in PATH variables should be cleaned-up properly now.
• Incompatible Change: DEBUG variables replaced by MYENV_DEBUG.
• MyEnv start/stop script (myenv_start_stop.php) accepts as parameter and instance name now (customer enhancement request).
• hideschema parameter implemented for myenv.conf (customer enhancement request).
• Default section is now configurable in section [default] (customer enhancement request).
• Better support for Percona Server (customer enhancement request).
• Readline support added for set-ups not having it by default (customer enhancement request).
• cdb alias added for cd $basedir or cd $MYSQL_HOME.
• Security check for my.cnf introduced.
• Password was shown in clear text in error log (Bug #12).
• tmp directory is added to MyEnv structure.
• unknown version fixed.
• Made hideschema variable inheritable.
• Parameter drizzle and type removed. De-support for Drizzle.
• The utl directory should be added to the PATH as well.
• Add Path to the right of PATH is fixed now.
• Possibility to start mysqld without angel process (mysqld_safe) now.
• /tmp/myEnv.xxx files where not removed. This is fixed now.
• 5.6.12 as version pattern is now accepted (customer request).
• Incompatible Change: Configuration file (myenv.conf) is now relocated from $MYENV_HOME/etc to /etc/myenv.
• MySQL stop functionality was changed from mysqladmin shutdown to kill (MySQL like).
• my.cnf privilege check is less restrictive now (rwxr-----).

MyEnv Installer

• Many bug fixes and improvements for MyEnv Installer.
• Incompatible Change: MyEnv Installer rewritten, renamed (bin/installMyEnv.php) and code cleaned-up.
• MySQL database can now be created in installer.
• Check for mysql user is added to installer.
• Missing mysql_install_db is caught.
• Missing socket error is caught.
• Only a stopped database can be deleted.
• Configuration files are versioned when changed.
• Delete process more user-friendly.
• Missing basedir and datadir variables in my.cnf fixed.
• Privileges of my.cnf file set correctly to avoid warning later.
• Not existing my.cnf file caught.
• Selection characters made more intuitive.
• Empty my.cnf string caught.
• Installer is aware of missing environment variables.
• Start/stop instance parameter was added in installer.

MyEnv Utilities

• Row Extractor (utl/row_extractor.pl) to extract rows from corrupt tables (leading to a DB crash) added.
• Script to flush Query Cache regularly (utl/flush_query_cache.sh) added.
• Script to find last access to tables (utl/filesystem_table.php) added.
• MySQL Backup Manager (mysql_bman) added.
• Script to do a NDB Cluster channel fail-over (utl/channel_failover.pl) added.
• README about MyEnv utilities usage updated (utl/README).
• Compare script to compare two GLOBAL STATUS outputs added (utl/compare.php).
• Rotate log script to rotate different MySQL logs added (utl/rotate_log.sh).
• Script to convert \G output of MySQL client to one-line output added (utl/backslashG2table.pl).
• alter_engine.sh script improved by a Perl version (for windows customer) (utl/alter_engine.pl).
• Different resources, configuration files and monitors added for Heartbeat v1/v2: (utl/{stop-heartbeat.alert|ping.monitor|node.monitor|mysql.monitor|mail.alert|Crontab|canias}, etc/{drbd.conf.template|ha.cf.template|mon.cf.template|my.cnf.template}).)
• Slave Monitor added (utl/{slave_monitoring.php|slave_monitor.php}).
• DRBD Monitor added (utl/check_drbd.sh).
• MySQL Profiler added (utl/profiler/{profiler.pl|tracer.pl}).
• split_partition.php and drop_partition.php scripts added (utl/{split_partition.php|drop_partition.php}).
• Insert test added (utl/{insert_test.sh|insert_test.phpx}). Shell script is for connection testing. PHP script is more for INSERT load testing...
• Utility mem_map.pl added (utl/mem_map.pl).
• Start/stop script for VIP added (utl/vip).
• Script to fix wrong encoding added (utl/fix_encoding.php).
• Alter Engine script enhanced with MySQL 5.6 and Galera features.
• Ping Log utility added (utl/ping_log.sh).
• Script added to block MySQL port so Load Balancer recognizes if Galera Cluster node is away (utl/block_galera_node.sh, customer request).
• Create table statement for test.test table for insert_test.* added as comment to the script.

MySQL Backup Manager (mysql_bman)

• Several bugs fixed and improvements.
• Schema dump implemented now (customer enhancement request).
• Backup is now possible with MySQL 5.5 as well (Bug #31).
• All parameters can now be put in the configuration file.
• Configuration file is now more dynamic.
• Archive job fixed.
• Configuration backup fixed.
• Xtrabackup wrapper added.
• Empty schema option defaults now to all databases.
• Per schema backup should not contain drop/create database.

Have fun,
The FromDual Consulting Team

FromDual holds its annual company meeting this year in Leoforos Vravronos near Athens (20 km outside) in Greece.

We are happy to invite everybody interested in MySQL technologies (MySQL, Percona Cluster, MariaDB, Galera Cluster, etc.) to participate Wednesday evening October 9th at the Mare Nostrum Hotel in Leoforos Vravronos for exchanging ideas about MySQL.

Meeting at 17:00 in the hotel lobby.

• Presentation about Xtrabackup (25') and 5' Questions and Answers (Abdel-Mawla Gharieb, Support Engineer at FromDual)
• Presentation of a MySQL community member (25') and 5' Questions and Answers
• Break 15 min
• Presentation about Galera Cluster (25') and 5' Questions and Answers (Oli Sennhauser, CTO of FromDual))
• Presentation of a MySQL community member or discussion and questions about MySQL (25') and 5' Questions and Answers
• Dinner at the Mare Nostrum Hotel Restaurant

Please feel free to send us your suggestion about your presentation. Any technical or non-technical MySQL related topic is welcome. For example how you use MySQL in your companies or special problems you have faced and solved (or not solved yet), research work you have done on MySQL products. Business cases you solve with MySQL products, Evaluations or experience you have made. The proposal can be sent to contac@fromdual.com.

Please also let us know when you plan to participate at contact@fromdual.com. So we can arrange and organize all the infrastructure with the Hotel.

The event is free of costs for all participants.

Best Regards,
Your FromDual Team

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 0.0.0.0:10050 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:10051 0.0.0.0:* LISTEN tcp 0 0 127.0.0.1:10051 127.0.0.1:60756 TIME_WAIT tcp 0 0 127.0.0.1:10050 127.0.0.1:50191 TIME_WAIT tcp 0 0 127.0.0.1:10050 127.0.0.1:52186 ESTABLISHED tcp 0 0 127.0.0.1:10051 127.0.0.1:34445 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.

Solutions

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

• TCP State Transition Diagram
• Transmission Control Protocol
• TIME_WAIT and its design implications for protocols and scalable client server systems
• Linux documentation about network parameters

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://192.168.13.1,192.168.13.2 --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://127.0.0.1:5671,127.0.0.1:5672,127.0.0.1:5673 group = Our Galera Cluster options = gmcast.listen_addr=tcp://127.0.0.1:5674 log = /tmp/garbd.log

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

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 block_galera_node.sh 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.

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

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/server.pid). 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!

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/lvs_control.sh

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/lvs_control.sh # LOG="/tmp/lvs_control.log" LBIP="192.168.0.89" VIP="192.168.0.99" 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.

The new release of the MySQL Performance Monitor (mpm) is out!

New additions and improvements

• Easy to use templates
• Improved Security
• New Trigger Checks
• New Warnings Enabled
• Time Zone Shift Added
• New screens added
• Data transfer enabled

and much more (see below).

The MySQL Performance Monitor (mpm) for MySQL, Galera Cluster, Percona Server and MariaDB is a Monitoring Solution based on the Enterprise open source Monitor Zabbix.

It provides all the necessary modules to monitor MySQL performance metrics in detail and you can display them graphically.

New Features

Template improved for easier use - Security module added - Slave and MySQL templates fixed after feedback from customers - Innodb log information and pending I/O information added - Sort_buffer_size trigger added - Slave error skipped trigger added - Check for isolation level added - Binlog do and ignore filter warning enabled - Innodb deadlock trigger downgraded from warning to info - Time-shift feature implemented - Flush_time trigger added - MyISAM flush_time variable added - Read_buffer_size and max_allowed_package conflict implemented - Binlog_cache_size too small trigger back-ported from live - Read_buffer_size rule added - Max_allowed_packet and read_buffer_size added for rules - All 24 cores added to template items - Process memory graph with RAM size - Thread_stack_size too small alert implemented - Network packet graph reordered - Trigger for missing MaaS agent added - Server screens visually improved - Screens for Galera, network, CPU and server added - Transfer of data over HTTPS is possible now.

Download and Install

The most recent FromDual Performance Monitor for MySQL you can download from here... Or for more information you can reach us by e-mail at: contact@fromdual.com or by phone on +41 44 940 24 82.

Due to customer requests, we have added two of our advanced MySQL training courses in late April in Zurich, Switzerland. One course will be presented in German, the other in English.

The venue is the HSO in Zurich-Oerlikon.

The following dates has been set:

April 22 - 26Advanced MySQLHSO,​ ZurichApril 29 - Mai 3Advanced MySQLHSO,​ Zurich

Caution: the 2nd training contains May 1st (bank holiday). The training takes place regardless.

Remember to book now to reserve your place.

The MySQL SIG of DOAG invites you to join on February 27 at the Inside Hotel in Munich.
Topics of the SIG Event: MySQL Replication and new Features.

Taxonomy upgrade extras: trainingMySQL Clustermysql-training

The dates for the next MySQL Cluster (ndb) trainings with the Linux Hotel are scheduled now: March 11 - 12 2013 and September 23 - 24 2013.
You can book your training here.

Taxonomy upgrade extras: doagreplicationgaleramysql

On Tuesday, September 4, 2012 the next DOAG SIG MySQL meeting will take place in Hamburg (Germany) with the topic Replication.

Possible presentations are: MySQL Replication, Galera Cluster, Replication with Oracle GoldenGate, Replication with Zimory Scale and Tungsten Replicator.

The event will presumably be located at the Hotel Böttcherhof at Wöhlerstrasse 2.

FromDual is having a talk at Frankfurter Datenbanktage 2013, March 14/15

Taxonomy upgrade extras: High Availabilitymysqlconsultingclusterinnodbfromdualsupportgaleracodershipmysql-supportmysql-consulting

Helsinki, Finland, Uster, Switzerland – February 18, 2012 – Codership, the provider of Galera Cluster for MySQL, and FromDual, a MySQL consulting company, today announced collaboration to offer Galera Cluster technology and related support and consulting services for Galera users all over the world, especially in German speaking countries Germany, Austria and Switzerland (DACH). Galera Cluster is a synchronous, true multi-master replication cluster for MySQL using the well known InnoDB storage engine. Customers can deploy Galera Cluster locally in LAN environments, as geo-clusters over the WAN or as virtual cluster in the cloud.

Galera Cluster for MySQL is offered as open source software. It can be downloaded freely from www.codership.com. Many of Codership customers are using Galera Cluster for business critical applications. FromDual will be offering consulting and support services for Galera users, especially in German speaking countries. FromDual has years of competence in MySQL consulting and support. They have a deep understanding of clustering and replication technologies and how to implement them into production. FromDual is the perfect partner for Codership said Seppo Jaakola, CEO of Codership.

With Galera we get a great high availability (HA) product for MySQL which removes all the pain we had with the existing MySQL HA solutions. Our customers are always looking for better ways to solve MySQL replication, scalability and management issues to secure 24/7 business availability and growth for the future business. Galera Cluster does exactly that. We are happy to add Galera Cluster to our consulting and support portfolio with the top level support from Codership says Oli Sennhauser, Senior MySQL consultant and CEO of FromDual.

Galera Cluster is used by users who need highly available MySQL database back-ends with very fast fail-over time for business critical applications like Social networks, Gaming platforms, Telecom/VoiP solutions, Media and entertainment sites, Business Software as a Service (SaaS), Platform as a Service (PaaS), ERP systems, web-shops, e-commerce solutions or similar critical applications.

About FromDual GmbH

FromDual is a global acting, neutral and vendor independent consulting, support and training company for MySQL, Percona Server, MariaDB and Galera Cluster with numerous customers all over the world in the Telecom, Media, Public Service, Internet and Industry sector. FromDual is Oracle Silver Partner and Open Database Alliance (ODBA) Silver Partner. In addition to its MySQL services FromDual distributes its Performance Monitor for MySQL for local installations or as Software as a Service solution. For more information about FromDual, their products, consulting and support services, please visit www.fromdual.com.

Press contact:

FromDual GmbH
Oli Sennhauser, CEO
E-Mail: oli.sennhauser@fromdual.com
Cell: +41 79 830 09 33

About Codership Oy

Codership develops fundamentally new replication and clustering solutions for open source databases, adopting novel ideas from latest DBMS and distributed computing research. The founders of Codership have long experience in developing several widely used MySQL clustering solutions before starting Galera development in 2007. Already thousands of users have chosen Galera Cluster solution – the Codership's flagship open source product and Codership is working actively for and with this growing user community. For more information about Codership, products, consulting and support services, please visit www.codership.com.

Press contact:

Codership OY
Seppo Jaakola, CEO
E-Mail: seppo.jaakola@codership.com
Cell: +358 405 105 938