Hmm... This gets stranger and stranger. When connecting to the
database with the psql client in 8.2's bin directory and using commands
such as \d the client hangs, or takes an extremely long time. If we
connect to the same 8.2 database with a psql client from 8.1.4, both
remotely and locally, \d responds immediately. Could the issue be with
the client programs somehow? Note also that we did our migration over
the xmas weekend using the dump straight into a restore command. We
kicked it off Saturday (12-23-06) night and it had just reached the
point of adding foreign keys the morning of the 26th. We stopped it
there, wrote a script to go through and build indexes (which finished in
a timely manner) and have added just the foreign keys strictly necessary
for our applications functionality (i.e. foreign keys set to cascade on
update/delete, etc...).
-------- Original Message --------
Subject: Re: [PERFORM] Slow dump?
Date: Tue, 02 Jan 2007 11:40:18 -0600
From: Erik Jones <erik@xxxxxxxxxx>
To: Tom Lane <tgl@xxxxxxxxxxxxx>
CC: pgsql-performance@xxxxxxxxxxxxxx
References: <459A8B51.8000906@xxxxxxxxxx> <5876.1167759024@xxxxxxxxxxxxx>
Tom Lane wrote:
Erik Jones <erik@xxxxxxxxxx> writes:
Hello, we recently migrated our system from 8.1.x to 8.2 and when
running dumps have noticed an extreme decrease in speed where the dump
is concerned (by more than a factor of 2).
That's odd. pg_dump is normally pretty much I/O bound, at least
assuming your tables are sizable. The only way it wouldn't be is if you
have a datatype with a very slow output converter. Have you looked into
exactly which tables are slow to dump and what datatypes they contain?
(Running pg_dump with log_min_duration_statement enabled would provide
useful data about which steps take a long time, if you're not sure.)
regards, tom lane
Well, all of our tables use pretty basic data types: integer (various
sizes), text, varchar, boolean, and timestamps without time zone. In
addition, other than not having a lot of our foreign keys in place,
there have been no other schema changes since the migration.
--
erik jones <erik@xxxxxxxxxx>
software development
emma(r)
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
--
erik jones <erik@xxxxxxxxxx>
software development
emma(r)
