DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=RU0BWk7TPMQRiPnY9dgAV4nArtDfkqOKhw3m+PVBer3HgMPrDMgOFldbTFXxeIdvvTAIpvVdGkQMEPvbOoYs0CJgxkzDH76iX/pcBpILg5k45bgid2wUkDTfyUowHa6vRhS4i65MokaiF24ufhlaxhcRF6CLWOcbaZQT6kKQcGc=
Message-ID: <33c6269f0510241611r1c6568fbg17ed2d059f46fbbb@mail.gmail.com>
Date: Mon, 24 Oct 2005 19:11:15 -0400
From: Alex Turner <armtuk@gmail.com>
To: "Craig A. James" <cjames@modgraph-usa.com>
Subject: Re: Is There Any Way ....
Cc: pgsql-performance@postgresql.org
In-Reply-To: <435D5DA6.9090405@moonviewscientific.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <435D5DA6.9090405@moonviewscientific.com>

This is possible with Oracle utilizing the keep pool

alter table t_name storage ( buffer_pool keep);

If Postgres were to implement it's own caching system, this seems like
it would be easily to implement (beyond the initial caching effort).

Alex


On 10/24/05, Craig A. James <cjames@modgraph-usa.com> wrote:
> Jim C. Nasby" <jnasby ( at ) pervasive ( dot ) com> wrote:
> > > Stefan Weiss wrote:
> > > ... IMO it would be useful to have a way to tell
> > > PG that some tables were needed frequently, and should be cached if
> > > possible. This would allow application developers to consider joins w=
ith
> > > these tables as "cheap", even when querying on columns that are
> > > not indexed.
> >
> > Why do you think you'll know better than the database how frequently
> > something is used? At best, your guess will be correct and PostgreSQL
> > (or the kernel) will keep the table in memory. Or, your guess is wrong
> > and you end up wasting memory that could have been used for something
> > else.
> >
> > It would probably be better if you describe why you want to force this
> > table (or tables) into memory, so we can point you at more appropriate
> > solutions.
>
> Or perhaps we could explain why we NEED to force these tables into memory=
, so we can point you at a more appropriate implementation.  ;-)
>
> Ok, wittiness aside, here's a concrete example.  I have an application wi=
th one critical index that MUST remain in memory at all times.  The index's=
 tablespace is about 2 GB.  As long as it's in memory, performance is excel=
lent - a user's query takes a fraction of a second.  But if it gets swapped=
 out, the user's query might take up to five minutes as the index is re-rea=
d from memory.
>
> Now here's the rub.  The only performance I care about is response to que=
ries from the web application.  Everything else is low priority.  But there=
 is other activity going on.  Suppose, for example, that I'm updating table=
s, performing queries, doing administration, etc., etc., for a period of an=
 hour, during which no customer visits the site.  The another customer come=
s along and performs a query.
>
> At this point, no heuristic in the world could have guessed that I DON'T =
CARE ABOUT PERFORMANCE for anything except my web application.  The perform=
ance of all the other stuff, the administration, the updates, etc., is utte=
rly irrelevant compared to the performance of the customer's query.
>
> What actually happens is that the other activities have swapped out the c=
ritical index, and my customer waits, and waits, and waits... and goes away=
 after a minute or two.  To solve this, we've been forced to purchase two c=
omputers, and mirror the database on both.  All administration and modifica=
tion happens on the "offline" database, and the web application only uses t=
he "online" database.  At some point, we swap the two servers, sync the two=
 databases, and carry on.  It's a very unsatisfactory solution.
>
> There is ONLY one way to convey this sort of information to Postgres, whi=
ch is to provide the application developer a mechanism to explicitely name =
the tables that should be locked in memory.
>
> Look at tsearchd that Oleg is working on.  It's a direct response to this=
 problem.
>
> It's been recognized for decades that, as kernel developers (whether a Li=
nux kernel or a database kernel), our ability to predict the behavior of an=
 application is woefully inadequate compared with the application developer=
's knowledge of the application.  Computer Science simply isn't a match for=
 the human brain yet, not even close.
>
> To give you perspective, since I posted a question about this problem (re=
garding tsearch2/GIST indexes), half of the responses I received told me th=
at they encountered this problem, and their solution was to use an external=
 full-text engine.  They all confirmed that Postgres can't deal with this p=
roblem yet, primarily for the reasons outlined above.
>
> Craig
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>