Re: Performance tuning...

On Jan 2, 2008 9:34 AM, Carol Walter <walterc(at)indiana(dot)edu> wrote:
> Greetings and Salutations...
>
> This has probably been discussed a number of times, but I really need
> your help. I am upgrading from 8.1 to 8.2. Right now I have the
> restore of the database to the new version running. It has been
> running since December 21, at 4:30 PM. I'm in a University
> environment and we're in the midst of winter break, so there is very
> little processing going on on my systems. This is actually a test
> run of the upgrade. I thought I would be able to do the test and
> live during the break but as slow as this thing is going, I may be
> lucky just to finish the test. This brings me to my question. Is
> there any performance tuning I can do to make this think run faster?
> This is a Sun box running Solaris 10.

That's a VERY long time for a restore to be running. You might want
to look at using pgbulkloader on your data if that helps.

Hard to say what's going wrong with this overview of info.
How big is the database on disk?
How big is the backup?
Where is the backup spending most of its time? restoring tables or
creating indexes or what?
What are you running out of? CPU, I/O, etc... vmstat iostat and top
can help figure that out.
What kind of disk subsystem do you have?
How many CPUs and how much memory does this machine have?

You can turn off fsync during the restore (good idea to turn it back
on when you're done though)
Turn up maintenance_work_mem to something in the 512M to 1G range
(depending on how much memory your machine has)