Re: Very big insert/join performance problem (bacula)

Le Tuesday 14 July 2009 10:15:21, vous avez écrit :
> Marc Cousin wrote:
> >> Your effective_cache_size is really small for the system you seem to
> >> have - its the size of IO caching your os is doing and uses no resources
> >> itself. And 800MB of that on a system with that amount of data seems a
> >> bit unlikely ;-)
> >>
> >> Using `free` you can see the amount of io caching your OS is doing atm.
> >> in the 'cached' column.
> >>
> >> That possibly might tip some plans in a direction you prefer.
> >>
> >> What kind of machine are you running this on?
> >
> > I played with this parameter too, and it didn't influence the plan.
> > Anyway, the doc says it's the OS cache available for one query,
>
> No they don't. I'm guessing you're getting mixed up with work_mem.

I'm not (from the docs) :
effective_cache_size (integer)
Sets the planner's assumption about the effective size of the disk cache that
is available to a single query

I trust you, of course, but then I think maybe this should be rephrased in the
doc then, because I understand it like I said ... I always had a doubt about
this sentence, and that's why I tried both 800MB and 8GB for this parameter.

>
> > and there may be a lot of
> >
> > insert queries at the same time, so I chose to be conservative with this
> > value. I tried it with 8GB too, the plans were the same.
> >
> > The OS cache is around 8-10GB by the way.
>
> That's what you need to set effective_cache_size to then.
Ok but that doesn't change a thing for this query (I had a doubt on this
parameter and tried with both 800MB and 8GB)