Fix high CPU usage by WordPress and MySQL

Today one of our wordpress sites had very high server load and it was being caused by MySQL

So I went to the mysql console, and looked up the process list:

So this guy is appearing a lot
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

Let’s see how it’s behaving with explain
explain SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

It’s scanning 226k rows to get its search results!

Probably some moronic plugin is doing this and wordpress does not add an index on that table. The solution is simple, let’s add an index!

ALTER TABLE wp_options ADD INDEX (`autoload`);

Now let’s run explain again

From scanning 226k it went down to 408!, 3 orders of magnitude drop.

And now the CPU load went below 4%, crisis averted.

[SYSADMIN] Serve your WordPress cached pages directly with lighttpd and not PHP

Optimizing Your WordPress Cache Loads in Lighttpd.

If you don’t configure your wordpress virtual host properly in lighttpd, your wordpress cache will still make use of PHP.

Wouldn’t it be nice if all those cached requests were served directy from the webserver as the static files that they are, bypassing the CPU/memory load PHP can have, and use those resources for otherthings?

Install and Enable mod_magnet

For this to occur with lighttpd, you will need mod_magnet, so assuming you’re on a Ubuntu/Debian based linux distro let’s make sure we have it installed.

sudo apt-get install lighttpd-mod-magnet

Then let’s make sure it’s enabled, you can do this manually on your lighttpd.conf by adding “mod_magnet” to the list of enabled modules…

server.modules = (
        "mod_fastcgi",
        "mod_access",
        "mod_alias",
        "mod_accesslog",
        "mod_compress",
        "mod_rewrite",
        "mod_redirect",
        "mod_status",
        "mod_proxy",
        "mod_setenv",
        "mod_magnet"
)

or you can do it the lighty way:

sudo lighty-enable-mod magnet

(this simply makes a symlink to the 10-magnet.conf file inside /etc/lighttpd/conf-enabled which lighty will check upon startup)

The cache logic script that will be executed by lighttpd

Now, on your wordpress directory, create a file called rewrite.lua and paste the following script in it:

function log(str)
   -- wanna tail -f a log to see what's happening    
   fp = io.open("/path/to/some/lua.log","a+")
   fp:write(str .. "\n")
   fp:flush()
   fp:close()
end

function serve_html(cached_page)
    if (lighty.stat(cached_page)) then
        lighty.env["physical.path"] = cached_page
        return true
    else
        return false
    end
end

function serve_gzip(cached_page)
    if (lighty.stat(cached_page .. ".gz")) then
        lighty.header["Content-Encoding"] = "gzip"
        lighty.header["Content-Type"] = ""
        lighty.env["physical.path"] = cached_page .. ".gz"
        return true
    else
        return false
    end
end

if (lighty.env["uri.scheme"] == "http") then
    ext = ".html"
else
    ext = "-https.html"
end

cached_page = lighty.env["physical.doc-root"] .. "/wp-content/cache/supercache/" .. lighty.request["Host"] .. lighty.env["request.orig-uri"]
cached_page = string.gsub(cached_page, "//", "/")
cached_page = string.gsub(cached_page, lighty.request["Host"] .. "/index.php", lighty.request["Host"])

attr = lighty.stat(cached_page)

if (attr) then
    query_condition = not (lighty.env["uri.query"] and string.find(lighty.env["uri.query"], ".*s=.*"))
    user_cookie = lighty.request["Cookie"] or "no_cookie_here"
    cookie_condition = not (string.find(user_cookie, ".*comment_author.*") or (string.find(user_cookie, ".*wordpress.*") and not string.find(user_cookie,"wordpress_test_cookie")))

    if (query_condition and cookie_condition) then
        accept_encoding = lighty.request["Accept-Encoding"] or "no_acceptance"

        if (string.find(accept_encoding, "gzip")) then
            if not serve_gzip(cached_page) then 
                serve_html(cached_page) 
            end
        else
            serve_html(cached_page)
        end
        --log('cache-hit: ' .. cached_page)
    end
else
    --log('cache-miss: ' .. cached_page)
end

Configuring your vhost in lighttpd for WordPress redirects and direct cache serves without php.

Then on your vhost configuration in lighttpd.conf add the following towards the end.
(Fix paths if you have to)

var.wp_blog = 1

magnet.attract-physical-path-to = ( server.document-root + "/rewrite.lua" )

url.rewrite-if-not-file = (
   "^/(wp-.+).*/?" => "$0",
   "^/(sitemap.xml)" => "$0",
   "^/(xmlrpc.php)" => "$0",
   "^/(.+)/?$" => "/index.php/$1"
  )

Restart your lighttpd sudo service lighttpd restart

Now watch how your PHP processes breathe a lot better and you page loads are insanely faster.

You’re welcome 🙂

WordPress: Cannot create directory error, got everything chmoded to 777?

So you went as far as chmod’ing your entire folder structure to 777 (very unsafe), you’ve hacked wp/wp-admin/includes/file.php

return new WP_Error( 'mkdir_failed_ziparchive', __( get_current_user() . ' Could not create directory. ('.$_dir.')' )...

to print out exactly what directory it cannot create and what user is trying to create the folder, everything is correct, yet it won’t create the fucking folder?

the issue might be your vsftpd configuration!

Go to /etc/vsftpd.con and make sure that you have this setting uncommented:

write_enable=YES

restart your vsftpd (sudo service vsftpd restart) and try upgrading again, you’ll want to hit that ‘Donate Bitcoin’ button if I saved your ass today.

Cheers

How to process thousands of WordPress posts without hitting or raising memory limits.

So you need to write a script that processes all the posts in your wordpress database, you don’t need to use the stupid wordpress loop because you’re not writing web facing code, you might just need to fix some metadata on each one of the posts, but every time you iterate through your posts, no matter if you use the wordpress api, or even if you try to fetch the post IDs directly with MySQL and then call “wp_get_post($id)” after a few hundred posts your PHP interpreter dies when it uses all the memory you’ve given it.

You Google and every other clueless php-wordpress-noob that thinks himself of a programmer will give you the “Raise your PHP’s memory limit” or “Do it in parts” answer.

WTF is it with these noobs?

You a programmer used to real programming languages start to curse on the frustration of having to get dirty with this awful language and badly documented wordpress “API”. The noobs don’t understand that you might have to deal with tens of thousands of posts that couldn’t possibly fit in memory and you know…

is the solution to the problem is to free memory?

The WordPress geniuses created this WP Object cache which is used by default whenever you invoke “get_post()” or other functions. The bastards didn’t bother to mention it on the function documentations, nor they bothered to put a little note on how to disable it in case you didn’t need it. This is what happens when you get people that don’t think about all the possible uses of an API.

If you start iterating through a list of IDs, and you start invoking get_post($somePostId,’OBJECT’), and you start to print how much memory is available you will see how get_post() does keep the posts in memory, if you read get_post() and dig further you will see objects being cached in the in-memory WP Object Cache, a half-ass solution would be to invoke wp_cache_flush() every now and then:

[php]
$post_ids_cursor = mysqli_query(“select ID from wp_posts where post_status=’publish’ order by post_date desc”);
$n = 0;
$last_memory_usage = memory_get_usage();

while ($row = mysqli_fetch_row($post_ids_cursor)) {
//this son of a bitch caches the post object.
//nowhere on the WordPress documentation for the function it says so
//http://codex.wordpress.org/Function_Reference/get_post
$post = get_post($row[0],’OBJECT’);

$memory_usage = memory_get_usage();
$delta_memory = $memory_usage – $last_memory_usage;
$last_memory_usage = $memory_usage;

echo “($n) ” . $memory_usage . ” ($delta_memory) \n”;

$n++;

//flush php’s cache every 100 posts, and let’s see what happens.
if ($n % 100 == 0) {
wp_cache_flush();
echo “Flush!\n”;
}
}
[/php]

[bash]
//N post – Memory used – Delta memory used
(0) 30254136 (13984) //start about 28.85MB
(1) 30262280 (8144)
(2) 30269592 (7312)
(3) 30277656 (8064)
(4) 30285720 (8064)
(5) 30293784 (8064)
(6) 30301848 (8064)
(7) 30309928 (8080)
(8) 30318056 (8128)
(9) 30326120 (8064)
(10) 30334184 (8064)

(93) 31054104 (8104)
(94) 31062168 (8064)
(95) 31070232 (8064)
(96) 31078344 (8112)
(97) 31086440 (8096)
(98) 31094552 (8112)
(99) 31102632 (8080) //already here at 29.66MB
Flush!
(100) 29816984 (-1285648) //bam we’ve freed 1.22MB with the flush call
[/bash]

However this solution is slow, WordPress will use and free unnecessary dynamic memory and it’ll check the cache with no luck every time you get a new object, which is the case of a linear scan like the one we have to do to batch process our posts.

Luckily someone in the wordpress team put a way to disable caching, so the real solution is…

To not use dynamic memory at all if you don’t need it

When you read the code of “wp_post_cache” every time $wp_object_cache->add() is invoked, that code always checks to see if caching has been suspended using a function called “wp_suspend_cache_addition()”

[php]
function add( $key, $data, $group = ‘default’, $expire = ” ) {
if ( wp_suspend_cache_addition() )
return false;

[/php]

This function can be used to turn off the freaking caching, this way you can iterate much faster through all your posts, every object fetched from the database will be kept in the stack of your loop and by not needing to flush or check the cache your processing will be much much faster.

This is how you turn it off:

[php]
wp_suspend_cache_addition(true);
[/php]

Hope this helped you process your posts in batch efficiently, leave a tip on the way out if I saved your ass.

Python Script to Update WordPress in One Step

During the past week, I think I had to update all my wordpress instances twice, and it’s become really annoying doing this manually. I’ve written a python script which I’ll share with you.

How I keep my wordpress updated by hand
I tend to keep my wp-content folder outside of my wordpress installation for 2 reasons:

1. I don’t like to loose my themes, plugins and customizations
2. I like to keep all my customization changes under subversion

So, if I had my wordpress installation say at:
/home/user/public_html/blog

I’d keep my wp-content folder for that here:

/home/user/public_html/wp-content-for-blog

So when I upgrade my blog, I always remove the original wp-content folder that comes along wordpress, and I symlink my hard worked on wp-content folder that lives outside to the freshly unzipped wordpress folder.

[bash]
user@machine:~/public_html/blog$ ls -l

lrwxrwxr-x 1 user www 54 2008-11-26 09:29 wp-content -> /home/user/public_html/wp-content-for-blog

[/bash]

So what I endup doing all the time, is downloading the latest.zip to ~/public_html/, it will unzip under ~/public_html/wordpress, and then I’ll copy the current ~/public_html/blog/wp-config.php to ~/public_html/wordpress, then I’ll remove the default ~/public_html/wordpress/wp-content and symlink the outer wp-content with all my customizations, themes and plugins to it. Once done, I’ll make a backup of the old wordpress folder, and then I’ll rename wordpress folder to the name of the blog folder, and it’s all done.

It’s simple, but when you have to do it for 5 blogs, every week, it’s not fun anymore.

The Update Script

So here’s a script to do it in one step. If you’re not using my symlinked technique, this will do it for you, you only need to specify the full path to the folder where you want to keep your current wp-content folder outside the new installation before you apply the update, and the name of the folder where your current blog lives. The script below will have its configuration variables towards the beginning set so that they are in line with the example I’ve been talking about.

[python]
#!/usr/bin/python
#########################################################################################
#
# upgrade_wordpress.py – Script to automatically upgrade your wordpress installation.
#
# Requirements:
# – Python 2.4 or older
# – WordPress should already be installed
# – CURL (sudo apt-get install curl)
#
# Author: Angel (Gubatron) Leon
# LICENSE: See the GPL2 license.
# 2008
#########################################################################################
import os

#########################################################################################
#Config (relative to the folder where this script will be run from)
#########################################################################################

#The current folder where the blog lives
BLOG_FOLDER=’blog’

#
# The first time you run the script, it will try to make a copy of your
# current wp-content folder outside. Copy here the location of where
# the wp-content folder with your themes and plugins should exist.
#
# After it unzips, it will remove the default wp-content folder from
# the new installation, and it will symlink the external wp-content
# That way you don’t ever have to worry about loosing your customizations
# and plugins.
#
WP_CONTENT_OUTSIDE_COPY_FOLDER="/home/user/public_html/wp-content-for-blog"

#This is where a backup of your current blog will be
BLOG_FOLDER_BACKUP_FOLDER=BLOG_FOLDER+’.old’

#Where to download the wordpress latest.zip from
WORDPRESS_LATEST_ZIP_URL=’http://wordpress.org/latest.zip’

#### DO NOT MODIFY AFTER THESE LINES ####

def downloadWordpress(url=WORDPRESS_LATEST_ZIP_URL):
if os.path.exists(‘latest.zip’):
print "Removing old latest.zip"
os.remove(‘latest.zip’)

#Try to download with CURL
print "Attempting to download latest.zip from wordpress.org"
os.system(‘curl %s -o latest.zip’ % url)

if not os.path.exists(‘latest.zip’):
os.system(‘wget ‘ + url)

return os.path.exists(‘latest.zip’)

def dirExists(dirName):
return os.path.exists(dirName) and os.path.isdir(dirName)

def backupBlog(currentBlogFolder=BLOG_FOLDER,
wpContentOriginalFolder=WP_CONTENT_OUTSIDE_COPY_FOLDER,
backupFolder=BLOG_FOLDER_BACKUP_FOLDER):

#Remove any previous backups
if os.path.exists(backupFolder) and os.path.isdir(backupFolder):
print "Removing previous backup folder"
os.system(‘rm -fr ‘ + backupFolder)

#Copy the current blog folder into a backup folder just in case.
#We won’t do any database backups for now.
print "Creating new backup folder"
os.system(‘cp -r %s %s’ % (currentBlogFolder,backupFolder))

#Check for the copy of wp-content outside the blog, if it doesn’t exist
#we’ll make it for the first time.
if not dirExists(wpContentOriginalFolder):
print "Creating outside copy of wp-content"
os.system(‘cp -r %s %s’ % (os.path.join(currentBlogFolder,’wp-content’),
wpContentOriginalFolder))

#Copy the latest wp-config.php outside to the current folder
print "Copying your latest wp-config.php outside"
os.system(‘cp %s .’ % (os.path.join(currentBlogFolder,’wp-config.php’)))

backupFolderExists = dirExists(backupFolder)
wpContentFolderExists = dirExists(wpContentOriginalFolder)
configFileExists = os.path.exists(‘wp-config.php’)

return backupFolderExists and wpContentOriginalFolder and configFileExists

def upgradeBlog(currentBlogFolder=BLOG_FOLDER,
backupFolder=BLOG_FOLDER_BACKUP_FOLDER,
url=WORDPRESS_LATEST_ZIP_URL,
wpContentOriginalFolder=WP_CONTENT_OUTSIDE_COPY_FOLDER):

if not downloadWordpress(url):
print "Could not download latest.zip, aborting."
return False

if not backupBlog(currentBlogFolder,wpContentOriginalFolder,backupFolder):
print "Could not backup blog or wp-config.ph, aborting."
return False

if currentBlogFolder == ‘wordpress’:
print "The current blog folder cannot be ‘wordpress, aborting."
return False

#1. If a wordpress/ folder exists, wipe it.
if dirExists(‘wordpress’):
print "Removing old wordpress folder"
os.system(‘rm -fr wordpress’)

if dirExists(‘%s.delete’ % currentBlogFolder):
print "Removing old %s.delete folder" % currentBlogFolder
os.system(‘rm -fr %s.delete folder’ % currentBlogFolder)

#2. Unzip new copy
os.system(‘unzip latest.zip’)

if not dirExists(‘wordpress’):
print "Could not unzip the wordpress installation, aborting."
return False

#1. Copy wp-config.php into the new installation
os.system(‘cp wp-config.php wordpress/’)

#2. Remove the default wp-content folder
os.system(‘rm -fr wordpress/wp-content’)

#3. Symlink the original wp-content that lives outside
os.system(‘ln -s %s wordpress/wp-content’ % (wpContentOriginalFolder))

#4. Verify symlink was created
if not (os.path.exists(‘wordpress/wp-content’) and os.path.islink(‘wordpress/wp-content’)):
print "Could not create symlink to wp-content, aborting."
return False

#5. Move original folder to folder.delete, and make this wordpress folder the current folder.
os.system(‘mv %s %s.delete’ % (currentBlogFolder,currentBlogFolder))

if not dirExists(currentBlogFolder + ".delete"):
print "Could not rename current folder for later deletion, aborting."
return False

#6. Rename the new installation as the current blog
os.system(‘mv %s %s’ % (‘wordpress’,currentBlogFolder))

if dirExists(‘wordpress’):
print "ALERT: The wordpress folder still exists."
return False

if not dirExists(currentBlogFolder):
print "ALERT: The blog doesn’t exist, recover from the backup folder %s please" % (backupFolder)
return False

#7 Cleanup
os.system(‘rm -fr %s.delete’ % (currentBlogFolder))

return True

if __name__ == ‘__main__’:
upgradeBlog()
[/python]

Requirements

  • shell access to the machine where you have your wordpress installed
  • a python interpreter installed
  • curl (sudo apt-get install curl) to download the zip. If you don’t have it it’ll attempt to use wget
  • Installation

  • Right outside your wordpress installation folder, create a new file called upgrade_wordpress.py
  • Copy and paste the script inside that file
  • Edit the configuration variables to point to the name of your wordpress installation folder, and give it a full path to where you want to keep your wp-content folder (including the name of the folder, so if you want to name it the same way, you could do for example /home/user/wp-content and it’ll be saved right under your home)
  • Usage:
    [bash]python upgrade_wordpress.py[/bash]

    The script is very fault proof, it will always try to abort in case something is not going the way it’s expected. At the end of the day it’ll also leave a backup copy of your current blog in case something goes bad, you can always recover.