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!
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! Regards, Bobby
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.
Become a contributor for community
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
DigitalOcean Documentation
Full documentation for every DigitalOcean product.
Resources for startups and SMBs
The Wave has everything you need to know about building a business, from raising funding to marketing your product.
The developer cloud
Scale up as you grow — whether you're running one virtual machine or ten thousand.
Get started for free
Sign up and get $200 in credit for your first 60 days with DigitalOcean.*
*This promotional offer applies to new accounts only.