Docs/details on extra Aurora DB information_schema.replica_host_status fields?

Question

I'm looking replica_host_status Aurora for MySQL DB table, as documented here, which documents only the 'CPU', 'IS_CURRENT', 'LAST_UPDATE_TIMESTAMP', 'REPLICA_LAG_IN_MILLISECONDS', 'SERVER_ID', AND 'SESSION_ID' fields:
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Reference.ISTables.html,
https://docs.aws.amazon.com/pdfs/whitepapers/latest/amazon-aurora-mysql-db-admin-handbook/amazon-aurora-mysql-db-admin-handbook.pdf#using-smart-drivers

On test, we see there are several *extra* fields returned by querying SELECT * on that table, not mentioned in the above docs, e.g.:

```
SERVER_ID(0)='..' 
  SESSION_ID(1)='..' 
  IOPS(2)(2)=0
  READ_IOS(3)=526
  PENDING_READ_IOS(4)=526 
  CPU(5)=4.712041854858398 
  DURABLE_LSN(6)=32589404
  ACTIVE_LSN(7)=0
  LAST_TRANSPORT_ERROR(8)=0
  LAST_ERROR_TIMESTAMP(9)=1970-01-01 00:00:01.000000 
  LAST_UPDATE_TIMESTAMP(10)=2023-05-14 02:39:04.204539 
  MASTER_SLAVE_LATENCY_IN_MICROSECONDS(11)=0 
  REPLICA_LAG_IN_MILLISECONDS(12)=22 
  LOG_STREAM_SPEED_IN_KiB_PER_SECOND(13)=4.312263844152626
  LOG_BUFFER_SEQUENCE_NUMBER(14)=0
  IS_CURRENT(15)=1
  OLDEST_READ_VIEW_TRX_ID(16)=3228322
  OLDEST_READ_VIEW_LSN(17)=32589394
  HIGHEST_LSN_RECEIVED(18)=32589419
  CURRENT_READ_POINT(19)=32589394
  CURRENT_REPLAY_LATENCY_IN_MICROSECONDS(20)=0
  AVERAGE_REPLAY_LATENCY_IN_MICROSECONDS(21)=0
  MAX_REPLAY_LATENCY_IN_MICROSECONDS(22)=0 
```

Many of these extra fields seem potentially useful, e.g. for load balancing criteria, but I'd like to confirm what they do..

Qs:
* I did notice this doc that says its for PostgreSQL that *does* include several of the above fields: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora_replica_status.html, can I assume that PostgreSQL link's descriptions are the same for MySQL?

* READ_IOS: is this the total number of read requests to the table page that has ever been done, or a guage of current read/pending page reads (noticed offhand the value often is same as 'pending_read_ios' like above..?

* IS_CURRENT: in the 'https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Reference.ISTables.html' doc above, it says its whether the replica is 'current', does that mean whether the replica is currently active (not some past used one), or does it mean the replica's data is considered up-to-date etc, or (?)

* What are example uses for the 'LSN' fields, e.g. the DURABLE_LSN, HIGHEST_LSN_RECEIVED? Can these be used to check how up to date the data on the replica is relative to other replicas (higher the value the more up to date?)

* Is there a way to tell from these values, the number of current client connections open to a replica above (doesn't look like it)? If we wanted to know roughly how much the connection was recently used, is there a way from the above?

* Is there any docs on these fields, and are they of any current use: OLDEST_READ_VIEW_TRX_ID, CURRENT_READ_POINT?

Thanks

rePost-User-0497758 · Answer

Hello There,

I understand that you came across the mentioned extra fields, upon querying SELECT * FROM certain Aurora MySQL information_schema tables and would like to know their uses.

Aurora MySQL has certain information_schema tables that are specific to Aurora. The 'information_schema.REPLICA_HOST_STATUS' table contains replication information. The 5 columns which are documented i.e 'CPU', 'IS_CURRENT', 'LAST_UPDATE_TIMESTAMP', 'REPLICA_LAG_IN_MILLISECONDS', 'SERVER_ID', AND 'SESSION_ID' are just the selected columns out of all the extra fields mentioned here:
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora_replica_status.html

To answer your questions one-by-one:

1. Yes, the description on these extra fields mentioned in the below link are applicable for Aurora MySQL as well, though the link belongs to PostgreSQL as these serve as the general fields which hold the same description.
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora_replica_status.html

2. 'READ_IOS' column represents the total number of read I/O operations that have been performed on the table pages of the replica host since the replication started.

So, 'READ_IOS' indicates the cumulative count of read requests that have been executed on the table pages of the replica host since the replication process began.

Regarding the relationship between 'READ_IOS' and 'PENDING_READ_IOS', they have the same value if there are no pending read operations at the moment you execute the query. However, if there are ongoing or pending read operations, you may observe a difference between the two metrics.

It is important to note that these values may differ when there is a larger load or when the buffer cache is unable to quickly satisfy all read requests. In such cases, the number of pending read I/O operations 'PENDING_READ_IOS' would increase, indicating that read operations are waiting to be serviced.

While 'READ_IOS' and 'PENDING_READ_IOS' may have the same value in some cases, they represent different aspects of read operations and might diverge when there is a larger demand or contention for resources.

3. 'IS_CURRENT' value represents the status of the replica host in relation to the primary instance. A non zero value indicates that the replica host is currently in sync with the primary instance. It means that the replica is up to date and replicating changes from the primary without any lag.

You can find out which replicas are now in sync with the primary instance and which ones might be experiencing replication latency by querying the 'information_schema.REPLICA_HOST_STATUS'  table and checking the 'IS_CURRENT' value for each replica host.

4. The LSN (Log Sequence Number) fields in Aurora MySQL, such as 'DURABLE_LSN' and 'HIGHEST_LSN_RECEIVED', Can not be taken as metric to compare or calculate replica lag between MySQL or Aurora MySQL Instances. This feature is not applicable as per design.

- As you know that Aurora can have one writer instance and multiple reader instances, it uses storage level replication to sync the data between writer and reader nodes. 
- The LSN of Aurora MySQL is tied to data changes at storage layer in aurora. Any modification at storage layer keeps moving the LSN record forward. 
- The LSN would be changing even if there is no activity on aurora, because the underlying monitoring system keeps updating its latest health records on internal storage. Therefore LSN is never at halt and it keeps moving. 
- Because of the design, LSN is again a independent metric for each of the instances participating in Aurora cluster. Therefore, ideally you can not use LSN for calculating the replication lag between Aurora Instances.

Please see this article regarding Aurora replica lag:
[+] https://aws.amazon.com/premiumsupport/knowledge-center/aurora-read-replica-restart/

5. No, the above fields does not does not directly show the number of client connections active to a replica as they primarily focuse on the status of replica hosts within the replication setup.
    However, To determine the number of client connections open to a replica in Aurora MySQL, you can use the `SHOW PROCESSLIST` command.
    [+]https://dev.mysql.com/doc/refman/8.0/en/show-processlist.html

6. Regarding the parameters 'OLDEST_READ_VIEW_TRX_ID' and 'CURRENT_READ_POINT':
'OLDEST_READ_VIEW_TRX_ID' denotes the Transaction ID of the oldest active read transaction's view in the database. It shows the point in the transaction log up to which the database has processed transactions.

'CURRENT_READ_POINT', on the other hand, represents the current Transaction ID of the executing transaction in the database.

That said, these fields are mainly related to the internal workings of the database transaction processing.

I hope that the above information was quite helpful. Have a great day ahead! :)

Docs/details on extra Aurora DB information_schema.replica_host_status fields?

Relevant content