Shulou(Shulou.com)06/01 Report--

MongoDB memory overflow error description

Exception: getMore runner error: Overflow sort stage buffered data usage of 33638076 bytes exceeds internal limit of 33554432 bytes

The limitation and solution of sorting in MongoDB memory

The following is quoted from: https://docs.mongodb.com/manual/reference/method/cursor.sort/#cursor.sort

When unable to obtain the sort order from an index, MongoDB will sort the results in memory, which requires that the result set being sorted is less than 32 megabytes.

When the sort operation consumes more than 32 megabytes, MongoDB returns an error. To avoid this error, either create an index supporting the sort operation (see Sort and Index Use) or use sort () in conjunction with limit () (see Limit Results).

Description and execution order of MongoDB query method

The following is quoted from: https://docs.mongodb.com/manual/tutorial/query-documents/#query-method

Query Method

MongoDB provides the db.collection.find () method to read documents from a collection. The db.collection.find () method returns a cursor to the matching documents.

Db.collection.find (,)

For the db.collection.find () method, you can specify the following optional fields:

A query filter to specify which documents to return.

A query projection to specifies which fields from the matching documents to return. The projection limits the amount of data that MongoDB returns to the client over the network.

You can optionally add a cursor modifier to impose limits, skips, and sort orders. The order of documents returned by a query is not defined unless you specify a sort ().

The following is quoted from: https://docs.mongodb.com/manual/reference/method/db.collection.find/#combine-cursor-methods

Combine Cursor Methods

The following statements chain cursor methods limit () and sort ():

Db.bios.find () .sort ({name: 1}) .limit (5) db.bios.find () .limit (5) .sort ({name: 1})

The two statements are equivalent; i.e. The order in which you chain the limit () and the sort () methods is not significant. Both statements return the first five documents, as determined by the ascending sort order on 'name'.

By the way, take a look at the execution order of SQL Server statements.

The first chapter of the book "SQL Server 2005 Technical Insider-Inquiry". The author of the book should also let the reader first understand the order in which sentences are executed.

The logical execution order of the query:

(1) FROM

< left_table>

(3)

< join_type>

JOIN

< right_table>

(2) ON

< join_condition>

(4) WHERE

< where_condition>

(5) GROUP BY

< group_by_list>

(6) WITH {cube | rollup}

(7) HAVING

< having_condition>

(8) SELECT (9) DISTINCT (11)

< top_specification>

< select_list>

(10) ORDER BY

< order_by_list>

The parsing order of the standard SQL is:

(1). FROM clause assembles data from different data sources

(2). The WHERE clause filters records based on the specified criteria

(3) the GROUP BY clause divides the data into multiple packets

(4)。 Use aggregate functions for calculation

(5)。 Use the HAVING clause to filter grouping

(6)。 Evaluate all expressions

(7)。 Use ORDER BY to sort result sets

Execution order:

1.FROM: generate a virtual table vt1 by performing Cartesian product on the first two tables in the FROM clause

2.ON: applying an ON filter to an vt1 table only satisfies the

< join_condition>

A true row is inserted into the vt2

3.OUTER (join): if you specify rows that are not found in the OUTER JOIN retention table (preserved table), add rows to vt2 generation T3 as external rows. If from contains more than two tables, repeat the steps and steps for the result table generated by the previous join and the next table.

4.WHERE: applying a WHERE filter to vt3 can only make

< where_condition>

Only rows that are true are inserted into vt4

5.GROUP BY: generate vt5 by grouping rows in vt4 by column list in the GROUP BY clause

6.CUBE | ROLLUP: insert supergroups into vt6 to generate vt6

7.HAVING: applying a HAVING filter to vt6 can only make

< having_condition>

Insert vt7 only for true groups

8.SELECT: processing select lists to generate vt8

9.DISTINCT: removes duplicate lines from vt8 to produce vt9

10.ORDER BY: sorts the rows of vt9 by the list of columns in the order by clause to generate a cursor vc10

11.TOP: select a specified number or proportion of rows from the beginning of vc10 to generate vt11 and return to the caller

Comparison and summary

Both MongoDB and SQL Server first SELECT the list, then sort it in memory, and finally take the first few lines.

Optimization for memory overflow

For the principles of MongoDB query optimization, please refer to:

Optimize Query Performance

Https://docs.mongodb.com/manual/tutorial/optimize-query-performance-with-indexes-and-projections/

Some developers will simply take out the data and sort it in the program, which is not recommended because it also takes up too much memory and does not fundamentally solve the problem.

There are three recommended options:

1. Optimize queries and indexes.

two。 Reduce output columns (limit the number of output columns) or rows (such as the limit function, or limit the number of input queries _ id).

3. The query is divided into two steps. In the first step, only _ id is output, and in the second step, the details are checked through _ id.

Can solve the problem of sort overflow in memory.

System parameter tuning starting from version 3.0

Starting with version 3. 0, you can increase the memory sort size limit by changing the parameter value internalQueryExecMaxBlockingSortBytes.

Let's first take a look at all the supported parameters:

Use admindb.runCommand ({getParameter: 1, "internalQueryExecMaxBlockingSortBytes": 1})

Let's take a look at how to set:

Db.adminCommand ({setParameter: 1, internalQueryExecMaxBlockingSortBytes:})

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.