Twitter this

Powered by MariaDB Powered by nginx ...

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.

1
2
3
wget -O- http://ourdelta.org/deb/ourdelta.gpg | sudo apt-key add -
sudo wget http://ourdelta.org/deb/sources/lucid-mariadb-ourdelta.list
\ -O /etc/apt/sources.list.d/ourdelta.list

Then you just do

1
$ apt-get update && apt-get install mariadb-server-5.1

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.

1
2
3
grep -r '0000-00-00 00:00:00' *
find . -type f -name '*.php' -ls -exec sed -i s/0000-00-00\ 00:00:00/1000-01-01\ 00:00:00/ {} \;
grep -r '1000-01-01 00:00:00' *

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

1
apt-get install nginx

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

1
apt-get update && apt-get install spawn-fcgi

configure the spawn-fcgi service

create and edit /etc/default/spawn-fcgi

Download
  1. #
  2. # Settings for php-cgi in external FASTCGI Mode
  3. #
  4.  
  5. # Should php-fastcgi run automatically on startup? (default: no)
  6.  
  7. START=yes
  8.  
  9. # Which user runs PHP? (default: www-data)
  10.  
  11. EXEC_AS_USER=www-data
  12. EXEC_AS_GROUP=www-data
  13.  
  14. # Host and TCP port for FASTCGI-Listener (default: localhost:9000)
  15.  
  16. FCGI_HOST=127.0.0.1 # correct this to your setup
  17. FCGI_PORT=66666 # replace with your port
  18.  
  19. # Environment variables, which are processed by PHP
  20.  
  21. #PHP_FCGI_CHILDREN=300
  22. #PHP_FCGI_MAX_REQUESTS=300
  23.  

spawn fcgi init script

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

Download
  1. #! /bin/sh
  2. ### BEGIN INIT INFO
  3. # Provides: spawn-php-fastcgi
  4. # Required-Start: $all
  5. # Required-Stop: $all
  6. # Default-Start: 2 3 4 5
  7. # Default-Stop: 0 1 6
  8. # Short-Description: Start and stop php-cgi in external FASTCGI mode
  9. # Description: Start and stop php-cgi in external FASTCGI mode
  10. ### END INIT INFO
  11.  
  12. # Author: Kurt Zankl <kz AT xon D.t uni D.t cc>
  13. # Modded by Glenn http://byte-consult.be
  14.  
  15. # Do NOT "set -e"
  16.  
  17. PATH=/sbin:/usr/sbin:/bin:/usr/bin
  18. DESC="spawn-php-cgi in external FASTCGI mode"
  19. NAME=spawn-fcgi
  20. DAEMON=/usr/bin/spawn-fcgi
  21. PIDFILE=/var/run/$NAME.pid
  22. SCRIPTNAME=/etc/init.d/$NAME
  23.  
  24. # Exit if the package is not installed
  25. [ -x "$DAEMON" ] || exit 0
  26.  
  27. # Read configuration variable file if it is present
  28. [ -r /etc/default/$NAME ] && . /etc/default/$NAME
  29.  
  30. # Load the VERBOSE setting and other rcS variables
  31. . /lib/init/vars.sh
  32.  
  33. # Define LSB log_* functions.
  34. # Depend on lsb-base (>= 3.0-6) to ensure that this file is present.
  35. . /lib/lsb/init-functions
  36.  
  37. # If the daemon is not enabled, give the user a warning and then exit,
  38. # unless we are stopping the daemon
  39. if [ "$START" != "yes" -a "$1" != "stop" ]; then
  40. log_warning_msg "To enable $NAME, edit /etc/default/$NAME and set START=yes"
  41. exit 0
  42. fi
  43.  
  44. # Process configuration
  45. #export PHP_FCGI_CHILDREN PHP_FCGI_MAX_REQUESTS
  46. DAEMON_ARGS=" -P $PIDFILE -a $FCGI_HOST -p $FCGI_PORT -u $EXEC_AS_USER -g $EXEC_AS_GROUP -- /usr/bin/php-cgi"
  47.  
  48. do_start()
  49. {
  50. # Return
  51. # 0 if daemon has been started
  52. # 1 if daemon was already running
  53. # 2 if daemon could not be started
  54. #/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
  55. exec $DAEMON $DAEMON_ARGS
  56. return 0
  57. }
  58.  
  59. do_stop()
  60. {
  61. # Return
  62. # 0 if daemon has been stopped
  63. # 1 if daemon was already stopped
  64. # 2 if daemon could not be stopped
  65. # other if a failure occurred
  66. start-stop-daemon --stop --quiet --retry=TERM/30/KILL/5 --pidfile $PIDFILE > /dev/null # --name $DAEMON
  67. RETVAL="$?"
  68. [ "$RETVAL" = 2 ] && return 2
  69. # Wait for children to finish too if this is a daemon that forks
  70. # and if the daemon is only ever run from this initscript.
  71. # If the above conditions are not satisfied then add some other code
  72. # that waits for the process to drop all resources that could be
  73. # needed by services started subsequently. A last resort is to
  74. # sleep for some time.
  75. start-stop-daemon --stop --quiet --oknodo --retry=0/30/KILL/5 --exec $DAEMON
  76. [ "$?" = 2 ] && return 2
  77. # Many daemons dont delete their pidfiles when they exit.
  78. rm -f $PIDFILE
  79. return "$RETVAL"
  80. }
  81.  
  82. case "$1" in
  83. start)
  84. [ "$VERBOSE" != no ] && log_daemon_msg "Starting $DESC" "$NAME"
  85. do_start
  86. case "$?" in
  87. 0|1) [ "$VERBOSE" != no ] && log_end_msg 0 ;;
  88. 2) [ "$VERBOSE" != no ] && log_end_msg 1 ;;
  89. esac
  90. ;;
  91. stop)
  92. [ "$VERBOSE" != no ] && log_daemon_msg "Stopping $DESC" "$NAME"
  93. do_stop
  94. case "$?" in
  95. 0|1) [ "$VERBOSE" != no ] && log_end_msg 0 ;;
  96. 2) [ "$VERBOSE" != no ] && log_end_msg 1 ;;
  97. esac
  98. ;;
  99. restart|force-reload)
  100. log_daemon_msg "Restarting $DESC" "$NAME"
  101. do_stop
  102. case "$?" in
  103. 0|1)
  104. do_start
  105. case "$?" in
  106. 0) log_end_msg 0 ;;
  107. 1) log_end_msg 1 ;; # Old process is still running
  108. *) log_end_msg 1 ;; # Failed to start
  109. esac
  110. ;;
  111. *)
  112. # Failed to stop
  113. log_end_msg 1
  114. ;;
  115. esac
  116. ;;
  117. *)
  118. echo "Usage: $SCRIPTNAME {start|stop|restart|force-reload}" >&2
  119. exit 3
  120. ;;
  121. esac
  122.  
  123. :
  124.  

And start it the old school way

1
$ /etc/init.d/spawn-fcgi start

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.

1
2
3
4
$ cd /usr/share/javascript/jquery
$ mv  jquery.js jquery.js_old
$ wget http://code.jquery.com/jquery-1.6.1.min.js
$ ln -s jquery-1.6.1.min.js jquery.js

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

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.

Download
  1. # a small bash script that checks for all tables in a given database for MyMaria tables, alters/repairs them
  2. #
  3.  
  4. MYSQL="/usr/bin/mysql"
  5. MYSQLR="/usr/bin/mysqlrepair"
  6. MYSQL_HOST="localhost"
  7. MYSQL_USER="root"
  8. MYSQL_PASSWD="your_password"
  9. MYSQL_SOCKET="/var/run/mysqld/mysqld.sock"
  10. MYSQLCONNECT="$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWD -h $MYSQL_HOST -S $MYSQL_SOCKET -A"
  11. MYSQLREPAIR="$MYSQLR -v -F -u$MYSQL_USER -p$MYSQL_PASSWD -B"
  12.  
  13. echo "Checking DB "
  14. # Use MySQL 'SHOW DATABASES'
  15. DATABASES="`$MYSQLCONNECT --batch -N -e "show databases like 'your_blog_db'"`"
  16.  
  17. echo "Checking for InnoDB Tables "
  18. # Loop through each instance of MySQL and check all databases in that instance
  19. for DATABASE in $DATABASES
  20. do
  21. 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')"`"
  22. for TABLE in $TABLES
  23. do
  24. echo "Altering Table : $TABLE"
  25. ALTERTABLES="`$MYSQLCONNECT --batch -D $DATABASE -N -e "ALTER TABLE $TABLE ENGINE=Maria"`"
  26. echo "Repairing Table: $TABLE"
  27. REPAIR_TABLE=`$MYSQLREPAIR $DATABASE --tables $TABLE`
  28. done
  29. done
  30.  

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

1
SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_TYPE='BASE TABLE' AND ENGINE IN ('InnoDB');

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).

1
2
3
4
5
6
7
...
skip-innodb
default_storage_engine  = Maria
# My installation of MariaDB on Ubuntu came like this by default.  Make sure you don't switch
# Traditional mode ON/OFF if you don'
t know the consequences.
sql_mode                = NO_ENGINE_SUBSTITUTION,TRADITIONAL
....

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

1
service 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.

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

recruitment
recruitment
recruitment
recruitment