"David Ben-Yaacov" <David_BenYaacov@itd.sterling.com> writes:
> [ many details snipped ]
> When I do notice the database taking 99% of CPU, I generally shut down
> the ingest, and then try to vacuum the database manually. I usually
> find that the indexes that I set up on some of the large tables do not
> correlate to the actual table data.
> Finally, as we lose the database connection consistently when running
> our Perl ingest routines, we automatically try to reconnect to the
> database (as per Mr. Fournier's advice).
Losing the database connection means you are tickling some sort of
coredump-causing bug in the backend. It's not too surprising that the
indexes would be left corrupt after such a crash. The high-CPU-usage
symptom probably results when the database gets so messed up that the
system is just chasing its tail trying to use the index.
I don't believe in automatic reconnection myself --- if you do that
you're just papering over the symptom of a serious problem. Better
to find out why the backend is crashing and fix it.
> (SGI IRIX 6.5 Dual Processor Octane, Postgres 6.3 built using
> SGI's C compiler)
You might want to look at src/include/storage/s_lock.h and make sure
that the IRIX spin-lock code is correct for a multiprocessor system.
> We tried building 6.3.2 using GNU's C compiler and SGI's C compiler but
> the problem appeared instantly and was much worse. We are going to
> attempt 6.4.
Good, I would definitely suggest moving to 6.4 before you do anything
else. There were some significant bug fixes in index handling, I
believe.
FWIW, my company has been using a pre-alpha 6.4 release with no problems
in a system where multiple processes write the database concurrently.
We saw hard-to-reproduce problems when we were on 6.3.2, but those seem
to have been cleaned up over the summer.
regards, tom lane