Monday 1 November 2010

Useful Queries for TRACK2SQL

What SQL queries are most useful when analyzing problems using TRACK2SQL?


There are several ways to track various server problems using TRACK2SQL, and this article discusses some of the most common queries you might use. Times returned by these queries are in milliseconds.

The following SQL statement returns user, command and "IO used", to show which processes consumed the most IO.

SELECT user,cmd,SUM(pagesIn+pagesOut) as ioFROM tableUse JOIN process USING ( processKey)GROUP BY tableUse.processKey ORDER BY io DESC LIMIT 25;

Example

Output from this SQL statement:

+----------+-----------------+--------+ | user | cmd | io | +----------+-----------------+--------+ | apps | user-fstat | 438251 | | chris | user-changes | 310286 | | jack | user-changes | 308542 | | leigh | user-changes | 308185 | | apps | user-changes | 307771 | | jack | user-changes | 307608 | | adam | user-changes | 307338 | | adam | user-changes | 307338 | | chris | user-changes | 306809 | | super | user-obliterate | 235944 | | super | user-obliterate | 235907 | | super | user-obliterate | 154358 | | super | user-obliterate | 154349 | | super | user-obliterate | 154349 | | super | user-obliterate | 154349 | | super | user-obliterate | 154349 | | super | user-obliterate | 154349 | | super | user-obliterate | 154349 | | super | user-obliterate | 154348 | | super | user-obliterate | 154348 | | super | user-obliterate | 154347 | | kate | user-revert | 145491 | | rachel | user-jobs | 120240 | | george | user-jobs | 120212 | | george | user-jobs | 120212 | +----------+-----------------+--------+ 25 rows in set (0.44 sec)

The following SQL statement returns a list of users, commands, and the length of the compute phase to show which processes had the longest compute phase:

SELECT user,cmd,MAX(readHeld+writeHeld)-MAX(readWait+writeWait) as compute FROM tableUse JOIN process USING (processKey) GROUP BY tableUse.processKey ORDER BY compute DESC LIMIT 25;

Example

Example output from the SQL statement.

+----------+-----------------+---------+ | user | cmd | compute | +----------+-----------------+---------+ | apps | user-jobs | 37647 | | browser | user-fstat | 32380 | | adam | user-jobs | 31404 | | jack | user-jobs | 30226 | | adam | user-jobs | 29852 | | browser | user-fstat | 21551 | | browser | user-fstat | 15429 | | browser | user-fstat | 14606 | | browser | user-fstat | 14570 | | browser | user-fstat | 12726 | | brett | user-files | 12701 | | brett | user-files | 12701 | | super | user-obliterate | 10441 | | super | user-obliterate | 10304 | | super | user-obliterate | 10302 | | super | user-obliterate | 10231 | | super | user-obliterate | 10214 | | super | user-obliterate | 10209 | | super | user-obliterate | 10193 | | super | user-obliterate | 10162 | | super | user-obliterate | 10103 | | super | user-obliterate | 10081 | | jeff | user-changes | 8760 | | charles | user-fstat | 8561 | | super | user-obliterate | 8168 | +----------+-----------------+---------+ 25 rows in set (0.29 sec)

The following SQL statement provides the average wait time from the "tableUse" table:

SELECT AVG(readWait+writeWait) as wait FROM tableUse;

Example

Example output from the SQL statement.

+---------+ | wait | +---------+ | 50.3600 | +---------+ 1 row in set (0.12 sec)

The following SQL statement returns relative values for "read" and "write" for a ratio of read and write for table usage; total usage is 1 (both values added together):

SELECT SUM(pagesIn)/SUM(pagesIn+pagesOut) as readPct,SUM(pagesOut)/SUM(pagesIn+pagesOut) as writePct FROM tableUse;

Example

An example of the data returned by this SQL statement.

+---------+----------+ | readPct | writePct | +---------+----------+ | 0.9992 | 0.0008 | +---------+----------+ 1 row in set (0.13 sec)

The following SQL statement gives timestamps showing the start and end of the log:

SELECT MIN(FROM_UNIXTIME(time)) as start, MAX(FROM_UNIXTIME(time)) as end FROM process;

The date and time is displayed in the following format:

yyyy-mm-dd hh:mi:ss

Example

Example output from this SQL statement.

+---------------------+---------------------+ | start | end | +---------------------+---------------------+ | 2007-01-09 10:28:31 | 2007-02-20 18:21:51 | +---------------------+---------------------+ 1 row in set (0.47 sec)

The following SQL statement returns the user, and the total read- and write-locks held by that user, to help you determine which users were the worst offenders.

SELECT user,SUM(readHeld+writeHeld) as held FROM tableUse JOIN process USING ( processKey ) GROUP BY user ORDER BY held DESC LIMIT 25;

Example

Example output from the SQL command.

+-----------+----------+ | user | held | +-----------+----------+ | charles | 14983217 | | super | 1802107 | | laura | 1585269 | | browser | 505506 | | build | 489437 | | brian | 367029 | | jason | 313469 | | apps | 306058 | | john | 193512 | | liz | 164194 | | adam | 159873 | | mike | 131956 | | jimmy | 122619 | | brett | 113818 | | kate | 104233 | | jack | 99173 | | www | 88942 | | scott | 70289 | | leigh | 67382 | | paul | 64768 | | doris | 54699 | | jeff | 53549 | | tony | 52796 | | george | 46388 | | mark | 46266 | +-----------+----------+ 25 rows in set (0.50 sec)

Wait locks do not count against hold times in some queries; negative values can result. The thinking is that if a command spends five minutes holding a lock on db.foo waiting to acquire a lock on db.bar, and only five seconds doing actual work, it should be counted as five seconds, rather than 5m5s.


View the original article here

No comments:

Post a Comment