High-Availability MySQL DB based on DRBD-Heartbeat

1
High-Availability MySQL DB based on
DRBD-Heartbeat

• Ming Yue
• September 27, 2007

2
Outline
1 Motivation
2 DRBD Distributed Replication Block Device
3 Integrating DRBD-Heartbeat-MySQL
4 Failovers
5 Discussion
3
Outline
1 Motivation
2 DRBD Distributed Replication Block Device
3 Integrating DRBD-Heartbeat-MySQL
4 Failovers
5 Discussion
4
1 Motivation
To minimize service interruption time due to
unacceptable long-time recovery
To maximize the uptime of our system
Solutions?
High-Availability
Network Redundancy
Failover
DRBD
Heartbeat
5
Outline
1 Motivation
2 DRBD Distributed Replication Block Device
3 Integrating DRBD-Heartbeat-MySQL
4 Failovers
5 Discussion
6
2 DRBD Distributed Replication Block Device
VFS
VFS
Block Device
Block Device
Block Device
Block Device
. . .
. . .
Mapping Layer
Mapping Layer
Disk
Disk
Network
7
2 DRBD Distributed Replication Block Device
(continued)
A loadable kernel module of Linux
providing real-time data replication
to other block device on remote machine.
8
2 DRBD Distributed Replication Block Device
(continued)
/dev/drbd0
(not mountable)
mounted
/dev/drbd0
Block Device
Block Device
/mydrbd
Network
Primary devdrbd01
Secondary devdrbd02
Disk
Disk
Real-time Replication
- Create file system on /dev/drbd0 of Primary
node at first.
- Only Primary nodes /dev/drbd0 can be mounted
to its local directories.
- Primary/Secondary status is specified manually
if no Heartbeat.
9
2 DRBD Distributed Replication Block Device
(continued)
/dev/drbd0
(not mountable)
(not mountable)
/dev/drbd0
Block Device
Block Device
Network
Secondary devdrbd01
Secondary devdrbd02
Disk
Disk
10
2 DRBD Distributed Replication Block Device
(continued)
/dev/drbd0
(not mountable)
mounted
/dev/drbd0
Block Device
Block Device
/mydrbd
Network
Secondary devdrbd01
Primary devdrbd02
Disk
Disk
Real-time Replication
After manual Primary/Secondary status switch
11
Outline
1 Motivation
2 DRBD Distributed Replication Block Device
3 Integrating DRBD-Heartbeat-MySQL
4 Failovers
5 Discussion
12
3.1 What does heartbeat serve here?
Heartbeat A well known high-availability
resource manager
In DRBD-Heartbeat-MySQL configuration
1. Provides the Virtual IP address interface
2. Auto-starts MySQL server on the Primary
3. Talks with the peers heartbeat process and
starts failover if the Primarys heartbeat
doesnt respond
13
3.2 Integrating DRBD-Heartbeat-MySQL
Client
Client
Client
devdrbd (virtual IP address/hostname)
Secondary devdrbd02
Primary devdrbd01
MySQL server
DRBD Replication
Eth0 Interface
Not mountable
/dev/drbd0
mounted
/dev/drbd0
/var/lib/mysql
Disk
Disk
Heartbeat
Heartbeat
Heartbeat Probing
14
Outline
1 Motivation
2 DRBD Distributed Replication Block Device
3 Integrating DRBD-Heartbeat-MySQL
4 Failovers
4.1 Failover after Power/Network Interruption
5 Discussion
4.2 DRBD Synchronization
4.3 Failover in MySQLstat Plot
15
4.1 Failover Power/Network Interruption on the
Primary
devdrbd (virtual IP address/hostname)
Secondary devdrbd02
Primary devdrbd01
Power Off
Un-plug eth0 Cable
MySQL server
Eth0 Interface
Not mountable
DRBD Replication
No peer to Replicate
/dev/drbd0
mounted
/dev/drbd0
of
/var/lib/mysql
No response from peer heartbeat. Switch!
Disk
Disk
No peer responding
Heartbeat
Heartbeat
Heartbeat Probing
devdrbd01
16
4.1 Failover Power/Network Interruption on the
Primary
devdrbd (virtual IP address/hostname)
Primary devdrbd02
MySQL server
Eth0 Interface
No DRBD Replication
/dev/drbd0
mounted
/var/lib/mysql
Disk
Heartbeat
Heartbeat Probing
17
4.1 Failover Power/Network Interruption on the
Primary
devdrbd (virtual IP address/hostname)
Primary devdrbd02
Secondary devdrbd01
DRBD Synchronization
Eth0 Interface
MySQL server
Not mountable
/dev/drbd0
mounted
DRBD Replication
/dev/drbd0
/var/lib/mysql
Disk
Disk
Heartbeat
Heartbeat
Heartbeat Probing
Recovered after some time
18
4.2 Failover DRBD Synchronization
DRBD Synchronization A DRBD procedure which
updates data of the Inconsistent node by the data
of the Up-to-Date node.
When does inconsistency happen?
If one machine is powered off or off-line,
write operation is performed only on the
on-line machine.
19
4.2 Failover DRBD Synchronization (continued)
The speed of DRBD Synchronization
In our configuration, the bandwidth of both
synchronization and normal replication is 10MB/s
in average.
Time needed for complete DRBD Synchronization
It depends on disk size. For our 4TB 64-bit
machine, almost 20 hours.
Too much time to accept?
DRBD Synchronization is smart. It chooses the
recently updated data to synchronize first.
20
4.3 Workload Simulation/Failover in MySQLstat Plot
Environment
Simulate the status and workload of PandaDB

• Two 64-bit machines configured by DRBD-heartbeat

• Install MySQL community 5.0.45 on both machines

• Use a typical PandaDB dump as the queried
  database

• Write workload generator to simulate client
  queries

• It performs balanced query to the PandaDB tables
  through virtual IP interface and multiple
  connections.

• It has re-connect feature to detect the service
  interruption and save current query state. It
  re-creates MySQL sessions when service is
  available again, and resumes the latest
  interrupted session.

21
4.3 Workload Simulation/Failover in MySQLstat Plot
1. General Queries
gt150 Client Connections
Time Gap
Time Gap
Workload
22
4.3 Workload Simulation/Failover in MySQLstat Plot
2. Query Type
5 Insert/Delete 48 Update 47 Select
3. Input/Output
8.8M/sec in average
23
Outline
1 Motivation
2 DRBD Distributed Replication Block Device
3 Integrating DRBD-Heartbeat-MySQL
4 Failovers
5 Discussion
5.1 The Comparison with Master/Slave Replication
5.2 What we have already done
5.3 What we will do next
24
5.1 The Comparison with Master/Slave Replication
Master/Slave is based on MySQL User Level
DRBD is based on block device Kernel Level.
Master/Slave is asynchronous
DRBD is synchronous.
Master/Slave has higher probability of
inconsistency.
Master/Slave has load-balancing DRBD doesnt.
Master/Slave is geographically more flexible
DRBD has to be located in the same subnet or
neighbor subnets.
25
5.2 What we have already done now
Testing InnoDB MySQL DB on the basis of
DRBD-Heartbeat Configuration (on both 32-bit and
64-bit machines)

• Configuration for integrating DRBD-Heartbeat-MySQ
  L

• Multi-connection/multi-type workload simulation
  according to the status of Panda Server

• Failover situations

• Power interruption

• Network interruption

• Automatic/manual DRBD Synchronization

• Testing time gap of failover/synchronization by
  automatically re-connect load generator

26
5.3 What we will do next

• Simulate the workload and failover feature
  through production Panda Server

• Quantitative efficiency comparison with
  Master/Slave Replication

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

27
Thank you very much! Questions?