7.1.6 Query History and Statistics Monitoring

• To view the HeatWave query history including query start time, end time, and wait time in the scheduling queue, as discussed in Section 2.3.3, “Auto Scheduling”.

  mysql> SELECT QUERY_ID,
            CONNECTION_ID,
            QUERY_START,
            QUERY_END,
            QUEUE_WAIT,
            SUBTIME(
                SUBTIME(QUERY_END, SEC_TO_TIME(RPD_EXEC / 1000)),
                SEC_TO_TIME(GET_RESULT / 1000)
            ) AS EXEC_START
        FROM (
                SELECT QUERY_ID,
                    STR_TO_DATE(
                        JSON_UNQUOTE(
                            JSON_EXTRACT(QEXEC_TEXT->>"$**.queryStartTime", '$[0]')
                        ),
                        '%Y-%m-%d %H:%i:%s.%f'
                    ) AS QUERY_START,
                    JSON_EXTRACT(QEXEC_TEXT->>"$**.timeBetweenMakePushedJoinAndRpdExec", '$[0]') 
                    AS QUEUE_WAIT,
                    STR_TO_DATE(
                        JSON_UNQUOTE(
                            JSON_EXTRACT(QEXEC_TEXT->>"$**.queryEndTime", '$[0]')
                        ),
                        '%Y-%m-%d %H:%i:%s.%f'
                    ) AS QUERY_END,
                    JSON_EXTRACT(QEXEC_TEXT->>"$**.rpdExec.msec", '$[0]') AS RPD_EXEC,
                    JSON_EXTRACT(QEXEC_TEXT->>"$**.getResults.msec", '$[0]') AS GET_RESULT,
                    JSON_EXTRACT(QEXEC_TEXT->>"$**.thread", '$[0]') AS CONNECTION_ID
                FROM performance_schema.rpd_query_stats
            ) tmp;

  The query returns the following data:

  • QUERY_ID

    The ID assigned to the query by HeatWave. IDs are assigned in first in first out (FIFO) order.

  • CONNECTION_ID

    The connection ID of the client that issued the query.

  • QUERY_START

    The time the query was issued.

  • QUERY_END

    The time the query finished executing.

  • QUEUE_WAIT

    The amount of time the query waited in the scheduling queue.

  • EXEC_START

    The time that HeatWave started executing the query.

• To view the number of records in the rpd_query_stats table. The rpd_query_stats table stores query compilation and execution statistics (the query history) for the last 1000 successfully executed queries.

  mysql> SELECT COUNT(*) FROM performance_schema.rpd_query_stats;
  +----------+
  | count(*) |
  +----------+
  |     1000 |
  +----------+

• To view query IDs for the first and last successfully executed queries:

  mysql> SELECT MIN(QUERY_ID), MAX(QUERY_ID) FROM performance_schema.rpd_query_stats;
  +---------------+---------------+
  | MIN(QUERY_ID) | MAX(QUERY_ID) |
  +---------------+---------------+
  |             2 |          1001 |
  +---------------+---------------+

• To view the query count for a table and the last time the table was queried:

  mysql> USE performance_schema;
  mysql> SELECT rpd_table_id.TABLE_NAME, rpd_tables.NROWS, rpd_tables.QUERY_COUNT,
            rpd_tables.LAST_QUERIED FROM rpd_table_id, rpd_tables
            WHERE rpd_table_id.ID = rpd_tables.ID;
  +------------+---------+-------------+----------------------------+
  | TABLE_NAME | NROWS   | QUERY_COUNT | LAST_QUERIED               |
  +------------+---------+-------------+----------------------------+
  | orders     | 1500000 |           1 | 2021-12-06 14:32:59.868141 |
  +------------+---------+-------------+----------------------------+