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
Jitendra's Technical Blog 