Friday, February 22nd, 2013, 12:00 am
Splitting SQL Loads to Multiple CPU Cores
oday I wrote the following bash
script (note: written for 16 cores although it can be modified for more or for less), which splits SQL operations, preceded by “ [NUMERIC WEIGHT] |
“. This helps assure a nice distribution of load on the bare metal. This might be reusable, so I decided to post it on my site.
IFS=$'\n' CORE0LOAD=0 CORE1LOAD=0 CORE2LOAD=0 CORE3LOAD=0 CORE4LOAD=0 CORE5LOAD=0 CORE6LOAD=0 CORE7LOAD=0 CORE8LOAD=0 CORE9LOAD=0 CORE10LOAD=0 CORE11LOAD=0 CORE12LOAD=0 CORE13LOAD=0 CORE14LOAD=0 CORE15LOAD=0 WEIGHT=0; echo Starting... for line in $(cat ~/Desktop/input) # while read line < ~/Desktop/input # for line in `cat ~/Desktop/input` do # echo -e line="$line" # $line="$line" # echo $line # exit if [ $CORE0LOAD -lt $CORE15LOAD ] then # echo $line echo $line > current_line cut -b 1-13 current_line > current_number read SCORE < current_number echo $(($CORE0LOAD+$SCORE)) > total read SCORETOTAL < total # echo $SCORETOTAL CORE0LOAD=$((SCORETOTAL)) cut -b 16-10000 current_line >> core0.sh elif [ $CORE1LOAD -lt $CORE2LOAD ] then echo $line > current_line cut -b 1-13 current_line > current_number read SCORE < current_number echo $(($CORE1LOAD+$SCORE)) > total read SCORETOTAL < total CORE1LOAD=$((SCORETOTAL)) cut -b 16-10000 current_line >> core1.sh elif [ $CORE2LOAD -lt $CORE3LOAD ] then echo $line > current_line cut -b 1-13 current_line > current_number read SCORE < current_number echo $(($CORE2LOAD+$SCORE)) > total read SCORETOTAL < total CORE2LOAD=$((SCORETOTAL)) cut -b 16-10000 current_line >> core2.sh elif [ $CORE3LOAD -lt $CORE4LOAD ] then echo $line > current_line cut -b 1-13 current_line > current_number read SCORE < current_number echo $(($CORE3LOAD+$SCORE)) > total read SCORETOTAL < total CORE3LOAD=$((SCORETOTAL)) cut -b 16-10000 current_line >> core3.sh elif [ $CORE4LOAD -lt $CORE5LOAD ] then echo $line > current_line cut -b 1-13 current_line > current_number read SCORE < current_number echo $(($CORE4LOAD+$SCORE)) > total read SCORETOTAL < total CORE4LOAD=$((SCORETOTAL)) cut -b 16-10000 current_line >> core4.sh elif [ $CORE5LOAD -lt $CORE6LOAD ] then echo $line > current_line cut -b 1-13 current_line > current_number read SCORE < current_number echo $(($CORE5LOAD+$SCORE)) > total read SCORETOTAL < total CORE5LOAD=$((SCORETOTAL)) cut -b 16-10000 current_line >> core5.sh elif [ $CORE6LOAD -lt $CORE7LOAD ] then echo $line > current_line cut -b 1-13 current_line > current_number read SCORE < current_number echo $(($CORE6LOAD+$SCORE)) > total read SCORETOTAL < total CORE6LOAD=$((SCORETOTAL)) cut -b 16-10000 current_line >> core6.sh elif [ $CORE7LOAD -lt $CORE8LOAD ] then echo $line > current_line cut -b 1-13 current_line > current_number read SCORE < current_number echo $(($CORE7LOAD+$SCORE)) > total read SCORETOTAL < total CORE7LOAD=$((SCORETOTAL)) cut -b 16-10000 current_line >> core7.sh elif [ $CORE8LOAD -lt $CORE9LOAD ] then echo $line > current_line cut -b 1-13 current_line > current_number read SCORE < current_number echo $(($CORE8LOAD+$SCORE)) > total read SCORETOTAL < total CORE8LOAD=$((SCORETOTAL)) cut -b 16-10000 current_line >> core8.sh elif [ $CORE9LOAD -lt $CORE10LOAD ] then echo $line > current_line cut -b 1-13 current_line > current_number read SCORE < current_number echo $(($CORE9LOAD+$SCORE)) > total read SCORETOTAL < total CORE9LOAD=$((SCORETOTAL)) cut -b 16-10000 current_line >> core9.sh elif [ $CORE10LOAD -lt $CORE11LOAD ] then echo $line > current_line cut -b 1-13 current_line > current_number read SCORE < current_number echo $(($CORE10LOAD+$SCORE)) > total read SCORETOTAL < total CORE10LOAD=$((SCORETOTAL)) cut -b 16-10000 current_line >> core10.sh elif [ $CORE11LOAD -lt $CORE12LOAD ] then echo $line > current_line cut -b 1-13 current_line > current_number read SCORE < current_number echo $(($CORE11LOAD+$SCORE)) > total read SCORETOTAL < total CORE11LOAD=$((SCORETOTAL)) cut -b 16-10000 current_line >> core11.sh elif [ $CORE12LOAD -lt $CORE13LOAD ] then echo $line > current_line cut -b 1-13 current_line > current_number read SCORE < current_number echo $(($CORE12LOAD+$SCORE)) > total read SCORETOTAL < total CORE12LOAD=$((SCORETOTAL)) cut -b 16-10000 current_line >> core12.sh elif [ $CORE13LOAD -lt $CORE14LOAD ] then echo $line > current_line cut -b 1-13 current_line > current_number read SCORE < current_number echo $(($CORE13LOAD+$SCORE)) > total read SCORETOTAL < total CORE13LOAD=$((SCORETOTAL)) cut -b 16-10000 current_line >> core13.sh elif [ $CORE14LOAD -lt $CORE15LOAD ] then echo $line > current_line cut -b 1-13 current_line > current_number read SCORE < current_number echo $(($CORE14LOAD+$SCORE)) > total read SCORETOTAL < total CORE14LOAD=$((SCORETOTAL)) cut -b 16-10000 current_line >> core14.sh else # echo $line echo $line > current_line cut -b 1-13 current_line > current_number read SCORE < current_number echo $(($CORE15LOAD+$SCORE)) > total read SCORETOTAL < total CORE15LOAD=$((SCORETOTAL)) cut -b 16-10000 current_line >> core15.sh fi done echo Ended...
Now, each file contains operations to be run on one core, without conflict (order or load imbalance).