Problem with MySql Replication

  • Filter
  • Time
  • Show
Clear All
new posts

  • Problem with MySql Replication

    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...!!!

  • #2
    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/...low-query-log/ and check this too for possible causes of replication delay and how to fix it here http://www.mysqlperformanceblog.com/...ion-slave-lag/

    2) Make sure you have log-slave-updates enable on Server B in order to replicate DB1 to server C.


    • #3
      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...???


      • #4
        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.


        • #5
          Yes, after enabling log-slave-updates on server B the transactions started to stream to final server C.
          For the update issue i found out an option low-priority-updates was set to 1, After commenting it and restarting the server all the transactions went through fine and as if now there is no lag on either of the slave.

          I guess this option was giving low priority to the update statements.

          Thank you so much for your help....!!

          Jazak Allah


          • #6
            Also I changed the Engine of Few of my main tables to InnoDB that seems to help with the locking.