Identifying Historical Slow Queries Using System Tables
SYSTEM.QUERYSTATS
To know which of your finished queries took an excessive amount of time, examine the logs in the SYSTEM.QUERYSTATS table. The SYSTEM.QUERYSTATS table is also referred to as the Slow Query Log. By default, NuoDB stores the ten slowest queries that took more than ten seconds to execute. This can be modified by changing MAX_QUERY_COUNT and MIN_QUERY_TIME values in SYSTEM.PROPERTIES. Use the SET command (see SET) to change the default settings.
To find your slowest queries, run a simple select from SYSTEM.QUERYSTATS. (We use SET OUTPUT VERTICAL here to make the output more readable.)
SET OUTPUT VERTICAL;
SELECT * FROM system.querystats;
==================================== Row #1 ====================================
SQLSTRING: select * from t where x=?;
COUNT: 1
RUNTIME: 13010424
USER: CLOUD
SCHEMA: USER
NUMPARAM: 1
PARAMS: 0/string/2
NODEID: 1
NROWS: 1
UNIQUEID: 4
TIMESTAMP: 2013-04-23 12:29:53.512637
EXPLAIN:
==================================== Row #2 ====================================
SQLSTRING: select s from t2 join t3 on t2.s=t3.s;
COUNT: 1
RUNTIME: 14013823
USER: CLOUD
SCHEMA: USER
NUMPARAM: 0
PARAMS:
NODEID: 2
NROWS: 1
UNIQUEID: 2
TIMESTAMP: 2013-04-23 12:52:54.830913
EXPLAIN:This will give you the SQL string for the query, the number of times the query has executed, the execution time for the query in microseconds (exaggerated in this example for illustration purposes), information about the user, schema and any parameters as well as other information. TIMESTAMP is the time at which the query started. SYSTEM.QUERYSTATS is a Pseudo System Table.
Because SYSTEM.QUERYSTATS is a Pseudo System Table, when a NuoDB Transaction Engine (TE) shuts down, the slow queries that were executed against that TE will no longer be reported in the QUERYSTATS table.
QUERYSTATS Columns
| Column | Description |
|---|---|
SQLString |
SQL query string |
Count |
Number of times the query executed |
Runtime |
Execution time in microseconds; this is the last execution time and not an average |
User |
User who executed the query |
Schema |
Schema against which the query was run |
NumParam |
Number of parameters in the query |
Params |
Information about each parameter including location, type and value |
NodeID |
Internal ID for TE for this query |
NRows |
Number of rows returned by the query |
Timestamp |
When the query started |
Explain |
Shows the execution plan of the executed statement See |