Today when I checked the mysql server, I was prompted to Sort aborted: Out of sort memory, consider increasing server sort buffer size, the installation literally means sort memory overflow, consider increasing the size of the server's sort buffer (sort_buffer_size)
sort_buffer_size = 3M
join_buffer_size = 3M
The following are the parameters set for 16G memory:
sort_buffer_size = 2M
# Sort_Buffer_Size is a connection level parameter. When the buffer is used for the first time in each connection (session), the set memory is allocated at one time.
#Sort_Buffer_Size is not the bigger the better, because it is a connection-level parameter, excessive settings + high concurrency may exhaust system memory resources. For example: 500 connections will consume 500 * sort_buffer_size (8M) = 4G memory
When #Sort_Buffer_Size exceeds 2KB, it will use mmap () instead of malloc () to allocate memory, resulting in reduced efficiency.
#Technical Guide 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 the sort_buffer_size to 10M, I just set 10M, and later I set to 2-3M, there is no problem
Written by cenalulu @ 2010-10-26
Basic knowledge:
1. Sort_Buffer_Size is a connection-level parameter. When the buffer is used for the first time in each connection, the set memory is allocated at one time.
2. Sort_Buffer_Size is not the bigger the better, because it is a connection-level parameter, too large settings + high concurrency may exhaust system memory resources.
3. The documentation says "On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation"
This article focuses on the third point:
It is said that when Sort_Buffer_Size exceeds 2KB, it will use mmap () instead of malloc () to allocate memory, resulting in reduced efficiency.
surroundings:
In order to further reflect the performance gap, Fedora virtual machine with 1GB memory was used for testing
Test table structure:
1w row 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 250K, 512K, 3M respectively, and then run the following statement to view the running time.
1. sql_no_cache prevents query cache from taking effect.
2. limit 1 In order to reduce the proportion of sorting in execution time, more reflect the impact of memory allocation
3. The result of the statement explain is filesort to ensure that sort_buffer is used
Copy the code The 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
250K: 1.318s
512K: 1.944s
3M: 2.412s
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
in conclusion:
Indeed, as the documentation says, when using mmap to allocate memory, it will cause performance loss, and the impact is about 30%.
Because it is limit 1, the impact of buffer size on sorting speed is reduced.
In fact, the larger the buffer size, the less the number of sort-merge and the shorter the execution time. It depends on the specific sentence.
I just want to explain, not bigger is better. There is also no absolute best value.
It still depends on the size of the value determined by the specific business environment. For highly concurrent applications, it is better not to set too much.
