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

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.

×
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

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

Submit an Answer