MySQL conference in Paris - High-availability with MySQL

Here's another good talk from the MySQL conference in Paris today: High-availability with MySQL by Max Mether, Sun Microsystems

Again, here are my raw notes.

MySQL replication

• Pros
  
  • Simple to use and maintain
  • Support for "active-active'" configuration, but without conflict resolution --> logical separation required to prevent them
• Cons
  
  • Asynchronous
  • No automatic failover (heartbeat must be installed to support HA)
  • No default monitoring
  • slave is mono-thread --> slave can lag behind on multiple transactional updates to the same table
• Binlog files : change requests (up to 5.1), change images (since 5.1)
  • every change to the binlog is sent to the slave, which writes it to the relay log (I/O thread)
  • every change to the relay log is executed by the slave (SQL thread)

DRBD

• Distributed Replicated Block Device
• Synchronous replication :
  • I/O operations are sent via TCP/IP to the slave, which commits them to disk and confirms the operation.
  • Active / passive configuration
• Works great with Linux HA & heartbeat : failover complete in about 30 seconds
• Pros
  • Simple
  • Synchronous
  • Integration with Linux HA
• Cons : small performance impact
• Continuent :
  • Active / active configuration
  • Written in Java
  • The UniCluster controller replicates data between both nodes
  • Support for Oracle / MySQL replication

Shared storage

• Active / Passive configuration
• Automatic management
  • Virtual IPs
  • Failover
  • Data synchronization
  • File system management
• Pros
  • Good performance
• Cons
  
  • Storage can be a SPOF
  • Costly NAS/SAN storage more expensive than DAS
    
  • Unused resources
  • Failover time is higher

MySQL cluster

• "Shared-nothing" architecture (no SPOF)
• Technology bought from Ericsson in 2003 (initially developed for telcos)
• Three node types
  • Data nodes (aka MySQL servers)
    • Store and manage data
    • Automative failover
    • Automatic synchronous replication within a node group
    • Data can be partitioned between node groups
    • All nodes can be active
  • SQL nodes
    • SQL processing
    • Native API
  • Management nodes
• Cons
  
  • multiple MySQL servers required (3 minimum, 5 recommended)
  • SQL processing not always optimized for clustering