speeding up pg_dump?

От:

<me@alternize.com>

Дата:

26 декабря 2005 г. в 22:48:02

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

Re: speeding up pg_dump?

От:

<me@alternize.com>

Дата:

27 декабря 2005 г. в 01:39:55

> 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

Re: speeding up pg_dump?

От:

<me@alternize.com>

Дата:

27 декабря 2005 г. в 02:10:34

From: "Tom Lane" 
> 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

Re: speeding up pg_dump?

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

27 декабря 2005 г. в 00:35:25

 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

Re: speeding up pg_dump?

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

27 декабря 2005 г. в 01:56:10

 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

Re: speeding up pg_dump?

От:

Christopher Browne <cbbrowne@acm.org>

Дата:

31 декабря 2005 г. в 03:14:52

> From: "Tom Lane" 
>> 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.