MySQL Forums
Forum List  »  Replication

slave getting futher and futher behind
Posted by: R H
Date: October 05, 2015 10:36AM

Hi,

I am new to replication so sorry if I am asking something obvious, but I couldnt find any good answers on google for this specific case.

I have set up 5.6 GTID based replication, and tested it by creating a random db and it did replicate over to the slave.

So I checked the next day if everything was ok, but it doesnt seem that way:

Here is my SHOW SLAVE STATUS:
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: System lock
Master_Host: xxxxxxxxxxxxxxxxxxxxxxxxx
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000141
Read_Master_Log_Pos: 149331857
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 8450030
Relay_Master_Log_File: mysqld-bin.000141
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: 8449818
Relay_Log_Space: 149334773
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: 48048
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 7846a847-62c7-11e5-91a6-e06995de432e
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Reading event from the relay log
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 7846a847-62c7-11e5-91a6-e06995de432e:4757140-4994235
Executed_Gtid_Set: 7846a847-62c7-11e5-91a6-e06995de432e:1-4770482
Auto_Position: 1


Here's the processlist:

+------+-------------+-----------+------+---------+--------+---------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-------------+-----------+------+---------+--------+---------------------------------------+------------------+
| 4 | system user | | NULL | Connect | 190657 | System lock | NULL |
| 5 | system user | | NULL | Connect | 0 | Reading event from the relay log | NULL |
| 6 | system user | | NULL | Connect | 48727 | Waiting for an event from Coordinator | NULL |
| 7 | system user | | NULL | Connect | 48579 | Waiting for an event from Coordinator | NULL |
| 1877 | root | localhost | NULL | Query | 0 | init | show processlist |
+------+-------------+-----------+------+---------+--------+---------------------------------------+------------------+
5 rows in set (0.00 sec)


and the show engine innodb status

------------
TRANSACTIONS
------------
Trx id counter 16569863
Purge done for trx's n:o < 16569863 undo n:o < 0 state: running but idle
History list length 2636
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 1877, OS thread handle 0x7f14cc77e700, query id 42655 localhost root init
show engine innodb status
---TRANSACTION 16569861, not started flushing log
mysql tables in use 1, locked 1
MySQL thread id 4, OS thread handle 0x7f14cc74d700, query id 0 System lock
---TRANSACTION 0, not started
MySQL thread id 6, OS thread handle 0x7f14cc2c7700, query id 3989 Waiting for an event from Coordinator
---TRANSACTION 16569532, not started
MySQL thread id 5, OS thread handle 0x7f14cc71c700, query id 0 Reading event from the relay log
---TRANSACTION 16569840, ACTIVE 0 sec
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 0x7f14cc296700, query id 42654 Waiting for an event from Coordinator


Basically what's happening is the slave is getting more and more behind and the relay log is just growing, even though the executed GTID seems to progress slowly the second behind master is growing rapidly.

The master and slave box has identical hardware.

Can anyone point me to the direction of how to solve this?
Your help would be much appreciated

Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
slave getting futher and futher behind
3516
R H
October 05, 2015 10:36AM


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.