背景
项目中使用TimescaleDB来存储时序数据,最近发现查询很慢,且对服务器资源消耗较大。通过查看官方文档,慢日志等方式,找到了一些可以优化的地方,包括索引优化、SQL优化、调整分区大小、删除过时的块、压缩这几个方面。
索引优化
优化依据
索引的一个好的经验法则是分层思考。首先选择通常希望在其上运行相等操作符的列,例如location = garage。然后选择要在其上使用范围操作符的列,例如time> 0930。(摘抄自官方文档索引章节)
存在问题
项目中大量查询使用db_id和time列进行过滤,但只有time列上有索引。这样的话会导致先按time列进行索引,然后扫描数据行来根据db_id过滤,从下面这个慢SQL的执行计划也可以看出:
postgres=# explain select * from db_monitor where db_id=1574 AND time > now() - INTERVAL '20 mins' order by time desc limit 1 ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.15..0.61 rows=1 width=760)
-> Custom Scan (ChunkAppend) on db_monitor (cost=0.15..4.75 rows=10 width=760)
Order: db_monitor.time DESC
Chunks excluded during startup: 123
-> Index Scan using _hyper_6_42420_chunk_db_monitor_time_idx on _hyper_6_42420_chunk (cost=0.15..4.75 rows=10 width=760)
Index Cond: (time > (now() - '00:20:00'::interval))
Filter: (db_id = 1574::bigint)
(7 rows)
优化建议
创建db_id和time的联合索引优化此类查询。
SQL优化
优化依据
分区通过快速排除不相关的数据使查询更快。(摘抄自官方文档)
存在问题
项目中部分查询SQL没有根据时间过滤,这样会导致扫描很多的分区,如下:
postgres=# explain select * from db_monitor where db_id=1572 order by time desc limit 1 ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.14..0.78 rows=1 width=1967)
-> Custom Scan (ChunkAppend) on db_monitor (cost=0.14..31.38 rows=49 width=1967)
Order: db_monitor.time DESC
-> Index Scan using _hyper_6_42420_chunk_db_monitor_time_idx on _hyper_6_42420_chunk (cost=0.14..31.38 rows=49 width=1409)
Filter: (db_id = 1572::bigint)
-> Index Scan using _hyper_6_42404_chunk_db_monitor_time_idx on _hyper_6_42404_chunk (cost=0.15..54.96 rows=91 width=1399)
Filter: (db_id = 1572::bigint)
......
-> Index Scan using _hyper_6_37989_chunk_db_monitor_time_idx on _hyper_6_37989_chunk (cost=0.28..98.45 rows=88 width=2207)
Filter: (db_id = 1572::bigint)
-> Index Scan using _hyper_6_37949_chunk_db_monitor_time_idx on _hyper_6_37949_chunk (cost=0.28..98.38 rows=91 width=2243)
Filter: (db_id = 1572::bigint)
(263 rows)
优化建议
查询SQL中增加时间的过滤条件,让其扫描尽可能少的分区。
调整分区大小
优化依据
建议25%的内存大小能够存储每个活跃超表中的一个块及块上的索引。但也不能有太多的块,这可能会影响查询计划时间和压缩。(摘抄自官方文档中时间分区的最佳实践)
优化建议
服务器内存16GB,16G的25%就是4G。查了一下现有的数据,一天的数据量大概是280MB,7天的数据量就是1.914GB,没有超过4G,所以分区大小改成7天。
删除过时的块
优化依据
TimescaleDB数据保留工作在块上,而不是行上。逐行删除数据,例如使用PostgreSQL DELETE命令,可能会很慢。但是按块删除数据更快,因为它会从磁盘上删除整个文件,它不需要垃圾收集和碎片整理。(摘抄自官方文档数据保留章节)
优化建议
在TimescaleDB中设置数据保留策略,也可以在定时任务中调用drop_chunks函数。
压缩
优化依据
随着数据的老化,查询可能会发生变化。它们变得更具分析性,涉及的列更少。在旧数据上运行的查询可能是“计算上个月的平均磁盘使用量”,此类查询在压缩的柱状数据上运行得更快。(摘抄自官方文档压缩章节)
优化建议
需求可以往这方面考虑,旧数据的查询由明细查询改为统计查询。