Benchmarking MySQL

Red Hat / CentOS Installation

yum -y install mysql-server mysql-bench
service mysqld start
# Make sure you have a root user created:
mysqladmin -u root password rootsqlpassword
# Navigate to the right directory:
cd /usr/share/sql-bench # Red Hat/CentOS

Ubuntu Installation

First make sure the right packages are installed. Root user is created at installation time.
apt-get install -y mysql-server
/etc/init.d/mysql start # Ubuntu

# With Ubuntu the benchmark is no longer installed with mysql-server.
# If you are a masochist you can check out the whole source from launchpad:
# bzr checkout lp:mysql-server/5.1 mysql
# or use the repository I have set up on google code:
apt-get install -y subversion
svn checkout http://sql-bench.googlecode.com/svn/5.0 sql-bench
cd sql-bench # Navigate to the right directory

Running the tests

Ubuntu required me to create a test database. (Create a test2 as well if you want to test two threads simultaneously.
read RPW #enter the root sql password at the prompt
mysqladmin -u root -p$RPW create test
OPT="--user root --password"
date; perl run-all-tests $OPT $RPW; date
perl test-wisconsin $OPT $RPW --loop-count 100 # run the wisconsin test 100 times
# for two threads, open a second window and run the following at the same time:
date; perl run-all-tests $OPT $RPW; date
# to run on another host:
date; perl run-all-tests $OPT $RPW --database test2 --host 192.168.1.102 ; date
# to remove afterwards (Ubuntu)
apt-get remove -y mysql-server; apt-get -y autoremove

To run from an Ubuntu live CD

# open up a terminal window
sudo su
RPW=xxxxxx #use this same password when prompted by mysql
OPT="--user root --password"
apt-get update; apt-get install -y mysql-server subversion #may fail to start mysql, because of apparmor
/etc/init.d/apparmor stop # or you won't be able to start mysql
/etc/init.d/mysql start # this was attempted by the installer
mysqladmin -u root -p$RPW create test
# install the tests as above and cd to the test directory
date; perl run-all-tests $OPT $RPW; date
perl test-wisconsin $OPT $RPW --loop-count 100 # run the wisconsin test 100 times

To run from an Ubuntu 8.04 (Hardy) live CD

# open up a terminal window
sudo su
RPW=xxxxxx #use this same password when prompted by mysql
OPT="--user root --password"
apt-get update; apt-get install -y mysql-server #will fail to start mysql, because of apparmor
/etc/init.d/apparmor stop # or you won't be able to start mysql
/etc/init.d/mysql start # this was attempted by the installer
cd /usr/share/mysql/sql-bench
mysqladmin -u root -p$RPW create test
date; perl run-all-tests $OPT $RPW; date
perl test-wisconsin $OPT $RPW --loop-count 100 # run the wisconsin test 100 times

To run tests in parallel

# create a second test database:
mysqladmin -u root -p$RPW create test2
# Open two windows. Cut and paste the OPT and RPW lines into both (with the correct password)
#In the first, get ready to run:
date; perl test-insert $OPT $RPW --database test2 ; date
# In the second run the following, and then hit enter in the first console
perl test-alter-table $OPT $RPW ; perl test-connect $OPT $RPW ; perl test-select $OPT $RPW ; perl test-ATIS $OPT $RPW ; perl test-big-tables $OPT $RPW ; perl test-wisconsin $OPT $RPW
echo "2222222222222222222222222222222222222222222222222"
perl test-alter-table $OPT $RPW ; perl test-connect $OPT $RPW ; perl test-select $OPT $RPW ; perl test-ATIS $OPT $RPW ; perl test-big-tables $OPT $RPW ; perl test-wisconsin $OPT $RPW
echo "3333333333333333333333333333333333333333333333333"
perl test-alter-table $OPT $RPW ; perl test-connect $OPT $RPW ; perl test-select $OPT $RPW ; perl test-ATIS $OPT $RPW ; perl test-big-tables $OPT $RPW ; perl test-wisconsin $OPT $RPW
echo "4444444444444444444444444444444444444444444444444"
perl test-alter-table $OPT $RPW ; perl test-connect $OPT $RPW ; perl test-select $OPT $RPW ; perl test-ATIS $OPT $RPW ; perl test-big-tables $OPT $RPW ; perl test-wisconsin $OPT $RPW
echo "Other thread should be done by now!"

# The time of the test-insert should be measured while these tests repeat around 3.5 times
# note that there may be a minor error in the insert benchmark, probably due to the default database not being INNODB, but it is a bit of a pain forcing this to change.