Mongo排序超出最大内存错误

最新推荐文章于 2023-03-08 11:47:38 发布
最新推荐文章于 2023-03-08 11:47:38 发布
阅读量3.8k 收藏 2
点赞数
分类专栏： SpringBoot 文章标签： mongodb java springBoot Sort
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_31150503/article/details/108730635
版权

背景

MongoDB是一个文档数据库，这意味着它将数据存储在类似JSON的文档中。我们认为，这是考虑数据的最自然方法，比传统的行/列模型更具表现力和功能。

现象

排序、分页功能也是在使用过程中最常见的查询，最近Spring data mongo使用排序、分页功能查询数据量在5000w左右的数据时出现超出最大内存限制错误。

代码分析

Page<VmMessageDtlVO> data = new Page<>();
// add common condition
Criteria criteria = commonCond(vmMessageDtlDTO);
Query query = new Query(criteria);
// order by createTime desc
query.with(Sort.by(Sort.Direction.DESC, "createTime")).skip(vmMessageDtlDTO.getCurrentPageIndex()).limit(vmMessageDtlDTO.getPageSize());
List<VmMessageDtlModel> list = mongoTemplate.find(query, VmMessageDtlModel.class);
// format data info
List<VmMessageDtlVO> result = convertList(list);
Long rows = mongoTemplate.count(query, VmMessageDtlModel.class);

使用Spring Query直接先排序后分页，如果根据条件匹配后仍然有海量数据的话就需要对大批量数据进行排序操作，不使用磁盘缓存的话很容易就出现内存暴增的情况，也是程序运营中的忌讳，如果多个查询同时进行则有可能程序直接内存爆掉。

错误信息

Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation

org.springframework.data.mongodb.UncategorizedMongoDbException: Query failed with error code 292 and error message 'Executor error during find command :: caused by :: Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.' on server 192.168.1.198:30014; nested exception is com.mongodb.MongoQueryException: Query failed with error code 292 and error message 'Executor error during find command :: caused by :: Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.' on server 192.168.1.198:30014
	at org.springframework.data.mongodb.core.MongoExceptionTranslator.translateExceptionIfPossible(MongoExceptionTranslator.java:138)
	at org.springframework.data.mongodb.core.MongoTemplate.potentiallyConvertRuntimeException(MongoTemplate.java:2781)
	at org.springframework.data.mongodb.core.MongoTemplate.executeFindMultiInternal(MongoTemplate.java:2691)
	at org.springframework.data.mongodb.core.MongoTemplate.doFind(MongoTemplate.java:2411)
	at org.springframework.data.mongodb.core.MongoTemplate.doFind(MongoTemplate.java:2394)
	at org.springframework.data.mongodb.core.MongoTemplate.find(MongoTemplate.java:830)
	at org.springframework.data.mongodb.core.MongoTemplate.find(MongoTemplate.java:816)
	at com.sioo.component.biz.impl.sioovmimpl.VmMessageDtlServiceImpl.listByPageAndCond(VmMessageDtlServiceImpl.java:67)
	at com.sioo.component.biz.impl.sioovmimpl.VmMessageRecordsServiceImpl.findVmMessageDetailsByPage(VmMessageRecordsServiceImpl.java:1113)
	at com.sioo.component.biz.impl.sioovmimpl.VmMessageRecordsServiceImpl$$FastClassBySpringCGLIB$$1df5b259.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:684)
	at com.sioo.component.biz.impl.sioovmimpl.VmMessageRecordsServiceImpl$$EnhancerBySpringCGLIB$$db6911a2.findVmMessageDetailsByPage(<generated>)
	at com.sioo.component.controller.VmMessageController.findVmMessageDetailsByPage(VmMessageController.java:312)
	at com.sioo.component.controller.VmMessageController$$FastClassBySpringCGLIB$$66bdd36.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:56)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
	at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:100)
	at com.sioo.point.aspect.ShowTimeAspect.dealOldMethod(ShowTimeAspect.java:162)
	at com.sioo.point.aspect.ShowTimeAspect.timeAround(ShowTimeAspect.java:134)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633)
	at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
	at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:62)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
	at com.sioo.component.controller.VmMessageController$$EnhancerBySpringCGLIB$$a00b1af3.findVmMessageDetailsByPage(<generated>)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:892)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:797)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1039)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1005)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:908)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:882)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:124)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.boot.actuate.web.trace.servlet.HttpTraceFilter.doFilterInternal(HttpTraceFilter.java:88)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:92)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.filterAndRecordMetrics(WebMvcMetricsFilter.java:114)
	at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:104)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:853)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1587)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)
Caused by: com.mongodb.MongoQueryException: Query failed with error code 292 and error message 'Executor error during find command :: caused by :: Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.' on server 192.168.1.198:30014
	at com.mongodb.operation.FindOperation$1.call(FindOperation.java:706)
	at com.mongodb.operation.FindOperation$1.call(FindOperation.java:695)
	at com.mongodb.operation.OperationHelper.withConnectionSource(OperationHelper.java:462)
	at com.mongodb.operation.OperationHelper.withConnection(OperationHelper.java:406)
	at com.mongodb.operation.FindOperation.execute(FindOperation.java:695)
	at com.mongodb.operation.FindOperation.execute(FindOperation.java:83)
	at com.mongodb.client.internal.MongoClientDelegate$DelegateOperationExecutor.execute(MongoClientDelegate.java:179)
	at com.mongodb.client.internal.MongoIterableImpl.execute(MongoIterableImpl.java:132)
	at com.mongodb.client.internal.MongoIterableImpl.iterator(MongoIterableImpl.java:86)
	at org.springframework.data.mongodb.core.MongoTemplate.executeFindMultiInternal(MongoTemplate.java:2674)
	... 100 common frames omitted

原因

Mongo数据库排序（Sort）阶段默认最大的存储内存限制为100 MB，所以如果要解除限制需要更改聚合操作中的“allowDiskUse ”属性，官方文档见：https://docs.mongodb.com/manual/reference/operator/aggregation/sort/#sort-memory-limit

 

解决方案

使用聚合方式进行条件查询、排序、分页操作

Criteria criteria = commonCond(vmMessageDtlDTO);
// 初始化聚合操作、允许使用磁盘内存缓存
AggregationOptions aggregationOptions = AggregationOptions.builder().allowDiskUse(true).build();
Aggregation aggregation = Aggregation.newAggregation(
                Aggregation.match(criteria),
                Aggregation.sort(Sort.by(Sort.Direction.DESC, "createTime")),
                Aggregation.skip((long)vmMessageDtlDTO.getCurrentPageIndex()),
                Aggregation.limit(vmMessageDtlDTO.getPageSize())
        ).withOptions(aggregationOptions);

List<VmMessageDtlModel> list = mongoTemplate.aggregate(aggregation, VmMessageDtlModel.class, VmMessageDtlModel.class).getMappedResults();

 

IT界刘德华
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
解决MongoDB 排序超过内存限制的问题
09-09
最近小编遇到这样的问题：对集合执行一个大排序操作（如聚合），出现以下错误：（测试版本：MongoDB 3.0.6），怎么快速解决此问题呢？下面小编给大家分享MongoDB 排序超过内存限制的解决方法，一起看看吧
mongo.tar 镜像
01-28
mongo docker 镜像
Mongo 3T带破解码
04-19
Mongo数据库连接工具,带破解批处理文件.3T支持的mongo功能很多
监控mongo状态慢查询
03-02
mongostat是mongdb自带的状态检测工具，在命令行下使用。它会间隔固定时间获取mongodb的当前运行状态，并输出。如果你发现数据库突然变慢或者有其他问题的话，你第一手的操作就考虑采用mongostat来查看mongo的状态。它的输出有以下几列：类似于MySQL的slowlog,MongoDB可以监控所有慢的以及不慢的查询。Profiler默认是关闭的，你可以选择全部开启，或者有慢查询的时候开启。查看Profile日志3个字段的意义不多说，此处有官方文档。注意，造成满查询可能是索引的问题，也可能是数据不在内存造成因此磁盘读入造成。Mongodb自带了Web控制台，默认和数据服务一同开
MongoDB增加排序内存--版本4.4.1
weixin_43054437的博客
11-09 908
调取大量文件并进行排序时，MongoDB报错，超出内存使用范围，报错信息如下： mongo排序默认的最大内存限制为100m，超出这个限制就会报错。所以查询时查询的字段过多，查询的数据量过大等情况导致排序缓存超出限制会产生这样的问题。 增大内存指令： 进入MongoDB后 use admin # 设置 db.adminCommand({setParameter:1, internalQueryMaxBlockingSortMemoryUsageBytes:209715200}) # 查询.
小狼-MongoDB数据多时报错
qq_44729800的博客
07-09 937
Query failed with error code 292 and error message 'Executor error during find command :: caused by :: Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.' on server 1
【Bug解决】pymongo.errors.OperationFailure: Executor error during find command :: caused by :: Sort exce
兴趣使然的创作者
03-08 1621
使用pymongo时，这行代码触发如下报错：触发代码： 原因 查阅mongo中文文档(https://mongodb.net.cn/manual/reference/method/cursor.sort/#sort-limit-results)，得知此报错的原因是排序内容超越32MB的内存限制。文档解释：当无法从索引获取排序顺序时，MongoDB将对内存中的结果进行排序，这要求所排序的结果集小于32 MB。找到如下解决方法，参考：https://segmentfault.com/a/119000004098
MongoDB 排序内存超出限制QueryExceededMemoryLimitNoDiskUseAllowed:Sort exceeded memory limit
qq_37975037的博客
04-26 3785
问题描述： 使用mongo进行排序时，报排序超出最大内存错误 org.springframework.data.mongodb.UncategorizedMongoDbException: Command failed with error 292 (QueryExceededMemoryLimitNoDiskUseAllowed): 'Error in $cursor stage :: caused by :: Sort exceeded memory limit of 104857600 bytes,
org.springframework.data.mongodb.UncategorizedMongoDbException
热门推荐
xiaomojun的专栏
04-29 1万+
在使用mongodb数据库执行带条件查询操作时遇到以下问题： org.springframework.data.mongodb.UncategorizedMongoDbException: Query failed with error code 2 and error message 'Field 'locale' is invalid in: { locale: "message" }'...
遇到错误----Mongodb----导出数据报错 Sort operation used more than the maximum 33554432 bytes of RAM
直到世界的尽头
11-29 6269
遇到的情况 线上的列表在运行过程中突然爆出了一个Mongodb 查询的BUG，错误如下： “exception”:”org.springframework.data.mongodb.UncategorizedMongoDbException”, “message”:”Query failed with error code 96 and error message ‘Executor...
mongo 集群
10-22
不同端口，3个可用的服务，3个分片，每个分片为3个副本的副本集
pymongo实现多结果进行多列排序的方法
12-25
本文实例讲述了pymongo实现多结果进行多列排序的方法。分享给大家供大家参考。具体分析如下： 这里多列排序即指定多个排序字段。 集合查询结果排序 复制代码 代码如下:>>> db.Account.find().sort(“UserName”) –...
mongo免安装
07-24
mongo免安装mongo免安装
mongo_server.py
05-22
本人爬虫工作中自己封装的 MongoServer 类, 封装了常用的方法, 只需根据实际项目进行简单的配置和微调, 就可以很大程度上减少代码的冗余, 助力开发.
meteor-mongo-collate:mongo 基于语言的排序和整理
06-06
mongo 基于语言的排序和整理。 目前，mongodb 的原生排序是基于英文且区分大小写的。 但是对于大多数应用程序来说，这种排序是不自然的，而且经常会产生误导。 这个包通过一种解决方法来缓解这个问题，该方法创建...
mongo客户端
03-29
mongo客户端mongo客户端mongo客户端mongo客户端mongo客户端
mongo-client
09-19
mongo服务器搭建之后所需的mongo客户端，功能强大，简单易用
mongo中模糊查询的综合应用
12-16
以下是一个mongo查询的综合应用，即介绍一个生产中实际应用的模糊查询，当然其实也很简单，主要用到mongo中的模糊查询和$or查询，以及并的关系，下面是一个mongo中的一条记录 { "_id" : "ffe6a068-9043-4334-97d2-...
MongoVUE,MongoDB客户端
04-17
MongoDB客户端管理工具（MongoVUE），图形显示，开发利器。速度快，安装简单，查询，创建和删除
mongo排序字段数组规则
最新发布
05-26
在MongoDB中，可以使用sort()方法对查询结果进行排序。当需要对一个数组字段进行排序时，可以使用以下规则： 1. 如果要按数组中某个元素的值进行排序，则可以使用以下语法： ``` db.collection.find().sort({...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值