I recently wrote about Moving WordPress into RAMÂ to gain a significant performance boost. This is however a bold move and there are a lot of other things you can do to gain performance, one of them is using a newer version of MySQL (and why not take a look at Percona Server?).
If you are running Ubuntu like me, you will have noticed that just using the apt-get install mysql-server will install MySQL Server 5.1. Moving to MySQL Server 5.5 will get you a performance increase and Oracle is claiming it will get you the following results:
In recent benchmarks using the release candidate for MySQL 5.5, compared to MySQL 5.1, results demonstrated marked performance improvements:
- On Windows: up to 1,500 percent performance gains for Read/Write operations, and up to 500 percent gain for Read Only(1).
- On Linux: up to 360 percent performance gain in Read/Write operations and up to 200 percent improvement in Read Only(2).
First of all, download the source code from the MySQL site then un-tar it wherever you like. You need to change the version number below to the one you downloaded.
tar -xvzf mysql-5.5.16.tar.gz
To be able to compile it we need to have the proper tools to do it:
apt-get install cmake c++ libncurses5-dev bison libaio-dev
We also need to create the mysql user and group before proceeding:
groupadd mysql
useradd -r -g mysql mysql
Enter the directory you extracted and issue the following commands to compile and install MySQL. This will install MySQL into the /usr/local/mysql
directory:
cmake .
make
make install
make install scripts
Change the ownership of the installed directory to the mysql user:
chown -R mysql:mysql /usr/local/mysql
If this is the first installation of MySQL on this machine, you need to initiate the MySQL data directory and tables:
cd /usr/local/mysql
./scripts/mysql_install_db --user=mysql
We need to provide a configuration file in the /etc/mysql
folder with the same name as the server instance (if you like to use the startup script provided shortly). There are multiple configuration samples available in the /usr/local/mysql/support-files
folder, you will need to review one to start off with and then change it according to your needs. In this case I am using the “medium size” MySQL server config.
mkdir /etc/mysql
cp /usr/local/mysql/support-files/my-medium.cnf /etc/mysql/my.cnf
If you want your MySQL server instance available on all network cards (0.0.0.0), then you can skip this part. But you would probably like to limit access to the localhost (127.0.0.1).
nano /etc/mysql/my.cnf
Add this to the [mysqld] section of the file:
bind-address = 127.0.0.1
Now the MySQL Server 5.5 is installed, but you might want to create a startup and shutdown script for it too. At Planet MySQL I found the following script that use the Ubuntu Upstart to manage MySQL. Create a file…
nano /etc/init/mysql.conf
…and paste the following script into it:
#
# MySQL Service for Recorded Future
#
description "MySQL Server"
author "Anders Karlsson, Recorded Future"
start on (net-device-up
and local-filesystems
and runlevel [2345])
stop on runlevel [016]
expect fork
kill timeout 2
# Set variables.
env MYSQL_ETC=/etc/mysql
env MYSQL_PIDFILE=/var/run/mysql.pid
env MYSQL_HOME=/usr/local/mysql
env MYSQL_INSTANCE=my
umask 007
exec $MYSQL_HOME/bin/mysqld_safe --defaults-file=$MYSQL_ETC/$MYSQL_INSTANCE.cnf >> /tmp/x.out &
post-start script
loop=600
# Wait for MySQL to start.
while [ $loop -gt 0 ]; do
if $MYSQL_HOME/bin/mysqladmin --defaults-file=$MYSQL_ETC/$MYSQL_INSTANCE.cnf ping; then
break
fi
loop=$(($loop - 1))
sleep 1
done
exit 0
end script
# Send a soft SIGTERM to MySQL before Upstart will kill it.
# A Sigterm to mysqld will cause a controlled shutdown.
pre-stop script
exec kill -SIGTERM `cat $MYSQL_PIDFILE`
# Wait for MySQL to end. Flushing buffers and all.
loop=600
while [ $loop -gt 0 ]; do
# If the pidfile is found, then continue waiting.
if [ -e $MYSQL_PIDFILE ] ; then
loop=$((loop - 1))
sleep 1
continue
fi
break
done
end script
Finally you will need to create the mysql.pid file referenced by the MySQL Ubuntu Upstart script:
touch /var/run/mysql.pid
Restart your Ubuntu server and it will be ready to use, enjoy!
Thanks for the tips… however, I’m not upgrading, so I’ll just do a fresh install from 5.5!