Identify longest running queries

Goal:

What types of queries are taking the longest on the system and how can they be identified?

Environment:

Fusion 4.x-5.x, Solr 7.x-9.x

Guide:

Finding the longest running queries in Fusion/Solr can be crucial for performance tuning and identifying bottlenecks in your search infrastructure. You can use the logging capabilities and command line tools to help you find this information:

gawk '/select/ {qtime=""; params=""; for (i = 1; i <= NF; i++) {if ($i ~ /QTime=/) qtime=gensub(/.*QTime=([0-9]+).*/, "\\1", "g", $i)/1000; if ($i ~ /params=\{.+\}/) params=gensub(/.*params=(\{.+\}).*/, "\\1", "g", $i)} if (qtime != "" && params != "") {gsub(/&/, "\n ", params); gsub(/\{|\}/, "", params); print qtime " s | Query Parameters: " params}}' solr_1.log | sort -rn -t"|" -k1,1 | head -200

Replace "solr_1.log" with the name of your log file.

This command analyzes the logs, specifically targeting (/select) queries, extracting their execution time (QTime) and parameters, converting the time to seconds, and then sorting and displaying the top 200 longest-running queries.

Here's an example output.

0.029 s | Query Parameters: q=*:*
0.028 s | Query Parameters: q=*:*
0.028 s | Query Parameters: df=_text_
0.026 s | Query Parameters: q=*:*
0.025 s | Query Parameters: q=*:*
0.025 s | Query Parameters: q=*:*
0.024 s | Query Parameters: q=*:*
0.024 s | Query Parameters: q=*:*
0.023 s | Query Parameters: q=*:*
0.023 s | Query Parameters: df=_text_
0.021 s | Query Parameters: q=*:*
0.021 s | Query Parameters: q=*:*
0.021 s | Query Parameters: q=*:*
0.021 s | Query Parameters: q=*:*
0.021 s | Query Parameters: df=_text_

Note: Due to the use of the (gensub) function, which is a GNU extension to AWK and may not be available in all AWK implementations. If you're using a version of AWK that doesn't support gensub, such as mawk or the default AWK on some systems, you will encounter an error. To resolve this issue, you can switch to using GNU AWK (gawk), which does support gensub. If gawk is installed on your system, you can simply replace awk with gawk in the command.