Mysql Sort aborted: Out of sort memory, consider increasing server sort buffer size solution, abortedincreasing

Mysql Sort aborted: Out of sort memory, consider increasing server sort buffer size solution, abortedincreasing

When checking the mysql server today, the system prompts Sort aborted: Out of sort memory, consider increasing server sort buffer size. The installation literal means that the sort memory overflows, and you need to increase the server's Sort buffer (sort_buffer_size) size

Sort_buffer_size = 3 M
Join_buffer_size = 3 M

The following parameters are set for 16 GB memory:

Sort_buffer_size = 2 M
# Sort_Buffer_Size is a connection-level parameter. When this buffer is required for each connection (session) for the first time, the configured memory is allocated at one time.
# Sort_Buffer_Size is not as large as possible. Because it is a connection-level parameter, excessive settings + high concurrency may exhaust system memory resources. For example, 500 connections consume 500 * sort_buffer_size (8 M) = 4G memory.
# When Sort_Buffer_Size exceeds 2 kb, mmap () instead of malloc () will be used for memory allocation, resulting in reduced efficiency.
# Technical guidance 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; filesort appears
# Key optimization parameters

However, I saw someone set sort_buffer_size to 10 M. I set it to 2-3 M, so there is no problem.

Written by cenalulu @ 2010-10-26

Basic knowledge:

1. Sort_Buffer_Size is a connection-level parameter. When this buffer is required for each connection for the first time, the configured memory is allocated at one time.
2. Sort_Buffer_Size is not as large as possible. Because it is a connection-level parameter, excessive settings + high concurrency may exhaust system memory resources.
3. The document says "On Linux, there are thresholds of 256KB and 2 MB where larger values may significantly slow down memory allocation"

This article mainly tests the third point:
It is said that when Sort_Buffer_Size exceeds 2 kb, mmap () instead of malloc () will be used for memory allocation, resulting in reduced efficiency.

Environment:

To better reflect the performance gap, use a 1 GB Fedora Virtual Machine for testing.

Test Table Structure:

1 w rows table, 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 250 K, 512 K, and 3 M respectively, and then run the following statement to view the running time.
1. SQL _no_cache prevents query cache from being effective.
2. limit 1 in order to reduce the proportion of sorting to execution time, more reflects the impact of Memory Allocation
3. The result of the explain statement is filesort to ensure that sort_buffer is used.

Copy codeThe Code is as follows:
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:

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

3 M
[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 mentioned in the document, using mmap to allocate memory may result in performance loss, with an impact of about 30%.
Because it is limit 1, the effect of buffer size on the sorting speed is reduced.
In fact, the larger the buffer size, the smaller the number of sort-merge operations required, and the shorter the execution time. Depends on the specific statement.
I just want to explain that it is not the bigger the better. And there is no absolute optimal value.
It depends on the specific business environment to determine the value size. For highly concurrent applications, it is better not to set too large.