log2sql-examples.sql #2

  • //
  • guest/
  • robert_cowham/
  • perforce/
  • utils/
  • log_analysis/
  • log2sql-examples.sql
  • View
  • Commits
  • Open Download .zip Download (3 KB)
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