Tuesday, April 13, 2010

Replication features in MySQL 5.5.3

We are happy to announce the new and exciting replication features in the MySQL 5.5.3 development release.

If you are at the MySQL User Conference, you are most welcome to join the "New Replication Features" talk at 2pm today where these will be presented in more detail.
  • Semi-sync replication plugin - you can now have improved resilience of the overall replication by having a master server to wait for the slave server to collect and persist events in the relay log on a per transaction basis. As a consequence, by the time the user commits a transaction there is a guarantee that the events are already written to one or more slaves relay logs. This feature is provided as a plugin and using it is as easy as installing the plugin and enabling it.
  • Slave fsync tuning - you can now fine tune relay logs and {master|relay-log}.info fsyncs operations so that corruption is much less likely on slave crashes. Three new parameters allow you to choose how often IO and SQL threads will issue file sync. You can tweak the options to your specific needs and set them on server startup.
  • Relay log corruption recovery - you can now automatically recover corrupted slave relay logs. When this recovery mechanism is active, it will, on slave startup, search for corrupted relay logs and if corruption is found it discards them and contacts the master so that these are re-transferred. This feature, together with the new fsync options, increases slave resilience to crash faults.
  • Replication Heartbeat - MySQL slaves will now request from the master that it sends a heartbeat (event) periodically. This is done when the slave connects to the master and the heartbeat period is configurable by the user. You can now avoid the spurious relay log rotation when the master is idle and have a more precise failure detection mechanism.
  • Show Relay Logs - you can now show relay log contents, as you would do for the binlogs, by issuing 'SHOW RELAYLOG EVENTS'. This may be used for comparing the events on the master binlog against the ones in the slave relay log. In a chained replication topology, the show commands provide the ability to painlessly inspect the binary and relay logs in the servers that act both as master and slave. Additionally, it may also be used to diagnose relay log corruption.
  • Per master server event filtering - you can now instruct a MySQL slave to discard events from a specific master. The 'CHANGE MASTER' has been enriched with a new 'IGNORE_SERVER_IDS = ...' clause that allows you to configure which events should be discarded. This feature is useful in circular replication topologies. In such scenarios, a server originating an event is also responsible for removing it from the chain. However, if it is removed from the chain, its events may loop forever. With this new feature, you can instruct the replacing server to remove the old server events.
  • Slave Type Conversions - unlike statement-based replication, when using row-based replication replication between columns with different types was limited. Starting from 5.5, type promotion and demotion is now supported in row-based replication. For instance, one can now replicate seamlessly from a TINYINT column on the master to an INT column on the slave, from a CHAR(10) column to a VARCHAR(20) column, or even from a TEXT column to a VARCHAR(20) column.
  • Individual Log Flushing - the user can now selectively flush server logs when using 'FLUSH LOGS'. Given that this command has a tight connection with binary and relay log maintenance, this means that every time the user wanted to flush such logs the other server logs would flush as well. Starting from 5.5, the user can now specify which individual log to flush by providing the log type as an argument to the 'FLUSH LOGS' command.
  • Writing Non-Transactional events Directly To The Binary Log - starting from 5.5, it is safe to mix non-transactional and transactional statements in the same transaction. In row and mixed format the non-transactional statement is immediately logged instead of waiting until commit time.
To try out these features, you can download MySQL 5.5.3 from:

As always, we appreciate the community feedback. Please feel free to drop some comments or contact us.

8 comments:

Jan said...

I haven't found any docs on the protocol side of semi-sync replication. Is http://jan.kneschke.de/2010/4/10/mysql-5-5-s-semi-sync-replication-the-protocol-side every thing ?

Shlomi N. said...

These are much awaited features. Well done to the replication team!

Looking forward to global transaction Ids, which make it even easier to resync master+slaves.

Andy said...

What about mult-threaded slave replication?

http://forge.mysql.com/worklog/task.php?id=4648

Any estimate on when will that be available?

Lars Thalmann said...

@Shlomi and @Andy, thanks for the input. @Andy, multi-threaded slave is a complex problem. We have written some prototype code, but it needs more work and lots of testing. It will not be in 5.5.

Lars Thalmann said...

@Jan, Yes, Zhenxing confirms that it is it. Feel free to email him with questions about semisync.

Mark Callaghan said...

And now all of the work done by the replication team over the last 2 years is much more obvious. These are great and will make it much easier to manage many slaves on servers that aren't perfect.

Andy said...

Lars,

How much performance penalty is normally incurred by using replication?

In the manual (http://dev.mysql.com/doc/refman/5.5/en/binary-log.html) it was said that "Running a server with binary logging enabled makes performance about 1% slower"

However in this talk (http://en.oreilly.com/mysql2010/public/schedule/detail/13515) they tested a 44% performance drop when replicaton is used, and a 90% drop with sync binlog (Slide 39)

Those are huge performance penalties. What were the causes? Is there any way to avoid those penalties?

Lars Thalmann said...

@Andy, there is a performance drop when the binlog is enabled. The performance drop is dependent on your setup and load. We are checking this to see if the manual should be updated. After introducing the recent InnoDB performance improvements, we now are now looking at improving the binlog performance, see http://forge.mysql.com/worklog/task.php?id=5223