Note that updating strategy of Seconds_Behind_Master also needs to consider the behavior of delayed slaves. See MDEV-29639 for details.

Hi Andrei!

This is ready for review. PR-2296

The cause of Seconds_Behind_Master (SBM) reporting as 0 after slave restart is that the relay log is empty until the IO thread is able to re-queue the events. That is, after STOP SLAVE has been issued, SBM is invalidated, and presented as NULL by SHOW SLAVE STATUS. Currently, this state is maintained until the slave threads are running again. However, SBM will report as 0 for the time it takes the IO thread to receive an event from the master, queue it to the relay log, and until the SQL thread reads it. This is misleading, as SBM is not actually 0, but should still be invalid because it is not yet known when the last timestamp from the master is.

The proposed fix (from Elkin) is to extend the duration of the NULL/invalid state of SBM until the first event is read by the SQL thread.

NULL looks like a bad idea what about 666 ? None of the tools around monitoring will have to be changed with a valid numeric value .

Unless io_thread reports "No" when second behind_masster is reporting NULL

Best would be to refetch the first event from the relay log to extract last event time ever played from the rejoining node and compute the delay from current_time - first_relay_time. and later from connection set it to 0 if the first event receive from leader is same GTID as this one , i don't think this could happen as if exists multiple entry in relay log it's because of a late situation

For my knowledge what reason trigger the relay log to be deleted, if they get replayed the time can be read from sql_thread ?

stephane@skysql.com, you have a good point and a nice recommendation. It's better not to endanger monitoring tools.
The relay log can be deleted at the slave start time due to the gtid slave setup via Change-Master...master_use_gtid=slave_pos (as well as by the legacy --relay-log-recovery).
It's not a big deal to memorize the last gained SBM to use it to compute an estimate while the first slave-restart event is still coming.
In case the last time SBM is unknown (the slave server restart), a maximum SBM would be displayed (rather than zero as of current).
bnestere, what you'd say as well?

"memorize the last gained SBM", non valid for stop slave with no delay but the next event is a long query

To preserve the definition of SBM = time difference of last event in queue and oldest event in the queue being COMMITTED,
i'm still curious why not using a heartbeat from the leader enrich with timestamp of last binary log event would be more accurate, a slave would not start fetching event before first heartbeat ? And SBM definition become time difference last event in the leader and oldest event COMMITTED in the queue

Andrei i have check the state Seconds_Behind_Master: NULL Slave_IO_Running: No already exist so will not break any tool , So init SBM NULL and transition of Slave_IO_Running to yes after first event fetch is correct

stephane@skysql.com, to the HB exploitation , your direction is great. Just not HB, but when necessary it's feasible to add up to the master-slave connection handshake something like you propose.
E.g that the slave service is started for the 1st time on the (restarted) slave server. In the recommended CM...master_use_gtid = slave_pos in the handshake time slave would receive back the end-of-transaction timestamp corresponding to its last GTID executed (without this ts piece slave would be aware only of the GTID details of its last executed trx).
This measure refines 'a maximum SBM'.

handshake to both of you so

At the same time Hancheck + Heartbeat would refine SBM by accounting network time, long waiting request