Thread

1. Re: Database Caching

   F Harvell <fharvell@fts.net> — 2002-03-05T16:35:32Z

   On 01 Mar 2002 10:24:39 +0500, Hannu Krosing wrote:
   > ...
   > 
   > > I don't see how result caching can be a win, since it can be done when
   > > needed anyway, without adding complexity to the database engine.  Just
   > > have the application cache the result set.  Certainly a web server could
   > > do this, if needed.
   > 
   > More advanced application server can do it all right. But you still need
   > sound cache invalidation mechanisms.
   > 
   > > If there were a way to mark a database as read only (and this could not
   > > be changed unless the entire database is shut down and restarted in
   > > read/write mode) then there might be some utility to result set cache.
   > > Otherwise, I think it will be wasted effort.  It might be worthwhile to
   > > do the same for individual tables (with the same sort of restrictions).
   > > But think of all the effort that would be needed to do this properly,
   > > and what sort of payback would be received from it?
   > 
   > The payback will be blazingly fast slashdot type applications with very
   > little effort from end application programmer.
   > 
   > > Again, the same goals can easily be accomplished without having to
   > > perform major surgery on the database system.  I suspect that there is
   > > some logical reason that Oracle/Sybase/IBM/Microsoft have not bothered
   > > with it.
   > 
   > I think they were designed/developed when WWW was nonexistent, and
   > client-server meant a system where client and server where separated by
   > a (slow) network connection that would negate most of the benefit from
   > server side cacheing. On todays application server scenario the client
   > (AS) and server (DB) are usually very close, if not on the same computer
   > and thus effectively managed cache can be kept on DB to avoid all the
   > cache invalidation logic going through DB-AS link.
   > 
   > ...
   
   You have identified one of the most valuable reasons for query/result
   caching.  As I read the threads going on about caching, it reminds me
   of the arguments within MySQL about the need for referential integrity
   and transactions.  Yes, the application can do it, however, by having
   the database do it, it frees the application programmer to perform
   more important tasks (e.g., more features).
   
   Your insights about the caching, etc. in the older, legacy databases
   is very likely the case.  With the development of high speed
   networking, etc., it is now very feasible to move the caching closer
   to the sources of change/invalidation.
   
   Quite frankly, while certainly there are literally millions of
   applications that would find minimal value in query/results caching, I
   know that, at least for web applications, that the query/results
   caching _would_ be very valuable.
   
   Certainly, as has been pointed out several times, the application can
   do the caching, however, utilizing todays "generic" tools such as
   Apache and PHP, it is relatively difficult.  By moving caching to the
   database server, there is little that needs to be done by the
   application to realize the benefits.
   
   For example, with my typical application, as a straight dynamic
   application, the process would be approximately:
   
     1) receive the request "http://www.xyz.com/index.php"
   
     2) query the current page contents from the database:
   
   	select * from table where dateline = '2002-03-05';
   
     3) begin the HTML output
   
     4) loop through the select results printing the contents
   
     5) complete the HTML output
   
   With caching within the database, this would likely achieve
   performance good enough to serve millions of requests per day.  (This
   is the case for some of our sites using Intersystems Cache which does
   do query caching and serves over 2 million page views per day.)
   
   Without the database caching, it is necessary to have the application
   perform this function.  Because of the short life of an Apache/PHP
   process, caching needs to be performed externally to the application:
   
     1) receive the request "http://www.xyz.com/index.php"
   
     2) check the age of the cache file (1min, 5min ???):
   
     3) if the cache is not fresh:
   
     3.1) lock the cache file
   
     3.2) query the database
   
     3.3) begin HTML output to the cache file
   
     3.4) loop through select results
   
     3.5) complete the HTML output to the cache file
   
     3.6) unlock the cache file
   
     4) open the cache file (i.e., wait for locked file)
   
     5) read/passthrough cache contents
   
   (Please note that this is one, simplified way to do the caching.  It
   assumes that the data becomes stale over time and needs to be
   refreshed.  It also uses a file cache instead of a memory cache.
   Certainly things could be made more efficient through the use of
   notifications from the database and the use of shared memory.  Another
   path would be to have an external program generating the pages and
   then placing the "static" pages into service (which requires changes
   to apache and/or the OS due to cache file invalidation during the
   generation process). Both paths make the application more complex
   requiring more programming time.)
   
   It is possible to have an application server do this caching for you.
   Of course, that in turn has its own complications.  For applications
   that do not need the added "features" of an application server,
   database caching can be a big win in both processing/response time as
   well as programmer time.
   
   Thanks,
   F Harvell
   
   
   
   

2. Re: Database Caching

   Rod Taylor <rbt@zort.ca> — 2002-03-05T17:46:27Z

   > Certainly, as has been pointed out several times, the application
   can
   > do the caching, however, utilizing todays "generic" tools such as
   > Apache and PHP, it is relatively difficult.  By moving caching to
   the
   > database server, there is little that needs to be done by the
   > application to realize the benefits.
   
   PHP has an amazingly easy to use Shared Memory resource.
   
   Create a semaphore, and an index hash in position 0 which points to
   the rest of the resources.
   
   On query lock memory, lookup in index, pull values if it exists
   otherwise run db query and stuff values in.
   
   The tricky part is expiring the cache.  I store a time in position 1
   and a table in the database.  To see if cache should be expired I do a
   select against the 'timer' table in the db.  If it's expired, clear
   cache and let it be rebuilt.  A trigger updates the time on change.
   
   Doesn't work well at all for frequent updates -- but thats not what
   it's for.
   
   
   Took about 15 minutes to write the caching mechanism in a generic
   fashion for all my applications.  Step 2 of my process was to cache
   the generated HTML page itself so I generate it once.  I store it gzip
   compressed, and serve directly to browsers which support gzip
   compression (most).  Pages are stored in shared memory using the above
   mechanism.   This took about 40 minutes (php output buffer) and is
   based on the uniqueness of the pages requested.  Transparent to the
   application too (prepend and append code elements). (Zend cache does
   this too I think).
   
   Anyway, I don't feel like rewriting it as non-private code, but the
   concept is simple enough.  Perhaps Zend or PEAR could write the above
   data lookup wrappers to shared memory.  Although you don't even have
   to worry about structures or layout, just the ID that represents the
   location of your object in memory.
   
   It can serve a couple million per hour using this on a E220 with lots
   of ram -- bandwidth always runs out first.
   
   
   Anyway, first suggestion is to buy Zend Cache (if available) to cache
   entire HTML pages, not to cache db queries.  Even the great Slashdot
   (which everone appears to be comparing this to) uses a page cache to
   reduce load and serves primarily static HTML pages which were
   pregenerated.
   
   I agree with the solution, I don't agree with the proposed location as
   caching DB queries only solves about 1/4 of the whole problem.  The
   other being network (millions of queries across 100mbit isn't fast
   either), and genereation of the non-static page from the static
   (cached) query.  Build a few large (10k row) tables to see what I mean
   about where the slowdown really is.
   
   
   

3. Re: Database Caching

   Steve Wolfe <steve@iboats.com> — 2002-03-05T19:13:15Z

   > > > I don't see how result caching can be a win, since it can be done when
   > > > needed anyway, without adding complexity to the database engine.  Just
   > > > have the application cache the result set.  Certainly a web server
   could
   > > > do this, if needed.
   
     There are a couple of catches with that idea.  First, we have thirty+
   applications that we've written, and trying to go back and patch the caching
   into all of them would be much more work than just doing it in the DB.
   Secondly, changes to the data can be made from psql, other apps, or from
   triggers and hence, there is no reliable way to deal with cache expiration.
   Not only that, but if you have a pool of web servers, applications on each
   one can be inserting/updating data, and none of the other machines have any
   clue about that.
   
     Really, doing it in PG makes a lot of sense.  Doing it outside of PG has a
   lot of problems.  At one point, I was resolved that I was going to do it in
   middleware.  I sat down and planned out the entire thing, including a really
   nifty hash structure that would make cache expiration and invalidtion
   lightning-fast... but I had focused entirely on the implementation, and when
   I realized all of the drawbacks to doing it outside of the backend, I
   scrapped it.  That's the first time I've ever really wished that I was a C
   programmer....
   
      In the worst-case scenario (never repeating a query), result caching
   would have a very small overhead.  In any semi-realistic scenario, the
   benefits are likely to be significant to extraordinary.
   
   steve