Very High CPU when we publish post in WordPress - MySQL spikes CPU

September 9, 2019 194 views
Apache MySQL

Whenever we upload & publish news, the server load will spike. and When 2 or 3 of them upload news at the same time, the server will hang for a few minutes (Internal Server Error) and then back to normal again.

Every time I publish or update a post in Wordpress there is a huge CPU spike due to MYSQL

I have deactivated plugins and themes, and the problem persists.

Every thing else on the site is very fast when there is high traffic. We tried increasing the server no luck.

Any help would be greatly appreciated!

Droplet Info:
Ubuntu 16.04.1 x64
Size: 6 vCPUs
16GB / 160GB Disk
MYSQL

2 Answers

Hi @jayaprash,

This seems to be a problem with the Database. Whenever you create/upload new posts/media your WordPress application creates queries to insert the data in the DB. Now, whether there are too many queries that are being executed or it’s just one query that takes a long time to do so, I’ll recommend contacting a Web Developer to optimize them.

Another thing you can do, is to try and optimize MySQL and turn on slow query log. That way, you should be able to see the exact query that’s taking so long to execute.

I have a couple of queries that have saved me in the past, maybe you can try them as well

MySQL Optimizations WORDPRESS
;; wp_options 'transient' rows
;; A transient value is one stored by WordPress and/or a plugin generated from a complex query - basically a cache. 

SELECT COUNT(*) FROM wp_options WHERE option_name LIKE  '_transient_%'; 
DELETE FROM wp_options WHERE option_name LIKE '%\_transient\_%'; 



;; wp_posts revisions
;; Every save of a WordPress post will create a new revision (and related wp_postmeta rows). To clear out all revisions older than 15 days:

SELECT COUNT(*) FROM wp_posts WHERE (post_type = 'revision') AND (post_modified_gmt < DATE_SUB(NOW(),INTERVAL 15 DAY)) ORDER BY post_modified_gmt DESC;
DELETE FROM wp_posts WHERE (post_type = 'revision') AND    (post_modified_gmt < DATE_SUB(NOW(),INTERVAL 15 DAY));



;; wp_postmeta '_edit_lock' and '_edit_last' rows
;; Rows created against a post when edited by a WordPress admin user. They can be safely removed.

SELECT COUNT(*) FROM wp_postmeta WHERE meta_key IN ('_edit_lock','_edit_last');
DELETE FROM wp_postmeta WHERE meta_key IN ('_edit_lock','_edit_last');

These queries shouldn’t actually delete any data in use or create problems. Nevertheless, I’ll recommend backing up your DB before you execute them.

It’s not 100% guarantee they will do the trick but for sure they will remove redundant data.

Kind regards,
Kalin

  • We tried above codes. No Luck.

    There are lot of slow queries while uploading posts such as wploadalloptions(), WPQuery->getposts() , wpcountposts().

    Can we optimize this using queries with MySQL index

Thanks Kalin for the reply.

Will implement the above MySQL Optimizations for wordpress ! Will revert back with the update!

Any other articles or methods can you suggest for optimizing MYSQL.

Regards
Prashanth

Have another answer? Share your knowledge.