hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Phabricator (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (HIVE-4051) Hive's metastore suffers from 1+N queries when querying partitions & is slow
Date Wed, 24 Jul 2013 23:53:51 GMT

     [ 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.1.patch

sershe requested code review of "HIVE-4051 [jira] Hive's metastore suffers from 1+N queries
when querying partitions & is slow".

Reviewers: JIRA

Preliminary patch for HIVE-4051 (ready for review).
This introduces "direct SQL" optimization for metastore.
When memstore gets partitions by filter or by names, it does so using ANSI SQL92 queries,
if enabled (default).
This will not work on non-RDBMS datastores, may not work on non-ANSI-compiant ones (I tested
on mysql in default mode).
If disabled, or if any error happens, it will use the default DataNucleus code.
When it does work, which is most practical cases, with local mysql it produces up to x20 speedup
for getting large number of partitions.
SQL queries can be further optimized to achieve even more, presumably - see some comments
in the code. That will be done as separate JIRAs.

So far the patch has no tests, because it is peculiar to the storage engine, so I need to
look if derby test makes sense.
Another question is whether it makes sense to put SQL antics into separate class.
Let me run HIVE QA (some test have run on local). After discussion I will update the patch,
but the core is ready for review.


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

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)

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.

TEST PLAN
  EMPTY

REVISION DETAIL
  https://reviews.facebook.net/D11805

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

MANAGE HERALD RULES
  https://reviews.facebook.net/herald/view/differential/

WHY DID I GET THIS EMAIL?
  https://reviews.facebook.net/herald/transcript/28047/

To: JIRA, sershe

                
> 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'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

Mime
View raw message