阅读更多

5顶
5踩

行业应用

在PostgreSQL中修改了一行不明显的代码,把(ANY(ARRAY[...]) 改成 ANY(VALUES(...))),结果查询时间从20s变为0.2s。最初我们学习使用EXPLAN ANALYZE来优化代码,到后来,Postgres社区也成为我们学习提升的一个好帮手,付出总会有回报,我们产品的性能也因此得到了极大的提升。

事出有因

我们所开发的产品是Datadog,这是专门为那些编写和运营大规模应用的团队、IT运营商提供监控服务的一个平台,帮助他们把海量的数据转化为切实可行的计划、操作方案。在这周早些时候,我们的许多数据库所面临的一个性能问题是在一个较小的表上进行大量的key查询。这些查询中的99.9%都是高效灵活的。在极少数实例中,有些数量的性能指标tag查询是费时的,这些查询需要花费20s时间。这也就意味着用户需要在浏览器面前花费这么长的时间来等待图形编辑器做出响应。即使是0.1%,这样的用户体验也显然糟透了,对此,我们进行了监测,探究为何速度会这么慢。

查询与计划

结果令人震惊,罪魁祸首竟然是下面这个简单的查询:

SELECT c.key,
       c.x_key,
       c.tags,
       x.name
 FROM context c
 JOIN x
   ON c.x_key = x.key
WHERE c.key = ANY (ARRAY[15368196, -- 11,000 other keys --)])
  AND c.x_key = 1
  AND c.tags @> ARRAY[E'blah'];

X表拥有上千行数据,C表拥有1500万行数据,这两个表的“key”列都带有适当的索引主键。简单地说,它就是一个简单的主键查询。但有趣地是,随着key列中记录的增加,例如在11000行时,我们通过添加EXPLAIN (ANALYZE, BUFFERS)前缀来查看key列的值是否与数组中的值匹配:

Nested Loop  (cost=6923.33..11770.59 rows=1 width=362) (actual time=17128.188..22109.283 rows=10858 loops=1)
  Buffers: shared hit=83494
  ->  Bitmap Heap Scan on context c  (cost=6923.33..11762.31 rows=1 width=329) (actual time=17128.121..22031.783 rows=10858 loops=1)
        Recheck Cond: ((tags @> '{blah}'::text[]) AND (x_key = 1))
        Filter: (key = ANY ('{15368196,(a lot more keys here)}'::integer[]))
        Buffers: shared hit=50919
        ->  BitmapAnd  (cost=6923.33..6923.33 rows=269 width=0) (actual time=132.910..132.910 rows=0 loops=1)
              Buffers: shared hit=1342
              ->  Bitmap Index Scan on context_tags_idx  (cost=0.00..1149.61 rows=15891 width=0) (actual time=64.614..64.614 rows=264777 loops=1)
                    Index Cond: (tags @> '{blah}'::text[])
                    Buffers: shared hit=401
              ->  Bitmap Index Scan on context_x_id_source_type_id_idx  (cost=0.00..5773.47 rows=268667 width=0) (actual time=54.648..54.648 rows=267659 loops=1)
                    Index Cond: (x_id = 1)
                    Buffers: shared hit=941
  ->  Index Scan using x_pkey on x  (cost=0.00..8.27 rows=1 width=37) (actual time=0.003..0.004 rows=1 loops=10858)
        Index Cond: (x.key = 1)
        Buffers: shared hit=32575
Total runtime: 22117.417 ms

这次查询共花费22s,我们可以通过下图对这22s进行很直观的了解,其中大部分时间花费在Postgres和OS之间,而磁盘I/O则花费非常少的时间。

在最低水平,这些查询看起来就像是这些CPU利用率的峰值。在这里主要是想证实一个关键点:数据库不会等待磁盘去读取数据,而是做排序、散列和行比较这些事。

通过Postgres获取与峰值最接近的行数。

显然,我们的查询在大多数情况下都有条不紊的执行着。

Postgres的性能问题:位图堆扫描 

rows_fetched度量与下面的部分计划是一致的: 

Buffers: shared hit=83494
  ->  Bitmap Heap Scan on context c  (cost=6923.33..11762.31 rows=1 width=329) (actual time=17128.121..22031.783 rows=10858 loops=1)
        Recheck Cond: ((tags @> '{blah}'::text[]) AND (x_key = 1))
        Filter: (key = ANY ('{15368196,(a lot more keys here)}'::integer[]))
        Buffers: shared hit=50919

Postgres使用位图堆扫描( Bitmap Heap Scan)来读取C表数据。当关键字的数量较少时,它可以在内存中非常高效地使用索引构建位图。如果位图太大,查询优化器会改变其查找数据的方式。在我们这个案例中,需要检查大量的关键字,所以它使用了非常相似的方法来检查候选行并且单独检查与x_key和tag相匹配的每一行。而所有的这些“在内存中加载”和“检查每一行”都需要花费大量的时间。

幸运的是,我们的表有30%都是装载在RAM中,所以在从磁盘上检查行的时候,它不会表现的太糟糕。但在性能上,它仍然存在非常明显的影响。查询过于简单,这是一个非常简单的key查找,所以没有显而易见的数据库或应用重构,它很难找到一些简单的方式来解决这个问题。最后,我们使用PGSQL-Performance邮件向社区寻求帮助。 

解决方案

开源帮了我们,经验丰富的且代码贡献量非常多的Tom Lane让我们试试这个:

SELECT c.key,
       c.x_key,
       c.tags,
       x.name
 FROM context c
 JOIN x
   ON c.x_key = x.key
WHERE c.key = ANY (VALUES (15368196), -- 11,000 other keys --)
  AND c.x_key = 1
  AND c.tags @> ARRAY[E'blah'];

你能发现有啥不同之处吗?把ARRAY换成了VALUES。

我们使用ARRAY[...]列举出所有的关键字来进行查询,但却欺骗了查询优化器。Values(...)让优化器充分使用关键字索引。仅仅是一行代码的改变,并且没有产生任何语义的改变。

下面是新查询语句的写法,差别就在于第三和第十四行。

Nested Loop  (cost=168.22..2116.29 rows=148 width=362) (actual time=22.134..256.531 rows=10858 loops=1)
  Buffers: shared hit=44967
  ->  Index Scan using x_pkey on x  (cost=0.00..8.27 rows=1 width=37) (actual time=0.071..0.073 rows=1 loops=1)
        Index Cond: (id = 1)
        Buffers: shared hit=4
  ->  Nested Loop  (cost=168.22..2106.54 rows=148 width=329) (actual time=22.060..242.406 rows=10858 loops=1)
        Buffers: shared hit=44963
        ->  HashAggregate  (cost=168.22..170.22 rows=200 width=4) (actual time=21.529..32.820 rows=11215 loops=1)
              ->  Values Scan on "*VALUES*"  (cost=0.00..140.19 rows=11215 width=4) (actual time=0.005..9.527 rows=11215 loops=1)
        ->  Index Scan using context_pkey on context c  (cost=0.00..9.67 rows=1 width=329) (actual time=0.015..0.016 rows=1 loops=11215)
              Index Cond: (c.key = "*VALUES*".column1)
              Filter: ((c.tags @> '{blah}'::text[]) AND (c.x_id = 1))
              Buffers: shared hit=44963
Total runtime: 263.639 ms

产品里新的查询

部署后的代码:

数据库看起来更美观

 

Postgres慢查询将一去不复返了。但有谁愿意因为这个0.1%的倒霉蛋再去折磨呢?我们使用Datadog来验证修改是否正确,它能够做出即时验证。如果你想查看Postgres查询速度的各种影响, 不妨试试Datadog吧。

via: Datadog

  • 大小: 56.4 KB
  • 大小: 43.4 KB
5
5
评论 共 15 条 请登录后发表评论
15 楼 msdghs 2013-08-31 14:48
oracle中可否这样运用呢?
14 楼 skzr.org 2013-08-30 15:54
icefishc 写道

“列出自己所能写出的所有可能sql” 这个一般由优化工具来做。。 一般情况下这个不太现实。 简单sql还好,需求复杂是很可能可能针对同一个需求能有几十种不同的sql写法。数据库优化器确实经常sb.. 但sb的时候99%都能从查询计划看出来。


^_^,我遇到复杂成这样的sql,第一就是使用应用程序来实现这个,而不是用sql。
调试这些东西搞多了后,你就找到症结了。个人感觉优化的sql必然是每步都极大的减少数据量。
13 楼 sakajiaofu 2013-08-30 12:05
麻烦问下你的这个图表是那个软件弄出来的?
12 楼 javaroom 2013-08-30 11:24
两个代码一样,没变化,贴错了吧?
11 楼 icefishc 2013-08-30 10:11
skzr.org 写道
曾经用join在sybase上查询3表sum,一个查询需要大约6min,而且是首页,客户爆跳把开发商,sybase厂家的人都搞来了。
web存在内存泄漏,解决了web几天就无响应问题。
首页统计sum从join修改为in的子查询,不到1s出结果。

我觉得调试这些慢的,最好的办法就是:
  • 列出自己所能写出的所有可能sql
  • 根据他们的执行计划和实际执行时间对比,得出最终优化sql

“列出自己所能写出的所有可能sql” 这个一般由优化工具来做。。 一般情况下这个不太现实。 简单sql还好,需求复杂是很可能可能针对同一个需求能有几十种不同的sql写法。数据库优化器确实经常sb.. 但sb的时候99%都能从查询计划看出来。
10 楼 pop1030123 2013-08-30 09:55
skzr.org 写道
曾经用join在sybase上查询3表sum,一个查询需要大约6min,而且是首页,客户爆跳把开发商,sybase厂家的人都搞来了。
web存在内存泄漏,解决了web几天就无响应问题。
首页统计sum从join修改为in的子查询,不到1s出结果。

我觉得调试这些慢的,最好的办法就是:
  • 列出自己所能写出的所有可能sql
  • 根据他们的执行计划和实际执行时间对比,得出最终优化sql

我觉得调试这些慢的,最好的办法就是:
列出自己所能写出的所有可能sql
根据他们的执行计划和实际执行时间对比,得出最终优化sql
9 楼 skzr.org 2013-08-30 09:29
曾经用join在sybase上查询3表sum,一个查询需要大约6min,而且是首页,客户爆跳把开发商,sybase厂家的人都搞来了。
web存在内存泄漏,解决了web几天就无响应问题。
首页统计sum从join修改为in的子查询,不到1s出结果。

我觉得调试这些慢的,最好的办法就是:
  • 列出自己所能写出的所有可能sql
  • 根据他们的执行计划和实际执行时间对比,得出最终优化sql
8 楼 devworks 2013-08-29 20:35
后面的代码贴错了。ARRAY还是ARRAY,没有改成VALUES
7 楼 pop1030123 2013-08-29 19:49
懂不懂啊,去其糟粕,取其精华.
6 楼 liudd5 2013-08-29 12:08
5 楼 colbybobo 2013-08-29 09:05
反正也是经验。。学习了。
4 楼 WingForce 2013-08-29 08:20
广告贴,鉴定完毕
3 楼 icefishc 2013-08-28 21:50
日数据开发人员和DBA的日常任务
2 楼 freezingsky 2013-08-28 19:47
这广告写得还行!
1 楼 alajl 2013-08-28 17:45
这个是在给Datadog打广告吗

发表评论

您还没有登录,请您登录后再发表评论

相关推荐

  • http://www.8now.info/soft/?554002.htm

    http://www.8now.info/soft/?554002.htm

  • HTTP协议演示_基于 HTT P的 Web Forms 编程5-5-实验

    本文主要介绍HTTP在Web应用程序中是如何体现的。

  • http://dotnet.mblogger.cn/w6f3y0/search.aspx

    http://dotnet.mblogger.cn/w6f3y0/search.aspx

  • c++优先级(来自http://www.cppreference.com)

    原文地址 http://en.cppreference.com/w/cpp/language/operator_precedence The following table lists the precedence and associativity of C++ operators. Operators are listed top to bottom, in descending prec

  • 修改一行SQL代码 性能提升了100倍

    http://www.csdn.net/article/2013-08-26/2816701-100x-faster-Postgres-performance-by-changing-1-line 转载于:https://www.cnblogs.com/byfei/p/6389912.html

  • 一行配置作业性能提升53%!Flink SQL 性能之旅

    翻译|毛家琦校对|伍翀最近,我们用 SQL 查询做了一些实验,这个查询关联了一些维表的丰富原始记录。同时,我们也考虑如果使用 DataStream API 实现相同的任务,是否能够从现...

  • 性能提升100倍的秘密武器:一行SQL代码的妙用

    在PostgreSQL中修改了一行不明显的代码,把(ANY(ARRAY[...]) 改成 ANY(VALUES(...))),结果查询时间从20s变为0.2s。最初我们学习使用 EXPLAN ANALYZE来优化代码,到后来,Postgres社区也成为我们学习提升的一个...

  • 【MySQL数据库】sql优化的15个小技巧,学会性能提升100%

    如果某天你负责的某个线上接口,出现了性能问题,需要做优化。那么你首先想到的很有可能是优化,因为它的改造成本相对于代码来说也要小得多。那么,如何优化sql语句呢?这篇文章从15个方面,分享了sql优化的一些小...

  • mysql5.7性能提升一百倍调优宝典

    每一个参数干吗? 在某些典型硬件配置下的db上参数该设多少? 设会怎么样? 不设会怎么样? 有什么坑如何填坑? 有些参数怎么算、算法又如何 这种style来写的,具体请看文档! 一千个DBA就有一千种配置方式! ...

  • 高效SQL代码 提升性能100倍

    在PostgreSQL中修改了一行不明显的代码,把(ANY(ARRAY[...]) 改成 ANY(VALUES(...))),结果查询时间从20s变为0.2s。最初我们学习使用EXPLAN ANALYZE来优化代码,到后来,Postgres社区也成为我们学习提升的一个好...

  • 使用SQL提升低代码项目的性能

    通过优化和执行有效的SQL查询,可以显著提高低代码项目的性能。请注意,上述示例代码仅为演示目的,实际使用时需要根据具体的低代码平台和数据库进行适当的调整和修改。使用批量操作可以大大提高数据库操作的效率。...

  • Java教程之SQL性能优化提升

     Sql的性能优化是数据库工程师在实际工作中必须面对的重要课题之一。对于某些数据库工程师来说,它几乎唯一的命题。实际上,像WEB服务这样需要快速响应的应用场景中,SQL的性能直接决定了系统是否可以使用。这里...

  • 代码质量提升——SQL优化与加固

    SQL语句性能优化2. SQL语句安全优化总结 一、优化可能带来的问题有哪些? 1.优化不总是对一个单纯的环境进行,还很可能是一个复杂的已投产的系统; 2.优化手段本来就有很大的风险,只不过你没能力意识到和预见到; ...

  • SQL性能优化技巧,常见优化方法,一文就让你学会

    Mysql架构 存储引擎类似于一个个组件,它们才是mysql真正获取一行行数据并返回数据的地方,存储引擎是可以替换更改的,既可以用不支持事务的MyISAM,也可以替换成支持事务的Innodb。这个可以在建表的时候指定。比如...

  • Oracle如何分析sql语句性能,Oracle数据库SQL语句的性能优化分析

    科学前沿 159 1 SQL语句优化的必要性及目的 近年来,科技发展速度加快,数据库规模也在不断扩大,其运行速度已成为影响管理信息系统运行效率的重要因素。而SQL语句优化,则是提高其运行效率的重要措施。针对大规模...

  • SQL代码编码原则和规范

    21、批量插入性能提升 22、表连接不宜太多,索引不宜太多,一般5个以内 23、禁止给表中的每一列都建立单独的索引 24、如何选择索引列的顺序 25、对于频繁的查询优先考虑使用覆盖索引 26、建议使用预编译语句进行...

  • SSIS最佳实践:SQL Server提升执行性能

    SQL Server集成服务(SQL Server Integration Services,SSIS)在其前辈DTS(Data Transformation Services,数据转换服务)的基础上进步了不少,从可用性、性能和并行等方面来说,它已经成长为一个企业级ETL(Extraction...

  • SQL性能规范

    SQL的优化是一个不断试错的过程,同时观摩不同人的SQL风格,会有不一样的理解,如果你看的懂别人写的,静下心多看,这样自己写的时候,水到渠成,如果看不懂别人的SQL/代码,静下心慢慢看,只有看懂才会写!...

  • 【SQL】关于SQL Server的性能优化——基础内容

    一、调优时,可以从以下五点考虑: 最小化SQL相应时间 合理地增加吞吐量 减少网络延迟 优化IO 减少最小化 二、调优要达到的目标: 使系统能有协调、平衡地运作,合理地响应外部及内部请求,实现资源利用的最大化。 ...

  • 数据库SQL性能优化总结

    一、SQL语句优化 1、对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引; 2、在 where 子句中对字段须避免以下操作,否则将导致引擎放弃使用索引而进行全表扫描; 进行 null ...

Global site tag (gtag.js) - Google Analytics