queries.py #9

""" These are the pre-configured SQL queries and graphs which are run
    by the code in routes.py
"""

graphs = [{'title': 'Cmds per minute',
           'id': 'cmds_per_minute',
           'graph_type': 'bar',
           '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',
            'id': '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',
            'id': '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': 'Percentage of pages read and pages written',
            'sql': """
                SELECT TOTAL(pagesIn) * 100.0 / (TOTAL(pagesIn)+TOTAL(pagesOut)) as readPct,
                    TOTAL(pagesOut) * 100.0 / (TOTAL(pagesIn)+TOTAL(pagesOut)) as writePct
                FROM tableUse
            """},

           # {'title': '',
           #  'explanation': '',
           #  'sql': """
           #  """},
           ]