Hive
  1. Hive
  2. HIVE-6157

Fetching column stats slower than the 101 during rush hour

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 0.13.0
    • Fix Version/s: 0.13.0
    • Component/s: None
    • Labels:
      None

      Description

      "hive.stats.fetch.column.stats" controls whether the column stats for a table are fetched during explain (in Tez: during query planning). On my setup (1 table 4000 partitions, 24 columns) the time spent in semantic analyze goes from ~1 second to ~66 seconds when turning the flag on. 65 seconds spent fetching column stats...

      The reason is probably that the APIs force you to make separate metastore calls for each column in each partition. That's probably the first thing that has to change. The question is if in addition to that we need to cache this in the client or store the stats as a single blob in the database to further cut down on the time. However, the way it stands right now column stats seem unusable.

      1. HIVE-6157.prelim.patch
        887 kB
        Sergey Shelukhin
      2. HIVE-6157.nogen.patch
        156 kB
        Sergey Shelukhin
      3. HIVE-6157.01.patch
        886 kB
        Sergey Shelukhin
      4. HIVE-6157.01.patch
        886 kB
        Sergey Shelukhin
      5. HIVE-6157.nogen.patch
        162 kB
        Sergey Shelukhin
      6. HIVE-6157.03.patch
        892 kB
        Sergey Shelukhin
      7. HIVE-6157.03.patch
        892 kB
        Sergey Shelukhin

        Issue Links

          Activity

          Gunther Hagleitner created issue -
          Sergey Shelukhin made changes -
          Field Original Value New Value
          Assignee Sergey Shelukhin [ sershe ]
          Hide
          Sergey Shelukhin added a comment -

          Ok, this took rather longer than expected... initially I tried to make stat fetching part of partition pruning, this can be added as an extra optimization if necessary as this requires too many API changes all over the place.
          The alternative is simple, getting stat calls are all batched. New APIs on thrift use req/resp pattern; requests contain db, table, column list, and partition list (for partitions). The request returns whatever it can find (rather than the full list with some nulls, like the old APIs that built lists using individual calls to metastore). The code then uses this.
          On metastore there's both JDO and SQL path for speed.
          Also, cleaned up some stuff in StatOptimizer and StatsUtil that was generally suboptimal.

          Show
          Sergey Shelukhin added a comment - Ok, this took rather longer than expected... initially I tried to make stat fetching part of partition pruning, this can be added as an extra optimization if necessary as this requires too many API changes all over the place. The alternative is simple, getting stat calls are all batched. New APIs on thrift use req/resp pattern; requests contain db, table, column list, and partition list (for partitions). The request returns whatever it can find (rather than the full list with some nulls, like the old APIs that built lists using individual calls to metastore). The code then uses this. On metastore there's both JDO and SQL path for speed. Also, cleaned up some stuff in StatOptimizer and StatsUtil that was generally suboptimal.
          Hide
          Sergey Shelukhin added a comment -

          Patch coming today barring something surprising happens

          Show
          Sergey Shelukhin added a comment - Patch coming today barring something surprising happens
          Hide
          Sergey Shelukhin added a comment -

          Well, it looks like I cannot defeat datanucleus today... SQL path seems to work, although I didn't run all the tests. Let me comment out and check for now.

          Show
          Sergey Shelukhin added a comment - Well, it looks like I cannot defeat datanucleus today... SQL path seems to work, although I didn't run all the tests. Let me comment out and check for now.
          Sergey Shelukhin made changes -
          Attachment HIVE-6157.prelim.patch [ 12623775 ]
          Sergey Shelukhin made changes -
          Status Open [ 1 ] Patch Available [ 10002 ]
          Hide
          Shreepadma Venugopalan added a comment -

          Currently, the API fetches statistics for a given column. hive.stats.fetch.column.stats fetches stats for all columns for all partitions in all tables. Bad idea. HIVE-4301 was filed to support a bulk fetch API so that stats for all columns for all partitions in multiple tables can be fetched with a single call. Feel free to pick up HIVE-4301.

          Show
          Shreepadma Venugopalan added a comment - Currently, the API fetches statistics for a given column. hive.stats.fetch.column.stats fetches stats for all columns for all partitions in all tables. Bad idea. HIVE-4301 was filed to support a bulk fetch API so that stats for all columns for all partitions in multiple tables can be fetched with a single call. Feel free to pick up HIVE-4301 .
          Sergey Shelukhin made changes -
          Link This issue relates to HIVE-4301 [ HIVE-4301 ]
          Hide
          Sergey Shelukhin added a comment -

          Sorry, was not aware of that JIRA. Among other things, this patch adds bulk APIs. They do not support multiple tables as of now, though. Stats are currently fetched on the level of one column (stat optimizer) or one table (table scan stuff), so making use of multi-table API would require more extensive changes on the client (optimizer) side.

          Show
          Sergey Shelukhin added a comment - Sorry, was not aware of that JIRA. Among other things, this patch adds bulk APIs. They do not support multiple tables as of now, though. Stats are currently fetched on the level of one column (stat optimizer) or one table (table scan stuff), so making use of multi-table API would require more extensive changes on the client (optimizer) side.
          Hide
          Sergey Shelukhin added a comment -

          first patch. There's one TODO# left where I think some validation code is dead, need to see if any tests fail with it.

          Other than that many tests I ran passed, let's see what HiveQA says

          Show
          Sergey Shelukhin added a comment - first patch. There's one TODO# left where I think some validation code is dead, need to see if any tests fail with it. Other than that many tests I ran passed, let's see what HiveQA says
          Sergey Shelukhin made changes -
          Attachment HIVE-6157.nogen.patch [ 12624224 ]
          Attachment HIVE-6157.01.patch [ 12624225 ]
          Hide
          Sergey Shelukhin added a comment -

          nogen patch omits generated code and is posted to https://reviews.apache.org/r/17162/

          Show
          Sergey Shelukhin added a comment - nogen patch omits generated code and is posted to https://reviews.apache.org/r/17162/
          Hide
          Sergey Shelukhin added a comment -

          hmm, q.out file change may have not been a planned inclusion, let me dbl check

          Show
          Sergey Shelukhin added a comment - hmm, q.out file change may have not been a planned inclusion, let me dbl check
          Sergey Shelukhin made changes -
          Status Patch Available [ 10002 ] Open [ 1 ]
          Hide
          Sergey Shelukhin added a comment -

          HiveQA won't pick the patch; same file

          Show
          Sergey Shelukhin added a comment - HiveQA won't pick the patch; same file
          Sergey Shelukhin made changes -
          Attachment HIVE-6157.01.patch [ 12624267 ]
          Sergey Shelukhin made changes -
          Status Open [ 1 ] Patch Available [ 10002 ]
          Hide
          Hive QA added a comment -

          Overall: -1 at least one tests failed

          Here are the results of testing the latest attachment:
          https://issues.apache.org/jira/secure/attachment/12624267/HIVE-6157.01.patch

          ERROR: -1 due to 3 failed/errored test(s), 4943 tests executed
          Failed tests:

          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_alter_partition_coltype
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_annotate_stats_table
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_metadataonly1
          

          Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/980/testReport
          Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/980/console

          Messages:

          Executing org.apache.hive.ptest.execution.PrepPhase
          Executing org.apache.hive.ptest.execution.ExecutionPhase
          Executing org.apache.hive.ptest.execution.ReportingPhase
          Tests exited with: TestsFailedException: 3 tests failed
          

          This message is automatically generated.

          ATTACHMENT ID: 12624267

          Show
          Hive QA added a comment - Overall : -1 at least one tests failed Here are the results of testing the latest attachment: https://issues.apache.org/jira/secure/attachment/12624267/HIVE-6157.01.patch ERROR: -1 due to 3 failed/errored test(s), 4943 tests executed Failed tests: org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_alter_partition_coltype org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_annotate_stats_table org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_metadataonly1 Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/980/testReport Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/980/console Messages: Executing org.apache.hive.ptest.execution.PrepPhase Executing org.apache.hive.ptest.execution.ExecutionPhase Executing org.apache.hive.ptest.execution.ReportingPhase Tests exited with: TestsFailedException: 3 tests failed This message is automatically generated. ATTACHMENT ID: 12624267
          Hide
          Gunther Hagleitner added a comment -

          Patch looks good. Some questions/comments on RB. What about the test failures? They seem relevant.

          Show
          Gunther Hagleitner added a comment - Patch looks good. Some questions/comments on RB. What about the test failures? They seem relevant.
          Sergey Shelukhin made changes -
          Link This issue blocks HIVE-6289 [ HIVE-6289 ]
          Sergey Shelukhin made changes -
          Link This issue blocks HIVE-6292 [ HIVE-6292 ]
          Hide
          Sergey Shelukhin added a comment -

          RB feedback, also test fixes (02 fixed those but I couldn't add it to jira). I am running the tez test now

          Show
          Sergey Shelukhin added a comment - RB feedback, also test fixes (02 fixed those but I couldn't add it to jira). I am running the tez test now
          Sergey Shelukhin made changes -
          Attachment HIVE-6157.nogen.patch [ 12624887 ]
          Attachment HIVE-6157.03.patch [ 12624888 ]
          Hide
          Sergey Shelukhin added a comment -

          metadata_only_queries.q.out got modified for tez test, but it appears that the changes come from unrelated patches. Because it doesn't run in HiveQA it didn't get updated at some point. Not including in this patch... the rest passed

          Show
          Sergey Shelukhin added a comment - metadata_only_queries.q.out got modified for tez test, but it appears that the changes come from unrelated patches. Because it doesn't run in HiveQA it didn't get updated at some point. Not including in this patch... the rest passed
          Hide
          Gunther Hagleitner added a comment -

          I have HIVE-6261 open for the metadata_only_queries

          Show
          Gunther Hagleitner added a comment - I have HIVE-6261 open for the metadata_only_queries
          Sergey Shelukhin made changes -
          Status Patch Available [ 10002 ] Open [ 1 ]
          Hide
          Sergey Shelukhin added a comment -

          exact same patch, HiveQA won't run

          Show
          Sergey Shelukhin added a comment - exact same patch, HiveQA won't run
          Sergey Shelukhin made changes -
          Attachment HIVE-6157.03.patch [ 12625080 ]
          Sergey Shelukhin made changes -
          Status Open [ 1 ] Patch Available [ 10002 ]
          Hide
          Gunther Hagleitner added a comment -

          Prasanth Jayachandran do you want to also take a look? This would effect the stats annotation too.

          Show
          Gunther Hagleitner added a comment - Prasanth Jayachandran do you want to also take a look? This would effect the stats annotation too.
          Hide
          Hive QA added a comment -

          Overall: -1 at least one tests failed

          Here are the results of testing the latest attachment:
          https://issues.apache.org/jira/secure/attachment/12625080/HIVE-6157.03.patch

          ERROR: -1 due to 6 failed/errored test(s), 4958 tests executed
          Failed tests:

          org.apache.hadoop.hive.cli.TestMinimrCliDriver.testCliDriver_bucket_num_reducers
          org.apache.hadoop.hive.cli.TestMinimrCliDriver.testCliDriver_bucketmapjoin6
          org.apache.hadoop.hive.cli.TestMinimrCliDriver.testCliDriver_import_exported_table
          org.apache.hadoop.hive.cli.TestMinimrCliDriver.testCliDriver_infer_bucket_sort_reducers_power_two
          org.apache.hadoop.hive.cli.TestMinimrCliDriver.testCliDriver_load_hdfs_file_with_space_in_the_name
          org.apache.hadoop.hive.cli.TestNegativeMinimrCliDriver.testNegativeCliDriver_file_with_header_footer_negative
          

          Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/1010/testReport
          Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/1010/console

          Messages:

          Executing org.apache.hive.ptest.execution.PrepPhase
          Executing org.apache.hive.ptest.execution.ExecutionPhase
          Executing org.apache.hive.ptest.execution.ReportingPhase
          Tests exited with: TestsFailedException: 6 tests failed
          

          This message is automatically generated.

          ATTACHMENT ID: 12625080

          Show
          Hive QA added a comment - Overall : -1 at least one tests failed Here are the results of testing the latest attachment: https://issues.apache.org/jira/secure/attachment/12625080/HIVE-6157.03.patch ERROR: -1 due to 6 failed/errored test(s), 4958 tests executed Failed tests: org.apache.hadoop.hive.cli.TestMinimrCliDriver.testCliDriver_bucket_num_reducers org.apache.hadoop.hive.cli.TestMinimrCliDriver.testCliDriver_bucketmapjoin6 org.apache.hadoop.hive.cli.TestMinimrCliDriver.testCliDriver_import_exported_table org.apache.hadoop.hive.cli.TestMinimrCliDriver.testCliDriver_infer_bucket_sort_reducers_power_two org.apache.hadoop.hive.cli.TestMinimrCliDriver.testCliDriver_load_hdfs_file_with_space_in_the_name org.apache.hadoop.hive.cli.TestNegativeMinimrCliDriver.testNegativeCliDriver_file_with_header_footer_negative Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/1010/testReport Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/1010/console Messages: Executing org.apache.hive.ptest.execution.PrepPhase Executing org.apache.hive.ptest.execution.ExecutionPhase Executing org.apache.hive.ptest.execution.ReportingPhase Tests exited with: TestsFailedException: 6 tests failed This message is automatically generated. ATTACHMENT ID: 12625080
          Hide
          Prasanth Jayachandran added a comment -

          Mostly looks good. Left minor comments on RB.

          Show
          Prasanth Jayachandran added a comment - Mostly looks good. Left minor comments on RB.
          Hide
          Sergey Shelukhin added a comment -

          Some of the test failures are also happening in other jiras and some I cannot repro... let me address recent RB feedback and rerun QA

          Show
          Sergey Shelukhin added a comment - Some of the test failures are also happening in other jiras and some I cannot repro... let me address recent RB feedback and rerun QA
          Hide
          Gunther Hagleitner added a comment -

          LGTM +1

          Show
          Gunther Hagleitner added a comment - LGTM +1
          Hide
          Sergey Shelukhin added a comment -

          on some other jira with harmless patch all but one of the same tests failed so I assume they are all broken. I'll take a look at bucketmapjoin6

          Show
          Sergey Shelukhin added a comment - on some other jira with harmless patch all but one of the same tests failed so I assume they are all broken. I'll take a look at bucketmapjoin6
          Sergey Shelukhin made changes -
          Status Patch Available [ 10002 ] Open [ 1 ]
          Hide
          Sergey Shelukhin added a comment -

          cannot repro on multiple machines and I see bucketmapjoin6 also fails in some jiras... probably flaky. Will commit tomorrow.

          Show
          Sergey Shelukhin added a comment - cannot repro on multiple machines and I see bucketmapjoin6 also fails in some jiras... probably flaky. Will commit tomorrow.
          Sergey Shelukhin made changes -
          Status Open [ 1 ] Patch Available [ 10002 ]
          Hide
          Gunther Hagleitner added a comment -

          Committed to trunk. Thanks Sergey!

          Show
          Gunther Hagleitner added a comment - Committed to trunk. Thanks Sergey!
          Gunther Hagleitner made changes -
          Status Patch Available [ 10002 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          Sergey Shelukhin made changes -
          Fix Version/s 0.13.0 [ 12324986 ]
          Ashutosh Chauhan made changes -
          Link This issue supercedes HIVE-4301 [ HIVE-4301 ]
          Transition Time In Source Status Execution Times Last Executer Last Execution Date
          Patch Available Patch Available Open Open
          10d 15h 7m 3 Sergey Shelukhin 28/Jan/14 22:36
          Open Open Patch Available Patch Available
          10d 11h 49m 4 Sergey Shelukhin 28/Jan/14 22:43
          Patch Available Patch Available Resolved Resolved
          1d 1h 41m 1 Gunther Hagleitner 30/Jan/14 00:24

            People

            • Assignee:
              Sergey Shelukhin
              Reporter:
              Gunther Hagleitner
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development