今天在檢查mysql服務(wù)器的時(shí)候提示Sort aborted: Out of sort memory, consider increasing server sort buffer size，安裝字面意思就是 sort內(nèi)存溢出，考慮增加服務(wù)器的排序緩沖區(qū)(sort_buffer_size)大小

sort_buffer_size=3M
join_buffer_size = 3M

下面是針對16G 內(nèi)存設(shè)置的參數(shù)：

sort_buffer_size = 2M
# Sort_Buffer_Size 是一個(gè)connection級參數(shù)，在每個(gè)connection（session）第一次需要使用這個(gè)buffer的時(shí)候，一次性分配設(shè)置的內(nèi)存。
#Sort_Buffer_Size 并不是越大越好，由于是connection級的參數(shù)，過大的設(shè)置+高并發(fā)可能會耗盡系統(tǒng)內(nèi)存資源。例如：500個(gè)連接將會消耗 500*sort_buffer_size(8M)=4G內(nèi)存
#Sort_Buffer_Size 超過2KB的時(shí)候，就會使用mmap() 而不是 malloc() 來進(jìn)行內(nèi)存分配，導(dǎo)致效率降低。
#技術(shù)導(dǎo)讀 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；出現(xiàn)filesort
#屬重點(diǎn)優(yōu)化參數(shù)

不過我看到有人將sort_buffer_size設(shè)置為10M，我就是因?yàn)樵O(shè)置了10M，后來我設(shè)置為2-3M，就沒有問題了

Written by cenalulu @ 2010-10-26

基礎(chǔ)知識：

1、 Sort_Buffer_Size 是一個(gè)connection級參數(shù)，在每個(gè)connection第一次需要使用這個(gè)buffer的時(shí)候，一次性分配設(shè)置的內(nèi)存。
2、 Sort_Buffer_Size 并不是越大越好，由于是connection級的參數(shù)，過大的設(shè)置+高并發(fā)可能會耗盡系統(tǒng)內(nèi)存資源。
3、 文檔說“On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation”

本文主要針對第三點(diǎn)做測試：
據(jù)說Sort_Buffer_Size 超過2KB的時(shí)候，就會使用mmap() 而不是 malloc() 來進(jìn)行內(nèi)存分配，導(dǎo)致效率降低。

環(huán)境：

為了更大的體現(xiàn)性能差距，使用 1GB內(nèi)存的Fedora 虛擬機(jī)進(jìn)行測試

測試表結(jié)構(gòu)：

1w 行的表， 表結(jié)構(gòu)

+-------+------------------+------+-----+---------+----------------+
| 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   |     |         |                |
+-------+------------------+------+-----+---------+----------------+

測試語句：

分別設(shè)置Sort_Buffer_Size 為 250K ，512K， 3M ，然后運(yùn)行以下語句，查看運(yùn)行時(shí)間。
1. sql_no_cache 防止query cache起效。
2. limit 1 為了減少排序占執(zhí)行時(shí)間的比重，更多的體現(xiàn)內(nèi)存分配帶來的影響
3. 語句explain的結(jié)果是 filesort ， 以確保使用sort_buffer

測試結(jié)果：

執(zhí)行時(shí)間

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

結(jié)論：
確實(shí)如文檔所說， 使用mmap 分配內(nèi)存時(shí)，會帶來性能上的損耗，影響大約在 30% 左右。
由于是limit 1，因此減弱了buffer size對排序速度的影響。
其實(shí)buffer size 越大，所需要進(jìn)行的sort-merge的次數(shù)越少，執(zhí)行時(shí)間越短。視具體的語句而定。
我只是想說明下，并不是越大越好。 也并不存在一個(gè)絕對的最佳值。
還是要看具體的業(yè)務(wù)環(huán)境決定值的大小。 對于高并發(fā)的應(yīng)用，還是別設(shè)置太大的比較好。

最新評論