[
https://issues.apache.org/jira/browse/HIVE-4051?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13728403#comment-13728403
]
Sergey Shelukhin commented on HIVE-4051:
----------------------------------------
Some perf results (with a patch I am about to update).
I am running metastore separately on the same box as MySQL box in a cluster,
with default config.
I've created a table as such:
{code}
create table sqltest(c string) partitioned by (s string,n string,p int);
{code}
and inserted 32k partitions with s being 32-character random string, n - a
number between 0 and 14, and p also a number.
There's no data in the table.
I am running a query that would select 256 partitions with filter pushdown from
hive CLI is on a different box
The time spent for creating partition objects out of 32k is about 150-180ms on
current database schema. Bulk of it (~100ms) is in the query that does the
filtering by joining PARTITION_KEY_VALS.
If this is done beforehand: "create index idx1 on PARTITION_KEY_VALS
(PART_KEY_VAL);", the total server-side fetch time becomes about 50ms, ~40-45ms
for assorted queries and ~5ms for java code.
On empty dataset, the total time to run the query (no job as there's no data)
is about 150ms in the latter case.
Trunk with filter pushdown takes the total of 1.7-2 seconds in this case; with
more partitions fetched the difference is proportional.
Results are similar if both are run w/o pushdown (both are slower ofc).
As mentioned above, I will move code into its own class in separate "last"
iteration w/o logic changes in order to not complicate the review.
Tuning MySQL/other databases, and queries, for further perf, can be in separate
JIRA(s). The Hive admin/DBA could even do the former in the meantime if
absolutely necessary.
[~cws] What do you think about the more conservative fallback scheme proposed
above? (not in the patch yet)
I can test on Postgres if desired but I don't think I have access to Oracle.
> 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-4051.D11805.4.patch, HIVE-4051.D11805.5.patch,
> HIVE-4051.D11805.6.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
