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)
