阿里布达年,绍兴e网,国模芭芭拉第三季
今天在检查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
#属重点优化参数
不过我看到有人将sort_buffer_size设置为10m,我就是因为设置了10m,后来我设置为2-3m,就没有问题了
written by cenalulu @ 2010-10-26
基础知识:
1、 sort_buffer_size 是一个connection级参数,在每个connection第一次需要使用这个buffer的时候,一次性分配设置的内存。
2、 sort_buffer_size 并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。
3、 文档说“on linux, there are thresholds of 256kb and 2mb where larger values may significantly slow down memory allocation”
本文主要针对第三点做测试:
据说sort_buffer_size 超过2kb的时候,就会使用mmap() 而不是 malloc() 来进行内存分配,导致效率降低。
环境:
为了更大的体现性能差距,使用 1gb内存的fedora 虚拟机进行测试
测试表结构:
1w 行的表, 表结构
+-------+------------------+------+-----+---------+----------------+
| 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 | | | |
+-------+------------------+------+-----+---------+----------------+
测试语句:
分别设置sort_buffer_size 为 250k ,512k, 3m ,然后运行以下语句,查看运行时间。
1. sql_no_cache 防止query cache起效。
2. limit 1 为了减少排序占执行时间的比重,更多的体现内存分配带来的影响
3. 语句explain的结果是 filesort , 以确保使用sort_buffer
测试结果:
执行时间
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的次数越少,执行时间越短。视具体的语句而定。
我只是想说明下,并不是越大越好。 也并不存在一个绝对的最佳值。
还是要看具体的业务环境决定值的大小。 对于高并发的应用,还是别设置太大的比较好。
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复
小白安装登录mysql-8.0.19-winx64的教程图解(新手必看)
Navicat连接MySQL时报10060、1045错误及my.ini位置问题
网友评论