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;
# | Change | User | Description | Committed | |
---|---|---|---|---|---|
#6 | 22907 | Robert Cowham | Updated examples and notes for SQLlite3 | ||
#5 | 22906 | Robert Cowham | Check for log file not existing. | ||
#4 | 21695 | Robert Cowham |
Sundry examples from Karl. Tweaked to use Sqlite syntax (e.g. SUBSTR not SUBSTRING). Also performance related ones. |
||
#3 | 10406 | Robert Cowham | Tweaked formatting slightly - no content changes. | ||
#2 | 10405 | Robert Cowham | Updated fuller set of reporting statements | ||
#1 | 10404 | Robert Cowham | Renamed | ||
//guest/robert_cowham/perforce/utils/log_analysis/lg2sql-examples.sql | |||||
#1 | 10403 | Robert Cowham | Examples - just getting started |