Yes, I've reverted to the default there. Thanks! On Mon, May 2, 2016 at 7:47 PM, Prasanth Jayachandran < pjayachand...@hortonworks.com> wrote:
> Hi Matt > > As Gopal mentioned below you might have to unset > hive.exec.orc.memory.pool=1.0; > since some memory is required for sorting. Try running with the defaults > for hive.exec.orc.memory.pool. > > Thanks > Prasanth > > On May 2, 2016, at 9:41 PM, Matt Olson <maolso...@gmail.com> wrote: > > Hi Prasanth, > > Thank you for the helpful information. I have been using the default ORC > stripe size, which I believe is 67,108,864 bytes. > > I was able to remove the constant value for dt as you suggested, and set > hive.optimize.sort.dynamic.partition=true. I saw in the new explain plan > that the partitions are now being sorted, and rather than a map-only job I > now have a map-reduce job as expected. > > For some reason, though, I'm still getting the same error as before. The > logs say that the map is 100% complete and the reduce is 99% complete, and > then it fails with > > Container > [pid=8962,containerID=container_e26_1460661845156_55073_01_005311] is > running beyond physical memory limits. Current usage: 2.0 GB of 2 GB > physical memory used; 2.7 GB of 4.2 GB virtual memory used. Killing > container. > > This seems strange since there should be plenty of memory for 30 * 5 * 256 > KB, which is only about 38 MB. I have tried reducing the compression buffer > size from 256 KB to 16 KB and even 1 KB by setting "orc.compress.size" in > the dynamic_partitioned_table properties, but it still fails with the same > error. > > Thanks again for your explanations so far. I'll keep working on this and > let you know if I work it out. > > Matt > > On Mon, May 2, 2016 at 3:43 PM, Prasanth Jayachandran < > pjayachand...@hortonworks.com> wrote: > >> Hi Matt >> >> So it looks like you are hitting the issue that I had mentioned >> previously. >> You might need to apply the patch from HIVE-12893. Alternatively, if dt >> has only one possible value then >> its better to remove the constant value for dt and the where condition. >> This will enable sorted dynamic partition optimization which >> is more scalable when the number of combined partitioned count is huge. >> >> What is the stripe size that you are using? >> >> The reason why it is causing OOM for ORC is >> >> ORC needs to buffer the incoming rows in columnar way before writing it >> to the file. It buffers until configured >> stripe size is reached and the entire stripe gets flushed. This is >> usually not a problem when there few ORC writers. >> When there are multiple concurrent writers then the available memory is >> shared across all writers. In case of dynamic >> partitioning, there will 1 writer per partition and per bucket in each >> mapper/reducer. >> >> If there are 100 partition, 4 buckets, 25 columns then memory >> requirement will be >> 100 * 4 * 25 * 5 (approx. number of internal streams per column) * 256KB >> (compression buffer size). >> This can get really huge if the number of partition increases. The way >> around this memory requirement is to reduce >> the number of orc writers. hive.optimize.sort.dynamic.partition sort the >> data on partition column and bucket number >> so there will be only 1 writer per mapper/reducer reducing the memory >> requirement to 25 * 5 * 256Kb which is more >> manageable. If this value needs to be further reduce, reduce the >> compression buffer size. >> >> Because of the bug outlined in HIVE-12893, in your case the optimization >> to have single orc writer is not kicking in causing OOM. >> >> Thanks >> Prasanth >> >> On May 2, 2016, at 3:30 PM, Matt Olson <maolso...@gmail.com> wrote: >> >> Hi Prasanth, >> >> Here is the explain plan for the insert query: >> >> OK >> STAGE DEPENDENCIES: >> Stage-1 is a root stage >> Stage-7 depends on stages: Stage-1 , consists of Stage-4, Stage-3, >> Stage-5 >> Stage-4 >> Stage-0 depends on stages: Stage-4, Stage-3, Stage-6 >> Stage-2 depends on stages: Stage-0 >> Stage-3 >> Stage-5 >> Stage-6 depends on stages: Stage-5 >> >> STAGE PLANS: >> Stage: Stage-1 >> Map Reduce >> Map Operator Tree: >> TableScan >> alias: original_table >> Statistics: Num rows: 44962613 Data size: 264560040271 Basic >> stats: COMPLETE Column stats: NONE >> Select Operator >> expressions: ... >> outputColumnNames: _col0, _col1, _col2, _col3, _col4, >> _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, >> _col15, _col16, _col17, _col18, _col19, _col20, _col21, _col22, _col23, >> _col24, _col25, _col26, _col27 >> Statistics: Num rows: 44962613 Data size: 264560040271 >> Basic stats: COMPLETE Column stats: NONE >> File Output Operator >> compressed: true >> Statistics: Num rows: 44962613 Data size: 264560040271 >> Basic stats: COMPLETE Column stats: NONE >> table: >> input format: >> org.apache.hadoop.hive.ql.io.orc.OrcInputFormat >> output format: >> org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat >> serde: org.apache.hadoop.hive.ql.io.orc.OrcSerde >> name: pin.dynamic_partitioned_table >> >> Stage: Stage-7 >> Conditional Operator >> >> Stage: Stage-4 >> Move Operator >> files: >> hdfs directory: true >> destination: >> hdfs://ci-ocean/mnt/tmp/hive-molson/molson/9f6b1ce0-f71a-4c87-9440-77f09e3860eb/hive_2016-05-02_20-14-12_260_7512820923555713567-1/-ext-10000 >> >> Stage: Stage-0 >> Move Operator >> tables: >> partition: >> dt 2016-04-05 >> title_id >> title_id_type >> replace: true >> table: >> input format: >> org.apache.hadoop.hive.ql.io.orc.OrcInputFormat >> output format: >> org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat >> serde: org.apache.hadoop.hive.ql.io.orc.OrcSerde >> name: pin.dynamic_partitioned_table >> >> Stage: Stage-2 >> Stats-Aggr Operator >> >> Stage: Stage-3 >> Merge File Operator >> Map Operator Tree: >> ORC File Merge Operator >> merge level: stripe >> input format: org.apache.hadoop.hive.ql.io.orc.OrcInputFormat >> >> Stage: Stage-5 >> Merge File Operator >> Map Operator Tree: >> ORC File Merge Operator >> merge level: stripe >> input format: org.apache.hadoop.hive.ql.io.orc.OrcInputFormat >> >> Stage: Stage-6 >> Move Operator >> files: >> hdfs directory: true >> destination: >> hdfs://ci-ocean/mnt/tmp/hive-molson/molson/9f6b1ce0-f71a-4c87-9440-77f09e3860eb/hive_2016-05-02_20-14-12_260_7512820923555713567-1/-ext-10000 >> >> Thank you, >> Matt >> >> >> On Mon, May 2, 2016 at 12:48 PM, Prasanth Jayachandran < >> pjayachand...@hortonworks.com> wrote: >> >>> Hi >>> >>> Can you please post explain plan for your insert query? I suspect sorted >>> dynamic partition optimization is bailing out because of >>> the constant value for ‘dt' column. If you are not seeing a reducer then >>> its likely not using the sorted dynamic partition optimization. >>> You are probably hitting this bug >>> https://issues.apache.org/jira/browse/HIVE-12893 >>> I can confirm if thats the case by looking at the explain plan. >>> >>> Thanks >>> Prasanth >>> >>> On May 2, 2016, at 2:24 PM, Ryan Harris <ryan.har...@zionsbancorp.com> >>> wrote: >>> >>> reading this: >>> "but when I add 2000 new titles with 300 rows each" >>> I'm thinking that you are over-partitioning your data.... >>> I'm not sure exactly how that relates to the OOM error you are getting >>> (it may not)....I'd test things out partitioning by date-only.... maybe >>> date + title_type, but adding 2000+ dynamic partitions that each have 300 >>> rows of data in them is asking for problems in Hive IMO... >>> >>> >>> *From:* Matt Olson [mailto:maolso...@gmail.com <maolso...@gmail.com>] >>> *Sent:* Friday, April 29, 2016 7:50 PM >>> *To:* user@hive.apache.org >>> *Subject:* Container out of memory: ORC format with many dynamic >>> partitions >>> >>> Hi all, >>> >>> I am using Hive 1.0.1 and trying to do a simple insert into an ORC >>> table, creating dynamic partitions. I am selecting from a table partitioned >>> by dt and category, and inserting into a table partitioned by dt, title, >>> and title_type. Other than the partitioning, the tables have the same >>> schemas. Both title and title_type are fields in the first table, and when >>> I insert into the second table, I am using them to create dynamic >>> partitions. The .q file with the CREATE and INSERT statements is copied >>> below. >>> >>> SET hive.optimize.sort.dynamic.partition=true; >>> SET hive.exec.orc.memory.pool=1.0; >>> SET hive.exec.max.dynamic.partitions = 5000; >>> SET hive.exec.max.dynamic.partitions.pernode = 5000; >>> SET hive.merge.mapfiles = true; >>> SET mapred.min.split.size=134217728; >>> SET mapred.min.split.size.per.node=134217728; >>> SET mapred.min.split.size.per.rack=134217728; >>> SET mapred.output.compression.codec=com.hadoop.compression.lzo.LzoCodec; >>> SET >>> mapred.map.output.compression.codec=com.hadoop.compression.lzo.LzoCodec; >>> SET mapred.max.split.size=134217728; >>> SET hive.map.aggr.hash.percentmemory=0.125; >>> SET hive.exec.parallel=true; >>> SET hive.exec.compress.intermediate=true; >>> SET hive.exec.compress.output=true; >>> SET mapred.map.child.java.opts=-Xmx2048M; >>> SET mapred.child.java.opts=-Xmx2048M; >>> SET mapred.task.profile=false; >>> >>> CREATE EXTERNAL TABLE IF NOT EXISTS dynamic_partition_table ( >>> >>> field1 string, >>> >>> field2 string, >>> >>> ... >>> >>> field26 string >>> >>> ) >>> PARTITIONED BY (dt string, title string, title_type string) >>> STORED AS ORC >>> LOCATION '/hive/warehouse/partitioned_table' >>> TBLPROPERTIES ("orc.compress.size"="16000"); >>> >>> INSERT OVERWRITE TABLE dynamic_partition_table PARTITION >>> (dt="2016-04-05", title, title_type) >>> SELECT >>> >>> field1, >>> >>> field2, >>> ... >>> >>> title, >>> >>> title_type >>> >>> FROM original_table >>> WHERE dt = "2016-04-05"; >>> >>> The original table has about 250 GB of data for 2016-04-05, and about >>> 260 different titles (some titles have very little data, some have ~20 GB). >>> There is generally only one title_type per title. The INSERT action >>> succeeds on that data set, but when I add 2000 new titles with 300 rows >>> each to the original table, I get the following error during the INSERT: >>> >>> >>> Container >>> [pid=6278,containerID=container_e26_1460661845156_49295_01_000244] is >>> running beyond physical memory limits. Current usage: 2.2 GB of 2 GB >>> physical memory used; 2.7 GB of 4.2 GB virtual memory used. Killing >>> container. >>> >>> >>> I've found a couple questions online about this same error message for >>> ORC files with lots of dynamic partitions, on an older version of Hive: >>> https://qnalist.com/questions/4836037/hive-0-12-orc-heap-issues-on-write >>> >>> Based on that and the information about configuration properties at >>> https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties#ConfigurationProperties-ORCFileFormat, >>> I have tried setting hive.exec.orc.memory.pool=1.0 in order to give as much >>> heap space as possible to the ORC file writers. As you can see from the >>> CREATE TABLE statement, I also decreased the orc.compress.size from the >>> default 256 kb to 16 kb. After making these changes, the INSERT is still >>> failing with the "beyond physical memory limits" error. >>> >>> I've tried inserting into a table stored as RCFile rather than ORC, and >>> in that case the action succeeds even with the additional 2000 titles. >>> >>> Can anyone explain how exactly the two ORC parameters above affect the >>> writing of dynamic partitions in ORC files, and why I'm not getting the OOM >>> error when I use the RCFile format instead? I'd also appreciate any >>> suggestions for other tuning I could do to fix the memory management when >>> using ORC. >>> >>> Thanks for any help, >>> Matt >>> ------------------------------ >>> THIS ELECTRONIC MESSAGE, INCLUDING ANY ACCOMPANYING DOCUMENTS, IS >>> CONFIDENTIAL and may contain information that is privileged and exempt from >>> disclosure under applicable law. If you are neither the intended recipient >>> nor responsible for delivering the message to the intended recipient, >>> please note that any dissemination, distribution, copying or the taking of >>> any action in reliance upon the message is strictly prohibited. If you have >>> received this communication in error, please notify the sender immediately. >>> Thank you. >>> >>> >>> >> >> > >
