Skip to content

Instantly share code, notes, and snippets.

@s-tajima

s-tajima/my.cnf

Created May 30, 2014 08:58
Show Gist options
  • Save s-tajima/9a9e18ef0fa1a18bdf2a to your computer and use it in GitHub Desktop.
Save s-tajima/9a9e18ef0fa1a18bdf2a to your computer and use it in GitHub Desktop.
Download ZIP
mysqlslap result at 2014/05/30 Case3 (TYPE="mixed")
Raw
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
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
[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
[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
Raw
mysqlslap.sh
#!/bin/bash
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=10
TYPE="mixed"
sync
echo 3 > /proc/sys/vm/drop_caches
sync
echo "########################################################################################################" >> $MYSQLSLAP_LOG
echo "### `date '+%Y%m%d%H%M%S'` ${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=${TYPE} \
--number-of-queries=5000 \
>> $MYSQLSLAP_LOG
echo ""
done
kill $PID
Raw
mysqlslap_dstat.csv
We can make this file beautiful and searchable if this error is corrected: It looks like row 2 should actually have 1 column, instead of 7 in line 1.
"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:","31 May 2014 02:58:41 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"
31-05 02:58:41,1.603,0.549,97.529,0.289,0.004,0.025,2.210,6.840,6.180,5131866112.0,6320128.0,23040000.0,26376183808.0,142052.842,5717727.590,16.130,292.572,0.0,0.0
31-05 02:58:44,70.665,14.099,12.500,2.062,0.210,0.463,2.680,6.860,6.190,5133815808.0,49258496.0,26931200.0,26327404544.0,730453.333,37265408.0,178.333,7021.333,718.667,4774.0
31-05 02:58:47,70.008,13.774,13.058,2.359,0.253,0.548,2.680,6.860,6.190,5137387520.0,86605824.0,31666176.0,26281750528.0,824661.333,40397482.667,201.333,7641.0,846.667,4378.0
31-05 02:58:50,70.430,13.690,12.848,2.317,0.211,0.505,2.940,6.840,6.190,5139156992.0,105058304.0,35328000.0,26257866752.0,778240.0,38576298.667,190.0,7273.667,810.667,4753.333
31-05 02:58:53,68.666,13.800,13.758,2.978,0.252,0.545,2.940,6.840,6.190,5140164608.0,116645888.0,40808448.0,26239791104.0,733184.0,35722581.333,179.0,6734.667,804.0,4395.333
31-05 02:58:56,69.734,14.099,13.086,2.406,0.253,0.422,3.670,6.930,6.220,5145530368.0,125259776.0,45072384.0,26221547520.0,817834.667,40470016.0,199.667,7693.0,602.0,4378.0
31-05 02:58:59,65.252,12.857,19.286,1.975,0.210,0.420,3.770,6.890,6.210,5146177536.0,1691648.0,24969216.0,26364571648.0,1980757.333,21146794.667,225.667,5924.333,430.667,4478.0
31-05 02:59:02,69.218,13.415,15.307,1.388,0.210,0.463,3.770,6.890,6.210,5144109056.0,1691648.0,30081024.0,26361528320.0,973653.333,18772821.333,240.0,6570.0,746.667,4395.333
31-05 02:59:05,71.477,14.304,12.369,1.262,0.168,0.421,4.030,6.900,6.220,5146411008.0,1699840.0,35483648.0,26353815552.0,939861.333,18739200.0,230.333,6568.333,2484.333,5977.333
31-05 02:59:08,71.061,14.364,12.553,1.348,0.168,0.505,4.030,6.900,6.220,5148766208.0,1699840.0,39972864.0,26346971136.0,888832.0,17855317.333,217.0,6300.667,424.667,4635.333
31-05 02:59:11,63.246,12.868,21.236,1.934,0.126,0.589,3.950,6.830,6.200,5146742784.0,19390464.0,23801856.0,26347474944.0,1772202.667,29549056.0,226.0,7115.0,790.667,4522.667
31-05 02:59:14,68.182,13.342,15.572,1.894,0.0,1.010,4.590,6.920,6.230,5147721728.0,68771840.0,28401664.0,26292514816.0,866986.667,43737429.333,211.667,9399.667,528.667,4707.333
31-05 02:59:17,67.033,13.212,16.505,2.153,0.0,1.098,4.590,6.920,6.230,5152161792.0,98111488.0,33325056.0,26253811712.0,1013077.333,48902144.0,247.333,10524.0,597.333,4424.667
31-05 02:59:20,68.465,13.997,14.587,1.981,0.0,0.970,4.950,6.950,6.250,5154156544.0,115195904.0,39002112.0,26229055488.0,905216.0,45204821.333,221.0,9727.667,460.667,4545.333
31-05 02:59:23,69.043,13.286,14.762,1.982,0.0,0.928,4.950,6.950,6.250,5151985664.0,123256832.0,44404736.0,26217762816.0,873813.333,43743914.667,213.333,9481.0,424.667,4395.333
31-05 02:59:26,63.842,12.206,21.477,1.762,0.0,0.713,4.870,6.900,6.230,5149929472.0,880640.0,26222592.0,26360377344.0,1903274.667,23414442.667,258.0,6032.667,518.0,4472.0
31-05 02:59:29,71.938,13.633,12.458,1.174,0.042,0.755,5.200,6.940,6.250,5154320384.0,1163264.0,31281152.0,26350645248.0,1079978.667,20267520.0,244.333,6044.667,494.667,4395.333
31-05 02:59:32,68.441,13.076,16.471,1.257,0.0,0.754,5.200,6.940,6.250,5154557952.0,1163264.0,37425152.0,26344263680.0,1030826.667,20911445.333,251.667,6207.333,667.333,4418.667
31-05 02:59:35,71.626,13.579,12.909,1.174,0.042,0.671,5.660,7.0,6.270,5157871616.0,1171456.0,42455040.0,26335911936.0,962560.0,23273813.333,234.0,5930.333,569.333,4378.0
Raw
result.txt
########################################################################################################
### 20140531025840 fio1 / CONCURRENCY:10 / ITERATIONS:10 / TYPE:mixed
########################################################################################################
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 1.491 seconds
Minimum number of seconds to run all queries: 1.364 seconds
Maximum number of seconds to run all queries: 1.768 seconds
Number of clients running queries: 10
Average number of queries per client: 500
########################################################################################################
### 20140531025856 fio2 / CONCURRENCY:10 / ITERATIONS:10 / TYPE:mixed
########################################################################################################
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 1.288 seconds
Minimum number of seconds to run all queries: 1.237 seconds
Maximum number of seconds to run all queries: 1.366 seconds
Number of clients running queries: 10
Average number of queries per client: 500
########################################################################################################
### 20140531025909 hdd1 / CONCURRENCY:10 / ITERATIONS:10 / TYPE:mixed
########################################################################################################
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 1.252 seconds
Minimum number of seconds to run all queries: 1.170 seconds
Maximum number of seconds to run all queries: 1.358 seconds
Number of clients running queries: 10
Average number of queries per client: 500
########################################################################################################
### 20140531025923 hdd2 / CONCURRENCY:10 / ITERATIONS:10 / TYPE:mixed
########################################################################################################
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 1.166 seconds
Minimum number of seconds to run all queries: 1.130 seconds
Maximum number of seconds to run all queries: 1.243 seconds
Number of clients running queries: 10
Average number of queries per client: 500
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment