Fix mysql high cpu usage on Ubuntu running WordPress

Posted March 22, 2020 10k views

While doing maintenance to the Ubuntu server running mysql and apache2 using these commands:

SELECT tableschema AS “Database”, sum(datalength + indexlength)/1024/1024 AS “size in MB” FROM informationschema.TABLES GROUP BY table_schema;

optimize table wpcommentmeta, wpcomments, wplinks, wpodblogs, wpoptions, wppostmeta, wpposts, wptermrelationships, wptermtaxonomy, wptermmeta, wpterms, wpusermeta, wpusers, wpyoastseolinks, wpyoastseometa;

Then I went to the “Feedback” section of WordPress to delete old feedback around 24. Select them all and clicked “send” to trash.
Usually this process is quick. But it was taking a long time.
So I restarted the server, and now, mysql is taking all the cpu usage.

using the htop I see a bunch of /usr/sbin/mysql and one in particular is using most of CPU power.

Anyway to troubleshoot and fix this problem?

edited by bobbyiliev

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Submit an Answer
2 answers

Hi there @mhweb,

What I could suggest is checking your running MySQL queries:

mysql -e 'show processlist'

I believe that there might be some long-running SQL queries that could be causing the high CPU load.

Depending on the state and the type of the queries, you might want to wait for them to complete, or kill them.

Hope that this helps!

I’ve found this query causing the problem:

This is what the show processlist shows in “info”
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.

This query runs, completes, and runs again over and over, even after restarting the server.