Re: Multiple buffer cache? - Mailing list pgsql-general

I don't know how to do this with PostgreSQL, but I am pretty sure what
Alexei is looking for is what Oracle and SQL Server people refer to as
'pinning' a table or other DB object (into memory). I would be interested to
know if PostgreSQL does this too. I think it is a very useful feature.

How to pin a table in cache with Oracle (for an example):
http://www.jlcomp.demon.co.uk/faq/pin_table.html

Couple more examples.

http://blogs.oracle.com/stevenChan/2007/05/pinning_objects_to_improve_app.ht
ml

http://www.mssqltips.com/tip.asp?tip=1317

In some large enterprise systems I have worked on (e.g. tier one telecom
companies), besides the standard Oracle installation the billing systems
used one database product where everything was in memory. This was used
*mostly* for static lookup data to help speed up the performance of the
system. When you have say, 300 million customers, every little bit helps. :)

Hopefully someone knows how with Postgres.

Cheers

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Bret S. Lambert
Sent: February-06-10 4:50 AM
To: Alexei Vladishev
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Multiple buffer cache?

On Fri, Feb 05, 2010 at 11:41:13PM +0200, Alexei Vladishev wrote:
> Greetings,
>
> Is there a way of configuring PostgreSQL so that one specific table would
> use, say, 4GB of buffer cache while other tables would use the rest?
>
> I would like to keep the table and its indexes always in "hot"
> state, so that
> other queries won't pollute this part of the buffer cache. It would ensure
> reliable performance and much less disk IOPS working with the table.

Fiddling with the buffer cache like that would require some sort of
OS support, if I'm not mistaken in what you're asking for.

And then, even if the support is there, you'd need to outline exactly
how you're planning on pushing this button.

Specifically, what's your usage pattern that would make this a
win for you?

If the table and its indexes can already fit into the buffer cache,
and it's as commonly accessed as you think it is, the OS should
probably have it cached anyway.

>
> Is it possible?
>
> Thanks for any hints!
>
> Alexei
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--------------------------------
Spam/Virus scanning by CanIt Pro

For more information see
http://www.kgbinternet.com/SpamFilter.htm

To control your spam filter, log in at
http://filter.kgbinternet.com


--
BEGIN-ANTISPAM-VOTING-LINKS
------------------------------------------------------

Teach CanIt if this mail (ID 80190050) is spam:
Spam:
http://filter.kgbinternet.com/canit/b.php?i=80190050&m=fbb7172fd677&t=201002
06&c=s
Not spam:
http://filter.kgbinternet.com/canit/b.php?i=80190050&m=fbb7172fd677&t=201002
06&c=n
Forget vote:
http://filter.kgbinternet.com/canit/b.php?i=80190050&m=fbb7172fd677&t=201002
06&c=f
------------------------------------------------------
END-ANTISPAM-VOTING-LINKS

__________ Information from ESET Smart Security, version of virus signature
database 4841 (20100206) __________

The message was checked by ESET Smart Security.

http://www.eset.com