Question
Fix mysql high cpu usage on Ubuntu running WordPress
While doing maintenance to the Ubuntu server running mysql and apache2 using these commands:
SELECT table_schema AS “Database”, sum(data_length + index_length)/1024/1024 AS “size in MB” FROM information_schema.TABLES GROUP BY table_schema;
optimize table wp_commentmeta, wp_comments, wp_links, wp_odb_logs, wp_options, wp_postmeta, wp_posts, wp_term_relationships, wp_term_taxonomy, wp_termmeta, wp_terms, wp_usermeta, wp_users, wp_yoast_seo_links, wp_yoast_seo_meta;
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?
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
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.
Want to learn more? Join the DigitalOcean Community!
Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in Q&A, subscribe to topics of interest, and get courses and tools that will help you grow as a developer and scale your project or business.
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.
Hi there @mhweb,
What I could suggest is checking your running MySQL queries:
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! Regards, Bobby