记录单条HQL语句配置调优过程

前言:数据库调优的PJ,我完成的实验部分内容包括基准测试,和一系列调优过程,因此在此记录一下调优过程,具体实验基准测试数据就不放在这里了,需要的请与我联系。本次测试基于hadoopHA环境基础上,使用mysql+hive对选取的数据集(TPC-H提供的GB级标准测试数据)进行性能测试,按照目前流行的TPC-H测试方法,进行了各种标准案例的测试,并在此基础上,进行了Hive配置参数,进行时间优化。

主要工具: CentOS release 6.4 hadoop-2.5.0 hive-1.2.2 TPC-H_on_Hive tpc-h 配置: 三台虚拟机,其中,两台2GB内存20GB硬盘,一台4GB内存20GB硬盘

tpc-h

TPC-H基准测试包括22个查询(Q1~Q22)随机组成查询流 TPC-H模型的评价指标主要为各个查询的响应时间,即从提交查询到结果返回所需时间。TPC-H基准测试的度量单位:每小时执行的查询数(Qph@size)。其中H表示每小时系统执行复杂查询的平均次数,size表示数据库规模的大小,它能够反映出系统在处理查询时的能力。 TPC-H模型的表结构,8张表(表上有些约束等需要满足,参见TPC-H规范),如下: PART:表示零件的信息 SUPPLIER:表示供货商的信息 PARTSUPP:表示供货商的零件的信息 CUSTOMER:表示消费者的信息 ORDERS:表示订单的信息 LINEITEM:表示订单的信息 NATION:表示国家的信息 REGION:表示地区的信息

TPC-H表结构图

调优过程

q4语句: DROP TABLE orders;
DROP TABLE lineitem;
DROP TABLE q4_order_priority_tmp;
DROP TABLE q4_order_priority;
– create tables and load data create external table orders (O_ORDERKEY INT, O_CUSTKEY INT, O_ORDERSTATUS STRING, O_TOTALPRICE DOUBLE, O_ORDERDATE STRING, O_ORDERPRIORITY STRING, O_CLERK STRING, O_SHIPPRIORITY INT, O_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘|’ STORED AS TEXTFILE LOCATION ‘/tpch/orders’;
Create external table lineitem (L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE STRING, L_COMMITDATE STRING, L_RECEIPTDATE STRING, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘|’ STORED AS TEXTFILE LOCATION ‘/tpch/lineitem’;
– create the target table CREATE TABLE q4_order_priority_tmp (O_ORDERKEY INT);
CREATE TABLE q4_order_priority (O_ORDERPRIORITY STRING, ORDER_COUNT INT);
set mapred.min.split.size=536870912;
– the query INSERT OVERWRITE TABLE q4_order_priority_tmp
select
DISTINCT l_orderkey
from
lineitem where
l_commitdate < l_receiptdate;
INSERT OVERWRITE TABLE q4_order_priority
select o_orderpriority, count(1) as order_count
from
orders o join q4_order_priority_tmp t
on
o.o_orderkey = t.o_orderkey and o.o_orderdate >= ‘1993-07-01’ and o.o_orderdate < ‘1993-10-01’
group by o_orderpriority
order by o_orderpriority;

我们以q4查询为例,逐步优化,最后总体测试优化结果;

default配置下q4查询总时间为: Time taken:216.158

优化1:部分本地模式

<property>
    <name>hive.exec.mode.local.auto</name>
    <value>true</value>
    <description>Let Hive determine whether to run in local mode automatically</description>
</property>

第一项优化我们开启了本地模式,再次运行,得到结果, Time taken:210.367 说明优化产生作用,但是效果并不明显,我们在输出日志注意到如下内容:

Cannot run job locally: Input Size (= 759863287) is larger than hive.exec.mode.local.auto.inputbytes.max (= 134217728)

说明本地模式并不完全,有部分作业超过本地内存输入,因此不在本地进行,因此我们进行第二项优化。

优化2:本地模式完全开启

  <property>
    <name>hive.exec.mode.local.auto.inputbytes.max</name>
    <value>760000000</value>
    <description>When hive.exec.mode.local.auto is true, input bytes should less than this for local mode.</description>
  </property>

Time taken:67.465 可见优化效果明显。

优化3:开启并行

<property>
    <name>hive.exec.parallel</name>
    <value>true</value>
</property>

Time taken:76.007 seconds 发现时间并没有缩短反而延长,多次测试后发现不是偶然现象,优化没有效果,查阅资料可能是本地没有足够资源进行并行,加上部分查询本身存在依赖,因此本次优化并没有取得很好的效果,除此之外在完全本地模式下,本地资源已经造成足够大的负担,采用其他优化在此之上时间并没有缩短,因此之后的优化中取消完全本地模式,开启半本地模式,其他任务还是提交到集群中进行处理。

优化4:groupby优化

<property>
    <name>hive.groupby.skewindata</name>
    <value>true</value>
    <description>Whether there is skew in data to optimize group by queries</description>
</property>

totaljob=8 Time taken:200.623 我们发现输出日志内容中,job数目从7增加到8,groupby配置用于控制负载均衡,当数据出现倾斜时,如果该变量设置为true,那么Hive会自动进行负载均衡。

优化5:非严格分区模式

<property>
    <name>hive.exec.dynamic.partition.mode</name>
    <value>nonstrict</value>
    <description>
      In strict mode, the user must specify at least one static partition
      in case the user accidentally overwrites all partitions.
      In nonstrict mode all partitions are allowed to be dynamic.
    </description>
</property>

hive提供了一个严格模式,可以防止用户执行那些可能产生意想不到的不好的效果的查询。即某些查询在严格 模式下无法执行。通过设置hive.mapred.mode的值为strict,可以禁止3种类型的查询。修改配置之后,发现测试在时间运行上没有优化效果 Time taken:202.823 seconds

优化6:独立的jvm中执行map/reduce

  <property>
    <name>hive.exec.submitviachild</name>
    <value>true</value>
    <description/>
</property>

Time taken:185.646 seconds 优化效果较为明显,该项配置修改在非本地模式的任务,在自己的jvm上提交任务,猜测减少了网络IO的时间啊,因此时间效率产生优化

优化7:压缩

<property>
                <name>hive.exec.compress.intermediate</name>
                <value>true</value>
     </property>
        <property>
       <name>hive.exec.compress.output</name>
     <value>true</value>
</property>

顺着优化6的思路,可以继续减少网络IO的时间,因此我们开启压缩,任务数据进行压缩会消耗部分cpu时间,但是发现结果中优化依然有效。 Time taken:167.921 seconds

优化8:基于上述配置开启本地模式

Time taken:131.777 seconds 发现运行时间减少,但是多于仅开启本地模式的时间Time taken:64.826,发现多条优化策略在时间上慢于开启完全本地模式时间。

总结

在数据库调优课程上老师说过,调优无非是时间空间上的转化,牺牲空间换取时间或者牺牲时间效率进行空间优化,基于本次调优的一系列过程,我更深刻的学习到,hadoop本身特征是基于海量数据处理,可以将任务提交到多台处理的机制,那么也就决定了,hadoop生态框架利于扩展和海量数据的处理,那么也就牺牲了时间效率,因此,时间优化效果,本地模式更快的结论与此也相契合,如要注重时间效率可以采用spark等实时基于内存实时计算的大数据并行计算框架,可能基于时间的优化更为明显。