On 2/23/2008 1:28 PM, Craig James wrote:
Jan Wieck wrote:
On 2/23/2008 12:20 AM, Craig James wrote:
A little more info on this problem...
Craig James wrote:
I'm trying to migrate a node for the second time, and no luck. Last
time I tried it, it just got stuck, and due to lack of time, I didn't
investigate.
This time I watched -- it got stuck again, doing some sort of huge
SELECT statement. I was under the impression that migrating a node
was a fairly simple operation that should happen in a short time
(less than a minute?) even for large databases.
I waited 10 minutes, during which the entire system was completely
locked up (no other process could access the database), and our web
site was offline. I finally had to kill all of the slon daemons and
kill Postgres to get our site back on the air, then run the
node-unlock command to get Slony back in shape.
This system appears to otherwise be working well. I can insert,
update and delete records, and they're copied to the slave node
immediately.
What's up? Am I just too impatient?
I tried it again, after vacuuming the slony tables that are subject to
bloat. This time I shut everything off, started the migration of the
master to node 2, and waited for 35 minutes, but the SELECT never
finished. vmstat showed massive I/O and CPU activity the whole time.
What SELECT are you referring to? I don't see where in the MOVE SET you
have to perform any SELECT.
You tell me? It is the slon daemon that is executing this select. There were
no other connections to the database the second time I tried this.
Again, after I killed postgres, restarted, and unlocked the node,
Slony went back to performing perfectly.
Killing postgres is a bad idea. Stop that habit right now, before you
physically corrupt any of your databases.
Thanks for the advice, but I don't think it's a problem. That's one of the
features of a robust relational database with a write-ahead log -- it can
withstand being killed without corrupting data. Besides, I had no choice, my
web site went offline because slon apparently took an exclusive lock on the
tables, blocking all other activity. And I killed a SELECT, not an INSERT or
UPDATE.
But that's a topic for a separate discussion ... I have to fix this Slony
problem first.
I don't think there is anything to discuss about killing the postmaster.
If you can't do it with pg_ctl, then don't do it.
Anyhow, apparently the LOCK SET part of the process succeeds. So what I
now assume is that the WAIT FOR EVENT never finishes. First, you don't
need a WAIT FOR EVENT between LOCK SET and MOVE SET. Both events are
executed on the origin, so by the time the LOCK SET finishes, everything
is ready for the MOVE.
I don't think it got as far as this, but I don't know the internals. When I
execute the script, the SELECT starts, and that's where everything comes to a
sudden halt.
I do know a little bit about the internals.
The lockSet() stored procedure, called by slonik to perform the LOCK SET
command, tries to add a trigger to all the tables in the set. That
requires an access exclusive lock on those tables. If nothing else is
connected to the database, then there is no reason why that would block.
So either your slonik script gets past that point, or your assumption
nothing is holding any kind of lock on any table in the set is wrong.
But what this indicates is that node 2 never confirms the LOCK SET. Can
it be that you actually have a problem with the connection from node 2
to node 1? What is the content of the view sl_status on both nodes?
Both nodes seem to be normal -- the st_last_event_ts is just a few seconds
prior to the query, st_lag_time is 00:00:11.465251 (node 1) and 00:00:07.50164.
That tells us at least that your sl_path settings are correct.
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
