Locks Held for 10 seconds SELECT startTime, endTime, computedLapse, running, cmd, pid, tablename, maxReadHeld, maxWriteHeld,totalReadWait,totalWriteWait FROM tableUse JOIN process USING (processKey) WHERE (( totalReadHeld > 10000 or totalWriteHeld > 10000 )) ORDER BY startTime, endTime; Commands waiting for locks for over 30 seconds SELECT startTime, endTime, computedLapse, running, cmd, pid, tablename, maxReadHeld, maxWriteHeld,totalReadWait,totalWriteWait FROM tableUse JOIN process USING (processKey) WHERE (( totalReadWait > 30000 or totalWriteWait > 30000 )) ORDER BY startTime,endTime; Commands running per second (look for bottlenecks) SELECT SUBSTRING(startTime,1,19),MAX(running) FROM process GROUP BY SUBSTRING(startTime,1,19); 25 Longest computes SELECT process.processKey,user,cmd, startTime AS time, MAX(maxreadHeld+maxwriteHeld)-MAX(maxreadWait+maxwriteWait) AS compute FROM tableUse JOIN process USING (processKey) GROUP BY tableUse.processKey ORDER BY compute DESC LIMIT 25; Consumed Most I/O Not working SELECT user,cmd,SUM(pagesIn+pagesOut) as io FROM tableUse JOIN process USING (processKey) GROUP BY tableUse.processKey ORDER BY io DESC LIMIT 25; Average wait time SELECT AVG(totalreadWait+totalwriteWait) as wait FROM tableUse Read/write percentage SELECT SUM(pagesIn)/SUM(pagesIn+pagesOut) as readPct, SUM(pagesOut)/SUM(pagesIn+pagesOut) as writePct FROM tableUse Worst lock offenders SELECT user,SUM(maxreadHeld+maxwriteHeld) as held FROM tableUse JOIN process USING (processKey) GROUP BY user ORDER BY held DESC LIMIT 25 Blocking Commands 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; Block commands - victims of the above 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; Blocked and blocking locks 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 ) or (totalReadWait > 10000 or totalWriteWait > 10000)) ORDER BY startTime, endTime; Max running per second SELECT SUBSTRING(startTime,1,19),MAX(running) from process group by SUBSTRING(startTime,1,19) HAVING MAX(running) > 20 ; Individual blocked commands SELECT startTime, endTime, computedLapse, running, cmd, pid, tablename, MAX(maxReadWait), MAX(maxWriteWait) FROM tableUse JOIN process USING (processKey) WHERE processkey = processkey AND (( totalReadWait > 30000 or totalWriteWait > 30000 )) GROUP BY pid ORDER BY startTime, endTime; Individual commands that blocked SELECT startTime, endTime, running, cmd, pid, MAX(maxReadHeld), MAX(maxWriteHeld),MAX(maxReadWait),MAX(maxWriteWait) FROM tableUse JOIN process USING (processKey) WHERE processkey = processkey AND (( totalReadHeld > 30000 or totalWriteHeld > 30000 )) GROUP BY pid ORDER BY startTime, endTime;