Comments on: How to identify and cure MySQL replication slave lag

By: Aris

Aris — Tue, 19 Mar 2019 21:01:44 +0000

In reply to Matt. Hi Matt, That also resolved my performance issue of SQL_THREAD, thank you.

By: Matt

Matt — Sat, 30 Dec 2017 03:00:29 +0000

I was running into massive lag in my Slave_SQL process for RBR, and it turned out to be because I had innodb_flush_log_at_trx_commit=1 on the slave. Setting this to innodb_flush_log_at_trx_commit=2 solved the problem, and I caught up in less than a minute.

By: Andy H

Andy H — Tue, 12 Dec 2017 10:32:53 +0000

In reply to Vinod T Veettil. Use a different tool. If you are using mysqldump as a backup, use percona xtrabackup or MySQL Enterprise backup instead.

By: prabhakerdeodixit

prabhakerdeodixit — Wed, 08 Nov 2017 01:32:20 +0000

Hi ,
is there any solution to reduce slave lag in RBR format(primary key is not there on tables) like mysql slave_rows_search_algorithms

By: Vinod T Veettil

Vinod T Veettil — Wed, 20 Sep 2017 15:53:29 +0000

I am facing an issue where I am running periodic mysqldump on the slave which is causing replication lag on slave. Can i avoid this kind of delay ?

By: Shikha Mehta

Shikha Mehta — Thu, 08 Jun 2017 06:55:40 +0000

Hi, i have a serious question that is hindering me to perform this automatic failover. I have deployed a master slave replication using gtid mode ON. And now i want test auto failover over my master and want to see how switchover performs but i am not able to do so because my slave is lagging behind of master i think as in show slave status it clearly shows.Due to this my mysqlfailover is failing as my slave status is not ‘OK’ as it is required.Can you please help me to get rid of this . I seriously need help.

By: Vinay

Vinay — Wed, 21 Dec 2016 14:40:26 +0000

Hi All ,

I am facing replication lag even I’m using multi thread replication could you please help to reduce the replication lag . it seems issue with SQL thread because I am getting SQL state Waiting for dependent transaction to commit

If someone help then it would be great help .

Slave_IO_State: Waiting for master to send event
Master_Host: 10.140.2.69
Master_User: repl
Master_Port: 3330
Connect_Retry: 60
Master_Log_File: mysql-bin.000360
Read_Master_Log_Pos: 745241233
Relay_Log_File: relay-log.000166
Relay_Log_Pos: 587394954
Relay_Master_Log_File: mysql-bin.000326
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: urapport_contact.%
Replicate_Wild_Ignore_Table: mysql.%,test.%,information_schema.%,performance_schema.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 587394741
Relay_Log_Space: 37252499844
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: 534243
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: 693330
Master_UUID: b753362d-abe6-11e6-8cbc-8cdcd4b0b20d
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Waiting for dependent transaction to commit
Master_Retry_Count: 86400

By: abdullah

abdullah — Thu, 12 May 2016 13:22:36 +0000

once enabling the log-slow-slave-statements it will log slow queries into slow-query-log file or slave server will create any log file for log-slow-slave-statements?.Thanks

By: Ashwini

Ashwini — Thu, 28 Jan 2016 10:16:57 +0000

Hi,

Thanks for you post..
I want to get all sql queries should log during my slave lag.

Am using below command :

/usr/bin/pt-stalk –function=/root/pt-plug.sh –variable=seconds_behind_master –threshold=5 –cycles=7 –notify-by-email=ashwini.a@gmail.com –log=/mnt/pt-stalk.log –collect –collect-tcpdump –pid=/root/pt-stalk.pid –daemonize

This variable does not takes values –collect –collect-tcpdump.
So where my sqldump will get log ? whats the default location for this logs ?

Thanks In Advance !!

By: Dmitry Balabka

Dmitry Balabka — Wed, 21 Oct 2015 08:11:16 +0000

Question according to binlog format. You did not specify multiple primary keys in query.
HAVING sum(if(column_key in (‘PRI’,’UNI’,’MUL’), 1,0)) =0;
Did this inadvertently omitted or specially?

By: Steve S

Steve S — Thu, 20 Aug 2015 20:17:22 +0000

Your code for finding tables without primary keys has an extra space in “information_schema .columns”. Also you said to disable binary logging on slave as it’s IO intensive, but on a slave it doesn’t write to the binary log unless log-slave-updates is turned on.

By: aftab

aftab — Fri, 16 May 2014 13:33:47 +0000

log_slow_slave_statements won’t help you log slow queries when using RBR.

By: Said Bakr

Said Bakr — Sat, 03 May 2014 06:50:30 +0000

Importing a large MySQL dump of 152 MB and more than 3 million rows of 450 MB on the database is a nightmare on My Windows 7 64 bit computer with 4 GB RAM!

By: Doug

Doug — Sat, 03 May 2014 02:23:14 +0000

Excellent breakdown, Muhammad.Thank you.

By: Simon Mudd

Simon Mudd — Fri, 02 May 2014 14:42:34 +0000

No mention of performance_schema or mysql -sys? sys.schema_table_statistics will point to the tables with most latency, and they are probably those you should focus on. I keep meaning to write a blog post with some examples.