Performance Schema overhead

Goal: estimate overhead of using Performance Schema in MySQL 5.6.2 in sysbench read-write and read-only workload

Hardware: Cisco UCS C250

Benchmark: sysbench 0.4.12, oltp 100mil rows, read/only and read/writes, threads from 1 to 1024.

MySQL:

• without –performance-schema
• with –performance-schema
• with –performance-schema, disabled consumers , global_instrumentation enabled
• with –performance-schema, disabled all consumers

read-only

read-write

all, but no global_instrumentation:

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

ALL:
mysql -e "update performance_schema.setup_consumers set enabled='NO'" sbtest

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

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

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

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

}


mysql -e "select min(c) from sbtest /* FORCE KEY PRIMARY */ where id <= $ROWS" sbtest

for thread in 1 2 4 8 16 24 32 48 64 128 256 512 1024 2048
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
mysql -e "update performance_schema.setup_consumers set enabled='NO'" sbtest

sysbench --test=oltp --oltp-table-size=$ROWS --oltp-read-only=off --init-rng=on --num-threads=$thread --max-requests=0 --oltp-dist-type=uniform --max-time=$WT  --mysql-user=root  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 performance_schema.events_waits_summary_global_by_event_name" sbtest
sysbench --test=oltp --oltp-table-size=$ROWS --oltp-read-only=off --init-rng=on --num-threads=$thread --max-requests=0 --oltp-dist-type=uniform --max-time=$RT  --mysql-user=root run | tee -a oltp.thread$thread.res
mysql -e "select * from events_waits_summary_global_by_event_name ORDER BY SUM_TIMER_WAIT DESC" 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 = 120G
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 = 2500
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