Read Replica Stalled/Delayed

[Simon DuPlessis]

Hi,

My CloudSQL read replica appears to have stalled yesterday and is not recovering. I have tried stopping and starting it, I have also done a manual backup (in the GCP CloudSQL console) and then setup a new read replica but that one is also not keeping up with the master and there is a constant straight line growth on the replication delay graph.

When I run SHOW SLAVE STATUS on the replica instance it all looks ok

# Slave_IO_State	Waiting for master to send event
Master_Host	35.189.67.3
Master_User	cloudsqlreplica
Master_Port	3306
Connect_Retry	60
Master_Log_File	mysql-bin.097078
Read_Master_Log_Pos	42673946
Relay_Log_File	relay-log.000002
Relay_Log_Pos	13383109
Relay_Master_Log_File	mysql-bin.096559
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	87918742
Relay_Log_Space	55226562178
Until_Condition	None
Until_Log_File
Until_Log_Pos	0
Master_SSL_Allowed	Yes
Master_SSL_CA_File	master_server_ca.pem
Master_SSL_CA_Path	/mysql/datadir
Master_SSL_Cert	replica_cert.pem
Master_SSL_Cipher
Master_SSL_Key	replica_pkey.pem
Seconds_Behind_Master	110511
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	1845083005
Master_UUID	ebcd15bd-8e39-11e7-8142-42010a9a001f
Master_Info_File	mysql.slave_master_info
SQL_Delay	0
SQL_Remaining_Delay
Slave_SQL_Running_State	Reading event from the relay log
Master_Retry_Count	86400
Master_Bind
Last_IO_Error_Timestamp
Last_SQL_Error_Timestamp
Master_SSL_Crl
Master_SSL_Crlpath
Retrieved_Gtid_Set	ebcd15bd-8e39-11e7-8142-42010a9a001f:262566099-263012216
Executed_Gtid_Set	ebcd15bd-8e39-11e7-8142-42010a9a001f:1-262566121
Auto_Position	1
Replicate_Rewrite_DB
Channel_Name
Master_TLS_Version

Here are the settings for the global settings for the master database

# Variable_name	Value
Aborted_clients	830
Aborted_connects	0
Binlog_cache_disk_use	2171
Binlog_cache_use	25206
Binlog_stmt_cache_disk_use	0
Binlog_stmt_cache_use	754
Bytes_received	18366478810
Bytes_sent	133342023108
Com_admin_commands	11812
Com_assign_to_keycache	0
Com_alter_db	0
Com_alter_db_upgrade	0
Com_alter_event	0
Com_alter_function	0
Com_alter_instance	0
Com_alter_procedure	0
Com_alter_server	0
Com_alter_table	55
Com_alter_tablespace	0
Com_alter_user	0
Com_analyze	0
Com_begin	0
Com_binlog	0
Com_call_procedure	0
Com_change_db	14
Com_change_master	0
Com_change_repl_filter	0
Com_check	0
Com_checksum	0
Com_commit	109569
Com_create_db	0
Com_create_event	0
Com_create_function	0
Com_create_index	127
Com_create_procedure	0
Com_create_server	0
Com_create_table	301
Com_create_trigger	0
Com_create_udf	0
Com_create_user	0
Com_create_view	0
Com_dealloc_sql	0
Com_delete	599
Com_delete_multi	0
Com_do	0
Com_drop_db	0
Com_drop_event	0
Com_drop_function	0
Com_drop_index	0
Com_drop_procedure	0
Com_drop_server	0
Com_drop_table	433
Com_drop_trigger	0
Com_drop_user	0
Com_drop_view	0
Com_empty_query	0
Com_execute_sql	0
Com_explain_other	0
Com_flush	0
Com_get_diagnostics	0
Com_grant	0
Com_ha_close	0
Com_ha_open	0
Com_ha_read	0
Com_help	0
Com_insert	21011
Com_insert_select	0
Com_install_plugin	0
Com_kill	1
Com_load	0
Com_lock_tables	0
Com_optimize	0
Com_preload_keys	0
Com_prepare_sql	0
Com_purge	0
Com_purge_before_date	0
Com_release_savepoint	0
Com_rename_table	0
Com_rename_user	0
Com_repair	0
Com_replace	0
Com_replace_select	0
Com_reset	0
Com_resignal	0
Com_revoke	0
Com_revoke_all	0
Com_rollback	17142310
Com_rollback_to_savepoint	0
Com_savepoint	0
Com_select	32129834
Com_set_option	34562276
Com_signal	0
Com_show_binlog_events	0
Com_show_binlogs	0
Com_show_charsets	1
Com_show_collations	1
Com_show_create_db	0
Com_show_create_event	0
Com_show_create_func	0
Com_show_create_proc	0
Com_show_create_table	4
Com_show_create_trigger	0
Com_show_databases	5
Com_show_engine_logs	0
Com_show_engine_mutex	0
Com_show_engine_status	478
Com_show_events	0
Com_show_errors	0
Com_show_fields	937
Com_show_function_code	0
Com_show_function_status	5
Com_show_grants	0
Com_show_keys	207
Com_show_master_status	4
Com_show_open_tables	0
Com_show_plugins	0
Com_show_privileges	0
Com_show_procedure_code	0
Com_show_procedure_status	5
Com_show_processlist	0
Com_show_profile	0
Com_show_profiles	0
Com_show_relaylog_events	0
Com_show_slave_hosts	0
Com_show_slave_status	479
Com_show_status	1448
Com_show_storage_engines	1
Com_show_table_status	0
Com_show_tables	553
Com_show_triggers	4
Com_show_variables	2498
Com_show_warnings	0
Com_show_create_user	0
Com_shutdown	0
Com_slave_start	0
Com_slave_stop	0
Com_group_replication_start	0
Com_group_replication_stop	0
Com_stmt_execute	0
Com_stmt_close	0
Com_stmt_fetch	0
Com_stmt_prepare	0
Com_stmt_reset	0
Com_stmt_send_long_data	0
Com_truncate	149
Com_uninstall_plugin	0
Com_unlock_tables	0
Com_update	5246
Com_update_multi	12
Com_xa_commit	0
Com_xa_end	0
Com_xa_prepare	0
Com_xa_recover	0
Com_xa_rollback	0
Com_xa_start	0
Com_stmt_reprepare	0
Connection_errors_accept	0
Connection_errors_internal	0
Connection_errors_max_connections	0
Connection_errors_peer_address	0
Connection_errors_select	0
Connection_errors_tcpwrap	0
Connections	30484
Created_tmp_disk_tables	1433
Created_tmp_files	322
Created_tmp_tables	15307
Delayed_errors	0
Delayed_insert_threads	0
Delayed_writes	0
Flush_commands	1
Handler_commit	18615431
Handler_delete	638
Handler_discover	0
Handler_external_lock	59946763
Handler_mrr_init	0
Handler_prepare	62200
Handler_read_first	216234
Handler_read_key	131595283
Handler_read_last	120
Handler_read_next	1850999215
Handler_read_prev	0
Handler_read_rnd	35201345
Handler_read_rnd_next	882659892
Handler_rollback	17142222
Handler_savepoint	0
Handler_savepoint_rollback	0
Handler_update	22357397
Handler_write	18882926
Innodb_buffer_pool_dump_status	Dumping of buffer pool not started
Innodb_buffer_pool_load_status	Buffer pool(s) load completed at 190425 14:22:30
Innodb_buffer_pool_resize_status
Innodb_buffer_pool_pages_data	307368
Innodb_buffer_pool_bytes_data	5035917312
Innodb_buffer_pool_pages_dirty	8232
Innodb_buffer_pool_bytes_dirty	134873088
Innodb_buffer_pool_pages_flushed	567914
Innodb_buffer_pool_pages_free	2176877
Innodb_buffer_pool_pages_misc	6123
Innodb_buffer_pool_pages_total	2490368
Innodb_buffer_pool_read_ahead_rnd	0
Innodb_buffer_pool_read_ahead	58171
Innodb_buffer_pool_read_ahead_evicted	0
Innodb_buffer_pool_read_requests	3434235197
Innodb_buffer_pool_reads	148050
Innodb_buffer_pool_wait_free	0
Innodb_buffer_pool_write_requests	132222590
Innodb_data_fsyncs	109874
Innodb_data_pending_fsyncs	0
Innodb_data_pending_reads	0
Innodb_data_pending_writes	0
Innodb_data_read	3399471616
Innodb_data_reads	207889
Innodb_data_writes	655781
Innodb_data_written	22894386176
Innodb_dblwr_pages_written	431725
Innodb_dblwr_writes	29147
Innodb_log_waits	0
Innodb_log_write_requests	13284835
Innodb_log_writes	44425
Innodb_os_log_fsyncs	44730
Innodb_os_log_pending_fsyncs	0
Innodb_os_log_pending_writes	0
Innodb_os_log_written	6477446144
Innodb_page_size	16384
Innodb_pages_created	348510
Innodb_pages_read	207482
Innodb_pages_written	570346
Innodb_row_lock_current_waits	0
Innodb_row_lock_time	0
Innodb_row_lock_time_avg	0
Innodb_row_lock_time_max	0
Innodb_row_lock_waits	0
Innodb_rows_deleted	676
Innodb_rows_inserted	24723254
Innodb_rows_read	2772197512
Innodb_rows_updated	5210135
Innodb_num_open_files	389
Innodb_truncated_status_writes	0
Innodb_available_undo_logs	128
Key_blocks_not_flushed	0
Key_blocks_unused	6689
Key_blocks_used	9
Key_read_requests	306
Key_reads	21
Key_write_requests	0
Key_writes	0
Locked_connects	0
Max_execution_time_exceeded	0
Max_execution_time_set	16
Max_execution_time_set_failed	0
Max_used_connections	208
Max_used_connections_time	2019-04-25 17:31:37
Not_flushed_delayed_rows	0
Ongoing_anonymous_transaction_count	0
Open_files	36
Open_streams	0
Open_table_definitions	1073
Open_tables	1601
Opened_files	8951
Opened_table_definitions	1882
Opened_tables	4332
Performance_schema_accounts_lost	0
Performance_schema_cond_classes_lost	0
Performance_schema_cond_instances_lost	0
Performance_schema_digest_lost	0
Performance_schema_file_classes_lost	0
Performance_schema_file_handles_lost	0
Performance_schema_file_instances_lost	0
Performance_schema_hosts_lost	0
Performance_schema_index_stat_lost	0
Performance_schema_locker_lost	0
Performance_schema_memory_classes_lost	0
Performance_schema_metadata_lock_lost	0
Performance_schema_mutex_classes_lost	0
Performance_schema_mutex_instances_lost	0
Performance_schema_nested_statement_lost	0
Performance_schema_prepared_statements_lost	0
Performance_schema_program_lost	0
Performance_schema_rwlock_classes_lost	0
Performance_schema_rwlock_instances_lost	0
Performance_schema_session_connect_attrs_lost	0
Performance_schema_socket_classes_lost	0
Performance_schema_socket_instances_lost	0
Performance_schema_stage_classes_lost	0
Performance_schema_statement_classes_lost	0
Performance_schema_table_handles_lost	0
Performance_schema_table_instances_lost	0
Performance_schema_table_lock_stat_lost	0
Performance_schema_thread_classes_lost	0
Performance_schema_thread_instances_lost	0
Performance_schema_users_lost	0
Prepared_stmt_count	0
Qcache_free_blocks	1
Qcache_free_memory	1031832
Qcache_hits	0
Qcache_inserts	0
Qcache_lowmem_prunes	0
Qcache_not_cached	18495620
Qcache_queries_in_cache	0
Qcache_total_blocks	1
Queries	1016139751
Questions	70390764
Rpl_semi_sync_master_clients	0
Rpl_semi_sync_master_estimated_slave_lag	0
Rpl_semi_sync_master_net_avg_wait_time	0
Rpl_semi_sync_master_net_wait_time	0
Rpl_semi_sync_master_net_waits	0
Rpl_semi_sync_master_no_times	1
Rpl_semi_sync_master_no_tx	25946
Rpl_semi_sync_master_status	OFF
Rpl_semi_sync_master_timefunc_failures	0
Rpl_semi_sync_master_tx_avg_wait_time	0
Rpl_semi_sync_master_tx_wait_time	0
Rpl_semi_sync_master_tx_waits	0
Rpl_semi_sync_master_wait_pos_backtraverse	0
Rpl_semi_sync_master_wait_sessions	0
Rpl_semi_sync_master_yes_tx	0
Rpl_semi_sync_slave_status	OFF
Rsa_public_key
Select_full_join	47817
Select_full_range_join	0
Select_range	5716712
Select_range_check	0
Select_scan	176228
Slave_open_temp_tables	0
Slow_launch_threads	0
Slow_queries	0
Sort_merge_passes	3824
Sort_range	378
Sort_rows	7026863
Sort_scan	1524
Ssl_accept_renegotiates	0
Ssl_accepts	0
Ssl_callback_cache_hits	0
Ssl_cipher
Ssl_cipher_list
Ssl_client_connects	0
Ssl_connect_renegotiates	0
Ssl_ctx_verify_depth	18446744073709551615
Ssl_ctx_verify_mode	1
Ssl_default_timeout	7200
Ssl_finished_accepts	0
Ssl_finished_connects	0
Ssl_server_not_after	Aug 31 10:48:01 2019 GMT
Ssl_server_not_before	Aug 31 10:47:01 2017 GMT
Ssl_session_cache_hits	0
Ssl_session_cache_misses	0
Ssl_session_cache_mode	SERVER
Ssl_session_cache_overflows	0
Ssl_session_cache_size	128
Ssl_session_cache_timeouts	0
Ssl_sessions_reused	0
Ssl_used_session_cache_entries	128
Ssl_verify_depth	18446744073709551615
Ssl_verify_mode	1
Ssl_version	TLSv1.1
Table_locks_immediate	4684
Table_locks_waited	0
Table_open_cache_hits	18566458
Table_open_cache_misses	4332
Table_open_cache_overflows	1497
Tc_log_max_pages_used	0
Tc_log_page_size	0
Tc_log_page_waits	0
Threads_cached	3
Threads_connected	187
Threads_created	243
Threads_running	5
Uptime	14345
Uptime_since_flush_status	14345

Can anybody help or point me in the right direction, as you can see from the graph it can normally deal with the replication load with ease ?

Thanks

Simon

This e-mail and any files transmitted with it are confidential and

intended solely for the use of the individual or entity to whom they

are addressed. If you have received this email in error please notify

the sender by replying to this e-mail.

Replies to this email may be monitored by the Haymarket Group

for operational or business reasons. 

Whilst every endeavour is taken to ensure that e-mails are free from

viruses, no liability can be accepted and the recipient is requested

to use their own virus checking software. 

www.haymarket.com

Haymarket Media Group Limited

Registered in England no. 267189

Registered Office: Bridge House, 69 London Road, Twickenham, TW1 3SP

[yka...@google.com]

Hello,

Usually the typical cause of replication lag is slow queries on the slave. These are discoverable by enabling log_slow_slave_statements and fixing them.

If you are not aware of any read/writes that could be causing this and believe this could be an issue on the Google side, you can create an issue tracker so that we can investigate it on our end.

[Simon DuPlessis]

Hi,

Thanks for your reply - I tried enabling log_slow_slave_statements via the console but it does not appear to be a supported flag in CloudSql (https://cloud.google.com/sql/docs/mysql/flags?hl=en_GB)?

Thanks

Simon

[Aref Amiri (Cloud Platform Support)]

Hi Simon,

I checked the suggested flag on my end on Cloud Console and noticed that it is currently not supported. 

As for the replication lag, it happens when the SQL thread on the replica is unable to keep up with the updates it is receiving from the master instance as well as slow queries on slave.

I suggest the following to mitigate the replication lags:

• If the primary is under a considerable read traffic load, increasing instance Tiers size may help.
• Send read traffic to the replica which can help reduce traffic on the master instance.

If You still experience the the Replica lag issue, I’d suggest to create a private Public Issue Tracker, and provide us with additional information such as Project Id and Cloud SQL instance name, and we shall investigate the issue further.