Tuesday, April 03, 2012

Using Heartbeat tables to monitor Database replication Part 1

Using heartbeats in database tables is a very widely used concept for an application to determine whether two or more database nodes are in sync. Part 1 of this series of articles describes the basic scenario of using a heartbeat table in a simple master slave replication.

In a simple master->slave replication, an application would normally run reports on the slave node. How can the application ensure that the slave is uptodate? One method is by creating a simple table on the master database called HeartBeat (as an example) and have one TIMESTAMP/DATE field. A job (database job/server job) will run every 10 minutes and update this field with the current timestamp. This table is also replicated to the slave, so both nodes have a HeartBeat table. This is shown in the figure below:

The application will read the timestamp field from the heartbeat table and compare it to current time. In this way, the application can detect any lag or replication errors. The figure below demonstrates such scenario:

When the master stops replicating, the HearBeat table on the Master keeps updating itself but this is not reflected onto the Slave. The application will read the date from the Slave's HeartBeat table and detects a lag from the current timestamp and alerts the user.
Post a Comment