CentOS/Redhat: How to Install MySQL Sandbox

When you have started to work heavily with MySQL servers, and you need to create, install, configure and destroy MySQL instances, MySQL Sandbox is a great tool to automate those tasks. MySQL Sandbox allows us to deploy a MySQL test environment within seconds with simple commands.

So following is what I would do to install MySQL Sandbox on my CentOS box and create 3 mysql instances for my test environment:

1. Install Perl CPAN package:

$ yum install perl-CPAN

2. Connect to Perl MCPAN shell :

$ perl -MCPAN -e shell

**Answer ‘yes’ for the prompt. Perl will auto-configure most of the stuff for you

3. Install required Perl modules:

install Test::More
install MySQL::Sandbox

4. MySQL Sandbox is recommended to run as a non-root user. Create a user called sandbox and switch to the user’s environment:

$ useradd sandbox
$ su - sandbox

5. Download the MySQL Sandox package from Launchpad:

$ wget https://launchpadlibrarian.net/187745286/MySQL-Sandbox-3.0.47.tar.gz

6. Add MySQL sandbox into environment path of user sandbox’s bashrc:

$ vim ~/.bashrc

And append following line:

export PATH=$PATH:/home/sandbox/MySQL-Sandbox-3.0.47/bin

7. Re-login as the sandbox user to activate environment:

$ exit
$ su - sandbox

8. Download MySQL generic TAR file from MySQL Server archive page:

$ wget http://downloads.mysql.com/archives/get/file/mysql-5.6.20-linux-glibc2.5-x86_64.tar.gz

** I used MySQL 5.6.20 for this installation

Installation completed. To create a single MySQL instances, use following command:

$ make_sandbox mysql-5.6.20-linux-glibc2.5-x86_64.tar.gz

I use following command to create 3 MySQL instances for my test environment:

$ make_multiple_sandbox mysql-5.6.20-linux-glibc2.5-x86_64.tar.gz

Your instances should be ready and running within minutes. They will be created under /home/sandbox/sandboxes.

Restoring xbstream Backup Created by Percona Xtrabackup


Backing up

Once Percona Xtrabackup is installed, you can create a full backup with parallel compression as below:

$ innobackupex --stream=xbstream --parallel=4 --compress --compress-threads=2 /var/lib/mysql > /root/backup/backup.xbstream

Once completed, you should see a backup file has been created with .xbstream extension:

du -sh /root/backup/backup.xbstream
106M backup.xbstream



1. Extract the file using xbstream -x command as below:

$ xbstream -x < /root/backup/backup.xbstream

2. If you look into the current folder recursively, you should see there are a lot of files ended with .qp extension. We need to extract these files using qpress which is available in Percona repository. Install qpress as below:


$ yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
$ yum install qpress


apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
echo 'deb http://repo.percona.com/apt VERSION main' >> /etc/apt/sources.list
echo 'deb-src http://repo.percona.com/apt VERSION main' >> /etc/apt/sources.list
$ apt-get update
$ apt-get install qpress

* Replace VERSION with your Debian/Ubuntu release name

3. Extract each file ended with .qp and in their respective directory, then remove .qp file once its extracted:

$ for i in $(find -name "*.qp"); do qpress -vd $i $(dirname ${i}) && rm -f $i; done

4. You can now use the –apply-log command to prepare the MySQL data:

$ innobackupex --apply-log ./

5. Stop MySQL server:

$ service mysql stop

5. Move or remove the current data directory and use –copy-back command to let xtrabackup copy the prepared data to MySQL data directory:

$ rm -Rf /var/lib/mysql/*
$ innobackupex --copy-back ./

6. Assign correct ownership to MySQL data directory:

$ chown -Rf mysql.mysql /var/lib/mysql

7. Start the MySQL server:

$ service mysql start


Restoration completed!

MySQL: Calculate Read and Write Ratios in Percentage

I have been assigned a task to assist a client to get some idea on his database usage in MySQL Cluster before migrating them to Galera cluster. Galera scales well for reads but not for writes. So the first thing you need to do is to calculate the reads/writes ratio:

SELECT @total_com := SUM(IF(variable_name IN ('Com_select', 'Com_delete', 'Com_insert', 'Com_update', 'Com_replace'), variable_value, 0)) AS `Total`,
 @total_reads := SUM(IF(variable_name = 'Com_select', variable_value, 0)) AS `Total reads`,
 @total_writes := SUM(IF(variable_name IN ('Com_delete', 'Com_insert', 'Com_update', 'Com_replace'), variable_value, 0)) as `Total writes`,
 ROUND((@total_reads / @total_com * 100),2) as `Reads %`,
 ROUND((@total_writes / @total_com * 100),2) as `Writes %`
FROM information_schema.GLOBAL_STATUS;

The output would be as below:

| Total      | Total reads | Total writes | Reads % | Writes % |
| 1932344732 |  1899878513 |     32466219 |  98.32 |     1.68 |
1 row in set (0.00 sec)

The output calculated above is relative since the last restart. So if you just restarted your MySQL server, you may need to wait several hours at least to get a more accurate result.

Simple initial estimation; if writes percentage is less than 50%, then the schema should be running fine in Galera cluster. However, you need to totally understand the application side as well. Hope this helps people out there!

MySQL: Export Table to Splitted CSV using Stored Procedure

I have been working with Amazon Redshift lately and I need to export some huge tables (hundred millions of rows) to load them into Redshift cluster. Since Redshift supports parallel bulk loading of data files (CSV, TSV, Json), I took advantage of this feature by splitting the MySQL CSV data files before upload them to S3 for data loading. The only problem was I need to split the records evenly for the selected tables.

So, I have came out with following stored procedure. Let’s say we have two tables in MySQL that we want to export; activity_log and messaging_log.

To export and split, copy following stored procedure lines into MySQL:

DROP PROCEDURE IF EXISTS export_csv_split //
CREATE PROCEDURE export_csv_split (IN table_name VARCHAR(50), IN rows INT)
DECLARE total_row INT;
SET x=0;
SET y=1;
-- count the total rows of the select statement as @total_row
SET @SQLString1 = CONCAT('SELECT COUNT(*) INTO @total_row FROM ',table_name);
PREPARE test1 FROM @SQLString1;
EXECUTE test1;
WHILE x <= @total_row DO
-- export the rows from select statement with limit
SET @SQLString = CONCAT('SELECT * FROM ',table_name,' LIMIT ',x,',',rows,' INTO OUTFILE "/tmp/',table_name,'-',y,'.csv" FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''"'' LINES TERMINATED BY ''\n''');
PREPARE test2 FROM @SQLString;
EXECUTE test2;
SET x=x+rows;
SET y=y+1;
END //

Then, call the stored procedure and pass the table name and the number of rows per file, 10M rows per file for table activity log and 5M rows per file for table messaging_log:

mysql> call export_csv_split(activity_log,10000000);
mysql> call export_csv_split(messaging_log,5000000);

The data files will be generated under /tmp directory:

ls -1 /tmp


For another case, some huge tables need to be exported with conditions; export all records which have been created for the last 10 years:

DROP PROCEDURE IF EXISTS export_csv_split_condition //
CREATE PROCEDURE export_csv_split_condition (IN table_name VARCHAR(50), IN rows INT)
DECLARE total_row INT;
SET x=0;
SET y=1;
-- condition of the select statement
SET @condition = CONCAT('WHERE DATE(created) < ''2014-01-01'' AND DATE(created) >= ''2004-01-01''');
-- count the total rows of the select statement + condition as @total_row
SET @SQLString1 = CONCAT('SELECT COUNT(*) INTO @total_row FROM ',table_name,' ',@condition);
PREPARE test1 FROM @SQLString1;
EXECUTE test1;
WHILE x <= @total_row DO
-- export the rows from select statement + condition with limit
SET @SQLString = CONCAT('SELECT * FROM ',table_name,' ',@condition,' LIMIT ',x,',',rows,' INTO OUTFILE "/tmp/',table_name,'-',y,'.csv" FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''"'' LINES TERMINATED BY ''\n''');
PREPARE test2 FROM @SQLString;
EXECUTE test2;
SET x=x+rows;
SET y=y+1;
END //

Similar to the first case, just call the stored procedure as below:

mysql> call export_csv_split_condition('tbl_user_activities',1000000);

You may need to change the COUNT(*) to single column lookup instead, e.g: COUNT(id) to speed up the rows counting process in InnoDB/XtraDB storage engine. Hope this simple sharing can help you a lot on exporting huge tables to CSV.

Install Puppet on CentOS, Redhat, Ubuntu, Debian

Lately, I have been spending my time doing research on developing a Puppet module. As you might know, Puppet is an orchestration tool used by many sysadmins to deploy and configure servers without hassle on repeating the same installation commands over and over again.

With Puppet you just simply:

  1. Install Puppet master
  2. Define node configuration in Puppet master
  3. Install Puppet agent
  4. Let the Puppet agent deploy what you have defined

Developing Puppet module requires me to test the module’s manifest on many popular OS distributions out there. I am going to stick with the subject of this post on installing Puppet on following OS distributions:

  • RHEL 6/CentOS 6
  • RHEL 5/CentOS 5
  • Ubuntu 12.04
  • Ubuntu 14.04
  • Debian 6
  • Debian 7



Ensure the host’s date and time is synced through ntp and /etc/hosts is configured correctly. Following is the example of /etc/hosts definition that I used:    puppetmaster.local    mysql1.local   # puppet-agent    mysql2.local   # puppet-agent    mysql3.local   # puppet-agent

** The /etc/hosts must be same on all nodes so hostname can be resolved to an IP. This is required later during the certificate signing stage by puppet master.


yum install -y ntpdate
ntpdate -u my.pool.ntp.org


sudo apt-get install -y ntpdate
ntpdate -u my.pool.ntp.org

Installing Puppetlabs Repository

We’ll need to install official Puppetlabs repository on all nodes regardless of their role in puppet for the installation purposes. Install the repository definition on respective OS distribution:

RHEL 6/CentOS 6:

rpm -ivh http://yum.puppetlabs.com/puppetlabs-release-el-6.noarch.rpm

RHEL 5/CentOS 5:

rpm -ivh http://yum.puppetlabs.com/puppetlabs-release-el-5.noarch.rpm

Ubuntu 12.04 (Precise):

wget https://apt.puppetlabs.com/puppetlabs-release-precise.deb
sudo dpkg -i puppetlabs-release-precise.deb

Ubuntu 14.04 (Trusty):

wget https://apt.puppetlabs.com/puppetlabs-release-trusty.deb
sudo dpkg -i puppetlabs-release-trusty.deb

Debian 6 (Squeeze):

wget https://apt.puppetlabs.com/puppetlabs-release-squeeze.deb
dpkg -i puppetlabs-release-squeeze.deb

Debian 7 (Wheezy):

wget https://apt.puppetlabs.com/puppetlabs-release-wheezy.deb
dpkg -i puppetlabs-release-wheezy.deb


Installing Puppet Master

On puppetmaster.local node, run following command to install Puppet master:


yum install -y puppet-server openssl


sudo apt-get update
sudo apt-get install -y puppetmaster openssl


Installing Puppet Agent

On all puppet agent nodes (mysql1.local, mysql2.local, mysql3.local), install puppet agent and its dependencies:


yum install -y puppet facter openssl


sudo apt-get update
sudo apt-get install -y puppet facter openssl


Signing the Certificate Authority

Puppet communicates through a secured channel with SSL. When puppet agent runs for the first time, it will auto-generate a new SSL and puppet master must sign it before all the communications begin. Run following command on each agent node:

$ puppet agent --server=puppetmaser.local --no-daemonize --verbose
Info: Creating a new SSL key for mysql1.local
Info: Caching certificate for ca
Info: csr_attributes file loading from /etc/puppet/csr_attributes.yaml
Info: Creating a new SSL certificate request for ccpuppet.local
Info: Certificate Request fingerprint (SHA256): 6F:8B:92:46:B0:3F:04:0A:4F:8D:BD:56:77:24:77:50:1C:E9:F4:EE:C6:00:5E:82:4F:B0:85:B5:26:72:43:E0
Info: Caching certificate for ca

This will generate a certificate to be signed by the puppet master. Now in the puppet master, list the certificate authority (CA):

$ puppet ca list
mysql1.local  (SHA256) 6F:8B:92:46:B0:3F:04:0A:4F:8D:BD:56:77:24:77:50:1C:E9:F4:EE:C6:00:5E:82:4F:B0:85:B5:26:72:43:E0

Sign the CA for this agent:

$ puppet ca sign mysql1.local
Notice: Signed certificate request for mysql1.local
Notice: Removing file Puppet::SSL::CertificateRequest ccpuppet.local at '/var/lib/puppet/ssl/ca/requests/mysql1.local.pem'
...the key..."

Repeat the above steps on the other nodes, mysql2.local and mysql3.local. Now the puppet master should able to communicate with its agents securely.


Configure Puppet Agent

The last step is to update /etc/puppet/puppet.conf and add following line under [main] directive:


** If you do not configure as above, you will need to add –server=puppetmaster.local on each of the puppet agent command below.

Now you can test from the agent node:

puppet agent --test


Deploy MySQL through Puppet

Puppet is now ready. Let’s deploy a mysql server with the simplest way. Go to Puppet Forge and look for a puppet module called puppetlabs-mysql. To install this module, run following command on to the puppet master node:

puppet module install puppetlabs-mysql

On the puppetmaster.local, create a puppet manifest to define how the agent should deploy at /etc/puppet/manifests/site.pp:

# /etc/puppet/manifests/site.pp
# Default node - this is compulsory
node "default" {
# Define the agent nodes
node "mysql1.local", "mysql2.local", "mysql3.local" {
 class { '::mysql::server':
 root_password => 'strongpassword'
 class { '::mysql::client':
 package_ensure => 'present'

Above definition will tell Puppet to install a MySQL server and client package on mysql1.local, mysql2.local and mysql3.local. To immediately start the deployment, go to the agent node and run:

puppet agent --test

Or, wait for the Puppet agent service to apply the catalog automatically (depending on the runinterval value, default is 30 minutes).

Once done, you will have three MySQL servers ready to serve! Imagine how much time you would save if you have many servers, applications, softwares and configurations to maintain.

Galera Cluster – Simpler way to view GRA file content

Galera Cluster (MySQL from Codership, Percona XtraDB Cluster, MariaDB Galera Cluster) generates a GRA log files if it fails to apply the writeset on the target node. This files exists in the MySQL data directory. You can get an overview of the file (if exist) by listing your MySQL data directory (in my case, the data directory is at /var/lib/mysql):

$ ls -1 /var/lib/mysql | grep GRA

MySQL Performance Blog has covered this topic in well-explained. I’m going to make this simple. Download the script here and copy it to your /usr/bin directory:

wget http://blog.secaserver.com/files/grareader -P /usr/bin/
chmod 755 /usr/bin/grareader

Just run following command to simply convert the GRA log file to a human-readable output:

grareader [gra_log_file]

Here is the example output:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#140114 3:12:42 server id 3 end_log_pos 120 Start: binlog v 4, server v 5.6.15-log created 140114 3:12:42 at startup
# at 120
#140114 3:12:43 server id 3 end_log_pos 143 Stop
# at 143
#140507 14:55:42 server id 4 end_log_pos 126 Query thread_id=3173489 exec_time=0 error_code=0
use `test_shop`/*!*/;
SET TIMESTAMP=1399445742/*!*/;
SET @@session.pseudo_thread_id=3173489/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
ALTER TABLE `tblreshipment_header` DROP `ShipmentStatus`
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET [email protected]_COMPLETION_TYPE*/;

You can download the script here or copy and paste the code:

# Convert Galera GRA_* files to human readable output
# Usage: grareader 
# Example: grareader /var/lib/mysql/GRA_1_1.log
## GRA header file path
[ ! -e $input ] && echo 'Error: File does not exist' && exit 1
        wget_bin=`command -v wget`
        [ -z "$wget_bin" ] && echo 'Error: Unable to locate wget. Please install it first' && exit 1
        echo "Downloadling GRA-Header file into $path"
        $wget_bin --quiet $download_url -P $path
        [ $? -ne 0 ] && echo 'Error: Download failed' && exit 1
        mysqlbinlog_bin=`command -v mysqlbinlog`
        [ -z "$mysqlbinlog_bin" ] && echo 'Error: Unable to locate mysqlbinlog binary. Please install it first' && exit 1
        [ ! -e $gra_header_path ] && echo 'Error: Unable to locate GRA header file' && get_gra_header
cat $gra_header_path >> $tmp_path
cat $input >> $tmp_path
echo ''
$mysqlbinlog_bin -v -v -v $tmp_path
echo ''
rm -rf $tmp_path


Hope this could help make your Galera administrative task simpler!

Percona Server Installation Error – libssl.so.10 and libcrypto.so.10

I stumbled upon one error when installing Percona Server and socat via yum repository with following error:

--> Processing Dependency: libssl.so.10(libssl.so.10)(64bit) for package: socat-
--> Processing Dependency: libcrypto.so.10(libcrypto.so.10)(64bit) for package: socat-

It turns out that:

“Red Hat upgraded the version of OpenSSL in EL6 from 1.0.0 to 1.0.1 during the 6.4-6.5 cycle, in order to resolve a years-old feature request. This package is no longer binary compatible, and programs that were built against OpenSSL 1.0.0 must be rebuilt from source against 1.0.1.”

What do we need to do then?

Luckily the package is available at IUS repository:

rpm -Uhv http://dl.iuscommunity.org/pub/ius/stable/Redhat/6/x86_64/epel-release-6-5.noarch.rpm
rpm -Uhv http://dl.iuscommunity.org/pub/ius/stable/Redhat/6/x86_64/ius-release-1.0-13.ius.el6.noarch.rpm
yum install yum-plugin-replace
yum replace --enablerepo=ius-archive openssl --replace-with openssl10

(Press y for any question)

Verify the dependent libraries are exist (libcrypto.so.10 and libssl.so.10) :

ls /usr/lib64/ | grep -e ssl.so -e crypto.so

Then, try again with the Percona Server installation command. In some cases, you might need to remove the installed epel-release package since it’s a little bit outdated with the current release 6.8.

Hope the solution will help you guys out there!



Sysbench 0.5 + Ubuntu 14.04 (Trusty) + Percona Server or XtraDB Cluster

I need to perform benchmarks on Percona XtraDB Cluster and Percona Server directly from my Ubuntu 14.04 box. Sysbench 0.5 is the latest stable to date and it supports benchmarking Galera cluster. If you are running on Sysbench 0.4, you would most likely encounter one of following errors if you are running it against a Galera cluster with high value of threads:

ALERT: failed to execute mysql_stmt_execute(): Err1317 Query execution was interrupted
ALERT: failed to execute mysql_stmt_execute(): Err1062 Duplicate entry '2199902' for key 'PRIMARY'

This post presumes that you have already installed standard Percona related packages including the apt-get repository to run a MySQL server (as instructed on Percona’s  documentation page).


Installing Sysbench 0.5

1. Install compiler tools and required packages:

$ sudo apt-get install -y gcc autoconf automake make libtool libssl-dev libcrypto++9

2. Install mysql_config (available from Percona repository):

$ sudo apt-get install -y libperconaserverclient18-dev

3. Get Sysbench from Launchpad:

$ bzr branch lp:sysbench

4. Compile and install sysbench:

$ cd sysbench
$ ./autogen.sh
$ ./configure --prefix=/usr --mandir=/usr/share/man
$ make
$ sudo make install

5. Create LUA template directory:

$ sudo mkdir /usr/share/sysbench/tests/db -p
$ sudo cp sysbench/tests/db/* /usr/share/sysbench/tests/db

6. Verify if sysbench is running in the correct version:

$ sysbench --version
sysbench 0.5

Running Sysbench

1. Before starting the benchmark, we need to prepare the test data. Following command will generate a table consists of 20 million rows on a Galera Cluster through HAproxy which running on port 33306:

$ sysbench \
--db-driver=mysql \
--mysql-table-engine=innodb \
--oltp-table-size=20000000 \
--mysql-host= \
--mysql-port=33306 \
--mysql-user=sbtest \
--mysql-password=password \
--test=/usr/share/sysbench/tests/db/oltp.lua \

Or we can generate 8 tables consists of 10M rows in parallel with 8 threads (total of 80M rows of data set):

$ sysbench \
--db-driver=mysql \
--mysql-table-engine=innodb \
--oltp-table-size=10000000 \
--oltp-tables-count=8 \
--num-threads=8 \
--mysql-host= \
--mysql-port=33306 \
--mysql-user=sbtest \
--mysql-password=password \
--test=/usr/share/sysbench/tests/db/parallel_prepare.lua \

2. Start the benchmark against Galera cluster:

$ sysbench \
--db-driver=mysql \
--num-threads=8 \
--max-requests=5000000 \
--oltp-table-size=20000000 \
--oltp-test-mode=complex \
--test=/usr/share/sysbench/tests/db/oltp.lua \
--mysql-host= \
--mysql-port=33306 \
--mysql-user=sbtest \
--mysql-password=password \

You can perform other tests based on the LUA templates exist under /usr/share/sysbench/tests/db. Just change the –test parameter to the fullpath of the template file and you are good to go.

Details on this can be found at http://www.percona.com/docs/wiki/benchmark:sysbench:olpt.lua


Replacing OpenJDK with Oracle JDK on CentOS 6

I encountered some issues with an application running on Java. I thought it would be fine if running on standard OpenJDK release available at CentOS repository since it is really easy to install with yum, but it wasn’t. The application does required JDK from Oracle release (which is lame).

So here what you should do when you want to replace OpenJDK with Java SE JDK:

1. Check the current OpenJDK version. I am going to retain the same version if possible:

java -version
java version "1.6.0_30"
OpenJDK Runtime Environment (IcedTea6 1.13.3) (rhel-
OpenJDK 64-Bit Server VM (build 23.25-b01, mixed mode)

Check the current RPM package name:

$ rpm -qa | grep openjdk

2. Download Java SE Development Kit 6 update 30, which is equivalent to OpenJDK 1.6.0_30. There is no way (AFAIK) that we can download it directly using curl or wget. You need to have an account with Oracle. Sign up using your web browser and you will get a temporary link similar to below. Copy it from  your web browser and use wget to download it directly to the box:

$ wget -O jdk-6u30-linux-x64-rpm.bin http://download.oracle.com/otn/java/jdk/6u30-b12/jdk-6u30-linux-x64-rpm.bin?AuthParam=1397834288_32d1181063a10cd15522d23abbc5e4f7

**You should do this quick. The link will be expired within minutes.

3. Remove the installed OpenJDK package:

$ yum remove -y java-1.6.0-openjdk

4. Install Oracle JDK:

$ chmod 755 jdk-6u30-linux-x64-rpm.bin
$ ./jdk-6u30-linux-x64-rpm.bin

5. Verify the new Java version:

java -version
java version "1.6.0_30"
Java(TM) SE Runtime Environment (build 1.6.0_30-b12)
Java HotSpot(TM) 64-Bit Server VM (build 20.5-b03, mixed mode)

Now my application works like a charm! The new path to JRE now is /usr/java/jdk1.6.0_30/jre/

Simple Benchmark of your MySQL Server

Installing Sysbench

The easiest way to perform benchmark on a MySQL server is by using sysbench. To install:

Debian/Ubuntu based:

$ apt-get install sysbench

RedHat/CentOS/Fedora based:

$ yum install sysbench

Preparing Sysbench

If we have a MySQL server ready, let’s create a database for sysbench called sbtest.

mysql> CREATE SCHEMA sbtest;

It is better to increase the default MySQL max_connections (which is too low for this test):

mysql> SET GLOBAL max_connections = 1000;

Let’s prepare the sample data for benchmarking with 2 million rows:

$ sysbench --db-driver=mysql --test=oltp --mysql-table-engine=innodb --oltp-table-size=2000000 --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=myR00tP4ssW0rd prepare

Perform the Benchmark

Now let’s the benchmarking begin. We will start an OLTP (online transactions processing) benchmark test with 8 threads and 10000 max-requests then send the output to a file called sysbench.log:

$ sysbench --db-driver=mysql --test=oltp --num-threads=8 --max-requests=10000 --oltp-table-size=2000000 --oltp-test-mode=complex --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=myR00tP4ssW0rd run >> sysbench.log

Repeat the steps for 16, 32, 64, 128, 256 and 512 threads and append the output to sysbench.log:

$ for i in 16 32 64 128 256 512; do sysbench --db-driver=mysql --test=oltp --num-threads=$i --max-requests=10000 --oltp-table-size=2000000 --oltp-test-mode=complex --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=myR00tP4ssW0rd run >> sysbench.log ; done

We will then need to convert the sysbench output to CSV format so we could plot it to a graph using gnuplot.

$ cat sysbench.log | egrep " cat|threads:|transactions:" | tr -d "\n" | sed 's/Number of threads: /\n/g' | sed 's/\[/\n/g' | sed 's/[A-Za-z\/]\{1,\}://g'| sed 's/ \.//g' | awk {'print $1 $3'} | sed 's/(/\t/g' > sysbench.csv

The content of sysbench.csv should be similar as below:

8    274.53
16   397.47
32   442.79
64   516.90
128  503.36
256  414.35
512  445.67

Plotting Graph

I will use GNUplot for this task. To install:

yum install gnuplot #redhat/centos based
apt-get install gnuplot #debian/ubuntu based

Create a file called mygraph, and then paste following lines:

# output as png image
set terminal png
# save file to "benchmark.png"
set output "benchmark.png"
# graph title
set title "Benchmark for Sysbench"
# aspect ratio for image size
set size 1,1
# enable grid on y and x axis
set grid y
set grid x
# x-axis label
set xlabel "Threads"
# y-axis label
set ylabel "Transactions (tps)"
# plot data from sysbench.csv
plot "sysbench.csv" using (log($1)):2:xtic(1) with linesp notitle

Run following command to produce the graph based on our template:

$ gnuplot mygraph

And you should see it generates a png image which you can view it similar as below:


That’s it. The benchmark results are well-presented in the graph. We can conclude that our MySQL server was performing nicely up until 64 threads then it appears to have some dropping after that.