Mysql benchmark
This one took quite a few hours. There were two ways to do it, one is a simple yum install and run, the other is what I did. I chose to do the clean and isolated model so that it can be run on any system, even existing ones.
This will grab source, extract to a standalone directory, run as current user, bind to a different non-privileged port to avoid conflict, set a dummy password, and kill itself when done. One more note, performance may vary where the db is stored, so running in /dev/shm is ideal, but I leave that to the person running as it will run in $PWD. Not everyone has access to 16GB /dev/shm stores...
Last part, I don't know which tests are useful so I tee'd the output into a log that can be culled. You can also choose to run single tests instead of all tests, which would speed things up.
This was tricky, mysql really wants to run as root and write in /var/lib... took hours of testing and tweaking.
I have no results published and I dare not run it on my old Athlons
. This is a really slow test, about 27 mins to run all tests on my dual E5-2665 running in /dev/shm. I will be first to admit that I don't necessarily play with mysql enough to know how to tune so there are probably flags or configs to change to make it faster/better.
Lastly, these are the canned benchmarks with mysql. There are many more, but now that we have an isolated instance, we can throw others at it if they are better/more preferred.
Code:
#!/bin/bash
cores=$(grep "processor" /proc/cpuinfo | wc -l)
# If the system has a ramdisk at /dev/shm, recomend to use that. Decided not to make it mandatory since we don't know how much RAM a system has.
# Space used after extracting and tests is about 2.5GB and Linux defaults to 50% RAM as ramdisk.
mysqlbuild=$PWD/chroot-mysql
if [ ! -e mysql-5.6.15.tar.gz ]
then
wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.15.tar.gz
fi
if [ ! -e cmake-2.8.12.1-Linux-i386.tar.gz ]
then
wget http://www.cmake.org/files/v2.8/cmake-2.8.12.1-Linux-i386.tar.gz
fi
tar xfz mysql-5.6.15.tar.gz
tar xfz cmake-2.8.12.1-Linux-i386.tar.gz
cd mysql-5.6.15
echo "Building configs..."
../cmake-2.8.12.1-Linux-i386/bin/cmake -Wno-dev -DCMAKE_INSTALL_PREFIX=$mysqlbuild -DMYSQL_TCP_PORT=5615 . 2>&1 >> /dev/null
# Using half the cores on the system to speed up build, but not too many cores. If you use too many, you have dependancies that cause build to fail
echo "Compiling..."
make -j $(($cores / 2)) 2>&1 >> /dev/null
echo "Installing..."
make install
cd $mysqlbuild
scripts/mysql_install_db
# Create a simple config, using an unprivledged port
echo "[mysqld]" > my.cnf
echo "basedir = $mysqlbuild" >> my.cnf
echo "port = 5615" >> my.cnf
bin/mysqld_safe --defaults-file=my.cnf &
sleep 10
# Set a disposable password
./bin/mysqladmin -u root password 'newdummypassword'
echo "Running tests, this will take a long time..."
cd sql-bench
./run-all-tests --user='root' --password='newdummypassword' --host='localhost.:5615' | tee output.txt
# Cleanup Mysqld instance
kill -TERM `cat $mysqlbuild/data/$HOSTNAME.pid`
echo "All mysql databases stopped"