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
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).
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…
…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 ) stop on runlevel  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:
Restart your Ubuntu server and it will be ready to use, enjoy!
Feel free to leave a comment using the form below.
November 28, 2011 at 9:12 PM
Thanks for the tips… however, I’m not upgrading, so I’ll just do a fresh install from 5.5!