登录 / 注册
IT168技术开发频道
IT168首页 > 技术开发 > 技术开发资讯 > 正文

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

2017-11-27 09:01    it168网站原创  作者: 编译 | 钰莹 编辑: 赵钰莹

  【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,可能会带来一些巨大的性能改进。

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

  • IT168企业级IT168企业级
  • IT168文库IT168文库

扫码送文库金币

编辑推荐
系统架构师大会
系统架构师大会
点击或扫描关注
IT168企业级微信关注送礼
IT168企业级微信关注送礼
扫描关注
首页 评论 返回顶部