Csql Cache For Postgres

CSQL cache for Postgres

Why CSQL cache? • A generic database platform to cache frequently accessed tables from a back end database. • 100X faster access for cached tables • Application transparent caching • Distributed caching for cluster • Flexible deployment options • Standard Support: JDBC, ODBC, SQL • Automatic Fail over to target database

Middle tier cache • CSQL cache acts as a middle tier cache for any target database • 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.

Caching options • Uni-Directional and Bi-Directional updates - Unidirectional update means all updates on cached tables will be automatically propagated to target database. - Bi-directional caching in which even direct updates on target database are propagated to CSQL cache automatically . • Synchronous and Asynchronous update propagation -in synchronous mode the update operation is effected on both the cache and the target database at the same time. - in asynchronous mode updates are delayed to the target database.

How to configure CSQL to cache Postgres? Following five parameters in csql.conf file needs to be set • CACHE_TABLE = true • DSN = psql - set to the data source name of the target ODBC driver specified in the odbc.ini file. Here it is ‘psql ‘ • TABLE_CONFIG_FILE = tmp/csql/csqltable.conf - contains the complete path to the file which holds the cache table information. • ENABLE_BIDIRECTIONAL_CACHE = true • CACHE_RECEIVER_WAIT_SECS = 10 - set to interval it waits if there are no update logs from the target database.

Step-by-Step configuration • Configure Postgres • Configure postgres ODBC driver • Create required tables in Postgres using ODBC driver • Start CSQL server • Cache tables from postgres • Use gateway connection to retrieve noncached tables

Configuring Postgres • Install ‘UnixODBC’ package • Install Postgres under ‘ /usr/local/pgsql ‘ • login with user name ‘ postgres ‘ and start the server using the following commands. $export PGDATA=/usr/local/pgsql/data $ /user/local/pgsql/bin/postmaster /tmp/postgreslog 2>postgreslog &

Configuring Postgres ODBC driver • Set up the files “etc/odbcinst.ini“‘ and “ etc/odbc.ini“

Creating tables in Postgres •

Connect to Postgres server using $ isql psql

•

Create tables in Postgres using 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));

•

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

Cache the tables from Postgres •

Start csqlserver $ csqlserver • Cache some entire tables from Postgres using $ cachetable -t t1 $ cachetable -t t2 • Cache specific records or fields using $cachetable -t t1 -c “f1=100? $ cachetable -t t2 -f “f2? •

To drop cached tables use $cachetable -t -u • To retrive information on cached table use $ cachetable -S • To retrive information on non-cached table run csql tool with –g option.

Where to use? • For performance critical applications in Industries such as Telecom, process control, airline reservation, stock market, health care etc., • wide variety of applications ranging from single-user systems to enterprise-wide multiserver installations with tens of thousands of concurrent users.

For more information, visit Product web site at http://www.csqldb.com Company web site at http://www.lakshyasolutions.com

THANK YOU