At file:///Users/hakan/work/monty_program/mariadb-tools/ ------------------------------------------------------------ revno: 19 revision-id: hakan@askmonty.org-20100309140429-75fbhpdrejalq31d parent: hakan@askmonty.org-20100304020337-9xmeeklcn4uccvdt committer: Hakan Kuecuekyilmaz <hakan@askmonty.org> branch nick: mariadb-tools timestamp: Tue 2010-03-09 15:04:29 +0100 message: * Added sudo for perro and work.inc * Set random seed of sysbench to have better comparision * Restart mysqld from scratch for each run and copy away DATA_DIR of the database for faster starts. * Between each run, run sync and clear file system caches with echo 3 > /proc/sys/vm/drop_caches (http://linux.die.net/man/5/proc) * Write out mysqld and sysbench options for reference. === modified file 'sysbench/analyze-sysbench.php' --- a/sysbench/analyze-sysbench.php 2010-03-04 02:03:37 +0000 +++ b/sysbench/analyze-sysbench.php 2010-03-09 14:04:29 +0000 @@ -2,10 +2,10 @@ /** * Analyze sysbench v0.5 results * - * We take one directories as an argument and produce + * We take one directory as an argument and produce * SQL INSERT statements for further usage. * - * The directory structure is: + * The current directory structure is: * ${RESULT_DIR}/${TODAY}/${PRODUCT}/${SYSBENCH_TEST}/${THREADS} * * For instance: @@ -19,7 +19,7 @@ * 21749.94 * * The current layout of the tables for storing the - * benchmark results of sysbench is: + * benchmark results of a sysbench run is: * CREATE TABLE sysbench_run ( * id int unsigned NOT NULL auto_increment, * host varchar(80), -- Hostname we ran the test on. @@ -62,7 +62,7 @@ */ /** - * Base path to our result files + * Base path to our result files. */ define('BASE_PATH', $_SERVER['HOME'] . '/work/sysbench-results/' . RUN_DATE . '/' . PRODUCT); === modified file 'sysbench/conf/perro.inc' --- a/sysbench/conf/perro.inc 2010-03-04 02:03:03 +0000 +++ b/sysbench/conf/perro.inc 2010-03-09 14:04:29 +0000 @@ -24,6 +24,9 @@ IOSTAT_DEVICE='/dev/sda' SAR='/usr/bin/sar' +# Other binaries. +SUDO=/my/local/bin/sur + # Directories. TEMP_DIR='/mnt/data/sysbench' DATA_DIR="${TEMP_DIR}/data" === modified file 'sysbench/conf/work.inc' --- a/sysbench/conf/work.inc 2010-03-04 02:03:03 +0000 +++ b/sysbench/conf/work.inc 2010-03-09 14:04:29 +0000 @@ -24,6 +24,9 @@ IOSTAT_DEVICE='/dev/sda' SAR='/usr/bin/sar' +# Other binaries. +SUDO=/my/local/bin/sur + # Directories. TEMP_DIR="${HOME}/tmp" DATA_DIR="${TEMP_DIR}/data" === modified file 'sysbench/run-sysbench-myisam.sh' --- a/sysbench/run-sysbench-myisam.sh 2010-03-04 02:03:03 +0000 +++ b/sysbench/run-sysbench-myisam.sh 2010-03-09 14:04:29 +0000 @@ -91,6 +91,10 @@ update_index.lua \ update_non_index.lua" +# +# Note: myisam-max-rows has to match or exceed oltp-table-size +# otherwise we get a table full error while preparing the run. +# SYSBENCH_OPTIONS="--oltp-table-size=$TABLE_SIZE \ --max-time=$RUN_TIME \ --max-requests=0 \ === modified file 'sysbench/run-sysbench.sh' --- a/sysbench/run-sysbench.sh 2010-03-04 02:03:03 +0000 +++ b/sysbench/run-sysbench.sh 2010-03-09 14:04:29 +0000 @@ -93,6 +93,9 @@ # How many times we run each test. LOOP_COUNT=3 +# 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 \ @@ -107,7 +110,9 @@ --max-requests=0 \ --mysql-table-engine=InnoDB \ --mysql-user=root \ - --mysql-engine-trx=yes" + --mysql-engine-trx=yes \ + --rand-init=on \ + --rand-seed=303" # Timeout in seconds for waiting for mysqld to start. TIMEOUT=100 @@ -118,12 +123,25 @@ BASE="${HOME}/work" TEST_DIR="${BASE}/monty_program/sysbench/sysbench/tests/db" RESULT_DIR="${BASE}/sysbench-results" +SYSBENCH_DB_BACKUP="${TEMP_DIR}/sysbench_db" # # Files # BUILD_LOG="${WORK_DIR}/${PRODUCT}_build.log" +# +# Check system. +# +# We should at least have $SPACE_LIMIT in $WORKDIR. +AVAILABLE=$(df $WORK_DIR | grep -v Filesystem | awk '{ print $4 }') + +if [ $AVAILABLE -lt $SPACE_LIMIT ]; then + echo "[ERROR]: We need at least $SPACE_LIMIT space in $WORK_DIR." + echo 'Exiting.' + + exit 1 +fi if [ ! -d $LOCAL_MASTER ]; then echo "[ERROR]: Supplied local master $LOCAL_MASTER does not exists." @@ -209,40 +227,72 @@ mkdir ${RESULT_DIR}/${TODAY} mkdir ${RESULT_DIR}/${TODAY}/${PRODUCT} -killall -9 mysqld -rm -rf $DATA_DIR -rm -f $MY_SOCKET -mkdir $DATA_DIR - -sql/mysqld $MYSQL_OPTIONS & - -j=0 -STARTED=-1 -while [ $j -le $TIMEOUT ] - do - $MYSQLADMIN $MYSQLADMIN_OPTIONS ping > /dev/null 2>&1 - if [ $? = 0 ]; then - STARTED=0 +function kill_mysqld { + killall -9 mysqld + rm -rf $DATA_DIR + rm -f $MY_SOCKET + mkdir $DATA_DIR +} + +function start_mysqld { + sql/mysqld $MYSQL_OPTIONS & + + j=0 + STARTED=-1 + while [ $j -le $TIMEOUT ] + do + $MYSQLADMIN $MYSQLADMIN_OPTIONS ping > /dev/null 2>&1 + if [ $? = 0 ]; then + STARTED=0 + + break + fi - break + sleep 1 + j=$(($j + 1)) + done + + if [ $STARTED != 0 ]; then + echo '[ERROR]: Start of mysqld failed.' + echo ' Please check your error log.' + echo ' Exiting.' + + exit 1 fi - - sleep 1 - j=$(($j + 1)) -done - -if [ $STARTED != 0 ]; then - echo '[ERROR]: Start of mysqld failed.' - echo ' Please check your error log.' - echo ' Exiting.' - - exit 1 -fi +} + +# +# Write out configurations used for future refernce. +# +echo $MYSQL_OPTIONS > ${RESULT_DIR}/${TODAY}/${PRODUCT}/mysqld_options.txt +echo $SYSBENCH_OPTIONS > ${RESULT_DIR}/${TODAY}/${PRODUCT}/sysbench_options.txt for SYSBENCH_TEST in $SYSBENCH_TESTS do mkdir ${RESULT_DIR}/${TODAY}/${PRODUCT}/${SYSBENCH_TEST} + kill_mysqld + start_mysqld + $MYSQLADMIN $MYSQLADMIN_OPTIONS create sbtest + if [ $? != 0 ]; then + echo "[ERROR]: Create of sbtest database failed" + echo " Please check your setup." + echo " Exiting" + 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}" + $SYSBENCH $SYSBENCH_OPTIONS prepare + + $MYSQLADMIN $MYSQLADMIN_OPTIONS shutdown + sync + 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." + cp -a ${DATA_DIR}/* ${SYSBENCH_DB_BACKUP}/ + for THREADS in $NUM_THREADS do THIS_RESULT_DIR="${RESULT_DIR}/${TODAY}/${PRODUCT}/${SYSBENCH_TEST}/${THREADS}" @@ -250,23 +300,24 @@ 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 '' >> ${THIS_RESULT_DIR}/results.txt + SYSBENCH_OPTIONS="$SYSBENCH_OPTIONS --num-threads=$THREADS" + k=0 while [ $k -lt $LOOP_COUNT ] do - $MYSQLADMIN $MYSQLADMIN_OPTIONS -f drop sbtest - $MYSQLADMIN $MYSQLADMIN_OPTIONS create sbtest - if [ $? != 0 ]; then - echo "[ERROR]: Create of sbtest database failed" - echo " Please check your setup." - echo " Exiting" - exit 1 - fi - - SYSBENCH_OPTIONS="$SYSBENCH_OPTIONS --num-threads=$THREADS --test=${TEST_DIR}/${SYSBENCH_TEST}" - $SYSBENCH $SYSBENCH_OPTIONS prepare - - sync - sleep 3 + echo '' + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Killing mysqld and copying back $DATA_DIR for $SYSBENCH_TEST." + kill_mysqld + cp -a ${SYSBENCH_DB_BACKUP}/* ${DATA_DIR} + + # Clear file system cache. This works only with Linux >= 2.6.16. + # On Mac OS X we can use sync; purge. + 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" + start_mysqld + sync $SYSBENCH $SYSBENCH_OPTIONS run > ${THIS_RESULT_DIR}/result${k}.txt 2>&1 @@ -274,6 +325,9 @@ 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 done done