Multi Node Distributed Caching

<< Cache option for IBM DB2 database - TOC - CSQL Cache Modes >>

Applications can scale up by employing multiple cache nodes to cache tables. Refer Section 3, “Deployment Scenarios” for more information on this.

Configure MySQL

To set multiple Bi-Directional caching, first create the table in MySQL to hold the log records. Triggers are installed in the target database for all the DML operations on cached table to generate log entries in the log table. The below statements is executed with mysql tool or isql tool.

Create Log Table

mysql> CREATE TABLE csql_log_int (tablename CHAR (64), pkid INT, operation INT, 
cacheid INT, id INT NOT NULL UNIQUE AUTO_INCREMENT) engine=’innodb’;

Create Trigger Lets say for a cached table p1 with primary key f1, write a trigger (trigger.sql) as below.

use test;
drop trigger if exists triggerinsertp1;
drop trigger if exists triggerupdatep1;
drop trigger if exists triggerdeletep1;
DELIMITER |
create trigger triggerinsertp1
AFTER INSERT on p1
FOR EACH ROW
BEGIN
Insert into csql_log_int (tablename, pkid, operation, cacheid) values (’p1′, NEW.f1, 1,1);
Insert into csql_log_int (tablename, pkid, operation, cacheid )values (’p1′, NEW.f1, 1,2);
End;

create trigger triggerupdatep1
AFTER UPDATE on p1
FOR EACH ROW
BEGIN
Insert into csql_log_int (tablename, pkid, operation, cacheid ) values (’p1′, OLD.f1, 2,1);
Insert into csql_log_int (tablename, pkid, operation,cacheid ) values (’p1′, NEW.f1, 1,1);
Insert into csql_log_int (tablename, pkid, operation, cacheid ) values (’p1′, OLD.f1, 2,2);
Insert into csql_log_int (tablename, pkid, operation,cacheid ) values (’p1′, NEW.f1, 1,2);
End;
create trigger triggerdeletep1
AFTER DELETE on p1
FOR EACH ROW
BEGIN
Insert into csql_log_int (tablename, pkid, operation, cacheid )values (’p1′, OLD.f1, 2,1);
Insert into csql_log_int (tablename, pkid, operation, cacheid )values (’p1′, OLD.f1, 2,2);
End;
|

Note that in above triggers, for each operation it inserts two logs into the log table, one for cache node-1 and another for cache node-2. After execution of the below command, triggers are installed on the p1 table and it is ready to cache the MySQL table and do some operations.

$ mysql -u root -p <trigger.sql

Configure multiple nodes

Install CSQL in two machines (say host-1 and host-2) and set the csql.conf file as specified in section 7.1.1. Make sure that SITE_ID parameter at host-1 is set to 1 and SITE_ID parameter at host-2 contains SITE_ID set to 2. The SITE_ID variable value must be unique for each node (host containing CSQL installation). For the first node the value is set up as 1 and for second node the value is 2. This number should be unique across all CSQL cache instances.

FIRST NODE:

CACHE_TABLE=true
SITE_ID=1
DSN=myodbc3
CACHE_MODE=SYNC 
ENABLE_BIDIRECTIONAL_CACHE=true
CACHE_RECEIVER_WAIT_SECS=10
TABLE_CONFIG_FILE=/tmp/csql/csqltable.conf
DS_CONFIG_FILE=/tmp/csql/csqlds.conf

SECOND NODE :

CACHE_TABLE=true
SITE_ID=2
DSN=myodbc3
CACHE_MODE=SYNC
ENABLE_BIDIRECTIONAL_CACHE=true
CACHE_RECEIVER_WAIT_SECS=10
TABLE_CONFIG_FILE=/tmp/csql/csqltable.conf
DS_CONFIG_FILE=/tmp/csql/csqlds.conf

Load MySQL table to Cache

Now run the csqlserver command and use the cachetable tool for caching MySQL table into cache on host-1(cache node-1). Make sure that the entries are present in csqlds.conf file for the appropriate DSN by using the ‘csqlds’ tool. Refer the section csqlds tool.

Create another terminal and move to csql root directory and set up the environment using the command.

$. ./setupenv.ksh
$ cachetable -U root -P manager -t p1 

Now check the contents of the cache table, which is present in Cache using CSQL tool.

CSQL>SELECT * FROM p1;
-------------------------
        f1      f2
-------------------------
        1       Hi
        2       All

Refer the section cachetable. Syntax and uses in detail about cachetable tool and its arguments.

Perform the above said operations (start csqlserver and run cachetable to cache 'p1' table) on host-2.

MySQL – to – CSQL update propagation

Insert some records in p1 table which is present in MySQL and the record should be propagated to the MySQL as well as other host (host-2) within the time interval specified in CACHE_RECEIVER_WAIT_SECS configuration variable. After this interval, query the table in both CSQL instances and MySQL database, the same records could be cached in both the cache instances and target database.

Insert Records in MySQL:

$isql myodbc3;
SQL> insert into p1 values (3,'NoOne');
SQLRowCount returns 1
SQL> insert into p1 values (4,'EveryOne');

Query 'p1' table in CSQL:

CSQL>select * from p1;
----------------------------------
        f1      f2
----------------------------------
        1       Hi
        2       All
        3       NoOne
        4       EveryOne

If the above query is run on host-2, it will also contain 4 records. If operations are performed on any cache node, it will be automatically propagated to target database as well as all the other cache nodes.

<< Cache option for IBM DB2 database - TOC - CSQL Cache Modes >>

Page last modified on October 23, 2009, at 06:09 AM