hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sergey Shelukhin (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (HIVE-4051) Hive's metastore suffers from 1+N queries when querying partitions & is slow
Date Sat, 20 Jul 2013 00:58:48 GMT

    [ https://issues.apache.org/jira/browse/HIVE-4051?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13714281#comment-13714281
] 

Sergey Shelukhin commented on HIVE-4051:
----------------------------------------

I am actually getting 16 queries... 3 by PART_ID (itself, values, params), 10 per its SD_ID,
2 for SD_ID_OID (might also be SD_ID?), and 1 for SERDE.
Some normalization doesn't make any sense, e.g.
{code}
mysql> select name, slib, count(1) from SERDES;
+------+----------------------------------------------------+----------+
| name | slib                                               | count(1) |
+------+----------------------------------------------------+----------+
| NULL | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe |    32769 |
+------+----------------------------------------------------+----------+
{code}
if stored for each row 1-on-1 anyway, why not just store it in the row?

I am looking in the direction of doing a full-string query returning a bag of fields, rather
than MPartition, so that per-object calls are not done. Then SDs can be fetched with join,
etc.
One thing that is not quite clear is how to do fetch from separate tables for which DN object
doesn't exist e.g. PARTITION_KEY_VALS. Ideally we would fetch all of these for all necessary
partitions in one query and put them into objects.

If all else fails we can use SQL http://www.datanucleus.org/products/datanucleus/jdo/sql.html,
but it will halfway defeat the purpose of having DN in the first place, since it becomes RDBMS-dependent.

In fact to avoid warring with datanucleus perhaps we could do SQL as storage-specific optimization,
configurable for metastore, and fall back to ORM if disabled.

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