mysql Sort aborted: Out of sort memory consider increasing server sort buffer size: Out of sort memory consider server sort buffer size

  • 2021-01-06 00:46:16
  • OfStack

Sort aborted: Out of sort memory, consider increasing server sort buffer size, sort: sort_buffer_size: Out of sort memory, consider increasing server sort buffer size: sort is out of memory

sort_buffer_size=3M
join_buffer_size = 3M

Here are the parameters set for 16G memory:

sort_buffer_size = 2M
# Sort_Buffer_Size is an connection level parameter that allocates the memory set once each connection (session) needs to use the buffer for the first time.
#Sort_Buffer_Size Bigger is not better. Since it is an connection level parameter, excessive setting + high concurrency can drain system memory resources. For example, 500 connections will consume 500*sort_buffer_size(8M)=4G memory
#Sort_Buffer_Size When you exceed 2KB, you will use mmap() instead of malloc() for memory allocation, resulting in reduced efficiency.
# technology introduction http: / / blog webshuo. com / 2011/02/16 / mysql sort_buffer_size /
#dev-doc: http://dev.mysql.com/doc/refman/5.5/en/server-parameters.html
#explain select*from table where order limit; Appear filesort
# is a key optimization parameter

But I saw someone set sort_buffer_size to 10M, I set 10M, then I set it to 2-3M and it was OK

Written by cenalulu @ 2010-10-26

Basics:

1, Sort_Buffer_Size is an connection parameter, which is allocated to the memory of each connection for the first time when the buffer is needed.
2, Sort_Buffer_Size is not the bigger the better, because the connection level parameter, too large setting + high concurrency may deplete the system memory resources.
On Linux, there are thresholds of 256KB and 2MB where larger may significantly slow down memory allocation allocation

This paper mainly tests the third point:
It is said that when Sort_Buffer_Size exceeds 2KB, it will use mmap() instead of malloc() for memory allocation, resulting in reduced efficiency.

Environment:

To better reflect the performance gap, the Fedora virtual machine with 1GB memory was used for testing

Test table structure:

Table for line 1w, table structure

+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| k | int(10) unsigned | NO | MUL | 0 | |
| c | char(120) | NO | | | |
| pad | char(60) | NO | | | |
+-------+------------------+------+-----+---------+----------------+

Test statement:

Set Sort_Buffer_Size to 250K, 512K, 3M respectively, and then run the following statements to see the elapsed time.
1. sql_no_cache prevents query and cache from being effective.
2. In order to reduce the proportion of sorting to execution time, limit 1 more reflects the impact of memory allocation
3. The result of the statement explain is filesort to ensure that sort_buffer is used


mysqlslap -uroot -h127.0.0.1 -q ' select sql_no_cache * from sbtest order by pad limit 1' -c 100 --create-schema=test -i 10

Test results:

The execution time

250 K: 1.318 s
512 K: 1.944 s
3 M: 2.412 s

250 K
[root@localhost tmp]# mysqlslap -uroot -h127.0.0.1 -q ' select sql_no_cache * from sbtest order by pad limit 1' -c 100 --create-schema=test -i 10
Benchmark
Average number of seconds to run all queries: 1.318 seconds
Minimum number of seconds to run all queries: 1.285 seconds
Maximum number of seconds to run all queries: 1.378 seconds
Number of clients running queries: 100
Average number of queries per client: 1

512 K

[root@localhost tmp]# mysqlslap -uroot -h127.0.0.1 -q ' select sql_no_cache * from sbtest order by pad limit 1' -c 100 --create-schema=test -i 10
Benchmark
Average number of seconds to run all queries: 1.944 seconds
Minimum number of seconds to run all queries: 1.362 seconds
Maximum number of seconds to run all queries: 4.406 seconds
Number of clients running queries: 100
Average number of queries per client: 1

3M
[root@localhost tmp]# mysqlslap -uroot -h127.0.0.1 -q ' select sql_no_cache * from sbtest order by pad limit 1' -c 100 --create-schema=test -i 10
Benchmark
Average number of seconds to run all queries: 2.412 seconds
Minimum number of seconds to run all queries: 2.376 seconds
Maximum number of seconds to run all queries: 2.527 seconds
Number of clients running queries: 100
Average number of queries per client: 1

Conclusion:
Indeed, as documented, using mmap to allocate memory results in a performance penalty of around 30%.
Because it is limit 1, the effect of buffer on sorting speed is mitigated.
In fact, the larger buffer is, the fewer times sort-merge is needed and the shorter the execution time is. Depends on the specific statement.
I just want to make it clear that bigger is not better. There is no one absolute best value.
Again, the size of the value depends on the specific business environment. For high concurrency applications, it is better not to set it too large.


Related articles: