Running WordPress on NGINX + CGI on top of MariaDB and UBUNTU 10.04

UPDATE 31/08/2011

Although this post talks about using spawnCGI, I had to give it up as the stability was terrible. I ended up using php5-fpm to serve php in cgi mode. This solution is a better choice. It's also very easy to install using apt resources.

Packages are great.  They save me valuable time and you dont make a mess for the guy that has to clean up behind you when you are gone.  So I wanted to make this wordpress a specific install, I wanted it to get exotic on the install with proven technology I came to appreciate since they where all ass-savers at points in my life. So tribute to those by doing a $enlarge_user_base++ proving some points that need no proving in the real world.

What I wanted

• Run on MariaDB, not mysql and use TRADITIONAL sql mode
• Use NGINX as the webserver instead of Apache (hence use php fastCGI, more specific spawnCGI)
• Atleast be at WordPress 3.0 BUT only using ubuntu packages. (this proved easy, no backports needed!)

I expected a lot more resistance from all these components but in the end it was just easy as hell,  only a few things you need to pay attention at.

Find the intructions to install MariaDB on debian/ubuntu

UPDATE 31/08/2011

Please look here for instructions for installation on debian and family. There is an apt sources config generator that will help you. I think the instruction below here are probably obsolete for anything but MariaDB 5.1. If you want 5.2 use the generator page.

It is truly a one-liner to add the repo for MariaDB.  and it's a DROP-IN replacement, I assure you, it is, even with advanced Databases schema's.  Either get the mariadb deb files, use the ourdelta package for unbuntu 10.04 Lucid install or build it yourself.  Since I was going to cover packages here, this is what I did to get mariaDB running on Lucid.  It's a copy paste from the Ourdelta way.

Then you just do

Install wordpress

Just use WordPress for Ubuntu Natty (there are no dependancy problems, you can just wget/dpkg install these!) from  https://launchpad.net/ubuntu/+source/wordpress/3.0.5+dfsg-1ubuntu1/+build/2233064

wget and dpkg -i this file.

Make it work on mariaDB

WordPress schema needs adaptations.  The traditional mode doesn't like the default date formats, and they have a point, that's not really a meaningful date for most applications with all the zero's.   The php code of wordpress needs to be modded.  We have a solution for that in one command. You might want to do this in the wordpress base directory (e.g. /usr/share/wordpress ), this might screw up a lot of other scripts otherwise. Also, do this before creating the wordpress schema, this command fixes everything, code and schema , since that sits conveniently in a .php file.

After this you should execute the wordpress install, it will not fail anymore on the creation of the database tables if you had this happen to you before, if you waited till now wordpress is installed correctly from the first try.

nginx install

For the nginx configuration part, please take a look at my nginx config extract here, this fixes the permalink issue's you might have too.

spawn fcgi install

configure the spawn-fcgi service

create and edit /etc/default/spawn-fcgi

#
# Settings for php-cgi in external FASTCGI Mode
#
 
# Should php-fastcgi run automatically on startup? (default: no)
 
START=yes
 
# Which user runs PHP? (default: www-data)
 
EXEC_AS_USER=www-data
EXEC_AS_GROUP=www-data
 
# Host and TCP port for FASTCGI-Listener (default: localhost:9000)
 
FCGI_HOST=127.0.0.1   # correct this to your setup
FCGI_PORT=66666       # replace with your port
 
# Environment variables, which are processed by PHP
 
#PHP_FCGI_CHILDREN=300
#PHP_FCGI_MAX_REQUESTS=300
 

spawn fcgi init script

Get the file below and put this in /etc/init.d/

#! /bin/sh
### BEGIN INIT INFO
# Provides:          spawn-php-fastcgi
# Required-Start:    $all
# Required-Stop:     $all
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: Start and stop php-cgi in external FASTCGI mode
# Description:       Start and stop php-cgi in external FASTCGI mode
### END INIT INFO
 
# Author: Kurt Zankl <kz AT xon D.t uni D.t cc>
# Modded by Glenn http://byte-consult.be
 
# Do NOT "set -e"
 
PATH=/sbin:/usr/sbin:/bin:/usr/bin
DESC="spawn-php-cgi in external FASTCGI mode"
NAME=spawn-fcgi
DAEMON=/usr/bin/spawn-fcgi
PIDFILE=/var/run/$NAME.pid
SCRIPTNAME=/etc/init.d/$NAME
 
# Exit if the package is not installed
[ -x "$DAEMON" ] || exit 0
 
# Read configuration variable file if it is present
[ -r /etc/default/$NAME ] && . /etc/default/$NAME
 
# Load the VERBOSE setting and other rcS variables
. /lib/init/vars.sh
 
# Define LSB log_* functions.
# Depend on lsb-base (>= 3.0-6) to ensure that this file is present.
. /lib/lsb/init-functions
 
# If the daemon is not enabled, give the user a warning and then exit,
# unless we are stopping the daemon
if [ "$START" != "yes" -a "$1" != "stop" ]; then
        log_warning_msg "To enable $NAME, edit /etc/default/$NAME and set START=yes"
        exit 0
fi
 
# Process configuration
#export PHP_FCGI_CHILDREN PHP_FCGI_MAX_REQUESTS
DAEMON_ARGS=" -P $PIDFILE -a $FCGI_HOST -p $FCGI_PORT -u $EXEC_AS_USER -g $EXEC_AS_GROUP -- /usr/bin/php-cgi"
 
do_start()
{
        # Return
        #   0 if daemon has been started
        #   1 if daemon was already running
        #   2 if daemon could not be started
	#/usr/bin/spawn-fcgi -P /var/run/spawn-fcgi.pid -a 127.0.0.1 -p 53217 -u www-data -g www-data -- /usr/bin/php-cgi
	exec $DAEMON $DAEMON_ARGS
        return 0
}
 
do_stop()
{
        # Return
        #   0 if daemon has been stopped
        #   1 if daemon was already stopped
        #   2 if daemon could not be stopped
        #   other if a failure occurred
        start-stop-daemon --stop --quiet --retry=TERM/30/KILL/5 --pidfile $PIDFILE > /dev/null # --name $DAEMON
        RETVAL="$?"
        [ "$RETVAL" = 2 ] && return 2
        # Wait for children to finish too if this is a daemon that forks
        # and if the daemon is only ever run from this initscript.
        # If the above conditions are not satisfied then add some other code
        # that waits for the process to drop all resources that could be
        # needed by services started subsequently.  A last resort is to
        # sleep for some time.
        start-stop-daemon --stop --quiet --oknodo --retry=0/30/KILL/5 --exec $DAEMON
        [ "$?" = 2 ] && return 2
        # Many daemons dont delete their pidfiles when they exit.
        rm -f $PIDFILE
        return "$RETVAL"
}
 
case "$1" in
  start)
        [ "$VERBOSE" != no ] && log_daemon_msg "Starting $DESC" "$NAME"
        do_start
        case "$?" in
                0|1) [ "$VERBOSE" != no ] && log_end_msg 0 ;;
                2) [ "$VERBOSE" != no ] && log_end_msg 1 ;;
        esac
        ;;
  stop)
        [ "$VERBOSE" != no ] && log_daemon_msg "Stopping $DESC" "$NAME"
        do_stop
        case "$?" in
                0|1) [ "$VERBOSE" != no ] && log_end_msg 0 ;;
                2) [ "$VERBOSE" != no ] && log_end_msg 1 ;;
        esac
        ;;
  restart|force-reload)
        log_daemon_msg "Restarting $DESC" "$NAME"
        do_stop
        case "$?" in
          0|1)
                do_start
                case "$?" in
                        0) log_end_msg 0 ;;
                        1) log_end_msg 1 ;; # Old process is still running
                        *) log_end_msg 1 ;; # Failed to start
                esac
                ;;
          *)
                # Failed to stop
                log_end_msg 1
                ;;
        esac
        ;;
  *)
        echo "Usage: $SCRIPTNAME {start|stop|restart|force-reload}" >&2
        exit 3
        ;;
esac
 
:
 

And start it the old school way

With your nginx up and php spawned, mariaDB started, you should be ready now to try browsing to your webserver.

optionally upgrade jquery

One more thing, you'll notice that the client request jquery v1.4.2 (atleast the theme I use). That file is about 120Kb. On Ubuntu it comes as a package you can find in /usr/share/javascript/jquery

You should try to upgrade this file to a min version of jquery but chances are it's not even the v1.4.2. version, its a v1.3.2 one. This I don't think is ok. As long as you don't depend on too much jquery plugins (which may differ from theme to theme). You can just manually replace the jquery lib. I'm going to make an exception here on packages since Ubuntu is ridiculously far behind on jquery versions.

now mv the old one to a backup name and wget the latest min version. You should just give it a try, check with firebug if the javascript errors out somewhere, you might want to roll back to another version. The 30Kb you save are actually a big chunk of the total of the page before I optimised this and only put 2 or 3 posts on the front page. I care about user experience so I want it to go fast and responsive.

Now recheck with firebug and it should load that new version in without trying to solve that inside wordpress or the plug-ins. I would just do it like that and in case of a problem later on you can justify the time investigating it in depth. I don't think doing it like I describe here is too 'dirty'.

why switching to the maria engine

Beyond the bite, we (I) go beyond the byte.... I know from experience MariaDB takes care of some headaches and issues I personally have with the classic Mysql releases. Next to that I want to use the Maria format, I like it, it's fast, consumes less memory, can be fixed all the time , also on plenty large tables within acceptable time limits. I kinda also hate InnoDB since locking issues drove me mad at one point. By dropping in MariaDB about 50% of my issues where simply gone. The other half, the design of the tables sucked so we(at work) fixed those issues with brainpower. Thats a hell of an intro for MariaDB imho.

Take a look at the differences between both. I call some features, mostly all are ass-savers. The two most important to me are

• Aria formerly known as maria
• XtraDB

Plain InnoDB has issues XtraDB fixes. It's as simple as that. I'm not going to elaborate on this, if you don't have InnoDB issues your dataset is too small to notice or underused. If you still care about getting the most out of that shiny new VPS read on.

changing all InnoDB tables to (m)Aria format and shut XtraDB down

Now lets move all our tables to the Maria format. First thing you need to do, is take a backup of your database. Second is ALTER the engine for all those InnoDB tables. If you have a small database this takes seconds. The thing you need to remember is when mysqld crashes, you need to manually repair all tables with the script below, the auto repair thing built into mysqld doesn't work for me, you'll see complaints in the error logs if you don't fix those kind of situations.

Make sure to put in your own password and database parameters. Comment the ALTER out after first use, make it look for Maria tables and it becomes a repair script.

# a small bash script that checks for all tables in a given database for MyMaria tables, alters/repairs them
# 
 
MYSQL="/usr/bin/mysql"
MYSQLR="/usr/bin/mysqlrepair"
MYSQL_HOST="localhost"
MYSQL_USER="root"
MYSQL_PASSWD="your_password"
MYSQL_SOCKET="/var/run/mysqld/mysqld.sock"
MYSQLCONNECT="$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWD -h $MYSQL_HOST -S $MYSQL_SOCKET -A"
MYSQLREPAIR="$MYSQLR -v -F -u$MYSQL_USER -p$MYSQL_PASSWD -B" 
 
echo "Checking DB "
# Use MySQL 'SHOW DATABASES'
DATABASES="`$MYSQLCONNECT --batch -N -e "show databases like 'your_blog_db'"`" 
 
echo "Checking for InnoDB Tables "
# Loop through each instance of MySQL and check all databases in that instance
for DATABASE in $DATABASES
do
   TABLES="`$MYSQLCONNECT --batch -D $DATABASE -N -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='$DATABASE' AND TABLE_TYPE='BASE TABLE' AND ENGINE IN ('InnoDB')"`"
   for TABLE in $TABLES
   do
      echo "Altering Table : $TABLE"
      ALTERTABLES="`$MYSQLCONNECT --batch -D $DATABASE -N -e "ALTER TABLE $TABLE ENGINE=Maria"`"
      echo "Repairing Table: $TABLE"
      REPAIR_TABLE=`$MYSQLREPAIR $DATABASE --tables $TABLE`
   done
done
 

If you get no errors here you have to check to see if you have changed all tables into the MariaDB format.

If this is zero, you just got rid the last InnoDB table on your server, you are ready to switch off XtraDB. In case you still have tables belonging to another DB/application, you need to either go for it and change those too or stop here and don't switch off InnoDB

shutting down XtraDB

In your /etc/mysql/my.cnf file under the [server] section add the skip-innodb directive and change the default storage type from InnoDB (if that was yours) to 'Maria'. (Ver: 5.1.49-MariaDB-mariadb82-log).

To free up memory you now restart your mysql database with the new settings, this time we will do what Ubuntu wants me to do instead of old school /etc/init.d/mysql restart

Now MariaDB should be up again, and InnoDB will not be loaded.

the resource gain

When checking the resource usage on a 750Mb VPS machine I saw the database using this before switching:

• Before: Virtual: 730m Resident:128m
• After : Virtual: 380m Resident:44m

I like that nice drop, It Makes me feel I've accomplished something just by changing to another db engine. And I'm using a slick MariaDB install. Monitor the error log from the database to make sure everything stays well.