search

couchbaselabs / cbprometheus_python

Couchbase Metrics exported in Prometheus text format
Apache License 2.0
8 stars 8 forks source link

Slow query dashboard is not rendering any data when timezone is different from expected. #89

Closed sahnianuj closed 3 years ago

sahnianuj commented 3 years ago

n1ql statement to find all of the the completed requests in cb_query.py file, is not working as expected for different timezones and because of that dashboard is not rendering any slow queries.

n1ql_stmt = """SELECT IFMISSING(preparedText, statement) as statement, requestId AS request_id,
    service_time_ms, elapsed_time_ms, queue_time_ms, request_time_ms,
    resultCount AS result_count, resultSize AS result_size, query_selectivity_percent,
    scan_results, fetches
FROM system:completed_requests
LET request_time_ms = STR_TO_MILLIS(REPLACE(requestTime, " +0000 UTC", "Z")),
    service_time_ms = ROUND(STR_TO_DURATION(serviceTime) / 1e6),
    elapsed_time_ms = ROUND(STR_TO_DURATION(elapsedTime) / 1e6),
    queue_time_ms = ROUND(
      (STR_TO_DURATION(elapsedTime) - STR_TO_DURATION(serviceTime)) / 1e6
    , 3),
    query_selectivity_percent = ROUND(IFNULL(
      (
        resultCount /
        IFMISSING(phaseCounts.`indexScan`, 0)
      ) * 100,
    0), 2),
    scan_results = IFMISSING(phaseCounts.`indexScan`, 0),
    fetches = IFMISSING(phaseCounts.`fetch`, 0)
WHERE node = NODE_NAME()
    AND UPPER(IFMISSING(preparedText, statement)) NOT LIKE 'INFER %'
    AND UPPER(IFMISSING(preparedText, statement)) NOT LIKE 'ADVISE %'
    AND UPPER(IFMISSING(preparedText, statement)) NOT LIKE '% INDEX%'
    AND UPPER(IFMISSING(preparedText, statement)) NOT LIKE '% SYSTEM:%'
    AND request_time_ms >= ROUND(NOW_MILLIS(), 0) - 60000"""