Simple Log Service:PromQL and SQL query Metricstore common cases

Query single metric data

process_resident_memory_bytes represents resident memory. Use the following PromQL to view the process metric data at different times.

process_resident_memory_bytes

Filter metric data by label

PromQL statements allow filtering conditions for specific labels after the metric to refine the metric data. For example, the following query filters data for a specific cluster.

process_resident_memory_bytes{cluster="sls-mall"}/1024/1024

Use regular expressions for label filtering

Label filtering conditions support regular expression syntax. For example, the following query filters metric data where the cluster is sls-mall or sls-demo.

process_resident_memory_bytes{cluster=~"sls-mall|sls-demo"}

Calculate the maximum value of a metric

Use the max operator to query the maximum value of heap memory usage in each cluster.

max by (cluster) (go_memstats_heap_inuse_bytes) / 1024 / 1024

Calculate the number of time series for a metric

Use the count operator to query the number of time series for a specific metric.

count(apiserver_request_total{cluster="sls-mall"})

Calculate a metric's rate of change over time

The rate function is used to calculate the rate of change of a metric within an interval, commonly used to calculate CPU utilization.

rate(process_cpu_seconds_total[1m])

Calculate the difference of a metric from n minutes ago

For example, query the change in a metric at each moment relative to its value one minute ago:

delta(go_goroutines[1m])

Nested use of multiple operators and functions

Multiple operators and functions can be used in nested queries. The following example first calculates the maximum value for each resource and cluster, then takes the top 3 values.

topk(3, max by (cluster, resource)(apiserver_request_total))

Calculations between multiple values

PromQL supports calculations between pure values, such as +, -, *, /, and %.

1 + 2

Calculations between metrics and values

PromQL supports calculations between metrics and values. For example, the following query converts the byte unit of the memory metric to MB.

process_resident_memory_bytes / 1024 / 1024

Binary calculations between multiple metrics

PromQL supports calculations between multiple metrics, such as dividing the following two metrics:

kube_daemonset_status_number_ready{job="kube-state-metrics"} 
/ 
kube_daemonset_status_desired_number_scheduled{job="kube-state-metrics"}

How to correctly use subquery

Functions such as rate, delta, increase, and {agg}_over_time can only operate on the original metrics and do not support calculations on the results of operators or functions. For example:

• Supported:

  max_over_time(go_goroutines[1m]) # Calculate the maximum value within the previous minute for each time series.

• Not supported:

  max_over_time(max(go_goroutines)[1m]) # First calculate the maximum value between time series, then select the maximum value within the previous minute.

• Correct usage:

  # Prometheus supports these requirements through subqueries. The PromQL statement must be modified to the following format:
  max_over_time(max(go_goroutines)[1m:10s])
  # In the subquery, the two parameters of [a:b] represent range and step respectively.

Query all raw data under a metric

Query all raw time series data of the process_resident_memory_bytes metric within a specified time range. (Demo)

 *| SELECT * FROM "metrics_store_name.prom" WHERE __name__ = 'process_resident_memory_bytes'

Query raw data with a specific instance value for a metric

Query all metric data where the specified metric is process_resident_memory_bytes and the instance is 172.20.0.143:8084. (Demo)

*| SELECT * FROM "metrics_store_name.prom" WHERE __name__ = 'process_resident_memory_bytes' and element_at(__labels__, 'instance')='172.20.0.143:8084' limit all

Create scheduled SQL jobs to aggregate time series data

Align time to every minute for a MAX aggregation calculation, calculating the maximum value of metrics under different instances. (Demo)

*| SELECT __time_nano__ FROM "metrics_store_name.prom" WHERE __name__ = 'process_resident_memory_bytes' and element_at(__labels__, 'instance')='x-abcd'