mysqlslap result at 2014/06/02 case2

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

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

key_buffer_size                = 128M
max_allowed_packet             = 1M
table_open_cache               = 1024
sort_buffer_size               = 2M
net_buffer_length              = 8K
read_buffer_size               = 2M
read_rnd_buffer_size           = 8M
myisam_sort_buffer_size        = 16M
query_cache_size               = 128M
thread_cache_size              = 100
max_connections                = 400

binlog_format                  = mixed
expire_logs_days               = 2

# innodb
innodb_buffer_pool_size        = 5120M
innodb_log_file_size           = 256M
innodb_log_buffer_size         = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout       = 5
innodb_file_format             = Barracuda
innodb_flush_method            = O_DIRECT
innodb_read_io_threads         = 4
innodb_write_io_threads        = 4


[mysqld1]
server-id=1
socket=/fio1/mysql/mysql.sock
pid-file=/fio1/mysql/mysql.pid
datadir=/fio1/mysql/
tmpdir=/fio1/tmp/
port=3310
slow_query_log_file=/fio1/mysql/slow.log
log-error=/fio1/mysql/mysql.log
#innodb_log_group_home_dir=/hdd1/fio1logs/
#innodb_data_home_dir=/hdd1/fio1logs/
#log-bin=/hdd1/fio1logs/mysqld-bin

[mysqld2]
server-id=2
socket=/fio2/mysql/mysql.sock
pid-file=/fio2/mysql/mysql.pid
datadir=/fio2/mysql/
tmpdir=/fio2/tmp/
port=3320
slow_query_log_file=/fio2/mysql/slow.log
log-error=/fio2/mysql/mysql.log
#innodb_log_group_home_dir=/hdd2/fio2logs/
#innodb_data_home_dir=/hdd2/fio2logs/
#log-bin=/hdd2/fio2logs/mysqld-bin

[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
#innodb_log_group_home_dir=/fio1/hdd1logs/
#innodb_data_home_dir=/fio1/hdd1logs/
#log-bin=/fio1/hdd1logs/mysqld-bin

[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
#innodb_log_group_home_dir=/fio1/hdd2logs/
#innodb_data_home_dir=/fio1/hdd2logs/
#log-bin=/fio1/hdd2logs/mysqld-bin

mysqlslap.sh

#!/bin/bash

rm -f result/*

DSTAT_LOG="result/mysqlslap_dstat.csv"

dstat -tclmdrn --output $DSTAT_LOG 3 &
PID=`echo $!`

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

	sync
	echo 3 >  /proc/sys/vm/drop_caches
	sync
	
	echo "########################################################################################################" >> $MYSQLSLAP_LOG
	echo "### `date '+%Y%m%d%H%M%S'` ${DIR} / 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=${TYPE} \
	--number-of-queries=${NUMBER_OF_QUERIES} \
	>> $MYSQLSLAP_LOG

	echo ""
done

kill $PID

mysqlslap_dstat.csv

"Dstat 0.7.0 CSV output"
"Author:","Dag Wieers <[email protected]>",,,,"URL:","http://dag.wieers.com/home-made/dstat/"
"Host:","mysql-test01.hq.ecnavi.info",,,,"User:","root"
"Cmdline:","dstat -tclmdrn --output result/mysqlslap_dstat.csv 3",,,,"Date:","03 Jun 2014 03:35:24 JST"

"system","total cpu usage",,,,,,"load avg",,,"memory usage",,,,"dsk/total",,"io/total",,"net/total",
"date/time","usr","sys","idl","wai","hiq","siq","1m","5m","15m","used","buff","cach","free","read","writ","read","writ","recv","send"
03-06 03:35:24,1.198,0.479,97.967,0.335,0.003,0.017,2.070,2.330,2.660,6419656704.0,2359296.0,20721664.0,25094672384.0,650695.965,2958533.320,37.635,220.870,0.0,0.0
03-06 03:35:27,38.130,18.259,38.331,4.111,0.242,0.927,2.150,2.340,2.670,6430507008.0,26959872.0,36200448.0,25043742720.0,4743168.0,45415082.667,1156.0,10496.333,170.0,134.0
03-06 03:35:30,39.518,17.831,37.510,4.056,0.241,0.843,2.150,2.340,2.670,6443929600.0,51433472.0,52908032.0,24989138944.0,4814165.333,43603285.333,1175.667,10659.0,348.667,39.333
03-06 03:35:33,38.480,18.472,37.874,4.042,0.243,0.889,2.210,2.350,2.670,6458986496.0,75874304.0,68141056.0,24934408192.0,4797781.333,43849045.333,1171.333,10615.333,294.667,0.0
03-06 03:35:40,19.045,9.084,69.025,2.183,0.156,0.507,2.200,2.350,2.660,6471917568.0,94732288.0,84353024.0,24886407168.0,4897450.667,70860800.0,1174.0,11634.0,1486.667,460.667
03-06 03:35:42,39.391,17.856,37.619,4.044,0.182,0.909,2.260,2.360,2.660,6482739200.0,98390016.0,98136064.0,24858144768.0,4287146.667,38076416.0,1046.333,9474.0,723.333,234.0
03-06 03:35:45,39.379,18.138,37.243,4.152,0.282,0.806,2.260,2.360,2.660,6496178176.0,105877504.0,115458048.0,24819896320.0,5102250.667,42554709.333,1184.0,10632.333,526.0,405.333
03-06 03:35:48,39.032,18.065,37.621,4.113,0.282,0.887,2.240,2.350,2.660,6506823680.0,111452160.0,132333568.0,24786800640.0,4800512.0,45081941.333,1171.0,10644.0,1519.667,1143.333
03-06 03:35:51,38.887,18.112,37.838,4.074,0.242,0.847,2.300,2.360,2.660,6520643584.0,118587392.0,147316736.0,24750862336.0,4775936.0,44933120.0,1166.333,10675.667,806.667,585.333
03-06 03:35:54,37.201,17.323,39.878,4.462,0.243,0.892,2.300,2.360,2.660,6530514944.0,123207680.0,164290560.0,24719396864.0,4844202.667,47101269.333,1124.0,10385.333,600.667,390.0
03-06 03:35:57,38.864,18.043,37.857,4.068,0.242,0.926,2.440,2.390,2.670,6538608640.0,123256832.0,179859456.0,24695685120.0,4774570.667,44610901.333,1165.667,10574.0,512.0,173.333
03-06 03:36:00,38.743,18.204,37.737,4.068,0.282,0.967,2.440,2.390,2.670,6550736896.0,127270912.0,196218880.0,24663183360.0,4773205.333,44670976.0,1165.333,10620.333,328.667,0.0
03-06 03:36:03,38.853,18.134,37.641,4.079,0.283,1.010,2.560,2.410,2.680,6562209792.0,127270912.0,211738624.0,24636190720.0,4790954.667,42711722.667,1169.667,10636.333,187.333,0.0
03-06 03:36:06,38.835,18.434,37.470,4.056,0.241,0.964,2.680,2.440,2.680,6581641216.0,127295488.0,227180544.0,24601292800.0,4767744.0,44791125.333,1164.0,10604.667,2225.0,1505.333
03-06 03:36:09,38.828,18.141,37.576,4.162,0.283,1.010,2.680,2.440,2.680,6594326528.0,127315968.0,243437568.0,24572329984.0,4801877.333,45465600.0,1172.667,10710.333,264.667,0.0
03-06 03:36:12,38.691,18.134,37.843,4.120,0.323,0.889,2.780,2.470,2.690,6603657216.0,128339968.0,259358720.0,24546054144.0,4771840.0,46530560.0,1164.667,10612.667,427.667,21.333
03-06 03:36:15,38.988,17.854,37.773,4.170,0.283,0.931,2.780,2.470,2.690,6618361856.0,128704512.0,275603456.0,24514740224.0,4785493.333,46507349.333,1168.667,10685.333,230.0,0.0
03-06 03:36:18,33.779,16.342,44.688,4.217,0.203,0.770,2.880,2.490,2.700,6618009600.0,1363968.0,18354176.0,24899682304.0,5278890.667,41495381.333,1030.667,9452.0,149.333,0.0
03-06 03:36:21,41.304,18.581,35.480,3.322,0.246,1.066,2.810,2.480,2.690,6632714240.0,1626112.0,36392960.0,24866676736.0,5190997.333,40967850.667,1265.0,10822.0,388.0,0.0
03-06 03:36:24,41.820,18.040,35.588,3.239,0.328,0.984,2.810,2.480,2.690,6646849536.0,1908736.0,53899264.0,24834752512.0,5268821.333,35616426.667,1266.0,10570.667,106.667,0.0
03-06 03:36:27,41.426,18.425,35.779,3.215,0.330,0.824,2.820,2.490,2.690,6662418432.0,1908736.0,71184384.0,24801898496.0,5163690.667,34396672.0,1260.667,10589.333,172.0,0.0
03-06 03:36:30,40.544,16.893,37.701,3.585,0.288,0.989,2.820,2.490,2.690,6675357696.0,1916928.0,87445504.0,24772689920.0,4956160.0,36093781.333,1210.0,10207.667,128.0,0.0
03-06 03:36:33,41.858,18.003,35.638,3.191,0.368,0.941,2.760,2.480,2.690,6685978624.0,1916928.0,104824832.0,24744689664.0,5147306.667,35469994.667,1256.667,10605.333,0.0,0.0
03-06 03:36:36,41.356,17.866,36.146,3.309,0.248,1.075,2.780,2.490,2.690,6701670400.0,1925120.0,122081280.0,24711733248.0,5128192.0,37779456.0,1252.0,10586.667,345.333,0.0
03-06 03:36:39,41.128,17.538,36.723,3.376,0.329,0.906,2.780,2.490,2.690,6714130432.0,1925120.0,139091968.0,24682262528.0,5059925.333,37178368.0,1235.333,10415.333,803.333,0.0
03-06 03:36:42,40.874,17.388,37.206,3.296,0.330,0.906,2.710,2.480,2.690,6726586368.0,1933312.0,155607040.0,24653283328.0,5012650.667,39970304.0,1224.667,10540.0,243.333,0.0
03-06 03:36:45,41.557,18.320,35.574,3.197,0.287,1.066,2.710,2.480,2.690,6744440832.0,1933312.0,173203456.0,24617832448.0,5186901.333,34964138.667,1266.333,10570.667,526.667,0.0
03-06 03:36:48,41.392,18.081,35.914,3.254,0.329,1.030,2.740,2.490,2.690,6755409920.0,1941504.0,189792256.0,24590266368.0,5158229.333,35153066.667,1259.667,10589.667,251.333,0.0
03-06 03:36:51,41.606,18.541,35.330,3.219,0.326,0.978,2.760,2.500,2.690,6773100544.0,1941504.0,207417344.0,24554950656.0,5155498.667,37445632.0,1258.667,10619.333,548.0,0.0
03-06 03:36:54,38.950,17.097,38.581,4.182,0.246,0.943,2.760,2.500,2.690,6784245760.0,1953792.0,223490048.0,24527720448.0,4812800.0,33733290.667,1175.0,9833.333,462.333,0.0
03-06 03:36:57,42.045,17.683,35.820,3.256,0.330,0.866,2.700,2.490,2.690,6799187968.0,1953792.0,240869376.0,24495398912.0,5163690.667,35554474.667,1260.667,10592.667,358.667,0.0
03-06 03:37:00,40.328,17.541,36.967,3.975,0.246,0.943,2.700,2.490,2.690,6811226112.0,2093056.0,257081344.0,24467009536.0,5104981.333,38389589.333,1213.0,10258.0,190.333,0.0
03-06 03:37:03,39.803,18.509,35.831,4.586,0.369,0.901,2.800,2.520,2.690,6829117440.0,3407872.0,279732224.0,24425152512.0,7442432.0,38097237.333,1356.0,10738.667,1351.333,1056.0
03-06 03:37:06,36.274,16.126,43.045,3.488,0.205,0.862,2.900,2.540,2.700,6832521216.0,7319552.0,23998464.0,24673570816.0,5959680.0,42556928.0,1141.0,9278.333,2743.667,1848.667
03-06 03:37:09,41.535,15.823,39.517,1.978,0.0,1.147,2.900,2.540,2.700,6851235840.0,31019008.0,39596032.0,24615559168.0,4851029.333,46513152.0,1184.333,8478.0,776.667,432.0
03-06 03:37:12,41.946,16.266,38.637,1.969,0.079,1.103,2.820,2.530,2.700,6866350080.0,54833152.0,56049664.0,24560177152.0,4883797.333,46381738.667,1192.333,8527.0,366.667,0.0
03-06 03:37:15,41.469,16.746,38.468,2.054,0.158,1.106,2.820,2.530,2.700,6878212096.0,78688256.0,72212480.0,24508297216.0,4829184.0,47184554.667,1179.0,8499.667,251.333,0.0
03-06 03:37:18,42.141,16.627,37.994,2.014,0.118,1.106,2.840,2.540,2.700,6890102784.0,92667904.0,89780224.0,24464859136.0,4896085.333,47951872.0,1195.0,8640.333,128.0,0.0
03-06 03:37:21,41.878,16.601,38.233,1.981,0.119,1.189,2.850,2.550,2.700,6905135104.0,93769728.0,106356736.0,24432148480.0,4897450.667,49134421.333,1196.0,8628.667,155.333,0.0
03-06 03:37:24,42.357,16.401,38.177,1.990,0.0,1.075,2.850,2.550,2.700,6921912320.0,101171200.0,121999360.0,24392327168.0,5040810.667,49598464.0,1211.333,8709.333,254.0,0.0
03-06 03:37:27,41.993,16.054,38.711,2.096,0.0,1.147,2.780,2.540,2.700,6938673152.0,111038464.0,138448896.0,24349249536.0,4866048.0,47994197.333,1188.333,8540.0,283.0,0.0
03-06 03:37:30,42.427,15.979,38.541,1.983,0.040,1.031,2.780,2.540,2.700,6948630528.0,117248000.0,154988544.0,24316542976.0,4891989.333,52612266.667,1193.333,8804.0,257.333,0.0
03-06 03:37:33,41.183,16.055,39.724,1.933,0.0,1.105,2.800,2.540,2.700,6963224576.0,125366272.0,171581440.0,24277237760.0,4789589.333,50021717.333,1169.667,8537.0,304.667,0.0
03-06 03:37:36,41.970,15.981,38.845,2.017,0.0,1.187,2.890,2.570,2.700,6975680512.0,125403136.0,188162048.0,24248164352.0,4912469.333,46936064.0,1199.333,8551.667,208.667,0.0
03-06 03:37:39,41.955,15.932,39.015,1.947,0.040,1.112,2.890,2.570,2.700,6989406208.0,127373312.0,204222464.0,24216408064.0,4912469.333,47782570.667,1199.0,8559.667,353.333,0.0
03-06 03:37:42,42.801,16.129,37.884,2.006,0.0,1.180,2.900,2.580,2.710,7006773248.0,131170304.0,221167616.0,24178298880.0,4930218.667,52023296.0,1203.667,8752.0,377.333,196.667
03-06 03:37:45,42.398,16.078,37.197,3.136,0.0,1.191,2.900,2.580,2.710,7022358528.0,132313088.0,240017408.0,24142721024.0,6142634.667,47385258.667,1265.667,8590.667,1280.667,713.333
03-06 03:37:48,42.485,15.976,38.146,2.170,0.0,1.223,2.990,2.600,2.710,7033085952.0,132341760.0,257380352.0,24114601984.0,4909738.667,51152213.333,1198.667,8690.667,1405.333,1195.333
03-06 03:37:51,39.617,14.866,42.168,2.272,0.040,1.036,2.990,2.600,2.710,7045861376.0,132345856.0,272547840.0,24086654976.0,4592981.333,45510656.0,1121.333,8057.667,1338.667,802.0
03-06 03:37:54,42.231,15.857,38.845,2.072,0.0,0.996,2.990,2.600,2.710,7061733376.0,133115904.0,289337344.0,24053223424.0,4900181.333,51658752.0,1196.667,8712.667,914.0,354.0
03-06 03:37:57,35.943,14.221,46.311,2.500,0.0,1.025,2.910,2.590,2.710,7057674240.0,1658880.0,30449664.0,24447627264.0,5500928.0,41570816.0,1080.333,6721.667,558.667,755.333
03-06 03:38:00,43.815,16.715,36.492,1.820,0.041,1.117,2.910,2.590,2.710,7074951168.0,1658880.0,48226304.0,24412573696.0,5388970.667,39131989.333,1315.667,7285.0,328.667,0.0
03-06 03:38:03,43.541,16.797,35.906,2.600,0.0,1.156,2.760,2.570,2.700,7086780416.0,2453504.0,67850240.0,24380325888.0,6163114.667,39226368.0,1350.667,7247.667,393.0,26.0
03-06 03:38:06,43.518,17.713,34.641,2.766,0.0,1.363,2.700,2.560,2.700,7105183744.0,3231744.0,87130112.0,24341864448.0,6150826.667,41316352.0,1340.0,7273.0,3189.667,2764.0
03-06 03:38:09,43.505,16.570,36.803,1.915,0.0,1.207,2.700,2.560,2.700,7123107840.0,3231744.0,105553920.0,24305516544.0,5348010.667,41463808.0,1306.0,7344.0,608.0,137.333
03-06 03:38:12,44.440,16.370,36.007,1.902,0.041,1.240,2.640,2.550,2.690,7135752192.0,3239936.0,123695104.0,24274722816.0,5428565.333,41887061.333,1325.0,7386.667,420.0,0.0
03-06 03:38:15,42.692,16.439,37.805,1.988,0.0,1.077,2.640,2.550,2.690,7151194112.0,3239936.0,141217792.0,24241758208.0,5203285.333,41279488.0,1270.333,7181.333,550.0,0.0
03-06 03:38:18,43.023,16.736,37.085,1.869,0.0,1.287,2.670,2.560,2.690,7170564096.0,3248128.0,159260672.0,24204337152.0,5298858.667,44076202.667,1293.667,7449.667,234.667,0.0
03-06 03:38:21,43.335,16.632,36.855,1.940,0.0,1.238,2.620,2.550,2.690,7181553664.0,3248128.0,176795648.0,24175812608.0,5313877.333,43899050.667,1297.333,7288.667,556.0,102.0
03-06 03:38:24,43.804,16.535,36.469,1.948,0.041,1.202,2.620,2.550,2.690,7194333184.0,3391488.0,194486272.0,24145199104.0,5412181.333,41571498.667,1312.333,7341.0,64.0,0.0
03-06 03:38:27,43.563,17.193,36.005,1.952,0.0,1.287,2.650,2.550,2.690,7209041920.0,3391488.0,212758528.0,24112218112.0,5356202.667,40728917.333,1307.333,7310.667,158.0,0.0
03-06 03:38:30,44.463,17.005,35.297,1.949,0.0,1.286,2.650,2.550,2.690,7226200064.0,3399680.0,230916096.0,24076894208.0,5458602.667,43232085.333,1329.0,7497.0,251.667,39.333
03-06 03:38:33,44.150,16.701,36.048,1.860,0.0,1.240,2.590,2.540,2.690,7239434240.0,3403776.0,248909824.0,24045662208.0,5409450.667,42072917.333,1321.0,7385.667,765.333,1364.667
03-06 03:38:36,44.380,16.179,36.147,1.935,0.041,1.317,2.630,2.550,2.690,7254462464.0,3411968.0,267083776.0,24012451840.0,5365760.0,42579968.0,1309.667,7322.333,385.333,0.0
03-06 03:38:39,43.682,16.376,36.783,1.953,0.0,1.205,2.630,2.550,2.690,7270338560.0,3411968.0,284553216.0,23979106304.0,5326165.333,42297856.0,1301.0,7373.333,466.0,0.0

result.txt

########################################################################################################
### 20140603033524 fio1 / ITERATIONS:2 / TYPE:write
########################################################################################################
Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 26.762 seconds
	Minimum number of seconds to run all queries: 25.094 seconds
	Maximum number of seconds to run all queries: 28.431 seconds
	Number of clients running queries: 10
	Average number of queries per client: 50000

########################################################################################################
### 20140603033618 fio2 / ITERATIONS:2 / TYPE:write
########################################################################################################
Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 23.501 seconds
	Minimum number of seconds to run all queries: 23.465 seconds
	Maximum number of seconds to run all queries: 23.538 seconds
	Number of clients running queries: 10
	Average number of queries per client: 50000

########################################################################################################
### 20140603033705 hdd1 / ITERATIONS:2 / TYPE:write
########################################################################################################
Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 24.745 seconds
	Minimum number of seconds to run all queries: 24.689 seconds
	Maximum number of seconds to run all queries: 24.802 seconds
	Number of clients running queries: 10
	Average number of queries per client: 50000

########################################################################################################
### 20140603033755 hdd2 / ITERATIONS:2 / TYPE:write
########################################################################################################
Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 22.525 seconds
	Minimum number of seconds to run all queries: 22.439 seconds
	Maximum number of seconds to run all queries: 22.612 seconds
	Number of clients running queries: 10
	Average number of queries per client: 50000