Introduction About Site Map

XML
RSS 2 Feed RSS 2 Feed
Navigation

Main Page | Blog Index

Friday, February 22nd, 2013, 12:00 am

Splitting SQL Loads to Multiple CPU Cores

Foday 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).

Technical Notes About Comments

Comments may include corrections, additions, citations, expressions of consent or even disagreements. They should preferably remain on topic.

Moderation: All genuine comments will be added. If your comment does not appear immediately (a rarity), it awaits moderation as it contained a sensitive word or a URI.

Trackbacks: The URI to TrackBack this entry is:

http://schestowitz.com/Weblog/archives/2013/02/22/cpu-sql-split/trackback/

Syndication: RSS feed for comments on this post RSS 2

    See also: What are feeds?, Local Feeds

Comments format: Line and paragraph breaks are automatic, E-mail address never displayed, HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Back to top

Retrieval statistics: 21 queries taking a total of 0.103 seconds • Please report low bandwidth using the feedback form
Original styles created by Ian Main (all acknowledgements) • PHP scripts and styles later modified by Roy Schestowitz • Help yourself to a GPL'd copy
|— Proudly powered by W o r d P r e s s — based on a heavily-hacked version 1.2.1 (Mingus) installation —|