Let me explain the problem in detail,
We have a Google cloud SQL setup(1 master, 1 failover replica and a read replica). From the last two days, we are facing replication delays on both the replica instances which is constantly increasing.(It's up to 16h as I write).
On pulling the logs from the replica instances, we see slave SQL and slave I/O thread being killed very frequently,
2019-07-15T12:35:19.181804Z 1025650 [Note] Slave SQL thread for channel '' exiting, replication stopped in log 'mysql-bin.068343' at position 62535096
2019-07-15T12:35:19.184434Z 1025649 [Note] Slave I/O thread exiting for channel '', read up to log 'mysql-bin.068473', position 63572825
Please find the relevant slave status info and processlist output below.
show slave status;
Slave_IO_State: Waiting for master to send event
Master_Log_File: mysql-bin.068826
Read_Master_Log_Pos: 21806289
Relay_Log_File: relay-log.000025
Relay_Log_Pos: 16457199
Relay_Master_Log_File: mysql-bin.068600
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 52371
Slave_SQL_Running_State: System lock
Master_Retry_Count: 86400
show processlist;
| 1504576 | system user | | NULL | Connect | 3288 | Waiting for master to send event | NULL |
| 1504577 | system user | | NULL | Connect | 52623 | System lock | NULL
Slave related global variables.
| binlog_cache_size | 32768
| binlog_checksum | CRC32
| binlog_direct_non_transactional_updates | OFF
| binlog_error_action | ABORT_SERVER
| binlog_format | ROW
| binlog_group_commit_sync_delay | 0
| binlog_group_commit_sync_no_delay_count | 0
| binlog_gtid_simple_recovery | ON
| binlog_max_flush_queue_time | 0
| binlog_order_commits | ON
| binlog_row_image | FULL
| binlog_rows_query_log_events | OFF
| binlog_stmt_cache_size | 32768
| slave_allow_batching | OFF
| slave_checkpoint_group | 512
| slave_checkpoint_period | 300
| slave_compressed_protocol | OFF
| slave_exec_mode | STRICT
| slave_load_tmpdir | /mysql/tmp
| slave_max_allowed_packet | 1073741824
| slave_net_timeout | 30
| slave_parallel_type | DATABASE
| slave_parallel_workers | 0
| slave_pending_jobs_size_max | 16777216
| slave_preserve_commit_order | OFF
| slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN
| slave_skip_errors | OFF
| slave_sql_verify_checksum | ON
| slave_transaction_retries | 10
| slave_type_conversions |
All three instances have 8 vCPU cores, 30GB RAM, and around 550GB SSD Storage, and are cloud SQL MySQL 2nd generation instances(MySQL version 5.7). Master has a very stable CPU usage pattern at around 40% usage, and both the failover and read replica are at around 60% usage.
Does anybody have any idea why the Slave SQL thread is constantly in the "System lock" stage and not proceeding with the replication? Any pointers would be great!
SHOW FULL PROCESSLIST;
- this may give some clues. Do you useLOCK TABLES
(I hope not)? – Rick James Jul 16 '19 at 19:46LOCK TABLES
. – techiewickie Jul 18 '19 at 5:59