The latest from the MySQL community

So we tested the 5.6.7-RC. And ran into a strange problem:

Because of a test, a preexisting configuration with GTID enabled existed, and suddenly we did not have properly initialized grants in mysql.* created for a new installation. Turns out: GTID and non-transactional tables are no friends, and that is even documented.

When using GTIDs, updates to tables using nontransactional storage engines such as MyISAM are not supported. This is because updates to such tables mixed with updates to tables that use a transactional storage engine such as InnoDB can result in multiple GTIDs being assigned to the same transaction.

Also, this is supposed to work with GRANT and REVOKE, but not with INSERT and DELETE. Now guess what mysql-install-db and friends are using?

server:~ # less …

Global Transaction Identifiers – why, what, and how Next post: Advanced use of Global Transaction Identifiers
This post was kindly translated to Japanese by Ryusuke Kajiyama.

In MySQL 5.6 we introduced a new replication feature called Global Transaction Identifiers, or GTIDs. While there are many use cases, our primary motivation for introducing GTIDs is that it allows for seamless failover. By this, we mean promoting one of the slaves to be come a master, if the master crashes, with minimal manual intervention and service disruption.

This is the first in a series of several blog posts. We will go through several use cases and show how easy it is to do a failover. We explain …

My colleague at Booking.com, Dennis Kaarsemaker, just blogged an interesting article about the Replication Load Monitor we are using at work. This is based on work from Mark Leith, but presents a much more detailed view.

May it be useful.

GenieDB is building a database with global distribution as its core thesis. It is no secret customers demand near-instantaneous and highly reliable service, and that they are becoming more globally dispersed than ever before. We believe that data custodianship must ultimately be moved to the “edge of the web” where it can be dynamically managed in order to improve user experience, optimize network/hardware utilization and reduce TCO. A single datacenter hosted database and application stack runs afoul of this fundamental thesis in a number of ways. In this article we will focus on the issue of improving response time for users even when they are globally distributed. This is simply a matter of physics and how long it takes to transmit a packet between the two locations. No amount of application tuning can overcome this obstacle.

The obvious solution is to have multiple copies of the …

So I am getting a mail with a complaint about rising replication delays in a certain replication hierarchy.

Not good, because said hierarchy is one of the important ones. As in 'If that breaks, people are sleeping under the bridge'-important.

The theory was that the change rate in that hierarchy is too high for the single threaded nature of MySQL replication. That was supported by the observation that all affected boxes had no local datadir, but were filer clients. Filer clients as slaves are dying first because the SAN introduces communication latencies that local disks don't have, and the single threaded nature of replication is not helpful here, either. Filers are better when it comes to concurrent accesses, really.

So if that theory would hold that would really ruin my day. Make that month: Said hierarchy is just now recovering from severe refactoring surgery and should have almost no exploitable …

Come meet Tungsten replication and clustering experts. Don't miss these 5 talks:

Managing Worldwide Data with MySQL and Continuent Tungsten by Robert Hodges Replicating from MySQL to Oracle Database and Back Again by Robert Hodges MySQL High Availability: Power and Usability by Giuseppe Maxia Lessons from Managing 500+ MySQL Instances in the Cloud by Ronald Bradford Improving Performance with

Following on from my post about MySQL Cluster sessions at the forthcoming Connect conference, its now the turn of MySQL Replication - another technology at the heart of scaling and high availability for MySQL.

Unless you've only just returned from a 6-month alien abduction, you will know that MySQL 5.6 includes the largest set of replication enhancements ever packaged into a single new release:

- Global Transaction IDs + HA utilities for self-healing cluster..(yes both automatic failover and manual switchover available!)

- Crash-safe slaves and binlog

- Binlog Group Commit and Multi-Threaded Slaves for high performance

- …

After the GitHub MySQL Failover incident a lot of blogs/people have explained that fully automated failover might not be the most optimal solution.

Fully automated failover is indeed dangerous, and should  be avoided if possible. But a complete manual failover is also dangerous. A fully automated manually triggered failover is probably a better solution.

A synchronous replication solution is also not a complete solution. A split-brain situation is a good example of a failure which could happen. Of course most clusters have all kinds of safe guard to prevent that, but unfortunately also safe guards can fail.

Every failover/cluster should be considered broken unless:

1. You've tested the failover scripts and procedures
2. You've tested the failover scripts and procedures under normal load
3. You've tested the failover scripts and procedures under high load
…

There have been a number of excellent articles about the pros and cons of automatic database failover triggered by Baron's post on the GitHub database outage.  In the spirit of Peter Zaitsev's article "The Math of Automated Failover," it seems like a good time to point out that database failure is usually not the biggest source of downtime for websites or indeed applications in general.  The real culprit is maintenance.

Here is a simple table showing availability numbers out to 5 nines and what they mean in terms of monthly down-time.

High availability is about more than just making sure that applications can get to your data, even if there is a failure:

How about when you are upgrading your database schema What if you need to add memory to a database server or reconfigure/restart MySQL If your apps want to read data from a MySQL slave, how can you be sure they are not reading stale data without re-coding your apps What