[HIVE-28509] Hive component Mysql metadata database exception

• Type: Bug
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Affects Version/s: 3.1.0
• Component/s: Database/Schema
• Flags: Important
• Assignee: Unassigned
• Reporter: hanlei
• Due: 27/Sep/24
• Created: 09/Sep/24 08:18
• Updated: 12/Nov/24 03:38

Description

we found  that the Hive metastore was encountering connection exceptions, leading to slow Hive job execution. Furthermore, the MySQL workload showed unusual activity, with a 1/3 decrease in QPS. The CPU load exceeded 3100%.

The slow log in the MySQL backend is primarily for the following two types of statements
Creating sort index
SELECT 'AO'.'PART_NAME','A0'.'PART_NAME' AS 'NUCORDER0' FROM 'PARTITIONS' A0 LEFT OUTER JOIN 'TBLS' 'B0' ON 'A0'.'TBL ID' = 'B0'.'TBL_ID' LEFT OUTER JOIN 'DBS' 'C0' ON 'B0'.'DB_ID' = 'C0'.'DB_ID' WHERE 'C0'.'NAME' = 'XXXXX' AND 'B0'.'TBL_NAME' = 'XXXXXXXXXXXXXXXX' AND 'C0'.'CTLG_NAME' = 'XXXXXXX' ORDER BY 'NUCORDER0'

Sending data
SELECT 'A0'.'TBL_NAME' FROM 'TBLS' 'A0' LEFT OUTER JOIN 'DBS' 'B0' ON 'A0'.'DB_ID'='B0'.'DB_ID' WHERE 'B0'.'NAME' = 'XXXXXXX' AND 'B0'.'CTLG_NAME' = 'XXXXXX' AND 'A0'.'TABLE_TYPE' = 'MATERIALIZED_VIEW' AND 'A0'.'IS_REWRITE_ENABLED' = 1;