List:General Discussion« Previous MessageNext Message »
From:Peter Boros Date:September 5 2011 5:32am
Subject:Re: Seconds Behind the master 0 but in reality it's over 10
minutes or hours..
View as plain text  
Hi,

Seconds behind master works this way. It displays the value based on the
difference in the relay log's timestamp and the current timestamp on the
slave machine, it is always 0 if you have no relay log events to
process. This means that your bottleneck is the IO thread. Also, if you
monitor seconds behind master, you will see some fluctuation in it,
since when you will have a relay log event to process, the timestamp
will most likely have a huge difference, seconds behind master will be
adjusted accordingly. After the event is processed, it will be 0 again. 

If you want to monitor "real" replication lag, you can use
mk-heartbeat. 

Peter

On Sun, 2011-09-04 at 20:33 +0200, Adam PAPAI wrote:
> Dear List,
> 
> I have a Master -> [ Slave 1, Slave 2 ] setup for years.
> 
> Last week we've reinstalled the whole setup to: 5.1.58 from: 5.1.54.
> 
> The situation is:
> 
> the master says: (show master status)
> 
> mysql-bin.000087 | 974376716
> 
> 
> slave says: (show slave status)
> 
> Master_Log_File: mysql-bin.000087
> Read_Master_Log_Pos: 627246309
> Exec_Master_Log_Pos: 627246309
> Relay_Log_Space: 596009233
> Seconds_Behind_Master: 0
> 
> In reality, it's NOT 0 seconds! it's delayet more than 10-20 minutes, 
> some times more than 1 hours.
> 
> Why? Is it a network problem? If i restart the slave, nothing happens. 
> If I restart the master and the slave, it recognises the "delay" and 
> start to read the logs again, but a few minutes or hours (or sometimes 
> days later) it stops again.
> 
> Is it a slave or a master problem? Network problems? What should I do?
> 
> 
> full outputs below:
> 
> mysql> show master status;
> +------------------+----------+--------------+------------------+
> | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
> +------------------+----------+--------------+------------------+
> | mysql-bin.000088 |  8078024 |              |                  |
> +------------------+----------+--------------+------------------+
> 1 row in set (0.30 sec)
> 
> mysql>
> 
> 
> 
> mysql> show slave status\G
> *************************** 1. row ***************************
>                 Slave_IO_State: Waiting for master to send event
>                    Master_Host: 10.3.3.12
>                    Master_User: root
>                    Master_Port: 3306
>                  Connect_Retry: 60
>                Master_Log_File: mysql-bin.000087
>            Read_Master_Log_Pos: 627246309
>                 Relay_Log_File: tungsten-relay-bin.000213
>                  Relay_Log_Pos: 541523464
>          Relay_Master_Log_File: mysql-bin.000087
>               Slave_IO_Running: Yes
>              Slave_SQL_Running: Yes
>                Replicate_Do_DB:
>            Replicate_Ignore_DB:
>             Replicate_Do_Table:
>         Replicate_Ignore_Table:
>        Replicate_Wild_Do_Table:
>    Replicate_Wild_Ignore_Table:
>                     Last_Errno: 0
>                     Last_Error:
>                   Skip_Counter: 0
>            Exec_Master_Log_Pos: 627246309
>                Relay_Log_Space: 596009233
>                Until_Condition: None
>                 Until_Log_File:
>                  Until_Log_Pos: 0
>             Master_SSL_Allowed: No
>             Master_SSL_CA_File:
>             Master_SSL_CA_Path:
>                Master_SSL_Cert:
>              Master_SSL_Cipher:
>                 Master_SSL_Key:
>          Seconds_Behind_Master: 0
> Master_SSL_Verify_Server_Cert: No
>                  Last_IO_Errno: 0
>                  Last_IO_Error:
>                 Last_SQL_Errno: 0
>                 Last_SQL_Error:
> 1 row in set (0.00 sec)
> 
> Any ideas?
> 
> I have the db under /var/db/mysql
> 
> it shows:
> 
> -rw-rw----   1 mysql  mysql  149765008 Sep  4 18:37 
> tungsten-relay-bin.000214
> -rw-rw----   1 mysql  mysql         67 Sep  4 18:38 relay-log.info
> -rw-rw----   1 mysql  mysql         73 Sep  4 18:38 master.info
> 
> but the date is:
> 
> [root@tungsten /var/db/mysql]# date
> Sun Sep  4 19:11:13 CEST 2011
> 
> 
> 
> And it's delayed 1 hour. No data is transferred to the slave. It says: 
> Secords behind master 0.
> 
> What the hell is going on here?
> 
> -- 
> Adam PAPAI
> Grapes Communication Kft.
> http://www.grapes.hu
> E-mail: papai.adam@stripped
> Phone: +36 30 33-55-735 (Hungary)
> 
> 


Thread
MySQL on Mac OS X ServerSilvio Sosio21 Apr
Re: Seconds Behind the master 0 but in reality it's over 10 minutesor hours..Adam PAPAI4 Sep
  • Re: Seconds Behind the master 0 but in reality it's over 10minutes or hours..Peter Boros5 Sep