引越しました!

http://blog.mogmet.com/blog-entry-55.html

へアクセスしてください。

スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

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

MySQLが使用するメモリを計算するのに毎回手動で計算するのはめんどいですよね。

なんかパールとかで設定ファイルよみこんで算出するツールとかもあるのですが、

パールいれるのすらめんどくさい。。。


ということでSQL一発で表示できるのを作ってみた。


詳細は以下。












メモリ計算式について



以下のサイト様から引用すると
MySQLの必要なメモリ量は?
必要メモリ量=グローバルバッファのサイズ+(各スレッドのバッファサイズの合計 × 最大接続数(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 メモリ使用量の計算式
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もなんか必要そうな雰囲気を醸し出してる

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


きぬろぐ: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


式を元に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 <- 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


テーマ : サーバ
ジャンル : コンピュータ

コメントの投稿

非公開コメント

管理人のみ閲覧できます

このコメントは管理人のみ閲覧できます

参考にさせてもらいました

すばらしい!参考にさせてもらいました。
スポンサーリンク
FC2カウンター
最新記事
最新コメント
カテゴリ
アマゾンドリンク
検索フォーム
リンク
ブロとも申請フォーム

この人とブロともになる

アクセスランキング
[ジャンルランキング]
コンピュータ
2287位
アクセスランキングを見る>>

[サブジャンルランキング]
未設定
--位
アクセスランキングを見る>>
バックリンク
最新トラックバック
上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。