Tuning Hive

To configure heap size and garbage collection for HiveServer2:

1. To set heap size, go to Home > Hive > Configuration > HiveServer2 > Resource Management.
2. Set Java Heap Size of HiveServer2 in Bytes to the desired value, and click Save Changes.
3. To set garbage collection, go to Home > Hive > Configuration > HiveServer2 > Advanced.

4. Set the PermGen space for Java garbage collection to 512M, the type of garbage collector used (ConcMarkSweepGC or ParNewGC), and enable or disable the garbage collection overhead limit in Java Configuration Options for HiveServer2.

   The following example sets the PermGen space to 512M, uses the new Parallel Collector, and disables the garbage collection overhead limit:

   -XX:MaxPermSize=512M -XX:+UseParNewGC -XX:-UseGCOverheadLimit
   

5. From the Actions drop-down menu, select Restart to restart the HiveServer2 service.

To configure heap size and garbage collection for the Hive metastore:

1. To set heap size, go to Home > Hive > Configuration > Hive Metastore > Resource Management.
2. Set Java Heap Size of Hive Metastore Server in Bytes to the desired value, and click Save Changes.
3. To set garbage collection, go to Home > Hive > Configuration > Hive Metastore Server > Advanced.
4. Set the PermGen space for Java garbage collection to 512M, the type of garbage collector used (ConcMarkSweepGC or ParNewGC), and enable or disable the garbage collection overhead limit in Java Configuration Options for Hive Metastore Server. For an example of this setting, see step 4 above for configuring garbage collection for HiveServer2.
5. From the Actions drop-down menu, select Restart to restart the Hive Metastore service.

To configure heap size and garbage collection for the Beeline CLI:

1. To set heap size, go to Home > Hive > Configuration > Gateway > Resource Management.
2. Set Client Java Heap Size in Bytes to at least 2 GiB and click Save Changes.
3. To set garbage collection, go to Home > Hive > Configuration > Gateway > Advanced.

4. Set the PermGen space for Java garbage collection to 512M in Client Java Configuration Options.

   The following example sets the PermGen space to 512M and specifies IPv4:

   -XX:MaxPermSize=512M -Djava.net.preferIPv4Stack=true
   

5. From the Actions drop-down menu, select Restart to restart the client service.

To configure the heap size for HiveServer2 and Hive metastore, set the -Xmx parameter in the HADOOP_OPTS variable to the desired maximum heap size in /etc/hive/hive-env.sh.

To configure the heap size for the Beeline CLI, set the HADOOP_HEAPSIZE environment variable in /etc/hive/hive-env.sh before starting the Beeline CLI.

if [ "$SERVICE" = "cli" ]; then
  if [ -z "$DEBUG" ]; then
    export HADOOP_OPTS="$HADOOP_OPTS -XX:NewRatio=12 -Xmx12288m -Xms12288m
              -XX:MaxHeapFreeRatio=40 -XX:MinHeapFreeRatio=15 -XX:+UseParNewGC -XX:-UseGCOverheadLimit"
  else
    export HADOOP_OPTS="$HADOOP_OPTS -XX:NewRatio=12 -Xmx12288m -Xms12288m
              -XX:MaxHeapFreeRatio=40 -XX:MinHeapFreeRatio=15 -XX:-UseGCOverheadLimit"
  fi
fi

export HADOOP_HEAPSIZE=2048

You can use either the Concurrent Collector or the new Parallel Collector for garbage collection by passing -XX:+useConcMarkSweepGC or -XX:+useParNewGC in the HADOOP_OPTS lines above. To enable the garbage collection overhead limit, remove the -XX:-UseGCOverheadLimit setting or change it to -XX:+UseGCOverheadLimit.

Set the PermGen space for Java garbage collection to 512M for all in the JAVA-OPTS environment variable. For example:

set JAVA_OPTS="-Xms256m -Xmx1024m -XX:PermSize=512m -XX:MaxPermSize=512m"

Optimal HS2 heap size configuration depends on several factors, including workload characteristics, number of concurrent clients, and the partitioning of underlying Hive tables. To resolve HS2 memory-related issues, confirm that the HS2 heap size is set properly for your environment.

1. In CDH 5.7 and higher, Cloudera Manager starts the HS2 service with 4 GB heap size by default unless hosts have insufficient memory. However, the heap size on lower versions of CDH or upgraded clusters might not be set to this recommended value. To raise the heap size to at least 4 GB:
   1. In Cloudera Manager, go to Home > Hive > Configuration > HiveServer2 > Resource Management.
   2. Set Java Heap Size of HiveServer2 in Bytes to 4 GiB and click Save Changes.
   3. From the Actions drop-down menu, select Restart to restart the HS2 service.

   If HS2 is already configured to run with 4 GB or greater heap size and there are still performance issues, workload characteristics may be causing memory pressure. Increase heap size to reduce memory pressure on HS2. Cloudera does not recommend exceeding 16 GB per instance because of long garbage collection pause times. See Identify Workload Characteristics That Increase Memory Pressure for tips to optimize query workloads to reduce the memory requirements on HS2. Cloudera recommends splitting HS2 into multiple instances and load-balancing once you start allocating over 16 GB to HS2.

2. If workload analysis does not reveal any major issues, or you can only address workload issues over time, consider the following options:
   • Increase the heap size on HS2 in incremental steps. Cloudera recommends increasing the heap size by 50% from the current value with each step. If you have increased the heap size to 16 GB and issues persist, contact Cloudera Support.
   • Reduce the number of services running on the HS2 host.
   • Load-balance workloads across multiple HS2 instances as described in How the Number of Concurrent Connections Affect HiveServer2 Performance.
   • Add more physical memory to the host or upgrade to a larger server.

The number of concurrent connections can impact HS2 in the following ways:

• High number of concurrent queries

  High numbers of concurrent queries increases the connection count. Each query connection consumes resources for the query plan, number of table partitions accessed, and partial result sets. Limiting the number of concurrent users can help reduce overall HS2 resource consumption, especially limiting scenarios where one or more "in-flight" queries returns large result sets.

  How to resolve:

  • Load-balance workloads across multiple HS2 instances by using HS2 load balancing, which is available in CDH 5.7 and later. Cloudera recommends that you determine the total number of HS2 servers on a cluster by dividing the expected maximum number of concurrent users on a cluster by 40. For example, if 400 concurrent users are expected, 10 HS2 instances should be available to support them. See Configuring HiveServer2 High Availability in CDH for setup instructions.
  • Review usage patterns, such as batch jobs timing or Oozie workflows, to identify spikes in the number of connections that can be spread over time.

• Many abandoned Hue sessions

  Users opening numerous browser tabs in Hue causes multiple sessions and connections. In turn, all of these open connections lead to multiple operations and multiple result sets held in memory for queries that finish processing. Eventually, this situation leads to a resource crisis.

  How to resolve:

  • Reduce the session timeout duration for HS2, which minimizes the impact of abandoned Hue sessions. To reduce session timeout duration, modify these configuration parameters as follows:
    • hive.server2.idle.operation.timeout=7200000

      The default setting for this parameter is 21600000 or 6 hours.

    • hive.server2.idle.session.timeout=21600000

      The default setting for this parameter is 43200000 or 12 hours.

      To set these parameters in Cloudera Manager, go to Home > Hive > Configuration > HiveServer2 > Advanced , and then search for each parameter.

  • Reduce the size of the result set returned by adding filters to queries. This minimizes memory pressure caused by "dangling" sessions.

If increasing the heap size based on configuration guidelines does not improve performance, analyze your query workloads to identify characteristics that increase memory pressure on HS2. Workloads with the following characteristics increase memory requirements for HS2:

• Queries that access a large number of table partitions:
  • Cloudera recommends that a single query access no more than 10,000 table partitions. If joins are also used in the query, calculate the combined partition count accessed across all tables.
  • Look for queries that load all table partitions in memory to execute. This can substantially add to memory pressure. For example, a query that accesses a partitioned table with the following SELECT statement loads all partitions of the target table to execute:

    SELECT * FROM <table_name> LIMIT 10;
    

    How to resolve:

    • Add partition filters to queries to reduce the total number of partitions that are accessed. To view all of the partitions processed by a query, run the EXPLAIN DEPENDENCY clause, which is explained in the Apache Hive Language Manual.
    • Set the hive.metastore.limit.partition.request parameter to 1000 to limit the maximum number of partitions accessed from a single table in a query. See the Apache wiki for information about setting this parameter. If this parameter is set, queries that access more than 1000 partitions fail with the following error:

      MetaException: Number of partitions scanned (=%d) on table '%s' exceeds limit (=%d)
      

      Setting this parameter protects against bad workloads and identifies queries that need to be optimized. To resolve the failed queries:
      • Apply the appropriate partition filters.
      • Override the limit on a per-query basis.
      • Increase the cluster-wide limit beyond 1000, if needed, but note that this adds memory pressure to HiveServer2 and the Hive metastore.
    • If the accessed table is not partitioned, see this Cloudera Engineering Blog post, which explains how to partition Hive tables to improve query performance. Choose columns or dimensions for partitioning based upon usage patterns. Partitioning tables too much causes data fragmentation, but partitioning too little causes queries to read too much data. Either extreme makes querying inefficient. Typically, a few thousand table partitions is fine.
• Wide tables or columns:
  • Memory requirements are directly proportional to the number of columns and the size of the individual columns. Typically, a wide table contains over 1,000 columns. Wide tables or columns can cause memory pressure if the number of columns is large. This is especially true for Parquet files because all data for a row-group must be in memory before it can be written to disk. Avoid wide tables when possible.

  • Large individual columns also cause the memory requirements to increase. Typically, this happens when a column contains free-form text or complex types.

    How to resolve:

    • Reduce the total number of columns that are materialized. If only a subset of columns are required, avoid SELECT * because it materializes all columns.
    • Instead, use a specific set of columns. This is particularly efficient for wide tables that are stored in column formats. Specify columns explicitly instead of using SELECT *, especially for production workloads.
• High query complexity

  Complex queries usually have large numbers of joins, often over 10 joins per query. HS2 heap size requirements increase significantly as the number of joins in a query increases.

  How to resolve:

  • Make sure that partition filters are specified on all partitioned tables that are involved in JOINs.
  • Whenever possible, break queries into multiple smaller queries with intermediate temporary tables.
• Improperly written user-defined functions (UDFs)

  Improperly written UDFs can exert significant memory pressure on HS2.

  How to resolve:

  • Understand the memory implications of the UDF and test it before using it in production environments.

The following general best practices help maintain a healthy Hive cluster:

• Review and test queries in a development or test cluster before running them in a production environment. Monitor heap memory usage while testing.
• Redirect and isolate any untested, unreviewed, ad-hoc, or "dangerous" queries to a separate HS2 instance that is not critical to batch operation.

Turn on this optimization feature on a per-query basis by setting these parameters in the query code with the Hive SET command:

set hive.blobstore.use.blobstore.as.scratchdir=false
set hive.exec.copyfile.maxsize=1099511627776;
          

Use Cloudera Manager to enable this feature by setting these same two parameters as service-wide defaults by using the HiveServer2 Safety Valve configuration setting for hive-site.xml:

1. In the Cloudera Manager Admin Console, go to the Hive service.
2. Click the Configuration tab.
3. Click the HiveServer2 scope.
4. Click the Advanced category.
5. Scroll down to the HiveServer2 Advanced Configuration Snippet (Safety Valve) for hive-site.xml and click the plus sign under the last configuration parameter in this section.
6. In the new Name and Value fields, type the hive.blobstore.use.blobstore.as.scratchdir parameter name and the false value, respectively.
7. Click the plus sign again, and in the Name and Value fields, type the hive.exec.copyfile.maxsize parameter name and the 1099511627776 value.
8. Click Save Changes.

The following restrictions apply to this feature:

• CTAS (CREATE TABLE AS SELECT) Queries
  Setting these parameters might not improve performance when the following two conditions are true:

  • The target table's parent database is created with an HDFS location, and
  • The individual CTAS query specifies an S3 location.

  Performance gains do not occur if CTAS queries have already staged data in HDFS prior to installing CDH 5.9. However, if the target database of a CTAS query is created with a location that specifies an S3 path, query performance might be improved when CDH 5.9 is installed due to this optimization. Enabling the optimization in either case causes no harm.

• HDFS Encryption

  The hive.exec.copyfile.maxsize parameter is also used when copying files between encryption zones for LOAD statements. If you are using HDFS encryption in an environment where performance is critical, Cloudera does not recommend that you change the service-wide value of hive.exec.copyfile.maxsize.