Uploaded image for project: 'Percona Monitoring and Management'
  1. Percona Monitoring and Management
  2. PMM-6233

Speed up get filter for QAN.

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Medium
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: PMM Server, QAN API
    • Labels:
      None
    • Needs Review:
      Yes
    • Needs QA:
      Yes
    • Needs Packaging:
      Yes

      Description

      We can speed up get filter for QAN in next way.

      1. add cache in Nginx
      location /qan/v0/Get/ {
          proxy_cache       cache;
          proxy_cache_key   $ui_header_cache_key;
          proxy_set_header  Range $slice_range;
          proxy_cache_valid 200 206 15m;
          proxy_pass        ...;
      }
      

        2. Add cache control header to react request to /qan/v0/Get/ with key:

      service_name__db_name__...._start_time_truncated_to_15_min__end_time_truncated_to_15_min
       
      

      3. Use one/two queries to ClickHouse to request all dimension instead of separate query per dimension.

      SELECT
          'service_name' AS key,
          service_name AS value,
          SUM(service_name) * {{range $key, $vals := .Dimensions }} AND ({{ $key }} IN ('{{ StringsJoin $vals "', '" }}')){{ end }} AS main_metric_sum
          FROM metrics
          WHERE (period_start >= ?) AND (period_start <= ?)
          GROUP BY service_name
          ORDER BY main_metric_sum DESC
      UNION 
      ....
      UNION
      SELECT
          'container_id' AS key,
          container_id AS value,
          SUM(service_name) * {{range $key, $vals := .Dimensions }} AND ({{ $key }} IN ('{{ StringsJoin $vals "', '" }}')){{ end }} AS main_metric_sum
          FROM metrics
          WHERE (period_start >= ?) AND (period_start <= ?)
          GROUP BY container_id
          ORDER BY main_metric_sum DESC
      

       

       

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                Unassigned
                Reporter:
                andrii.skomorokhov Andrii Skomorokhov
                Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                  Dates

                  Created:
                  Updated:

                    Time Tracking

                    Estimated:
                    Original Estimate - Not Specified
                    Not Specified
                    Remaining:
                    Remaining Estimate - Not Specified
                    Not Specified
                    Logged:
                    Time Spent - 15 minutes
                    15m