Benchmarking your MySQL servers

Benchmarking tools like Sysbench and DBT2 has helped alot of DBAs in measuring their MySQL databases performance. By benchmarking you will really know how far your current setup will go. In this part, you will learn how to install the sysbench in Ubuntu and other Enterprise linux.

1. Installing sysbench to Ubuntu is never been easy as issuing the apt-get command. You can also download the source from sourceforge.

In ubuntu, execute below.

sudo apt-get install sysbench

2. Installing to enterprise linux like Oracle Linux/ Red Hat Linux. Download the rpm file from or any sites providing rpm downloads. You can also check this source.

3. Once downloaded try to install it if it will not find the dependencies file.

rpm -ivh sysbench-04.12-5.el6.x86_64.rpm

In this example, i used a 64-bit machine with Oracle Linux 6.0 installed. There are required dependencies before sysbench will be installed.

1. It will require that the machine has a GCC Compiler. To install it,

yum install gcc

Yum will not work to unregistered linux, so to get this done you need to create a local repository.

2. Other dependencies; (openssl-1.0.0-4.el6.x86_64.rpm) (krb5-libs-1.8.2-3.el6..x86_64.rpm) (openldap-2.4.19-15.el6..x86_64.rpm) (postgresql-libs-8.4.4-2.el6.x86_64.rpm);;; (glibc-2.12-1.7.el6.x86_64.rpm) (nss-softokn-freebl-3.12.7-1.1.el6..x86_64.rpm)

All these dependencies are available in your Linux DVD/ISO Installers.

Sysbench Test Modes

1. Create 'sbtest' database first
2. Prepare the database

sysbench --db-driver=mysql --test=oltp –mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-socket=/var/lib/mysql/mysql.sock --mysql-user=root --mysql-password=yourpassword prepare
This command will create 'sbtest' table and inserts 1M records

3. OLTP Test

OLTP Read Only

sysbench --db-driver=mysql --num-threads=16 --max-requests=100000 --test=oltp --oltp-table-size=1000000 –mysql-socket=/var/lib/mysql/mysql.sock --oltp-read-only --mysql-user=root --mysql-password=yourpassword run

OLTP Read+Write

sysbench --db-driver=mysql --num-threads=16 --max-requests=100000 --test=oltp --oltp-table-size=1000000 –mysql-socket=/var/lib/mysql/mysql.sock --oltp-test-mode=complex --mysql-user=root --mysql-password=admin run
this command will run the actual benchmark with 16 client threads, limiting the total number of request by 100,000

4. CPU Test

sysbench --test=cpu --cpu-max-prime=20000 run

5. Thread Test - This test mode was written to benchmark scheduler performance.

sysbench --num-threads=64 --test=threads --thread-yields=100 --thread-locks=2 run