MySQL Forums
Forum List  »  Install & Repo

[Replication] Seconds_Behind_Master increasing
Posted by: Tom L
Date: July 05, 2013 04:04AM

Hello all!

First of all, I'm glad to be here in this awesome forum, using MySQL as each one of you. :)

I used to deal with MySQL replication issues but tody I'm confronted to a problem that I had never seen until now.

I have a Master/Slave replication which was healthy for many years (which sometimes had some replication problems, but that's ok :) ). And last night, the slave began to drop behind the master.

I am running MySQL on two Debian servers and here are the symptoms :
- No system alerts (syslog...) on both servers
- Slave_IO_Running is set to Yes on the slave server
- Slave_SQL_Running is set to Yes on the slave server
- MySQL daemon is taking over 200% of CPU usage on the slave server ("top" command)
- Seconds_Behind_Master is now at 23778 on the slave server and is increasing second by second
- The "File" column of a "show master status" command has value "mysql-bin.023891" on the master server
- The "Master_Log_File" column of a "show slave status" command has value "mysql-bin.023891" on the slave server
- The "Position" column of a "show master status" command has value "26838543" on the master server
- The "Read_Master_Log_Pos" column of a "show slave status" command has value "26838543" on the slave server
- The "Relay_Log_Pos" column of a "show slave status" command has value "19847020" on the slave server
- There are much IO Wait on the slave (between 10% and 35%)
- ibd files are well changed /var/lib/mysql/my_db/
- Every disk partition have free space available
- Exec_Master_Log_Pos and Relay_Log_Space are increasing on the slave server (show slave status)

Master server details :
- OS : Debian squeeze 6.0.3
- Architecture : 64 bits
- MySQL version : Ver 14.14 Distrib 5.1.63

Slave server details :
- OS : Debian squeeze 6.0.3
- Architecture : 64 bits
- MySQL version : Ver 14.14 Distrib 5.1.63

#################################################################################

Here are the details on the current situation :

SHOW SLAVE STATUS ON THE SLAVE SERVER :


*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.105.24
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.023891
Read_Master_Log_Pos: 26838543
Relay_Log_File: relay.033081
Relay_Log_Pos: 19847020
Relay_Master_Log_File: mysql-bin.023878
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test,my_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: 19846875
Relay_Log_Space: 1239899652
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: 24197
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

#################################################################################


SHOW MASTER STATUS ON THE MASTER SERVER :

+------------------+----------+--------------+-----------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+-----------------------+
| mysql-bin.023891 | 26838543 | test,my_db | my_db_ignored,mysql |
+------------------+----------+--------------+-----------------------+

#################################################################################


CONTENT OF /var/log/mysql/ ON THE MASTER SERVER :

-rw-r-----+ 1 mysql adm 104857700 4 juil. 13:45 mysql-bin.023782
-rw-r-----+ 1 mysql adm 104857966 4 juil. 18:31 mysql-bin.023783
-rw-r-----+ 1 mysql adm 104858062 4 juil. 18:48 mysql-bin.023784
-rw-r-----+ 1 mysql adm 104857743 4 juil. 18:50 mysql-bin.023785
-rw-r-----+ 1 mysql adm 104857699 4 juil. 18:52 mysql-bin.023786
-rw-r-----+ 1 mysql adm 104857813 4 juil. 18:54 mysql-bin.023787
-rw-r-----+ 1 mysql adm 104857977 4 juil. 18:56 mysql-bin.023788
-rw-r-----+ 1 mysql adm 104857857 4 juil. 18:57 mysql-bin.023789
-rw-r-----+ 1 mysql adm 104858072 4 juil. 18:58 mysql-bin.023790
-rw-r-----+ 1 mysql adm 104857897 4 juil. 19:00 mysql-bin.023791
-rw-r-----+ 1 mysql adm 104857976 4 juil. 19:01 mysql-bin.023792
-rw-r-----+ 1 mysql adm 104857842 4 juil. 19:02 mysql-bin.023793
-rw-r-----+ 1 mysql adm 104857765 4 juil. 19:04 mysql-bin.023794
-rw-r-----+ 1 mysql adm 104858180 4 juil. 19:05 mysql-bin.023795
-rw-r-----+ 1 mysql adm 104857774 4 juil. 19:06 mysql-bin.023796
-rw-r-----+ 1 mysql adm 104858534 4 juil. 19:07 mysql-bin.023797
-rw-r-----+ 1 mysql adm 104858145 4 juil. 19:08 mysql-bin.023798
-rw-r-----+ 1 mysql adm 104858447 4 juil. 19:10 mysql-bin.023799
-rw-r-----+ 1 mysql adm 104857972 4 juil. 19:11 mysql-bin.023800
-rw-r-----+ 1 mysql adm 104858122 4 juil. 19:12 mysql-bin.023801
-rw-r-----+ 1 mysql adm 104857754 4 juil. 19:13 mysql-bin.023802
-rw-r-----+ 1 mysql adm 104857858 4 juil. 19:14 mysql-bin.023803
-rw-r-----+ 1 mysql adm 104858352 4 juil. 19:16 mysql-bin.023804
-rw-r-----+ 1 mysql adm 104858649 4 juil. 19:17 mysql-bin.023805
-rw-r-----+ 1 mysql adm 104858219 4 juil. 19:18 mysql-bin.023806
-rw-r-----+ 1 mysql adm 104857819 4 juil. 19:19 mysql-bin.023807
-rw-r-----+ 1 mysql adm 104857652 4 juil. 19:20 mysql-bin.023808
-rw-r-----+ 1 mysql adm 104858554 4 juil. 19:22 mysql-bin.023809
-rw-r-----+ 1 mysql adm 104858231 4 juil. 19:23 mysql-bin.023810
-rw-r-----+ 1 mysql adm 104858507 4 juil. 19:24 mysql-bin.023811
-rw-r-----+ 1 mysql adm 104857858 4 juil. 19:25 mysql-bin.023812
-rw-r-----+ 1 mysql adm 104857838 4 juil. 19:27 mysql-bin.023813
-rw-r-----+ 1 mysql adm 104858464 4 juil. 19:28 mysql-bin.023814
-rw-r-----+ 1 mysql adm 104858438 4 juil. 19:29 mysql-bin.023815
-rw-r-----+ 1 mysql adm 104857904 4 juil. 19:30 mysql-bin.023816
-rw-r-----+ 1 mysql adm 104857966 4 juil. 19:32 mysql-bin.023817
-rw-r-----+ 1 mysql adm 104858334 4 juil. 19:33 mysql-bin.023818
-rw-r-----+ 1 mysql adm 104858232 4 juil. 19:34 mysql-bin.023819
-rw-r-----+ 1 mysql adm 104857652 4 juil. 19:35 mysql-bin.023820
-rw-r-----+ 1 mysql adm 104857660 4 juil. 19:37 mysql-bin.023821
-rw-r-----+ 1 mysql adm 104858435 4 juil. 19:38 mysql-bin.023822
-rw-r-----+ 1 mysql adm 104858203 4 juil. 19:39 mysql-bin.023823
-rw-r-----+ 1 mysql adm 104858155 4 juil. 19:41 mysql-bin.023824
-rw-r-----+ 1 mysql adm 104857995 4 juil. 19:42 mysql-bin.023825
-rw-r-----+ 1 mysql adm 104858623 4 juil. 19:43 mysql-bin.023826
-rw-r-----+ 1 mysql adm 104858287 4 juil. 19:44 mysql-bin.023827
-rw-r-----+ 1 mysql adm 104858005 4 juil. 19:46 mysql-bin.023828
-rw-r-----+ 1 mysql adm 104858587 4 juil. 19:47 mysql-bin.023829
-rw-r-----+ 1 mysql adm 104858250 4 juil. 19:48 mysql-bin.023830
-rw-r-----+ 1 mysql adm 104857839 4 juil. 19:49 mysql-bin.023831
-rw-r-----+ 1 mysql adm 104857688 4 juil. 19:51 mysql-bin.023832
-rw-r-----+ 1 mysql adm 104858182 4 juil. 19:52 mysql-bin.023833
-rw-r-----+ 1 mysql adm 104857713 4 juil. 19:53 mysql-bin.023834
-rw-r-----+ 1 mysql adm 104859422 4 juil. 23:01 mysql-bin.023835
-rw-r-----+ 1 mysql adm 104858389 5 juil. 04:13 mysql-bin.023836
-rw-r-----+ 1 mysql adm 104858368 5 juil. 04:16 mysql-bin.023837
-rw-r-----+ 1 mysql adm 104857919 5 juil. 04:18 mysql-bin.023838
-rw-r-----+ 1 mysql adm 104858646 5 juil. 04:20 mysql-bin.023839
-rw-r-----+ 1 mysql adm 104857685 5 juil. 04:22 mysql-bin.023840
-rw-r-----+ 1 mysql adm 104858223 5 juil. 04:23 mysql-bin.023841
-rw-r-----+ 1 mysql adm 104857805 5 juil. 04:25 mysql-bin.023842
-rw-r-----+ 1 mysql adm 104858040 5 juil. 04:26 mysql-bin.023843
-rw-r-----+ 1 mysql adm 104857764 5 juil. 04:27 mysql-bin.023844
-rw-r-----+ 1 mysql adm 104858207 5 juil. 04:29 mysql-bin.023845
-rw-r-----+ 1 mysql adm 104858336 5 juil. 04:30 mysql-bin.023846
-rw-r-----+ 1 mysql adm 104857984 5 juil. 04:31 mysql-bin.023847
-rw-r-----+ 1 mysql adm 104857886 5 juil. 04:32 mysql-bin.023848
-rw-r-----+ 1 mysql adm 104858397 5 juil. 04:33 mysql-bin.023849
-rw-r-----+ 1 mysql adm 104857801 5 juil. 04:34 mysql-bin.023850
-rw-r-----+ 1 mysql adm 104858644 5 juil. 04:36 mysql-bin.023851
-rw-r-----+ 1 mysql adm 104857659 5 juil. 04:37 mysql-bin.023852
-rw-r-----+ 1 mysql adm 104858173 5 juil. 04:38 mysql-bin.023853
-rw-r-----+ 1 mysql adm 104857774 5 juil. 04:39 mysql-bin.023854
-rw-r-----+ 1 mysql adm 104858695 5 juil. 04:40 mysql-bin.023855
-rw-r-----+ 1 mysql adm 104858134 5 juil. 04:41 mysql-bin.023856
-rw-r-----+ 1 mysql adm 104858579 5 juil. 04:43 mysql-bin.023857
-rw-r-----+ 1 mysql adm 104858660 5 juil. 04:44 mysql-bin.023858
-rw-r-----+ 1 mysql adm 104858113 5 juil. 04:45 mysql-bin.023859
-rw-r-----+ 1 mysql adm 104858198 5 juil. 04:46 mysql-bin.023860
-rw-r-----+ 1 mysql adm 104858218 5 juil. 04:47 mysql-bin.023861
-rw-r-----+ 1 mysql adm 104858140 5 juil. 04:48 mysql-bin.023862
-rw-r-----+ 1 mysql adm 104858683 5 juil. 04:50 mysql-bin.023863
-rw-r-----+ 1 mysql adm 104858569 5 juil. 04:51 mysql-bin.023864
-rw-r-----+ 1 mysql adm 104858521 5 juil. 04:52 mysql-bin.023865
-rw-r-----+ 1 mysql adm 104858440 5 juil. 04:53 mysql-bin.023866
-rw-r-----+ 1 mysql adm 104858115 5 juil. 04:54 mysql-bin.023867
-rw-r-----+ 1 mysql adm 104857757 5 juil. 04:56 mysql-bin.023868
-rw-r-----+ 1 mysql adm 104857912 5 juil. 04:57 mysql-bin.023869
-rw-r-----+ 1 mysql adm 104858268 5 juil. 04:58 mysql-bin.023870
-rw-r-----+ 1 mysql adm 104858422 5 juil. 04:59 mysql-bin.023871
-rw-r-----+ 1 mysql adm 104857800 5 juil. 05:00 mysql-bin.023872
-rw-r-----+ 1 mysql adm 104858020 5 juil. 05:02 mysql-bin.023873
-rw-r-----+ 1 mysql adm 104857821 5 juil. 05:03 mysql-bin.023874
-rw-r-----+ 1 mysql adm 104858041 5 juil. 05:04 mysql-bin.023875
-rw-r-----+ 1 mysql adm 104858087 5 juil. 05:05 mysql-bin.023876
-rw-r-----+ 1 mysql adm 104857813 5 juil. 05:07 mysql-bin.023877
-rw-r-----+ 1 mysql adm 104858534 5 juil. 05:08 mysql-bin.023878
-rw-r-----+ 1 mysql adm 104858040 5 juil. 05:09 mysql-bin.023879
-rw-r-----+ 1 mysql adm 104858533 5 juil. 05:10 mysql-bin.023880
-rw-r-----+ 1 mysql adm 104857889 5 juil. 05:12 mysql-bin.023881
-rw-r-----+ 1 mysql adm 104858325 5 juil. 05:13 mysql-bin.023882
-rw-r-----+ 1 mysql adm 104857753 5 juil. 05:14 mysql-bin.023883
-rw-r-----+ 1 mysql adm 104857784 5 juil. 05:15 mysql-bin.023884
-rw-r-----+ 1 mysql adm 104858442 5 juil. 05:17 mysql-bin.023885
-rw-r-----+ 1 mysql adm 104857743 5 juil. 05:18 mysql-bin.023886
-rw-r-----+ 1 mysql adm 104858112 5 juil. 05:19 mysql-bin.023887
-rw-r-----+ 1 mysql adm 104858152 5 juil. 05:20 mysql-bin.023888
-rw-r-----+ 1 mysql adm 7187316 5 juil. 06:37 mysql-bin.023889
-rw-r-----+ 1 mysql adm 52425956 5 juil. 10:52 mysql-bin.023890
-rw-r-----+ 1 mysql adm 27142886 5 juil. 11:54 mysql-bin.023891
-rw-rwx---+ 1 mysql adm 3520 5 juil. 10:52 mysql-bin.index
-rw-r-x---+ 1 mysql adm 1472 25 août 2011 mysql-bin.index.old
-rw-r-----+ 1 mysql adm 64136 5 juil. 11:54 mysql-slow.log
-rw-r-----+ 1 mysql adm 31622 5 juil. 06:35 mysql-slow.log.1.gz
-rw-r-----+ 1 mysql adm 36691 4 juil. 06:31 mysql-slow.log.2.gz
-rw-r-----+ 1 mysql adm 142618 3 juil. 06:36 mysql-slow.log.3.gz
-rw-r-----+ 1 mysql adm 33568 2 juil. 06:31 mysql-slow.log.4.gz
-rw-r-----+ 1 mysql adm 13625 1 juil. 06:31 mysql-slow.log.5.gz
-rw-r-----+ 1 mysql adm 25089 30 juin 06:31 mysql-slow.log.6.gz
-rw-r-----+ 1 mysql adm 26734 29 juin 06:31 mysql-slow.log.7.gz

#################################################################################


CONTENT OF /var/log/mysql/ ON THE SLAVE SERVER :

-rw-r-----+ 1 mysql adm 27613 5 juil. 11:20 mysql-slow.log
-rw-r-----+ 1 mysql adm 18757 5 juil. 06:32 mysql-slow.log.1.gz
-rw-r-----+ 1 mysql adm 7588 4 juil. 06:30 mysql-slow.log.2.gz
-rw-r-----+ 1 mysql adm 6266 3 juil. 06:36 mysql-slow.log.3.gz
-rw-r-----+ 1 mysql adm 6806 2 juil. 06:31 mysql-slow.log.4.gz
-rw-r-----+ 1 mysql adm 6218 1 juil. 06:37 mysql-slow.log.5.gz
-rw-r-----+ 1 mysql adm 4429 30 juin 06:31 mysql-slow.log.6.gz
-rw-r-----+ 1 mysql adm 10282 29 juin 06:34 mysql-slow.log.7.gz
-rw-r-----+ 1 mysql adm 188 5 juil. 05:07 relay.033080
-rw-r-----+ 1 mysql adm 104857694 5 juil. 05:08 relay.033081
-rw-r-----+ 1 mysql adm 1228 5 juil. 05:08 relay.033082
-rw-r-----+ 1 mysql adm 188 5 juil. 05:08 relay.033083
-rw-r-----+ 1 mysql adm 104858154 5 juil. 05:09 relay.033084
-rw-r-----+ 1 mysql adm 274 5 juil. 05:09 relay.033085
-rw-r-----+ 1 mysql adm 188 5 juil. 05:09 relay.033086
-rw-r-----+ 1 mysql adm 104857682 5 juil. 05:10 relay.033087
-rw-r-----+ 1 mysql adm 1239 5 juil. 05:10 relay.033088
-rw-r-----+ 1 mysql adm 188 5 juil. 05:10 relay.033089
-rw-r-----+ 1 mysql adm 104858003 5 juil. 05:12 relay.033090
-rw-r-----+ 1 mysql adm 274 5 juil. 05:12 relay.033091
-rw-r-----+ 1 mysql adm 188 5 juil. 05:12 relay.033092
-rw-r-----+ 1 mysql adm 104858439 5 juil. 05:13 relay.033093
-rw-r-----+ 1 mysql adm 274 5 juil. 05:13 relay.033094
-rw-r-----+ 1 mysql adm 188 5 juil. 05:13 relay.033095
-rw-r-----+ 1 mysql adm 104857867 5 juil. 05:14 relay.033096
-rw-r-----+ 1 mysql adm 274 5 juil. 05:14 relay.033097
-rw-r-----+ 1 mysql adm 188 5 juil. 05:14 relay.033098
-rw-r-----+ 1 mysql adm 104857898 5 juil. 05:15 relay.033099
-rw-r-----+ 1 mysql adm 274 5 juil. 05:15 relay.033100
-rw-r-----+ 1 mysql adm 188 5 juil. 05:15 relay.033101
-rw-r-----+ 1 mysql adm 104858556 5 juil. 05:17 relay.033102
-rw-r-----+ 1 mysql adm 274 5 juil. 05:17 relay.033103
-rw-r-----+ 1 mysql adm 188 5 juil. 05:17 relay.033104
-rw-r-----+ 1 mysql adm 104857857 5 juil. 05:18 relay.033105
-rw-r-----+ 1 mysql adm 274 5 juil. 05:18 relay.033106
-rw-r-----+ 1 mysql adm 188 5 juil. 05:18 relay.033107
-rw-r-----+ 1 mysql adm 104858226 5 juil. 05:19 relay.033108
-rw-r-----+ 1 mysql adm 274 5 juil. 05:19 relay.033109
-rw-r-----+ 1 mysql adm 188 5 juil. 05:19 relay.033110
-rw-r-----+ 1 mysql adm 104858266 5 juil. 05:20 relay.033111
-rw-r-----+ 1 mysql adm 274 5 juil. 05:20 relay.033112
-rw-r-----+ 1 mysql adm 188 5 juil. 05:20 relay.033113
-rw-r-----+ 1 mysql adm 7187457 5 juil. 06:37 relay.033114
-rw-r-----+ 1 mysql adm 188 5 juil. 06:37 relay.033115
-rw-r-----+ 1 mysql adm 22522 5 juil. 06:38 relay.033116
-rw-r-----+ 1 mysql adm 24382249 5 juil. 09:52 relay.033117
-rw-r-----+ 1 mysql adm 145 5 juil. 09:53 relay.033118
-rw-r-----+ 1 mysql adm 7271268 5 juil. 10:09 relay.033119
-rw-r-----+ 1 mysql adm 20750869 5 juil. 10:52 relay.033120
-rw-r-----+ 1 mysql adm 145 5 juil. 10:52 relay.033121
-rw-r-----+ 1 mysql adm 290 5 juil. 10:52 relay.033122
-rw-r-----+ 1 mysql adm 27727317 5 juil. 11:56 relay.033123
-rw-r-----+ 1 mysql adm 1232 5 juil. 11:48 relay.index
-rw-rwx---+ 1 mysql adm 65 5 juil. 11:56 relay.info

#################################################################################

Have you got any idea for this issue? This problem is blocking my service.

Thanks
Tom



Edited 4 time(s). Last edit at 07/05/2013 06:11AM by Tom L.

Options: ReplyQuote


Subject
Written By
Posted
[Replication] Seconds_Behind_Master increasing
July 05, 2013 04:04AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.