There have been many advancements in MySQL 5.5, a majority of them have been towards improving performance and scalability. InnoDB is now the default storage engine, which provides higher levels of performance and reliability than MyISAM. InnoDB 1.1 that is, a rearchitected InnoDB with many performance and scalability features over and above the built-in InnoDB in 5.1 and before. The industry switch from increasing CPU clock speed, to increasing the number of cores, left MySQL with a number of bottlenecks that prevented it from taking advantage of the extra processing cycles made available. This is no longer the case with MySQL 5.5, which takes full advantage of SMP systems. One area however, that I feel will be of particular interest to web hosts, are the advances in database replication in MySQL 5.5.

Any web host that has implemented MySQL replication knows that it works well…….as long as both Master/Slave stay online. Once they fall out of sync, things can start to get ugly. True to their word, the MySQL replication team have been hard at work and have included some very exciting replication features in MySQL 5.5 that overcome these problems. Lets take a look at a few of them:

Semi-synchronous Replication

In older versions, the Master server writes its updates to a binlog and then moves on to handle other requests. The Salves are expected to keep themselves up to date by retrieving information from the binary logs. In v5.5, the Master waits for at least one slave server in the calling chain to collect and persist events in their relay log on a per transaction basis. This guarantees that by the time a user commits a transaction, at least one slave has written the events to its relay log.

In the case where no acknowledgment is received from a slave, the Master switches back to the old asynchronous replication till one of the salves is able to catch up. The semi-synchronous replication feature is available as a plugin that must be installed and then enabled on both the Master and the Salve.

To enable the semi-synchronous replicator on the master:

INSTALL PLUGIN 'rpl_semi_sync_master' SONAME / 'semisync_master.so';
SET rpl_semi_sync_master_enabled=1;
SET rpl_semi_sync_master_timeout=1000; (1s, default 10s)

To enable the semi-synchronous replicator on a slave or slaves:

INSTALL PLUGIN 'rpl_semi_sync_slave' SONAME 'semisync_slave.so';
SET rpl_semi_sync_slave_enabled=1;
START SLAVE;

The SHOW VARIABLES and SHOW STATUS commands will now expose new system and status variables that can be used to check on configuration and operational status.

Replication Heartbeat

In MySQL 5.5, the user can configure a “Heartbeat” interval on the Slave. When the Slave connects to the Master, it tells the Master to send out messages(Heartbeats) at these regular intervals. The Slave is then able to check for these messages. If it does not receive them, it knows that the connection to the Master has failed in some way.

Replication heartbeat is an optional configuration and is enabled on the 5.5 slave using:

STOP SLAVE;
CHANGE MASTER TO master_heartbeat_period= milliseconds;
START SLAVE;

The following status variables can then be monitored to easily detect when a master is idle and to get a finer-grained estimate on slave seconds behind master for recovery purposes:

SHOW STATUS like 'slave_heartbeat period'
SHOW STATUS like 'slave_received_heartbeats'

In the next part of this post, I’ll talk about 3 other exciting new replication features introduced in MySQL 5.5: Automatic Relay Log Recovery, Replication Per Server Filtering and Replication Slave Side Data Type Conversions.

About the Author:

Hamish joined Bobcares in July of 2004, and since then has grown to be well versed in the Control Panels and Operating systems used in the Web Hosting industry today. He is highly passionate about Linux and is a great evangelist of open-source. When not at work, he keeps himself busy populating this blog with both technical and non-technical posts. When he is not on his Xbox, he is an avid movie lover and critic