Re: per table random-page-cost?

Le vendredi 23 octobre 2009 01:08:15, Joshua D. Drake a écrit :
> On Thu, 2009-10-22 at 11:33 -0400, Robert Haas wrote:
> > On Thu, Oct 22, 2009 at 11:16 AM, Cédric Villemain
> >
> > <cedric(dot)villemain(at)dalibo(dot)com> wrote:
> > > Le lundi 19 octobre 2009 23:27:20, Greg Stark a écrit :
> > >> On Mon, Oct 19, 2009 at 2:08 PM, marcin mank <marcin(dot)mank(at)gmail(dot)com>
wrote:
> > >> > Currently random_page_cost is a GUC. I propose that this could be
> > >> > set per-table.
> > >>
> > >> Or per-tablespace.
> > >>
> > >> Yes, I think there are a class of GUCs which describe the physical
> > >> attributes of the storage system which should be per-table or
> > >> per-tablespace. random_page_cost, sequential_page_cost,
> > >> effective_io_concurrency come to mind.
> > >
> > > and, perhaps effective_cache_size.
> > >
> > > You can have situation where you don't want some tables go to OS memory
> > > (you can disabled that at filesystem level, ... l'd like to be able to
> > > do that at postgres level but it is another point)
> > >
> > > So you put those tables in a separate tablespace, and tell postgresql
> > > that the effective_cache_size is 0 (for this tablespace), up to
> > > postgres to do the right thing with that ;)
> >
> > Why would you ever want to set effective_cache_size to 0?
>
> I think this is a misunderstanding of how effective_cache_size works. I
> can't think of any reason to do that. I could see a reason to tell the
> OS to not throw a relation into cache but that is a different thing.

Well the effective_cache_size in this context is OS cache memory (0 in my case)
+ estimation of shared_buffer.. ah so DBA should estimate the amount in the
shared_buffer only, ok.

So consider effective_cache_size = 0 + what pg_buffer_cache will tell.

My case is a table containing 29 GB of bytea in a database of 52 GB. Every row
on the 29GB table is grab only few times. And it will just renew OS cache
memory every time (the server have only 8GB of ram).
So when I remove this table (not the index) from the OS cache memory, I keep
more interesting blocks in the OS cache memory.

And disk + raid are quick enought to bypass the OS cache memory for this
tablespace.

Are things a bit clearer and usage not so silly ?

>
> Joshua D. Drake
>
> > ...Robert
>

--
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org