Thread: speeding up pg_dump?
hi list
the pg_dump of our 2.45gb db takes an awfully long 45min during which db services come more or less to a hold.
this does not seem right to me. cpus and disks are basically idelling around. what other factors are involved in pg_dump?
here's our pg_dump command:
pg_dump -Fc -Z 2 -U postgres -f /backup/temp/mydb.db mydb
i tried to remove compression but that didn't help either - the server is more or less idle anyways.
i've noticed autovacuum continuing to vacuum tables during backup. might this be a problem?
any help speeding up the backup process is very appreciated!
- thomas
<me@alternize.com> writes:
> the pg_dump of our 2.45gb db takes an awfully long 45min during which db
> services come more or less to a hold.
> this does not seem right to me.
Me either. Do you have operations that are taking exclusive locks on
tables? pg_dump takes AccessShareLock (ie, a reader's lock) on every
table in sight, to make sure the tables don't disappear or change schema
underneath it. This doesn't cause any problem for concurrent SELECT,
INSERT/UPDATE/DELETE, nor plain VACUUM ... but it could result in
blocking schema changes, VACUUM FULL, etc. And if so, those would
in turn block everything else.
Looking at pg_locks would confirm or deny this idea.
regards, tom lane
> Me either. Do you have operations that are taking exclusive locks on > tables? there is one exclusive lock... locktype | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid | mode | granted ---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+------+-----------------+--------- relation | 4131529 | 10342 | | | | | | | 42571885 | 3680 | AccessShareLock | t transactionid | | | | | 42571885 | | | | 42571885 | 3680 | ExclusiveLock | t (2 rows) but this lock is also present when restarting the db, so i guess this shouldn't hurt? VACUUM FULL takes around 15min... - thomas
<me@alternize.com> writes:
>> Me either. Do you have operations that are taking exclusive locks on
>> tables?
> there is one exclusive lock...
> transactionid | | | | | 42571885 |
> | | | 42571885 | 3680 | ExclusiveLock | t
Every transaction takes ExclusiveLock on its own transaction ID. That
in itself isn't an issue. You sure you don't see any rows with granted
= 'f' while pg_dump is running and everything seems blocked?
regards, tom lane
From: "Tom Lane" <tgl@sss.pgh.pa.us> > Every transaction takes ExclusiveLock on its own transaction ID. That > in itself isn't an issue. You sure you don't see any rows with granted > = 'f' while pg_dump is running and everything seems blocked? yes. during a pg_dump, there are like 30 locks - all of then granted (t) i'll set up pg8.1.1 tomorrow on a new server to check if its db/web or server related... best regards, thomas
> From: "Tom Lane" <tgl@sss.pgh.pa.us>
>> Every transaction takes ExclusiveLock on its own transaction ID. That
>> in itself isn't an issue. You sure you don't see any rows with granted
>> = 'f' while pg_dump is running and everything seems blocked?
>
> yes. during a pg_dump, there are like 30 locks - all of then granted (t)
That makes sense; once you are dumping the 30th table, there will be
about 30 locks, although they should only be AccessShared locks.
> i'll set up pg8.1.1 tomorrow on a new server to check if its db/web or
> server related...
You can expect to see a bunch of AccessShared locks associated with
the transaction used for the pg_dump.
The interesting question is what *else* is trying to grab a lock; that
"something else" is presumably the root of your troubles.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://linuxfinances.info/info/slony.html
Keeping instructions and operands in different memories saves .20
(.09) microseconds.