New white paper – MySQL Replication – Enhancing Scalability and Availability with MySQL 5.5

MySQL Replication from 1 Master to Multiple Slaves

A new white paper has been published that covers MySQL Replication – background information, how it works, how to use it and what’s new in MySQL 5.5. Simply register for the white paper at mysql.com and you’ll be sent your free copy.

The paper starts by covering the fundamental concepts behind replication such as the difference between synchronous and asynchronous replication and the idea behind semisynchronous replication.

It goes on to describe the common use-cases for replication – scaling out, high availability, geographic redundancy and offloading backups or analytics.

Various replication topologies are discussed from simple master-slave to multi-master rings.

As the title of the paper suggests, the paper covers the significant replication changes introduced in MySQL 5.5:

• Semisynchronous replication: Improved resilience by having master wait for slave to persist events.
• Slave fsync tuning & Automatic relay log recovery: Option to dictate when relay logs are written to disk rather than relying on default operating system behavior; set sync_relay_log=1 to ensure that no more than 1 statement or transaction is missing from the relay log after a crash. The slave can now recover from corrupted relay logs by requesting corrupt entries to be resent from the master. Three new options are introduced (sync-master-info, sync-relay-log and sync-relay-log-info)
• Replication Heartbeat: Automatically checks the status of the connection between the master and the slave(s), allowing a more precise failure detection mechanism. Can detect loss of connection within milliseconds (configurable). Avoid unnecessary relay log rotation when the master is idle.
• Per server replication filtering: When a server is removed from a replication ring, a surviving server can be selected to remove its outstanding replication messages once they’ve been applied by all servers.
• Precise Slave Type Conversions: Allows different types to be used on the master and slave, with automatic type promotion and demotion when using row-based replication (already possible with statement-based replication)
• Individual Log Flushing: Selectively flush server logs when using ‘FLUSH LOGS’ for greater control
• Safe logging of mixed transactions: Replicate transactions containing both InnoDB and MyISAM changes

To be better able to use replication, it helps to understand the basics about how MySQL Replication has been implemented – for example the roles of the binary and relay logs and so this is covered.

Perhaps the most useful sections are those that actually walk through using MySQL Replication – specifically these activities:

• Configuring, running and testing replication
• Migrating from traditional asynchronous replication to semisynchronous replication
• Administering & monitoring replication (including MySQL Enterprise Monitor)
• Failover and recovery

Replication is a little different when using MySQL Cluster; most significantly because you can have multiple MySQL Servers and other application nodes modifying the same database but also because the domains where MySQL Cluster has been used have required extreme High Availability and so there are Cluster-unique features such as active-active replication with conflict detection and resolution. This paper isn’t focussed on MySQL Cluster but a summary of these differences is included.

New white paper: Guide to Optimizing Performance of the MySQL Cluster Database

MySQL Cluster Connection Pooling

This guide explores how to tune and optimize the MySQL Cluster database to handle diverse workload requirements. It discusses data access patterns and how to build distribution awareness into applications, before exploring schema and query optimization, tuning of parameters and how to get the best out of the latest innovations in hardware design.

The Guide concludes with recent performance benchmarks conducted with the MySQL Cluster database, an overview of how MySQL Cluster can be integrated with other MySQL storage engines, before summarizing additional resources that will enable you to optimize MySQL Cluster performance with your applications.

Download the white paper (as always, for free) from: http://www.mysql.com/why-mysql/white-papers/mysql_wp_cluster_perfomance.php

Introduction

Just a quick introduction to begin with.

I joined Sun Microsystems in Feb 2009 to look after the product management for MySQL Cluster and MySQL replication.

I started my career with Nortel (technically BNR which was the R&D arm of Northern Telecom but everything later got merged and rebranded as Nortel). I was responsible for writing the original proprietary, in-memory database for Nortel’s HLR product. Later on, we used a number of 3rd party databases for the HLR (provisioning rather than real-time) and then HSS – starting with Oracle for the HLR and then SOLID but then settling on MySQL Cluster as the scaleable real-time database for the HSS.

When I left Nortel (via an IBM rebadging) I moved to Sun, hoping to use my experience as a MySQL Cluster customer to help the team build upon their strong product.

So far, so good – I’ve been really impressed both with the MySQL team and with how well the product is doing both with telco companies and others.

I’m planning on using this blog to publish technical content, publicise product information as well as anything else that crops up.