Performance schema in IO benchmark

Google doc

• Hardware: Dell PowerEdge R800
• Benchmark: sysbench oltp multi-table. Data is bugger than memory.

read-only

read-write

#!/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

[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