Disabling old wordpress comments

As with most individuals that manage a blog, I get inundated with comment spam. Based on some research I did, it looks like almost all of the blog spam occurs for older posts, especially those over 30 days old. Based on this information, I decided to disable comments for all old posts older than 30 days. It turns out that you can’t do this directly from wordpress without a plugin, so I decided to adjust the “comment_status” column in the wp_posts table directly. To remove comment spam for all posts older than 30 days, I first used the GNU date utility to find the date 30 days ago:

$ date –date=”30 days ago” “+%Y-%m-%d”
2006-11-29

Once I had the date string from 30 days ago, I connected to the MySQL database that hosts my blog, and ran the following SQL query to disable posts for all entries greater than the date returned form GNU date:

$ mysql -umatty -p -h mysql.prefetch.net
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16155903 to server version: 4.0.27-standard-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> use matty
Database changed

mysql> UPDATE wp_posts SET comment_status = ‘closed’ WHERE post_date < '2006-11-29' AND post_status = 'publish'

mysql> quit
Bye

Now that old posts don’t have comments enabled, I am curious to see how the comment spammers react.

Speeding up wordpress

WordPress has become one of the most popular blogging engines on the Internet, and has numerous features that make blogging simple and easy. While there are numerous upsides to using wordpress, there is also one major drawback. WordPress generates each blog entry dynamically from a database, which can cause a considerable drain on computing resources. To get a better idea of just many resources wordpress would consume on a 2GHZ/1GB AMD Athlon server, I decided to run a few tests with the siege web benchmarking utility. The first test I ran was a 50-user simulation:

$ siege -c 50 http://prefetch.net/blog

** siege 2.64
** Preparing 50 concurrent users for battle.
The server is now under siege...

Availability:                 100.00 %
Elapsed time:                  50.04 secs
Data transferred:               4.28 MB
Response time:                 10.05 secs
Transaction rate:               4.00 trans/sec
Throughput:                     0.09 MB/sec
Concurrency:                   40.17
Successful transactions:         250
Failed transactions:               0
Longest transaction:           31.60
Shortest transaction:           0.09

Ouch! The results reported by siege were nothing like I expected. When 50 simultaneous users were accessing my website, it took over 10-seconds to render the main page of my blog. In addition, the server run queue was over 30, and the box was nearly unusable during the test (luckily I was using the Solaris fair share scheduler to limit how much CPU time Apache could consume). Since these results were unacceptable, I decided to start profiling and tuning WordPress to serve up content quicker. After spending a bit of time reviewing the MySQL performance data, I adjusted several buffer cache values that were sized too low, and enabled the WordPress post-query accelerator to allow the SQL queries to be served from the MySQL query cache. Once these changes were made, I ran siege again, and the results were not much better:

$ siege -c 50 http://prefetch.net/blog

** siege 2.64
** Preparing 50 concurrent users for battle.
The server is now under siege...

Transactions:                    268 hits
Availability:                 100.00 %
Elapsed time:                  62.02 secs
Data transferred:               6.25 MB
Response time:                  9.84 secs
Transaction rate:               4.32 trans/sec
Throughput:                     0.10 MB/sec
Concurrency:                   42.50
Successful transactions:         317
Failed transactions:               0
Longest transaction:           32.78
Shortest transaction:           0.08

Since the machine was limited by CPU, I decided to fire up DTrace to see where the httpd processes were spending their time. The DTrace results indicated that Apache was spending a considerable amount of time compiling PHP pages, and issuing queries to the back-end database. Since I only update my blog a few times each week, I started to wonder if there was a way to turn the pages I created in WordPress into static content. After reading through a variety of wordpress resources, I came across the wordpress cache #2 plugin. This super useful plugin will create a static represetnation of each dynamically generated page, and serve that file instead of the page that wordpress would dynamically generate. Once I got wp-cache2 installed, the results were right on par with what I orginally thought they should be (since I don’t typically serve 50 simultaneous user sessions, 2.76 seconds was sufficient):

$ siege -c50 http://prefetch.net/blog/

** siege 2.64
** Preparing 50 concurrent users for battle.

Lifting the server siege...      done.                                            
Transactions:                    592 hits
Availability:                 100.00 %
Elapsed time:                  45.44 secs
Data transferred:              30.93 MB
Response time:                  2.76 secs
Transaction rate:              13.03 trans/sec
Throughput:                     0.68 MB/sec
Concurrency:                   35.99
Successful transactions:         592
Failed transactions:               0
Longest transaction:           33.67
Shortest transaction:           0.24

Since the load was low processing 50 simultaneous users, I decided to see what 100 simultaneous sessions would look like:

$ siege -c100 http://prefetch.net/blog

** siege 2.64
** Preparing 100 concurrent users for battle.

Availability:                 100.00 %
Elapsed time:                  54.91 secs
Data transferred:              35.59 MB
Response time:                  2.15 secs
Transaction rate:              24.15 trans/sec
Throughput:                     0.65 MB/sec
Concurrency:                   51.93
Successful transactions:        1423
Failed transactions:               0
Longest transaction:           34.29
Shortest transaction:           0.06

The results with 100 sessions were just as good (actually better) than the 50-session output, and I was able to get the results even lower by enabling the PHP cache accelerator. So in summary, if you are looking to handle large volumes of connections with wordpress, you might investigate the following:

– Remove uneeded Apache modules (or convert to lighttpd)

– Tune MySQL to handle the numerous wordpress queries

– Install and enable the wordpress post query accelerator

– Install and enable the wordpress cache #2

– Install one of the PHP opcode caches

Viva la blizog!

Securely backing up a wordpress configuration to a remote location

I have been using wordpress as my blogging engine for quite some time. To ensure that I can recover my blog in the event of a disaster (a good example would be a server catching on fire), I take weekly backups of the MySQL database that stores my posts and the wordpress configuration.Since the wordpress backups are relatively small, I typically use mysqldump to extract the data from the MySQL database, and openssl to encrypt the data. This allows me to email my backup to a remote location, and ensures that prying eyes cannot view any data that might be sensitive. To accomplish this, I use the following shell script:

#!/bin/bash

export PATH=/usr/bin:/usr/sfw/bin

DBNAME="dbname"
DBPASS="password"
DBUSER="dbuser"
EMAIL=admin@something.com"
SYMMETRICKEY="SOMESECUREWPASSWORD"

mysqldump --opt -u ${DBUSER} -p${DBPASS} ${DBNAME} wp_categories \
                  wp_comments wp_linkcategories wp_links wp_options \
                  wp_post2cat wp_postmeta wp_posts wp_usermeta wp_users \
                  | /home/apps/bin/openssl bf -e -a -k ${SYMMETRICKEY} \
                  | mailx -vv -s "Wordpress backup (`/bin/date`)" ${EMAIL}

This solution has worked well for me for the past two years, and I have never had a problem running openssl with the “-d” (decrypt data) option to decrypt the data that openssl’s “-e” (encrypt data) option produces. I reckon I should probably add “START PAYLOAD” and “END PAYLOAD” strings to the output to ensure that the data made it to the destination in one piece.

Upgrading wordpress

While reading through the wordpress dashboard this weekend, I noticed that several new security advisories were announced for the version of wordpress I was using. To mitigate potential security issues, I decided to upgrade to WordPress 2.0.2 and Spam Karma 2.2. The upgrade process was super simple, and I will describe it below.

To begin the upgrade, you need to grab the latest versions of wordpress and spam karma:

$ wget http://wordpress.org/latest.zip

$ wget http://wp-plugins.net/sk2/sk2_final.zip

Once the files have been downloaded to a location on the server you plan to upgrade, you can unzip them to a staging location ( this BLOG posting assumes that you are upgrading a version of wordpress that lives in a directory named public_html in your home directory):

$ mkdir ~/public_html/staging && cd ~/public_html/staging && unzip ~/latest.zip

$ cd ~/public_html/staging/wordpress/wp-content/plugins && unzip ~/sk2_final.zip

Once the files are extracted, you will need to copy your existing wordpress configuration file to the staging directory:

$ cp ~/public_html/wordpress/wp-config.php ~/public_html/staging/wordpress/wp-config.php

If everything completed successfully, you should be able to view the new wordpress version by prepending the word staging to your wordpress administrator URL. WordPress will prompt you to upgrade the database schema, and once that completes, you can backup the current version and move the staging version into place. This entire process takes less than 10-minutes, and the new version has lots of cool features. Nice!