最近在做SNMP流量采集时遇到一个问题,当查询设备的一个接口5min内的流量时数据库延时过长,其本质是数据库的查询效率问题,为了优化查询采取如下处理
- 尝试对查询column建立索引
- 将IP(表中的一个column)改成int类型存储
但是效率并没有什么显著的改善 - 最后只好再重建建立一个缓存表,定期将采集所有时间点流量信息的表以一定间隔存到缓存表,当查询跨度较大时调用缓存表,然后定期让进程删除一定数据
还有另一种解决方案是通过建立timedb表来优化查询时间
time_bucket_gapfill() 方法是Timescale对外提供的查询方法,第一个参数是指定间隔,第二个参数是数据库中要压缩的列,最后是时间跨度
以下是官网的例子: https://docs.timescale.com/latest/using-timescaledb/reading-data#gap-filling
查询出的结果
def timedb():
# str(time):时间段,当前时间 - 时间段 = 跨度
# minutes:数据库中两条数据的时间间隔
result_sql = """select time_bucket_gapfill('5.0 minutes',time) as time_range, avg(value), min(value) from xxx where id='15424' and time > now() - interval '30 day' AND time < now() group by time_range order by time_range desc """
cursor.execute(result_sql)
db_result = serialize(cursor.fetchall())
for per_result in db_result:
try:
agg_db = {"time": per_result[0], "avg": per_result[1], "min": per_result[2]}
data.append(agg_db)
except JSONDecodeError:
pass
return JsonRes(data, success=True, info="Successfully.")
如果数据库中存放的例子是如下形式
time | value |
---|---|
00:00 | 100 |
00:01 | 100 |
00:02 | 100 |
00:03 | 100 |
00:04 | 100 |
00:05 | 100 |
00:06 | 100 |
查询后的结果是
time | value |
---|---|
00:00 | 100 |
00:05 | 100 |