与 SQL 对比

下面将以 Prometheus server 收集的 http_requests_total 时序数据为例子展开对比。

MySQL 数据准备

  1. mysql>
  2. # 创建数据库
  3. create database prometheus_practice;
  4. use prometheus_practice;
  6. # 创建 http_requests_total 表
  7. CREATE TABLE http_requests_total (
  8.     code VARCHAR(256),
  9.     handler VARCHAR(256),
  10.     instance VARCHAR(256),
  11.     job VARCHAR(256),
  12.     method VARCHAR(256),
  13.     created_at DOUBLE NOT NULL,
  14.     value DOUBLE NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  16. ALTER TABLE http_requests_total ADD INDEX created_at_index (created_at);
  18. # 初始化数据
  19. # time at 2017/5/22 14:45:27
  20. INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "query_range", "localhost:9090", "prometheus", "get", 1495435527, 3);
  21. INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("400", "query_range", "localhost:9090", "prometheus", "get", 1495435527, 5);
  22. INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "prometheus", "localhost:9090", "prometheus", "get", 1495435527, 6418);
  23. INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "static", "localhost:9090", "prometheus", "get", 1495435527, 9);
  24. INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("304", "static", "localhost:9090", "prometheus", "get", 1495435527, 19);
  25. INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "query", "localhost:9090", "prometheus", "get", 1495435527, 87);
  26. INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("400", "query", "localhost:9090", "prometheus", "get", 1495435527, 26);
  27. INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "graph", "localhost:9090", "prometheus", "get", 1495435527, 7);
  28. INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "label_values", "localhost:9090", "prometheus", "get", 1495435527, 7);
  30. # time at 2017/5/22 14:48:27
  31. INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "query_range", "localhost:9090", "prometheus", "get", 1495435707, 3);
  32. INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("400", "query_range", "localhost:9090", "prometheus", "get", 1495435707, 5);
  33. INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "prometheus", "localhost:9090", "prometheus", "get", 1495435707, 6418);
  34. INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "static", "localhost:9090", "prometheus", "get", 1495435707, 9);
  35. INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("304", "static", "localhost:9090", "prometheus", "get", 1495435707, 19);
  36. INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "query", "localhost:9090", "prometheus", "get", 1495435707, 87);
  37. INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("400", "query", "localhost:9090", "prometheus", "get", 1495435707, 26);
  38. INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "graph", "localhost:9090", "prometheus", "get", 1495435707, 7);
  39. INSERT INTO http_requests_total (code, handler, instance, job, method, created_at, value) values ("200", "label_values", "localhost:9090", "prometheus", "get", 1495435707, 7);

数据初始完成后，通过查询可以看到如下数据：

  1. mysql>
  2. mysql> select * from http_requests_total;
  3. +------+--------------+----------------+------------+--------+------------+-------+
  4. | code | handler      | instance       | job        | method | created_at | value |
  5. +------+--------------+----------------+------------+--------+------------+-------+
  6. | 200  | query_range  | localhost:9090 | prometheus | get    | 1495435527 |     3 |
  7. | 400  | query_range  | localhost:9090 | prometheus | get    | 1495435527 |     5 |
  8. | 200  | prometheus   | localhost:9090 | prometheus | get    | 1495435527 |  6418 |
  9. | 200  | static       | localhost:9090 | prometheus | get    | 1495435527 |     9 |
  10. | 304  | static       | localhost:9090 | prometheus | get    | 1495435527 |    19 |
  11. | 200  | query        | localhost:9090 | prometheus | get    | 1495435527 |    87 |
  12. | 400  | query        | localhost:9090 | prometheus | get    | 1495435527 |    26 |
  13. | 200  | graph        | localhost:9090 | prometheus | get    | 1495435527 |     7 |
  14. | 200  | label_values | localhost:9090 | prometheus | get    | 1495435527 |     7 |
  15. | 200  | query_range  | localhost:9090 | prometheus | get    | 1495435707 |     3 |
  16. | 400  | query_range  | localhost:9090 | prometheus | get    | 1495435707 |     5 |
  17. | 200  | prometheus   | localhost:9090 | prometheus | get    | 1495435707 |  6418 |
  18. | 200  | static       | localhost:9090 | prometheus | get    | 1495435707 |     9 |
  19. | 304  | static       | localhost:9090 | prometheus | get    | 1495435707 |    19 |
  20. | 200  | query        | localhost:9090 | prometheus | get    | 1495435707 |    87 |
  21. | 400  | query        | localhost:9090 | prometheus | get    | 1495435707 |    26 |
  22. | 200  | graph        | localhost:9090 | prometheus | get    | 1495435707 |     7 |
  23. | 200  | label_values | localhost:9090 | prometheus | get    | 1495435707 |     7 |
  24. +------+--------------+----------------+------------+--------+------------+-------+
  25. 18 rows in set (0.00 sec)

基本查询对比

假设当前时间为 2017/5/22 14:48:30

  • 查询当前所有数据
  1. // PromQL
  2. http_requests_total
  4. // MySQL
  5. SELECT * from http_requests_total WHERE created_at BETWEEN 1495435700 AND 1495435710;

我们查询 MySQL 数据的时候，需要将当前时间向前推一定间隔，比如这里的 10s (Prometheus 数据抓取间隔)，这样才能确保查询到数据，而 PromQL 自动帮我们实现了这个逻辑。

  • 条件查询
  1. // PromQL
  2. http_requests_total{code="200", handler="query"}
  4. // MySQL
  5. SELECT * from http_requests_total WHERE code="200" AND handler="query" AND created_at BETWEEN 1495435700 AND 1495435710;
  • 模糊查询: code 为 2xx 的数据
  1. // PromQL
  2. http_requests_total{code~="2xx"}
  4. // MySQL
  5. SELECT * from http_requests_total WHERE code LIKE "%2%" AND created_at BETWEEN 1495435700 AND 1495435710;
  • 比较查询: value 大于 100 的数据
  1. // PromQL
  2. http_requests_total > 100
  4. // MySQL
  5. SELECT * from http_requests_total WHERE value > 100 AND created_at BETWEEN 1495435700 AND 1495435710;
  • 范围区间查询: 过去 5 分钟数据
  1. // PromQL
  2. http_requests_total[5m]
  4. // MySQL
  5. SELECT * from http_requests_total WHERE created_at BETWEEN 1495435410 AND 1495435710;

聚合, 统计高级查询

  • count 查询: 统计当前记录总数
  1. // PromQL
  2. count(http_requests_total)
  4. // MySQL
  5. SELECT COUNT(*) from http_requests_total WHERE created_at BETWEEN 1495435700 AND 1495435710;
  • sum 查询: 统计当前数据总值
  1. // PromQL
  2. sum(http_requests_total)
  4. // MySQL
  5. SELECT SUM(value) from http_requests_total WHERE created_at BETWEEN 1495435700 AND 1495435710;
  • avg 查询: 统计当前数据平均值
  1. // PromQL
  2. avg(http_requests_total)
  4. // MySQL
  5. SELECT AVG(value) from http_requests_total WHERE created_at BETWEEN 1495435700 AND 1495435710;
  • top 查询: 查询最靠前的 3 个值
  1. // PromQL
  2. topk(3, http_requests_total)
  4. // MySQL
  5. SELECT * from http_requests_total WHERE created_at BETWEEN 1495435700 AND 1495435710 ORDER BY value DESC LIMIT 3;
  • irate 查询，过去 5 分钟平均每秒数值
  1. // PromQL
  2. irate(http_requests_total[5m])
  4. // MySQL
  5. SELECT code, handler, instance, job, method, SUM(value)/300 AS value from http_requests_total WHERE created_at BETWEEN 1495435700 AND 1495435710  GROUP BY code, handler, instance, job, method;

总结

通过以上一些示例可以看出，在常用查询和统计方面，PromQL 比 MySQL 简单和丰富很多，而且查询性能也高不少。