I've been looking for ways to collect information about the replication delays on MySql 8. I found an alternative solution, but I imagine there's a more straightforward way to do that.
By reading its reference manual, I found out that I could get useful information in the performance_schema database. When I look into the table replication_connection_status on the Slave server I can find the following information, among others:
- When the original Master commited the transaction (LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)
- When the immediate Master commited the transaction (LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP).
- When the Slave started and finished to write the updates in its relay log (LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP, LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP).
But it lacks the information about when the transaction was commited on this Slave server.
The alternative solution: Perform replication of these updates, from the Slave server to a third server (its master would be the Slave we're trying to get this info from), and then looking the column LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP on the third server, which would be when the main Slave server has commited the transaction.
A possible solution: The table events_transactions_history may contain the time where the transaction ended, but the time format is confusing. According to 2:
The timer baseline (“time zero”) occurs at Performance Schema initialization during server startup. TIMER_START and TIMER_END values in events represent picoseconds since the baseline. TIMER_WAIT values are durations in picoseconds.
I have no idea of how to know what this "time zero" would be exactly, all we're given is that it occurs during server startup, but how could I get this exact time?
And of course, if there's an easier solution that I've missed, let me know.
Thanks.
