Cluster Query Diagnostics Innovation Release

Query Diagnostics

The Query Diagnostics tab provides insight into the workload running on your cluster. It collects detailed statistics on the queries that have been executed, enabling users to quickly identify problem areas.

The Query Diagnostics tab comprises four charts and a table of queries, providing helpful statistics for diagnosing performance issues with query execution.

Charts

The charts are the following:

• “Query latency”, a line chart with query latency percentiles (50, 90 and 99),
• “Number of queries executed”, a line chart with the raw count of executed queries,
• “Query latency histogram”, a bar chart with query latency distributions,
• “Number of active sessions”, a stacked area chart with the number of active sessions and the related wait states for I/O, CPU, IPC, and locks.

The filters allow users to restrict data by:

• A time interval,
• A start and end date,
• Specific nodes.

Query Table

Below the charts, a table lists all the collected queries along with their info and statistics:

• “Query ID”, the ID of the query,
• “Query”, the truncated query text,
• “# of Executions”, the number of executions for this query,
• “50th Percentile”, the 50th percentile for query latency,
• “90th Percentile”, the 90th percentile for query latency,
• “99th Percentile”, the 99th percentile for query latency,
• “CPU %”, the percentage of time spent working on the CPU,
• “Wait %”, the percentage of time spent waiting (i.e. active, but not on CPU),
• “Min Execution Time”,
• “Max Execution Time”,
• “CPU AAS”, average active sessions spent on CPU by the query
• “Wait AAS”, average active sessions spent in wait states by the query,
• “Total AAS”, average active sessions spent running the query,
• “Count”, number users, databases and nodes that executed this query at least once.

With the cog icon, it’s possible to show/hide columns.

The filters allow users to restrict data by:

• Databases,
• Users.

The drop-down menu on the right side allows you to sort rows in the table by column in ascending or descending order.

The search bar on the left side lets you search for a substring in the query text.

Understanding Query Diagnostics

What is a query?

For the purpose of Query Diagnostic, we defined a query as being a unique query text after predicates are removed (also known as normalization). For example, SELECT id FROM users WHERE name = "Bob" ia the same query as SELECT id FROM users WHERE name = "Alice". This is true even if the queries run in different databases against different versions of the users table.

What does Average Active Sessions (AAS) mean?

Average Active Sessions is a metric of how much time was spent in a particular state across all sessions. It is calculated as the proportion of time in each session spent in the state of interest, summed over all sessions.

For example, if a single session runs the same query continuously for an hour, that query would be said to occupy 1 AAS for that hour. If it only spent half of the hour running the query, that would be 0.5 AAS.

If there were instead 10 sessions running the query, each for half of the hour, that would be 5 AAS.

Average Active Sessions is a useful metric to answer the question "what were my top ten queries yesterday?". By definition, the ten queries with the highest AAS will be those that occupied the most time across the whole day, across all sessions, . For AAS, whether the query executed a million times in the day or only once is irrelevant: it measures the total execution time.

Where does the data come from?

Data for Query Diagnostics is sourced from two extensions. If only one is available, functionality is reduced. If neither is available Query Diagnostics will be disabled.

• EDB Wait States collects sampled data from the session state. This is used to populate the AAS and related metrics.
• EDB Stat Monitor collects data at the query execution level. By default it records every query execution. This is used to populate the number of executions, and query latency metrics.

Does Query Diagnostics show every query executed?

Yes, if EDB Stat Monitor is enabled and edbsm_sample_rate is set to its default of 1 then Query Diagnostics will include every query executed with the exception of utility queries such as SET and queries executed inside PGPLSQL procedures.

If EDB Stat Monitor is not enabled, the queries shown will only be those which have been observed by EDB Wait States. This is likely to include any query that contributes a significant amount of AAS, but this cannot be guaranteed.