Not the answer you need?
Register and ask your own question!

Problem with MySql Replication

Shafat HusainShafat Husain EntrantCurrent User Role Beginner
I have Multi Server Replication setup which is described below:
1. Database DB1 is hosted on Server A (Transactional MyISAM DB)
2. Database DB2 and DB3 are hosted on Server B (Transactional MyISAM DB)
3. DB1 is replicated on Server B
4. 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 :

1. 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.
2. Data for DB1 is not being replicated to Server C though it is coming on Server B.

Please help...!!!

Comments

  • mirfanmirfan Database Administrator Inactive User Role Beginner
    1) what version of MySQL you are using ? there can be many causes, as you using MyISAM tables so table level locking comes into play, slow SQL you may want to check my posts how to enable slow query logs and to analyze it here http://www.mysqlperformanceblog.com/2014/03/14/tools-and-tips-for-analysis-of-mysqls-slow-query-log/ and check this too for possible causes of replication delay and how to fix it here http://www.mysqlperformanceblog.com/2014/05/02/how-to-identify-and-cure-mysql-replication-slave-lag/

    2) Make sure you have log-slave-updates enable on Server B in order to replicate DB1 to server C.
  • Shafat HusainShafat Husain Entrant Current User Role Beginner
    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
    Status: locked

    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.

    ​Any ideas...???
  • mirfanmirfan Database Administrator Inactive User Role Beginner
    I hope after enabling log-slave-updates replicaion should be stream to server C.
    For update query as you can see,

    Status:locked

    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.
  • Shafat HusainShafat Husain Entrant Current User Role Beginner
  • Shafat HusainShafat Husain Entrant Current User Role Beginner
    Also I changed the Engine of Few of my main tables to InnoDB that seems to help with the locking.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.