Web lists-archives.com

Re: Refresh slave state

2015-06-18 22:52 GMT+02:00 shawn l.green <shawn.l.green@xxxxxxxxxx>:
> On 6/18/2015 2:10 PM, Ben RUBSON wrote:
>> Hello,
>> In order for the slave to quickly show a communication issue between
>> the master and the slave, I set slave_net_timeout to 10.
>> "show slave status" then quickly updates, perfect.
>> I would also like the master to quickly show when the slave is no more
>> reachable.
>> However, "show processlist" and "show slave hosts" take a very long
>> time to update their status when the slave has gone.
>> Is there any way to have a refresh rate of about 10 seconds, as I did
>> on slave side ?
> There are two situations to consider
> 1) The slave is busy re-trying.  It will do this a number of times then
> eventually disconnect itself. If it does disconnect itself, the processlist
> report will show it as soon as that happens.

Yes, I confirm.

> 2) The connection between the master and slave died (or the slave itself is
> lost).  In this case, the server did not receive any "I am going to
> disconnect" message from its client (the slave). So as far as the server is
> concerned, it is simply sitting in a wait expecting the client to eventually
> send in a new command packet.
> That wait is controlled by --wait-timeout.  Once an idle client connection
> hits that limit, the server is programmed to think "the idiot on the other
> end of this call has hung up on me" so it simply closes its end of the
> socket. There are actually two different timers that could be used,
> --wait-timeout or --interactive-timeout and which one is used to monitor the
> idle socket depends entirely on if the client did or did not set the
> 'interactive flag' when it formed the connection. MySQL slaves do not use
> that flag.
> Now, if the line between the two systems died in the middle of a
> conversation (an actual data transfer) then a shorter -net-write-timeout or
> --net-read-timeout would expire and the session would die then.

This is the interesting part yes, when the connection dies (whatever
the link status is at this moment, idle or not).
So I set wait_timeout=10.

When the link is up, we clearly see that the idle connection is reset
every 10 seconds : the "show processlist" clearly shows that the slave
TCP source port changes, and time is reset from 10 to 0.

However, when the link dies, the "Binlog Dump" process stays in the
"show processlist", I have to wait more than 1000 seconds for it to
I made tests adding interactive_timeout=10, net_read_timeout=10 and
net_write_timeout=10, however the behavior is the same.

Did I miss something ?

Of course goal is to monitor replication, from the slave (done and
working thanks to slave_net_timeout), but from the master too (some
more tuning needed), as we never know which one will be able to
transmit the alert properly.

Thank you very much Shawn.

Best regards,


MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql