基础设施 CPU 数据收集分析

$ echo "CREATE DATABASE monitor" | curl 'http://ChronusDB用户名:ChronusDB密码@高可用IP:8123/' --data-binary @-

$ echo "CREATE TABLE monitor.cpu_tags_metrics
 (
   time DateTime DEFAULT now(),
   hostname LowCardinality(String),
   region LowCardinality(String),
   datacenter LowCardinality(String),
   rack LowCardinality(String),
   os LowCardinality(String),
   arch LowCardinality(String),
   team LowCardinality(String),
   service LowCardinality(String),
   service_version LowCardinality(String),
   service_environment LowCardinality(String),
   usage_user Nullable(Float64),
   usage_system Nullable(Float64),
   usage_idle Nullable(Float64),
   usage_nice Nullable(Float64),
   usage_iowait Nullable(Float64),
   usage_irq Nullable(Float64),
   usage_softirq Nullable(Float64),
   usage_steal Nullable(Float64),
   usage_guest Nullable(Float64),
   usage_guest_nice Nullable(Float64)
 )
 ENGINE = MergeTree()
 PARTITION BY toYYYYMM(time)
  ORDER BY (hostname, region, datacenter, rack, os, arch, team, service, service_version, service_environment)" | curl 'http://ChronusDB用户名:ChronusDB密码@高可用IP:8123/' --data-binary @-

  $ echo "CREATE TABLE monitor.
    cpu_tags_metrics_logical_distributed
  (
    time DateTime DEFAULT now(),
    hostname LowCardinality(String),
    region LowCardinality(String),
    datacenter LowCardinality(String),
    rack LowCardinality(String),
    os LowCardinality(String),
    arch LowCardinality(String),
    team LowCardinality(String),
    service LowCardinality(String),
    service_version LowCardinality(String),
    service_environment LowCardinality(String),
    usage_user Nullable(Float64),
    usage_system Nullable(Float64),
    usage_idle Nullable(Float64),
    usage_nice Nullable(Float64),
    usage_iowait Nullable(Float64),
    usage_irq Nullable(Float64),
    usage_softirq Nullable(Float64),
    usage_steal Nullable(Float64),
    usage_guest Nullable(Float64),
    usage_guest_nice Nullable(Float64)
  )
  ENGINE = Distributed(logical_consistency_cluster, monitor, cpu_tags_metrics, rand())"| curl 'http://ChronusDB用户名:ChronusDB密码@高可用IP:8123/' --data-binary @-

  $ echo "SELECT
    toStartOfHour(time) AS hour,
    max(usage_user) AS max_usage_user,
    max(usage_system) AS max_usage_system,
    max(usage_idle) AS max_usage_idle,
    max(usage_nice) AS max_usage_nice,
    max(usage_iowait) AS max_usage_iowait,
    max(usage_irq) AS max_usage_irq,
    max(usage_softirq) AS max_usage_softirq,
    max(usage_steal) AS max_usage_steal,
    max(usage_guest) AS max_usage_guest,
    max(usage_guest_nice) AS max_usage_guest_nice
  FROM monitor.   cpu_tags_metrics_logical_distributed
  WHERE (hostname IN ('host_9', 'host_5', 'host_1', 'host_7', 'host_2')) AND (time >= '2019-01-01 00:17:45') AND (time < '2019-01-01 08:17:45')
  GROUP BY hour
  ORDER BY hour ASC" | curl 'http://<ChronusDB用户名>:<ChronusDB密码>@<高可用IP>:8123/'  --data-binary @-

  $ echo "SELECT
    toStartOfHour(time) AS hour,
    avg(usage_user) AS mean_usage_user,
    avg(usage_system) AS mean_usage_system,
    avg(usage_idle) AS mean_usage_idle,
    avg(usage_nice) AS mean_usage_nice,
    avg(usage_iowait) AS mean_usage_iowait
  FROM monitor.cpu_tags_metrics_logical_distributed
  WHERE (time >= '1970-01-01 00:54:10') AND (time < '1970-01-01 12:54:10')
  GROUP BY
     hour,
     hostname
  ORDER BY
     hour ASC,
     hostname ASC" | curl 'http://<ChronusDB用户名>:<ChronusDB密码>@<高可用IP>:8123/' --data-binary @-

  $ echo "SELECT
    toStartOfMinute(time) AS minute,
     max(usage_user)
  FROM monitor.cpu_tags_metrics_logical_distributed
  WHERE time < '2019-01-01 01:16:22'
  GROUP BY minute
  ORDER BY minute DESC
  LIMIT 5" | curl 'http://<ChronusDB用户名>:<ChronusDB密码>@<高可用IP>:8123/' --data-binary @-

  $ echo "SELECT *
  FROM monitor.cpu_tags_metrics_logical_distributed
  PREWHERE (usage_user > 90.) AND (time >= '2019-01-01 00:16:22') AND (time < '2019-01-01 12:16:22')" | curl 'http://<ChronusDB用户名>:<ChronusDB密码>@<高可用IP>:8123/' --data-binary @-

  $ echo "SELECT *
  FROM monitor.cpu_tags_metrics_logical_distributed
  PREWHERE (usage_user > 90.) AND (time >= '2019-01-01 00:08:59') AND (time < '2019-01-01 12:08:59') AND (hostname IN ('host_9', 'host_5', 'host_1', 'host_7', 'host_2'))" | curl 'http://<ChronusDB用户名>:<ChronusDB密码>@<高可用IP>:8123/' --data-binary @-

  $ echo "SELECT DISTINCT
     hostname,
     usage_user,
     usage_system,
     usage_idle,
     usage_nice,
     usage_iowait,
     usage_irq,
     usage_softirq,
     usage_steal,
     usage_guest,
     usage_guest_nice
  FROM monitor. cpu_tags_metrics_logical_distributed
  ORDER BY
     hostname ASC,
     time DESC" | curl 'http://<ChronusDB用户名>:<ChronusDB密码>@<高可用IP>:8123/' --data-binary @-

  $ echo "SELECT
     toStartOfMinute(time) AS minute,
     max(usage_user) AS max_usage_user
  FROM monitor.cpu_tags_metrics_logical_distributed
  WHERE (hostname = 'host_9') AND (time >= '2019-01-01 01:09:26') AND (time < '2019-01-01 01:09:27')
  GROUP BY minute
  ORDER BY minute ASC" | curl 'http://<ChronusDB用户名>:<ChronusDB密码>@<高可用IP>:8123/' --data-binary @-