mysql Sort aborted:Out of sort memory,consider increasing server sort buffer sizeの解決方法
4191 ワード
今日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
以下は16 Gメモリのパラメータです.
sort_buffer_size = 2M # Sort_Buffer_Sizeはconnectionレベルのパラメータで、各connection(session)が初めてこのbufferを使用する必要がある場合、設定したメモリを一度に割り当てます.Sort_Buffer_Sizeは大きいほど良いわけではありません.connectionレベルのパラメータなので、設定+高すぎると同時にシステムメモリリソースが消費される可能性があります.たとえば、500個の接続で500*sort_が消費されます.buffer_size(8 M)=4 Gメモリ#Sort_Buffer_Sizeが2 KBを超えると、malloc()ではなくmmap()を使用してメモリ割り当てが行われ、効率が低下します.技術ガイド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を10 Mに設定したのは、10 Mを設定したからです.その後、2-3 Mに設定したので、問題ありません.
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 256 KB and 2 MB where larger values may significantly slow down memory allocation」
本文は主に第3点に対してテストを行います:Sort_Buffer_Sizeが2 KBを超えると、malloc()ではなくmmap()を使用してメモリ割り当てが行われ、効率が低下します.
環境:
パフォーマンスの差をより大きくするために、1 GBメモリのFedora仮想マシンを使用してテストします.
テストテーブル構造:
1 w行のテーブル、テーブル構造
+-------+------------------+------+-----+---------+----------------+ | 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は250 K、512 K、3 Mで、次の文を実行して実行時間を確認します.1. sql_no_Cacheはquery cacheの有効化を防止します.2.limit 1は、実行時間に占めるソートの比重を減らすために、メモリ割り当てによる影響をより多く体現する.文explainの結果はfilesortであり、sort_が使用されていることを確認します.buffer
テスト結果:
じっこうじかん
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の回数が少なくなり、実行時間が短くなります.具体的な文によって異なります.私はただ説明したいだけです.大きくなればなるほどいいわけではありません.絶対的な最適値も存在しません.やはり具体的なビジネス環境によって値の大きさが決まります.高同時性のアプリケーションでは、あまり大きく設定しないほうがいいです.
sort_buffer_size=3M join_buffer_size = 3M
以下は16 Gメモリのパラメータです.
sort_buffer_size = 2M # Sort_Buffer_Sizeはconnectionレベルのパラメータで、各connection(session)が初めてこのbufferを使用する必要がある場合、設定したメモリを一度に割り当てます.Sort_Buffer_Sizeは大きいほど良いわけではありません.connectionレベルのパラメータなので、設定+高すぎると同時にシステムメモリリソースが消費される可能性があります.たとえば、500個の接続で500*sort_が消費されます.buffer_size(8 M)=4 Gメモリ#Sort_Buffer_Sizeが2 KBを超えると、malloc()ではなくmmap()を使用してメモリ割り当てが行われ、効率が低下します.技術ガイド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を10 Mに設定したのは、10 Mを設定したからです.その後、2-3 Mに設定したので、問題ありません.
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 256 KB and 2 MB where larger values may significantly slow down memory allocation」
本文は主に第3点に対してテストを行います:Sort_Buffer_Sizeが2 KBを超えると、malloc()ではなくmmap()を使用してメモリ割り当てが行われ、効率が低下します.
環境:
パフォーマンスの差をより大きくするために、1 GBメモリのFedora仮想マシンを使用してテストします.
テストテーブル構造:
1 w行のテーブル、テーブル構造
+-------+------------------+------+-----+---------+----------------+ | 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は250 K、512 K、3 Mで、次の文を実行して実行時間を確認します.1. sql_no_Cacheはquery cacheの有効化を防止します.2.limit 1は、実行時間に占めるソートの比重を減らすために、メモリ割り当てによる影響をより多く体現する.文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の回数が少なくなり、実行時間が短くなります.具体的な文によって異なります.私はただ説明したいだけです.大きくなればなるほどいいわけではありません.絶対的な最適値も存在しません.やはり具体的なビジネス環境によって値の大きさが決まります.高同時性のアプリケーションでは、あまり大きく設定しないほうがいいです.