Changeset 9853 for trunk/Mars/datacenter/scripts

Timestamp:

08/13/10 10:19:09 (14 years ago)

Author:

Daniela Dorner

Message:

implemented faster queries for the dependencies between the steps

File:

• trunk/Mars/datacenter/scripts/sourcefile (modified) (12 diffs)

trunk/Mars/datacenter/scripts/sourcefile

@@ -98 +98 @@
    if ! [ "$lockfile" = "" ] && ls $lockfile >/dev/null 2>&1
    then 
-      printprocesslog "INFO " `rm -v $lockfile`
-   fi
-   printprocesslog "INFO finished $0"
+      printprocesslog "DEBUG " `rm -v $lockfile`
+   fi
+   printprocesslog "DEBUG finished $0"
    exit
 }
@@ -154 +154 @@
       exit
    else
-      printprocesslog "INFO created lockfile $lockfile"
+      printprocesslog "DEBUG created lockfile $lockfile"
    fi
 }
@@ -183 +183 @@
 }
 
+# function to get information from the setupfile $steps
+function getfromsetup()
+{
+   grep $1"[.]"$2":" $steps | grep -v '#' | sed -e "s/$1[.]$2://"
+}
+
 # function to get the needed information from the dependencies-file steps.rc
 function getstepinfo()
 {
    getdbsetup
-   needs=`grep "$step[.]Needs:" $steps | grep -v '#' | sed -e "s/$step[.]Needs://"`
-   noderestricted=`grep "$step[.]NodeRestricted:" $steps | grep -v '#' | sed -e "s/$step[.]NodeRestricted://" -e 's/ //g'`
-   prims=( `grep "$step[.]Primaries:" $steps | grep -v '#' | sed -e "s/$step[.]Primaries://"` )
-#   echo " needs: $needs"
-#   echo " noderestricted: $noderestricted"
-#   echo " prims: ${prims[@]}"
-}
-
-# function to get the primaries of a step from the dependencies-file steps.rc
-function getprimary()
-{
-   getdbsetup
-   grep $@"[.]Primaries:" $steps | grep -v '#' | sed -e "s/$@[.]Primaries://"
-}
-
-# function to get the join of a step 
-#  from the dependencies-file steps.rc
-# $1 gives the step
-# $2 gives the type of join
-function getjoin()
-{
-   getdbsetup
-   grep $1"[.]"$2"Join:" $steps | grep -v '#' | sed -e "s/$1[.]$2Join://"
-}
-
-# function to get the needs of a step from the dependencies-file steps.rc
-function getneeds()
-{
-   getdbsetup
-   grep $@"[.]Needs:" $steps | sed -e "s/$@[.]Needs://"
-}
-
-# subquery which is needed to check the 
-#   status of the steps with different primaries
-function subquerypart()
-{
-   query=$query" SELECT COUNT(*) FROM "$need"Status "`getjoin $need "SpecialInfluence"`
-   needinfluences=`grep $need $steps | grep "Needs" | grep -v "$need[.]Needs" | cut -d'.' -f1`
-   for needinfl in $needinfluences
+   needs=( `getfromsetup $step "Needs"` )
+   noderestricted=`getfromsetup $step "NodeRestricted"`
+   prims=( `getfromsetup $step "Primaries"` )
+   maintable=`getfromsetup $step "MainTable" | sed -e "s/\ //g"`
+#   echo " maintable: "$maintable
+#   echo " needs: "${needs[@]}
+#   echo " noderestricted: "$noderestricted
+#   echo " prims: "${prims[@]}
+}
+
+# function to get the joins needed for the get/set status queries
+function getalljoins()
+{
+   # add table
+   query=$query" "$maintable"Status"
+   # add special join
+   query=$query" "`getfromsetup $maintable "SpecialJoin"`
+   # add join for step unless step is the same as maintable
+   if ! [ "$step" = "$maintable" ]
+   then 
+      query=$query" LEFT JOIN "$step"Status USING("${prims[@]}") "
+   fi
+   # add joins for influences or needs
+   for otherstep in ${othersteps[@]}
    do
-      needinfljoin=`getjoin $needinfl "Influence"`
-      if ! [ "$needinfljoin" = "" ]
-      then
-         query=$query" LEFT JOIN "$needinfl"Status USING ("$needinfljoin") "
+      if ! [ "$otherstep" = "$maintable" ]
+      then 
+         query=$query" LEFT JOIN "$otherstep"Status USING("`getfromsetup $otherstep "Primaries"`") "
       fi
-      query=$query" "`getjoin $needinfl "SpecialInfluence"`" "
-   done
-   query=$query" WHERE "`echo ${prims[0]} | sed -e 's/,//g'`"="$step"Status."`echo ${prims[0]} | sed -e 's/,//g'`
-   for (( j=1 ; j < ${#prims[@]} ; j++ ))
-   do
-      query=$query" AND "`echo ${prims[$j]} | sed -e 's/,//g'`"="$step"Status."`echo ${prims[$j]} | sed -e 's/,//g'`
    done
 }
@@ -243 +227 @@
 # function to create the middle part of a query
 #  which is identical for the functions getstatus() and gettodo()
-function middlepartofquery()
+function getstatusquery()
 {
    # add from which table the information is queried
-   query=$query" FROM "$step"Status "
-   # add the joins to the tables in which the status of the preceding steps is stored
-   for need in $needs
-   do
-      needprims=( `getprimary $need` )
-      # make sure that the correct joins are used
-      specialneedjoin=`getjoin $need "SpecialNeed"`
-      if [ "$specialneedjoin" = "" ]
-      then
-         query=$query" LEFT JOIN "$need"Status USING (${needprims[@]}) "
-      else
-         query=$query" "$specialneedjoin" "
-         query=$query" LEFT JOIN "$need"Status USING (${needprims[@]}) "
-         break
-      fi
-   done
+   query=$query" FROM "
+   othersteps=${needs[@]}
+   getalljoins
    # add condition
    query=$query" WHERE "
-   # add condition for the status of the peceding steps
-   counter=0
-   specialneedjoin=
-   specialinfljoin=
-   for need in $needs
-   do
-      if [ $counter -gt 0 ]
-      then
-         query=$query" AND "
-      fi
-      specialneedjoin=$specialneedjoin" "`getjoin $need "SpecialNeed"`
-      specialinfljoin=$specialinfljoin" "`getjoin $need "SpecialInfluence"`
-      if [ "$specialneedjoin" = " " ]
-      then 
-         # condition for step with same primaries
-         query=$query" NOT ISNULL("$need"Status.fStartTime) AND "
-         query=$query" NOT ISNULL("$need"Status.fStopTime) AND "
-         query=$query" ISNULL("$need"Status.fReturnCode) "
-         specialneedjoin=
-         specialinfljoin=
-      else 
-         # special query to check steps which 
-         #   do not have the same primaries
-         # comparison of 
-         #     number of rows
-         #   with
-         #     number of rows which are done
-         query=$query" ( "
-         subquerypart
-         query=$query" ) = ( "
-         subquerypart
-         query=$query" AND NOT ISNULL("$need"Status.fStartTime) "
-         query=$query" AND NOT ISNULL("$need"Status.fStopTime) "
-         query=$query" AND ISNULL("$need"Status.fReturnCode)) "
-      fi
-      counter=`echo $counter + 1 | bc -l`
-   done
-   # add condition for the status of the step itself
-   if [ $counter -gt 0 ]
-   then
-      query=$query" AND "
-   fi
+   # add condition for step, i.e. step is not yet done
    query=$query" ISNULL("$step"Status.fStartTime) "
    query=$query" AND ISNULL("$step"Status.fStopTime) "
    query=$query" AND ISNULL("$step"Status.fReturnCode) "
+   # add requirement for production host in case it is needed
+   if [ "$1 " != " " ]
+   then 
+      query=$query" AND fProductionHostKEY=$2 "
+   fi
+   if ! echo $query | grep UPDATE >/dev/null 2>&1
+   then 
+      query=$query" GROUP BY "${prims[@]}
+   fi
+   # add condition for needs, i.e. that step is done
+   for (( k=0 ; k < ${#needs[@]} ; k++ ))
+   do
+      if [ $k -eq 0 ]
+      then 
+         query=$query" HAVING "
+      else
+         query=$query" AND "
+      fi
+      query=$query" COUNT(*)=COUNT(IF("
+      query=$query" NOT ISNULL("${needs[$k]}"Status.fStartTime) "
+      query=$query" AND NOT ISNULL("${needs[$k]}"Status.fStopTime) "
+      query=$query" AND ISNULL("${needs[$k]}"Status.fReturnCode) "
+      query=$query" , 1, NULL)) "
+   done
 }
 
@@ -317 +271 @@
    # reset the variable for the number of the next step 
    process=
-   printprocesslog "INFO getting todo..."
+   printprocesslog "DEBUG getting todo for step $step..."
    getstepinfo
    # get query
-   query=" SELECT "`echo ${prims[0]} | sed -e 's/,//g'`
-   for (( j=1 ; j < ${#prims[@]} ; j++ ))
-   do
-      query=$query", "`echo ${prims[$j]} | sed -e 's/,//g'`
-   done
-   # get middle par of query 
-   middlepartofquery
-   # add requirement for production host in case it is needed
-   if [ "$2 " != " " ]
-   then 
-      query=$query" AND fProductionHostKEY=$2 "
-   fi
+   query=" SELECT "${prims[@]}
+   getstatusquery $2
    # order by priority to the the number of the next step to be done
    query=$query" ORDER BY "$step"Status.fPriority desc "
@@ -341 +285 @@
    fi
    # print query 
-   #echo " gettodo QUERY: "$query
-   printprocesslog "INFO gettodo QUERY: "$query
+   printprocesslog "DEBUG gettodo for step $step QUERY: "$query
    # execute query 
    if ! process=`mysql -s -u $us --password=$pw --host=$ho $db -e " $query "`
@@ -352 +295 @@
    if [ "$process" = "" ]
    then
-      printprocesslog "INFO => nothing to do"
+      printprocesslog "DEBUG => nothing to do"
       finish
    else
@@ -364 +307 @@
 {
    # reset the variable for the number of steps to be done
-   numproc=
+   numproc=0
    getstepinfo
    # get query
-   query=" SELECT COUNT(*), 1 " # the 1 is just for grouping
-   # get middle part of query 
-   middlepartofquery
-   # add requirement for production host in case it is needed
-   if [ "$1 " != " " ]
-   then 
-      query=$query" AND fProductionHostKEY=$1 "
-   fi
-   # group by an 'artifical' column to get the number of lines
-   query=$query" GROUP BY 2 "
-   # printing query
-   #echo " getstatus QUERY: "$query
-   printprocesslog "INFO getstatus QUERY: "$query
+   query=" SELECT "${prims[@]}
+   getstatusquery $1
+   # print query
+   printprocesslog "DEBUG getstatus for step $step QUERY: "$query
    # execute query
-   if ! numprocs=( `mysql -s -u $us --password=$pw --host=$ho $db -e " $query "` )
+   if ! numproc=`mysql -s -u $us --password=$pw --host=$ho $db -e " $query " | wc -l`
    then
       printprocesslog "ERROR could not query number of processes from db (program: $program, function getstatus)"
       echo `date +%F\ %T`" ERROR could not query number of processes from db (program: $program, function getstatus)"
       continue
-   fi
-   # get number of processes from mysql result
-   if [ "${numprocs[1]}" = "" ]
-   then
-      numproc=0
-   else
-      numproc=${numprocs[0]}
    fi
 }
@@ -415 +342 @@
    # evaluate the status values
    case $@ in
-      start)   printprocesslog "INFO setstatus start"
+      start)   printprocesslog "DEBUG setstatus start"
                starttime="Now()"
                ;;
        stop)   case $check in
-                  ok)  printprocesslog "INFO setstatus stop - ok"
+                  ok)  printprocesslog "DEBUB setstatus stop - ok"
                        starttime=noreset
                        stoptime="Now()"
                        ;;
-                  no)  printprocesslog "INFO setstatus stop - nothing new"
+                  no)  printprocesslog "DEBUG setstatus stop - nothing new"
                        check="ok"
                        ;;
-                   *)  printprocesslog "INFO setstatus stop - failed"
+                   *)  printprocesslog "DEBUG setstatus stop - failed"
                        starttime=noreset
                        stoptime="Now()"
@@ -443 +370 @@
                ;;
    esac
+   
    # get 
    getstepinfo
    
    # get the influences from the steps.rc by evaluating the needs of all steps
-   influences=`grep $step $steps | grep "Needs" | grep -v "$step[.]Needs" | cut -d'.' -f1`
+   othersteps=`grep $step $steps | grep "Needs" | grep -v "$step[.]Needs" | cut -d'.' -f1`
    
    # get query
-   query=" UPDATE "$step"Status "
-   # add joins to the influenced tables
-   specialinfljoin=`getjoin $step "SpecialInfluence"`
-   if ! [ "$specialinfljoin" = "" ]
-   then
-      query=$query" "$specialinfljoin
-   fi
-   for influence in $influences
-   do
-      infljoin=`getjoin $influence "Influence"`
-      if [ "$infljoin" = "" ]
-      then
-         inflprims=( `getprimary $influence` )
-         query=$query" LEFT JOIN "$influence"Status USING (${inflprims[@]}) "
-      else 
-         query=$query" LEFT JOIN "$influence"Status USING ("$infljoin") "
-      fi
-      specialinfljoin2=`getjoin $influence "SpecialInfluence"`
-      if ! [ "$specialinfljoin2" = "" ]
-      then
-         query=$query" "$specialinfljoin2
-      fi
-   done
+   query=" UPDATE "
+   getalljoins
    # set the status values according to the new status of the step
    query=$query" SET "
@@ -499 +406 @@
    done   
    # print query
-   #echo " setstatus QUERY: "$query
-   printprocesslog "INFO setstatus QUERY: "$query
+   printprocesslog "DEBUG setstatus for step $step QUERY: "$query
    # execute query
    if ! mysql -s -u $us --password=$pw --host=$ho $db -e " $query "
@@ -513 +419 @@
 {
    getdbsetup
-   printprocesslog "INFO sendquery QUERY: "$query
+   printprocesslog "DEBUG sendquery QUERY: "$query
    if ! val=`mysql -s -u $us --password=$pw --host=$ho $db -e " $query "`
    then