log2sql-examples.sql #2

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;