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)
Best Answer
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.cnfthat 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
mysqlbinlogtool 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.