Database caching with other processes modifying data

Discussions

Performance and scalability: Database caching with other processes modifying data

  1. Hello,

    We have a database server, and an several application servers. When accessing data, we cache certain object that are used very often, and almost never change.

    We'd like to cache more objects, that change more often. For our application, it's not a big deal. But we also have several applications that write directly to the database, and therefore will not invalidate the cache.

    How would / do you solve this problem?

    I was thinking about:

    1) a short cache timeout of maybe 5 minutes or something, but I think that's a bit ugly, because the data will not always be consistent, and if for example a productgroup is deleted externally, I would run into FK constraint problems, when someone would add a product into tat product group. Invalidating the cache before those kinds of actions would require too much cache code spreaded around.


    2) I could write a trigger on the database and on update / insert / delete update a caching table. Or a cache-timestamp on each cached-table, so I can check with that.
    This is a solid solution, but requires triggers to be set up. And I'm not sure wether it's smart to require such functionality. This solution would cost a roundtrip when accessing an object, so that would really help performance too much.

    If you could help, please :)
  2. Hi Joris,

    Have you looked into using Tangosol's Coherence clustered caching/data-management product?

    Many of our customers cache data that is read and write intensive, this is accomplished with our Read-Through and Write-Behind technology. Within this strategy a developer could implement a cache update from database (in the event that the row has been updated behind the scenes) by using a version (timestamp) column within the database table.

    If you would like to discuss the details of such an implementation contact sales at tangosol dot com.

    Later,
    Rob Misek
    Tangosol Coherence: Cluster your Work. Work your Cluster.
    Coherence Forums.
  3. Hi Rob,

    I've heard great things about Coherence the past year. But unfortunately I cannot justify the cost at this moment, because performance is not yet really an issue.

    I will give it a try this month, just to checkout the possibilities/performance.

    You're saying the timestamp method is the way to do caching with other applications updating the db. But doesn't that require each getXXX, to check if the db is updated?
  4. Hi Joris,
     
    > I've heard great things about Coherence the past year. But unfortunately I cannot justify the cost at this moment, because performance is not yet really an issue.
    >
    > I will give it a try this month, just to checkout the possibilities/performance.

    It is just as much about High Availability and Fault Tolerance as it is about performance ;). Let me know if you need anything answered during your evaluation.

    > You're saying the timestamp method is the way to do caching with other applications updating the db. But doesn't that require each getXXX, to check if the db is updated?

    Ideally, of course, all applications would be running off the cached data. If this is not the case there will have to be some sort of communication to the cache on a 'behind the scenes' update. Most likely using a database trigger of some sort, as you suggested.

    Later,
    Rob Misek
    Tangosol Coherence: Cluster your Work. Work your Cluster.
    Coherence Forums.
  5. If you want to cache tables which shall be modified also, then csql cache shall 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