How Do I Locate a Large Hive SQL Exception?

Symptom

Large SQL statements in Hive consume excessive memory of HiveServer or MetaStore in MRS 3.5.0-LTS and later versions, leading to frequent full garbage collections (GCs) that can cause services to fail or restart abnormally.

Cause Analysis

• A large-field table is queried.
• Too many files are involved in the executed SQL statement.
• Too many partitions are involved in the executed SQL statement.

Procedure

• A large-field table is queried.

  HiveServer returns query results in batches to the client. By default, each batch contains 1000 records. Large fields consume a significant amount of memory, potentially causing HiveServer to run out of memory (OOM) and restart, resulting in failed tasks.

  When a large-field table query fails, the client crashes, as shown in Figure 1. FetchResults fails to process large fields, causing HiveServer to restart unexpectedly and disconnect.

  Figure 1 Large-field table query error
  

  Perform the following operations:

  1. Log in to Manager and choose O&M > Events. In the event list, view the details about the Process Restart event of the Hive object. In Location, record the name of the HiveServer host that reports the event. Record the value of the Generated field, which is the time when the HiveServer is restarted.

     Click Hosts in the navigation on the top. In the host list, view the IP address of the node corresponding to the host name for which the event is generated.

  2. Log in to the HiveServer node that is restarted in 1 and run the following command to check whether the JVM error log hs_err_pid_*log is generated during restart:

     ll /var/log/Bigdata/hive/hiveserver/hs_err_pid_*log

     • If yes, go to 3.
     • If no, go to 4.
  3. Use the keyword HiveSessionImpl.fetchResults to search for the name of the thread that is obtaining the returned result in the hs_err_pid_*log log and go to 5.
     Figure 2 Searching for a thread name
     
  4. In the run log file generated before the HiveServer is restarted in the /var/log/Bigdata/hive/hiveserver directory of the HiveServer run log, search for the OOM restart log "Stopping HiveServer2 due to OOM", record the thread name, and go to 5.
  5. In the HiveServer audit log /var/log/Bigdata/audit/hive/hiveserver/hive-audit.log, use the compilation thread name to search for the query SQL statement executed before the HiveServer restarts, and contact related service engineers to check whether the related table contains large fields, Stop the corresponding task or modify related tables.
     Figure 3 Searching for SQL statements
     
• Too many files are involved in the executed SQL statement.

  When you execute SQL statements with the MapReduce engine, HiveServer reads files from memory, slices them, and then submits tasks. Each file consumes approximately 2 KB of memory. Excessive files can cause HiveServer memory issues.

  1. Log in to Manager, choose Cluster > Services > Hive > Configurations > All Configurations, and check whether the following parameters are configured:

     hive-ext.combine.input.file.limit.enable: specifies whether to limit the number of files that can be read, defaulting to false. Enabling this function causes the system to collect statistics on read files and decide whether to intercept the SQL statement before submitting a Hive task, which increases task execution time.

     hive-ext.combine.input.file.limit.threshold: When the maximum number of files to read is enabled, SQL statements that exceed this limit will fail. The default limit is 1,000,000 files.

     • If yes, go to 2.
     • If no, go to 3.
  2. In the HiveServer run log file generated within a period before the HiveServer restarts in the /var/log/Bigdata/hive/hiveserver directory, use the keyword "The number of files exceeds" to filter the names of the threads that exceed 50% of the upper limit. Check the corresponding SQL statements and contact service engineers.
  3. Check with service engineers if parameters need to be enabled to limit large task execution. If the file count reaches 50% of the limit, a "The number of files exceeds ***" error is logged. If the file count hits the limit, the SQL query fails.
     • If yes, add the preceding two parameters to the custom HiveServer configuration and restart the HiveServer instance.
     • If no, go to 4.
  4. The task normally executed prints logs as shown in Figure 4. Filter out the logs by keyword "Total number of paths" and check whether there is any "Total input files to process" log (indicating the file scanning threads did not finish properly) or thread whose "Total input files to process" exceeds 100,000. If yes, contact or O&M engineers to check the related SQL statements.
     Figure 4 Task run log
     
• Too many partitions are involved in the executed SQL statement.

  When you query partitioned tables or delete partitions using msck, analyze, or alter without partition filter criteria, HiveServer queries all partition names and then partition objects by name in batches, consuming a large amount of memory. With partition filtering conditions are configured, HiveServer pushes the conditions to MetaStore. However, if many partitions match the conditions, MetaStore memory usage becomes excessive.

  1. Check whether it is a HiveServer or MetaStore node that experiences exceptions.
     • If it is a HiveServer node, go to 2.
     • If it is a MetaStore node, go to 3.
  2. In the HiveServer run log file generated before the HiveServer becomes abnormal in the /var/log/Bigdata/hive/hiveserver directory, use the keyword "The number of part size" to filter the tables that have more than 10,000 partitions scanned during the exception period. Check related SQL statements based on the thread name, and contact O&M engineers.
  3. Log in to Manager, choose Cluster > Services > Hive > Configurations > All Configurations, and check whether the following parameters are configured:

     metastore.limit.partition.request: The default value is -1. When this parameter is set to a value greater than 0, MetaStore pre-counts the number of partitions to query, using the filter criteria. If the count exceeds the specified value, the query fails.

     • If yes, go to 4.
     • If no, go to 5.
  4. In the MetaStore run log file generated before the MetaStore exception occurs in the /var/log/Bigdata/hive/metastore directory, use the keyword "The number of part size" to filter the tables that have more than 10,000 partitions during the exception period. Check related SQL statements based on the table names, and contact O&M engineers.
  5. Check with service engineers if parameters need to be enabled to limit large task execution. Enabling these parameters may increase query time of count operations due to added partition filtering.
     • If yes, add related parameters to the MetaStore custom configuration and restart the MetaStore instance.
     • If no, go to 6.
  6. In the MetaStore audit log file in the /var/log/Bigdata/audit/hive/metastore directory, use the keywords get_partitions_by_expr, get_partitions, get_partitions_with_auth, and get_partitions_by_filter to filter the tables queried during the exception period, check related SQL statements, and contact O&M engineers.