Cost-Based Optimizer Makes Apache Hive 0.14 More Than 2.5X Faster

Hortonworks Data Platform (HDP) provides Hadoop for the Enterprise, with a centralized architecture of core enterprise services, for any application and any data. HDP is uniquely built around native YARN services to enable a centralized architecture through which multiple data access applications interact with a shared data set. Apache Hive is one of the most important of those data access applications—the defacto standard for interactive SQL queries over petabytes of data in Hadoop.

This blog shares benchmark-testing results that show performance improvements made by the Apache Hive community with the Cost Based Optimizer (CBO). The CBO is part of Phase 1 of the Stinger.next Initiative to further enhance Hive’s speed, scale and SQL capabilities.

As more analysts and data scientists adopt Hive across their organizations, performance becomes crucial. Many users rely on BI tools to generate queries that are far from optimal in Hive, so Apache Hive 0.14 includes the CBO to maintain high performance while also allowing your team members to continue using their preferred BI tools.

CBOs do this by planning how a query will be executed, using statistics about the data and sophisticated algorithms to come up with query plans that optimize performance and maximize resource utilization across the Hadoop cluster. We want Hive to just work, without users having to concern themselves with performance.

We’re delighted with the performance gains shown in the table below, using the TPC-DS benchmark:

• 6 out of the 50 queries showed speedups greater than 5x
• Across all 51 queries, the average speedup was 3x
• Total workload runtime shrank from 20.6 to 9 hours

We would like to thank the members of the Apache Hive community who contributed code to make these improvements possible – Ashutosh Chauhan, Gunther Hagleitner, Harish Butani, Jesus Camacho Rodriguez, Julian Hyde, John Pullokkaran, Mostafa Mokhtar, and Pengcheng Xiong.

Results: Hive Query Performance Numbers on TPC-DS

Below are the highlights comparing run times of “data mining”, “interactive” and “reporting” queries in Apache Hive 0.14. The numbers compare speeds with CBO on with the same queries when CBO was disabled. We categorized queries by these six CBO effects:

Testing Environment & Parameters

Software

Hortonworks Data Platform 2.2 with:

• Hadoop 2.6.0
• Tez 0.5.2
• Hive 0.14.0

HDP was deployed using Apache Ambari 1.7.0. For the most part, the cluster used the Ambari defaults (except where noted below). Hive 0.14.0 runs were done using Java 7 (default JVM).

Apache Tez and MapReduce were tuned to process all queries using 4 GB containers at a target container-to-disk ratio of 2.0. The ratio is important because it minimizes disk thrash and maximizes throughput.

Other Settings:

• yarn.nodemanager.resource.memory-mb was set to 49152
• Default virtual memory for a job’s map-task and reduce-task were set to 4096
• hive.tez.dynamic.partition.pruning was set to false
• hive.stats.fetch.column.stats was set to true
• hive.tez.container.size was set to 4096
• hive.tez.java.opts was set to -Xmx3800m
• Tez app masters were given 8 GB
• mapreduce.map.java.opts and mapreduce.reduce.java.opts were set to -Xmx3800m. This is smaller than 4096 to allow for some garbage collection overhead
• hive.auto.convert.join.noconditionaltask.size was set to 320000000.

The following additional optimizations were used for Hive 0.14.0:

• Vectorized Query enabled
• ORCFile formatted data
• Map-join auto conversion enabled

Hardware

20 physical nodes, each with:

• 2x Intel(R) Xeon(R) CPU E5-2640 v2 @ 2.00GHz for total of 16 CPU cores/machine
• Hyper-threading enabled
• 256GB RAM per node
• 6x 4TB WDC WD4000FYYZ-0 drives per node
• 10 Gigabit interconnect between the nodes

Note: Based on the YARN Node Manager’s Memory Resource setting used below, only 48 GB of RAM per node was dedicated to query processing, the remaining 200 GB of RAM were available for system caches and HDFS.

Linux Configurations

• /proc/sys/net/core/somaxconn = 512
• /proc/sys/vm/dirty_writeback_centisecs = 6000
• /proc/sys/vm/swappiness = 0
• /proc/sys/vm/zone_reclaim_mode = 0
• /sys/kernel/mm/redhat_transparent_hugepage/defrag = never
• /sys/kernel/mm/redhat_transparent_hugepage/khugepaged/defrag = no
• /sys/kernel/mm/transparent_hugepage/khugepaged/defrag = 0

Data

• TPC-DS Scale 30,000 data, partitioned by day
• Hive 0.14.0 was run against data stored in ORCFile

Queries

• The test was driven by the Hive Testbench https://github.com/hortonworks/hive-testbench.
• First, the tpcds-build and tpcds-setup scripts were used to generate 30 terabytes (TB) of data. tpcds-setup first generated the data in text format, then converted it into ORCFile and RCFile formatted data, partitioned by day. After data was generated, “hadoop balancer” was used to balance data across the cluster.
• A total of 50 queries from the industry standard TPC-DS benchmark were run.

Note: Queries were run as-is with no additional hinting, and there was no special tuning used for any of the queries.