Using 7.0.3, I've got a database that has about 30 tables. One in particular
seems to be giving us problems. It's a pretty simple table with one index that
logs certain web site activity. It gets about 100K insertions per day, and now
has about 6 million records.
All of a sudden (yesterday), we seem to have reached a "critical mass". No
other tables or code have changed significantly (or more than normal). However,
the database performance has abruptly become abyssmal -- the server which
previously hadn't broken a load average of 4 now spikes continuously between 20
and 40, rarely dropping below 10. Web server logs show normal activity. Also,
in the pgsql/data/base/dbname directory, I'm getting hundreds of pg_sorttemp
and pg_noname files lying around.
I thought there might be some data or index corruption, so I've even gone so
far as to initdb and recreate the database from scratch, using a previous
pg_dump output, but it has not helped.
Six million tables doesn't seem like it should be too much of a problem, but we
suspect this table might be the cause of the problem because it's the only one
that changes significantly from day to day. Memory is ok, there is no swapping,
disk space is plentiful, I don't know where else to look. Any ideas?
--
Alex Howansky
Wankwood Associates
http://www.wankwood.com/