改动PostgreSQL配置参数查询性能提高50倍?

2019-08-19 15:29 评论 0 条

  【IT168 资讯】Amplitude是一家数据分析公司,目标是提供易于使用的交互式产品分析,希望每个人都可以从中找到他们产品问题的答案。为了提供良好的用户体验,Amplitude需要快速提供这些答案。所以,当他们的一个客户抱怨在Amplitude UI中加载事件属性下拉时间特别长的时候,Amplitude开始深入研究这个问题。

  通过跟踪不同级别的延迟,他们认为一个特定的PostgreSQL查询需要20秒才能完成。 这对他们来说是值得思考的,因为两个表都在连接列上有索引。

PostgreSQL配置参数查询性能提高50倍?
▲Slow Query

  这个查询的PostgreSQL执行计划是奇怪的。即使两个表都有索引,PostgreSQL也决定在大表上进行顺序扫描,这是查询时间的大部分来源。

PostgreSQL配置参数查询性能提高50倍?
▲慢查询执行计划

  Amplitude最初怀疑这可能是由于碎片化。但是在检查数据后,意识到这个表只是附加的,这个表上没有多少删除。由于使用真空回收空间在这里不是很有帮助,所以Amplitude开始探索其他解决方案。接下来,Amplitude尝试了对另一个客户进行相同查询,但是响应时间很好。令我惊讶的是,查询执行计划看起来完全不同!

PostgreSQL配置参数查询性能提高50倍?

  有趣的是,应用程序A只能访问比应用程序B多10倍的数据,但响应时间却延长了3000倍。

  要查看PostgreSQL在选择散列连接之前考虑的替代查询计划,Amplitude禁用散列连接并重新进行了该查询。

PostgreSQL配置参数查询性能提高50倍?
▲慢查询的可替代执行计划

  使用嵌套循环代替散列连接时,相同的查询速度提高了50倍。那么为什么PostgreSQL选择一个更糟糕的A计划呢?

  仔细查看两个计划的预估成本和实际运行时间,实际运行时间与估计成本的比率是非常不同的。 造成这种差异的主要原因是顺序扫描成本估算,PostgreSQL估计顺序扫描比4000+索引扫描更好,但实际上索引扫描速度要快50倍。

  这主要与'random_page_cost'和'seq_page_cost'配置选项相关。对于'random_page_cost','seq_page_cost'分别默认的PostgreSQL值为4和1,这些值是针对硬盘设置的,随机存取磁盘比顺序存取要贵。然而,这些成本对于使用固态驱动器的gp2 EBS卷部署是不准确的。对于这种部署方式,随机和顺序访问几乎是一样的。

  Amplitude将“random_page_cost”更改为1并重试了查询。这一次,PostgreSQL使用了一个嵌套循环,查询速度提高了50倍。改变之后,我们也注意到PostgreSQL的最大响应时间显著下降。

PostgreSQL配置参数查询性能提高50倍?
▲总体慢查询性能显著提高

  如果你正在使用SSD并以默认配置运行PostgreSQL,建议尝试调整random_page_cost&seq_page_cost,可能会带来一些巨大的性能改进。

  有没有其他的参数调整给你带来过巨大收益?欢迎留在评论里。

版权声明:本文著作权归原作者所有,欢迎分享本文,谢谢支持!
转载请注明:改动PostgreSQL配置参数查询性能提高50倍? | 万人堂心水论坛-六和合彩生肖图

发表评论


表情