0

I have a very large database in RDS - just under 3 TB. I have set up a replication slave in an EC2 instance (t2.xlarge with io1 disk volume for data). OS is Debian 9, DB is MySQL 5.6.

The problem is that replication is lagging slowly - something like 20 - 25 sec per minute clock time, and I can't spot what the problem is.

Show slave status mostly looks like this - it seems to be waiting for events from the master much of the time:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.14.6.241
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-changelog.063140
          Read_Master_Log_Pos: 6919049
               Relay_Log_File: rgseu-prod-mysql-bis-relay-bin.000394
                Relay_Log_Pos: 109648736
        Relay_Master_Log_File: mysql-bin-changelog.061073
             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: 109648563
              Relay_Log_Space: 277441601381
              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: 558005
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: 1571602707
                  Master_UUID: 67d7b2b0-e452-398f-a66b-b751619ede66
             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
1 row in set (0.00 sec)

Top seems to indicate that mysqld isn't doing too much, %CPU typically 20 - 20:

top - 06:44:04 up 7 days, 18:47,  2 users,  load average: 1.07, 1.10, 1.09
Tasks: 149 total,   1 running, 148 sleeping,   0 stopped,   0 zombie
%Cpu(s):  3.8 us,  0.6 sy,  0.0 ni, 85.4 id,  9.6 wa,  0.0 hi,  0.0 si,  0.6 st
KiB Mem : 32950424 total,   256180 free,   786404 used, 31907840 buff/cache
KiB Swap:        0 total,        0 free,        0 used. 31679664 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
27441 mysql     20   0 2130716 664044  16356 S  31.2  2.0 214:25.12 mysqld
27335 root      20   0       0      0      0 D   3.3  0.0  17:33.04 jbd2/xvdh-8
28901 jan.and+  20   0   44904   3784   3104 R   0.3  0.0   0:00.04 top
...

iotop doesn't seem to indicate enormous pressure on the disk:

Total DISK READ :     486.50 K/s | Total DISK WRITE :      10.70 M/s
Actual DISK READ:     486.50 K/s | Actual DISK WRITE:      15.14 M/s
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
27477 be/4 mysql     470.55 K/s    3.45 M/s  0.00 % 36.52 % mysqld --basedir=/usr --datadir=/var/lib/mysql --p~--pid-file=/var/lib/mysql/rgseu-prod-mysql-bis.pid
27335 be/3 root        0.00 B/s   33.90 K/s  0.00 % 35.84 % [jbd2/xvdh-8]
27449 be/4 mysql       0.00 B/s    0.00 B/s  0.00 %  1.80 % mysqld --basedir=/usr --datadir=/var/lib/mysql --p~--pid-file=/var/lib/mysql/rgseu-prod-mysql-bis.pid
27451 be/4 mysql       0.00 B/s    0.00 B/s  0.00 %  1.05 % mysqld --basedir=/usr --datadir=/var/lib/mysql --p~--pid-file=/var/lib/mysql/rgseu-prod-mysql-bis.pid
27476 be/4 mysql       0.00 B/s  323.40 K/s  0.00 %  1.03 % mysqld --basedir=/usr --datadir=/var/lib/mysql --p~--pid-file=/var/lib/mysql/rgseu-prod-mysql-bis.pid
27450 be/4 mysql       0.00 B/s    0.00 B/s  0.00 %  0.85 % mysqld --basedir=/usr --datadir=/var/lib/mysql --p~--pid-file=/var/lib/mysql/rgseu-prod-mysql-bis.pid
27469 be/4 mysql       0.00 B/s    4.26 M/s  0.00 %  0.46 % mysqld --basedir=/usr --datadir=/var/lib/mysql --p~--pid-file=/var/lib/mysql/rgseu-prod-mysql-bis.pid
27452 be/4 mysql       0.00 B/s    0.00 B/s  0.00 %  0.29 % mysqld --basedir=/usr --datadir=/var/lib/mysql --p~--pid-file=/var/lib/mysql/rgseu-prod-mysql-bis.pid
27468 be/4 mysql       0.00 B/s    2.62 M/s  0.00 %  0.25 % mysqld --basedir=/usr --datadir=/var/lib/mysql --p~--pid-file=/var/lib/mysql/rgseu-prod-mysql-bis.pid
27457 be/4 mysql      15.95 K/s   13.56 K/s  0.00 %  0.13 % mysqld --basedir=/usr --datadir=/var/lib/mysql --p~--pid-file=/var/lib/mysql/rgseu-prod-mysql-bis.pid
27471 be/4 mysql       0.00 B/s   13.96 K/s  0.00 %  0.10 % mysqld --basedir=/usr --datadir=/var/lib/mysql --p~--pid-file=/var/lib/mysql/rgseu-prod-mysql-bis.pid
    1 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % init
    2 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kthreadd]
    3 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [ksoftirqd/0]

nethogs shows fairly moderate traffic, I think:

NetHogs version 0.8.5-2

    PID USER     PROGRAM                                                     DEV        SENT      RECEIVED
  27441 mysql    /usr/sbin/mysqld                                            eth0        5.479     218.855 KB/sec
      ? root     10.14.3.66:968-10.14.1.15:2049                                          0.000       0.000 KB/sec
  28892 jan.an.. sshd: jan.andersen@pts/1                                    eth0        0.000       0.000 KB/sec
      ? root     unknown TCP                                                             0.000       0.000 KB/sec

  TOTAL                                                                                  5.479     218.855 KB/sec

So, in short: what is wrong that I haven't spotted?

It is quite urgent that I solve this soon; it took more than two days to take a backup of the master, using mysqldump (the only option apparently), and further more than 3 days to restore the backup into the new slave - and the maximum time RDS will keep binlogs is 7 days, so I'm running out of time. I'd really like to avoid doing that over again, so please, if you have any insight, let me know.

7
  • what show processlist on slave shows ? Do you have scripts taking data from slave ? Sep 28 '21 at 8:46
  • @ErgestBasha This is a compltely new slave - the only processes are the replication thread and the session that submits 'show processlist'. Nothing else uses it atm.
    – j4nd3r53n
    Sep 28 '21 at 10:03
  • Do you have heavy read or write on master server ? Sep 28 '21 at 10:17
  • Well, there's a lot of transactions coming in - the heavy reads use a r/o instance in the RDS cluster. But the problem seems to be on the slave: it doesn't get through the relay-logs fast enough.
    – j4nd3r53n
    Sep 28 '21 at 10:31
  • MAster & Slave resources are same ( Os & DB level) ? Sep 28 '21 at 10:51

Your Answer

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

Browse other questions tagged or ask your own question.