|
今天在检查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 | Typenbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; | Null | Key | Default | Extranbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; |
+-------+------------------+------+-----+---------+----------------+
| idnbsp;nbsp;nbsp; | int(10) unsigned | NOnbsp;nbsp; | PRI | NULLnbsp;nbsp;nbsp; | auto_increment |
| knbsp;nbsp;nbsp;nbsp; | int(10) unsigned | NOnbsp;nbsp; | MUL | 0nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; |nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; |
| cnbsp;nbsp;nbsp;nbsp; | char(120)nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; | NOnbsp;nbsp; |nbsp;nbsp;nbsp;nbsp; |nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; |nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; |
| padnbsp;nbsp; | char(60)nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; | NOnbsp;nbsp; |nbsp;nbsp;nbsp;nbsp; |nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; |nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; |
+-------+------------------+------+-----+---------+----------------+
测试语句：
分别设置Sort_Buffer_Size 为 250K ，512K， 3M ，然后运行以下语句，查看运行时间。
1. sql_no_cache 防止query cache起效。
2. limit 1 为了减少排序占执行时间的比重，更多的体现内存分配带来的影响
3. 语句explain的结果是 filesort ， 以确保使用sort_buffer
复制代码 代码如下:
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
3Mnbsp;nbsp;nbsp;nbsp; ： 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
nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; Average number of seconds to run all queries: 1.318 seconds
nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; Minimum number of seconds to run all queries: 1.285 seconds
nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; Maximum number of seconds to run all queries: 1.378 seconds
nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; Number of clients running queries: 100
nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; 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
nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; Average number of seconds to run all queries: 1.944 seconds
nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; Minimum number of seconds to run all queries: 1.362 seconds
nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; Maximum number of seconds to run all queries: 4.406 seconds
nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; Number of clients running queries: 100
nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; 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
nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; Average number of seconds to run all queries: 2.412 seconds
nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; Minimum number of seconds to run all queries: 2.376 seconds
nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; Maximum number of seconds to run all queries: 2.527 seconds
nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; Number of clients running queries: 100
nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; Average number of queries per client: 1
（编辑：常州站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
