Help Center/ MapReduce Service/ Component Operation Guide (Normal)/ Using Hive/ Hive Troubleshooting/ How Do I Troubleshoot Slow Hive SQL Execution?
Updated on 2024-10-08 GMT+08:00
View PDF
Share

How Do I Troubleshoot Slow Hive SQL Execution?

Scenario

Possible causes of a Hive SQL task's long running time include slow HiveServer compilation, HDFS access, YARN access, and metadata access.

Procedure

To troubleshoot Hive SQL execution slowdown, follow these steps:

  • Slow HiveServer Compilation

    View the HiveServer audit log /var/log/Bigdata/audit/hive/hiveserver/hive-audit.log and search for the running SQL statement. For example, the SQL statement in Figure 1 is show databases. Filter the two records whose thread names contain HiveServer2-Handler-Pool. The first record indicates the compilation start time, and the second record indicates the end time. Check the audit records before and after the SQL statement execution becomes slow to determine if the HiveServer compilation is slowing down.

    Figure 1 SQL statement running on HiveServer
  • Slow HDFS access
    • Method 1:

      View the HiveServer run log file /var/log/Bigdata/hive/hiveserver/hive.log, search for the thread log corresponding to the running SQL statement, and then search for the number of splits log. If the interval between the logs is long, the HDFS access is slowing down.

    • Method 2:

      Print the jstack of the HiveServer process and check whether the related threads are suspended during HDFS access . If yes, the HDFS access is slowing down.

    • Method 3:

      View the HDFS RPC monitoring to check whether the HDFS RPC usage increases abnormally when the SQL statement becomes slow. If yes, there is a high probability that the HDFS access is becoming slow.

  • Slow Yarn access

    View the HiveServer run log file /var/log/Bigdata/hive/hiveserver/hive.log, search for the thread log corresponding to the running SQL statement, and then search for the Kill Command log. If the new line generation in the log takes a long time, the access to YARN is slowing down.

  • Slow metadata access

    On FusionInsight Manager, choose Cluster > Services > Hive > Instances > Any MetaStore Instance > Chart. Select Operations from the Chart Category list. View Metadata Operation Latency of the create_table API and Execution of the add_partitions_req API to check whether the slow SQL execution is caused by slow MetaStore access.

Parent topic: Hive Troubleshooting