""" 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': 'Cmds started per Ten minutes',
'id': 'cmds_per_10_minute',
'graph_type': 'bar',
'explanation': 'Commands started in each ten minute bucket',
'x': {'title': 'Ten Minute',
'field': 'TenMinute'},
'y': {'title': 'Num Cmds',
'field': 'NumCommands'},
'sql': """
SELECT SUBSTR(startTime, 0, 16) as 'TenMinute', COUNT(cmd) as 'NumCommands'
FROM process
GROUP BY TenMinute;
"""},
{'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',
'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': 'Cmd Totals by User',
'explanation': 'How many commands of each type per user',
'sql': """
select cmd, count(cmd) as CountCmds, user
from process
group by cmd, user
order by CountCmds desc limit 30;
"""},
{'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%'
AND tablename not like 'changes%'
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': 'System CPU',
'explanation': 'Top 25 commands by system CPU',
'sql': """
select pid, user, cmd, completedLapse, rpcRcv, rpcSnd, uCpu, sCpu, startTime, endTime
from process
order by sCpu desc limit 25
"""},
{'title': 'User CPU',
'explanation': 'Top 25 commands by user CPU',
'sql': """
select pid, user, cmd, completedLapse, rpcRcv, rpcSnd, uCpu, sCpu, startTime, endTime
from process
order by uCpu desc limit 25
"""},
# {'title': '',
# 'explanation': '',
# 'sql': """
# """},
]