Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-4051

Hive's metastore suffers from 1+N queries when querying partitions & is slow

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 0.12.0
    • Component/s: Clients, Metastore
    • Labels:
      None
    • Environment:

      RHEL 6.3 / EC2 C1.XL

      Description

      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.

        Attachments

        1. HIVE-4051.D11805.9.patch
          51 kB
          Phabricator
        2. HIVE-4051.D11805.8.patch
          51 kB
          Phabricator
        3. HIVE-4051.D11805.7.patch
          43 kB
          Phabricator
        4. HIVE-4051.D11805.6.patch
          40 kB
          Phabricator
        5. HIVE-4051.D11805.5.patch
          182 kB
          Phabricator
        6. HIVE-4051.D11805.4.patch
          181 kB
          Phabricator
        7. HIVE-4051.D11805.3.patch
          176 kB
          Phabricator
        8. HIVE-4051.D11805.2.patch
          37 kB
          Phabricator
        9. HIVE-4051.D11805.1.patch
          36 kB
          Phabricator

          Issue Links

            Activity

              People

              • Assignee:
                sershe Sergey Shelukhin
                Reporter:
                gopalv Gopal V
              • Votes:
                3 Vote for this issue
                Watchers:
                15 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: