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