mysqlslap result at 2014/05/30

df.txt

[root@mysql-test01 result]# df -hT
Filesystem                   Type   Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root ext4    50G  3.4G   44G   8% /
tmpfs                        tmpfs   15G     0   15G   0% /dev/shm
/dev/sda1                    ext4   485M   32M  428M   7% /boot
/dev/mapper/VolGroup-lv_hdd1 ext4    74G  1.3G   69G   2% /hdd1
/dev/mapper/VolGroup-lv_hdd2 xfs     75G  1.3G   74G   2% /hdd2
/dev/fioa                    ext4    74G  1.3G   69G   2% /fio1
/dev/fiob                    xfs     75G  1.3G   74G   2% /fio2

my.cnf

[mysqld_multi]
mysqld=/usr/bin/mysqld_safe
mysqladmin=/usr/bin/mysqladmin
log=/var/log/mysql/mysqld_multi.log

[mysqld]
user=mysql
character-set-server=utf8
long_query_time=1
log-bin=mysqld-bin

skip-character-set-client-handshake
skip-name-resolve

sync-binlog=1

relay-log=relay-bin.nnnnnn
relay_log_purge=0
#relay_log_info_repository=TABLE
relay_log_recovery=ON

event_scheduler=on

slow_query_log

innodb_file_per_table

[mysqld1]
server-id=1
socket=/fio1/mysql/mysql.sock
pid-file=/fio1/mysql/mysql.pid
datadir=/fio1/mysql/
tmpdir=/fio1/tmp
port=3310
tmpdir=/fio1/tmp
slow_query_log_file=/fio1/mysql/slow.log
log-error=/fio1/mysql/mysql.log

[mysqld2]
server-id=2
socket=/fio2/mysql/mysql.sock
pid-file=/fio2/mysql/mysql.pid
datadir=/fio2/mysql/
tmpdir=/fio2/tmp
port=3320
tmpdir=/fio2/tmp
slow_query_log_file=/fio2/mysql/slow.log
log-error=/fio2/mysql/mysql.log

[mysqld3]
server-id=3
socket=/hdd1/mysql/mysql.sock
pid-file=/hdd1/mysql/mysql.pid
datadir=/hdd1/mysql/
tmpdir=/hdd1/tmp
port=3330
slow_query_log_file=/hdd1/mysql/slow.log
log-error=/hdd1/mysql/mysql.log

[mysqld4]
server-id=4
socket=/hdd2/mysql/mysql.sock
pid-file=/hdd2/mysql/mysql.pid
datadir=/hdd2/mysql/
tmpdir=/hdd2/tmp/
port=3340
slow_query_log_file=/hdd2/mysql/slow.log
log-error=/hdd2/mysql/mysql.log

mysqlslap.sh

#!/bin/bash

for DIR in "fio1" "fio2" "hdd1" "hdd2"
do
        MYSQLSLAP_LOG="result/${DIR}_mysqlslap.log"
        DSTAT_LOG="result/${DIR}_dstat.log"
        CONCURRENCY=10
        ITERATIONS=10
        TYPE="write"

        sync
        echo 3 >  /proc/sys/vm/drop_caches
        sync

        echo "#####################################################################################" >> $MYSQLSLAP_LOG
        echo "### `date` ${DIR} / CONCURRENCY:${CONCURRENCY} / ITERATIONS:${ITERATIONS} / TYPE:${TYPE}"     >> $MYSQLSLAP_LOG
        echo "#####################################################################################" >> $MYSQLSLAP_LOG

        mysqlslap \
        --user=sbtestuser \
        --password=sbtestpass \
        --create-schema=sbtest \
        --socket=/${DIR}/mysql/mysql.sock \
        --concurrency=${CONCURRENCY} \
        --iterations=${ITERATIONS} \
        --engine=innodb \
        --auto-generate-sql \
        --auto-generate-sql-load-type=write \
        --number-of-queries=50000 \
        --create-schema=test_mysqlslap \
        >> $MYSQLSLAP_LOG

        echo ""
done

result.txt

#####################################################################################
### fio1 / CONCURRENCY:10 / ITERATIONS:10 / TYPE:write
#####################################################################################
Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 5.654 seconds
	Minimum number of seconds to run all queries: 5.392 seconds
	Maximum number of seconds to run all queries: 5.941 seconds
	Number of clients running queries: 10
	Average number of queries per client: 5000

#####################################################################################
### fio1 / CONCURRENCY:10 / ITERATIONS:10 / TYPE:write
#####################################################################################
Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 5.624 seconds
	Minimum number of seconds to run all queries: 5.382 seconds
	Maximum number of seconds to run all queries: 5.836 seconds
	Number of clients running queries: 10
	Average number of queries per client: 5000

#####################################################################################
### fio2 / CONCURRENCY:10 / ITERATIONS:10 / TYPE:write
#####################################################################################
Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 4.834 seconds
	Minimum number of seconds to run all queries: 4.614 seconds
	Maximum number of seconds to run all queries: 5.065 seconds
	Number of clients running queries: 10
	Average number of queries per client: 5000

#####################################################################################
### fio2 / CONCURRENCY:10 / ITERATIONS:10 / TYPE:write
#####################################################################################
Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 4.828 seconds
	Minimum number of seconds to run all queries: 4.638 seconds
	Maximum number of seconds to run all queries: 5.013 seconds
	Number of clients running queries: 10
	Average number of queries per client: 5000

#####################################################################################
### hdd1 / CONCURRENCY:10 / ITERATIONS:10 / TYPE:write
#####################################################################################
Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 5.728 seconds
	Minimum number of seconds to run all queries: 4.974 seconds
	Maximum number of seconds to run all queries: 6.936 seconds
	Number of clients running queries: 10
	Average number of queries per client: 5000

#####################################################################################
### hdd1 / CONCURRENCY:10 / ITERATIONS:10 / TYPE:write
#####################################################################################
Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 5.651 seconds
	Minimum number of seconds to run all queries: 4.978 seconds
	Maximum number of seconds to run all queries: 7.265 seconds
	Number of clients running queries: 10
	Average number of queries per client: 5000

#####################################################################################
### hdd2 / CONCURRENCY:10 / ITERATIONS:10 / TYPE:write
#####################################################################################
Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 3.995 seconds
	Minimum number of seconds to run all queries: 3.823 seconds
	Maximum number of seconds to run all queries: 4.202 seconds
	Number of clients running queries: 10
	Average number of queries per client: 5000

#####################################################################################
### hdd2 / CONCURRENCY:10 / ITERATIONS:10 / TYPE:write
#####################################################################################
Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 3.996 seconds
	Minimum number of seconds to run all queries: 3.799 seconds
	Maximum number of seconds to run all queries: 4.330 seconds
	Number of clients running queries: 10
	Average number of queries per client: 5000