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

Le Wednesday 15 July 2009 15:45:01, Alvaro Herrera a écrit :
> Marc Cousin escribió:
> > There are other things I am thinking of : maybe it would be better to
> > have sort space on another (and not DBRD'ded) raid set ? we have a quite
> > cheap setup right now for the database, and I think maybe this would help
> > scale better. I can get a filesystem in another volume group, which is
> > not used that much for now.
>
> You know, that's the first thing it came to me when I read you're using
> DRDB. Have you tried setting temp_tablespace to a non-replicated disk?

I wish I could easily. I'm not entitled to tune the database, only to give
directives. I've given this one, but I don't know when it will be done. I'll
keep you informed on this one, but I don't have my hopes too high.

As mentionned before, I tried to deactivate DRBD (still using the DRBD device,
but not connected to the other node, so it has almost no effect). It didn't
change much (performance was a bit (around 20% better).

Anyway, the thing is that :
- big sorts kill my machine when there are more that 5 of them. I think it is
a system problem (raid, filesystem, linux tuning, don't really know, I'll have
to dig into this, but it will be complicated, for human reasons :) )
- the plan through nested loops is faster anyway, and I think it's because
there is only a small fraction of filename and path that is used (most files
backed up have the same name or path, as we save 600 machines with mostly 2
OSes, linux and windows), so the hot parts of these 2 tables are extremely
likely to be in the database or linux cache (buffer hit rate was 97% in the
example provided). Moreover, the first two queries of the insert procedure fill
the cache for us...