Skip site navigation (1) Skip section navigation (2)

Re: Is There Any Way ....

From: Alan Stange <stange(at)rentec(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Is There Any Way ....
Date: 2005-10-25 00:50:25
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Alex Turner wrote:
> 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(at)modgraph-usa(dot)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 with
>>>> 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 with 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 excellent - 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-read from memory.
>> Now here's the rub.  The only performance I care about is response to queries from the web application.  Everything else is low priority.  But there is other activity going on.  Suppose, for example, that I'm updating tables, performing queries, doing administration, etc., etc., for a period of an hour, during which no customer visits the site.  The another customer comes 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 performance of all the other stuff, the administration, the updates, etc., is utterly irrelevant compared to the performance of the customer's query.
>> What actually happens is that the other activities have swapped out the critical 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 computers, and mirror the database on both.  All administration and modification happens on the "offline" database, and the web application only uses the "online" database.  At some point, we swap the two servers, sync the two databases, and carry on.  It's a very unsatisfactory solution.
We have a similar problem with vacuum being the equivalent of 
"continuously flush all system caches for a long time".  Our database is 
about 200GB in size and vacuums take hours and hours.   The performance 
is acceptable still, but only because we've hidden the latency in our 

I've occasionally thought it would be good to have the backend doing a 
vacuum or analyze also call priocntl() prior to doing any real work to 
lower its priority.   We'll be switching to the 8.1 release ASAP just 
because the direct IO capabilities are appearing to be a win on our 
development system.

-- Alan

In response to

pgsql-performance by date

Next:From: Christian Paul B. CosinasDate: 2005-10-25 02:39:51
Subject: Re: Used Memory
Previous:From: Alex TurnerDate: 2005-10-24 23:11:15
Subject: Re: Is There Any Way ....

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group