0

I am having an issue where the slave replication keeps falling behind. The master is running Mysql 5.5.40, while the slave is running Mariadb 10.1.13

The binlogs do not apply fast enough on the slave, when I reset the slave It starts falling behind immediately( the Relay_Master_Log_File does not move as fast as the Master_Log_File) while "Seconds_Behind_Master" remains 0

From the slave

MariaDB [fester_audit]> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: x.x.x.x
                  Master_User: repl_s3-db-02
                  Master_Port: 3321
                Connect_Retry: 60
              Master_Log_File: s4-banking-db-02_bin_.046125
          Read_Master_Log_Pos: 211955563
               Relay_Log_File: mysql21-relay-bin.000088
                Relay_Log_Pos: 643960646
        Relay_Master_Log_File: s4-banking-db-02_bin_.046106
             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: 643960346
              Relay_Log_Space: 20613783012
              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: 0
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: 21
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative

What does worry me is the log format. From the master:

mysql> SHOW GLOBAL VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+

While the slave shows

MariaDB [fester_audit]> SHOW GLOBAL VARIABLES LIKE 'binlog_format';

    +---------------+-----------+
    | Variable_name | Value     |
    +---------------+-----------+
    | binlog_format | STATEMENT |
    +---------------+-----------+

I have enabled parallel_threads on the MariaDb slave, but this did not help

MariaDB [fester_audit]> SELECT @@slave_parallel_threads;
+--------------------------+
| @@slave_parallel_threads |
+--------------------------+
|                        4 |
+--------------------------+

My config file for the MariaDB slave

[mysqld21]
pid-file                        = /data/3321/mysql21/mysql21.pid
port                            = 3321
extra_port                      = 33321
socket                          = /data/3321/mysql21/mysql21.sock
log-bin                         = /data/3321/logs/repl/binary_
binlog_format                   = STATEMENT
log-warnings                    = 1
#query_cache_type                = 1
#query_cache_size                = 1024M
#query_alloc_block_size          = 32768
#query_cache_limit               = 128M
#query_cache_min_res_unit        = 8192
#query_prealloc_size             = 32768
open_files_limit                = 16384
max_connections                 = 4000
max_allowed_packet              = 64M
tmpdir                          = /data/global_tmp/3321
datadir                         = /data/3321/mysql21
user                            = mysql
log-error                       = /var/log/mysqld21.log
relay-log                       = mysql21-relay-bin
#relay-log                       = /data/3321/mysql21/mysql21-relay-bin
#relay-log                       = /data/3321/logs/repl/slave_bin_logs/mysql21-relay-bin
server-id                       = 20
default-storage-engine          = MYISAM
log-slave-updates
slave_parallel_threads          = 4
#slave_parallel_mode            = aggressive
expire-logs-days                = 1
auto_increment_offset           = 1
auto_increment_increment        = 1

thread_handling                 = pool-of-threads
thread_pool_size                = 128
thread_pool_max_threads         = 2000

innodb_file_per_table
sync_binlog                     = 0

Any advise would be appreciated

  • Assuming you are using InnoDB, what are the values from SELECT @@innodb_flush_log_at_trx_commit; on master and replica? – Michael - sqlbot May 29 '18 at 0:32
  • Hi. On Master: +----------------------------------+ | @@innodb_flush_log_at_trx_commit | +----------------------------------+ | 1 | +----------------------------------+ – rezizter May 30 '18 at 12:45
  • On slave : | @@innodb_flush_log_at_trx_commit | +----------------------------------+ | 1 – rezizter May 30 '18 at 12:45
  • "starts falling behind" -- Does it stay about a constant amount behind? – Rick James Jun 3 '18 at 2:26
  • Don't "reset the slave". – Rick James Jun 3 '18 at 2:26

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.