Mariadb 10.1.13 – log positions similar but “seconds behind master” is behind

mariadbmaster-slave-replication

The setup I have is a master/slave replication, both running Mariadb 10.1.13.

On the slave I noticed that the Seconds_Behind_Master parameter is showing a delay due to some form of lagging, but the values for Master_Log_File and Relay_Master_Log_File are the same.

I have checked that the server IDs are different, however both have the same gtid_domain_id.

               Slave_IO_State: Waiting for master to send event
                  Master_Host: X.X.X.X
                  Master_User: repl
                  Master_Port: 3321
                Connect_Retry: 60
              Master_Log_File: binary_.005825
          Read_Master_Log_Pos: 140625151
               Relay_Log_File: mysql20-relay-bin.000145
                Relay_Log_Pos: 138885256
        Relay_Master_Log_File: binary_.005825
             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: 138884970
              Relay_Log_Space: 1214370105
              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: 10056
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: Slave_Pos
                  Gtid_IO_Pos: 0-20-34183339144
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative

Any advice on solving the lag between my master/slave setup would be appreciated.
What could the root cause be?

Best Answer

140625151 and 138884970 are not that close. But 10056 seconds is a nearly 3 hours. What query is the Slave executing? Maybe a long-running ALTER?

There have been cases where Seconds_behind_master bounces between 0 and some large value; is that happening?