]]>
]]>

Performance schema in IO benchmark

Google doc

  • Benchmark: sysbench oltp multi-table. Data is bugger than memory.

read-only

read-onlyNO PSPS ONPS only global
1361.29298.64348.8
2722.41651.32693.79
41394.021218.261333.03
82624.312275.822497.32
164782.033788.294160.9
246512.824735.995274.71
327487.14899.385521.9
489045.734773.295359.21
648894.024537.695133.3

read-write

read-writeNO PSPS ONPS only global
1294.63267.93281.82
2534.13477.76515.55
4886.6801.52844.29
81446.851302.461379.73
162187.091845.921962.52
242683.442204.962337.29
322369.842143.872405.28
482153.081938.632200.06
641990.481703.811956.31

Script

#!/bin/sh
set -u
set -x
set -e

ulimit -n 60000

DR="/mnt/raid10"
BD="/mnt/x25e/sysb.80m"

export LD_LIBRARY_PATH=/usr/local/mysql/lib/ 

WT=300
RT=180

ROWS=100000000

EXPER="L2 ro"

log2="/data/log/"
#log2="$DR/"

# restore from backup


function waitm {

while [ true ]
do

mysql -e "set global innodb_max_dirty_pages_pct=0" sbtest

wt=`mysql -e "SHOW ENGINE INNODB STATUS\G" | grep "Modified db pages" | sort -u | awk '{print $4}'`
if [[ "$wt" -lt 100 ]] ;
then
mysql -e "set global innodb_max_dirty_pages_pct=90" sbtest
break
fi

echo "mysql pages $wt"
sleep 10
done

}


#for thread in 2048
#for thread in 1 2 4 8 16 24 32 48 64 
for thread in 24 
do

#mysql -e "select avg(id) from sbtest;" sbtest

echo "sysbench $EXPER bp ${thread} warmup" >> /tmp/bench.log

mysql -e "update performance_schema.setup_consumers set enabled='NO' where name like 'events_waits%'" sbtest
mysql -e "update performance_schema.setup_consumers set enabled='NO' where name like 'thread_instrumentation'" sbtest

./sysbench --test=db/oltp.lua  --oltp_tables_count=100 --oltp-table-size=5000000 --rand-init=on --mysql-socket=/var/lib/mysql/mysql.sock --num-threads=$thread  --max-requests=0 --max-time=$WT --oltp-read-only=off --report-interval=10 --oltp-dist-type=uniform run

echo "sysbench $EXPER bp ${thread} warmup END" >> /tmp/bench.log
sleep 30

for j in 1 2 3
do
echo "$j"
echo "sysbench $EXPER bp ${thread} run $j" >> /tmp/bench.log
iostat -dx 10 >> oltp.bp$thread.iostat.res &
vmstat  10 >> oltp.bp$thread.vmstat.res &

mysql -e "truncate table table_io_waits_summary_by_table" performance_schema
mysql -e "truncate table table_io_waits_summary_by_index_usage" performance_schema
mysql -e "truncate table file_summary_by_event_name" performance_schema
mysql -e "truncate table file_summary_by_instance" performance_schema

./sysbench --test=db/oltp.lua  --oltp_tables_count=100 --oltp-table-size=5000000 --rand-init=on --mysql-socket=/var/lib/mysql/mysql.sock --num-threads=$thread  --max-requests=0 --max-time=$RT --oltp-read-only=off --report-interval=10 --oltp-dist-type=uniform run | tee -a oltp.thread$thread.res

mysql -e "select * from table_io_waits_summary_by_table\G" performance_schema >> perf_schema.bp$thread.vmstat.res
mysql -e "select * from table_io_waits_summary_by_index_usage\G" performance_schema >> perf_schema.bp$thread.vmstat.res
mysql -e "select * from file_summary_by_event_name\G" performance_schema >> perf_schema.bp$thread.vmstat.res
mysql -e "select * from file_summary_by_instance\G" performance_schema >> perf_schema.bp$thread.vmstat.res

echo "sysbench $EXPER bp ${thread} run $j END" >> /tmp/bench.log
kill -9 `pidof iostat`
kill -9 `pidof vmstat`
sleep 30
done



done

MySQL config

[mysqld]
gdb
datadir=/mlc/data
innodb_file_per_table = true
innodb_data_file_path = ibdata1:10M:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 256M
innodb_buffer_pool_size = 30G
innodb_log_file_size = 2000M
innodb_log_files_in_group = 2
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 1000
port = 3306
back_log = 50
max_connections = 5000
max_connect_errors = 10
table_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 16M
max_heap_table_size = 64M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 8
query_cache_size = 0
query_cache_limit = 2M
ft_min_word_len = 4
memlock
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 64M
server-id = 10
key_buffer_size = 8M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
socket=/var/lib/mysql/mysql.sock
user=root
skip-grant-tables
[mysql]
no-auto-rehash
 
benchmark/sysbench/ps_overhead_io/start.txt · Last modified: 2011/07/17 21:05 (external edit)
Except where otherwise noted, content on this wiki is licensed under the following license:CC Attribution-Noncommercial-Share Alike 3.0 Unported
Contact Us 24 Hours A Day
SupportContact us 24×7
Emergency? Contact us for help now!
Sales North America(888) 316-9775 or
(208) 473-2904
Sales
Europe
+44-208-133-0309 (UK)
0-800-051-8984 (UK)
0-800-181-0665 (GER)
Training(855) 55TRAIN or
(925) 271-5054

 

Share This
]]> ]]>