Last updated December 26, 2010. Created by Damien Tournoud on May 19, 2009.
Edited by Stevel, chx, rfay, dereine. Log in to edit this page.
- Tweak MySQL configuration
InnoDB, while recommanded for production systems, is not ideal on a test slave because DDL operations (CREATE TABLE / DROP TABLE) tend to be much slower then MyISAM's. - Move MySQL database to tmpfs
The test slave will stress the database server by issuing a lot of heavy queries (especially DDL queries) in a short period of time. After some of those queries, the database server explicitely flushes data to disk (MySQL tries to avoid that when possible, but it still callsfsync()after a DDL operation). Moving the database files to a tmpfs filesystem makes a lot of difference. - Tweak PostgreSQL configuration
By default, the PostgreSQL server will try to make sure that updates are physically written to disk, by issuingfsync()system calls or various equivalent methods. This causes heavy I/O during testing. - Place SQLite database in memory
By keeping the SQLite database in memory, most of the disk I/O is avoided speeding up the entire process. Note that this means the database must be recreated after rebooting the computer.
Tweak MySQL configuration
Add the following to your my.cnf:
skip-innodbIts important to place this setting under the group [mysqld], otherwise it will not work.
Moving MySQL database to tmpfs
Tmpfs is a linux pseudo-filesystem that stores files and directory in memory. Files placed on a tmpfs will be lost after a reboot, but it doesn't matter for a test slave.
To move the test database to tmpfs, add the following line to your /etc/fstab:
tmpfs /var/lib/mysql/drupal_checkout tmpfs rw,nosuid,nodev,noexec,uid=<uid of the mysql user>,gid=<gid of the mysql group> 0 0<uid of the mysql user> and <gid of the mysql group> can be found in /etc/passwd and /etc/groups, respectively.
Tweak PostgreSQL configuration
To stop postgresql from writing to disk on each change, place fsync = off in postgresql.conf. Note that this can cause serious data loss in case of a crash, so use this with care on a testing database server, and NEVER on a production database.
Place SQLite database in memory
To place the sqlite database entirely in memory, on Linux you can choose a file inside /dev/shm as the database path.
| Attachment | Size |
|---|---|
| mysql-tmpfs.txt | 5.92 KB |
| mysql-reference-cfg.txt | 909 bytes |
Comments
additional setup info for v1
These are also apparently relevant to v1 clients:
Fix for cURL on Lenny: http://drupalbin.com/10366
Manual test starter: http://drupalbin.com/10367
--
deekayen.net
Some clarification on mysql-tmpfs.txt file
Found the following at http://qa.drupal.org//performance-tuning-tips-for-D7, might clarify the use of mysql-tmpfs.txt a bit.
Brief instructions:
uncomment skip-innodb in /etc/mysql/my.cnf
apt-get install rsync
Backed up /etc/init.d/mysql somewhere.
Drop the script in place.
mkdir /var/lib/.mysql
chown -R mysql:mysql /var/lib/.mysql
/etc/init.d/mysql stop
cd /var/lib/.mysql (to confirm files got copied there)
cd /var/lib/mysql
rm -r *
cd /var/lib
/etc/init.d/mysql start
cd /var/lib/mysql (to verify files moved back in)
df -h (to verify tmpfs is mounted at /var/lib/mysql)
improving the speed of Simpletest on Windows
Any pointers on how to do this on windows?
upstart script
As ubuntu 11.04 comes with a upstart script for MySQL, I modified the instructions+the script in order to work with that. See here.
Optimization with InnoDB
If for some reason you need to stay with the InnoDB engine then setting the following option may speed up tests for you:
innodb_flush_log_at_trx_commit = 0
(this goes into the [mysqld] section of the my.cnf file, or it can be set globally at runtime if you have the proper MySQL privilege)
Setting this reduced the setup time by a factor of 3 on my laptop (YMMV).
Please note that this setting is only admissible if you can live with lost transactions in case of a crash.
On a test server I think this is ok, but it is not suitable for tests that are run on systems that double as production servers.
Please read the documentation:
http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_inn...
[edit]Ah, I've seen this is already set in in http://qa.drupal.org/performance-tuning-tips-for-D7 in the "InnoDB tuning" section. But it doesn't mention the point about lost transactions.[/edit]
For anyone running
For anyone running WAMP...
First, testing seems awfully slow.
Second, trying to use the skip-innodb option may not work if you already have innodb tables in any of your databases. At least, this was the case on WAMP 2.1.
Since I wasn't able to use skip-innodb, I had to stay with innodb tables. So I tried using innodb_flush_log_at_trx_commit = 0 in my.conf (my.ini in WAMP), and it was faster but only marginally so. I tried the XML RPC test set (which is only 3 tests) and it took nearly 7 minutes to run.
MySQL ramdrive handling for MacOS X
As the tmpfs is not directly available under MacOS, I use other functions to get MySQL running in a RAM disk and put the sequence in a shell script. It's pretty straight forward and expects context on your machine:
Unfortunately I cannot attach the shell script as file, so here the source code.
#!/bin/bash
#
# This script helps to speed up Drupal testing under MacOS 10.5/10.6/(10.7 ???)
# and XAMPP (tested under XAMPP 1.7.3) by moving the MySQL data directiry into
# a 600MB sized ramdisk.
#
# Usage:
#
# Just execute it. The script will switch between RAMDISK and the hard disk
# depending on the existence of the ram disk called "mysqlramdisk". You can
# see the disk as well on the desktop.
#
#
# The normal XAMPP installation path is /Applications/XAMPP. If your path is
# not the same, modify the "xampp_root_dir" variable below.
#
# Some variables
xampp_root_dir=/Applications/XAMPP/xamppfiles
mysql_disk_data_dir=${xampp_root_dir}/var/mysql
ramdisk_name=mysqlramdisk
mysql_ram_data_dir=/Volumes/${ramdisk_name}
drupal_database_filter=drupal*
# Some functions
function sayOK() { echo -e "\033[0;32mOK\033[0m"; }
function sayFAILED() { echo -e "\033[0;31mFAILED\033[0m"; exit; }
function sayTASK() { echo -ne "$1 ... "; }
# Initial checks
function initialChecks() {
# Check if the MacOS X version fits
os_version_major=`sw_vers -productVersion | cut -f2 -d"."`
os_version_minor=`sw_vers -productVersion | cut -f3 -d"."`
sayTASK "Checking if the MacOS X version fits";
if [ $os_version_major -ne 5 -a $os_version_major -ne 6 ];then
echo -e "\033[0;31mFAILED\033[0m";
echo -e " \033[0;31mYour OS version is not 10.5 or 10.6. You can edit this script"
echo -e " and check yourself if it still works for Lion and later.\033[0m\n"
exit;
else
sayOK;
fi;
sayTASK "Checking this script is executed using sudo"
if [ "$USERNAME" != "root" ]; then sayFAILED; else sayOK; fi;
sayTASK "Checking if XAMPP installed in expected directory"
if [ -d ${xampp_root_dir} ]; then sayOK; else sayFAILED; fi;
sayTASK "Checking if MySQL data dir exists"
if [ -d ${mysql_disk_data_dir} ]; then sayOK; else sayFAILED; fi;
}
# $1=process name
function get_pid() {
ps -A | grep -v grep | grep $1 | awk '{print $1}' | head -n 1;
}
# $1=process name
function waitForProcessStopped() {
x=0
timeout=50
ppid=$(get_pid "$1")
while [ "$x" -lt $timeout -a "$ppid" != "" ]; do
ppid=$(get_pid "$1")
x=$((x+1))
sleep .1
done
}
# $1=process name
function waitForProcessStarted() {
x=0
timeout=50
ppid=$(get_pid "$1")
while [ "$x" -lt $timeout -a "$ppid" == "" ]; do
ppid=$(get_pid "$1")
x=$((x+1))
sleep .1
done
}
#
# Some main sequence ...
#
if [ -d ${mysql_ram_data_dir} ]; then
#
# Undo test envoronment, eject ramdrive
#
echo -en "\n\033[0;32m--- RAM DRIVE --> DISK DRIVE ---\033[0m\n"
initialChecks;
f=$(ls -1 ${mysql_ram_data_dir}/*.pid 2>/dev/null)
if [ -f "$f" ]; then
kill $(cat $f) >/dev/null;
sayTASK "Checking if MySQL is stopped";
waitForProcessStopped "mysqld_safe"
if ps -ax | grep -v grep | grep mysqld_safe >/dev/null; then sayFAILED; else sayOK; fi
else
sayTASK "MySQL not running";
sayOK
fi
sayTASK "Ejecting ramdisk";
hdiutil eject ${mysql_ram_data_dir} &>/dev/null
if [ -d ${mysql_ram_data_dir} ]; then sayFAILED; else sayOK; fi
sayTASK "Starting MySQL in normal disk directory";
${xampp_root_dir}/xampp startmysql >/dev/null
waitForProcessStopped "${xampp_root_dir}/xampp"
if ps -ax | grep -v grep | grep mysqld_safe >/dev/null; then sayOK; else sayFAILED; fi
else
#
# Setup test envoronment to use ramdrive
#
echo -en "\n\033[0;31m--- DISK DRIVE --> RAM DRIVE ---\033[0m\n"
initialChecks;
# Create RAM disk
diskutil erasevolume HFS+ "${ramdisk_name}" `hdiutil attach -nomount ram://1165430` >/dev/null
sayTASK "Checking if ramdisk was created"
if [ -d ${mysql_ram_data_dir} ]; then sayOK; else sayFAILED; fi;
# Stop mysql
sayTASK "Stopping mysqld";
${xampp_root_dir}/xampp stopmysql >/dev/null
if ps -ax | grep -v grep | grep mysqld_safe >/dev/null; then sayFAILED; else sayOK; fi
# Copy MySQL data direcoty to ramdisk
cp ${mysql_disk_data_dir}/* ${mysql_ram_data_dir} &>/dev/null
cp -R ${mysql_disk_data_dir}/mysql ${mysql_ram_data_dir} &>/dev/null
cp -R ${mysql_disk_data_dir}/cdcol ${mysql_ram_data_dir} &>/dev/null
cp -R ${mysql_disk_data_dir}/${drupal_database_filter} ${mysql_ram_data_dir} &>/dev/null
# Restart in RAM
sayTASK "Starting mysqld in ramdisk";
sudo -b ${xampp_root_dir}/bin/mysqld_safe --datadir=${mysql_ram_data_dir} >/dev/null &
waitForProcessStarted "${xampp_root_dir}/bin/mysqld_safe"
if ps -ax | grep -v grep | grep mysqld_safe >/dev/null; then sayOK; else sayFAILED; fi
fi;
echo "--------"; sayOK; echo " "