queries.py #4

  • //
  • guest/
  • perforce_software/
  • utils/
  • log_analyzer/
  • psla/
  • app/
  • queries.py
  • View
  • Commits
  • Open Download .zip Download (4 KB)
""" These are the pre-configured SQL queries and graphs which are run
    by the code in routes.py
"""

graphs = [{'title': 'Cmds per minute',
           'graph_type': 'line',
           'explanation': 'Maximum number of concurrent commands each minute',
           'x': {'title': 'x axis',
                 'field': 'Minute'},
           'y': {'title': 'y axis',
                 'field': 'MaxCommands'},
           'sql': """
                SELECT SUBSTR(startTime, 0, 17) as 'Minute', MAX(running) as 'MaxCommands'
                FROM process
                GROUP BY Minute;
            """},
          {'title': 'Cmd Totals',
           'explanation': 'How many commands of each type',
           'x': {'title': 'x axis',
                 'field': 'Cmd'},
           'y': {'title': 'y axis',
                 'field': 'NumCmds'},
           'sql': """
                select cmd as 'Cmd', count(cmd) as 'NumCmds'
                from process
                group by cmd
                order by NumCmds desc
                limit 15;
            """},
            {'title': 'Cmds per user',
           'explanation': 'How many commands of each type per user',
           'x': {'title': 'x axis',
                 'field': 'User'},
           'y': {'title': 'y axis',
                 'field': 'NumCmds'},
           'sql': """
                select user as 'User', count(user) as 'NumCmds'
                from process
                group by user
                order by NumCmds desc
                limit 15;
            """}]

queries = [{'title': 'Cmd Totals',
           'graph_type': 'line',
            'explanation': 'How many commands of each type',

            'sql': """
                select cmd, count(cmd) as CountCmds from process
                group by cmd
                order by CountCmds desc limit 20;
            """},
           {'title': 'Average wait time',
            'explanation': 'Average wait time',
            'sql': """
                SELECT AVG(totalreadWait+totalwriteWait) as wait
                FROM tableUse;
            """},

           {'title': 'Worst lock offenders',
            'explanation': 'Users whose commands hold locks',
            'sql': """
            SELECT user, SUM(maxreadHeld+maxwriteHeld) as held
              FROM tableUse JOIN process USING (processKey)
              GROUP BY user ORDER BY held DESC LIMIT 25;
            """},

           {'title': 'Blocking Commands',
            'explanation': 'Commands that blocked others',
            'sql': """
            	SELECT startTime, endTime, running, cmd, pid,
                    tablename, maxReadHeld,
                    maxWriteHeld, totalReadWait, totalWriteWait
                FROM tableUse JOIN process USING (processKey)
                WHERE processkey = processkey AND (( totalReadHeld > 10000 or
                    totalWriteHeld > 10000 )) ORDER BY startTime, endTime
                    limit 20;
            """},
           {'title': 'Block commands - victims of the above',
            'explanation': '',
            'sql': """
                SELECT startTime, endTime, computedLapse, running, cmd, pid,
                    tablename, maxReadHeld,
                    maxWriteHeld,totalReadWait, totalWriteWait
                FROM tableUse JOIN process USING (processKey)
                WHERE processkey = processkey AND (( totalReadWait > 10000 or
                totalWriteWait > 10000 ))
                ORDER BY startTime, endTime
                limit 20;
            """}
           # {'title': '',
           #  'explanation': '',
           #  'sql': """
           #  """},
           ]

# Change User Description Committed
#12 25220 Robert Cowham Moved project files to new location: //guest/perforce_software/log-analyzer/psla/...
Required by Swarm project structure for workshop.
#11 24978 Robert Cowham Added new queries
#10 24321 Robert Cowham Allow canned queries to be included
#9 23933 Robert Cowham Create new seperate charting page
#8 23932 Robert Cowham Fix read/write percentage report
#7 23924 Robert Cowham Make bar charts and fix axis labels
#6 23842 Robert Cowham Tweak queries - have a version of blocking without meta tables
#5 23835 Robert Cowham New queries from P4RA.
#4 23834 Robert Cowham Order descending for graphs
#3 23793 Robert Cowham Limit queries
#2 23774 Robert Cowham Ensure chart labels don't overlap
#1 23771 Robert Cowham Error handling for queries - refactor and move to seperate file