【MySQL】総メモリー使用量を算出するSQL作ってみた

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

メモリ計算式について

以下のサイト様から引用すると

必要メモリ量＝グローバルバッファのサイズ＋（各スレッドのバッファサイズの合計 × 最大接続数(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版

みてみたけどそのような式の記述がみあたらない。
どこに書いてあったのだろう。。。
他にも調べると以下のサイト様の式がよく出たりする。

MySQLで使用するメモリの計算式は次のとおりです。
max_connections *
(sort_buffer_size
＋ join_buffer_size
＋ read_buffer_size
＋ read_rnd_buffer_size
＋ net_buffer_length
＋ max_allowed_packet)

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もなんか必要そうな雰囲気を醸し出してる

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

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

式を元にSQLを作ってみた。

select
KEY_BUFFER_SIZE + QUERY_CACHE_SIZE + INNODB_BUFFER_POOL_SIZE + INNODB_LOG_BUFFER_SIZE + INNODB_ADDITIONAL_MEM_POOL_SIZE as GLOBAL_BUFFER_SIZE,
SORT_BUFFER_SIZE + JOIN_BUFFER_SIZE + READ_BUFFER_SIZE + READ_RND_BUFFER_SIZE + NET_BUFFER_LENGTH as THREAD_BUFFER_SIZE,
KEY_BUFFER_SIZE + QUERY_CACHE_SIZE + INNODB_BUFFER_POOL_SIZE + INNODB_LOG_BUFFER_SIZE + INNODB_ADDITIONAL_MEM_POOL_SIZE
+ (SORT_BUFFER_SIZE + JOIN_BUFFER_SIZE + READ_BUFFER_SIZE + READ_RND_BUFFER_SIZE + NET_BUFFER_LENGTH) * MAX_CONNECTIONS AS TOTAL_MEMORY_SIZE,
(KEY_BUFFER_SIZE + QUERY_CACHE_SIZE + INNODB_BUFFER_POOL_SIZE + INNODB_LOG_BUFFER_SIZE + INNODB_ADDITIONAL_MEM_POOL_SIZE
+ (SORT_BUFFER_SIZE + JOIN_BUFFER_SIZE + READ_BUFFER_SIZE + READ_RND_BUFFER_SIZE + NET_BUFFER_LENGTH) * MAX_CONNECTIONS)/1024 AS TOTAL_MEMORY_SIZE_kb,
(KEY_BUFFER_SIZE + QUERY_CACHE_SIZE + INNODB_BUFFER_POOL_SIZE + INNODB_LOG_BUFFER_SIZE + INNODB_ADDITIONAL_MEM_POOL_SIZE
+ (SORT_BUFFER_SIZE + JOIN_BUFFER_SIZE + READ_BUFFER_SIZE + READ_RND_BUFFER_SIZE + NET_BUFFER_LENGTH) * MAX_CONNECTIONS)/1024/1024 AS TOTAL_MEMORY_SIZE_mb,
(KEY_BUFFER_SIZE + QUERY_CACHE_SIZE + INNODB_BUFFER_POOL_SIZE + INNODB_LOG_BUFFER_SIZE + INNODB_ADDITIONAL_MEM_POOL_SIZE
+ (SORT_BUFFER_SIZE + JOIN_BUFFER_SIZE + READ_BUFFER_SIZE + READ_RND_BUFFER_SIZE + NET_BUFFER_LENGTH) * MAX_CONNECTIONS)/1024/1024/1024 AS TOTAL_MEMORY_SIZE_gb
from
(select VARIABLE_VALUE as SORT_BUFFER_SIZE from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'SORT_BUFFER_SIZE') as table1,
(select VARIABLE_VALUE as READ_BUFFER_SIZE from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'READ_BUFFER_SIZE') as table3,
(select VARIABLE_VALUE as JOIN_BUFFER_SIZE from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'JOIN_BUFFER_SIZE') as table4,
(select VARIABLE_VALUE as READ_RND_BUFFER_SIZE from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'READ_RND_BUFFER_SIZE') as table5,
(select VARIABLE_VALUE as KEY_BUFFER_SIZE from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'KEY_BUFFER_SIZE') as table6,
(select VARIABLE_VALUE as INNODB_BUFFER_POOL_SIZE from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'INNODB_BUFFER_POOL_SIZE') as table7,
(select VARIABLE_VALUE as INNODB_LOG_BUFFER_SIZE from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'INNODB_LOG_BUFFER_SIZE') as table8,
(select VARIABLE_VALUE as INNODB_ADDITIONAL_MEM_POOL_SIZE from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'INNODB_ADDITIONAL_MEM_POOL_SIZE') as table9,
(select VARIABLE_VALUE as NET_BUFFER_LENGTH from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'NET_BUFFER_LENGTH') as table10,
(select VARIABLE_VALUE as MAX_CONNECTIONS from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'MAX_CONNECTIONS') as table11,
(select VARIABLE_VALUE as QUERY_CACHE_SIZE from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'QUERY_CACHE_SIZE') as table12
\G

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

*************************** 1. row ***************************
GLOBAL_BUFFER_SIZE: 286277632
THREAD_BUFFER_SIZE: 2359296
TOTAL_MEMORY_SIZE: 475021312
TOTAL_MEMORY_SIZE_kb: 463888

なお、この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