Marta Kuczora created HIVE-22271:
------------------------------------
Summary: Create index on the TBL_COL_PRIVS table for the columns
COLUMN_NAME, PRINCIPAL_NAME, PRINCIPAL_TYPE and TBL_ID
Key: HIVE-22271
URL: https://issues.apache.org/jira/browse/HIVE-22271
Project: Hive
Issue Type: Bug
Components: Metastore
Reporter: Marta Kuczora
In one of the escalations for HDP-3.1.0 we found that the table privilege
checks could be very slow and these checks could be speed up by defining an
INDEX on the TBL_COL_PRIVS table for the following columns:
COLUMN_NAME,PRINCIPAL_NAME,PRINCIPAL_TYPE,TBL_ID
In the MYSQL slow query log, we found that the following query is executed
slowly:
{noformat}
SELECT DISTINCT 'org.apache.hadoop.hive.metastore.model.MTableColumnPrivilege'
AS
`NUCLEUS_TYPE`,`A0`.`AUTHORIZER`,`A0`.`COLUMN_NAME`,`A0`.`CREATE_TIME`,`A0`.`GRANT_OPTION`,`A0`.`GRANTOR`,`A0`.`GRANTOR_TYPE`,`A0`.`PRINCIPAL_NAME`,`A0`.`PRINCIPAL_TYPE`,`A0`.`TBL_COL_PRIV`,`A0`.`TBL_COLUMN_GRANT_ID`
FROM `TBL_COL_PRIVS` `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
`A0`.`PRINCIPAL_NAME` = 'xxx' AND `A0`.`PRINCIPAL_TYPE` = 'GROUP' AND
`B0`.`TBL_NAME` = 'xxxx' AND `C0`.`NAME` = 'xxx' AND `C0`.`CTLG_NAME` = 'xxx'
AND `A0`.`COLUMN_NAME` = 'xxx'
{noformat}
When checked the explain plan of the this query, it could be seen that the
index defined on the TBL_COL_PRIVS table is not used. In the slow query, the
COLUMN_NAME, PRINCIPAL_NAME, PRINCIPAL_TYPE and TBL_ID columns were used, and
after creating an index on these columns only, we saw significant performance
improvement.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
