At file:///Users/hakan/work/monty_program/mariadb-tools/ ------------------------------------------------------------ revno: 33 revision-id: hakan@askmonty.org-20100408002114-36ml405ax1h61x60 parent: hakan@askmonty.org-20100407121715-2k24p66xyfkgpdpn committer: Hakan Kuecuekyilmaz branch nick: mariadb-tools timestamp: Thu 2010-04-08 02:21:14 +0200 message: Added: * IO utilization with iostat * CPU utilization with mpstat * Simple queries/second monitoring per test with mysqladmin --sleep 10 status * Tests can have options now ** select_random_points has option for number of points ** select_random_ragnes has option for number of ranges and delta of ranges. === modified file 'sysbench/run-sysbench-myisam.sh' --- a/sysbench/run-sysbench-myisam.sh 2010-04-07 12:17:15 +0000 +++ b/sysbench/run-sysbench-myisam.sh 2010-04-08 00:21:14 +0000 @@ -106,16 +106,26 @@ # We need at least 1 GB disk space in our $WORK_DIR. SPACE_LIMIT=1000000 -SYSBENCH_TESTS="delete.lua \ - insert.lua \ - oltp_complex_ro.lua \ - oltp_complex_rw.lua \ - oltp_simple.lua \ - select.lua \ - select_random_points.lua \ - select_random_ranges.lua \ - update_index.lua \ - update_non_index.lua" +# Interval in seconds for monitoring system status like disk IO, +# CPU utilization, and such. +MONITOR_INTERVAL=10 + +SYSBENCH_TESTS[0]="delete.lua" +SYSBENCH_TESTS[1]="insert.lua" +SYSBENCH_TESTS[2]="oltp_complex_ro.lua" +SYSBENCH_TESTS[3]="oltp_complex_rw.lua" +SYSBENCH_TESTS[4]="oltp_simple.lua" +SYSBENCH_TESTS[5]="select.lua" + +# Default option is --random-points=10. +SYSBENCH_TESTS[6]="select_random_points.lua" + +# Default options are +# --number-of-ranges=10 +# --random-ranges-delta=5. +SYSBENCH_TESTS[7]="select_random_ranges.lua" +SYSBENCH_TESTS[8]="update_index.lua" +SYSBENCH_TESTS[9]="update_non_index.lua" # # Note: myisam-max-rows has to match or exceed oltp-table-size @@ -277,7 +287,7 @@ } # -# Write out configurations used for future refernce. +# Write out configurations used for future reference. # echo $MYSQLD_OPTIONS > ${RESULT_DIR}/${TODAY}/${PRODUCT}/mysqld_options.txt echo $SYSBENCH_OPTIONS > ${RESULT_DIR}/${TODAY}/${PRODUCT}/sysbench_options.txt @@ -285,8 +295,17 @@ echo "Warm up time is: $WARM_UP_TIME" >> ${RESULT_DIR}/${TODAY}/${PRODUCT}/sysbench_options.txt echo "Run time is: $RUN_TIME" >> ${RESULT_DIR}/${TODAY}/${PRODUCT}/sysbench_options.txt -for SYSBENCH_TEST in $SYSBENCH_TESTS +# +# Kill possibly left over monitoring processes. +# +killall -9 iostat +killall -9 mpstat +killall -9 mysqladmin + +for (( i = 0 ; i < ${#SYSBENCH_TESTS[@]} ; i++ )) do + # Get rid of any options of given sysbench test. + SYSBENCH_TEST=$(echo "${SYSBENCH_TESTS[$i]}" | awk '{ print $1 }') mkdir ${RESULT_DIR}/${TODAY}/${PRODUCT}/${SYSBENCH_TEST} kill_mysqld @@ -299,8 +318,8 @@ exit 1 fi - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Preparing and loading data for $SYSBENCH_TEST." - SYSBENCH_OPTIONS="${SYSBENCH_OPTIONS} --test=${TEST_DIR}/${SYSBENCH_TEST}" + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Preparing and loading data for ${SYSBENCH_TESTS[$i]}." + SYSBENCH_OPTIONS="${SYSBENCH_OPTIONS} --test=${TEST_DIR}/${SYSBENCH_TESTS[$i]}" $SYSBENCH $SYSBENCH_OPTIONS --max-time=$RUN_TIME prepare $MYSQLADMIN $MYSQLADMIN_OPTIONS shutdown @@ -308,14 +327,14 @@ rm -rf ${SYSBENCH_DB_BACKUP} mkdir ${SYSBENCH_DB_BACKUP} - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Copying $DATA_DIR of $SYSBENCH_TEST for later usage." + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Copying $DATA_DIR of ${SYSBENCH_TESTS[$i]} for later usage." cp -a ${DATA_DIR}/* ${SYSBENCH_DB_BACKUP}/ for THREADS in $NUM_THREADS do THIS_RESULT_DIR="${RESULT_DIR}/${TODAY}/${PRODUCT}/${SYSBENCH_TEST}/${THREADS}" mkdir $THIS_RESULT_DIR - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Running $SYSBENCH_TEST with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" | tee ${THIS_RESULT_DIR}/results.txt + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Running ${SYSBENCH_TESTS[$i]} with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" | tee ${THIS_RESULT_DIR}/results.txt echo '' >> ${THIS_RESULT_DIR}/results.txt SYSBENCH_OPTIONS_WARM_UP="${SYSBENCH_OPTIONS} --num-threads=3 --max-time=$WARM_UP_TIME" @@ -325,7 +344,7 @@ while [ $k -lt $LOOP_COUNT ] do echo '' - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Killing mysqld and copying back $DATA_DIR for $SYSBENCH_TEST." + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Killing mysqld and copying back $DATA_DIR for ${SYSBENCH_TESTS[$i]}." kill_mysqld cp -a ${SYSBENCH_DB_BACKUP}/* ${DATA_DIR} @@ -334,7 +353,7 @@ sync echo 3 | $SUDO tee /proc/sys/vm/drop_caches - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Starting mysqld for running $SYSBENCH_TEST with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Starting mysqld for running ${SYSBENCH_TESTS[$i]} with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" start_mysqld sync @@ -346,8 +365,25 @@ echo "[$(date "+%Y-%m-%d %H:%M:%S")] Finnished warm up." echo "[$(date "+%Y-%m-%d %H:%M:%S")] Starting actual sysbench run." + + $IOSTAT -d -k $IOSTAT_DEVICE $MONITOR_INTERVAL > ${THIS_RESULT_DIR}/iostat${k}.txt 2>&1 & + IOSTAT_PID=$! + + $MPSTAT -u $MONITOR_INTERVAL > ${THIS_RESULT_DIR}/cpustat${k}.txt 2>&1 & + MPSTAT_PID=$! + + $MYSQLADMIN $MYSQLADMIN_OPTIONS --sleep $MONITOR_INTERVAL status > ${THIS_RESULT_DIR}/server_status${k}.txt 2>&1 & + SERVER_STATUS_PID=$! + $SYSBENCH $SYSBENCH_OPTIONS_RUN run > ${THIS_RESULT_DIR}/result${k}.txt 2>&1 + sync; sync; sync + sleep 1 + + kill -9 $SERVER_STATUS_PID + kill -9 $MPSTAT_PID + kill -9 $IOSTAT_PID + grep "write requests:" ${THIS_RESULT_DIR}/result${k}.txt | awk '{ print $4 }' | sed -e 's/(//' >> ${THIS_RESULT_DIR}/results.txt echo 'SELECT * FROM INFORMATION_SCHEMA.KEY_CACHES' | $MYSQL $MYSQL_OPTIONS > ${THIS_RESULT_DIR}/key_cache_stats${k}.txt @@ -356,7 +392,7 @@ done echo '' >> ${THIS_RESULT_DIR}/results.txt - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Finnished $SYSBENCH_TEST with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" | tee -a ${THIS_RESULT_DIR}/results.txt + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Finnished ${SYSBENCH_TESTS[$i]} with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" | tee -a ${THIS_RESULT_DIR}/results.txt done done === modified file 'sysbench/run-sysbench.sh' --- a/sysbench/run-sysbench.sh 2010-04-07 12:17:15 +0000 +++ b/sysbench/run-sysbench.sh 2010-04-08 00:21:14 +0000 @@ -103,16 +103,26 @@ # We need at least 1 GB disk space in our $WORK_DIR. SPACE_LIMIT=1000000 -SYSBENCH_TESTS="delete.lua \ - insert.lua \ - oltp_complex_ro.lua \ - oltp_complex_rw.lua \ - oltp_simple.lua \ - select.lua \ - select_random_points.lua \ - select_random_ranges.lua \ - update_index.lua \ - update_non_index.lua" +# Interval in seconds for monitoring system status like disk IO, +# CPU utilization, and such. +MONITOR_INTERVAL=10 + +SYSBENCH_TESTS[0]="delete.lua" +SYSBENCH_TESTS[1]="insert.lua" +SYSBENCH_TESTS[2]="oltp_complex_ro.lua" +SYSBENCH_TESTS[3]="oltp_complex_rw.lua" +SYSBENCH_TESTS[4]="oltp_simple.lua" +SYSBENCH_TESTS[5]="select.lua" + +# Default option is --random-points=10. +SYSBENCH_TESTS[6]="select_random_points.lua" + +# Default options are +# --number-of-ranges=10 +# --random-ranges-delta=5. +SYSBENCH_TESTS[7]="select_random_ranges.lua" +SYSBENCH_TESTS[8]="update_index.lua" +SYSBENCH_TESTS[9]="update_non_index.lua" SYSBENCH_OPTIONS="--oltp-table-size=$TABLE_SIZE \ --max-requests=0 \ @@ -269,7 +279,7 @@ } # -# Write out configurations used for future refernce. +# Write out configurations used for future reference. # echo $MYSQLD_OPTIONS > ${RESULT_DIR}/${TODAY}/${PRODUCT}/mysqld_options.txt echo $SYSBENCH_OPTIONS > ${RESULT_DIR}/${TODAY}/${PRODUCT}/sysbench_options.txt @@ -277,8 +287,17 @@ echo "Warm up time is: $WARM_UP_TIME" >> ${RESULT_DIR}/${TODAY}/${PRODUCT}/sysbench_options.txt echo "Run time is: $RUN_TIME" >> ${RESULT_DIR}/${TODAY}/${PRODUCT}/sysbench_options.txt -for SYSBENCH_TEST in $SYSBENCH_TESTS +# +# Kill possibly left over monitoring processes. +# +killall -9 iostat +killall -9 mpstat +killall -9 mysqladmin + +for (( i = 0 ; i < ${#SYSBENCH_TESTS[@]} ; i++ )) do + # Get rid of any options of given sysbench test. + SYSBENCH_TEST=$(echo "${SYSBENCH_TESTS[$i]}" | awk '{ print $1 }') mkdir ${RESULT_DIR}/${TODAY}/${PRODUCT}/${SYSBENCH_TEST} kill_mysqld @@ -291,8 +310,8 @@ exit 1 fi - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Preparing and loading data for $SYSBENCH_TEST." - SYSBENCH_OPTIONS="${SYSBENCH_OPTIONS} --test=${TEST_DIR}/${SYSBENCH_TEST}" + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Preparing and loading data for ${SYSBENCH_TESTS[$i]}." + SYSBENCH_OPTIONS="${SYSBENCH_OPTIONS} --test=${TEST_DIR}/${SYSBENCH_TESTS[$i]}" $SYSBENCH $SYSBENCH_OPTIONS --max-time=$RUN_TIME prepare $MYSQLADMIN $MYSQLADMIN_OPTIONS shutdown @@ -300,14 +319,14 @@ rm -rf ${SYSBENCH_DB_BACKUP} mkdir ${SYSBENCH_DB_BACKUP} - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Copying $DATA_DIR of $SYSBENCH_TEST for later usage." + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Copying $DATA_DIR of ${SYSBENCH_TESTS[$i]} for later usage." cp -a ${DATA_DIR}/* ${SYSBENCH_DB_BACKUP}/ for THREADS in $NUM_THREADS do THIS_RESULT_DIR="${RESULT_DIR}/${TODAY}/${PRODUCT}/${SYSBENCH_TEST}/${THREADS}" mkdir $THIS_RESULT_DIR - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Running $SYSBENCH_TEST with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" | tee ${THIS_RESULT_DIR}/results.txt + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Running ${SYSBENCH_TESTS[$i]} with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" | tee ${THIS_RESULT_DIR}/results.txt echo '' >> ${THIS_RESULT_DIR}/results.txt SYSBENCH_OPTIONS_WARM_UP="${SYSBENCH_OPTIONS} --num-threads=1 --max-time=$WARM_UP_TIME" @@ -317,7 +336,7 @@ while [ $k -lt $LOOP_COUNT ] do echo '' - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Killing mysqld and copying back $DATA_DIR for $SYSBENCH_TEST." + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Killing mysqld and copying back $DATA_DIR for ${SYSBENCH_TESTS[$i]}." kill_mysqld cp -a ${SYSBENCH_DB_BACKUP}/* ${DATA_DIR} @@ -326,7 +345,7 @@ sync echo 3 | $SUDO tee /proc/sys/vm/drop_caches - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Starting mysqld for running $SYSBENCH_TEST with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Starting mysqld for running ${SYSBENCH_TESTS[$i]} with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" start_mysqld sync @@ -337,15 +356,32 @@ echo "[$(date "+%Y-%m-%d %H:%M:%S")] Finnished warm up." echo "[$(date "+%Y-%m-%d %H:%M:%S")] Starting actual sysbench run." + + $IOSTAT -d -k $IOSTAT_DEVICE $MONITOR_INTERVAL > ${THIS_RESULT_DIR}/iostat${k}.txt 2>&1 & + IOSTAT_PID=$! + + $MPSTAT -u $MONITOR_INTERVAL > ${THIS_RESULT_DIR}/cpustat${k}.txt 2>&1 & + MPSTAT_PID=$! + + $MYSQLADMIN $MYSQLADMIN_OPTIONS --sleep $MONITOR_INTERVAL status > ${THIS_RESULT_DIR}/server_status${k}.txt 2>&1 & + SERVER_STATUS_PID=$! + $SYSBENCH $SYSBENCH_OPTIONS_RUN run > ${THIS_RESULT_DIR}/result${k}.txt 2>&1 - + + sync; sync; sync + sleep 1 + + kill -9 $SERVER_STATUS_PID + kill -9 $MPSTAT_PID + kill -9 $IOSTAT_PID + grep "write requests:" ${THIS_RESULT_DIR}/result${k}.txt | awk '{ print $4 }' | sed -e 's/(//' >> ${THIS_RESULT_DIR}/results.txt k=$(($k + 1)) done echo '' >> ${THIS_RESULT_DIR}/results.txt - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Finnished $SYSBENCH_TEST with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" | tee -a ${THIS_RESULT_DIR}/results.txt + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Finnished ${SYSBENCH_TESTS[$i]} with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" | tee -a ${THIS_RESULT_DIR}/results.txt done done