queries.py #7

  • //
  • guest/
  • perforce_software/
  • utils/
  • log_analyzer/
  • psla/
  • app/
  • queries.py
  • View
  • Commits
  • Open Download .zip Download (6 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': 'Minute',
                 'field': 'Minute'},
           'y': {'title': 'Num Cmds',
                 'field': 'MaxCommands'},
           'sql': """
                SELECT SUBSTR(startTime, 0, 17) as 'Minute', MAX(running) as 'MaxCommands'
                FROM process
                GROUP BY Minute;
            """},
        {'title': 'Cmd Totals',
            'graph_type': 'bar',
            'explanation': 'How many commands of each type',
            'x': {'title': 'Cmd',
                 'field': 'Cmd'},
            'y': {'title': 'Num Cmds',
                 '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',
            'graph_type': 'bar',
            'explanation': 'How many commands of each type per user',
            'x': {'title': 'User',
                 'field': 'User'},
            'y': {'title': 'Num Cmds',
                 'field': 'NumCmds'},
            'sql': """
                select user as 'User', count(user) as 'NumCmds'
                from process
                group by user
                order by NumCmds desc
                limit 15;
                """}]

queries = [{'title': 'Time Frame',
            'explanation': 'Start and end time for this log',
            'sql': """
                select MIN(starttime) as Start, MAX(starttime) as End
                from process;
            """},
           {'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, user, cmd, pid, tablename,
            	    maxReadHeld, maxWriteHeld, totalReadWait, totalWriteWait
                FROM tableUse JOIN process USING (processKey)
                WHERE (totalReadHeld > 10000 or totalWriteHeld > 10000)
                    AND tablename not like 'meta%'
                    AND tablename not like 'clients%'
                ORDER BY startTime, endTime
                    limit 30;
            """},

            {'title': 'Blocking Commands including meta tables',
            'explanation': 'Commands that blocked others including meta_db and clients locks',
            'sql': """
            	SELECT startTime, endTime, running, user, cmd, pid, tablename,
            	    maxReadHeld, maxWriteHeld, totalReadWait, totalWriteWait
                FROM tableUse JOIN process USING (processKey)
                WHERE (totalReadHeld > 10000 or totalWriteHeld > 10000)
                ORDER BY startTime, endTime
                    limit 30;
            """},

            {'title': 'Block commands - victims of the above',
            'explanation': '',
            'sql': """
                SELECT startTime, endTime, computedLapse, running, user, cmd, pid, tablename,
                    maxReadHeld, maxWriteHeld,totalReadWait, totalWriteWait
                FROM tableUse JOIN process USING (processKey)
                WHERE (totalReadWait > 10000) or (totalWriteWait > 10000)
                ORDER BY startTime, endTime
                limit 30;
            """},

            {'title': 'Longest Compute Phases',
            'explanation': 'Longest Compute Phases',
            'sql': """
                SELECT process.processKey, user, cmd, args, startTime,
                CASE WHEN MAX(totalreadHeld + totalwriteHeld) > MAX(totalreadWait + totalwriteWait) THEN
                    MAX(totalreadHeld + totalwriteHeld) - MAX(totalreadWait + totalwriteWait)
                ELSE
                    MAX(totalreadHeld + totalwriteHeld)
                END
                AS compute
                FROM tableUse JOIN process USING (processKey)
                GROUP BY tableUse.processKey
                ORDER BY compute DESC LIMIT 25
            """},

            {'title': 'Consumed Most IO',
            'explanation': 'Consumed Most IO',
            'sql': """
                SELECT user, cmd, SUM(pagesIn+pagesOut) as io, process.processKey, process.args
                FROM tableUse JOIN process USING (processKey)
                GROUP BY tableUse.processKey ORDER BY io
                DESC LIMIT 25
            """},

            {'title': 'Read Write Percentage',
            'explanation': '',
            'sql': """
                SELECT SUM(pagesIn)/SUM(pagesIn+pagesOut) * 100 as readPct,
                    SUM(pagesOut)/SUM(pagesIn+pagesOut) * 100 as writePct
                FROM tableUse
            """},

           # {'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