Wordpress slow when publishing

Question

I have an Wordpress website with more than 219k of posts, and everytime when I publish something my CPU/MySQL usage go to 100%.

I really don't know why, because my entire website stay on 6% all day of usage and only spikes at a new post is published.

Yes, I use w3 super cache, but the problem is on the wp-admin itself! I've used the mysqltuner for optimizing the MySQL... But, like I already said, the problem only occurs when a new post is published.

I use two clouds on Amazon AWS for hosting the blog. The MySQL cloud is m1.medium and the Web Server is m1.xlarge.

Here is the graphics of my CPU's, as you can see, I have spikes at the same time on both clouds... These spikes occurs exactly on the time when I publish a new post. The green line is the MySQL and the blue line is the Web server.

Here you can see the graphic: https://i.stack.imgur.com/PReQP.png

If no one can help me with this situation, please, I want something to "inspectate" the code... The entire scruture of Wordpress is so ugly that I can't understand how new posts works. I want to debug the queries/functions or something like that.

Thanks!

Answer 1

I had a similar issue with a medium instance but it ended up being an issue with the theme I was using. Change the theme to a default WP theme to see if the problem persists. There was a function in the theme's function.php file that was not working properly. Deleting the function fixed the posting issue for me.

The idea is narrow down if the theme or even a plugin is causing the issue.

Example: The function that I had to eliminate from the theme I was using was

// updates post id in sequential order by post date used for post page
function updateNumbers() {

global $wpdb;
$querystr = "SELECT $wpdb->posts.* FROM $wpdb->posts 
             WHERE $wpdb->posts.post_status = 'publish' 
             AND $wpdb->posts.post_type = 'post' 
             ORDER BY $wpdb->posts.post_date ASC";
$pageposts = $wpdb->get_results($querystr, OBJECT);
$counts = 0 ;
if ($pageposts):
foreach ($pageposts as $post):
    $counts++;
    add_post_meta($post->ID, 'incr_number', $counts, true);
    update_post_meta($post->ID, 'incr_number', $counts);
endforeach;
endif; 
}  

One plugin that caused issues after updating to 4.2.4 on multisite was "Installer". The backend was extremely slow. Removing this plugin resolved this issue.

Answer 2

Assuming you have used mysqltuner well, the problem may well lie in the data storage hardware.

Presumably your MySQL instance is saving data to on an EBS volume. There are at least two issues you're facing:

• EBS is slow
• Medium instances have relatively slow IO performance compared to large instances. EBS needs good IO performance.

Try measuring the file system performance of the EBS volume where your MySQL data is stored, using something like iozone. When I did that a few weeks back, the performance of an EBS volume mounted to a small instance was about half as good as a single hard drive on my laptop.

You can improve EBS performance by striping multiple EBS volumes into a software raid configuration

http://www.mysqlperformanceblog.com/2009/08/06/ec2ebs-single-and-raid-volumes-io-bencmark/

http://dodizzle.com/setting-up-software-raid-zero-over-8-volumes

Moving your MySQL instance to Large or better gives you much more memory as well as better IO performance. I would try creating a software RAID first, and then consider moving to a larger instance. Note that you actually may be able to step down Wordpress from an xlarge to a large instance if you tune the data layer well.