sort memory limit is computed, as follows: MQMPN = planner.memory.max_query_memory_per_node MPN = planner.width.max_per_node NC = number of core in each cluster node NS = number of sort operators in the query
sort limit = MQMPN / (MPN * NC * 0.7) In your case I assume the query contains a single sort operator and you have 16 cores per node. To increase the sort limit you can increase the value of max_query_memory_per_node and you can also reduce the value of planner.width.max_per_node. Please note that reducing the value of the latter option may increase the query's execution time. On Wed, Mar 16, 2016 at 2:47 PM, François Méthot <[email protected]> wrote: > The default spill directory (/tmp) did not have enough space. We fixed > that. (thanks John) > > I altered session to set > planner.memory.max_query_memory_per_node = 17179869184 (16GB) > planner.enable_hashjoin=false; > planner.enable_hashadd=false; > > We ran our aggregation. > > After 7h44m. > > We got > > Error: RESOURCE ERROR: External Sort encountered an error while spilling to > disk > > Fragment 7:35 > > Caused by org.apache.drill.exec.exception.OutOfMemory: Unable to allocate > buffer of size 65536 (rounded from 37444) due to memory limit. Current > allocation: 681080448. > > org.apache.drill.exec.memory.BaseAllocator.buffer(BaseAllocator.java:216) > > org.apache.drill.exec.memory.BaseAllocator.buffer(BaseAllocator.java:191) > > > org.apache.drill.exec.cache.VectorAccessibleSerializable.readFromStream(VectorAccessibleSerializable.java:112) > > > org.apache.drill.exec.physical.impl.xsort.BatchGroup.getBatch(BatchGroup.java:110) > > > org.apache.drill.exec.physical.impl.xsort.BatchGroup.getNextIndex(BatchGroup.java:136) > > > org.apache.drill.exec.test.generated.PriorityQueuedCopierGen975.next(PriorityQueueCopierTemplate.java:76) > > > org.apache.drill.exec.physical.impl.xsort.ExternalSortBatch.mergeAndSpill(ExternalSortBatch.java:557) > > > I think we were close to have the query completed, In the Fragment Profiles > Web UI, the 2 bottom major fragment (out of 5) were showing that they were > done. > I had the same query working on a (20x) smaller set of data. > Should I add more mem to planner.memory.max_query_memory_per_node ? > > > > Abdel: > We did get the memory leak below while doing streaming aggregation, when > our /tmp directory was too small. > After fixing that, our streaming aggregation got us the error above. > > Error: SYSTEM ERROR: IllegalStateException: Memory was leaked by query. > Memory leaked: (389120) > Allocator(op:6:51:2:ExternalSort) 2000000/389120/680576640/715827882 > (res/actual/peal/limit) > > Fragment 6:51 > > [Error Id: ..... on node014.prod:31010] > > > (java.lan.IllegalStateException) Memory was leaked by query. Memory > leaked (389120) > Allocator(op:6:51:2:ExternalSort) 2000000/389120/680576640/715827882 > (res/actual/peal/limit) > org.apache.drill.exec.memory.BaseAllocator.close():492 > org.apache.drill.exec.ops.OperatorContextImpl.close():124 > org.apache.drill.exec.ops.FragmentContext.supressingClose():416 > org.apache.drill.exec.ops.FragmentContext.close():405 > > > org.apache.drill.exec.work.fragment.FragmentExecutor.closeOutResources():343 > org.apache.drill.exec.work.fragment.FragmentExecutor.cleanup():180 > org.apache.drill.exec.work.fragment.FragmentExecutor.run():287 > org.apache.drill.common.SelfCleaningRunnable.run():38 > java.util.concurrentThreadPoolExecutor.runWorker():1142 > > > Thanks guys for your feedback. > > > > On Sat, Mar 12, 2016 at 1:18 AM, Abdel Hakim Deneche < > [email protected]> > wrote: > > > Disabling hash aggregation will default to streaming aggregation + sort. > > This will allow you to handle larger data and spill to disk if necessary. > > > > Like stated in the documentation, starting from Drill 1.5 the default > > memory limit of sort may not be enough to process large data, but you can > > bump it up by increasing planner.memory.max_query_memory_per_node > (defaults > > to 2GB), and if necessary reducing planner.width.max_per_node (defaults > to > > 75% of number of cores). > > > > You said disabling hash aggregate and hash join causes a memory leak. Can > > you give more details about the error ? the query may fail with an out of > > memory but it shouldn't leak. > > > > On Fri, Mar 11, 2016 at 10:53 PM, John Omernik <[email protected]> wrote: > > > > > I've had some luck disabling multi-phase aggregations on some queries > > where > > > memory was an issue. > > > > > > https://drill.apache.org/docs/guidelines-for-optimizing-aggregation/ > > > > > > After I try that, than I typically look at the hash aggregation as you > > have > > > done: > > > > > > > > > > > > https://drill.apache.org/docs/sort-based-and-hash-based-memory-constrained-operators/ > > > > > > I've had limited success with changing the max_query_memory_per_node > and > > > max_width, sometimes it's a weird combination of things that work in > > there. > > > > > > https://drill.apache.org/docs/troubleshooting/#memory-issues > > > > > > Back to your spill stuff if you disable hash aggregation, do you know > if > > > your spill directories are setup? That may be part of the issue, I am > not > > > sure what the default spill behavior of Drill is for spill directory > > setup. > > > > > > > > > > > > On Fri, Mar 11, 2016 at 2:17 PM, François Méthot <[email protected]> > > > wrote: > > > > > > > Hi, > > > > > > > > Using version 1.5, DirectMemory is currently set at 32GB, heap is > at > > > > 8GB. We have been trying to perform multiple aggregation in one query > > > (see > > > > below) on 40 Billions+ rows stored on 13 nodes. We are using parquet > > > > format. > > > > > > > > We keep getting OutOfMemoryException: Failure allocating buffer.. > > > > > > > > on a query that looks like this: > > > > > > > > create table hdfs.`test1234` as > > > > ( > > > > select string_field1, > > > > string_field2, > > > > min ( int_field3 ), > > > > max ( int_field4 ), > > > > count(1), > > > > count ( distinct int_field5 ), > > > > count ( distinct int_field6 ), > > > > count ( distinct string_field7 ) > > > > from hdfs.`/data/` > > > > group by string_field1, string_field2; > > > > ) > > > > > > > > The documentation state: > > > > "Currently, hash-based operations do not spill to disk as needed." > > > > > > > > and > > > > > > > > "If the hash-based operators run out of memory during execution, the > > > query > > > > fails. If large hash operations do not fit in memory on your system, > > you > > > > can disable these operations. When disabled, Drill creates > alternative > > > > plans that allow spilling to disk." > > > > > > > > My understanding is that it will fall back to Streaming aggregation, > > > which > > > > required sorting.. > > > > > > > > but > > > > > > > > "As of Drill 1.5, ... the sort operator (in queries that ran > > successfully > > > > in previous releases) may not have enough memory, resulting in a > failed > > > > query" > > > > > > > > And Indeed, disabling hash agg and hash join resulted in memory leak > > > error. > > > > > > > > So it looks like increasing direct memory our only option. > > > > > > > > Is there a plan to have Hash Aggregation to spill on disk in the next > > > > release? > > > > > > > > > > > > Thanks for your feedback > > > > > > > > > > > > > > > -- > > > > Abdelhakim Deneche > > > > Software Engineer > > > > <http://www.mapr.com/> > > > > > > Now Available - Free Hadoop On-Demand Training > > < > > > http://www.mapr.com/training?utm_source=Email&utm_medium=Signature&utm_campaign=Free%20available > > > > > > -- Abdelhakim Deneche Software Engineer <http://www.mapr.com/> Now Available - Free Hadoop On-Demand Training <http://www.mapr.com/training?utm_source=Email&utm_medium=Signature&utm_campaign=Free%20available>
