Using QPC_HISTORY for Statistics

Henry Hwang -

Using QPC_HISTORY

Clustrix collects statistics on not just cluster health, but also on queries and ranks them based on how much CPU the query takes, rows read, rows written, execution count and execution time.

Sometimes you just want to see what are your heavy queries are or perhaps trying to figure out a slowdown in performance without using the UI (the UI queries the same CLUSTRIX_STATD.QPC_HISTORY table).

You could parse out the query logs as described in How to Read the Query Logs, but sometimes you want something quick and easy.  You can query CLUSTRIX_STATD.QPC_HISTORY.

Main things to keep in mind regarding stats in QPC_HISTORY:

  • Stats are collected every five minutes and inserted into the qpc_history table.
  • Stats in each entry are collected from the previous five minutes of the timestamp.
  • Stats older than 24 hours are rolled into one hour entries where is_rollup is then set to 1 and all the stats in that entry are from the previous hour of the timestamp.
  • Rank is based on how much of the CPU the query takes, rows read, rows written, execution count and execution time. The higher the rank, the more resources that particular query took.
  • Stats older than 7 days will get purged.
  • Stats with rank > 100 will get purged after 24 hours.
  • Sometimes, the same query will have different query keys because the query itself was recompiled due to staleness or due to changes to the probability distribution.
mysql> describe clustrix_statd.qpc_history;
+-------------------+---------------------+------+-----+-------------------+
| Field             | Type                | Null | Key | Default           | 
+-------------------+---------------------+------+-----+-------------------+
| query_key         | bigint(20) unsigned | NO   | PRI | NULL              | 
| timestamp         | timestamp           | NO   | PRI | current_timestamp | 
| database          | varchar(256)        | YES  |     | NULL              |
| statement         | varchar(8196)       | YES  |     | NULL              |
| exec_count        | int(11) unsigned    | YES  |     | NULL              |
| exec_ms           | bigint(20) unsigned | YES  |     | NULL              |
| exec_us           | bigint(20) unsigned | YES  |     | NULL              |
| avg_exec_ms       | double              | YES  |     | NULL              |
| avg_exec_us       | double              | YES  |     | NULL              |
| min_exec_ms       | double              | YES  |     | NULL              |
| max_exec_ms       | double              | YES  |     | NULL              |
| compile_count     | int(11) unsigned    | YES  |     | NULL              |
| compile_ms        | bigint(20) unsigned | YES  |     | NULL              |
| rows_read         | bigint(20) unsigned | YES  |     | NULL              |
| avg_rows_read     | double              | YES  |     | NULL              |
| rows_written      | bigint(20) unsigned | YES  |     | NULL              |
| avg_rows_written  | double              | YES  |     | NULL              |
| rows_output       | bigint(20) unsigned | YES  |     | NULL              |
| avg_rows_output   | double              | YES  |     | NULL              |
| rank              | int(11) unsigned    | YES  |     | NULL              |
| is_rollup         | tinyint(4)          | NO   | PRI | 0                 |
| rows_inserted     | bigint(20) unsigned | YES  |     | NULL              |
| avg_rows_inserted | double              | YES  |     | NULL              |
| rows_replaced     | bigint(20) unsigned | YES  |     | NULL              |
| avg_rows_replaced | double              | YES  |     | NULL              |
| rows_deleted      | bigint(20) unsigned | YES  |     | NULL              |
| avg_rows_deleted  | double              | YES  |     | NULL              |
+-------------------+---------------------+------+-----+-------------------+
27 rows in set (0.01 sec)

Main columns of interest are:

  • timestamp: contains stats from the previous 5 minutes, unless it's rolled up after 24 hours where the entry would have stats from the previous hour.
  • exec_count: total number of executions for this query during the previous stats window 
  • exec_ms: total time in ms the query took during the period
  • avg_exec_ms: average execution time for the query. Queries with long avg_exec_ms times are potential candidates for optimization.
  • max_exec_ms: max execution time...may require further investigation if the max_exec_ms exceeds avg_exec_ms by a significant margin. May indicate resource contention or some blocking issue.
  • rows_read: total number of rows read for the stats window.
  • avg_rows_read: average rows read per query execution. More rows read, the more the cluster has to work. Likely candidate for query optimization if query needs to read a lot of rows.
  • rank: useful to find top ranked queries.
  • is_rollup: stats older than 24 hours will get rolled into an hourly entry.

Find top queries from the last 24 hours by execution counts (may indicate a misconfigured application or some test script that isn't working as expected):

SELECT query_key, 
       LEFT(statement, 100), 
       Sum(exec_count)           AS exec_count, 
       Round(Avg(avg_rows_read)) AS avg_RR, 
       Round(Avg(avg_exec_ms))   AS avg_exec_ms 
FROM   clustrix_statd.qpc_history 
WHERE  timestamp BETWEEN ( Now() - INTERVAL 24 hour ) AND Now() 
       AND DATABASE != 'clustrix_statd' 
GROUP  BY query_key 
ORDER  BY exec_count DESC, 
          avg_rr DESC LIMIT 100;

Find top queries from the last 24 hours by rows read (may indicate a poorly optimized query):

SELECT query_key, 
       LEFT(statement, 100), 
       Sum(exec_count)           AS exec_count, 
       Round(Avg(avg_rows_read)) AS avg_RR, 
       Round(Avg(avg_exec_ms))   AS avg_exec_ms 
FROM   clustrix_statd.qpc_history 
WHERE  timestamp BETWEEN ( Now() - INTERVAL 24 hour ) AND Now() 
       AND DATABASE != 'clustrix_statd' 
GROUP  BY query_key 
ORDER  BY avg_rr DESC, 
          exec_count DESC LIMIT 100; 

Find top queries by total exec time (may indicate a complex query that takes a lot of time to execute (in conjunction with high rows read)):

SELECT query_key, 
       LEFT(statement, 100), 
       Sum(exec_count)           AS exec_count, 
       Round(Avg(avg_rows_read)) AS avg_RR, 
       Round(Avg(avg_exec_ms))   AS avg_exec_ms 
FROM   clustrix_statd.qpc_history 
WHERE  timestamp BETWEEN ( Now() - INTERVAL 24 hour ) AND Now() 
       AND DATABASE != 'clustrix_statd' 
GROUP  BY query_key 
ORDER  BY avg_exec_ms DESC, 
          exec_count DESC LIMIT 100; 

 Look at last 30 top 3 ranked queries:

SELECT timestamp, 
       DATABASE, 
       rank, 
       LEFT(statement,100), 
       exec_count, 
       avg_rows_read, 
       avg_exec_ms 
FROM   clustrix_statd.qpc_history 
WHERE  rank <4 AND DATABASE !='clustrix_statd' ORDER BY timestamp DESC, rank ASC LIMIT 30;


Some Tips

  • Narrow down the timestamp period if you have an idea of when an issue occurred.
  • Specify the table name in the where clause with the like predicate: where statement like '%table_name%' if you know which table may be the culprit to your problem.
  • Look at rows_written if you're interested in write queries.
  • If a query seems to be running slower than normal, compare the avg_exec_ms of that query from a time when it seems to be running fine and see what else might be running around the same time frame. Could indicate a resource contention (other large running queries or blocking queries). Looking at clustrix_statd.QPC_HISTORY in conjunction with the query logs would help.
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk