Using SQL Observability Metrics to Identify Slow Query Performance

SQL Observability metrics are enabled by default for NuoDB 7.0 and later.

SQL observability tables store SQL statement performance metrics. These metrics are used to view the performance of queries previously executed on a client’s connection to a given Transaction Engine (TE), or across all TEs within the database. The metrics include statement count (the number of times the statement was executed), execution times, compile times, and the number of records fetched.

The metrics are stored per unique normalized query. Each statement is normalized by removing all literal information and replacing it with a ?. For example, SELECT f1 from t1 WHERE f1 > 123; is normalized to SELECT f1 from t1 WHERE f1 > ?;. This normalization allows the grouping of statements of the same type regardless of the literal values and gives more information on the types of queries rather than individual queries.

In the following example, there is only one set of metrics because there is only one unique normalized query.

Original queries:

SELECT f1 FROM t WHERE f1 > 10;
SELECT f1 FROM t WHERE f1 > 20;
SELECT f1 FROM t WHERE f1 > 30;

Normalized query:

SELECT F1 FROM T WHERE (F1) > (?);

Monitoring Performance Metrics

To monitor performance metrics, use the following two pseudo tables:

All currently stored metrics can be viewed immediately after executing a statement by querying the SYSTEM.LOCALQUERYPERFORMANCEMETRICS or SYSTEM.QUERYPERFORMANCEMETRICS pseudo tables. These tables provide information on compile time and execution time of queries, and to find queries that take longer to execute.

Modifying Default Settings

The metrics in the pseudo tables do not persist forever. Periodically, a logger task performs the following two tasks:

  • All the statement metrics from each TE in the database are aggregated, and the top n based on total execution time are written to log.

    The metrics are logged in the sql-statement-metrics logging category at the warn logging level and logged to nuoadmin.log by default.

  • All stored metrics on each TE are removed.

By default, 10,000 unique normalized queries can be stored in the table. At the start of every hour the top 10 queries will be written to logs and the stored metrics will be cleared.

These default values can be modified by configuring the following database options:

  • max-stmt-metrics-size

  • stmt-metrics-log-count

  • stmt-metrics-log-frequency

For more information, see Database Options.

Manually Clearing Local Metrics

If more statements are executed before the logger task automatically clears the local metrics, the performance metrics of new statements will not be recorded. To manually clear the local metrics and write to log, use:

ALTER DATABASE FLUSH QUERY PERFORMANCE METRICS

For more information, see ALTER DATABASE.

  • If there is only one TE, then the LOCALQUERYPERFORMANCEMETRICS pseudo table will be identical to the QUERYPERFORMANCEMETRICS pseudo table.

  • The contents of the pseudo tables are only visible to users with the DBA role.

  • In NuoDB 7.0, the aggregation is performed only if all TEs in the database are running the same version (i.e., NuoDB 7.0 and greater.) The QUERYPERFORMANCEMETRICS pseudo table will not return any rows and no data will be written to the log. However, the LOCALQUERYPERFORMANCEMETRICS pseudo table will function normally.

Example

SELECT statement, count,
    minexectime, maxexectime,
    totalexectime, avgexectime
FROM
    system.localqueryperformancemetrics;
                STATEMENT                 COUNT  MINEXECTIME  MAXEXECTIME  TOTALEXECTIME  AVGEXECTIME
 ---------------------------------------- ------ ------------ ------------ -------------- ------------

 CREATE TABLE T1 (F1 INTEGER, F2 STRING)     2       2008        23357         25365         12682
 INSERT INTO T1 VALUES (?, ?)               25         33          249          1586            63
 SELECT COUNT(*) FROM T1 WHERE (F1) > (?)    5         82          282           770           154
 UPDATE T1 SET F1 = ? WHERE (F2) = (?)       4         28          326           598           149
 SELECT * FROM T1 WHERE (F2) = (?)           3         51           80           201            67
 SELECT F1 FROM T1 WHERE (F2) = (?)          2         77           79           156            78
 DROP TABLE T1                               1       7774         7774          7774          7774
 CREATE INDEX IDX1 ON T1 (F1)                1       4070         4070          4070          4070