调优--学习笔记

1，Presto调优

数据存储格式

1）合理设置分区
与Hive类似，Presto会根据元信息读取分区数据，合理的分区能减少Presto数据读取量，提升查询性能。
2）使用列式存储
Presto对ORC文件读取做了特定优化，因此在Hive中创建Presto使用的表时，建议采用ORC格式存储。相对于Parquet，Presto对ORC支持更好。
Parquet和ORC一样都支持列式存储，但是Presto对ORC支持更好，而Impala对Parquet支持更好。在数仓设计时，要根据后续可能的查询引擎合理设置数据存储格式。
3）使用压缩
数据压缩可以减少节点间数据传输对IO带宽压力，对于即席查询需要快速解压，建议采用Snappy压缩。
4）预先排序
对于已经排序的数据，在查询的数据过滤阶段，ORC格式支持跳过读取不必要的数据。比如对于经常需要过滤的字段可以预先排序。

INSERT INTO table nation_orc partition(p) SELECT * FROM nation SORT BY n_name;

如果需要过滤n_name字段，则性能将提升。

SELECT count(*) FROM nation_orc WHERE n_name=’AUSTRALIA’; 

SQL优化

1）只选择使用必要的字段
由于采用列式存储，选择需要的字段可加快字段的读取、减少数据量。避免采用*读取所有字段。
[✔]: SELECT time,user,host FROM tbl
[✘]: SELECT * FROM tbl

2）过滤条件必须加上分区字段
对于有分区的表，where语句中优先使用分区字段进行过滤。acct_day是分区字段，visit_time是具体访问时间。
[✔]: SELECT time, user, host FROM tbl where acct_day=20171101
[✘]: SELECT * FROM tbl where visit_time=20171101

3）Group By语句优化 (presto)
合理安排Group by语句中字段顺序对性能有一定提升。将Group By语句中字段按照每个字段distinct数据多少进行降序排列。
[✔]: SELECT GROUP BY uid, gender
[✘]: SELECT GROUP BY gender, uid

4）Order by时使用Limit
Order by需要扫描数据到单个worker节点进行排序，导致单个worker需要大量内存。如果是查询Top N或者Bottom N，使用limit可减少排序计算和内存压力。
[✔]: SELECT * FROM tbl ORDER BY time LIMIT 100
[✘]: SELECT * FROM tbl ORDER BY time

5）用regexp_like代替多个like语句
Presto查询优化器没有对多个like语句进行优化，使用regexp_like对性能有较大提升

[✔]
SELECT
  ...
FROM
  access
WHERE
  regexp_like(method, 'GET|POST|PUT|DELETE')

[✘]
SELECT
  ...
FROM
  access
WHERE
  method LIKE '%GET%' OR
  method LIKE '%POST%' OR
  method LIKE '%PUT%' OR
  method LIKE '%DELETE%'

6）使用Join语句时将大表放在左边(presto)
Presto中join的默认算法是broadcast join，即将join左边的表分割到多个worker，然后将join
右边的表数据整个复制一份发送到每个worker进行计算。如果右边的表数据量太大，则可能会报内存溢出错误。

[✔] SELECT ... FROM large_table l join small_table s on l.id = s.id
[✘] SELECT ... FROM small_table s join large_table l on l.id = s.id

7）使用Rank函数代替row_number函数来获取TopN
在进行一些分组排序场景时，使用rank函数性能更好。

[✔]
SELECT checksum(rnk)
FROM (
  SELECT rank() OVER (PARTITION BY l_orderkey, l_partkey ORDER BY l_shipdate DESC) AS rnk
  FROM lineitem
) t
WHERE rnk = 1

[✘]
SELECT checksum(rnk)
FROM (
  SELECT row_number() OVER (PARTITION BY l_orderkey, l_partkey ORDER BY l_shipdate DESC) AS rnk
  FROM lineitem
) t
WHERE rnk = 1

内存调优

1 User & System memory
Presto把自己管理的内存分为两大类：user memory和system memory，所谓的user memory是跟用户数据相关的，比如读取用户输入数据会占据相应的内存，这种内存的占用量跟用户底层数据量大小是强相关的；system memory则是执行过程中衍生出的副产品，比如tablescan表扫描，write buffers写入缓冲区，跟查询输入的数据本身不强相关的内存。
执行过程中，presto是从具体的内存池中来实现分配user memory和system memory的。Presto有两种内存池，分别为常规内存池GENERAL_POOL、预留内存池RESERVED_POOL。
注：0.201之后SYSTEM_POOL已经被废弃，GENERAL_POOL扮演了之前GENERAL_POOL及SYSTEM_POOL的作用，提供user memory和system memory。
1．GENERAL_POOL：在一般情况下，一个查询执行所需要的user/system内存都是从general pool中分配的，reserved pool在一般情况下是空闲不用的。
2．RESERVED_POOL：大部分时间里是不参与计算的，但是当集群中某个Worker节点的general pool消耗殆尽之后，coordinator会选择集群中内存占用最多的查询，把这个查询分配到reserved pool，这样这个大查询自己可以继续执行，而腾出来的内存也使得其它的查询可以继续执行，从而避免整个系统阻塞。
reserved pool到底多大呢？这个是没有直接的配置可以设置的，他的大小上限就是集群允许的最大的查询的大小(query.total-max-memory-per-node)。
reserved pool也有缺点，一个是在普通模式下这块内存会被浪费掉了，二是大查询可以用Hive来替代。因此也可以禁用掉reserved pool（experimental.reserved-pool-enabled=false），那系统内存耗尽的时候没有reserved pool怎么办呢？它有一个OOM Killer的机制，对于超出内存限制的大查询SQL将会被系统Kill掉，从而避免影响整个presto。
三，Presto由于是完全基于内存的计算，经常出现OOM，需要调整内存。
1、Query exceeded per-node total memory limit of xx
适当增加query.max-total-memory-per-node。
2、Query exceeded distributed user memory limit of xx
适当增加query.max-memory。
3、Could not communicate with the remote task. The node may have crashed or be under too much load
内存不够，导致节点crash，可以查看/var/log/message。

注意：
1、query.max-memory-per-node小于query.max-total-memory-per-node。
2、query.max-memory小于query.max-total-memory。
3、query.max-total-memory-per-node 与memory.heap-headroom-per-node 之和必须小于 jvm max memory，也就是jvm.config 中配置的-Xmx。

2，DWS层实现

Ntille

NTILE()函数它把有序的数据集合 平均分配 到 指定的数量（num）个组中, 如果不能平均分配，则优先分配较小编号的组，并且各个组中能放的行数最多相差1。各个组有编号，编号从1开始，就像我们说的’分区’一样 ，分为几个区，一个区会有多少个。
每个组行数最多相差1的含义：
1、每组的记录数不能大于它上一组的记录数，即编号小的桶放的记录数不能小于编号大的桶。也就是说，第1组中的记录数只能大于等于第2组及以后各组中的记录数。
2、所有组中的记录数要么都相同，要么从某一个记录较少的组（命名为X）开始后面所有组的记录数都与该组（X组）的记录数相同。也就是说，如果有个组，前三组的记录数都是9，而第四组的记录数是8，那么第五组和第六组的记录数也必须是8。

GROUPING SETS、CUBE和ROLLUP

Grouping sets允许用户指定要分组的多个列列表。将不属于分组列的给定子列表的列设置为NULL。
比如，需求分别按照店铺分组、订单组分组、店铺和订单组分组统计订单销售额，并获取三者的结果集（插入到宽表）。可以通过union all多个group by来实现：

select store_id,
       group_id,
       sum(order_amount)
from yp_dwb.dwb_order_detail
group by
    grouping sets (store_id, group_id, (store_id, group_id));

同时，使用grouping sets、cube、rollup这些复杂group语法的SQL只会读取基础数据源一次，而使用union all拼接的SQL会读取基础数据源三次（或更多）。因此当数据源正在频繁变化时，使用union all拼接的方式可能会产生一些奇怪的结果。

CUBE

CUBE操作会生成所提供column所有可能的grouping sets结果。

select store_id,
       group_id,
       sum(order_amount)
from yp_dwb.dwb_order_detail
group by
    cube (store_id, group_id);

ROLLUP

rollup((a),(b),©)等价于grouping sets((a,b,c),(a,b),(a),())。

select store_id,
       group_id,
       sum(order_amount)
from yp_dwb.dwb_order_detail
group by
rollup (store_id,group_id);

GROUPING操作

grouping 返回一个转换为十进制数字的二进制数值集合，指示分组中存在哪些列。 它必须与GROUPING SETS，ROLLUP，CUBE或GROUP BY结合使用，并且其参数必须与相应的GROUPING SETS，ROLLUP，CUBE或GROUP BY子句中引用的列完全匹配。
对于给定的分组，如果分组中包含相应的列，则将位设置为0，否则将其设置为1。
Demo：

select group_id,
       store_id,
       sum(goods_amount),
       grouping(group_id)           as ging,
       grouping(store_id)           as sing,
       grouping(group_id, store_id) as g_s_ing
from yp_dwb.dwb_order_detail
group by
    grouping sets (group_id, store_id, (group_id, store_id));

grouping(group_id)列为0时，可以看到group_id列都是有值的，为1时则相反，证明当前行是按照group_id来进行分组统计的；
grouping(store_id)同理，为0时store_id列有值，为1时store_id为空，证明当前行时按照store_id来进行分组统计的；
grouping(group_id, store_id)是grouping(group_id)、grouping(store_id)二进制数值组合后转换得到的数字：
a.按照group_id分组，则group_id=0，store_id=1，组合后为01，二进制转换为十进制得到数字1；
b.按照store_id分组，则group_id=1，store_id=0，组合后为10，二进制转换为十进制得到数字2；
c.同时按照group_id和store_id分组，则group_id=0，store_id=0，组合后为00，二进制转换为十进制得到数字0。
因此可以使用grouping操作来判断当前数据是按照哪个字段来分组的。

运行时优化

set hive.optimize.skewjoin=true;
默认关闭。
如果大表和大表进行join操作，则可采用skewjoin(倾斜关联)来开启对倾斜数据的优化。
skewjoin原理：
1.对于skewjoin.key，在执行job时，将它们存入临时的HDFS目录，其它数据正常执行
2.对倾斜数据开启map join操作（多个map并行处理），对非倾斜值采取普通join操作
3.将倾斜数据集和非倾斜数据集进行合并Union操作。

开启skewin以后，究竟多大的数据才会被认为是倾斜了的数据呢？
set hive.skewjoin.key=100000;
默认值100000。
如果join的key对应的记录条数超过这个值，就认为这个key产生了数据倾斜，则会对其进行分拆优化。

编译时优化

上面的配置项其实应该理解为hive.optimize.skewjoin.runtime，也就是sql运行时来对偏斜信息进行优化；除此之外还有另外一个配置：
set hive.optimize.skewjoin.compiletime=true;
默认关闭。
此参数的用处和上面的hive.optimize.skewjoin一致，但在编译sql时就已经将执行计划优化完毕。但要注意的是，只有在表的元数据中存储的有数据倾斜信息时，才能生效。因此建议runtime和compiletime都设置为true。
可以通过建表语句来指定数据倾斜元数据：

Union优化

应用了表连接倾斜优化以后，会在执行计划中插入一个新的union操作，此时建议开启对union的优化配置：
set hive.optimize.union.remove=true;
默认关闭。
此项配置减少对Union all子查询中间结果的二次读写，可以避免union输出的额外扫描过程，当我们开启了skewjoin时尤其有用，建议同时开启。

set hive.optimize.skewjoin=true;
set hive.optimize.skewjoin.compiletime=true;
set hive.optimize.union.remove=true;

Map阶段聚合

hive.map.aggr=true;
开启map端combiner。此配置可以在group by语句中提高HiveQL聚合的执行性能。这个设置可以将顶层的聚合操作放在Map阶段执行，从而减轻数据传输和Reduce阶段的执行时间，提升总体性能。默认开启，无需显示声明。

两次MR

开启参数

set hive.groupby.skewindata=true;

思想

第一次MR
reduce拉取的数据是随机拉取，数据就均衡分布了，不会产生数据倾斜
第二次MR
reduce拉取的数据按照相同key的值发送到同一个reduce这种操作。因为上一次的数据量减少了，就不会产生数据倾斜

空key处理

空key的过滤

---2表连接
select  * from  A left join  B  on A.id = B.id
---
select * from (select  * from  A where id is not null )A  left join  (select * from B where id is not null ) B  on A.id = B.id

空key的转换

 select * from a LEFT JOIN  b ON CASE WHEN a.id IS NULL THEN round(rand(),2)*100 ELSE a.id END = CASE WHEN b.id IS NULL THEN round(rand(),2)*100 ELSE a.id END;

相当于将很多的空值转换成100份数据，就打散了