Mysql performance analysis

Question

Hello all,

Its aurora mysql database. At one specific time we see high resource/ACU utilization and from the dashboard and its showing the the top consumer processes in descending fashion too. Now want to understand , how can we further dig into the top resource consuming process/queries and find the exact point where we have to fix things?

I understand "explain analyze" will give the detail execution of the query along with where the majority of resource getting spent . Want to understand if that is the only option we have ? (For e.g. like if something which shows the resource spent on the exact line of the execution path for full scan, sorting, joins etc. , so as to take necessary action).

In Oracle we used to have Performance views which holds real time execution report of the queries/procedures and shows the exact line in the execution path which is contributing majority of the resource. Also views to hold entries of the sample historical executions, to see where(which line in the execution plan) the query spent most of the time. Do we have such things available in Aurora mysql?

Answer

First of all I recommend turn on the performance insight.
Aurora provides CloudWatch metrics on wait events, query patterns and top resource consumers. The Performance Insights dashboard integrates this which can help identify top queries.
Also the Performance Schema tables in MySQL provide runtime performance metrics on queries, statements and stages that you can analyze. This is similar to Oracle performance views.
Last you can enable the slow query log to capture execution details over time. The log files or log tables can then be analyzed separately.
I hope this would help you.

Mysql performance analysis

Relevant content