#!/usr/bin/tclsh

# Script to test performance of MySQL server regarding multiple CPU usage.
# Written by Heng Sun at sunheng@hotmail.com
#
# The root query used to perform the tests.
# The actual query string is appended by some dummy condition.
set rootQuery {SELECT count(*) FROM pst n, iss m
    where m.var_run_cnt = n.var_run_cnt and m.cusip_nbr = n.cusip_nbr 
    and m.wi_in=n.wi_in and n.part = '9999'
    and n.var_run_cnt between 1710 and 1760
}

# The total number of tests to repeat.
set totalTests 3

# In each time, the total number of cases to run.
set totalCases 9

# This array holds all alive child PIDs. The key is a child PID.
# The value is the concantenation of test ID and case ID separated by underscore.
array set alivePidArray ""

# These two arrays hold starting time and ending time of a test case.
# The value is a time measured in second.
# The key can be the concantenation of test ID and case ID separated by underscore,
# or further appended by a PID.
array set startTimeArray ""
array set endTimeArray ""

# Output message with time stamp.
proc log {message} {
    puts "[clock format [clock seconds]]: $message"
}

# Fork a child process that run a query to the database server.
proc forkQuery {testId caseId queryId} {
    global alivePidArray startTimeArray rootQuery
    set fileName ${testId}_${caseId}_${queryId}
    # Append the query string by some dummy condition to avoid using query result cache.
    set query [join [list $rootQuery "and '$fileName' = '$fileName'"]]
    exec echo $query > logs/${fileName}
    set newPid [exec mysql -e $query >> logs/${fileName} & ]
    set startTimeArray(${testId}_${caseId}_${newPid}) [clock seconds]
    set alivePidArray($newPid) ${testId}_${caseId}
}

# Record finish time after a child process has done the query.
# Return after all child processes are finished.
proc checkForks {} {
    global alivePidArray endTimeArray
    while {1} {
	foreach child [array names alivePidArray] {
	    if {[catch {
		exec kill -s 0 $child
	    } err]} {
		# An error means child process has finished. 
		# If a process is finished, OS would throw error like:
		# kill: (121423) - No such process
		set endTimeArray($alivePidArray($child)_${child}) [clock seconds]
		array unset alivePidArray $child
	    } else {
		# The child process is still alive.
	    }
	}
	if [array size alivePidArray] {
	    # Some child process is still running. Sleep for half second.
	    after 500
	} else {
	    # All child processes are done.
	    return
	}
    }
}

# Prefix a string by spaces so that the resulting string lenth is some fixed value.
proc formatCell {s} {
    set fixedLen 15
    set len [string length $s]
    set rtn [string repeat " " [expr $fixedLen - $len]]
    append rtn $s
    return $rtn
}

log "Starting MySQL performance tests on parallel queries."
file delete -force logs
file mkdir logs

for {set testId 1} {$testId <= $totalTests} {incr testId} {
    log "Starting test $testId."
    for {set caseId 1} {$caseId <= $totalCases} {incr caseId} {
	log "Starting test $testId, case $caseId parallels."
	set startTimeArray(${testId}_${caseId}) [clock seconds]
	for {set queryId 1} {$queryId <= $caseId} {incr queryId} {
	    forkQuery $testId $caseId $queryId
	    log "Forked query $queryId."
	    after 1000
	}
	checkForks
	set endTimeArray(${testId}_${caseId}) [clock seconds]
	log "Finished test $testId, case $caseId parallels."
	after 2000
    }
    log "Finished test $testId."
}

set report [formatCell "Parallels"]
for {set testId 1} {$testId <= $totalTests} {incr testId} {
    append report [formatCell "Test $testId"]
}
append report \n
for {set caseId 1} {$caseId <= $totalCases} {incr caseId} {
    append report [formatCell $caseId]
    for {set testId 1} {$testId <= $totalTests} {incr testId} {
	set queryTime [expr $endTimeArray(${testId}_${caseId}) - $startTimeArray(${testId}_${caseId})]
	# convert into minutes
	set queryTime [format "%.2f (min)" [expr $queryTime / 60.0]]
	append report [formatCell "${queryTime}"]
    }
    append report \n
}

set detail ""
for {set testId 1} {$testId <= $totalTests} {incr testId} {
    for {set caseId 1} {$caseId <= $totalCases} {incr caseId} {
	append detail "Test $testId, Case $caseId paralles:\n"
	foreach name [array names startTimeArray -glob ${testId}_${caseId}_*] {
	    set lastInx [string last "_" $name]
	    set pid [string range $name [incr lastInx] end]
	    append detail "\tPID $pid started at [clock format $endTimeArray(${testId}_${caseId}_${pid})], "
	    append detail "ended at [clock format $endTimeArray(${testId}_${caseId}_${pid})].\n"
	}
    }
}

log "All are finished. The final results are as follows.
SUMMARY
$report
DETAILS
$detail"