时序数据的存储

如果在工作中遇到需要存储若干时间点的数据，并以折线图展示，还支持时间范围筛选以及以小时或天为单位将数据汇总，这就是一个标准的时许数据的存储和查询的需求了。

例如：每分钟采集一次设备的可用状态，并将数据存储，在页面上展示指定时间段内设备的可用状态折线图。

按照每分钟一条数据来计算，30天则会有43200条数据，一年有525600条数据。

对大量的数据进行分组汇总统计，在关系型数据库中执行效率不高，为了提升效率，通常采用事先汇总的方式，将数据定时汇总到另一张表中。

事先汇总的方式也有一些不灵活的地方，就是需要先确定汇总算法，平均值还是最大最小值。

这里我对比了一些其它的存储方案。

为了对比测试，先准备一些数据，ChatGPT帮我编写了下面的 python 代码来生成测试数据：

# 生成时间序列，从365天前到现在，每分钟一条数据
start_time = datetime.datetime.now() - datetime.timedelta(days=365)
end_time = datetime.datetime.now()
time_index = pd.date_range(start=start_time, end=end_time, freq='T')

# 生成数据
data = []
for metric_id in range(1, 4):  # 生成3个metric的数据
    for timestamp in time_index:
        value = np.random.randint(0, 100)  # 随机生成0到100之间的整数值
        data.append([metric_id, timestamp, value])

# 转换为DataFrame
df = pd.DataFrame(data, columns=['metric_id', 'timestamp', 'value'])

# 保存为CSV文件
df.to_csv('metric_test_data.csv', index=False)

print("CSV文件已生成。")

生成的数据有157万条，CSV文件大小为48MB。

MySql#

在MySql中创建表：

CREATE TABLE IF NOT EXISTS metric_test (
    metric_id INT NOT NULL,
    timestamp TIMESTAMP NOT NULL,
    value FLOAT NOT NULL,
    PRIMARY KEY (metric_id, timestamp)
)

然后将测试数据导入：

LOAD DATA INFILE '/var/lib/mysql-files/metric_test_data.csv'
INTO TABLE metric_test
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(metric_id, timestamp, value)

导入后 metric_test.ibd 文件大小为 60MB。

通过下面语句查询一个月内的数据，并按天汇总取平均值：

SELECT DATE(timestamp) AS date, AVG(value) AS average_value
FROM metric_test
WHERE metric_id = 1 AND timestamp <= '2024-6-16' AND timestamp >= '2024-5-16'
GROUP BY DATE(timestamp)
ORDER BY DATE(timestamp)

在我的笔记本上，这个查询耗时 87ms。

如果查询时间为一年（2023-6-16到2024-6-16）则耗时 827ms。

DuckDB#

https://duckdb.org/docs/sql/functions/date#time_bucketbucket_width-date-offset

DuckDB是近期新出现的一款开源分析型数据库，它是一款嵌入式数据库，可以理解成列式存储版本的Sqlite。

它可以作为一个python的一个包直接安装，之后使用下面的代码创建表：

con = duckdb.connect(database="metric_test.duckdb", read_only=False)
create_sql = """CREATE TABLE IF NOT EXISTS metric_test (
      metric_id INT NOT NULL,
      timestamp TIMESTAMP NOT NULL,
      value FLOAT NOT NULL,
      PRIMARY KEY (metric_id, timestamp)
)"""
con.execute(create_sql)

之后导入数据：

con = duckdb.connect(database="metric_test.duckdb", read_only=False)
con.execute("COPY metric_test FROM 'metric_test_data.csv'")

导入后，metric_test.duckdb 文件大小为 82MB。

DuckDB当前只支持在一个进程中以只读方式打开或者以可读写的方式打开，而不能有多个进程同时用读写方式打开。所以在读取或操作DuckDB之前需要打开文件，使用后关闭。

所以在下面测试性能的代码中，包括了打开文件的时间：

query_sql = """SELECT date_trunc('d', timestamp) AS date, COUNT(CASE WHEN value > 50 THEN 1 END) AS count_greater_than_50, COUNT(value) AS total_count
    FROM metric_test
    WHERE metric_id = 3 AND timestamp <= '2024-6-16' AND timestamp >= '2023-6-16'
    GROUP BY date_trunc('d', timestamp)
    ORDER BY date_trunc('d', timestamp)"""
begin_time = time.perf_counter()
with duckdb.connect(database="metric_test.duckdb", read_only=True) as con:
    con.execute(query_sql)
    rows = con.fetchall()
    print(f"total: {len(rows)}")
    print(rows[:10])
    end_time = time.perf_counter()
    print("execute:", time.perf_counter() - begin_time, "seconds")

DuckDB在查询一个月的数据并按天汇总时耗时为 10ms，而1年的数据按天来汇总也仅需 13ms！

这效率确实非常令人惊叹，要知道每次查询之前还要先打开文件。

ClickHouse#

创建表（表格的结构是ChatGPT建议的，这里直接使用）：

CREATE TABLE metrics
(
    timestamp DateTime,
    metric_id UInt32,
    value Float32,
    tags Nested (
        key String,
        value String
    )
) ENGINE = MergeTree()
ORDER BY (metric_id, timestamp)
PARTITION BY toYYYYMM(timestamp)
SAMPLE BY metric_id
TTL timestamp + INTERVAL 1 YEAR
SETTINGS index_granularity = 8192;

ClickHouse的导入速度非常快，1～2秒就完成了，可能是先存下来后台再慢慢处理。

导入后metrics表占用磁盘空间12M，这里充分体现了ClickHouse列式存储的压缩效率。

查询一个月的数据并按天汇总时耗时为 12ms，而1年的数据按天来汇总耗时 36ms。

在ChatGPT给出的这个设计中，使用了 tags 字段来存储一些额外的标识，于是我又测试了通过 tags 进行过滤时的查询性能。

SELECT DATE(timestamp) AS date, AVG(value) AS average_value
FROM metrics
ARRAY JOIN tags.key AS tag_key, tags.value AS tag_value
WHERE metric_id = 1 AND timestamp >= '2024-06-16 00:00:00' AND timestamp <= '2024-07-16 00:00:00'
      AND tag_key = 'device_id' AND tag_value = '2'
GROUP BY DATE(timestamp)
ORDER BY DATE(timestamp);

查询一个月的数据按天汇总耗时为 30ms，查询1年的数据耗时为 92ms。

将 tags 字段由 Nested 改为 Map 性能会有一定提高，但在我测试的数据规模下相差不大。

总体来说用 tags 进行过滤会比只使用 metric_id 进行过滤性能上慢一倍以上。

Whisper#

https://graphite.readthedocs.io/en/stable/

Whisper是时序数据库Graphite的存储模块，可以单独使用。

Graphite整个都是Python编写的，Whisper的代码仅有1千多行，全部在一个文件里。

Whisper的功能比较简单，不具备实时汇总统计的功能，只能事先指定好不同的汇总统计的颗粒度和统计方法，在插入数据时会自动进行汇总统计。

查询时也只能查询事先指定好的统计粒度，它的数据插入速度很慢，比MySql还要慢很多。

但它的查询效率相当快，因为都是提前汇总好的数据，查询时只是文件读取，在毫秒级就可以完成查询。

OpenTSDB#

https://github.com/OpenTSDB/opentsdb

Java语言编写，使用HBase作为存储，对于资源受限的私有化部署不是很友好，没有深入调研。

comments powered by Disqus