Re: Multiple buffer cache?

2010/2/6 BillR <iambill@williamrosmus.com>:
> 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.

In some way pgfincore let you do that with function
pgfadv_willneed(table/index). It will try to load blocks of the
relations without killing your IO.

But, I am not sure it is the correct answer for the problem here.

>
> 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
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



--
Cédric Villemain