Grafana仪表板读取超大规模Timescale数据库加载过慢求助

环境与配置 #

• 源数据库：Aiven托管的Timescale（基于PostgreSQL），8核CPU、16GB内存、512GB磁盘，最高10Gbps带宽，运行PostgreSQL 14.10、Timescale 2.11.2
• Grafana服务器：AWS EC2 m5a.xlarge实例（Linux 22.04），4vCPU、16GB内存，最高10Gbps带宽，运行Grafana 9.5.1（免费版）
• 访问方式：Windows 10 Pro（22H2）远程桌面连接EC2，下载速度超27Mbps，上传速度超5Mbps

仪表板与查询情况 #

仪表板包含14个面板（8个时间序列图、3个柱状图、1个饼图、2个统计面板），共执行69条SQL查询（含11条变量定义查询）。部分查询针对原始数据表，部分针对小时级聚合视图，同时包含17项数据转换。测试多采用1天时间范围（原始表返回144个数据点，聚合视图返回24个数据点）。

查询示例：

SELECT mtimestamp, mvalue AS "internal temperature" FROM periodic_measurements WHERE ($__timeFilter(mtimestamp) AND apartmentid = $apartment AND roomid = $room_id AND metric = 1)

问题现象 #

连接1600万条记录的测试库时，仪表板数秒即可加载；但连接超10亿条记录的生产库时，加载耗时约40分钟。

数据表与索引配置 #

原始数据表 #

查询涉及的列均为复合主键组成部分，已自动建立索引：

CREATE TABLE periodic_measurements
    (mtimestamp TIMESTAMP WITH TIME ZONE NOT NULL,
     apartmentid INTEGER NOT NULL,
     roomid INTEGER NOT NULL,
     sensorid INTEGER NOT NULL,
     metric SMALLINT NOT NULL,
     mvalue NUMERIC NULL,
     PRIMARY KEY (mtimestamp,
                  apartmentid, roomid, sensorid, metric));

表详情：

production_measurements=> \d+ periodic_measurements;
                                             Table "public.periodic_measurements"
   Column    |           Type           | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
-------------+--------------------------+-----------+----------+---------+---------+-------------+--------------+-------------
 mtimestamp  | timestamp with time zone |           | not null |         | plain   |             |              | 
 originaldb  | smallint                 |           |          |         | plain   |             |              | 
 originalid  | bigint                   |           |          |         | plain   |             |              | 
 apartmentid | integer                  |           | not null |         | plain   |             |              | 
 roomid      | integer                  |           | not null |         | plain   |             |              | 
 sensorid    | integer                  |           | not null |         | plain   |             |              | 
 metric      | smallint                 |           | not null |         | plain   |             |              | 
 mvalue      | numeric                  |           |          |         | main    |             |              | 
Indexes:
    "periodic_measurements_pkey" PRIMARY KEY, btree (mtimestamp, apartmentid, roomid, sensorid, metric)
    "periodic_measurements_mtimestamp_idx" btree (mtimestamp DESC)
Triggers:
    ts_cagg_invalidation_trigger AFTER INSERT OR DELETE OR UPDATE ON periodic_measurements FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.continuous_agg_invalidation_trigger('1')
    ts_insert_blocker BEFORE INSERT ON periodic_measurements FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Child tables: _timescaledb_internal._hyper_1_1422_chunk,
              _timescaledb_internal._hyper_1_1423_chunk,
              _timescaledb_internal._hyper_1_1424_chunk,
              _timescaledb_internal._hyper_1_1425_chunk,
              _timescaledb_internal._hyper_1_1942_chunk,
              _timescaledb_internal._hyper_1_1943_chunk,
              _timescaledb_internal._hyper_1_1944_chunk,
              _timescaledb_internal._hyper_1_1945_chunk,
              _timescaledb_internal._hyper_1_1946_chunk,
              _timescaledb_internal._hyper_1_1947_chunk,
              _timescaledb_internal._hyper_1_1948_chunk,
              _timescaledb_internal._hyper_1_1949_chunk,
              _timescaledb_internal._hyper_1_1950_chunk,
              _timescaledb_internal._hyper_1_1951_chunk,
              _timescaledb_internal._hyper_1_1952_chunk,
              _timescaledb_internal._hyper_1_1953_chunk,
              _timescaledb_internal._hyper_1_1954_chunk
Access method: heap

连续聚合视图 #

创建连续聚合时，自动为每个GROUP BY列结合time_bucket列创建复合索引：

CREATE MATERIALIZED VIEW max_hour_aggregates
    (mtimestamp, apartmentid, roomid, sensorid, metric, mvalue)
    WITH (timescaledb.continuous) AS
    SELECT time_bucket('1 hour', mtimestamp, 'Europe/Helsinki'),
        apartmentid,
        roomid,
        sensorid,
        metric,
        MAX(mvalue)
    FROM public.periodic_measurements
    GROUP BY time_bucket('1 hour', mtimestamp, 'Europe/Helsinki'),
        apartmentid,
        roomid,
        sensorid,
        metric
    WITH NO DATA;

聚合视图详情：

testdb=> \d+ _timescaledb_internal._materialized_hypertable_205
                                  Table "_timescaledb_internal._materialized_hypertable_205"
   Column    |           Type           | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
-------------+--------------------------+-----------+----------+---------+---------+-------------+--------------+-------------
 mtimestamp  | timestamp with time zone |           | not null |         | plain   |             |              | 
 apartmentid | integer                  |           |          |         | plain   |             |              | 
 roomid      | integer                  |           |          |         | plain   |             |              | 
 sensorid    | integer                  |           |          |         | plain   |             |              | 
 metric      | smallint                 |           |          |         | plain   |             |              | 
 mvalue      | numeric                  |           |          |         | main    |             |              | 
Indexes:
    "_materialized_hypertable_205_apartmentid_mtimestamp_idx" btree (apartmentid, mtimestamp DESC)
    "_materialized_hypertable_205_metric_mtimestamp_idx" btree (metric, mtimestamp DESC)
    "_materialized_hypertable_205_mtimestamp_idx" btree (mtimestamp DESC)
    "_materialized_hypertable_205_roomid_mtimestamp_idx" btree (roomid, mtimestamp DESC)
    "_materialized_hypertable_205_sensorid_mtimestamp_idx" btree (sensorid, mtimestamp DESC)
Triggers:
    ts_insert_blocker BEFORE INSERT ON _timescaledb_internal._materialized_hypertable_205 FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Access method: heap

生产库测试监控数据 #

• Timescale服务器：CPU使用率50%，读取IOPS 1600，内存使用率11%
• EC2实例：CPU使用率38%，5分钟内网入流量5MB，网出流量210MB

已尝试的优化措施 #

• 升级软硬件配置至当前最高规格，加载时间无明显改善
• 合并面板减少独立SQL查询数量，性能无提升
• 在SQL CLI单独执行仪表板查询，响应仅需数秒，无法解释整体加载缓慢问题

求助排查思路 #

为何Grafana仪表板读取超10亿条记录的Timescale数据库时，加载耗时超半小时？

内容的提问来源于stack exchange，提问作者Berni Hacker