Mark Van de Wiel

A few weeks ago I wrote about latency in the context of long-running transactions. Latency is generally considered to be the time it takes for a transaction committed on the source system to be reflected (i.e. committed) on the target system. Even though real-time data replication tools generally report latency (even at various steps along the way), you may want to consider a vendor-independent approach to measuring latency, given that tools don’t generally document how latency is measured and what is taken into consideration. That approach uses a heartbeat table.

At the most basic level a heartbeat tables contains 2 columns: source_timestamp and target_timestamp. But to make comparisons easier it is wise to add a third column to identify rows. Here is DDL to create such a table in an Oracle Database, and a sequence and PL/SQL procedure to insert a row in the table.

create table rep_heartbeat
( id number not null primary key
, source_ts timestamp default systimestamp not null
, target_ts timestamp
) ;
create sequence rep_heartbeat_seq ;
create procedure rep_heartbeat_proc as
  cursor c is
  select rep_heartbeat_seq.nextval as next_seq
  from dual ;
  r c%rowtype ;
  open c ;
  fetch c into r ;
  close c ;
  insert into rep_heartbeat (id) values (r.next_seq) ;
  commit ;
end ;

Create the same table on the destination database, but add a trigger to set the timestamp on the destination.

create trigger rep_heartbeat_bri
before insert on rep_heartbeat
for each row
  :new.target_ts := systimestamp ;
end ;

With this, all you have to do is schedule the PL/SQL procedure to execute at a regular interval. This can be done using DBMS_SCHEDULER, or DBMS_JOB, or simply in a user session calling the procedure in a loop followed by DBMS_LOCK.SLEEP(). Please note that the call to retrieve the system timestamp relies on the systems being time-synchronized (e.g. using an internet service).

The heartbeat table can answer the question how much time passed between the row being inserted into the source database and the row being inserted into the target database. Note that this is not precisely in line with the definition of latency I gave earlier (since that definition mentioned commit time, not insert time) but in most cases the heartbeat tables provides a very close approximation to actual latency.

Latency reporting becomes very interesting when you can relate latency to load on the system. The heartbeat table shows you a time-based view of the system that can be overlaid with other time-based statistics. Or, consider extending the heartbeat table (or a detail table to the heartbeat table) to send across some key load statistics from the source system to the target system to be able to do a single integrated analysis of load and latency.

What’s an acceptable latency for your replication project? We’d love to find out and see how we can do!

About Mark

Mark Van de Wiel is the CTO for HVR. He has a strong background in data replication as well as real-time Business Intelligence and analytics.

Test drive
Contact us