程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql Sort aborted: Out of sort memory, consider increasing server sort buffer size的解決方法,abortedincreasing

mysql Sort aborted: Out of sort memory, consider increasing server sort buffer size的解決方法,abortedincreasing

編輯:MySQL綜合教程

mysql Sort aborted: Out of sort memory, consider increasing server sort buffer size的解決方法,abortedincreasing


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

sort_buffer_size=3M
join_buffer_size = 3M

下面是針對16G 內存設置的參數:

sort_buffer_size = 2M
# Sort_Buffer_Size 是一個connection級參數,在每個connection(session)第一次需要使用這個buffer的時候,一次性分配設置的內存。
#Sort_Buffer_Size 並不是越大越好,由於是connection級的參數,過大的設置+高並發可能會耗盡系統內存資源。例如:500個連接將會消耗 500*sort_buffer_size(8M)=4G內存
#Sort_Buffer_Size 超過2KB的時候,就會使用mmap() 而不是 malloc() 來進行內存分配,導致效率降低。
#技術導讀 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;出現filesort
#屬重點優化參數

不過我看到有人將sort_buffer_size設置為10M,我就是因為設置了10M,後來我設置為2-3M,就沒有問題了

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
[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% 左右。
由於是limit 1,因此減弱了buffer size對排序速度的影響。
其實buffer size 越大,所需要進行的sort-merge的次數越少,執行時間越短。視具體的語句而定。
我只是想說明下,並不是越大越好。 也並不存在一個絕對的最佳值。
還是要看具體的業務環境決定值的大小。 對於高並發的應用,還是別設置太大的比較好。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved