]]>
]]>

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

results

read-only

threadsNO PSPS onPS only globalPS all off
1598.74540.12545.42530.82
21160.671024.161070.21116.57
42257.421955.842101.492149.59
84344.963690.64079.744212
247809.696478.447247.667364.83
3210234.468195.089297.79441.82
4810062.428063.239123.819339.1
6410070.728161.099150.599467.54
1289968.158073.89038.699539.61
2569537.47899.028795.229161.88
5128886.227502.728266.528763.82
10245433.444652.785375.065657.84

read-write

threadsNO PSPS onPS only globalPS all off
1457.49428.54488.59468.35
2925.05829.79925.86876.1
41829.41592.761757.451694.04
83489.229333434.043225.92
245818.994994.695814.285462.98
327145.016060.66698.366752.5
487093.286021.326495.16293.51
647030.515996.426339.526307.83
1287041.435948.566432.56286.99
2566522.675698.116254.476568.15
5125818.24983.925728.595592.04
10243441.512835.043258.653229

Disable consumers

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

Script

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

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 = 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
 
benchmark/sysbench/ps_overhead/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
]]> ]]>