Databases Reference
In-Depth Information
Capturing System Performance and Status
It is important to capture as much information about the system under test (SUT) as
possible while the benchmark runs. It's a good idea to make a benchmark directory
with subdirectories for each run's results. You can then place the results, configuration
files, measurements, scripts, and notes for each run in the appropriate subdirectory. If
you can measure more than you think you're interested in, record the extra data any-
way. It's much better to have unneeded data than to miss important data, and you
might find the extra data useful in the future. Try to record status and performance
metrics such as CPU usage, disk I/O, network traffic statistics, counters from
SHOW
GLOBAL STATUS
; and so on.
Here is a sample shell script that you can use to gather data on MySQL during bench-
marks:
#!/bin/sh
INTERVAL=5
PREFIX=$INTERVAL-sec-status
RUNFILE=/home/benchmarks/running
mysql -e 'SHOW GLOBAL VARIABLES' >> mysql-variables
while test -e $RUNFILE; do
file=$(date +%F_%I)
sleep=$(date +%s.%N | awk "{print $INTERVAL - (\$1 % $INTERVAL)}")
sleep $sleep
ts="$(date +"TS %s.%N %F %T")"
loadavg="$(uptime)"
echo "$ts $loadavg" >> $PREFIX-${file}-status
mysql -e 'SHOW GLOBAL STATUS' >> $PREFIX-${file}-status &
echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus
mysql -e 'SHOW ENGINE INNODB STATUS\G' >> $PREFIX-${file}-innodbstatus &
echo "$ts $loadavg" >> $PREFIX-${file}-processlist
mysql -e 'SHOW FULL PROCESSLIST\G' >> $PREFIX-${file}-processlist &
echo $ts
done
echo Exiting because $RUNFILE does not exist.
The shell script, simple as it is, is a solid framework for gathering performance and
status data. There are a few things about it that we find useful, which you might not
appreciate until you run large benchmarks across many servers and find it difficult to
answer questions about system behavior:
• The iterations are timed so that it will run every time the clock is evenly divisible
by 5 seconds. If you just insert “sleep 5” into the loop, the loop will take slightly
longer than 5 seconds to run, and you won't have an easy time correlating any data
captured by this script with any other scripts or graphs. And even if your loops
somehow last exactly 5 seconds, it's annoying to have some data from one system
with a timestamp of 15:32:18.218192 and another system at 15:32:23.819437. You
can change 5 seconds to something else, such as 1, 10, 30, or 60 if you want; we
usually use 5 or 10 seconds.
