Administration

Identifying Slow Queries

0

In this post, I’ll show how to identify slow running query requests in Vertica. The query_requests system table in the v_monitor schema holds information about user-issued query requests. The column of interest in this table is request_duration_ms. It provides the length of time the query ran in milliseconds.

I feel that most queries should be under 1 second (Vertica would say 3 seconds). I took this distribution of query requests from my cluster:

Duration (ms) Requests % of Total
< 1000 514629 97.35%
1001 – 2000 6611 1.25%
2001 – 3000 2094 0.40%
3001 – 4000 1363 0.26%
4001 – 5000 648 0.12%
> 5000 3316 0.63%

To return a distribution like the one above, you can use the following query:

Which will return something something like:

There’s a healthy distribution of queries running under 1 second. Only 0.63% of the total requests have a request duration longer than 5 seconds. Sometimes queries that are running longer than 5 seconds have a negative impact on other queries as well as system resources. Drilling into the > 5000 millisecond group, the distribution looks like:

Duration (ms) Requests % of Total
5001 – 10000 1526 46.02%
10001 – 15000 394 11.88%
15001 – 20000 363 10.95%
20001 – 25000 585 17.64%
25001 – 30000 166 5.01%
30001 – 35000 55 1.66%
> 35000 227 6.85%

There’s a significant amount of queries that are running longer than 35 seconds, and that’s where I would focus my efforts. You can also take an average of the request duration for queries that run longer than 1 second:

Then, use this average to get the basics about those requests:

You can read about the available columns in the documentation.

It’s important to remember that this table has a default memory retention of 2000KB and disk retention of 50000KB. In my cluster, that’s about 4 days worth of data. You can query the retention period using the GET_DATA_COLLECTOR_POLICY() function:

I do my analysis in Excel as it’s easier for me to filter and chart the data. It may be helpful to look at the Vertica Management Console and write down the peak IO or CPU times and compare back to your results.

Lastly, it’s worth mentioning that this doesn’t identify the unique slow queries. You would need to build out a query that accounts for distinct requests. Also, you may want to consider using query profiling to create a more informative analysis on queries.

About the author / 

Norbert Krupa

Norbert is the founder of vertica.tips and a Solutions Engineer at Talend. He is an HP Accredited Solutions Expert for Vertica Big Data Solutions. He has written the Vertica Diagnostic Queries which aim to cover monitoring, diagnostics and performance tuning. The views, opinions, and thoughts expressed here do not represent those of the user's employer.

Leave a Reply

Upcoming Events

  • No upcoming events
AEC v1.0.4

Subscribe to Blog via Email

Enter your email address to subscribe and receive notifications of new posts by email.

Read more use cases here.

Notice

This site is not affiliated, endorsed or associated with HPE Vertica. This site makes no claims on ownership of trademark rights. The author contributions on this site are licensed under CC BY-SA 3.0 with attribution required.
%d bloggers like this: