Thanks for the info. I checked Hive3.1.3, and there will have performance issues when HS2 invoking method get_materialized_views_for_rewritin. You can refer to this ticket https://issues.apache.org/jira/browse/HIVE-21631 which was fixed in Hive4.
And if you do not need mv ability, here is a workaroud to fix this issue is that you can turn off mv rewriting by setting hive.materializedview.rewriting=false in hive-site.xml. Or you can port the change HIVE-21631 into your Hive3.1.3 source code. BTW, you can also try Hive4 version to enjoy more interesting features, including Apache Iceberg integration, enhanced materialized view, etc. Thanks, Butao Zhang ---- Replied Message ---- | From | Eugene Miretsky<eug...@badal.io> | | Date | 11/17/2023 09:06 | | To | <user@hive.apache.org> | | Subject | Re: [EXTERNAL] Re: Slow Hive query with a lot of 'get_materialized_views_for_rewriting' | Hey! Hive version is 3.1.3 On Wed, Nov 15, 2023 at 9:23 PM Butao Zhang <butaozha...@163.com> wrote: Hi, which version of hms are you using now? I have checked the master branch and beta-1 branch source code, but I can't find the place where this method get_materialized_views_for_rewriting is called by mistake. Thanks, Butao Zhang ---- Replied Message ---- | From | Eugene Miretsky<eug...@badal.io> | | Date | 11/16/2023 02:21 | | To | <user@hive.apache.org> | | Subject | Slow Hive query with a lot of 'get_materialized_views_for_rewriting' | Hey! We have a catalog with fairly a lot of databases and tables. Where we do a simple query (select * from table limit 5;) on an ideal cluster, it takes around 20seconds, sometimes longer (usually first run takes 40s+) Looking at the hive-metastore logs during most of the query time the logs show "metastore.Hivemetastore: 13: get_materialized_views_for_rewriting: db = <Database name>" for each database. When these calls finish, the query executes pretty quickly. My interpretation of this is that most of the time is spent on analyzing the metastore and building a query plan, perhaps some sort of Metastore in-memory cache is building built (but it happens on every call). But I am not really sure how to debug it further, nor could find in the code where this is happening. Any advice on what's causing it or how to troubleshoot? p.s The metadata (i.e original tables and databases) is actually coming from a very old Hive version (1.1), and we migrated it to the newest version of the Metastore using the upgrade tool. In the original Hive version materialized views were not even supported. Cheers, Eugene | | | | | -- | | |
