Loading ...Giving a bunch of mysql instances something you do everyday and you might think ….. how should I do it? Write a bunch of selects and inserts manually? nahh that takes s**tload of time, should I run binlogs collected from a live system on my test server? nahh thats not practical nor is it real since it doesn’t contain selects, should I gather the general query log and try that out? nahhh …..
MySQL has been kind enough to supply us with their mysql_slap which does the job for us and given I needed to do a proof of concept on monitoring a group of 4 circular replicated servers I wrote a small script which does the job of slapping them with a varying level of concurrancy, iterations, number of queries and connections for as long as you like.
Here it is and I hope some of you might find it useful for slapping their own test servers :).
#!/bin/bash
NumberOfConcurrentLoads=4
LOGFILE=randomslap-4n-circularreplication
load () {
run=0
#Obtaining pid of the current process
PID=$$
echo "MySQL Slap random loading MySQL instances" >> $LOGFILE-$PID.log
echo "Loading child $a out of $NumberOfConcurrentLoads" >> $LOGFILE-$PID.log
echo "Starting at: `date`" >> $LOGFILE-$PID.log
echo ""
while [ 1 ]
do
#Generate a few values for the actual command to run through
prt=$((RANDOM%4+17001)) #ports being 17001,17002,17003,17004
cnc=$((RANDOM%50+1)) #number of concurrent sessions
itr=$((RANDOM%9+1)) #number of iterations
noq=$((RANDOM%100+50)) #number of queries
sec=$((RANDOM%30+10)) #seconds idle after finishing the process
echo "Run number $run" >> $LOGFILE-$PID.log
echo `date +%Y/%m/%d---%H:%M | sed 's/---/ /'` >> $LOGFILE-$PID.log
echo "Port=$prt" >> $LOGFILE-$PID.log
echo "Concurrency=$cnc" >> $LOGFILE-$PID.log
echo "Iterations=$itr" >> $LOGFILE-$PID.log
echo "Number-Of-Queries=$noq" >> $LOGFILE-$PID.log
echo "Breaktime=$sec" >> $LOGFILE-$PID.log
echo "Output:" >> $LOGFILE-$PID.log
mysqlslap --user=root --password='msandbox' --auto-generate-sql -vv -h hostname -P $prt --concurrency=$cnc --iterations=$itr --number-of-queries=$noq >> $LOGFILE-$PID.log
run=`expr $run + 1`
sleep $sec
done
}
#Fork a number of concurrent scripts to emulate more of a realistic load rather than one process connecting to just one server at a time
for ((a=1;a<=NumberOfConcurrentLoads;a++))
do
load &
done
If you are wondering … yes I totally indendate my code :) but the html version came out this way and I didn’t bother with indenting it using html really.