[AuroraDB]: How to monitor replica lag

You can monitor how far an Aurora Replica is lagging behind the primary instance of your Aurora DB cluster by monitoring the Amazon CloudWatch Replica Lag metric. Because Aurora Replicas read from the same cluster volume as the primary instance, the Replica Lag metric has a different meaning for an Aurora DB cluster. The Replica Lag metric for an Aurora Replica indicates the lag for the page cache of the Aurora replica compared to that of the primary instance.

If you need the most current value for Aurora Replica lag, you can query the mysql.ro_replica_status table in your Aurora DB cluster and check the value in the Replica_lag_in_mseccolumn. This column value is provided to Amazon CloudWatch as the value for the Replica Lag metric. The values in the mysql.ro_replica_status are also provided in theINFORMATION_SCHEMA.REPLICA_HOST_STATUS table in your Aurora DB cluster.

[root@dbversity.com ec2-user]# mysql -h myhost_dbversity.com -u myawsuser -p”MyPassw0rd” -A -P 3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 219
Server version: 5.6.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>
mysql> use mysql
Database changed
mysql>
mysql>
mysql> show tables like ‘%replica_status%’;
+————————————+
| Tables_in_mysql (%replica_status%) |
+————————————+
| ro_replica_status |
+————————————+
1 row in set (0.01 sec)

mysql> select * from ro_replica_status\G
*************************** 1. row ***************************
Server_id: my-auroradb-cluster
Session_id: MASTER_SESSION_ID
Iops: 0
Read_IOs: 0
Pending_Read_IOs: 0
Cpu: 0.246305
Durable_lsn: 16112
Active_lsn: 0
Last_transport_error: 0
Last_error_timestamp: 1970-01-01 00:00:01
Last_updated_timestamp: 2015-04-23 11:11:39
Master_slave_latency_in_usec: 0
Replica_lag_in_msec: 0
Log_stream_speed_in_KiB_per_second: 2.1106194001991043
Log_buffer_sequence_number: 2094
Is_current: 0
Oldest_read_view_trx_id: 0
Oldest_read_view_lsn: 0
Highest_lsn_received: 0
Current_read_lsn: 0
Current_replay_latency_in_usec: 0
Average_replay_latency_in_usec: 0
Max_replay_latency_in_usec: 0
*************************** 2. row ***************************
Server_id: my-replica1
Session_id: 7ac86e8c-e9a6-11e4-a7b1-0e6f97ce4433
Iops: 0
Read_IOs: 192
Pending_Read_IOs: 0
Cpu: 0
Durable_lsn: 18446744073709551363
Active_lsn: 0
Last_transport_error: 0
Last_error_timestamp: 1970-01-01 00:00:01
Last_updated_timestamp: 2015-04-23 11:11:39
Master_slave_latency_in_usec: 0
Replica_lag_in_msec: 20.017
Log_stream_speed_in_KiB_per_second: 2.1106194001991043
Log_buffer_sequence_number: 2095
Is_current: 0
Oldest_read_view_trx_id: 1249560
Oldest_read_view_lsn: 16098
Highest_lsn_received: 16102
Current_read_lsn: 16098
Current_replay_latency_in_usec: 8859
Average_replay_latency_in_usec: 194031
Max_replay_latency_in_usec: 1033973
*************************** 3. row ***************************
Server_id: my-replica2
Session_id: 42b9ee82-e9a9-11e4-bee4-124019c088f3
Iops: 0
Read_IOs: 0
Pending_Read_IOs: 0
Cpu: 0
Durable_lsn: 18446744073709551363
Active_lsn: 0
Last_transport_error: 0
Last_error_timestamp: 1970-01-01 00:00:01
Last_updated_timestamp: 2015-04-23 11:11:39
Master_slave_latency_in_usec: 0
Replica_lag_in_msec: 19.479
Log_stream_speed_in_KiB_per_second: 2.1106194001991043
Log_buffer_sequence_number: 2095
Is_current: 0
Oldest_read_view_trx_id: 1249560
Oldest_read_view_lsn: 16098
Highest_lsn_received: 16102
Current_read_lsn: 16098
Current_replay_latency_in_usec: 8875
Average_replay_latency_in_usec: 194039
Max_replay_latency_in_usec: 1034462
3 rows in set (0.00 sec)

mysql>
mysql>
mysql> select Server_id,Replica_lag_in_msec from ro_replica_status;
+———————+———————+
| Server_id | Replica_lag_in_msec |
+———————+———————+
| my-auroradb-cluster | 0 |
| my-replica1 | 19.556 |
| my-replica2 | 19.065 |
+———————+———————+
3 rows in set (0.00 sec)

mysql>
mysql> select Server_id,Read_IOs,Pending_Read_IOs,Last_updated_timestamp,Master_slave_latency_in_usec,Replica_lag_in_msec,Is_current,Current_replay_latency_in_usec,Max_replay_latency_in_usec from ro_replica_status;
+———————+———-+——————+————————+——————————+———————+————+——————————–+—————————-+
| Server_id | Read_IOs | Pending_Read_IOs | Last_updated_timestamp | Master_slave_latency_in_usec | Replica_lag_in_msec | Is_current | Current_replay_latency_in_usec | Max_replay_latency_in_usec |
+———————+———-+——————+————————+——————————+———————+————+——————————–+—————————-+
| my-auroradb-cluster | 0 | 0 | 2015-04-23 11:17:08 | 0 | 0 | 0 | 0 | 0 |
| my-replica1 | 192 | 0 | 2015-04-23 11:17:08 | 0 | 19.86 | 0 | 25877 | 1034359 |
| my-replica2 | 0 | 0 | 2015-04-23 11:17:08 | 0 | 19.348 | 0 | 25931 | 1034331 |
+———————+———-+——————+————————+——————————+———————+————+——————————–+—————————-+
3 rows in set (0.00 sec)

mysql>
mysql>
mysql> select now() ;
+———————+
| now() |
+———————+
| 2015-04-23 11:17:30 |
+———————+
1 row in set (0.00 sec)

mysql>

mysql> select * from INFORMATION_SCHEMA.REPLICA_HOST_STATUS\G
*************************** 1. row ***************************
SERVER_ID: my-auroradb-cluster
SESSION_ID: MASTER_SESSION_ID
IOPS: 0
READ_IOS: 0
PENDING_READ_IOS: 0
CPU: 0.2463054209947586
DURABLE_LSN: 21861
ACTIVE_LSN: 0
LAST_TRANSPORT_ERROR: 0
LAST_ERROR_TIMESTAMP: 1970-01-01 00:00:01
LAST_UPDATE_TIMESTAMP: 2015-04-23 11:18:50
MASTER_SLAVE_LATENCY_IN_MICROSECONDS: 0
REPLICA_LAG_IN_MILLISECONDS: 0
LOG_STREAM_SPEED_IN_KiB_PER_SECOND: 2.1231731965174125
LOG_BUFFER_SEQUENCE_NUMBER: 2773
IS_CURRENT: 0
OLDEST_READ_VIEW_TRX_ID: 0
OLDEST_READ_VIEW_LSN: 0
HIGHEST_LSN_RECEIVED: 0
CURRENT_READ_POINT: 0
CURRENT_REPLAY_LATENCY_IN_MICROSECONDS: 0
AVERAGE_REPLAY_LATENCY_IN_MICROSECONDS: 0
MAX_REPLAY_LATENCY_IN_MICROSECONDS: 0
mysql/ro_replica_status: 0
*************************** 2. row ***************************
SERVER_ID: my-replica1
SESSION_ID: 7ac86e8c-e9a6-11e4-a7b1-0e6f97ce4433
IOPS: 0
READ_IOS: 0
PENDING_READ_IOS: 0
CPU: 0
DURABLE_LSN: 18446744073709551363
ACTIVE_LSN: 0
LAST_TRANSPORT_ERROR: 0
LAST_ERROR_TIMESTAMP: 1970-01-01 00:00:01
LAST_UPDATE_TIMESTAMP: 2015-04-23 11:18:50
MASTER_SLAVE_LATENCY_IN_MICROSECONDS: 0
REPLICA_LAG_IN_MILLISECONDS: 19.39699935913086
LOG_STREAM_SPEED_IN_KiB_PER_SECOND: 2.1231731965174125
LOG_BUFFER_SEQUENCE_NUMBER: 2774
IS_CURRENT: 0
OLDEST_READ_VIEW_TRX_ID: 1250944
OLDEST_READ_VIEW_LSN: 21851
HIGHEST_LSN_RECEIVED: 21851
CURRENT_READ_POINT: 21851
CURRENT_REPLAY_LATENCY_IN_MICROSECONDS: 13551
AVERAGE_REPLAY_LATENCY_IN_MICROSECONDS: 190221
MAX_REPLAY_LATENCY_IN_MICROSECONDS: 1034252
mysql/ro_replica_status: 0
*************************** 3. row ***************************
SERVER_ID: my-replica2
SESSION_ID: 42b9ee82-e9a9-11e4-bee4-124019c088f3
IOPS: 0
READ_IOS: 192
PENDING_READ_IOS: 0
CPU: 0
DURABLE_LSN: 18446744073709551363
ACTIVE_LSN: 0
LAST_TRANSPORT_ERROR: 0
LAST_ERROR_TIMESTAMP: 1970-01-01 00:00:01
LAST_UPDATE_TIMESTAMP: 2015-04-23 11:18:50
MASTER_SLAVE_LATENCY_IN_MICROSECONDS: 0
REPLICA_LAG_IN_MILLISECONDS: 18.854999542236328
LOG_STREAM_SPEED_IN_KiB_PER_SECOND: 2.1231731965174125
LOG_BUFFER_SEQUENCE_NUMBER: 2774
IS_CURRENT: 0
OLDEST_READ_VIEW_TRX_ID: 1250944
OLDEST_READ_VIEW_LSN: 21851
HIGHEST_LSN_RECEIVED: 21851
CURRENT_READ_POINT: 21851
CURRENT_REPLAY_LATENCY_IN_MICROSECONDS: 13585
AVERAGE_REPLAY_LATENCY_IN_MICROSECONDS: 190258
MAX_REPLAY_LATENCY_IN_MICROSECONDS: 1034206
mysql/ro_replica_status: 0
3 rows in set (0.00 sec)

mysql>

Source & more details at : http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Aurora.Replication.html