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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.