mysql sort aborted_mysql Sort aborted: Out of sort memory, consider increasing_MySQL

今天在检查mysql服务器的时候提示Sort aborted: Out of sort memory, consider increasing server sort buffer size，安装字面意思就是 sort内存溢出，考虑增加服务器的排序缓冲区(sort_buffer_size)大小

sort_buffer_size=3M

join_buffer_size = 3M

下面是针对16G 内存设置的参数：

sort_buffer_size = 2M# Sort_Buffer_Size 是一个connection级参数，在每个connection(session)第一次需要使用这个buffer的时候，一次性分配设置的内存。

#Sort_Buffer_Size 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。例如：500个连接将会消耗 500*sort_buffer_size(8M)=4G内存

#Sort_Buffer_Size 超过2KB的时候，就会使用mmap() 而不是 malloc() 来进行内存分配，导致效率降低。

#技术导读 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

#属重点优化参数

代码如下:

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

测试结果：

执行时间

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

结论：确实如文档所说， 使用mmap 分配内存时，会带来性能上的损耗，影响大约在 30% 左右。

由于是limit 1，因此减弱了buffer size对排序速度的影响。

其实buffer size 越大，所需要进行的sort-merge的次数越少，执行时间越短。视具体的语句而定。

我只是想说明下，并不是越大越好。 也并不存在一个绝对的最佳值。

还是要看具体的业务环境决定值的大小。 对于高并发的应用，还是别设置太大的比较好。

本文原创发布php中文网，转载请注明出处，感谢您的尊重！