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;