Open topic with navigation
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
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.
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
|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