queries.py #5

""" 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': 'Time Frame',
            'explanation': 'Start and end time in 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, 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 30;
            """},
           {'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 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': """
           #  """},
           ]