I have Multi Server Replication setup which is described below:
- Database DB1 is hosted on Server A (Transactional MyISAM DB)
- Database DB2 and DB3 are hosted on Server B (Transactional MyISAM DB)
- DB1 is replicated on Server B
- DB1, DB2 and DB3 are replicated from Server B to Server C which is used for reporting purposes.
I am facing the the following issues :
- There is a huge lag in replication from Server A to Server B. Update statements are taking a lot of time to execute at Server B.
- Data for DB1 is not being replicated to Server C though it is coming on Server B.
Thanks for the prompt response…!!
I have made the changes in my.cnf of Server B and restarted it.
The problem is still with the update statements. I ran show processlist and found out a simple update query coming from master to this slave which is taking huge time.
Query : UPDATE ticket SET escalation_response_time = ‘0’ WHERE id = ‘1990757’
Time taken till now : 14660
this is a very simple update statement i am unable to understand why it is taking so much time.
there are almost 3,50000 records in this table.
I hope after enabling log-slave-updates replicaion should be stream to server C.
For update query as you can see,
Which means some other query preventing this query to execute as it holding the lock. So, this query is locked waiting for lock to acquire quite long. I would suggest to inspect processlist to identify what query is blocked this update. This clearly suggests that some other query is really slow taking more than 14660 seconds.
Also I changed the Engine of Few of my main tables to InnoDB that seems to help with the locking.