[GENERAL] 5 minutes to pg_dump nothing

Hi all,
I'm seeing a problem where pg_dump takes at least 5 minutes to execute no matter what I ask it to dump -- even a non-existent or empty table. One possible red flag is that pg_type contains 56508 rows. This doesn't seem excessive to me, but perhaps it should. I've looked through the source code and a system trace and I can see where the execution time disappears, but I can't pinpoint the source of the problem.

Some background: the database contains about 1200 schemas with 30+ tables and 20+ custom types in each. The whole database is only about 12G. An empty table created in another database on the same machine pg_dumps in < .1sec, as expected. PG version is 8.1.4, OS is FreeBSD.

When I run pg_dump in verbose mode, it stalls after printing "reading user-defined types". In the systrace, I can see the pg_types query at the top of getTypes() in pgdump.c followed by 628 reads of 4k blocks (= 2512k) which only takes a second or so in total. I guess this is the loop through the tuples in getTypes().

There's then four calls to getDomainConstraints() followed by a 129-second delay during which nothing appears in the system trace. After that, there's 124 pairs of these:
 72263 pg_dump 135.956209 CALL break(0x9b37000)  72263 pg_dump 135.956222 RET break 0

They consume another 118 seconds, and then pg_dump moves on to "reading procedural languages".

I've VACUUM FULL ANALYZEd everything I can think of. I'd really appreciate any further hints or help.

PS - Thanks for Postgres; it works like a dream for me except for infrequent bumps. =)

-- 
Philip
http://NikitaTheSpider.com/
Whole-site HTML validation, link checking and more

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               
http://archives.postgresql.org/