Forum OpenACS Development: Response to Cacing of database queries ...

In Greenpeace Planet I'm caching the public permission check for site nodes for the lifetime of the server instance (in other words I load it up when AOLserver starts).  The implication is that changing permissions on site nodes doesn't take effect until you restart AOLserver.  In the GP environment this is perfectly OK as the only restricted pages are admin pages which are identifiable through the URL.  It wouldn't be hard to zap the cached value when permissions change, of course, but for this project there was no need (and no time left before launch) to do so.

The net effect is similar as described above for Story Server - public pages fly through the request processor with a simple check against a globably stored value.  No db hit is involved.

The more general question raised by Stephen, there are complex approaches and simple approaches.

One relatively complex idea - use dependencies declared by packages and already used by the APM to zap cached content based on these dependencies.

One very simple idea - zap all cached query results whenever you do an insert or update or call PL/SQL (new etc) unless specifically told not to.  Before rejecting this simplistic notion out of hand consider that even the short-term caching that would result from this brain-dead approach is better than no caching at all.

I'm sure everyone here can come up with multiple ideas which vary greatly in terms of the effort needed to implement them.  I favor a relatively simplistic approach.

In GP Planet I'm just zapping the cache every 5 minutes.  GP wanted minimal latency between the posting and appearance of content, and there was no time in our last-minute rush to production to implement anything sophisticated.  The 5 minute zap means average latency between posting and appearance is 2.5 minutes.

The boost in performance has been considerable.  We couldn't measure it precisely because after caching we were able to saturate GPI's incoming 2 mbs internet connection while keeping server load at a relatively low level.  In other words our testing was limited by GPI's bandwidth at the office (the servers themselves are in Germany and we had no spare local to the datacenter to run tests from).  Before caching we were serving up about 1 mbs with heavy server load.

As far as RDBMS caching ... PG doesn't do it.  There's been discussion in the group and the consensus, which I agree with, is that this is best done at the application level.  One reason is that my caching versions of the db API procs cache more than the query result.  For instance in the case of db_multirow I'm caching the result array, which means that not only the cost of the query is avoided but the cost of the (optional) Tcl block which is executed for each row returned by the query.  For those of you thinking that any general implementation needsd to allow the caller to override the caching of the results of the code block, you're right.  But in the GP Planet context caching the full result is just what we want and saves processing cycles above and beyond those involved in executing the RDBMS query.

Likewise db_list caches the list that's built, avoiding the cost of lappend'ing the query result row by row.

So caching at this level gains more than would be gained by the RDBMS doing query result caching.  This is the kind of reasoning that's led the PG group to reject RDBMS-level query caching (for now at least).