Re: Seconds Behind the master 0 but in reality it"s over 10 minutesor hours..

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@grapes.hu
Phone: +36 30 33-55-735 (Hungary)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Seconds Behind the master 0 but in reality it"s over 10minutes or hours..

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@grapes.hu
> Phone: +36 30 33-55-735 (Hungary)
>
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org