pg_dump slow on windows

From: Kobus Wolvaardt <kobuswolf(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: pg_dump slow on windows
Date: 2012-09-06 20:34:39
Message-ID: CALy-Kr7ZK41BtyvfQ92B2qGy148zFRO431+TwZLE06KepsD+Ng@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I hope this is the right list. I have read through a few pg_dump slow posts
but none of them seem to apply to our case. We have a nice big server
running windows server 2008 and postgres 8.4. The machine does nothing else
and every so often running the nightly backup take 10 to 12 hours to
complete. Most nights it completes in 1 to 2 hours. Just for some context,
5 rather large DBs get backed up. All of them have many schema (100s) and
many many tables.

A few things I cannot change. I cannot switch to Linux even though I want
to. I cannot upgrade to a newer postgres just yet (though 9.0 should be
installed before year end). One of the issues holding a newer postgres up
is the fact that backups aren't running right. :-)

So my first thought was the machine or DB being over worked, but apart from
a lone vacuum at 2am there is little else going on, the cpu seems fine. I
thought maybe IO, but running the command as a user during peak working
hours seems to run well (< hour per DB).

Something that is curios is that if a DB takes long, it really takes
horribly long like some kind of a lock is holding it. It would sit at a few
kb dump size for 20 minutes en then run a bit and get stuck again (as far
as we can tell), what we do know is that it is way to slow for some IO or
cpu starvation. We have seen a 5GB backup sitting at 1Gb after 12hours and
then we stop it.

Any siggestions? Can autovacume or lack thereof cause this? It seems noone
has been doing any maintenance on the DB (it does look like autovacuum is
running), so any suggestions would be nice.

Thanks,
Kobus

P.S. Would we see performance improvements on windows going from 8.4 to
9.0? Any comment on the difference between 32 and 64? Is it a safe
migration?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2012-09-06 20:38:35 Re: pg_dump slow on windows
Previous Message Tom Lane 2012-09-06 20:15:02 Re: return text from explain