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).
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
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?