We do have quite a few trigger. Some are RI triggers postgres
automagically creates. The remaining triggers are used for things like
audit tables, or logging who updated a row etc...
I did what you suggested. I ran pg_dump dbname file.dmp. Immediatly
aftwards, kill -ABRT pid. I ended up w/ a 300meg core file. Just viewing
the file - it has a ton of DROP TRIGGER statements. And a few create
trigger statements.
[EMAIL PROTECTED] /tmp]# strings core | grep DROP TRIGGER | wc -l
5450219
[EMAIL PROTECTED] /tmp]# strings core | grep CREATE TRIGGER | wc -l
466
We have alot of triggers, but not 5.5 million :) So, I took one table,
and decided to look at the number of triggers for that table.
[EMAIL PROTECTED] /tmp]# strings core | grep DROP TRIGGER .* \data_owner\ | sort |
uniq | wc -l
107
[EMAIL PROTECTED] /tmp]# strings core | grep DROP TRIGGER .* \data_owner\ | wc -l
347550
[EMAIL PROTECTED] /tmp]# strings core | grep DROP TRIGGER .* \product\ | sort |
uniq | wc -l
151
[EMAIL PROTECTED] /tmp]# strings core | grep DROP TRIGGER .* \product\ | wc -l
346696
The back trace of the core looks as you describe below:
(gdb) bt
#0 0x401d7322 in __libc_send () from /lib/i686/libc.so.6
#1 0x40020675 in pqFlush () at eval.c:41
#2 0x4001e127 in PQsendQuery () at eval.c:41
#3 0x4001eb21 in PQexec () at eval.c:41
#4 0x0804d312 in getTables () at eval.c:41
#5 0x0805283e in dumpSchema () at eval.c:41
#6 0x0804adb2 in main () at eval.c:41
#7 0x4010c177 in __libc_start_main (main=0x804a5f4 main, argc=2,
ubp_av=0xbfffe204, init=0x804954c _init, fini=0x8059c50 _fini,
rtld_fini=0x4000e184 _dl_fini, stack_end=0xbfffe1fc)
at ../sysdeps/generic/libc-start.c:129
(gdb)
Again, this happens only on some of the servers... But, we downloaded the
entire db directory from one of those servers, and ran postgres against
that directory - and we see the same results... So, it would seems that
something in the data causes pg_dump to freak out... Is there a query
that I can run against the system catalog that would help ?
Steve
On Fri, 19 Mar 2004, Tom Lane wrote:
Steve Krall [EMAIL PROTECTED] writes:
You can get the file here( 20 megs uncompressed, 130K compressed ):
http://www.papajohns.com/postgres/postgres.log.bz2
While this dump was running, top reported that pg_dump was taking up
around 500-550megs. Then the machine stopped responding.
Hmm. The trace looks completely unexceptional --- it's just running
through your tables collecting index and trigger info (the loop in
getTables() in pg_dump.c). You do seem to have rather a lot of
triggers, but not 500 megs worth.
Digging in the 7.1 source code, I notice that there is a small leak in
this loop: the query results from the two index-related queries are
never freed. There should be a PQclear(res2); at line 2313 and
another at line 2386. (Each at the end of the scope of the res2 local
variables; the line numbers might be a bit different in 7.1.2 than in
the 7.1.3 code I'm looking at.) However the trace shows that these
queries are executed a couple hundred times apiece, and the waste from
the unfreed query results shouldn't exceed a couple K each, so this
doesn't explain hundreds of megs of bloat either. Still you might try
fixing it and see if it makes a difference.
The next move I can think of is to kill -ABRT the pg_dump run after
it's gotten to some moderate size (50Meg at most) and then manually poke
through the resulting core file to get a sense of what it's filling
memory with.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
