pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPinning a table into memory
3+ messages / 3 participants
[nested] [flat]
* Pinning a table into memory
@ 2002-10-04 16:46 David Blood <david@matraex.com>
0 siblings, 1 reply; 3+ messages in thread
From: David Blood @ 2002-10-04 16:46 UTC (permalink / raw)
To: pgsql-general@postgresql.org
In Oracle you can Pin large objects into memory to prevent frequent
reloads. Is there anyway to do this with Postgres? It appears that some
of our tables that get hit a lot may get kicked out of memory when we
access some of our huge tables. Then they have to wait for I/O to get
loaded back in.
David Blood
Matraex, Inc
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Pinning a table into memory
@ 2002-10-04 18:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Blood <david@matraex.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2002-10-04 18:47 UTC (permalink / raw)
To: David Blood <david@matraex.com>; +Cc: pgsql-general@postgresql.org
"David Blood" <david@matraex.com> writes:
> In Oracle you can Pin large objects into memory to prevent frequent
> reloads. Is there anyway to do this with Postgres?
I can never understand why people think this would be a good idea.
If you're hitting a table frequently, it will stay in memory anyway
(either in Postgres shared buffers or kernel disk cache). If you're
not hitting it frequently enough to keep it swapped in, then whatever
is getting swapped in instead is probably a better candidate to be
occupying the space. ISTM that a manual "pin this table" knob would
mostly have the effect of making performance worse, whenever the
system activity is slightly different from the situation you had in
mind when you installed the pin.
Having said that, I'll freely concede that our cache management
algorithms could use improvement (and there are people looking at
that right now). But a manual pin doesn't seem like a better answer.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Pinning a table into memory
@ 2002-10-08 13:32 Jan Wieck <JanWieck@Yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Jan Wieck @ 2002-10-08 13:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David Blood <david@matraex.com>; pgsql-general@postgresql.org
Tom Lane wrote:
>
> "David Blood" <david@matraex.com> writes:
> > In Oracle you can Pin large objects into memory to prevent frequent
> > reloads. Is there anyway to do this with Postgres?
>
> I can never understand why people think this would be a good idea.
> If you're hitting a table frequently, it will stay in memory anyway
> (either in Postgres shared buffers or kernel disk cache). If you're
> not hitting it frequently enough to keep it swapped in, then whatever
> is getting swapped in instead is probably a better candidate to be
> occupying the space.
As I understand it, he's looking for a mechanism to prevent a single
sequential scan on a table, larger than the buffer cache, to kick out
everything else at once. But I agree with you that pinning other objects
is just mucking with the symptoms instead of curing the desease.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-10-08 13:32 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-04 16:46 Pinning a table into memory David Blood <david@matraex.com>
2002-10-04 18:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-10-08 13:32 ` Jan Wieck <JanWieck@Yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox