mysql增加sort_buffer_Sort_Buffer_Size 设置对服务器性能的影响

基础知识：

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

[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% 左右。