Hadoop企业级应用案例之SQL on Hadoop倒数第二周课程马上就要完成了，感谢一直以来支持的朋友，坚持下来不容易，也有很多不如意的地方，经验不足，请大家多参与讨论，有反馈才能根据情况做调整？性能优化篇，目前更新了2节最新的内容。还有一些小思考，课后大家有时间尽量回复我。非常感谢支持！
• Yarn
–CPU：yarn.nodemanager.resource.cpu-vcores = 8
–内存：yarn.nodemanager.resource.memory-mb = 8G
• Map Tasks:
mapreduce.map.memory.mb=2240 # Container size
mapreduce.map.java.opts=-Xmx2016m # JVM arguments for a Map task
mapreduce.map.cpu.vcores=1
• Reduce Tasks:
mapreduce.reduce.memory.mb=2240 # Container size
mapreduce.reduce.java.opts=-Xmx2016m # JVM arguments for a Reduce task
mapreduce.reduce.cpu.vcores=1
• MapReduce Application Master:
yarn.app.mapreduce.am.resource.mb=2240 # Container size
yarn.app.mapreduce.am.command-opts=-Xmx2016m # JVM arguments for an Application Master
yarn.app.mapreduce.am.resource.cpu-vcores=1
-----
Yarn:
yarn.nodemanager.resource.memory-mb 60GB #yarn中其他一些任务也耗内存
yarn.nodemanager.resource.cpu-vcores 18core
Mapper /node
CPU:1 [mapreduce.map.cpu.vcores ]
MEM:2G [mapreduce.map.memory.mb ]
===> 18 map slot/node
Reducer/node
CPU:1 [mapreduce.reduce.cpu.vcores]
MEM:2G [mapreduce.reduce.memory.mb]
===> 【如果有60G内存，实际有CPU 18个，所以只能启动18个reduce在每个node上】 | 18 reduce slot/node
–Heap Size：-Xmx在mapreduce.map.java.opts
–Heap Size：-Xmx在mapreduce.reduce.java.opts
<property>
<name>hive.mapred.reduce.tasks.speculative.execution</name>
<value>false</value>
</property>
<property>
<name>mapreduce.reduce.speculative</name>
<value>false</value>
</property>
<property>
<name>hive.ignore.mapjoin.hint</name>
<value>false</value>
</property>
<property>
<name>hive.mapjoin.smalltable.filesize</name>
<value>500000000</value>
</property>
<property>
<name>hive.exec.parallel</name>
<value>true</value>
</property>
<property>
<name>hive.exec.parallel.thread.number</name>
<value>16</value>
</property>
<property>
<name>hive.cli.print.current.db</name>
<value>true</value>
</property>
<property>
<name>hive.cli.print.header</name>
<value>true</value>
</property>
<property>
<name>hive.stats.autogather</name>
<value>false</value>
</property>
reducer个数
• hive.exec.reducers.bytes.per.reducer
• mapred.reduce.tasks=-1
权限问题
• hive.warehouse.subdir.inherit.perms
HiveServer2内存
-Xmx=2048m 甚至 -Xmx=4g
数据倾斜
set hive.exec.reducers.max=200
set mapred.reduce.tasks=200
set hive.groupby.skewindata=true;
set hive.skewjoin.key=100000
set hive.optimize.skewjoin=true
set hive.groupby.mapaggr.checkinterval=100000
Hadoop 2.7+ :
允许用户设置每个job可同时运行的map task和reduce task数目。这个功能非常实用,用户可通过以下两个参数控制同时运行的task数目：
mapreduce.job.running.map.limit (default: 0, for no limit)
mapreduce.job.running.reduce.limit (default: 0, for no limit)
spark on yarn
----
// 启用动态资源分配
set spark.dynamicAllocation.enabled=true;
set spark.dynamicAllocation.schedulerBacklogTimeout= 1s;
set spark.dynamicAllocation.executorIdleTimeout=120s;
set spark.dynamicAllocation.sustainedSchedulerBacklogTimeout=1s
set spark.dynamicAllocation.minExecutors=100;
set spark.dynamicAllocation.maxExecutors=400;
set spark.shuffle.service.enabled=true;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
spark.shuffle.service.enabled true //启用External shuffle Service服务
spark.dynamicAllocation.enabled true //开启动态资源分配
spark.dynamicAllocation.minExecutors 1 //每个Application最小分配的executor数
spark.dynamicAllocation.maxExecutors 30 //每个Application最大并发分配的executor数
spark.dynamicAllocation.schedulerBacklogTimeout 1s
spark.dynamicAllocation.executorIdleTimeout=120; //executor空闲时间超过120s回收
spark.dynamicAllocation.sustainedSchedulerBacklogTimeout 5s
compress.codec：
•mapreduce.map.output.compress.codec
•mapreduce.output.fileoutputformat.compress.codec
•mapreduce.output.fileoutputformat.compress.type
– org.apache.hadoop.io.compress.DefaultCodec
– org.apache.hadoop.io.compress.SnappyCodec [最佳选择]
– org.apache.hadoop.io.compress.BZip2Codec GzipCodec【GzipCodec压缩最高，但是时间上比较耗时】
客户端设置：
Job:
•mapreduce.job.reduces 根据情况设置
–(节点数 * reduce slot数) 的倍数
•mapreduce.client.submit.file.replication 提交jar文件的副本个数
•mapreduce.map.output.compress + codec (对map生成的中间数据，做一些压缩，但是会耗时，cpu压力？)
io.sort:
•mapreduce.task.io.sort.factor = 64
•mapreduce.task.io.sort.mb - io.sort的内存，缓存map中间结果的buffer
–io.sort.mb (MR1)
•mapreduce.map.sort.spill.percent - MR2的map.sort内存溢出百分比
–io.sort.spill.percent (MR1)
•mapreduce.reduce.shuffle.parallelcopies - 复制（洗牌）阶段期间 reduce 运行的并行传输的默认数量
–sqrt(节点数* map slot数) 与 (节点数 * map slot数)/2 之间
--SQL 95
SELECT count(distinct ws1.ws_order_number) as order_count,
sum(ws1.ws_ext_ship_cost) as total_shipping_cost,
sum(ws1.ws_net_profit) as total_net_profit
FROM web_sales ws1
JOIN customer_address ca ON (ws1.ws_ship_addr_sk = ca.ca_address_sk)
JOIN web_site s ON (ws1.ws_web_site_sk = s.web_site_sk)
JOIN date_dim d ON (ws1.ws_ship_date_sk = d.d_date_sk)
LEFT SEMI JOIN (SELECT ws2.ws_order_number as ws_order_number
FROM web_sales ws2 JOIN web_sales ws3
ON (ws2.ws_order_number = ws3.ws_order_number)
WHERE ws2.ws_warehouse_sk <> ws3.ws_warehouse_sk
) ws_wh1
ON (ws1.ws_order_number = ws_wh1.ws_order_number)
LEFT SEMI JOIN (SELECT wr_order_number
FROM web_returns wr
JOIN (SELECT ws4.ws_order_number as ws_order_number
FROM web_sales ws4 JOIN web_sales ws5
ON (ws4.ws_order_number = ws5.ws_order_number)
WHERE ws4.ws_warehouse_sk <> ws5.ws_warehouse_sk
) ws_wh2
ON (wr.wr_order_number = ws_wh2.ws_order_number)) tmp1
ON (ws1.ws_order_number = tmp1.wr_order_number)
WHERE d.d_date between '2002-05-01' and '2002-06-30' and
ca.ca_state = 'GA' and
s.web_company_name = 'pri';
Hive优化：
前： 344.609 s
后： 273.793 s
Impala:
impala 无on yarn,没资源池限制: 184.84s
llama on yarn模式，资源池控制：143.13s
+-------------+---------------------+--------------------+
| order_count | total_shipping_cost | total_net_profit |
+-------------+---------------------+--------------------+
| 508 | 823037.2186106443 | -138806.7298403084 |
+-------------+---------------------+--------------------+
Fetched 1 row(s) in 143.13s
1、内存
-服务器端(impalad)
.mem_limit #一个节点的impala守护进程能使用的内存
-default_query_options
.mem_limit=xxx #每启动一个impala client它的单一查询，能够使用的最大内存
2、并发查询
-queue
.default pool 设置
.-queue_wait_timeout_ms默认只有60s-->600s
3、资源管理
-Dynamic Resource Pools
.并发控制：max running queries
4、llama on yarn #impala基于yarn的动态资源分配实现
- default_query_options --> REQUEST_POOL=root.llama # 指定一个以长任务运行于yarn,资源池
- http://cloudera.github.io/llama/llama-site.html
- cpu && mem config
5、impala 溢写磁盘,需要占用空间
Impala Daemon 暂存目录
-- 大数据数据仓库选型中，集群性能指标的主要基准测试标准是？
-- 看具体的应用场景
-做报表，没有太多的adhoc查询，ok ？
-有多用户同时访问，多用户同时提交查询的话，hadoop表现就不那么好了，延迟严重，
并发性没看到很好的解决方案出现？
-一个是计算，一个是数据转换，hadoop计算处理后放入rdbms or NOSQL(newsql) ？
我的几个问题：
-- 是否需要讲解更多？有关错误以及解决过程？目前我把最佳实践告诉各位？
你在使用集群1-2年后才能慢慢遇到问题，才知道集群规划，参数该怎么设置
该犯的错你一个不会少?
-- 基础，百度谷歌就能解决的，就别直接来问我，在群里讨论即可？一些概念性的东西,下来多去看看资料？
泪流满面，不是说了0基础学习大数据技术吗？我们这个课程太高级？NO,NO,NO！
==> Impala Daemon 内存限制，mem_limit，impalad内存设置导致问题：导致的问题如下：
ERROR: com.cloudera.llama.util.LlamaException: RESERVATION_ASKING_MORE_MB - Reservation '574da7b46da0b8d0:1dc47257073ccab2', expansion 'null' is asking for more memory in mb '15112' than capacity '5281' on node 'server04'., com.cloudera.llama.util.LlamaException: RESERVATION_ASKING_MORE_MB - Reservation '574da7b46da0b8d0:1dc47257073ccab2', expansion 'null' is asking for more memory in mb '15112' than capacity '5281' on node 'server04'., at com.cloudera.llama.am.impl.ExpansionReservationsLlamaAM.checkAndUpdateCapacity(ExpansionReservationsLlamaAM.java:170), at com.cloudera.llama.am.impl.ExpansionReservationsLlamaAM.reserve(ExpansionReservationsLlamaAM.java:129), at com.cloudera.llama.am.impl.APIContractLlamaAM.reserve(APIContractLlamaAM.java:144), at com.cloudera.llama.am.LlamaAMServiceImpl.Reserve(LlamaAMServiceImpl.java:132), at com.cloudera.llama.am.MetricLlamaAMService.Reserve(MetricLlamaAMService.java:140), at com.cloudera.llama.thrift.LlamaAMService$Processor$Reserve.getResult(LlamaAMService.java:512), at com.cloudera.llama.thrift.LlamaAMService$Processor$Reserve.getResult(LlamaAMService.java:497), at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39), at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39), at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:206), at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145), at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615), at java.lang.Thread.run(Thread.java:745)
WARNING: The following tables are missing relevant table and/or column statistics.
tpcds_text_10.customer_address,tpcds_text_10.date_dim,tpcds_text_10.web_returns,tpcds_text_10.web_sales,tpcds_text_10.web_site
1. If I understand correctly, what you want is to set a default query mem limit from CM using the safety valve machanism. That should have the effect that any Impala query has a default query mem_limit based on that value (you can still change it on a case by case basis if needed):
You can refer to this docs page:
http://www.cloudera.com/content/cloudera/en/documentation/cloudera-impala/latest/topics/impala_config_options.htm
Search for 'default_query_option'
2. Alternatively, you may want to refer to:
http://www.cloudera.com/documentation/cdh/5-1-x/Impala/Installing-and-Using-Impala/ciiu_resource_management.html#rm_resource_estimates_unique_1
and consider setting the 'rm_always_use_defaults' option min conjunction with 'rm_default_memory' and 'rm_default_cpu_cores'.
