MySQL on tmpfs

Entry at the end of /etc/fstab:

tmpfs /var/lib/mysql/drupal_checkout/ tmpfs size=32M    0       0

Entry in /etc/init.d/mountall.sh to copy the database files into the right location on start up:
(N.B. This is for Ubuntu Jaunty, other distributions are likely to use a different file)
Just after the "post_mountall" line I have the line:
cp -ar /var/lib/mysqlsrc/drupal_checkout/* /var/lib/mysql/drupal_checkout


To create the bare database files in the first place:

1. Ensure MySQL is running and create the database drupal_checkout.
2. Set permissions on the database (via MySQL).
3. Stop MySQL and issue:
   
   mkdir -p /var/lib/mysqlsrc/drupal_checkout
cp -ar /var/lib/mysql/drupal_checkout/* /var/lib/mysqlsrc/drupal_checkout

Note that I'm using this setup as I'm just running the SimpleTest tests directly from the Drupal 7 HEAD checkout.


For a full PIFR client I would recommend the following:
Modify /etc/rc.local (or what ever is appropriate for your distribution) and issue the required SQL commands via mysql -u root -p ... to setup the database. Then set the permissions for drupal_checkout; these permissions should only need to be set once and then they will be remembered in the MySQL metabase.


For me, and I'm testing this on a Pentium III, 500Mhz at the moment ;) the Node* class of tests resulted in a performance improvement of 5%:
Original time taken: 8m12s
New time taken: 7m45s
Percentage improvement: 5%


Final notes:

• MySQL won't start if /var/lib/mysql is on ramfs, however it doesn't check any of the subfolders ;)
  This behaviour may or may not last and there is always a risk with ramfs that the files (i.e. database) could grow so large that the kernel has to invoke it's Out Of Memory killer :( . tmpfs is better in that any growth over the specified amount will use swap. See http://www.thegeekstuff.com/2008/11/overview-of-ramfs-and-tmpfs-on-linux/ for more details.
• I choose 32M as rough guestimate of size and I haven't see it get close enough to that for me to increase that value.