-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, February 28, 2002 3:27 PM
To: Greg Sabino Mullane
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Database Caching 


"Greg Sabino Mullane" <greg@turnstep.com> writes:
> III. Relation caching

> The final cache is the relation itself, and simply involves putting
the entire 
> relation into memory. This cache has a field for the name of the
relation, 
> the table info itself, the type (indexes should ideally be cached more
than 
> tables, for example), the access time, and the acccess number. Loading
could 
> be done automatically, but most likely should be done according to a
flag 
> on the table itself or as an explicit command by the user.

This would be a complete waste of time; the buffer cache (both Postgres'
own, and the kernel's disk cache) serves the purpose already.

As I've commented before, I have deep misgivings about the idea of a
query-result cache, too.
>>
I certainly agree with Tom on both counts.

Think of the extra machinery that would be needed to retain full
relational integrity with a result cache...

Then think of how easy it is to write your own application that caches
results if that is what you are after and you know (for some reason)
that it won't matter if the database gets updated.

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.

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?

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 am ready to be convinced otherwise if I see a logical reason for it.
But with the current evidence, I don't see any compelling reason to put
effort in that direction.
<<

Thu, 28 Feb 2002 15:44:54 -0800
"Dann Corbit" <DCorbit@connx.com>
Re: Database Caching 
On Fri, 2002-03-01 at 04:44, Dann Corbit wrote:
> As I've commented before, I have deep misgivings about the idea of a
> query-result cache, too.
> >>
> I certainly agree with Tom on both counts.
> 
> Think of the extra machinery that would be needed to retain full
> relational integrity with a result cache...
> 
> Then think of how easy it is to write your own application that caches
> results if that is what you are after and you know (for some reason)
> that it won't matter if the database gets updated.

That would be trivial indeed.

The tricky case is when you dont know when and how the database will be
updated. That would need an insert/update/delete trigger on each and
every table that contributes to the query, either explicitly ot through
rule expansion. Doing that from client side would a) be difficult and b)
probably too slow to be of any use. To do it in a general fashion wopuld
also need a way to get the expanded query tree for a query to see which
tables the query depends on.

> 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.

> I am ready to be convinced otherwise if I see a logical reason for it.
> But with the current evidence, I don't see any compelling reason to put
> effort in that direction.

In what direction are _you_ planning to put your effort ?

------------
Hannu


01 Mar 2002 10:24:39 +0500
Hannu Krosing <hannu@krosing.net>
Re: Database Caching
> The tricky case is when you dont know when and how the database will
be
> updated. That would need an insert/update/delete trigger on each and
> every table that contributes to the query, either explicitly ot
through
> rule expansion. Doing that from client side would a) be difficult
and b)
> probably too slow to be of any use. To do it in a general fashion
wopuld
> also need a way to get the expanded query tree for a query to see
which
> tables the query depends on.

Rather than result caching, I'd much rather see an asynchronous NOTICE
telling my webservers which have RULES firing them off when a table is
modified.

Let the webserver hold the cache (as they do now in my case, and in
slashdots) but it gives a way that the database can tell all those
involved to drop the cache and rebuild.  Currently I accomplish this
with a timestamp on a single row table.  Could probably accomplish it
with a periodic SELECT TRUE and watch for the notice -- but in my case
I need to support other dbs as well.


Mon, 4 Mar 2002 16:05:16 -0500
"Rod Taylor" <rbt@zort.ca>
Re: Database Caching
"Rod Taylor" <rbt@zort.ca> writes:
> Rather than result caching, I'd much rather see an asynchronous NOTICE
> telling my webservers which have RULES firing them off when a table is
> modified.

LISTEN/NOTIFY?

			regards, tom lane

Mon, 04 Mar 2002 16:50:07 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Database Caching 
Sorry, NOTIFY -- not NOTICE  (damn keyboard...)

Right now we're required to do a select against the database
periodically which means a test is required before hitting any cached
elements.  By the time you select true, might as well do the real
select anyway (normally simple index lookup).

The ability to receive one without making a query first would be very
advantageous.
--
Rod Taylor

This message represents the official view of the voices in my head

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Rod Taylor" <rbt@zort.ca>
Cc: "Hannu Krosing" <hannu@krosing.net>; "Dann Corbit"
<DCorbit@connx.com>; "Greg Sabino Mullane" <greg@turnstep.com>;
<pgsql-hackers@postgresql.org>
Sent: Monday, March 04, 2002 4:50 PM
Subject: Re: [HACKERS] Database Caching


> "Rod Taylor" <rbt@zort.ca> writes:
> > Rather than result caching, I'd much rather see an asynchronous
NOTICE
> > telling my webservers which have RULES firing them off when a
table is
> > modified.
>
> LISTEN/NOTIFY?
>
> regards, tom lane
>


Mon, 4 Mar 2002 16:56:35 -0500
"Rod Taylor" <rbt@zort.ca>
Re: Database Caching 
"Rod Taylor" <rbt@zort.ca> writes:
> Sorry, NOTIFY -- not NOTICE  (damn keyboard...)
> Right now we're required to do a select against the database
> periodically

Certainly not; I fixed that years ago (I think it was the first
nontrivial thing I ever did with the PostgreSQL code).  You can
execute PQnotifies without any PQexecs, and you can even have your
application sleep waiting for a notify to come in.  See the libpq
docs under the heading of "Asynchronous Notification".

			regards, tom lane

Mon, 04 Mar 2002 17:05:23 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Database Caching 
On Mon, 2002-03-04 at 23:50, Tom Lane wrote:
> "Rod Taylor" <rbt@zort.ca> writes:
> > Rather than result caching, I'd much rather see an asynchronous NOTICE
> > telling my webservers which have RULES firing them off when a table is
> > modified.
> 
> LISTEN/NOTIFY?

But is there an easy way to see which tables affect the query result,
something like machine-readable EXPLAIN ?

Another thing that I have thought about Is adding a parameter to notify,
so that you can be told _what_ is changed (there is big difference
between being told that "somebody called" and "Bob called")

There are two ways of doing it 

1) the "wire protocol compatible" way , where the argument to LISTEN is
interpreted as a regular expression (or LIKE expression), so that you
can do

LISTEN 'ITEM_INVALID:%';

and the receive all notifies for

NOTIFY 'ITEM_INVALID:' || ITEM_ID;

and

NOTIFY 'ITEM_INVALID:ALL';

where the notify comes in as one string

2) the more general way where you listen on exact "relation" and notify
has an argument at both syntax and protocol level, i.e

LISTEN ITEM_INVALID;

and

NOTIFY 'ITEM_INVALID',ITEM_ID;

NOTIFY 'ITEM_INVALID','ALL';

------------------
Hannu



05 Mar 2002 09:45:26 +0200
Hannu Krosing <hannu@tm.ee>
Cache invalidation notification (was: Database Caching)
You could accomplish that with rules.

Make a rule with the where clause you like, then NOTIFY the client.
--
Rod Taylor

This message represents the official view of the voices in my head

----- Original Message -----
From: "Hannu Krosing" <hannu@tm.ee>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "Rod Taylor" <rbt@zort.ca>; "Hannu Krosing" <hannu@krosing.net>;
"Dann Corbit" <DCorbit@connx.com>; "Greg Sabino Mullane"
<greg@turnstep.com>; <pgsql-hackers@postgresql.org>
Sent: Tuesday, March 05, 2002 2:45 AM
Subject: [HACKERS] Cache invalidation notification (was: Database
Caching)


> On Mon, 2002-03-04 at 23:50, Tom Lane wrote:
> > "Rod Taylor" <rbt@zort.ca> writes:
> > > Rather than result caching, I'd much rather see an asynchronous
NOTICE
> > > telling my webservers which have RULES firing them off when a
table is
> > > modified.
> >
> > LISTEN/NOTIFY?
>
> But is there an easy way to see which tables affect the query
result,
> something like machine-readable EXPLAIN ?
>
> Another thing that I have thought about Is adding a parameter to
notify,
> so that you can be told _what_ is changed (there is big difference
> between being told that "somebody called" and "Bob called")
>
> There are two ways of doing it
>
> 1) the "wire protocol compatible" way , where the argument to LISTEN
is
> interpreted as a regular expression (or LIKE expression), so that
you
> can do
>
> LISTEN 'ITEM_INVALID:%';
>
> and the receive all notifies for
>
> NOTIFY 'ITEM_INVALID:' || ITEM_ID;
>
> and
>
> NOTIFY 'ITEM_INVALID:ALL';
>
> where the notify comes in as one string
>
> 2) the more general way where you listen on exact "relation" and
notify
> has an argument at both syntax and protocol level, i.e
>
> LISTEN ITEM_INVALID;
>
> and
>
> NOTIFY 'ITEM_INVALID',ITEM_ID;
>
> NOTIFY 'ITEM_INVALID','ALL';
>
> ------------------
> Hannu
>
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>


Tue, 5 Mar 2002 08:10:50 -0500
"Rod Taylor" <rbt@zort.ca>
Re: Cache invalidation notification (was: Database Caching)
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



Tue, 05 Mar 2002 11:35:32 -0500
F Harvell <fharvell@fts.net>
Re: Database Caching 
> 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.


Tue, 5 Mar 2002 12:46:27 -0500
"Rod Taylor" <rbt@zort.ca>
Re: Database Caching 
> > > 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





Tue, 5 Mar 2002 12:13:15 -0700
"Steve Wolfe" <steve@iboats.com>
Re: Database Caching 
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



Tue, 05 Mar 2002 14:59:45 -0500
F Harvell <fharvell@fts.net>
Re: Database Caching 