《Mysql實例mysql Sort aborted: Out of sort memory, consider increasing server sort buffer size的解決方法》要點:
本文介紹了Mysql實例mysql Sort aborted: Out of sort memory, consider increasing server sort buffer size的解決方法,希望對您有用。如果有疑問,可以聯(lián)系我們。
MYSQL應(yīng)用今天在檢查mysql服務(wù)器的時候提示Sort aborted: Out of sort memory, consider increasing server sort buffer size,安裝字面意思就是 sort內(nèi)存溢出,考慮增加服務(wù)器的排序緩沖區(qū)(sort_buffer_size)大小
MYSQL應(yīng)用sort_buffer_size=3M
join_buffer_size = 3M
MYSQL應(yīng)用下面是針對16G 內(nèi)存設(shè)置的參數(shù):
MYSQL應(yīng)用sort_buffer_size = 2M
# Sort_Buffer_Size 是一個connection級參數(shù),在每個connection(session)第一次需要使用這個buffer的時候,一次性分配設(shè)置的內(nèi)存.
#Sort_Buffer_Size 并不是越大越好,由于是connection級的參數(shù),過大的設(shè)置+高并發(fā)可能會耗盡系統(tǒng)內(nèi)存資源.例如:500個連接將會消耗 500*sort_buffer_size(8M)=4G內(nèi)存
#Sort_Buffer_Size 超過2KB的時候,就會使用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
#屬重點優(yōu)化參數(shù)
MYSQL應(yīng)用不過我看到有人將sort_buffer_size設(shè)置為10M,我就是因為設(shè)置了10M,后來我設(shè)置為2-3M,就沒有問題了
MYSQL應(yīng)用Written by cenalulu @ 2010-10-26
MYSQL應(yīng)用基礎(chǔ)知識:
MYSQL應(yīng)用1、 Sort_Buffer_Size 是一個connection級參數(shù),在每個connection第一次需要使用這個buffer的時候,一次性分配設(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”
MYSQL應(yīng)用本文主要針對第三點做測試:
據(jù)說Sort_Buffer_Size 超過2KB的時候,就會使用mmap() 而不是 malloc() 來進(jìn)行內(nèi)存分配,導(dǎo)致效率降低.
MYSQL應(yīng)用環(huán)境:
MYSQL應(yīng)用為了更大的體現(xiàn)性能差距,使用 1GB內(nèi)存的Fedora 虛擬機(jī)進(jìn)行測試
MYSQL應(yīng)用測試表結(jié)構(gòu):
MYSQL應(yīng)用1w 行的表, 表結(jié)構(gòu)
MYSQL應(yīng)用+-------+------------------+------+-----+---------+----------------+
| 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?? |???? |???????? |??????????????? |
+-------+------------------+------+-----+---------+----------------+
MYSQL應(yīng)用測試語句:
MYSQL應(yīng)用分別設(shè)置Sort_Buffer_Size 為 250K ,512K, 3M ,然后運(yùn)行以下語句,查看運(yùn)行時間.
1. sql_no_cache 防止query cache起效.
2. limit 1 為了減少排序占執(zhí)行時間的比重,更多的體現(xiàn)內(nèi)存分配帶來的影響
3. 語句explain的結(jié)果是 filesort , 以確保使用sort_buffer
MYSQL應(yīng)用測試結(jié)果:
MYSQL應(yīng)用執(zhí)行時間
MYSQL應(yīng)用250K : 1.318s
512K : 1.944s
3M???? : 2.412s
MYSQL應(yīng)用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
MYSQL應(yīng)用512 K
MYSQL應(yīng)用[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
MYSQL應(yīng)用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
MYSQL應(yīng)用結(jié)論:
確實如文檔所說, 使用mmap 分配內(nèi)存時,會帶來性能上的損耗,影響大約在 30% 左右.
由于是limit 1,因此減弱了buffer size對排序速度的影響.
其實buffer size 越大,所需要進(jìn)行的sort-merge的次數(shù)越少,執(zhí)行時間越短.視具體的語句而定.
我只是想說明下,并不是越大越好. 也并不存在一個絕對的最佳值.
還是要看具體的業(yè)務(wù)環(huán)境決定值的大小. 對于高并發(fā)的應(yīng)用,還是別設(shè)置太大的比較好.
轉(zhuǎn)載請注明本頁網(wǎng)址:
http://www.snjht.com/jiaocheng/2356.html