基礎編の続きです。
これらのパラメータはMySQLのチューニングをしたい!という人や、
MySQLで性能問題があったため改善したい!という人(それだってチューニングやん、、、)
が確認すべきものです。
【3.チューニングをする人向け】
○ autocommit
MySQLはどんなクエリであれ、DML発行時には自動でコミットがなされるのがデフォルトの挙動です。
(もちろん、beginでトランザクションを明示的に開始している時にはそのトランザクション中では
コミットされませんが)
この機能をOFFにしたい場合にはこのパラメータを変更します。
○ binlog_cache_size
未コミットのトランザクション情報をキャッシュしておけるサイズです。
コミットされたらSQLはバイナリログに書き出されますが、
未コミットのものはキャッシュ上で情報を持っておく必要があるためです。
○ core_file
MySQLが不慮のダウンをした場合にはコアファイルを書き出すことができるよう設定できます。
パフォーマンスに影響が出ることはないですが、
障害解析等に非常に有用です。
ただし、当然ながらMySQLが使用しているメモリサイズ分だけのファイルが書き出されますので、
書き出し先のディスクサイズについては余裕を持っておく必要があります。
なお、少なくともLinuxではこのパラメータを指定するだけでは、
OS側の仕様によりコアファイルを出力することはできません。
/etc/sysctl.confにfs.suid_dumpable=1の指定を入れる必要があります。
ほか、ulimitによる制限や、下記バグの回避など、
考慮すべきことは他にもあります。
Should be able to dump core after setuid() under Linux
また、当然ながら、そもそもコアファイルを読むだけの技術力がないと、
コアファイルを出力したところで無意味です。
○ innodb_buffer_pool_dump_at_shutdown
○ innodb_buffer_pool_load_at_startup
MySQLのウォームスタートアップのためのパラメータで、2つセットで使用します。
MySQLを停止する際、InnoDBのバッファプールの内容をダンプしておき、
MySQLを起動する時、それを読み込む、というものです。
こちらの公式ページに詳細が記されているので、興味のある方はのぞいてみると良いと思います。
○ innodb_data_file_path
InnoDBのデータファイルについての指定です。
ibdata1:12M:autoextend
などと指定されており、
ibdata1がデータファイル名、12Mがデフォルトでのデータファイルサイズ、autoextendが自動拡張をONにする
ということを意味しています。
○ innodb_file_per_table
InnoDBテーブルのデータファイルを、テーブルごとに分割する設定です。
OFFであれば、上述のようなibdata1などのファイルに全てテーブルのデータが保存されますが、
innodb_file_per_tableがONの時には、○○(テーブル名).ibdファイルが作成され、
ここにテーブルのデータが保存されます。
○ innodb_log_file_size
InnoDBテーブルでコミットされたデータは、一旦InnoDBログファイルに書き出され、
その後まとめてデータファイルへの更新が行われます。
このInnoDBログファイルのサイズを指定するパラメータが
innodb_log_file_sizeです。
なぜ直接データファイルを更新せずにInnoDBログファイルに一旦書いたりするかというと、
データファイルの更新は非常に時間的コストのかかる作業だからです。
データファイルを更新する際にはディスクへのランダムアクセスとなりますが、
ログへの記載であれば、シーケンシャルアクセスです。
この違いがあるために、パフォーマンス向上のため、
このような仕様になっているわけです。
興味がある人は、WAL(Write Ahead Log)でググって見ると良いと思います。
○ join_buffer_size
インデックスを使わずに実行するJOIN処理の際、データを保持しておくためのバッファサイズの指定です。
この値を大きくすることでJOIN処理速度の向上が期待できますが、
そんなことより素直にインデックスを追加する方が良いのでは?
と思います。
○ key_buffer_size
MyISAMテーブルのインデックスをキャッシュしているキーバッファのサイズを指定します。
MyISAMテーブルを多用しているシステムであれば非常に重要なパラメータ(それこそ、innodb_buffer_pool_size並みに)
になりますが、
今はMyISAMテーブルは主流では無くなってきており、
MySQL Utilityなどの比較的新しいツールを利用するためには、
MyISAMテーブルを使用しないことが条件になっていたりもするので、
一般的にはあまり重要なパラメータではなくなりつつあります。
○ lower_case_table_names
テーブル名を格納する際、大文字小文字の区別をするか否かを指定します。
0を指定すると大文字小文字区別ありで、
1を指定すると全て小文字に変換して格納されます。
2を指定すると大文字小文字区別ありで格納されますが、比較の際には区別がされません。
大文字小文字の区別をするかどうかはOSの仕様にも依り、
マニュアルいわく、Windowsではこの値を0にしてはいけないそうです。
○ max_allowed_packet
MySQLにクエリを送る際のパケットサイズの最大値です。
初期サイズはnet_buffer_lengthパラメータで指定した値ですが、
BLOB型のデータ等、巨大なデータを送る際にはmax_allowed_packetで指定したサイズまで
パケットのバッファサイズを拡張します。
ここで勘違いしてはならないのは、
あくまでクエリ自身のサイズが問題になっている、と言う点です。
すなわち、いかに大量のテーブルの更新を伴うクエリであっても、
そのSQL文自体の長さのみがここでの規制対象になる、と言うことです。
なので、BLOB型データを扱う際には注意が必要ですが、
それ以外のvarcharやint型のデータを扱う際には
問題が起こることは少ないかと思われます。
○ performance_schema
MySQL5.5より登場した、PERFORMANCE SCHEMAを利用するか否かの設定です。
5.6ではPERFORMANCE SCHEMAはだいぶ見やすくなったという評判で、
性能調査などの観点では大いに活用すべきです。
また、5.7よりデフォルトで使用されるSYS SCHEMAはPERFORMANCE SCHEMAやINFORMATION SCHEMAなどから
情報を取得しているため、SYS SCHEMAを使用する際はONにすることが必須になります。
SYS SCHEMAについては趣旨がずれるので割愛しますが、
MySQLに詳しくない人でもパフォーマンスの評価ができるくらいに
見やすいものになっている(と思う)ので、
是非使ってみてください。
○ query_cache_size
クエリー結果をキャッシュするために確保するメモリサイズを指定します。
デフォルトでは0です。
データベースに対して、画一的なクエリ(全くクエリが同じ、という意味です)を投げる場合には
クエリキャッシュの利用は有効ですが、
クエリが少しでも異なってしまうとクエリキャッシュは使用されないため、
一般的にクエリキャッシュが活用できる例が少ない、ということからデフォルトがOFFになっています。
○ read_buffer_size
インデックスを使わないテーブルスキャンに使うメモリサイズです。
読み取り速度の向上が期待できますが、
このパラメータを大きくするよりは素直にインデックスを使用する方が良いかと思います。
○ read_rnd_buffer_size
ソート後のデータを読み取る際、ディスクシークを回避するためにこのバッファからデータは読み取られます。
特段パラメータを大きくする必要はないことが多いですが、
DWH系の処理などではより大きな値に変更することは有効です。
ただし、グローバル単位で大きな値を割り当てるのはメモリの無駄遣いですので、
必要なセッションにのみ、セッション単位で割り当てるのが良いと思います。
○ sort_buffer_size
ソート実行時に使用するバッファのサイズです。
データサイズが大きい場合、デフォルトのサイズでは収まりきらない可能性がありますので、
データの大きさを見積もった上で値を指定する必要があります。
こちらもグローバル単位で割り当てるのは無駄なことが多いですので、
必要なセッションにのみ割り当てるのが良いかと思います。
○ thread_cache_size
スレッドキャッシュするスレッドの最大数です。
スレッドキャッシュとは、接続終了したスレッドを開放せず、
次の接続してくるスレッドが流用できるようにキャッシュしておくことを指します。
これにより、頻繁にスレッドを生成、消滅させるコストを削減し、
パフォーマンス向上につなげることができます。
大事なパラメータについては以上です
無論、状況によって重要になってくるパラメータは他にもあるので、
都度調べる必要はあるのですが、
ここに書いたパラメータを把握できていれば、
概ねMySQLのサーバパラメータについては把握できている、
と思ってよいと思います。
また、パラメータについて勉強している時に、
必要に迫られてMySQLの仕組みについても同時に勉強できるので、
MySQLの勉強をする時に、まずサーバパラメータから入ってみる、
というのもやり方の1つかも知れませんね。
・・・結構量があるので大変ですけどね、、、笑
==================================================
より基本的なパラメータはこちら
UCO-Tech(MySQLサーバーパラメータ基礎編)