Performance and scalability: Database Caching
I'm after a little help regarding database caching. I'm looking to improve the efficiency of a website that consists of many JavaServer pages, each one making several SQL queries.
I've been told that database caching would improve the performance of the site, but am a little at a loss as to what the process entails, what tools are available for this, what the pros, cons and pitfalls are etc.
If you could be of any assistance I would really, really appreciate it. Also if you would know of any formal papers, articles etc it would help me in justifying any eventual decision.
- Database Caching by Tugdual Grall on April 01 2005 12:11 EST
- Use Bind Variable, Cursor Sharing, Stored Procedures by john walton on April 05 2005 12:11 EDT
- Database Caching by Jose Ramon Huerga Ayuso on April 06 2005 15:10 EDT
- Have you considered resultset caching by Suds Menon on August 22 2005 18:02 EDT
- Database Caching by Cameron Purdy on August 29 2005 21:44 EDT
- Hibernate Caching by Cameron Purdy on September 22 2005 21:22 EDT
- Re: Database Caching- CSQL Cache gives 20- 100 times performance by Prabakaran Thirumalai on June 01 2008 12:54 EDT
How do you access the database currently? are you using a EJB, or an O/R mapping like Oracle Toplink, or another persistence framework?
In most of the case if you are using a good persistence framework it will give you a way of caching your data on the middle tier, and everything will be done for your more or less transparently. As an example you can read this article about Oracle Toplink Caching and Locking
To have a fast and scaleable database application, one will need to do at least two things: 1) use Bind variable, and 2) use cursor sharing.
A book may need to be written to explain the full details of these two concepts.
A quick way to use bind variable and cursor in Oracle database can be done by using stored procedures with static query.
In other words, you should try to call PLSQL stored procedures instead of executing SQL in Oracle.
Just before you start adding database caching to your architecture, you must be able that the performance problems are in the database side.
You should consider performing serious QA testing: perform a load test (using any product such as LoadRunner) and - while running the test - monitor the application server activity (with products such as Quest PerformaSure). Once you learn if the performance problem is in the database, you may consider caching some data in the application server (this works better than caching in the database side).
Hope this helps,
Jose Ramon Huerga
Depending on how much of the querying is repetitive and with tools that allow you to characterize the rate of change of data in the underlying database, you can use resultset caching to give your apps some serious performance boost. Especially if you have a number of app servers processing almost the same set of queries, having a resultset cache that is built on top of a distributed caching product (with support for distributed expirations and distributed invalidation of data), resultset caching is the way to go. Checkout this website for more info on JDBC caching
I'm after a little help regarding database caching. I'm looking to improve the efficiency of a website that consists of many JavaServer pages, each one making several SQL queries.I've been told that database caching would improve the performance of the site, but am a little at a loss as to what the process entails, what tools are available for this, what the pros, cons and pitfalls are etc.
There are a couple different approaches that could work:
1) The simplest is to add JSP caching, which could cache the blocks of the JSP that do the SQL queries. For example, you could use OSCache or (if you are using Weblogic) the built-in cache tags: http://e-docs.bea.com/wls/docs81/jsp/customtags.html#56944
2) You can cache queries. There are a number of products (e.g. Isocra LiveStore) that do that. Basically, most of these products just give you a drop-in JDBC driver replacement to do their work.
3) If you have any objects that you want to cache, including result sets, you can just throw them into a cache. In this case, you're going to be doing more work yourself than the #2 example above, but you'll have more control over the caching. For this type of solution, check out Tangosol Coherence.
Tangosol Coherence: Clustered Shared Memory for Java
Drop-in JDBC drivers allow you to cache resultsets. Most drop-in JDBC drivers however will not give you distributed caching under the covers. The best way to go about doing this is to first uncover the query patterns in your application. How often is the query executed? How many different web/app server instances are executing the query? What is the rate of change of resultset invalidations (due to updates to the db) and so on. Gemfire JDBC interceptor provides you with an integrated tool to gather all of this information. Using the same tool, you can select the query templates whose resultsets you want to cache. When your application starts up, your drop-in JDBC driver automatically becomes a distributed cache. Resultsets are fetched from app nodes (VM on the same or other machines), further minimizing calls to the underlying database. Distributed expiration ensures that when a resultset becomes stale, it is removed from the cache from all application instances. And above all, performance is what counts when the rubber meets the road and Gemfire JDBC Interceptor builds on top of the fastest most performant enterprise data fabric that powers mission critical apps in some of the largest investment banks on wall street today. And as Cameron rightly points out, once you have this in place, you can use it for more than just resultset caching.
Check it out at http://www.gemstone.com/products/gemfire/jdbc.php
If you are using Hibernate to do your database connectivity, we have an updated caching plug-in available for it. Coherence supports all four built-in Hibernate cache strategies.
Tangosol Coherence: Clustered Transactional Caching
If you want to cache tables which shall be modified also, then result set based read only cache options may not solve your purpose. CSQL Cache, middle tier fully transactional bidirectional updateable caching mechanism might help in that case. It gives 20- 100 times performance improvement. Check out the below article for more infomation http://www.general.databasecorner.com/resource,4200,csql-main-memory-database-management-system-and-middle-tier-cache.aspx It is open source product and shall be downloaded from the below link http://www.sourceforge.net/projects/csql http://www.databasecache.com