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 EXPLAIN.