Web lists-archives.com

Re: Refresh slave state




2015-06-25 14:02 GMT+02:00 wagnerbianchi.com <me@xxxxxxxxxxxxxxxxx>:
> Some additional information here, just my 2 cents.
>
> (...)
>
> Just checking in: using two servers in replication, idle servers, on the
> slave side I configured globally the slave_net_timeout=1 and log_warnings=2,
> as I'm using 5.6 for these tests. The interest here is to check the
> reconnection made by the slave and with that, the restart of Binlog Dump
> Thread on the master. Looking at the MySQL Error log...
>
> #: slave error log - reported every 5 secs
> 2015-06-25 11:38:21 2598 [Warning] Storing MySQL user name or password
> information in the master info repository is not secure and is therefore not
> recommended. Please consider using the USER and PASSWORD connection options
> for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more
> information.
> 2015-06-25 11:38:26 2598 [Warning] Storing MySQL user name or password
> information in the master info repository is not secure and is therefore not
> recommended. Please consider using the USER and PASSWORD connection options
> for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more
> information.
> 2015-06-25 11:38:31 2598 [Warning] Storing MySQL user name or password
> information in the master info repository is not secure and is therefore not
> recommended. Please consider using the USER and PASSWORD connection options
> for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more
> information.
>
> Here we can see clearly that the slave is reconnecting every sec, since
> those messages appearing on the error log are showing us the same behavior
> that happens when one issue a START SLAVE (with no user and password and
> even SSL) using e.g. the mysql client.
>
> #: master side error log - reported every 3 secs
> 2015-06-25 11:41:05 2648 [Note] Start binlog_dump to master_thread_id(120)
> slave_server(256380), pos(mysql-bin.000002, 120)
> 2015-06-25 11:41:08 2648 [Note] Start binlog_dump to master_thread_id(121)
> slave_server(256380), pos(mysql-bin.000002, 120)
> 2015-06-25 11:41:11 2648 [Note] Start binlog_dump to master_thread_id(122)
> slave_server(256380), pos(mysql-bin.000002, 120)
>
> We can see that the master Binlog Dump Thread is re-initialized as well when
> the Slave I/O Thread reconnects.
>
> BTW, SHOW PROCESSLIST delays at least 10 seconds to report that a slave has
> died when we can see a new connection is made observing the increment of the
> thread id.
>
> (...)
>
> Even having the SLAVE reconnecting on every second, the slave error log
> reports that reconnection every 5 secs, the SHOW PROCESSLIST reports a new
> thread id every 10 secs, the master report the start of Binlog Dump Thread
> on every 3 secs.
>
> From here, we need to investigate more...
>

Wagner, thank you very much for your experience.

And when you make your network link to die (remove cable / ifconfig
down / iptables...) between your master and your slave, how long does
your Binlog Dump process stay up ?



> 2015-06-25 2:48 GMT-03:00 Ben RUBSON <ben.rubson@xxxxxxxxx>:
>>
>> 2015-06-22 13:45 GMT+02:00 Ben RUBSON <ben.rubson@xxxxxxxxx>:
>>
>> > 2015-06-19 12:08 GMT+02:00 Ben RUBSON <ben.rubson@xxxxxxxxx>:
>> >>
>> >> 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.
>> >> Perfect.
>> >
>> > Well this behavior is due to slave_net_timeout, not to wait_timeout.
>> > So neither wait_timeout nor interactive_timeout (expected),
>> > net_read_timeout, net_write_timeout helped.
>> >
>> >> 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
>> >> disappear.
>> >> 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.
>>
>> Hello,
>>
>> Would you have any further advice on this topic please ?
>>
>> Thank you again,
>>
>> Best regards,
>>
>> Ben

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