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 (that is, 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 of 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?

In Query Diagnostics, a query is a unique query text after predicates are removed (also known as normalization). For example, SELECT id FROM users WHERE name = "Bob" is 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's 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 is disabled.

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

Does Query Diagnostics show every query executed?

Yes, if pg_stat_ch is installed, Query Diagnostics includes every query execution that pg_stat_ch records. By default, pg_stat_ch records every query execution.

If pg_stat_ch isn't installed, Query Diagnostics shows only the queries that EDB Wait States has observed. These include most queries that contribute a significant amount of AAS, but a query with a small AAS contribution can be missing.