high-availability mysql db based on drbd-heartbeat

High-Availability MySQL DB based on DRBD-Heartbeat

Ming YueMing Yue September 27, 2007September 27, 2007

2 DRBD: Distributed Replication Block 2 DRBD: Distributed Replication Block DeviceDevice

1 Motivation1 Motivation

3 Integrating DRBD-Heartbeat-MySQL3 Integrating DRBD-Heartbeat-MySQL

4 Failovers4 Failovers

OutlineOutline

5 Discussion5 Discussion

OutlineOutline







To To minimize minimize service interruption time service interruption time due to unacceptable long-time due to unacceptable long-time recovery;recovery;To To maximizemaximize the uptime of our system; the uptime of our system;

SolutionsSolutions? ? Network Network RedundancyRedundancy

+ Failover+ Failover

DRBDDRBD + Heartbeat+ Heartbeat

High-AvailabilityHigh-Availability

OutlineOutline






2 DRBD:2 DRBD: DDistributedistributed RReplicationeplication BBlocklock DDeviceevice

VFS

BlockDevice

BlockDevice

. . .

Mapping Layer

Disk

VFS

BlockDevice

BlockDevice

. . .

Mapping Layer

DiskNetworNetworkk

2 DRBD:2 DRBD: DDistributed istributed RReplicationeplication BBlocklock DDevice (continued)evice (continued)

A loadable kernel A loadable kernel modulemodule of of LinuxLinuxproviding providing real-time real-time data data replicationreplication to other block device on to other block device on remoteremote machine.machine.

BlockDevice

Disk

BlockDevice

Disk

Network

/dev/drbd0

Primary Primary “devdrbd01”“devdrbd01”

SecondarSecondaryy

“devdrbd02“devdrbd02

””Real-time Replication

- Primary/Secondary status is specified - Primary/Secondary status is specified manually if no Heartbeat.manually if no Heartbeat.

- Create file system on /dev/drbd0 of - Create file system on /dev/drbd0 of PrimaryPrimary node at node at first.first.- - Only PrimaryOnly Primary node’s /dev/drbd0 can be mounted to its local node’s /dev/drbd0 can be mounted to its local directories.directories.

/mydrb/mydrbdd

(not (not mountable)mountable)

mountemountedd /dev/

drbd0

2 DRBD:2 DRBD: DDistributedistributed RReplicationeplication BBlocklock DDevice evice (continued)(continued)

BlockDevice

Disk

BlockDevice

Disk

Network

/dev/drbd0 /dev/

drbd0



SecondarSecondaryy

“devdrbd01”“devdrbd01”

SecondarSecondary y

“devdrbd02“devdrbd02

””


BlockDevice

Disk

BlockDevice

Disk

Network

/dev/drbd0

SecondarSecondary y


PrimaryPrimary “devdrbd02“devdrbd02

””

/dev/drbd0

Real-time Replication

After manual Primary/Secondary status After manual Primary/Secondary status switchswitch

mountedmounted/mydrbd/mydrbd



OutlineOutline






Heartbeat:Heartbeat: A well known high- A well known high-availability resource manager availability resource manager

3.1 What does heartbeat serve 3.1 What does heartbeat serve here?here?

In DRBD-Heartbeat-MySQL configuration:In DRBD-Heartbeat-MySQL configuration:

1. Provides the 1. Provides the Virtual IP address Virtual IP address interfaceinterface

2. Auto-starts 2. Auto-starts MySQL MySQL server on the server on the PrimaryPrimary

3. 3. TalksTalks with the peer’s heartbeat with the peer’s heartbeat process and starts process and starts failover failover if the if the Primary’s heartbeat doesn’t respondPrimary’s heartbeat doesn’t respond

3.2 Integrating DRBD-Heartbeat-MySQL3.2 Integrating DRBD-Heartbeat-MySQL

“devdrbd” (virtual IP address/hostname)

ClientClient Client

/dev/drbd0

MySQL server

Disk

/var/lib/mysql

Heartbeat

/dev/drbd0

DiskHeartbeat

mounted DRBD Replication

Not mountable

SecondarSecondary y



Eth0 Interface

Heartbeat Probing




OutlineOutline


5 Discussion5 Discussion4.1 Failover after Power/Network Interruption4.2 DRBD Synchronization

4.3 Failover in MySQLstat Plot

4.1 Failover – Power/Network Interruption on the 4.1 Failover – Power/Network Interruption on the PrimaryPrimary


/dev/drbd0

MySQL server

Disk

/var/lib/mysql

Heartbeat

/dev/drbd0

DiskHeartbeat

mounted

Not mountable

SecondarSecondary y


PrimaryPrimary “devdrbd01”“devdrbd01”

Eth0 Interface

Heartbeat Probing

DRBD ReplicationPower OffNo peer to Replicate

Un-plug eth0 Cable

“devdrbd01”

of

No peer respondingNo response from peer heartbeat.

Switch!


/dev/drbd0

DiskHeartbeat

/var/lib/mysql

MySQL server

mounted

Heartbeat Probing

Eth0 Interface

No DRBD Replication




/dev/drbd0

Disk

/dev/drbd0

DiskHeartbeat

/var/lib/mysql

PrimaryPrimary “devdrbd02”“devdrbd02”

SecondarSecondary y

“devdrbd01”“devdrbd01” Eth0 Interface

Heartbeat Probing

Not mountable

MySQL server

mounted

DRBD Replication

Heartbeat

DRBD Synchronization

Recovered after some Recovered after some timetime


4.2 Failover – DRBD Synchronization4.2 Failover – DRBD Synchronization

DRBD Synchronization:DRBD Synchronization: A DRBD A DRBD procedure which procedure which updatesupdates data of the data of the InconsistentInconsistent node by the data of the node by the data of the Up-to-DateUp-to-Date node. node.

When doesWhen does inconsistencyinconsistency happen?happen?If one machine is powered off or off-If one machine is powered off or off-line, line, “write”“write” operation is performed operation is performed onlyonly on the on-line machine. on the on-line machine.

4.2 Failover – DRBD Synchronization 4.2 Failover – DRBD Synchronization (continued)(continued)

The speed of DRBD SynchronizationThe speed of DRBD Synchronization

In our configuration, the bandwidth of In our configuration, the bandwidth of both both synchronizationsynchronization and normal and normal replicationreplication is 10MB/s in average. is 10MB/s in average.

Time needed for complete DRBD Time needed for complete DRBD SynchronizationSynchronization

Too much time to accept? Too much time to accept?

DRBD Synchronization is smart. It chooses DRBD Synchronization is smart. It chooses the the recently updatedrecently updated data to synchronize data to synchronize first.first.

It depends on disk size. For our 4TB 64-It depends on disk size. For our 4TB 64-bit machine, almost 20 hours.bit machine, almost 20 hours.

4.3 Workload Simulation/Failover in MySQLstat Plot4.3 Workload Simulation/Failover in MySQLstat Plot

Environment:Environment:

Two 64-bit machines configured by Two 64-bit machines configured by DRBD-heartbeatDRBD-heartbeat

Install Install MySQLMySQL community 5.0.45 on both machines community 5.0.45 on both machines

Simulate the status and workload ofSimulate the status and workload of PandaDBPandaDB

Use a typical Use a typical PandaDB dumpPandaDB dump as the queried database as the queried database Write Write workload generatorworkload generator to simulate client to simulate client queriesqueries

It performs balanced query to the PandaDB tablIt performs balanced query to the PandaDB tables through es through virtual IPvirtual IP interface and interface and multiple connemultiple connections.ctions. It has It has re-connectre-connect feature to detect the feature to detect the service interrupservice interruptiontion and save current query state. It and save current query state. It re-createsre-creates MySQL se MySQL sessions when service is ssions when service is available again,available again, and and resumesresumes the l the latest interrupted session.atest interrupted session.

1. General 1. General QueriesQueries

Tim

e G

ap

Tim

e G

apWorkload

>150 Client Connections


3. Input/Output3. Input/Output

2. Query Type2. Query Type

5% Insert/Delete48% Update47% Select

8.8M/sec in average





OutlineOutline



5.1 The Comparison with Master/Slave Replication5.2 What we have already done

5.3 What we will do next

5.1 The Comparison with Master/Slave 5.1 The Comparison with Master/Slave ReplicationReplication

Master/Slave is based on MySQL – Master/Slave is based on MySQL – User Level;User Level;DRBD is based on block device – DRBD is based on block device – Kernel Kernel Level.Level.Master/Slave is Master/Slave is asynchronous; asynchronous;

DRBD is DRBD is synchronous.synchronous.

Master/Slave has higher probability of Master/Slave has higher probability of inconsistency.inconsistency.

Master/Slave has Master/Slave has load-balancingload-balancing; DRBD ; DRBD doesn’t.doesn’t.Master/Slave isMaster/Slave is geographically more flexible;geographically more flexible;DRBD has to be located in the DRBD has to be located in the same subnetsame subnet or neighbor subnets.or neighbor subnets.

5.2 What we have already done now 5.2 What we have already done now

Testing Testing InnoDB MySQL DBInnoDB MySQL DB on the basis of DRBD-Heartbea on the basis of DRBD-Heartbeat Configuration (on both 32-bit and 64-bit machines):t Configuration (on both 32-bit and 64-bit machines):

Configuration for Configuration for integratingintegrating DRBD-Heartbeat-MySQL DRBD-Heartbeat-MySQL Multi-connection/multi-typeMulti-connection/multi-type workload simulation workload simulation according to the status of Panda Serveraccording to the status of Panda Server

FailoverFailover situations situations

PowerPower interruption interruption

NetworkNetwork interruption interruption

Automatic/manualAutomatic/manual DRBD DRBD SynchronizationSynchronizationTesting Testing time gaptime gap of of failover/synchronization by automatically failover/synchronization by automatically re-connect load generatorre-connect load generator

5.3 What we will do 5.3 What we will do nextnext

Simulate the workload and failover feature Simulate the workload and failover feature through production through production Panda Server;Panda Server;

Quantitative Quantitative efficiency efficiency comparison comparison with with Master/Slave Replication;Master/Slave Replication;

Simulate the work load and failover feature accordinSimulate the work load and failover feature according to the status of g to the status of Archive MyISAM MySQL DB.Archive MyISAM MySQL DB. It has sm It has small number of connections, but is highly all number of connections, but is highly insert-intensivinsert-intensive.e.

Thank you very much!Thank you very much!

Questions?Questions?

high-availability mysql db based on drbd-heartbeat

Documents

drbd synchronization4

mapping layerdisknetwork2

peer heartbeat

drbdheartbeatming yue

peers heartbeat process

virtual ip address interface2

devdrbd0 of primary

primary nodes devdrbd0