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.
- 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.
- Method 1:
- 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.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot