Seeing replica lag

MySQL & MariaDB
1

Hi Team,

I have observed while checking the replica lag, my replica is running with 19482sec lag and in show processlist i can see below the replica user was active form the same time i,e 19482sec it is same as my replica lag

Is there any relation b/w this?

±-------±-------------±--------------------±--------±------------±--------±--------------------------------------------------------------±-----------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
±-------±-------------±--------------------±--------±------------±--------±--------------------------------------------------------------±-----------------------------------------------------------------------------------------------------+
| 23308 | system user | | NULL | Connect | 4833169 | Waiting for master to send event | NULL |
| 23309 | system user | | wallet4 | Connect | 19482 | update | insert into table |
|
| 206947 | database1 | 172.31.22.134:54466 | NULL | Binlog Dump | 5470 | Master has sent all binlog to slave; waiting for more updates | NULL | |
±-------±-------------±--------------------±--------±------------±--------±--------------------------------------------------------------±-----------------------------------------------------------------------------------------------------+

1 Like
2

Hi @devaraj , thanks for posting to the Percona forums!

Replica lag is generally evaluated from SHOW REPLICA STATUS\G and looking at the Seconds_behind variable. This indicates “how many seconds ago this statement executed on the Primary”

You may consider changing to binlog_format=ROW in order to apply replica events more quickly.
https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_format

1 Like
3

Hi @Michael_Coburn

Thanks for the reply, binlog_format is row on master and by seeing SHOW REPLICA STATUS\G i can see the replica lag is 19482 sec, but one thing im not understanding here from show processlist on replica show the thread active from same number of time is there any realation between the thread and the replica lag

1 Like
4

Hi, well not, they are different types but SQL thread Is causing the replication lag, improving your SQL queries AND checking what Is the reason of this stuck. You could check if there are missing constraints on your tables

1 Like