[
https://issues.apache.org/jira/browse/HIVE-4051?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Phabricator updated HIVE-4051:
------------------------------
Attachment: HIVE-4051.D11805.3.patch
sershe updated the revision "HIVE-4051 [jira] Hive's metastore suffers from 1+N
queries when querying partitions & is slow".
Addressed Phabricator comments, fixed minor bugs (e.g. null checks,
setTableName called instead of setDbName), added column schemas that ended up
being needed after all, cleaned up the code a bit, added some short circuiting,
added order to tests that had undefined order and so depended on the order in
which partitions are returned (ORM code returns them by name, SQL by ID).
Added some short-circuiting to the queries/getting stuff.
I compared the reflection-based dump of SQL- and ORM- based objects from some
tests (code not included) and they are the same.
The existing tests seem to adequately cover this code. The only concern is
that if it fails it's impossible to see as it falls back to ORM...
Reviewers: JIRA
REVISION DETAIL
https://reviews.facebook.net/D11805
CHANGE SINCE LAST DIFF
https://reviews.facebook.net/D11805?vs=36357&id=36615#toc
AFFECTED FILES
build.xml
common/src/java/org/apache/hadoop/hive/conf/HiveConf.java
metastore/src/java/org/apache/hadoop/hive/metastore/ObjectStore.java
metastore/src/java/org/apache/hadoop/hive/metastore/parser/ExpressionTree.java
ql/src/java/org/apache/hadoop/hive/ql/metadata/Hive.java
ql/src/test/queries/clientpositive/alter_partition_coltype.q
ql/src/test/queries/clientpositive/load_dyn_part3.q
ql/src/test/queries/clientpositive/load_dyn_part4.q
ql/src/test/queries/clientpositive/load_dyn_part9.q
ql/src/test/queries/clientpositive/ppr_pushdown2.q
ql/src/test/queries/clientpositive/stats4.q
ql/src/test/results/clientpositive/load_dyn_part3.q.out
ql/src/test/results/clientpositive/load_dyn_part4.q.out
ql/src/test/results/clientpositive/load_dyn_part9.q.out
ql/src/test/results/clientpositive/ppr_pushdown2.q.out
ql/src/test/results/clientpositive/stats4.q.out
To: JIRA, sershe
Cc: brock
> 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
> Assignee: Sergey Shelukhin
> Attachments: HIVE-4051.D11805.1.patch, HIVE-4051.D11805.2.patch,
> HIVE-4051.D11805.3.patch
>
>
> 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
