Hi Venkatesh
Thanks for your valuable inputs provided.
Binlog_format was changed by previous DBA's to STATEMENT , from default ROW
1) What is the bulk insert command that you executed on Master ? How many inserts it is doing?
Response : Number of record inserted in master is 500K - 600K records (Pushed from application)
2) Are you sure that slave halted ? Or is it that it is taking time to apply the bulk insert? If it is the bulk insert/batch operation looks slow on Slave , please consider setting slave_rows_search_algorithms depends on your requirement. More details about the variable can be found at
http://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#option_mysqld_slave-rows-search-algorithms
Response:
Yes Server is halted. If i provide stop slave command, its just hanging at that point and i have to kill the mysql. But if i kill the mysql and start the slave, it starts catching up the lags. Value of slave_rows_search_algorithms is set to TABLE_SCAN,INDEX_SCAN. Please let me know if this needs to be changed.
To confirm you that slave is halt Exec_Master_Log_Pos, Relay_Log_Space is not changing and same insert remains for long time without any progress
3) Are there any local transactions on Slave that could have locked the table and SQL thread is waiting for that lock ? What is the SQL slave state in 'show slave status' command? Please give the 'show slave status' output on Slave for me to give more information on the same.
Response:
There is no local transactions on slave, it accepts only from master. Whenever huge inserts come to one particular table, it gets halted. Also insert to same table happens if it less number of record.
4) Are you able to reproduce the issue every time ? If so, please raise a bug in bugs.mysql.com with all the possible information to reproduce the issue,
We will look into it.
Response:
Yes , this issue happens frequently. Will capture all datas on next occurence and will raise a bug.
Below are the few variable values in my.cnf, request you to provide any recommendation
bulk_insert_buffer_size = 512M changed from 256M to 512M
innodb_default_row_format = COMPACT
innodb_thread_sleep_delay = 10000
innodb_file_per_table
innodb_buffer_pool_size = 50G
innodb_doublewrite=OFF
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout = 28800
innodb_log_file_size = 500M
binlog_format = STATEMENT
Regards
Karthik R