Changeset 9853


Ignore:
Timestamp:
08/13/10 10:19:09 (14 years ago)
Author:
Daniela Dorner
Message:
implemented faster queries for the dependencies between the steps
Location:
trunk/Mars
Files:
3 edited

Legend:

Unmodified
Added
Removed

  • trunk/Mars/Changelog

    r9851 r9853  
    1818
    1919                                                 -*-*- END OF LINE -*-*-
     20
     21 2010/08/13 Daniela Dorner
     22
     23   * datacenter/scripts/sourcefile:
     24     - implemented faster queries for the dependencies of the steps
     25     - simplified code
     26     - introduced new level for printprocesslog: DEBUG
     27
     28   * resources/steps_fact.rc:
     29     - adapted to change in queries in sourcefile
     30
     31
    2032
    2133 2010/08/12 Thomas Bretz
    … …  
    148160
    149161
     162
    150163 2010/07/23 Reiner Rohlfs
    151164

  • trunk/Mars/datacenter/scripts/sourcefile

    r9618 r9853  
    9898   if ! [ "$lockfile" = "" ] && ls $lockfile >/dev/null 2>&1
    9999   then
    100       printprocesslog "INFO " `rm -v $lockfile`
    101    fi
    102    printprocesslog "INFO finished $0"
     100      printprocesslog "DEBUG " `rm -v $lockfile`
     101   fi
     102   printprocesslog "DEBUG finished $0"
    103103   exit
    104104}
    … …  
    154154      exit
    155155   else
    156       printprocesslog "INFO created lockfile $lockfile"
     156      printprocesslog "DEBUG created lockfile $lockfile"
    157157   fi
    158158}
    … …  
    183183}
    184184
     185# function to get information from the setupfile $steps
     186function getfromsetup()
     187{
     188   grep $1"[.]"$2":" $steps | grep -v '#' | sed -e "s/$1[.]$2://"
     189}
     190
    185191# function to get the needed information from the dependencies-file steps.rc
    186192function getstepinfo()
    187193{
    188194   getdbsetup
    189    needs=`grep "$step[.]Needs:" $steps | grep -v '#' | sed -e "s/$step[.]Needs://"`
    190    noderestricted=`grep "$step[.]NodeRestricted:" $steps | grep -v '#' | sed -e "s/$step[.]NodeRestricted://" -e 's/ //g'`
    191    prims=( `grep "$step[.]Primaries:" $steps | grep -v '#' | sed -e "s/$step[.]Primaries://"` )
    192 #   echo " needs: $needs"
    193 #   echo " noderestricted: $noderestricted"
    194 #   echo " prims: ${prims[@]}"
    195 }
    196 
    197 # function to get the primaries of a step from the dependencies-file steps.rc
    198 function getprimary()
    199 {
    200    getdbsetup
    201    grep $@"[.]Primaries:" $steps | grep -v '#' | sed -e "s/$@[.]Primaries://"
    202 }
    203 
    204 # function to get the join of a step
    205 #  from the dependencies-file steps.rc
    206 # $1 gives the step
    207 # $2 gives the type of join
    208 function getjoin()
    209 {
    210    getdbsetup
    211    grep $1"[.]"$2"Join:" $steps | grep -v '#' | sed -e "s/$1[.]$2Join://"
    212 }
    213 
    214 # function to get the needs of a step from the dependencies-file steps.rc
    215 function getneeds()
    216 {
    217    getdbsetup
    218    grep $@"[.]Needs:" $steps | sed -e "s/$@[.]Needs://"
    219 }
    220 
    221 # subquery which is needed to check the
    222 #   status of the steps with different primaries
    223 function subquerypart()
    224 {
    225    query=$query" SELECT COUNT(*) FROM "$need"Status "`getjoin $need "SpecialInfluence"`
    226    needinfluences=`grep $need $steps | grep "Needs" | grep -v "$need[.]Needs" | cut -d'.' -f1`
    227    for needinfl in $needinfluences
     195   needs=( `getfromsetup $step "Needs"` )
     196   noderestricted=`getfromsetup $step "NodeRestricted"`
     197   prims=( `getfromsetup $step "Primaries"` )
     198   maintable=`getfromsetup $step "MainTable" | sed -e "s/\ //g"`
     199#   echo " maintable: "$maintable
     200#   echo " needs: "${needs[@]}
     201#   echo " noderestricted: "$noderestricted
     202#   echo " prims: "${prims[@]}
     203}
     204
     205# function to get the joins needed for the get/set status queries
     206function getalljoins()
     207{
     208   # add table
     209   query=$query" "$maintable"Status"
     210   # add special join
     211   query=$query" "`getfromsetup $maintable "SpecialJoin"`
     212   # add join for step unless step is the same as maintable
     213   if ! [ "$step" = "$maintable" ]
     214   then
     215      query=$query" LEFT JOIN "$step"Status USING("${prims[@]}") "
     216   fi
     217   # add joins for influences or needs
     218   for otherstep in ${othersteps[@]}
    228219   do
    229       needinfljoin=`getjoin $needinfl "Influence"`
    230       if ! [ "$needinfljoin" = "" ]
    231       then
    232          query=$query" LEFT JOIN "$needinfl"Status USING ("$needinfljoin") "
     220      if ! [ "$otherstep" = "$maintable" ]
     221      then
     222         query=$query" LEFT JOIN "$otherstep"Status USING("`getfromsetup $otherstep "Primaries"`") "
    233223      fi
    234       query=$query" "`getjoin $needinfl "SpecialInfluence"`" "
    235    done
    236    query=$query" WHERE "`echo ${prims[0]} | sed -e 's/,//g'`"="$step"Status."`echo ${prims[0]} | sed -e 's/,//g'`
    237    for (( j=1 ; j < ${#prims[@]} ; j++ ))
    238    do
    239       query=$query" AND "`echo ${prims[$j]} | sed -e 's/,//g'`"="$step"Status."`echo ${prims[$j]} | sed -e 's/,//g'`
    240224   done
    241225}
    … …  
    243227# function to create the middle part of a query
    244228#  which is identical for the functions getstatus() and gettodo()
    245 function middlepartofquery()
     229function getstatusquery()
    246230{
    247231   # add from which table the information is queried
    248    query=$query" FROM "$step"Status "
    249    # add the joins to the tables in which the status of the preceding steps is stored
    250    for need in $needs
    251    do
    252       needprims=( `getprimary $need` )
    253       # make sure that the correct joins are used
    254       specialneedjoin=`getjoin $need "SpecialNeed"`
    255       if [ "$specialneedjoin" = "" ]
    256       then
    257          query=$query" LEFT JOIN "$need"Status USING (${needprims[@]}) "
    258       else
    259          query=$query" "$specialneedjoin" "
    260          query=$query" LEFT JOIN "$need"Status USING (${needprims[@]}) "
    261          break
    262       fi
    263    done
     232   query=$query" FROM "
     233   othersteps=${needs[@]}
     234   getalljoins
    264235   # add condition
    265236   query=$query" WHERE "
    266    # add condition for the status of the peceding steps
    267    counter=0
    268    specialneedjoin=
    269    specialinfljoin=
    270    for need in $needs
    271    do
    272       if [ $counter -gt 0 ]
    273       then
    274          query=$query" AND "
    275       fi
    276       specialneedjoin=$specialneedjoin" "`getjoin $need "SpecialNeed"`
    277       specialinfljoin=$specialinfljoin" "`getjoin $need "SpecialInfluence"`
    278       if [ "$specialneedjoin" = " " ]
    279       then
    280          # condition for step with same primaries
    281          query=$query" NOT ISNULL("$need"Status.fStartTime) AND "
    282          query=$query" NOT ISNULL("$need"Status.fStopTime) AND "
    283          query=$query" ISNULL("$need"Status.fReturnCode) "
    284          specialneedjoin=
    285          specialinfljoin=
    286       else
    287          # special query to check steps which
    288          #   do not have the same primaries
    289          # comparison of
    290          #     number of rows
    291          #   with
    292          #     number of rows which are done
    293          query=$query" ( "
    294          subquerypart
    295          query=$query" ) = ( "
    296          subquerypart
    297          query=$query" AND NOT ISNULL("$need"Status.fStartTime) "
    298          query=$query" AND NOT ISNULL("$need"Status.fStopTime) "
    299          query=$query" AND ISNULL("$need"Status.fReturnCode)) "
    300       fi
    301       counter=`echo $counter + 1 | bc -l`
    302    done
    303    # add condition for the status of the step itself
    304    if [ $counter -gt 0 ]
    305    then
    306       query=$query" AND "
    307    fi
     237   # add condition for step, i.e. step is not yet done
    308238   query=$query" ISNULL("$step"Status.fStartTime) "
    309239   query=$query" AND ISNULL("$step"Status.fStopTime) "
    310240   query=$query" AND ISNULL("$step"Status.fReturnCode) "
     241   # add requirement for production host in case it is needed
     242   if [ "$1 " != " " ]
     243   then
     244      query=$query" AND fProductionHostKEY=$2 "
     245   fi
     246   if ! echo $query | grep UPDATE >/dev/null 2>&1
     247   then
     248      query=$query" GROUP BY "${prims[@]}
     249   fi
     250   # add condition for needs, i.e. that step is done
     251   for (( k=0 ; k < ${#needs[@]} ; k++ ))
     252   do
     253      if [ $k -eq 0 ]
     254      then
     255         query=$query" HAVING "
     256      else
     257         query=$query" AND "
     258      fi
     259      query=$query" COUNT(*)=COUNT(IF("
     260      query=$query" NOT ISNULL("${needs[$k]}"Status.fStartTime) "
     261      query=$query" AND NOT ISNULL("${needs[$k]}"Status.fStopTime) "
     262      query=$query" AND ISNULL("${needs[$k]}"Status.fReturnCode) "
     263      query=$query" , 1, NULL)) "
     264   done
    311265}
    312266
    … …  
    317271   # reset the variable for the number of the next step
    318272   process=
    319    printprocesslog "INFO getting todo..."
     273   printprocesslog "DEBUG getting todo for step $step..."
    320274   getstepinfo
    321275   # get query
    322    query=" SELECT "`echo ${prims[0]} | sed -e 's/,//g'`
    323    for (( j=1 ; j < ${#prims[@]} ; j++ ))
    324    do
    325       query=$query", "`echo ${prims[$j]} | sed -e 's/,//g'`
    326    done
    327    # get middle par of query
    328    middlepartofquery
    329    # add requirement for production host in case it is needed
    330    if [ "$2 " != " " ]
    331    then
    332       query=$query" AND fProductionHostKEY=$2 "
    333    fi
     276   query=" SELECT "${prims[@]}
     277   getstatusquery $2
    334278   # order by priority to the the number of the next step to be done
    335279   query=$query" ORDER BY "$step"Status.fPriority desc "
    … …  
    341285   fi
    342286   # print query
    343    #echo " gettodo QUERY: "$query
    344    printprocesslog "INFO gettodo QUERY: "$query
     287   printprocesslog "DEBUG gettodo for step $step QUERY: "$query
    345288   # execute query
    346289   if ! process=`mysql -s -u $us --password=$pw --host=$ho $db -e " $query "`
    … …  
    352295   if [ "$process" = "" ]
    353296   then
    354       printprocesslog "INFO => nothing to do"
     297      printprocesslog "DEBUG => nothing to do"
    355298      finish
    356299   else
    … …  
    364307{
    365308   # reset the variable for the number of steps to be done
    366    numproc=
     309   numproc=0
    367310   getstepinfo
    368311   # get query
    369    query=" SELECT COUNT(*), 1 " # the 1 is just for grouping
    370    # get middle part of query
    371    middlepartofquery
    372    # add requirement for production host in case it is needed
    373    if [ "$1 " != " " ]
    374    then
    375       query=$query" AND fProductionHostKEY=$1 "
    376    fi
    377    # group by an 'artifical' column to get the number of lines
    378    query=$query" GROUP BY 2 "
    379    # printing query
    380    #echo " getstatus QUERY: "$query
    381    printprocesslog "INFO getstatus QUERY: "$query
     312   query=" SELECT "${prims[@]}
     313   getstatusquery $1
     314   # print query
     315   printprocesslog "DEBUG getstatus for step $step QUERY: "$query
    382316   # execute query
    383    if ! numprocs=( `mysql -s -u $us --password=$pw --host=$ho $db -e " $query "` )
     317   if ! numproc=`mysql -s -u $us --password=$pw --host=$ho $db -e " $query " | wc -l`
    384318   then
    385319      printprocesslog "ERROR could not query number of processes from db (program: $program, function getstatus)"
    386320      echo `date +%F\ %T`" ERROR could not query number of processes from db (program: $program, function getstatus)"
    387321      continue
    388    fi
    389    # get number of processes from mysql result
    390    if [ "${numprocs[1]}" = "" ]
    391    then
    392       numproc=0
    393    else
    394       numproc=${numprocs[0]}
    395322   fi
    396323}
    … …  
    415342   # evaluate the status values
    416343   case $@ in
    417       start)   printprocesslog "INFO setstatus start"
     344      start)   printprocesslog "DEBUG setstatus start"
    418345               starttime="Now()"
    419346               ;;
    420347       stop)   case $check in
    421                   ok)  printprocesslog "INFO setstatus stop - ok"
     348                  ok)  printprocesslog "DEBUB setstatus stop - ok"
    422349                       starttime=noreset
    423350                       stoptime="Now()"
    424351                       ;;
    425                   no)  printprocesslog "INFO setstatus stop - nothing new"
     352                  no)  printprocesslog "DEBUG setstatus stop - nothing new"
    426353                       check="ok"
    427354                       ;;
    428                    *)  printprocesslog "INFO setstatus stop - failed"
     355                   *)  printprocesslog "DEBUG setstatus stop - failed"
    429356                       starttime=noreset
    430357                       stoptime="Now()"
    … …  
    443370               ;;
    444371   esac
     372   
    445373   # get
    446374   getstepinfo
    447375   
    448376   # get the influences from the steps.rc by evaluating the needs of all steps
    449    influences=`grep $step $steps | grep "Needs" | grep -v "$step[.]Needs" | cut -d'.' -f1`
     377   othersteps=`grep $step $steps | grep "Needs" | grep -v "$step[.]Needs" | cut -d'.' -f1`
    450378   
    451379   # get query
    452    query=" UPDATE "$step"Status "
    453    # add joins to the influenced tables
    454    specialinfljoin=`getjoin $step "SpecialInfluence"`
    455    if ! [ "$specialinfljoin" = "" ]
    456    then
    457       query=$query" "$specialinfljoin
    458    fi
    459    for influence in $influences
    460    do
    461       infljoin=`getjoin $influence "Influence"`
    462       if [ "$infljoin" = "" ]
    463       then
    464          inflprims=( `getprimary $influence` )
    465          query=$query" LEFT JOIN "$influence"Status USING (${inflprims[@]}) "
    466       else
    467          query=$query" LEFT JOIN "$influence"Status USING ("$infljoin") "
    468       fi
    469       specialinfljoin2=`getjoin $influence "SpecialInfluence"`
    470       if ! [ "$specialinfljoin2" = "" ]
    471       then
    472          query=$query" "$specialinfljoin2
    473       fi
    474    done
     380   query=" UPDATE "
     381   getalljoins
    475382   # set the status values according to the new status of the step
    476383   query=$query" SET "
    … …  
    499406   done   
    500407   # print query
    501    #echo " setstatus QUERY: "$query
    502    printprocesslog "INFO setstatus QUERY: "$query
     408   printprocesslog "DEBUG setstatus for step $step QUERY: "$query
    503409   # execute query
    504410   if ! mysql -s -u $us --password=$pw --host=$ho $db -e " $query "
    … …  
    513419{
    514420   getdbsetup
    515    printprocesslog "INFO sendquery QUERY: "$query
     421   printprocesslog "DEBUG sendquery QUERY: "$query
    516422   if ! val=`mysql -s -u $us --password=$pw --host=$ho $db -e " $query "`
    517423   then

  • trunk/Mars/resources/steps_fact.rc

    r9618 r9853  
    11
     2Corsika.MainTable: Ceres
    23Corsika.Primaries: fRunNumber, fFileNumber
    34
     5Ceres.MainTable: Ceres
    46Ceres.Primaries: fRunNumber, fFileNumber, fCeresSetupKEY
    57Ceres.Needs: Corsika
    6 Ceres.SpecialNeedJoin: LEFT JOIN CeresInfo USING(fSequenceNumber, fCeresSetupKEY)
    7 Ceres.SpecialInfluenceJoin: LEFT JOIN CeresInfo USING(fRunNumber, fFileNumber, fCeresSetupKEY)
    8 Ceres.NeedJoin: fSequenceNumber, fCeresSetupKEY
    9 Ceres.InfluenceJoin: fRunNumber, fFileNumber
     8Ceres.SpecialJoin: LEFT JOIN CeresInfo USING(fRunNumber, fFileNumber, fCeresSetupKEY)
    109
     10SequenceFile.MainTable: Ceres
    1111SequenceFile.Primaries: fSequenceNumber
    1212
     13Callisto.MainTable: Ceres
    1314Callisto.Primaries: fSequenceNumber, fCeresSetupKEY
    1415Callisto.Needs: SequenceFile Ceres Corsika
    1516
     17Star.MainTable: Ceres
    1618Star.Primaries: fSequenceNumber, fCeresSetupKEY
    1719Star.Needs: Callisto SequenceFile Ceres Corsika
Note: See TracChangeset for help on using the changeset viewer.