Nov 19, 2008

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)
  • 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

No comments:

Post a Comment