Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

At my environment we had slave replication with mysql server version 5.1.47 enterprise version.When ever there is huge updates from master, slave is lagging behind the master. What is the best possible way to eradicate it.

In certain period there will be a huge updates, by observing bin logs updates i could find 30 MB per minute from master which leads to slave replication delays.

  Slave :

   mysql> show processlist; show slave status\G
  +--------+----------------+-------------------+--------------+---------+---------+----------------------------------+-----------------------------------------------------------------------------------------------------+
  | Id     | User           | Host              | db           | Command | Time    | State                            |Info                                                                                                  | 
  +--------+----------------+-------------------+--------------+---------+---------+----------------------------------+------------------------------------------------------------------------------------------------------+
  |      6 | system user    |                   | NULL         | Connect | 9548580 | Waiting for master to send event | NULL 
  |      7 | system user    |                   | cs_prod | Connect |    2150 | update                           | INSERT INTO prod_last_item(prod1_id, prod2_id, prod_item, prod_no) VALUES (15055876, 2372515          |  
  | 495447 | usr_replica    | 192.168.10.100:47020 | NULL      | Query   |       0 | NULL                             | show processlist|
  | 496035 | master_mon     | 192.168.10.111:42175 | mysql     | Sleep   |       6 |                                  |    NULL                                                                                                 |
  +--------+----------------+-------------------+--------------+---------+---------+----------------------------------+------------------------------------------------------------------------------------------------------+



        *************************** 1. row ***************************
           Slave_IO_State: Waiting for master to send event
              Master_Host: 192.168.10.111
              Master_User: replication
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: dbfr-bin.009808
      Read_Master_Log_Pos: 657767101
           Relay_Log_File: dbfr-relay.024833
            Relay_Log_Pos: 643748273
    Relay_Master_Log_File: dbfr-bin.009807
         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: 643748129
          Relay_Log_Space: 1731510298
          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: 2150
    Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 0
            Last_IO_Error: 
           Last_SQL_Errno: 0
           Last_SQL_Error: 
     1 row in set (0.00 sec)

   mysql> select (2150)/60);
            +-----------+
            | (2150)/60 |
            +-----------+
            | 35.8333   |
            +-----------+
            1 row in set (0.00 sec)


        From Master Data updates :

      mysql> select now();show master logs;system sleep 60;show master logs;
             +---------------------+
             | now()               |
             +---------------------+
             | 2011-12-26 15:27:53 |
             +---------------------+
             1 row in set (0.00 sec)

             +-----------------+------------+
             | Log_name        | File_size  |
             +-----------------+------------+
             | db-bin.009803   | 1073742199 |
             | db-bin.009804   | 1073742313 |
             | db-bin.009805   | 1073742225 |
             | db-bin.009806   | 1073741957 |
             | db-bin.009807   | 1073742271 |
             | db-bin.009808   |  383125494 |
             +-----------------+------------+
             6 rows in set (0.00 sec)

            +-----------------+------------+
            | Log_name        | File_size  |
            +-----------------+------------+
            | db-bin.009803   | 1073742199 |
            | db-bin.009804   | 1073742313 |
            | db-bin.009805   | 1073742225 |
            | db-bin.009806   | 1073741957 |
            | db-bin.009807   | 1073742271 |
            | db-bin.009808   |  413567052 |
            +-----------------+------------+
            6 rows in set (0.01 sec)

            mysql> select (383125494)/1024/1024;
              +-----------------------+
              | (383125494)/1024/1024 |
              +-----------------------+
              | 365.37694359          |
              +-----------------------+
              1 row in set (0.01 sec)

             mysql> select (413567052)/1024/1024;
                    +-----------------------+
                    | (413567052)/1024/1024 |
                    +-----------------------+
                    | 394.40827560          |
                    +-----------------------+
                    1 row in set (0.00 sec)

              mysql> select (394.40827560-365.37694359);
                   +-----------------------------+
                   | (394.40827560-365.37694359) |
                   +-----------------------------+
                   | 29.03133201                 |
                   +-----------------------------+
                   1 row in set (0.00 sec)
share|improve this question
    
If I were having the same problem, I would check to make sure the network connection wasn't being overloaded or that there wasn't someother non-mysql problem. If you were on linux, you could use sysstat to measure your system's behavior every time a large update occured. Jet Profiler could also be useful in this case. –  Craig Efrein Dec 27 '11 at 6:13
    
Thank you!! Craig Efrein I figured it out. There was a bulk reads and writes. –  Gopinath Dec 27 '11 at 22:17
    
Interpretation guess: "There were a LOT of BLOCK read/writes in iostat, meaning a ton of disk activity, especially writes." Okay, good luck with that. O_o –  IcarusNM Dec 27 '11 at 23:33
    
Thank you icarusNM +1 –  Gopinath Jan 22 '12 at 20:22
add comment

2 Answers

There's a stack of system related questions here. We don't know what the hardware looks like for your master or slave. Maybe you're lacking RAM on the slave, or any number of other differences.

The master is probably receiving bursts of updates in parallel from many client connections, and for whatever reason, the slave can't keep up since all replication is done in serial.

If you have a lot of InnoDB activity on the master, you can probably disable InnoDB on the slave and gain some speed that way. That option and a few others are given here:

16.4.4.7: How can I use replication to improve performance of my system? http://dev.mysql.com/doc/refman/5.0/en/replication-faq.html#qandaitem-16-4-4-1-7

Or there could be configuration tweaks in my.cnf that could help you. I'd start by looking at your memory usage on the slave, and looking up buffer and memory related options for innodb and/or myisam depending on which of those engines you're using (or others).

You can also look through your binlogs with the mysqlbinlog tool to see what database and tables have the most activity. Then start working with your application developers and start chipping away at the problem.

Also check your mysqld error log on the slave. There might be clues in there.

share|improve this answer
    
mysql> system iostat; Linux 2.6.9-89.ELsmp (prodsys) 12/27/2011 avg-cpu: %user %nice %sys %iowait %idle 2.57 0.00 0.77 0.00 96.66 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 11.49 0.35 814.64 1835374 4250499336 –  Gopinath Dec 27 '11 at 22:23
add comment

Try ROW based replication. It reduces slave load significantly. But keep in mind that your bin-log files will grow a lot faster than with Statement based replication

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.