Magento 2 high MySQL CPU usage troubleshooting
Magento 2 merchant recently asked whether it is possible to identify the query causing high CPU usage on his website. Hopefully, this Magento is hosted not on Magento Broken official Cloud. So it is possible to fix the issues, not just create the ticket and wait for a while response that Magento 2 Core sucks and it is not a MySQL issue.
MySQL has support to map processlist ids to OS thread ids through column TID of the information_schema.processlist table starting with the release of 5.7, MySQL followed with its own implementation by extending the PERFORMANCE_SCHEMA.THREADS table and adding a new column named THREAD_OS_ID.
To troubleshoot CPU issues, we can use several tools, such as top or pidstat.
Install PIDstat:
sudo apt install sysstat -yThe pidstat command is used for monitoring individual tasks currently being managed by the Linux kernel. It writes to standard output activities for every task selected with option -p or for every task managed by the Linux kernel if option -p ALL has been used. Not selecting any tasks is equivalent to specifying -p ALL, but only active tasks (tasks with non-zero statistics values) will appear in the report.
The pidstat command also can be used for monitoring the child processes of selected tasks. Read about option -T below.
The interval parameter specifies the amount of time in seconds between each report. A value of 0 (or no parameters at all) indicates that task statistics are to be reported since the system startup (boot). The count parameter can be specified in conjunction with the interval parameter if it is not set to zero. The value of count determines the number of reports generated at interval seconds apart. If the interval parameter is specified without the count parameter, the pidstat command generates reports continuously.
Adding the -p parameter and the MySQL process id. This tool only shows MySQL threads, making it easier for us to troubleshoot. The last parameter (1) is to display one sample per second:
pidstat -t -p 99999 1
We can see that the thread 66666 is consuming the most CPU by quite a large margin, and we made sure to verify that the consumption is constant across multiple samples of pidstat.
AWS RDE Performance Insight has really great visualization CPU usage visualisation per query at a point in time:
AWS RDS (MySQL) instance load can be sliced by waits (default), SQL commands, users, and hosts | This metric is designed to correlate aggregate load (sliced by the selected dimension) with the available compute capacity on that DB instance (number of vCPUs). Load is aggregated and normalized using the Average Active Session (AAS) metric. A number of AAS that exceed the compute capacity of the DB instance is a leading indicator of performance problems.
Using that information, we can log into the database and use the following query to find out which MySQL Thread is the culprit:
mysql > select * from performance_schema.threads where THREAD_OS_ID = 66666;Now we know that the high CPU consumption comes from a query in the EAV tables join. Using this information, we can troubleshoot the query and check the execution plan with the EXPLAIN command to see if there is any room for improvement.
So, starting from version 5.7. By knowing the OS thread, we can quickly detect CPU-intensive queries that are affecting the system performance.
PS: my favorite way to entertain my sale is — Magento slow query hunting. You can just run the “shows full processlist;” query, and you will see all currently running queries. If you see some queries too often or for a long time, it is definitely an issue. Try it yourself, and you will see how good Magento 2 is…
I have even special script for hunting :
while true; do mysql -h 127.0.0.1 magento2 -u root -e 'show full processlist'; sleep 2; done;