Slave (SQL thread) replicates very slowly

Question

See this video: https://mckay.media/Yxjp2

I have two MySQL replication slaves which are replicating from the same master. Both slaves are running on identical hardware. Each has its data directory stored on an SSD, which has comparable throughput. There is no I/O lag as you can see, only SQL lag.

The terminal on the left is the new slave. The one on the right is the existing slave from which the one on the left was cloned. I cloned them by copying all configs (changing server-id), and rsyncing the MySQL data directory.

I can't for the life of me figure out why the new slave is replicating so slowly. The video was taken during an off-peak time. During peak time, the new slave is just unable to keep up at all.

I don't know 100% what was done when the old slave was set up. The individual who set it up is no longer available. The MySQL configs are 100% identical besides the server-id.

I'm running Debian 8 on both slaves. The old slave has MySQL v5.5.43-0+deb8u1-log. The new one has MySQL v5.5.47-0+deb8u1-log. There is nothing noteworthy in the slowlog for the new slave.

I did notice that the new slave has a much larger %iowait in iostat, but I'm unsure what to make of this, since both are running on SSDs. sda is an HDD, and sdb is the SSD where the MySQL data directory is stored (on both machines).

Old slave:

Linux 3.16.0-4-amd64 (mysql-cluster-5)  03/03/2016      _x86_64_        (8 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          34.11    0.00    0.93    0.22    0.00   64.74

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sda               3.85         0.25      1351.60    5234043 28302154992
sdb              73.54         2.41      1684.24   50383141 35267640148

New slave:

Linux 3.16.0-4-amd64 (mysql-cluster-6)  03/03/2016      _x86_64_        (8 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.53    0.00    0.23    8.75    0.00   89.49

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sda               2.49        23.51        25.60     186982     203664
sdb             145.48       581.01      5359.82    4621437   42632848

Here is the new slave's my.cnf, which is identical to the old one with the exception of server-id:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /ssd/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
skip-name-resolve

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8

myisam-recover         = BACKUP
max_connections        = 10000

innodb_buffer_pool_size = 20G
innodb_thread_concurrency=8
innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_io_capacity=500
query_cache_type        = 1
query_cache_limit       = 64M
query_cache_size        = 512M
sort_buffer_size=74M
read_rnd_buffer_size=16M
tmp_table_size=1G
max_heap_table_size=1G

log_error = /var/log/mysql/error.log

slow_query_log_file = /var/log/mysql/mysql-slow.log
slow_query_log      = 1
long_query_time = 2
log_queries_not_using_indexes

server-id               = 11
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/

There is nothing notable in /etc/mysql/conf.d/.

All of my tables are InnoDB.

Why is it replicating so slowly?

Answer 1

(Too many questions for 'comments'. Anyway something here might help locate the naughty.)

Backup occurring?

sync_binlog - Probably 0 is OK for Slave.

innodb_flush_log_at_trx_commit - may as well be 2 on a Slave.

Differences in OS config, especially relating to I/O? Swappiness?

Are clients hitting both Slaves? In the same proportion?

How much RAM?

query_cache_size = 512M -- This is bad for any system. Limit it to something like 50M.

tmp_table_size=1G -- This is dangerous. Some complex queries could chew up RAM and cause swapping, which is really bad for performance, especially I/O. Keep that down to, say, 1% of RAM.

You have the slowlog turned on -- Look for clues there. If you don't find anything, then turn ON log_slow_slave_statements also.

Is Seconds_behind_master increasing or oscillating?