masalibの日記

システム開発、運用と猫の写真ブログです

mysqlのメモリの計算式(エクセルあり)

対象になる人

 mysqlをちょっと知っている人
 詳細に設定したい人は参考URLに飛んでください

経緯

VPSでサービスを展開しなければならなかった
ただ複数サービスをのせて「メモリが足りるのか」計算しなければならなかった
(金だせよと思うがそれは置いといて)

前提

細かいメモリは無視www

計算式について

必要メモリ量=
(各スレッドのバッファサイズの合計× 最大接続数(max_connections)) +グローバルバッファのサイズ

現状を調べる方法

phpmyadminのシステム変数で取得するまたはコマンドで取得する
もしphpmyAdminがない場合は、下記のコマンドで
 show global variables like '%変数名%';

 例
 show global variables like '%max_connections%';
取得する

各スレッドのバッファサイズの合計とは、以下の値の合計値

変数 サイズ 見やすいサイズ
sort_buffer_size 2097152 2048Kバイ
myisam_sort_buffer_size 8388608 8192Kバイ
read_buffer_size 262144 256 Kバイ
join_buffer_size 131072 128 Kバイ
read_rnd_buffer_size 262144 256Kバイ
合計 -- 10,8M

各スレッドのバッファサイズの合計は約10.8Mになる
バッファサイズ×max_connectionsが各スレッドの最大メモリ数なので
約1080Mになる

変数 サイズ 見やすいサイズ
key_buffer_size 8388608 8192Kバイ
innodb_buffer_pool_size 134217728 131072Kバイ
innodb_log_buffer_size 8388608 8192Kバイ
innodb_additional_mem_pool_size 8388608 8192Kバイ
net_buffer_length 8192 8Kバイ
合計 -- 150M


最大使用料は最大1230Mになる
これがデフォルトの設定です
VPSなどメモリが少ないサーバーだとこの1230Mが辛いです
なのでConnection数などを調整した方がいいです
innodb_buffer_pool_sizeは増やした方がいいと思います


計算式は下記のエクセルに仕込みました
変更したいときに使ってください

mysqlのメモリの計算式.xlsx - Google ドライブ


なお、数値が間違えていても責任は取りませんので
自己責任でお願いします

もう1つデフォルト値で腐っているのは

wait_timeoutです

接続したClientが何もせずにいるときに、Connectionを切断する時間。
デフォルトが28800秒(8時間)です
どうしてこの数字にした?と問いかけたいぐらい長い数値です
特にWEBサーバーでの接続の場合、Timeoutは大体 60~120秒です
Webサーバーのタイムアウトになってるため、
DBのDefaultの値は大きい数値である。

自分は28800 => 60に変更した

詳細な設定を知りたい人は下記を参照してください
http://time-complexity.blogspot.jp/2013/10/mysqlmycnf.html
http://tetsuyai.hatenablog.com/entry/20111006/1317873012
http://sawara.me/mysql/1428/

mysqlの記事ってあまり書いてないのね・・
masalib.hatenablog.com

masalib.hatenablog.com


今日の猫