InnoDB Cluster Read-Only Lag Behind Writer

Question

For software testing purposes, I'm using MySQL v5.7 and MySQL Router with a sandbox deployment of an InnoDB cluster with one read-write instance and two read-only instances.

I'd like to be able to determine the amount of time between when data is written to the read-write instance and when that data is available in a read-only instance. From what I read, it sounds like SHOW SLAVE STATUS it what I'm looking for; it returns Seconds_Behind_Master. Unfortunately that command returns zero rows.

The cluster configuration is whatever the MySQL wizard created for me which appears below for one of the instances. The other two instances have different port numbers.

[client]
protocol = TCP
port = 3310
user = root

[mysqld]
transaction_write_set_extraction = XXHASH64
disabled_storage_engines = BLACKHOLE,FEDERATED,ARCHIVE
binlog_checksum = NONE
gtid_mode = ON
server_id = 1746145354
auto_increment_offset = 3
basedir = C:/Program Files/MySQL/MySQL Server 5.7
port = 3310
datadir = C:/Users/mjk99/MySQL/mysql-sandboxes/3310/sandboxdata
log_error = C:/Users/mjk99/MySQL/mysql-sandboxes/3310/sandboxdata/error.log
pid_file = C:/Users/mjk99/MySQL/mysql-sandboxes/3310/3310.pid
log_bin
loose_mysqlx_socket = mysqlx.sock
log_slave_updates = ON
relay_log_info_repository = TABLE
secure_file_priv = C:/Users/mjk99/MySQL/mysql-sandboxes/3310/mysql-files
master_info_repository = TABLE
binlog_format = ROW
plugin_load = mysqlx.dll
enforce_gtid_consistency = ON
loose_mysqlx_port = 33100
socket = mysqld.sock
report_port = 3310
log_syslog = OFF

Is there a setting here that's my problem or does SHOW SLAVE STATUS just not work with this setup?

Answer 1

SHOW SLAVE STATUS is only applicable to master/slave replication channels.

For Group Replication, you would look at the Replication_group_member_stats table. In particular the two queue sizes, the certification queue and the applier queue. The applier queue length tells you how many transactions behind this node is in applying the transactions which the group has agreed/promised to apply (in the same order) through the consensus protocol.

That column is new in MySQL 8. You can see how I calculated it by hand here in 5.7 (the gr_applier_queue_length() function).

We also plan to add the applier queue length (lag indicator) to the status output in the MySQL Shell AdminAPI.