Re: Database Caching - Mailing list pgsql-hackers

On Tue, 05 Mar 2002 12:46:27 EST, "Rod Taylor" wrote:
> 
> PHP has an amazingly easy to use Shared Memory resource.
> 
> ...
> 
> 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.
 Thanks for the information about PHP.  It looks very useful.
 Just an FYI, Zend cache (which we use) only caches the prepared PHP
"code" in a shared memory buffer.  The code is still executed (just
not parsed) for each request.  "Finished" HTML pages are not cached.
This is still a terrific gain as the majority of the overhead with PHP
is the parsing/preparation.  We have seen 8 page/second executions
jump to 40 pages/second.

> 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.
 Certainly I agree that query caching does not provide the most
efficient solution for webpage caching.  Neither does DB based
referential integrity provide the most efficient solution for
integrity either.  Application driven referential integrity can have
orders of magnitude better performance (application, i.e., programmer,
knows what needs to happen, etc.).
 What query caching does (potentially) provide is a direct, data
driven solution to caching that relieves the programmer from having to
deal with the issue.  The solution brings the caching down to the data
layer where invalidation/caching occurs automatically and "correctly".
 This works well for most applications, at least until the provided
"solution" becomes/drives the next bottleneck.  This can occur with
referential integrity, transactions, data typing, etc. even now.  When
it does, it becomes incumbent upon the programmer to find a higher
level solution to the issue.
 It also likely would provide a measured level of benefit to most
applications.  I know that many, many people on this list have said
that after thinking about it they didn't think so, but, it has been my
experience (as a database engine user not a database engine
programmer) that people using any application tend to ask the same
query over and over and over again.  This has true from both the human
interaction as well as the application code interaction.  Because I
have been buried in the web application domain for the last 5 years, I
will defer to others about applicability to other domains.

Thanks,
F Harvell