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

CSQL: NetBeans Configuration for CSQL JDBC DRIVER

CSQL JDBC driver supports connection through driver manager, data sources object, connection pooling data source object. For Netbeans IDE, you need to configure properly for CSQL database.

Connection Through DriverManager

For the connection through DriverManager object, you need to start the CSQL server in one terminal. In another terminal run . ./setupenv.ksh in csql root directory , then go to Netbeans /bin directory and run ./netbeans . Create a java applications with following code.

public class Main {

public static void main(String[] args) {

try {

Class.forName(“csql.jdbc.JdbcSqlDriver”);

Connection con = DriverManager.getConnection(“jdbc:csql”, “root”, “manager”);

if(con!=null){System.out.println(“Connection Exstablished”);}

else {System.out.println(“Connection failed”);}

Statement cStmt = con.createStatement();

cStmt.execute(“CREATE TABLE T1 (f1 integer, f2 char (20));”);

System.out.println(“Table T1 is created”);

cStmt.close();

con.close();

}catch(Exception e) {

System.out.println(“Exception in Test: “+e);

e.printStackTrace();

}

}

Go to the projects ->libraries, right click on it and go Add JAR/Folders…Give the jar file path and click on OK . Now run Applications.

Connection Through DataSource

For DataSource Configuration, set as mention above with a web application. For example use the following jsp code.

<html>

<head>

<meta http-equiv=”Content-Type” content=”text/html; charset=UTF-8″>

<title>JSP Page</title>

</head>

<body>

<%

try{

out.println(“Table created on csql “);

javax.naming.Context cxc= new javax.naming.InitialContext();

javax.sql.DataSource ds = (javax.sql.DataSource) cxc.lookup(“jdbc/bijaya”);

java.sql.Connection conn=ds.getConnection(“root”, “manager”);

out.println(“Table created on csql “);

java.sql.Statement stmt=conn.createStatement();

stmt.execute(“CREATE TABLE papu (f1 int,f2 int);”);

out.println(“Table created on csql “);

conn.close();

} catch (java.sql.SQLException e){

out.println(“An error occurred.”);

}

%>

</body>

</html>

Start application server, go to ‘application sever admin console‘ for connection pool setup. Go to application sever, click on JVM settings, from their in Path Settings set Classpath Prefix and Native Library Path Suffix

Now go to Resources ->JDBC->Connection Pools, create new connection pool with additional propertics with URL as csql jdbc url, user, password properties and Datasource Classname as csql.jdbc.JdbcSqlDataSource. Now save and ping for successful connection. Create a JDBC Resources name.

Now run the web application.

free invisible web counter

CSQL : Postgres Configuration for Multiple bidirectional cache

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

CREATE TABLE csql_log_int(tablename varchar(64), pkid int, operation int, cacheid int);
ALTER TABLE csql_log_int add id serial;

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 . Again DSN should set to psql for Postgres . Set “/etc/odbcinst.ini” and “/etc/odbc.ini” file properly.For help refer Uni-directional cache configuration.

Lets for a cached table “t” having primary key “f1” create trigger (say trigger.psql) as per following format in the Postgres database.

CREATE LANGUAGE plpgsql;
CREATE FUNCTION log_insert_t() RETURNS trigger AS $triggerinsertt$
BEGIN
insert into csql_log_int (tablename, pkid, operation,cacheid) values (‘t’, NEW.f1, 1,1);
insert into csql_log_int (tablename, pkid, operation,cacheid) values (‘t’, NEW.f1, 1,2);
RETURN NEW;
END;
$triggerinsertt$ LANGUAGE plpgsql;
create trigger triggerinsertt
AFTER INSERT on t
FOR EACH ROW
EXECUTE PROCEDURE log_insert_t();

CREATE FUNCTION log_update_t() RETURNS trigger AS $triggerupdatet$
BEGIN
insert into csql_log_int (tablename, pkid, operation,cacheid) values (‘t’, OLD.f1, 2,1);
insert into csql_log_int (tablename, pkid, operation,cacheid) values (‘t’, NEW.f1, 1,1);
insert into csql_log_int (tablename, pkid, operation,cacheid) values (‘t’, OLD.f1, 2,2);
insert into csql_log_int (tablename, pkid, operation,cacheid) values (‘t’, NEW.f1, 1,2);
RETURN NEW;
END;
$triggerupdatet$ LANGUAGE plpgsql;

create trigger triggerupdatet
AFTER UPDATE on t
FOR EACH ROW
EXECUTE PROCEDURE log_update_t();

CREATE FUNCTION log_delete_t() RETURNS trigger AS $triggerdeletet$
BEGIN
insert into csql_log_int (tablename, pkid, operation,cacheid) values (‘t’, OLD.f1, 2,1);
insert into csql_log_int (tablename, pkid, operation,cacheid) values (‘t’, OLD.f1, 2,2);
RETURN NEW;
END;
$triggerdeletet$ LANGUAGE plpgsql;

create trigger triggerdeletet
AFTER DELETE on t
FOR EACH ROW
EXECUTE PROCEDURE log_delete_t();

Trigger name ends with the table name. Replace ‘t’ in the above script to the cached table name and ‘f1’ to the primary key fieldname of the cached table.

After writing the trigger file run this trigger in Postgres database .

$ psql test -f trigger.psql

In above command ,it is assumed that trigger file in current directory .

CSQL as Multiple bidirectional cache nodes for single data source

CSQL Cache is an open source high performance, bi-directional updateable data caching infrastructure for any disk residence database that sits between the application process and back-end to provide unprecedented high throughput to your application.

csql_fig1

CSQL cache accelerate application performance at the data tier by sitting in the clustered middle tier servers . In multiple bidirectional cache, most frequently used table are cached to CSQL which is connected to the clustered application, by the table loader module. Any change made in application layer directly reflects to all other CSQL cache nodes as well as target database. For any kind of DML operation on non cached table, CSQL provides gateway to directly access that table from target database. Application doesn’t have any information whether their operation is held at CSQL or target database.

Any changes in target database on cached table is propagated to all CSQL cache nodes so that application connected to any CSQL node gets consistent data . To achieve this, CSQL maintains a log in target database which keeps track of all operation on cached tables as well as number of cached nodes running currently. Triggers are installed in the target database for all the DML operations on cached table to generate log entires in the log table.

Bi-Directional Cache Settings for MySQL as target database

Before start for Bi-Directional setting make sure that target database as MySQL ,Unixodbc and mysql-connector are installed.If not,then install first.

Let us consider there are two CSQL cache node and a target database as MySQL .To set multiple Bi-Directional caching, First create the table in MySQL to hold the log records using the SQL statement below using mysql 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’;

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 . Again DSN should set to myodbc3 for MySQL.

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;
|

Here for different table replace table name with p1 and primary field name with f1.

After writting the trigger.sql as per requirement execute it as below

$ mysql -u root -p <trigger.sql

Now start CSQL server by csqlserver -c and execute csql -g in both cache nodes . Check by DML operaton in target and CSQL cache node multiple Bi-Directional caching.

Product Page

http://www.csqldb.com

http://www.csqlcache.com

free invisible web counter