On 2/28/2008 9:17 PM, Craig A. James wrote:
Jan Wieck wrote:
On 2/25/2008 9:59 AM, Craig A. James wrote:
I sent the attached email ony to Jan by mistake, here it is, I hope
Jan or somebody has an idea what's going on.
What I still don't understand is what that stored procedure moveSet() is
waiting for. Are there any pg_locks entries with granted='f' at all?
Forget the whole question, the mystery is solved.
A rogue process with a bug in it had created five million tables (that's right,
five MILLION tables) in the database. The process was creating about 50,000
tables per day, and had been running for almost three months.
Thanks for sharing this. Good to know that it after all wasn't caused by
Slony.
However, this should raise another question on your side. The time
consuming part inside of either lockSet() or moveSet() is a loop over a
join between Slony's sl_table and Postgres' pg_class. The join condition
between the two is sl_table.tab_reloid -> pg_class.oid. Also involved in
the query is pg_namespace, but I think we can ignore it in this case.
Your pg_class had grown into a 5M row table, which suggests that the
join should have led to an outer loop over sl_table performing index
scans via pg_class_oid_index into pg_class. Is it possible that your
vacuum strategy misses to vacuum the system catalog?
Now we can't even do a single "pg_dump --table" because getting an exclusive
lock seems to take forever and a day. So we can't dump it, and we can't use Slony to
move the node. Fortunately, we already had the most important tables replicated via
Slony, which was still working well, so we were able to just drop Slony replication and
transfer the whole load to the backup database. We'll simply discard the corrupted
database and recreate it with Slony.
An ANALYZE of pg_class might get you some relief on that.
Jan
--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin
_______________________________________________
Slony1-general mailing list
[email protected]
http://lists.slony.info/mailman/listinfo/slony1-general
