MySQLが使用するメモリを計算するのに毎回手動で計算するのはめんどいですよね。
なんかパールとかで設定ファイルよみこんで算出するツールとかもあるのですが、
パールいれるのすらめんどくさい。。。
ということでSQL一発で表示できるのを作ってみた。
詳細は以下。

メモリ計算式について

rental.off-soft.net

 

3 Users

MySQLの必要なメモリ量は？ | phpとmysql が使えるレンタルサーバー・ドメイン選び...

http://rental.off-soft.net/323.html

必要メモリ量＝グローバルバッファのサイズ＋（各スレッドのバッファサイズの合計 × 最大接続数(max_connections)）
各スレッドのバッファサイズの合計とは、以下の値の合計値です。
sort_buffer_size
myisam_sort_buffer_size
read_buffer_size
join_buffer_size
read_rnd_buffer_size
グローバルバッファのサイズは、以下の値の合計値です。
key_buffer_size
innodb_buffer_pool_size
innodb_log_buffer_size
innodb_additional_mem_pool_size
net_buffer_length
※実践ハイパフォーマンスMySQL による

とあるのだが、一般的にいわれてる計算式はさらにそれに+query_cache_sizeがプラスされているようだし、
そもそも実践ハイパフォーマンスMySQL 第2版
みてみたけどそのような式の記述がみあたらない。
どこに書いてあったのだろう。。。
他にも調べると以下のサイト様の式がよく出たりする。

antas.jp

 

35 Users

57 Pockets

http://antas.jp/blog/ina/archives/2007/05/mysql_1.html

http://antas.jp/blog/ina/archives/2007/05/mysql_1.html

MySQLで使用するメモリの計算式は次のとおりです。
max_connections *
(sort_buffer_size
　　＋ join_buffer_size
　　＋ read_buffer_size
　　＋ read_rnd_buffer_size
　　＋ net_buffer_length
　　＋ max_allowed_packet) <-- カッコ内は1接続あたりの使用メモリ ＋ key_buffer_size ＋ query_cache_size ＋ innodb_buffer_pool_size ＋ innodb_additional_mem_pool_size ＋ innodb_log_buffer_size

max_allowed_packetはメモリ使用量の計算式としては適切ではないらしいので削除。
myisam_sort_buffer_sizeについての記述もあった

また、myisam_sort_buffer_sizeもスレッドバッファとして加算する方もいらっしゃるようです。
これは、MyISAMでREPAIR TABLE, CREATE INDEX, ALTER INDEX文を実行したとき使われるバッファで、デフォルトは8MB。
全文検索エンジンTritonnを使用する場合はなんと1GBとすることが推奨されています。
ですが、同時接続100のとき合計で100GB使用するかというと、そうではないので、接続時に必ずmyisam_sort_buffer_size分を確保するわけではないようです。
ということで、考え得る最大値という意味では、max_allowed_packetやmyisam_sort_buffer_size分を加算することは間違いではないのですが、すべての通信接続でこれらの値分を確保するわけではないので、パラメータチューニングにおける計算式からは削除しました。

InnoDBマンセーに構築してるので例にみならってmyisam_sort_buffer_sizeは計算にいれないことに。
tmp_table_sizeについて
以下のサイト様様によるとtmp_table_sizeもなんか必要そうな雰囲気を醸し出してる

www.mysqlpracticewiki.com

 

60 Pockets

http://www.mysqlpracticewiki.com/index.php/MySQLサーバが消費するメモリ#.E3.83...

http://www.mysqlpracticewiki.com/index.php/MySQLサーバが消費するメモリ#.E3.83.A1.E3.83.A2.E3.83.AA.E4.B8.8A.E3.81.AB.E4.BD.9C.E6.88.90.E3.81.95.E3.82.8C.E3.82.8B.E3.83.86.E3.83.B3.E3.83.9D.E3.83.A9.E3.83.AA.E3.83.86.E3.83.BC.E3.83.96.E3.83.AB

メモリ上に作成されるテンポラリテーブル
複雑なJOINやGROUP BYの処理などは、テンポラリテーブルを用いて行われる。テンポラリテーブルはまずMEMORYストレージエンジンとして作成されるが、–tmp-table-sizeバイトまたは–max-heap-table-sizeバイトを超えるとMyISAMに変換される。もちろん、MyISAMはディスクアクセスを発生させるため処理はスローダウンしてしまう。それを避けるためには、それらの値を大きくすると良い。どの程度まで大きく出来るかは実際に搭載されているメモリ量と相談である。

www.kinusati.net

 

17 Users

7 Pockets

http://www.kinusati.net/2011/01/27/mysqlお勉強メモサーバ設定項目/

http://www.kinusati.net/2011/01/27/mysqlお勉強メモサーバ設定項目/

tmp_table_size
一時テーブルにおけるメモリテーブルの最大値。メモリテーブルはmax_heap_table_sizeも併せて定義する必要有り。
メモリサイズを超えた場合はMyISAM形式でディスク上にテーブルを作成する。これは避けるべき!

しかし、以下のサイト様だとtmp_table_sizeはスレッドにもグローバルにも属していない
[MySQL] パフォーマンス関連メモ
うーん、大事そうではあるけどとりあえず計算式からは外しておくか。。
もし計算式としていれるならグローバルのほうか？
各パラメーターの詳細については以下のサイトが参考になりそうです。
MySQLのメモリ関係のシステム変数
算出式決定！
というわけでとりあえず計算式を以下で決定した。

max_connections *
(sort_buffer_size
　　＋ join_buffer_size
　　＋ read_buffer_size
　　＋ read_rnd_buffer_size
　　＋ net_buffer_length )
＋ key_buffer_size
＋ query_cache_size
＋ innodb_buffer_pool_size
＋ innodb_log_buffer_size
＋ innodb_additional_mem_pool_size

一発総メモリ算出SQL

するとこんな感じでバイト単位で出てきます。

*************************** 1. row ***************************
GLOBAL_BUFFER_SIZE: 286277632
THREAD_BUFFER_SIZE: 2359296
TOTAL_MEMORY_SIZE: 475021312
TOTAL_MEMORY_SIZE_kb: 463888 <- KBで表示 TOTAL_MEMORY_SIZE_mb: 453.015625　<ー MBで表示 TOTAL_MEMORY_SIZE_gb: 0.442398071289062 <-ギガで(ry

なお、このSQLは見ての通りINFORMATION_SCHEMAのGLOBAL_VARIABLESテーブルをみているのだが、
MySQL 5.1.12以降のバージョンでないと使えないのでご注意。
計算式については、なんだかあちこち諸説がある感じなので、これ違うだろみたいなツッコミがあれば
いつでもお待ちしております。
P.S
MySQL 5.1.12以降じゃないと使えないのはちょっと不便だな・・・と思って調べてたら
もっと簡単に出来る方法がありました。
なんと、ステータス自体は@@を2つつけるだけで参照できたっぽいです。
以下がどのバージョンでも使えるバージョン

select
@@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.INNODB_ADDITIONAL_MEM_POOL_SIZE + @@GLOBAL.NET_BUFFER_LENGTH as GLOBAL_BUFFER_SIZE,
@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE as THREAD_BUFFER_SIZE,
@@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.INNODB_ADDITIONAL_MEM_POOL_SIZE + @@GLOBAL.NET_BUFFER_LENGTH
+ (@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE) * @@GLOBAL.MAX_CONNECTIONS AS TOTAL_MEMORY_SIZE,
(@@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.INNODB_ADDITIONAL_MEM_POOL_SIZE + @@GLOBAL.NET_BUFFER_LENGTH
+ (@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE) * @@GLOBAL.MAX_CONNECTIONS)/1024 AS TOTAL_MEMORY_SIZE_kb,
(@@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.INNODB_ADDITIONAL_MEM_POOL_SIZE + @@GLOBAL.NET_BUFFER_LENGTH
+ (@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE) * @@GLOBAL.MAX_CONNECTIONS)/1024/1024 AS TOTAL_MEMORY_SIZE_mb,
(@@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.INNODB_ADDITIONAL_MEM_POOL_SIZE + @@GLOBAL.NET_BUFFER_LENGTH
+ (@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE) * @@GLOBAL.MAX_CONNECTIONS)/1024/1024/1024 AS TOTAL_MEMORY_SIZE_gb
\G