Unusually low insert rate on MySQL Slaves
0
votes
1
answer
170
views
I have 4 MySQL nodes replicating like this:
M1 - S1
|
M2 - S2
Only the M1 master is writing, the hardware is similar (the slaves are a bit beefier), they all run Percona 5.7.
The trouble is that when M1 has a lot of inserts in a small time frame, the slaves lag behind. While M1 and M2 ar able to insert at a rate of thousands per second, S2 seems limited ad 120 inserts/s. S1 varies between 70 and 180 but not more.
Here's the slave status on S2 during this time:
mysql> show slave status\G;
Slave_IO_State: Waiting for master to send event
Master_Host: *******
Master_User: *******
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000799
Read_Master_Log_Pos: 677668480
Relay_Log_File: db2-relay-bin.000101
Relay_Log_Pos: 568744098
Relay_Master_Log_File: mysql-bin.000799
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: 568743885
Relay_Log_Space: 677668945
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: 1118
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 0b400f69-3459-16e6-a835-14feb5d6c592
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Reading event from the relay log
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
And here's the general mysql config of all the nodes:
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION
# MyISAM
key-buffer-size = 32M
# SAFETY
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
# DATA STORAGE
datadir = /var/lib/mysql/
# BINARY LOGGING modified 4 slave replication
server-id = 3
binlog_do_db = ********
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1
binlog_format = ROW
relay_log_info_repository=TABLE
relay_log_recovery = ON
# CACHES AND LIMITS
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 2000
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 1024
table-open-cache = 2048
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 100M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 90G
innodb_buffer_pool_instances = 48
# LOGGING
log-error = /var/lib/mysql/mysql-error.log
slow-query-log = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log
Also, checking on the processes on S2 i only get the:
Waiting for master to send event
Reading event from the relay log
Any help or idea to get to the bottom of this would be highly appreciated.
Update: here's a visual
Update 2: it's not just the inserts, it's everything except selects:


Asked by Bobby Tables
(101 rep)
Feb 25, 2020, 08:47 AM
Last activity: Jul 5, 2025, 10:03 AM
Last activity: Jul 5, 2025, 10:03 AM