CSQL as Multiple Bi-directional Cache Node for MySql

CSQL Cache is generic database caching platform to cache frequently accessed tables from your existing open source or commercial database management system (Oracle, MySQL, Postgres etc) close to application tier. It uses the fastest Main Memory Database (CSQL MMDB) designed for high performance and high volume data computing to cache the table and enables real time applications to provide faster and predictive response time with high throughput.

Supported Feature Summary

* Full table

* Partial table

* Read only table

* Bi-directional table updates

* Transparent Caching

* Synchronous, Asynchronous Update Propagation Modes

* High Availability

* And More

For the Multiple bi-directional CSQL cache for MySql as single data source, Be sure that MySql database is installed and currently running. First create the log table in MySql to hold the log records using the SQL statements given below using tool or isql tool.

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

Let us consider there are two CSQL cache node. Make changes in csql.conf file CACHE_ID = 1 for one cache node and CACHE_ID = 2 in other cache node and make sure that CACHE_TABLE, ENABLE_BIDIRECTIONAL_CACHE are set to true. For same machine change SYS_DB_KEY and USER_DB_KEY values in both of nodes. Again DSN should set to myodbc3 for MySql . Set “/etc/odbcinst.ini” and “/etc/odbc.ini” file properly show that isql tool will work properly.

Lets a table “t1” having primary key “f1″ integer and f1 char to be cached. Create trigger (say trigger.sql) as per following format for MySql database.

drop trigger if exists triggerinsertt1; 
drop trigger if exists triggerupdatet1;
drop trigger if exists triggerdeletet1;
DELIMITER |
create trigger triggerinsertt1
AFTER INSERT on t1
FOR EACH ROW
BEGIN
Insert into csql_log_int (tablename, pkid, operation,cacheid )values (’t1′, NEW.f1, 1,1);
Insert into csql_log_int (tablename, pkid, operation,cacheid )values (’t1′, NEW.f1, 1,2);
End;
create trigger triggerupdatet1
AFTER UPDATE on t1
FOR EACH ROW
BEGIN
Insert into csql_log_int (tablename, pkid, operation, cacheid ) values (’t1′, OLD.f1, 2,1);
Insert into csql_log_int (tablename, pkid, operation,cacheid ) values (’t1′, NEW.f1, 1,1);
Insert into csql_log_int (tablename, pkid, operation, cacheid ) values (’t1′, OLD.f1, 2,2);
Insert into csql_log_int (tablename, pkid, operation,cacheid ) values (’t1′, NEW.f1, 1,2);
End;
create trigger triggerdeletet1
AFTER DELETE on t1
FOR EACH ROW
BEGIN
Insert into csql_log_int (tablename, pkid, operation, cacheid )values (’t1′, OLD.f1, 2,1);
Insert into csql_log_int (tablename, pkid, operation, cacheid )values (’t1′, 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 t1 table.

$ mysql -u root -p

Run two cache node server identified by cache id 1 and 2 and cache t1 tables in both the node by using following tool.

$ cachetable –U root –U manager –t t1
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: