Re: per table random-page-cost?

Le vendredi 23 octobre 2009 14:23:09, Robert Haas a écrit :
> On Fri, Oct 23, 2009 at 5:23 AM, Cédric Villemain
>
> <cedric(dot)villemain(at)dalibo(dot)com> wrote:
> > 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 ?
>
> Well, I think you're vastly overestimating the power of
> effective_cache_size. effective_cache_size changes the planner's
> estimation of how likely a repeated partial index scan is to find the
> same block in cache. So it only affects
> nested-loop-with-inner-indexscan plans, and if effective_cache_size is
> set to a value larger than the size of the index (or maybe the
> relation, I'm too lazy to go reread the code right now), one value is
> as good as another. For a typical user, I think you could set
> effective_cache_size to, say, a terabyte, and it wouldn't make a bit
> of difference. Heck, why not 2TB.

Ok. I don't care too much on this parameter so.
As we were talking about some parameters that can be tablespace specific, I
thought this one can have different values too.

>
> As far as I can see, the only possible value of setting this knob to a
> value other than positive-infinity is that if you have a huge dataset
> that's not close to fitting in memory, this might cause the planner to
> pick a merge join over a nested loop with inner indexscan, which might
> be better if it makes the I/O sequential rather than random. Anyone
> think I'm a pessimist?
>
> ...Robert
>

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