- Newest
- Most votes
- Most comments
Hello,
This behaviour regarding “INNODB MONITOR OUTPUT” getting logged for the reader instances for an Aurora MySQL cluster running on version 3.05.2 could be observed when show engine innodb status;
query is being executed on the reader instances.
In order to isolate the occurrence of this event, kindly check if the query show engine innodb status;
is being executed on the reader instances of your cluster and compare the time of the execution of this command with the time of the 'INNODB MONITOR OUTPUT’ logs for further verification of this behaviour.
If feasible, you may consider checking if there is any change in the logs of the reader instances when show engine innodb status;
is not being executed on them.
Hi,
Thank you so much for asking your question.
We have included this change in the release notes for Aurora MySQL 3.05.2.
In Aurora MySQL versions lower than 3.05.2, users are unable to retrieve the output of SHOW ENGINE INNODB STATUS on Aurora MySQL reader DB instances. This is due to the default InnoDB behavior when innodb_read_only is enabled.
In Aurora MySQL version 3.05.2 and higher, when SHOW ENGINE INNODB STATUS is run on a reader instance, the output is written to the MySQL error log, allowing for easier troubleshooting.
For more information on working with MySQL error logs, see Aurora MySQL error logs. For more information on SHOW ENGINE INNODB STATUS, see SHOW ENGINE statement in the MySQL documentation.
I hope this might help.
Thank you Yutaka_H, yes that is helpful to have (I hope I did not overlook that in the initial release notes?).
I gather thus from the release notes and the link to MySQL's original documentation that this is Aurora-specific and an intentional change from the MySQL behaviour to aid debugging on reader instances.
The (in my opinion) unfortunate side effect is that for users of Percona Monitoring and Management or similar tools, that regularly perform such queries, they now flood the log, possibly hiding valuable information (and incurring extra cost when CloudWatch is used). I wonder if it were possible to perhaps make the output conditional to the
innodb_status_output
, so the user retains some measure of control?Anyway, thanks again for the clarification!
Edit: Uh, but
innodb_status_output
has a different function, of course. So my suggestion makes no sense, it's probably more complicated and/or would require a separate toggle.
Relevant content
- Accepted Answerasked 2 years ago
- asked 2 years ago
- AWS OFFICIALUpdated 2 months ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 7 months ago
- AWS OFFICIALUpdated a month ago
Thank you Vedanshi A, that was it! We use Percona Monitoring and Management with the metrics resolution set to "Standard", implying a sample every 10 seconds. When I change this to "Rare" (collecting every 180 seconds), an Innodb Monitor Log is appended every 180 seconds.
Interesting that those logs didn't show up with Aurora 3.04.1, but perhaps we have to upgrade PMM as well. In any event, that was the culprit. Thanks again!
(Edit on 2024-08-19: Originally (11 days ago) posted the above text mistakenly as answer, now deleted that and instead add it as comment.)