MySQLをチューニング３：MySQLの設定見直す

2015/04/02

データベースが重いからMySQLをチューニングするに書いた四天王の三人目「MySQLの設定見直す」と戦います。
「二人目は？」という声が聞こえてきそうですが、
たぶん一人目の時に一緒に倒したのでしょう。
アナタトテモツヨイ！（手抜き

というわけで、indexなどDB構造の最適化やクエリを改善してもなお重い場合は、
いよいよMySQLの設定そのものをチューニングすることになります。

MySQLの設定とは？

おもに、MySQLにメモリやキャッシュ、パケットを
どれだけ使わせるかという内容になります。

MySQLの設定ファイルはmy.cnfですが、どこにあるんだろう？
とくにLaravelとかフレームワーク使っていると、場所が分かりません。
無い場合もあります。
逆にあちこちにたくさんmy.cnfがあったりすることもあります。
もう大変。

れさく「えーと・・・これだ！」
my.cnf「残像だ」

れさく「じゃあお前かッ！くらえいッ！　vi my.cnf」
my.cnf「・・・いつからconfファイルだと思っていた？」

こんな無益な戦いはよくありません。

まずは索敵。
「どこにおけばいい？」と聞くにはこれ。
mysql --help | grep .cnf

見つかればそれを開いて編集。
じゃあ無い場合は？
無いなら作る。
どこに？
/etc/が一番良いらしい。
etcに移動するよ。
cd /etc/
my.cnf無いから作るよ。
touch my.cnf
中身は当然空だから自分で書くよ。
vi my.cnf

なんかそれ面倒だなあという私のようなあなた。
そんなあなたはmy.cnfを作らなくてもmysqlのコマンド「SET GLOBAL 〜」で諸々設定変更しちゃいましょう。
一時的な変更なので、MySQL再起動とかしたら設定は消えて元に戻ります。
検証だからいいんじゃない？
そんなの嫌だという人はmy.cnfを編集しましょう。

変更後の確認は
show variables;

それぞれの値の推奨値はこちらがとても参考になり。

こちらもオススメ。グローバルバッファとスレッドバッファの解説もあり。

設定したらMySQLを再起動しましょう。
停止　mysql.server stop
起動　mysql.server start
コマンドは実行する場所を問わない。

以下メモ。
むかしむかし、これで3分の処理が1分半に短縮できたことがある。

key_buffer_size
推奨；256MB
現状；8388608　（8MB程度
しかしkey_buffer_sizeはMyISAM用だった。

innodb_buffer_pool_size
推奨；総メモリの70-80％ほど DSAS値512MB
現状；134217728　（128MB すなわちメモリ4Gに対し3%
改善；mysql>set global innodb_buffer_pool_size = 3435973836 indoorはkey_buffer_sizeを使わない分、大いに割り当てましょうでもとりあえず2Gにした。
改善；mysql>set global innodb_buffer_pool_size = 536870912

table_open_cache
推奨；レコード数百程度なら1024
現状；2000

thread_cache_size
推奨；少なくとも16
現状；9
改善；mysql>set global thread_cache_size = 16

innodb_log_file_size
推奨；64-512MBくらいで調整 DSAS値128MB
現状；50331648　（48MB
改善；mysql>set global innodb_log_file_size = 134217728

innodb_flush_log_at_trx_commit
推奨；2。0は絶対に回避せよ。1がディスク、2がメモリ、0がとらない。
現状；1

max_allowed_packet
推奨；1MBほど
現状；4194304　（4MB

join_buffer_size
推奨；1MBほど DSAS値256KB
現状；262144　（0.2MB

- Laravel, MySQL