CSQL Introduces In-Memory database Cache option for POSTGRES database

Welcome to CSQL, the main memory relational database Cache.

This introduction provides an overview of the major components and technologies that make up CSQL. These components include :

  • A powerful, storage engine that includes the ability to create databases.
  • A high performance SQL engine.
  • A suite of technologies and tools that provide rapid development for database and applications.
  • Automatic interoperability via Java, JDBC, C++, ODBC and more.

An Unequaled Architecture :

CSQL main memory database Cache derives much of its power from its unique architecture. At the core, the CSQL database storage engine provides the complete set of services – including data storage, concurrency management, transactions, and process management – needed to build complex database management systems. You can think of the CSQL storage engine as a powerful database toolkit. Using this toolkit, CSQL implements a completet relational management system.

CSQL Cache in Brief :

CSQL Cache could be used around the world for a wide variety of applications ranging from single-user systems to enterprise-wide multiserver installations with tens of thousands of concurrent users.

CSQL In-memory database cache option, which includes the CSQL In-memory database, and cache connect to Postgres. This CSQL Cache option enables customers to significantly Improve Application Response times and throughput. Based on the CSQL In-Memory database, the CSQL Cache option delivers a real-time,dynamic,updatable cache for frequently accessed data in the Postgres database.

This Software dramatically reduces application response times and increases throughput by relying on memory-optimized algorithms and avoiding communication delays between computers and processes. For performance critical applications in Industries such as Telecom, process control, airline reservation, stock market, health care etc., the CSQL Cache option delivers application response times in the microseconds by bringing the frequently accessed data closer to the application, and by executing SQL requests in the CSQL In-Memory Database.We found the CSQL main memory engine to be between 10 and 20 times faster than traditional database system we tested.

Cache in Action

CSQL MMDB also acts as a middle-tier cache for Postgres database. More formally CSQL Cache is a high performance, bi-directional updateable data-caching component that sits between the cluster application process and back-end data sources to provide high throughput to the application.

Uni-Directional and Bi-Directional updates

The default caching in CSQL is unidirectional caching, which means all updates ( INSERT, UPDATE, DELETE) on cached tables will be automatically propagated to target database. CSQL also supports bi-directional caching in which direct updates on target database are propagated to CSQL cache automatically .

Synchronous and Asynchronous update propagation

The updates on cache table shall be propagated to target database in two modes. In synchronous mode, the database operation completes the updatets are applied at the target database as well. In Asynchronous mode, the updates are delayed to the target database.

CSQL Configuration :

The configuration file, csql.conf has five parameters for caching. They can be found in cache section of the csql.conf file. They are :

· CACHE_TABLE is a boolean parameter which needs to be set to true.

i.e. CACHE_TABLE = true

· DSN is a string parameter, which needs to be set to the data source name of the target ODBC driver specified in the odbc.ini file. Here it is ‘psql ‘

i.e. DSN = psql

· TABLE_CONFIG_FILE is a string parameter which contains the complete path to the file which holds the cache table information. You can specify your own path for this file.

i.e. TABLE_CONFIG_FILE = /tmp/csql/csqltable.conf

· ENABLE_BIDIRECTIONAL_CACHE is a boolean parameter which needs to be set to true to enable bi-directional update propagation.

i.e. ENABLE_BIDIRECTIONAL_CACHE = false

· CACHE_RECEIVER_WAIT_SECS is an integer parameter, which needs to be set to interval it waits if there are no update logs from the target database.

i.e. CACHE_RECEIVER_WAIT_SECS = 10

Uni-Directional Cache Configuration settings :

Postgres Configuration :

Before proceed,  the “ unixODBC ” package and “ Postgres “ have to installed in your system .

Once Postgres is installed, now login with user name ‘ postgres ‘ and start the server using the following commands.

The following commands assume that postgres is installed under ‘ /usr/local/pgsql ‘ directory.

$export PGDATA=/usr/local/pgsql/data

$ /user/local/pgsql/bin/postmaster /tmp/postgreslog 2>postgreslog &

Configuring Postgres ODBC Driver :

The two files  can be found  in  “etc/odbcinst.ini‘ and  “ etc/odbc.ini,  which needs to be setup . After this a database named ‘ test’ will be created. But its not mandatory. You can specify for own database.

[ Copy the following lines into /etc/odbcinst.ini ]

[ODBC]

Trace = yes

TraceFile = /tmp/sql.log

[postgres]

Driver = /usr/lib/libodbcpsql.so

Setup = /usr/lib/libodbcpsqlS.so

Description = Connector Driver DSN

SERVER = localhost

Database = test

ReadOnly = No

[ Copy the following lines into /etc/odbc.ini ]

[psql]

Description = CP resources db

Driver = PostgreSQL

Trace = No

TraceFile = sql.log

Database = test

Servername = localhost

UserName = postgres

Password =

Port = 5432

Protocol = 6.4

ReadOnly = No

RowVersioning = No

ShowSystemTables = No

ShowOidColumn = No

FakeOidIndex = No

ConnSettings =

ExtraSysTablePrefixes =

Note : you need to have the root privileges to modify /etc/odbc.ini and /etc/odbcinst.ini file.

Creating required table in Postgres using ODBC Driver :

$ isql psql

+—————————————+

| Connected! |

| sql-statement |

help [tablename]

| quit |

+—————————————+

SQL>

If the above output is generated, This means ODBC driver manager is properly configured and it connects to Postgres server.

Now the table will be created in Postgres which will be required for caching in CSQL.

SQL> create table t1 ( f1 int, f2 char(10),primary key(f1));

SQL> create table t2 ( f1 int, f2 char(10),primary key(f1));

SQL> create table t3 ( f1 int, f2 char(10),primary key(f1));

SQL>INSERT INTO t1 (f1, f2) values (100, ‘rama’);

SQL>INSERT INTO t2 (f1, f2) values (200, 2000);

SQL>INSERT INTO t3 (f1, f2) values (300, 3000);

SQL>quit;

Starting the CSQL Server :

$ csqlserver

Start the csql server in one terminal using the command ‘csqlserver’ and open another terminal which will be used for Cache.

Cache the Tables from Postgres :

Using the ‘cachetable’ tool the Target DB tables could be cached to CSQL. In our case the tables ‘t1’ and ‘t2’ is cached. Follow the below commands to cache the tables. Refer this link http://www.csqldb.com/pro_documentation.html for CSQL Cache Guide and follow the Section 8. to know more about “cachetable” tool.

$ cachetable -t  t1

$ cachetable -t  t2

After executing these two commands, table t1 and t2 are present in CSQL. Open another terminal and follow the below commands.

CSQL>show tables;

=============TableNames===================

t1

t2

=========================================

Select statements on these two tables :

CSQL>select * from t1;

—————————————

f1 f2

—————————————

100 rama

CSQL>select * from t2;

———————————————————

f1 f2

———————————————————

200 2000

The cache tables can be downloaded by using the “cachetable -t <tablename> -u” command.

Specific Record and Fields could be cached :

Furthermore, if it is required to cache only specific records then use -c options or use -f option for specific field. Follow the below examples.

$cachetable -t t1 -c “f1=100”

$ cachetable -t t2 -f “f2”

Before executing this two commans use the “$cachetable -t <TabalenName> -u” to drop the cache table if present in cache. Both the options can be used for a table also at a time ($cachetable -t t1 -c “f1=100” -f “f1 “) .

Get the Information about cache tables :

The cache table information could be retrieved by using the -S options. It can be used in two ways. In one way, this options alone used then all the table information will be displayed or could be used with a specific tablename. Follow the below commands,

$ cachetable -S

After executing the above command the below output is generated.

==========================================
| Mode | Table Name | Primary Key | Condition | Field List |
==========================================
| 2 | t1 | NULL | f1=100 | NULL |
—————————————————————————-
| 2 | t2 | NULL | NULL | f2 |

—————————————————————————-

$cachetable -t t1 -S

After executing the above command , the below output is generated.

===========================================

| Mode | Table Name | Primary Key | Condition | Field List |
===========================================
| 2 | t1 | NULL | f1=100 | NULL |
———————————————————————————————————————-

Using Gateway Connection :

so far it has been  studied that the tables (t1, t2) those are mentioned in the ‘ csqltable.conf ‘ file have been cached from Postgres to CSQL. One anothe table ‘ t3 ‘ have created in Postgres and  have not mentioned this table name in the csqltable .conf file. Using the gateway the records of t3 table can be retrieved If it is required.

Run the CSQL tool with -g option.

$ csql -g

It will show the prompt what was before .

CSQL > select * from t3;

15124:3086870224:DatabaseManagerImpl.cxx:603:Table not exists t3

15124:3086870224:SelStatement.cxx:258:Unable to open the table:Table not exists

———————————————————

f1 f2

———————————————————

300 3000

DML operation on cached tables in CSQL:

Now teh DML statements could be executed on these two tables, and that reflects in Postgres as well.

CSQL> insert into t1 values(105,’csql’);

Statement Executed: Rows Affected = 1

Now it needs to  verify this by accessing Postgres database through ‘isql‘ tool by speciifying its DSN (psql) .

$isql psql ;

SQL> select * from t1;

+————+———–+

f1 f2

+————+———–+

100 rama

105 csql

+————+———–+

SQLRowCount returns 2

2 rows fetched

From the above output it has been studied that the  Uni-directional caching is happened with Postgres database.

Bi-Directional Cache :

The default caching in CSQL is unidirectional caching, which means all updates ( INSERT, UPDATE, DELETE) on cached tables will be automatically propagated to target database. CSQL also supports bi-directional caching in which direct updates on target database are propagated to CSQL cache automatically.

You are welcome for your suggestion and commentts about this blog which will help for writing my next blog .

For Bi-directional setting please visit this blog

CSQL Documents Link :

http://www.csqldb.com/pro_documentation.html

Advertisements

Tags:

2 Responses to “CSQL Introduces In-Memory database Cache option for POSTGRES database”

  1. CSQL : Postgres Configuration for Multiple bidirectional cache « Bijayakumar’s Weblog Says:

    […] 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 MySQL . Set “/etc/odbcinst.ini” and “/etc/odbc.ini” file properly.For help refer Uni-directional cache configuration. […]

  2. Cache MySQL table to improve performance « Niharpaitalcsql’s Weblog Says:

    […] target database. CSQL introduced caching mechanism for Postgres. For more information refer this blog CSQL also supports the updatable Bidirectional caching both for MySQL and Postgres. For more […]

Leave a Reply

Please log in using one of these methods to post your comment:

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: