Monday, June 2, 2008

CSQL Cache - Improve database performance 100 times

Accelerate database performance using In-Memory CSQL Cache

Abstract

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

Improving Database Performance Using Database Cache

Many applications today are being developed and deployed on multi-tier environments that involve browser-based clients, web application servers and backend databases. These applications need to generate web pages on-demand by talking to backend databases because of their dynamic nature, making middle-tier database caching an effective approach to achieve high scalability and performance. Following are the advantages of database caching

Scalability: distribute query workload from backend to multiple cheap front-end systems.
Flexibility: achieve QoS, where each cache hosts different parts of the backend data, e.g., the data of Platinum customers are cached while that of ordinary customers is not.
Availability: by continued service for applications that depend only on cached tables even if the backend server is unavailable.
Performance: by potentially responding fast because of locality of data and smoothing out load peaks by avoiding round-trips between middle-tier and data-tier

In order to overcome the throughput barrier, application scales through deployment of multiple small systems. Companies have developed various homegrown solutions that involve database caching. These caching solutions can help accelerate database performance to some extent, but they are fairly ineffective as most of them support only result set caching and some are poor at dealing with the scalability. Some of these caching solutions use another heavy weight full-fledged database management system to cache the data, which yields less performance gain. These caching solutions are mostly read only or else provide tools for doing manual lazy updates. For frequently changing data, it will be holding "dirty" cached data, resulting in long latency periods that may be entirely unacceptable for applications requiring immediate access to current data.

Requirements of Caching Solution

Updateable Cache Tables
Most of the existing cache solutions are read only which limits their usage to small segment of the applications, non-real time applications.

Bi-Directional Updates
For updateable caches, updates, which happen in cache, should be propagated to the target database and any updates that happen directly on the target database should come to cache automatically.

Synchronous and Asynchronous update propagation
The updates on cache table shall be propagated to target database in two modes. Synchronous mode makes sure that after the database operation completes the updates are applied at the target database as well. In case of Asynchronous mode the updates are delayed to the target database.
Synchronous mode gives high cache consistency and is suited for real time applications. Asynchronous mode gives high throughput and is suited for near real time applications.

Multiple cache granularity: Database level, Table level and Result-set caching
Major portions of corporate databases are historical and infrequently accessed. But, there is some information that should be instantly accessible like premium customer’s data, etc

Recovery for cached tables
Incase of system or power failure, during the restart of caching platform all the committed transactions on the cached tables should be recovered.

Tools to validate the coherence of cache
In case of asynchronous mode of update propagation, cache at different cache nodes and target database may diverge. This needs to be resolved manually and the caching solution should provide tools to identify the mismatches and take corrective measures if required.

Horizontally Scalable
Clustering is employed in many solutions to increase the availability and to achieve load balancing. Caching platform should work in a clustered environment spanning to multiple nodes thereby keeping the cached data coherent across nodes.

Transparent access to non-cached tables reside in target database
Database Cache should keep track of queries and should be able to intelligently route to the database cache or to the origin database based on the data locality without any application code modification.

Transparent Fail over
There should not be any service outages, incase of caching platform failure. Client connections should be routed to the target database.

No or very minimal changes to application for the caching solution
Support for standard interfaces JDBC, ODBC etc that will make the application to work seamlessly without any application code changes. It should route all stored procedure calls to target database so that they don’t need to be migrated.


CSQL Cache

CSQL Cache is a high performance, bi-directional updateable data-caching infrastructure that sits between the clustered application process and back-end data sources to provide unprecedented high throughput to your application by offloading the computing cycles from expensive backend systems along with reduction in costly network calls, thereby enabling real time application to provide faster and predictive response time.

CSQL Cache uses the fastest Main Memory Database (CSQL MMDB) designed for high performance and high volume data computing for caching the table and provides most flexible and cost-effective way to cache and manage enterprise information without compromising on transactional and indexed access to the data. This main memory database is 10-20 times faster than traditional disk based database system as the database completely resides in main memory and developed to be used on real time high computing data platforms.

CSQL implements most of the requirements of good caching solution mentioned in the previous section except few in the latest version. For complete set of features supported by CSQL cache in the latest version, refer the data sheet on the product web site. http://www.csqlcache.com

Open source web site:

http://sourceforge.net/projects/csql