Gopal V created HIVE-4051:
-----------------------------
Summary: Hive's metastore suffers from 1+N queries when querying
partitions & is slow
Key: HIVE-4051
URL: https://issues.apache.org/jira/browse/HIVE-4051
Project: Hive
Issue Type: Bug
Components: Clients, Metastore
Environment: RHEL 6.3 / EC2 C1.XL
Reporter: Gopal V
Hive's query client takes a long time to initialize & start planning queries
because of delays in creating all the MTable/MPartition objects.
For a hive db with 1800 partitions, the metastore took 6-7 seconds to
initialize - firing approximately 5900 queries to the mysql database.
Several of those queries fetch exactly one row to create a single object on the
client.
The following 12 queries were repeated for each partition, generating a storm
of SQL queries
{code}
4 Query SELECT
`A0`.`SD_ID`,`B0`.`INPUT_FORMAT`,`B0`.`IS_COMPRESSED`,`B0`.`IS_STOREDASSUBDIRECTORIES`,`B0`.`LOCATION`,`B0`.`NUM_BUCKETS`,`B0`.`OUTPUT_FORMAT`,`B0`.`SD_ID`
FROM `PARTITIONS` `A0` LEFT OUTER JOIN `SDS` `B0` ON `A0`.`SD_ID` =
`B0`.`SD_ID` WHERE `A0`.`PART_ID` = 3945
4 Query SELECT `A0`.`CD_ID`,`B0`.`CD_ID` FROM `SDS` `A0` LEFT OUTER JOIN
`CDS` `B0` ON `A0`.`CD_ID` = `B0`.`CD_ID` WHERE `A0`.`SD_ID` =4871
4 Query SELECT COUNT(*) FROM `COLUMNS_V2` THIS WHERE THIS.`CD_ID`=1546 AND
THIS.`INTEGER_IDX`>=0
4 Query SELECT
`A0`.`COMMENT`,`A0`.`COLUMN_NAME`,`A0`.`TYPE_NAME`,`A0`.`INTEGER_IDX` AS
NUCORDER0 FROM `COLUMNS_V2` `A0` WHERE `A0`.`CD_ID` = 1546 AND
`A0`.`INTEGER_IDX` >= 0 ORDER BY NUCORDER0
4 Query SELECT `A0`.`SERDE_ID`,`B0`.`NAME`,`B0`.`SLIB`,`B0`.`SERDE_ID` FROM
`SDS` `A0` LEFT OUTER JOIN `SERDES` `B0` ON `A0`.`SERDE_ID` = `B0`.`SERDE_ID`
WHERE `A0`.`SD_ID` =4871
4 Query SELECT COUNT(*) FROM `SORT_COLS` THIS WHERE THIS.`SD_ID`=4871 AND
THIS.`INTEGER_IDX`>=0
4 Query SELECT `A0`.`COLUMN_NAME`,`A0`.`ORDER`,`A0`.`INTEGER_IDX` AS
NUCORDER0 FROM `SORT_COLS` `A0` WHERE `A0`.`SD_ID` =4871 AND `A0`.`INTEGER_IDX`
>= 0 ORDER BY NUCORDER0
4 Query SELECT COUNT(*) FROM `SKEWED_VALUES` THIS WHERE
THIS.`SD_ID_OID`=4871 AND THIS.`INTEGER_IDX`>=0
4 Query SELECT 'org.apache.hadoop.hive.metastore.model.MStringList' AS
NUCLEUS_TYPE,`A1`.`STRING_LIST_ID`,`A0`.`INTEGER_IDX` AS NUCORDER0 FROM
`SKEWED_VALUES` `A0` INNER JOIN `SKEWED_STRING_LIST` `A1` ON
`A0`.`STRING_LIST_ID_EID` = `A1`.`STRING_LIST_ID` WHERE `A0`.`SD_ID_OID` =4871
AND `A0`.`INTEGER_IDX` >= 0 ORDER BY NUCORDER0
4 Query SELECT COUNT(*) FROM `SKEWED_COL_VALUE_LOC_MAP` WHERE `SD_ID` =4871
AND `STRING_LIST_ID_KID` IS NOT NULL
4 Query SELECT 'org.apache.hadoop.hive.metastore.model.MStringList' AS
NUCLEUS_TYPE,`A0`.`STRING_LIST_ID` FROM `SKEWED_STRING_LIST` `A0` INNER JOIN
`SKEWED_COL_VALUE_LOC_MAP` `B0` ON `A0`.`STRING_LIST_ID` =
`B0`.`STRING_LIST_ID_KID` WHERE `B0`.`SD_ID` =4871
4 Query SELECT `A0`.`STRING_LIST_ID_KID`,`A0`.`LOCATION` FROM
`SKEWED_COL_VALUE_LOC_MAP` `A0` WHERE `A0`.`SD_ID` =4871 AND NOT
(`A0`.`STRING_LIST_ID_KID` IS NULL)
{code}
This data is not detached or cached, so this operation is performed during
every query plan for the partitions, even in the same hive client.
The queries are automatically generated by JDO/DataNucleus which makes it
nearly impossible to rewrite it into a single denormalized join operation &
process it locally.
Attempts to optimize this with JDO fetch-groups did not bear fruit in improving
the query count.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
