mysql buffer size_sort_buffer_size参数--mysql

sort_buffer_size是一个connection级参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存。并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。官方文档推荐范围为256KB~2MB，这里我们设置为2M。

采用mysqlslap进行压力测试

测试语句：

SELECT SQL_NO_CACHE * FROM zabbix_server.`alerts` ORDER BY SUBJECT LIMIT 1

设为256K

./mysqlslap -uroot -h127.0.0.1 -q 'SELECT SQL_NO_CACHE * FROM zabbix_server.`alerts` ORDER BY SUBJECT LIMIT 1' -c 100 --create-schema=zabbix_server -i 10 -p

Enter password:

Benchmark

Average number of seconds to run all queries: 0.221 seconds

Minimum number of seconds to run all queries: 0.123 seconds

Maximum number of seconds to run all queries: 1.043 seconds

Number of clients running queries: 100

Average number of queries per client: 1

设为512K

./mysqlslap -uroot -h127.0.0.1 -q 'SELECT SQL_NO_CACHE * FROM zabbix_server.`alerts` ORDER BY SUBJECT LIMIT 1' -c 100 --create-schema=zabbix_server -i 10 -p

Enter password:

Benchmark

Average number of seconds to run all queries: 0.145 seconds

Minimum number of seconds to run all queries: 0.123 seconds

Maximum number of seconds to run all queries: 0.319 seconds

Number of clients running queries: 100

Average number of queries per client: 1

设为1M

./mysqlslap -uroot -h127.0.0.1 -q 'SELECT SQL_NO_CACHE * FROM zabbix_server.`alerts` ORDER BY SUBJECT LIMIT 1' -c 100 --create-schema=zabbix_server -i 10 -p

Enter password:

Benchmark

Average number of seconds to run all queries: 0.133 seconds

Minimum number of seconds to run all queries: 0.124 seconds

Maximum number of seconds to run all queries: 0.206 seconds

Number of clients running queries: 100

Average number of queries per client: 1