Recently I had a problem running Drupal Simpletest on my new i7 dev box. It took couple minutes to execute a set of relatively simple tests and as a developer you probably know how annoying that could be. The problem was largely due to InnoDB storage engine and it slowness caused by creating / removing tables (compared to let say MyISAM).
The solution I came up with was to move that single Drupal databse to in-memory (tmpfs) partition. Luckily I had couple of free gigs of ram to spare.
I was able to reduce Drupal Simpletest exectuion time from aprox 2 minutes to 9 seconds! That’s impressive! Hope those instructions will work for you as well!
Here is how I did it:
First we need to create a tmpfs partition in your fstab ( /etc/fstab on my box) :
tmpfs /var/lib/mysql/tmpfs tmpfs rw,nosuid,nodev,uid=115,gid=123 0 0
Note: 115 and 123 are uid and gid of mysql user. To get those on your Ubuntu box you can run
id -u/-g mysql
After partition was created:
sudo mkdir /var/lib/mysql/tmpfs – create mount point
sudo chown mysql:mysql /var/lib/mysql/tmpfs – change the owner of the folder to mysql since we create it inside mysql dir
sudo mount -a – mount partition
Then we move our Drupal database from it’s old location ( /var/lib/mysql/drupal_db on my box ) to
the newly created partition (/var/lib/mysql/tmpfs in our sample):
Important: Don’t forget to stop mysql server first!
mv drupal_db tmpfs
Note: You can skip following section and go directly to Make changes permanent below
Since we are moving just one database instead of whole mysql data folder, we create a symlink to our drupal_database so mysql could still find it at its old location:
ln -s tmpfs/drupal_db drupal_db
After that you should be able to start mysql server and have drupal_db running in memory.
If you see your database in the list of databases when you start mysql then you are almost there, the final and very important step is to make changes “permanent”, so your database could survive mysql server start / stop events.
Important: If you will reboot your server / workstation without copying files from tmpfs back to hardive, you will lose the database!
Make changes permanent or survive server reboot
There are multiple ways to do this, you can either write a job which will start before and after mysql, or you can just modify mysql init script. For the sake of simplicity we will use the last mentioned method.
Usually you can find your mysql init script in /etc/init.d/mysql. Add following lines in bold to the start) section after the first else statement:
log_daemon_msg "Starting MySQL database server" "mysqld" if mysqld_status check_alive nowarn; then log_progress_msg "already running" log_end_msg 0 else # Copy drupal db into tmpfs mv /var/lib/mysql/drupal_db /var/lib/mysql/tmpfs ln -s /var/lib/mysql/tmpfs/drupal_db /var/lib/mysql/drupal_db /usr/bin/mysqld_safe > /dev/null 2>&1 &
log_failure_msg "Please stop MySQL manually and read /usr/share/doc/percona-server-server-5.1/README.Debian.gz!" exit -1 else # Copy our Drupal db back to hardrive rm /var/lib/mysql/drupal_db mv /var/lib/mysql/tmpfs/drupal_db /var/lib/mysql log_end_msg 0 fi
That’s about it! You can test if those commands by starting and stopping your mysql instance and checking your databases list.