Examine query duration from JMX logs collected by monex #existdb

slowest-queries.xq

xquery version "3.0";

(: Examine query duration from JMX logs collected by monex :)

declare namespace jmx="http://exist-db.org/jmx";

declare function local:milliseconds-to-time($timestamp as xs:long) as xs:dateTime {
    let $days := xs:int($timestamp div 1000 div 24 div 60 div 60)
    let $remainder := $timestamp - ($days * 24 * 60 * 60 * 1000)
    let $hours := xs:int($remainder div 1000 div 60 div 60)
    let $remainder := $remainder - ($hours * 60 * 60 * 1000)
    let $minutes := xs:int($remainder div 1000 div 60)
    let $remainder := $remainder - ($minutes * 60 * 1000)
    let $seconds := xs:int($remainder div 1000)
    let $millis := format-number($remainder - ($seconds * 1000), "000")
    return
        xs:dateTime("1970-01-01T00:00:00Z") + 
        xs:dayTimeDuration("P" || $days || "DT" || $hours || "H" || $minutes || "M" || $seconds || "." || $millis || "S")
};

(:
 : Include only queries slower than 30s: [xs:integer(jmx:mostRecentExecutionDuration) gt 30000]
 : Exclude /historicaldocuments:         [not(starts-with(jmx:requestURI, '/historical'))] 
 :)
let $recent-queries := collection('/db/apps/monex/data')//jmx:RecentQueryHistory/jmx:row[xs:integer(jmx:mostRecentExecutionDuration) gt 30000]
let $ordered-queries :=
    for $query in $recent-queries
    let $duration := xs:long($query/jmx:mostRecentExecutionDuration)
    let $timestamp := $query/jmx:mostRecentExecutionTime
    (:
    Slowest first, regardless of day:    order by $duration descending
    Reverse chronological:               order by $timestamp descending
    :)
    order by $timestamp descending
    return $query
for $query in subsequence($ordered-queries, 1, 100)
return 
    element result {
        element duration {$query/jmx:mostRecentExecutionDuration/string()},
        element timestamp {local:milliseconds-to-time($query/jmx:mostRecentExecutionTime)},
        element uri {$query/jmx:requestURI/string()}
    }