Re: speed up restore from dump

On Friday 31 October 2008 08:07:08 Sam Mason wrote:
> On Thu, Oct 30, 2008 at 02:28:38PM -0700, Alan Hodgson wrote:
> > On Thursday 30 October 2008, Joao Ferreira
> > <jmcferreira(at)critical-links(dot)com>
> >
> > wrote:
> > > well..... see for yourself... (360 RAM , 524 SWAP) that's what it is...
> > > it supposed to be somewhat an embedded product...
> >
> > Clearly your hardware is your speed limitation. If you're swapping at
> > all, anything running on the machine is going to be slow.
>
> The vmstat output only showed the odd block going in and out; but
> performance is only really going to suffer when it's thrashing. If the
> swap in number stays in the double digits for a reasonable amount of
> time then you should probably look at what's causing it. Giving memory
> back to the system to use for caching the file system can be good, lots
> of shared memory can also be good.
>

well, i think he needs to cut back on the work mem, but i think he might want
to give a little more to wal buffers.

> Building indexes takes time and IO bandwidth, maybe you could look at
> building less of them? I'd be tempted to pull the import script apart
> into its constituent parts, i.e. the initial data load, and then all the
> constraints/index builds separately. Then run through executing them by
> hand and see what you can change to make things more efficient.
>

It would be good to know where and when his bottlenecks are... ie. i could see
him being i/o, memory, or cpu bottlenecked depending on where he is in the
restore process.

--
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com