Sort_Buffer_Size 设置对服务器性能的影响 【原创】
本帖最后由 cenalulu 于 2010-10-26 11:48 编辑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
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
# 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
# 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
# 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% 左右。 250K ： 1.318s
512K ： 1.944s
3M ： 2.412s
Sort_Buffer_Size 越大，查询所消耗的时间也越多？？应该有个最合适的值，曲线应该像抛物线的，多测试几个值看看呢？ 支持原创！ 250K ： 1.318s
512K ： 1.944s
3M ： 2.412s
Sort_Buffer_Size 越大，查询所消耗的时间也越多？？应 ...
飞鸿无痕 发表于 2010-10-26 11:57 http://bbs.chinaunix.net/images/common/back.gif
由于是limit 1，因此减弱了buffer size对排序速度的影响。
其实buffer size 越大，所需要进行的sort-merge的次数越少，执行时间越短。视具体的语句而定。
我只是想说明下，并不是越大越好。 也并不存在一个绝对的最佳值。
还是要看具体的业务环境决定值的大小。 对于高并发的应用，还是别设置太大的比较好。
页:
[1]