0

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?

  • for check difference better compare not only config files, but as well - variables (including global). in some case - mysql not read value from my.cnf (for example by permission issues). check variables - help You identify difference. – a_vlad Mar 4 '16 at 22:23
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?

  • 1
    I set sync_binlog=0 and there was no noticeable change. I set innodb_flush_log_at_trx_commit=2 and now it appears to be replicating at a reasonable speed. Thank you. To answer your remaining questions: I don't really know if there are any differences in the OS config, as I didn't set up the existing server. No clients are hitting the new slave for reads yet; I didn't want to put it in production without it replicating properly. 32 GB of RAM (on both). There wasn't anything in the slowlog with log_slow_slave_statements. Seconds_Behind_Master was mostly increasing, with occasional drops. – Dr. McKay Mar 4 '16 at 22:27
  • run SHOW VARIABLES; on both and compare – a_vlad Mar 4 '16 at 22:34
  • Your observations are consistent with iflatc having been 1 in the new Slave, but 0 or 2 on the Master and old Slaves. That setting leads to an extra write at every COMMIT, which could lead to getting "behind" on very busy systems. – Rick James Mar 4 '16 at 22:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.