# 11. Data Replication, Synchronization and Transformation Services

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
Data Replication, Synchronization and Transformation Services
Introduction
Snapshot Replication
Transactional Replication
Virtuoso scheduler
Transactional Replication Example
Replication Logger Sample
Configuration of the Sample Synchronization Running the Sample Notes on the Sample's Dynamics

## 11.6. Replication Logger Sample

The logger directory in the samples in the distribution contains a simple load balancing sample. It implements a simplified web site hit log where there is a count of hits maintained per user name and origin IP of each hit.

Thus the transaction being replicated between the servers consists of incrementing an IP's hit count and then incrementing a user's hit count. If either IP or user do not have a count, a row is added with a count of 1. The transaction is then logged for replication, so that all servers get all hits, no matter which of the replicating servers processes the hit.

    create table wl_ip_cnt (ic_ip varchar, ic_cnt integer,
           primary key (ic_ip));

    create table wl_user (wu_user varchar, wu_cnt integer,
           primary key (wu_user));

    create procedure wl_hit_repl (in ip varchar, in usr varchar)
    {
      set isolation = 'serializable';
      update wl_ip_cnt set ic_cnt = ic_cnt + 1 where ic_ip = ip;
      if (0 = row_count ())
        insert into wl_ip_cnt (ic_ip, ic_cnt) values (ip, 1);
      update wl_user set wu_cnt = wu_cnt + 1 where wu_user = usr;
      if (0 = row_count ())
        insert into wl_user (wu_user, wu_cnt) values (usr, 1);
    }

    create procedure wl_hit (in ip varchar, in usr varchar)
    {
      wl_hit_repl (ip, usr);
      repl_text ('hits', 'wl_hit_repl (?, ?)', ip, usr);
    }

The application client calls wl_hit on one of the mutually replicating servers to log an event. The event's trace will then be propagated to all other servers. The wl_hit_repl function does the actual work. The top level function calls this plus logs the call with its arguments on the local server's hits publication for distribution to other servers.

### 11.6.1. Configuration of the Sample

The following sequence of calls can be used to define a network of four servers, each replicating every other server. For the sake of example, they are all on localhost and listen at ports 2001 through 2004.

    repl_server ('log1', 'localhost:2001');
    repl_server ('log2', 'localhost:2002');
    repl_server ('log3', 'localhost:2003');
    repl_server ('log4', 'localhost:2004');

    repl_publish ('hits', 'hits.log');

    repl_subscribe ('log1', 'hits');
    repl_subscribe ('log2', 'hits');
    repl_subscribe ('log3', 'hits');
    repl_subscribe ('log4', 'hits');

First all the servers are identified. Next the local server declares that it has a publication 'hits'. Next it subscribes to the hits publications of all other servers. In the process it also subscribes to itself, which signals an error and has no other effect.

In this way all servers share one configuration. Each server knows which of the servers it is based on the DBName setting in its virtuoso.ini file.

### 11.6.2. Synchronization

    create procedure log_sync ()
    {
      for select SERVER, ACCOUNT from SYS_REPL_ACCOUNTS do
        {
          if (SERVER <> repl_this_server ())
    	{
    	  declare err, msg varchar;
    	  err := '00000';
    	  exec ('repl_sync (?, ?, ?, ?)', err, msg, vector (SERVER, ACCOUNT, 'dba', 'dba'), 0);
    	}
        }
    }

This procedure will go through all subscriptions and request sync for each. Note that the repl_sync function is called inside exec to catch any possible exceptions, as servers may not be available etc. For the sake of simplicity this supplies the literal default dba login 'dba', 'dba' as authentication.

The replication sample schedules a call to this function to be made every minute as a background job. if all replication servers are on line and in sync or syncing the function will return without delay or effect. Otherwise it will keep trying until it gets a connection.

### 11.6.3. Running the Sample

The logger directory contains various scripts for starting and stopping servers etc.

log_init.sh - Creates the databases with tables and procedures loaded in the l1, l2, l3 and l4 subdirectories.

log_start.sh - starts the 4 servers and leaves them running in the background.

log_shut.sh - Shuts down the 4 test servers.

hits.sh <hist-per-hour> <no-of-hits>

Starts the hits program on each of the 4 servers. The first command line argument gives the test transaction rate for each client and the next gives the duration as a transaction count.

    hits <dsn> <uid> <pwd> <hits-per-hour> <no-of-hits>

The hits executable repeatedly calls wl_hit with random arguments and collects statistics on call times. If calls complete at a rate faster than the requested rate this periodically sleeps to keep the rate close to the requested rate. It prints statistics every 1000 hits.

### 11.6.4. Notes on the Sample's Dynamics

When the network initially starts all the publications are at level 0 and in sync. When transactions are fed into the network at a sufficiently slow rate all the servers get to process all transactions in real time. Note that the structure is such that every server does everybody else's work in addition to its own. Thus the insertion rate of the network can't be expected to be higher than that of an individual server. However read load can be spread across servers, so that this type of configuration is effective for balancing query load but not for balancing update load.

As we increase the transaction rate at each server we reach a point at which the queue of locally committed but un-replicated transactions grows faster than the other servers will absorb the feed. The servers will each eventually disconnect all synced replication to stop the queue from growing. Once the queue that no longer grows goes empty the subscribers get disconnected. At this point all servers only process their own load without any other distraction.

Next each server will notice that it is disconnected from the network and will attempt a resync as a result of the periodic scheduled call to log_sync. Each server will then re-establish a connection to every other server and start resyncing. This will lead to the network being again in sync if the per server transaction rate slows down sufficiently to allow replicators to catch up. If this does not happen the syncing can stay in progress indefinitely, until it either reaches sync or is terminated.

Typically a server's capacity for processing local transactions is greater than its capacity for replaying replication feed. This is because one thread is responsible for all replay activity while many threads can process local transactions.

The net result of this scheduling policy is that even a heavily replicated network will scale to high peak loads and will automatically return to sync state as soon as the peak is over. If guaranteed transaction level synchronicity must be maintained between servers then the application should not be written using transactional replication but rather with distributed transactions, where each commit makes sure the transaction is fully processed on each participant before returning to the client. This is however up to several times slower and will stop the entire network if a single node fails.

Previous Transactional Replication Example | Chapter Contents | Next Contents of Web Application Development