That is good to know. We are using Hive 0.9. Right now the biggest table contains 2 years data, and we partitioned by hour, as the data volume is big. So right now, it has 2*365*24 around 17000+ partitions. So far we didn't see too much problem yet, but I do have some concerns about it. We are using IBM BigInsight, which is using derby as the hive metastore, not as mysql as my most experience was on. Yong
From: norbert.bur...@gmail.com Date: Thu, 27 Feb 2014 07:57:05 -0500 Subject: Re: Metastore performance on HDFS-backed table with 15000+ partitions To: user@hive.apache.org Thanks everyone for the feedback. Just to follow up in case someone else runs into this: I can confirm that local client works around the OOMEs, but it's still very slow. It does seem like we were hitting some combination of HIVE-4051 and HIVE-5158. We'll try reducing partition count first, and then switch to 0.12.0 if that doesn't improve things significantly. Fwiw - http://www.slideshare.net/oom65/optimize-hivequeriespptx also has has some good rules-of-thumb. Norbert On Sat, Feb 22, 2014 at 1:27 PM, Stephen Sprague <sprag...@gmail.com> wrote: yeah. That traceback pretty much spells it out - its metastore related and that's where the partitions are stored. I'm with the others on this. HiveServer2 is still a little jankey on memory management. I bounce mine once a day at midnight just to play it safe (and because i can.) Again, for me, i use the hive local client for production jobs and remote client for adhoc stuff. you may wish to confirm the local hive client has no problem with your query. other than that you either increase your heap size on the HS2 process and hope for the best and/or file a bug report. bottom line hiveserver2 isn't production bullet proof just yet, IMHO. Others may disagree. Regards, Stephen. On Sat, Feb 22, 2014 at 9:50 AM, Norbert Burger <norbert.bur...@gmail.com> wrote: Thanks all for the quick feedback. I'm a bit surprised to learn 15k is considered too much, but we can work around it. I guess I'm also curious why the query planner needs to know about all partitions even in the case of simple select/limit queries, where the query might target only a single partition. Here's the client-side OOME with HADOOP_HEAPSIZE=2048: https://gist.githubusercontent.com/nburger/3286d2052060e2efe161/raw/dc30231086803c1d33b9137b5844d2d0e20e350d/gistfile1.txt This was from a CDH4.3.0 client hitting HIveServer2. Any idea what's consuming the heap? Norbert On Sat, Feb 22, 2014 at 10:32 AM, Edward Capriolo <edlinuxg...@gmail.com> wrote: Dont make tbales with that many partitions. It is an anti pattern. I hwve tables with 2000 partitions a day and that is rewlly to many. Hive needs go load that informqtion into memory to plan the query. On Saturday, February 22, 2014, Terje Marthinussen <tmarthinus...@gmail.com> wrote: > Query optimizer in hive is awful on memory consumption. 15k partitions sounds > a bit early for it to fail though.. > > What is your heap size? > > Regards, > Terje > >> On 22 Feb 2014, at 12:05, Norbert Burger <norbert.bur...@gmail.com> wrote: >> >> Hi folks, >> >> We are running CDH 4.3.0 Hive (0.10.0+121) with a MySQL metastore. >> >> In Hive, we have an external table backed by HDFS which has a 3-level >> partitioning scheme that currently has 15000+ partitions. >> >> Within the last day or so, queries against this table have started failing. >> A simple query which shouldn't take very long at all (select * from ... >> limit 10) fails after several minutes with a client OOME. I get the same >> outcome on count(*) queries (which I thought wouldn't send any data back to >> the client). Increasing heap on both client and server JVMs (via >> HADOOP_HEAPSIZE) doesn't have any impact. >> >> We were only able to work around the client OOMEs by reducing the number of >> partitions in the table. >> >> Looking at the MySQL querylog, my thought is that the Hive client is quite >> busy making requests for partitions that doesn't contribute to the query. >> Has anyone else had similar experience against tables this size? >> >> Thanks, >> Norbert > -- Sorry this was sent from mobile. Will do less grammar and spell check than usual.
