今回はMySQLのサーバーパラメータについて書いてみます。
・・・書いている記事の順番がレベル的にも内容的にもてんでばらばらなのは、
ひとえに私が書きたいことを書いてるからです。
ゆるしてください笑
いつか体系立ってまとめるかもしれません。
サーバーパラメータは、原則my.cnfという設定ファイルに書きます。
Windowsではmy.iniというらしいです。
my.cnfは決められた場所に配置しておけば、MySQLの起動時に自動で読み込んでくれます。
決められた場所とは、下記4つです。
1./etc/my.cnf
2./etc/mysql/my.cnf
3./usr/etc/my.cnf
4.~/.my.cnf
この情報は、mysqlコマンドの–helpオプションで確認できます。
オプションファイルを読むのは1から順になっており、
たとえば/etc/my.cnfでdatadir=/var/lib/mysqlと書いてあり、
/usr/etc/my.cnfにdatadir=/data/mysqlと書いてあった場合、
後から読んだパラメータで上書きされ、
実際にdatadirの値は/data/mysqlとなります。
なお、/etc/my.cnfに設定を記載するのが慣例となっており、
残りの3つを使うと、混乱を招くことになるのであまりお勧めしません。
ここで、1つ注意です。
MySQL 5.6では、有名なバグがあって、
RPMパッケージ導入をした際などに、
basedirの配下にmy.cnfを作ってしまいます。
デフォルトではbasedirは/usrとなっているので、
/usr/my.cnf
のバグとして認識されています。
この/usr/my.cnfにはsql_modeの指定が書かれており、
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
となっています。
そして、タチの悪いことに、この/usr/my.cnfは
自動読み込みファイルの対象になっており(mysql –helpでは表示されないのに、です!)、
さらに一番最後に読み込まれます(=1番優先度が高い)。
このため、/etc/my.cnfでいくらsql_modeを指定しても、
NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
のままになってしまう、という悲劇がここに発生するわけです。
知らないとドツボにハマる可能性があるので、
頭の片隅に入れておいてください。
(5.7では解消されているようです)
話がそれましたね。
ほかにもMySQLのサーバーパラメータを指定する方法はあります。
・MySQL起動後に動的に変更する(変更できるパラメータのみ)
・mysqldプロセス起動時に引数として与える。
・mysqldプロセス起動時に–defaults-fileで設定ファイルを指定する。
上の2つは永続設定ではない(Oracleで言えば、scope=MEMORY)ので、
注意が必要です。
–defaults-fileによる指定は、検証環境などで複数のMySQLプロセスを利用する場合などに
便利です。
さて、それでは肝心のパラメータにどんなものがあるか見ていきましょう。
私の独断と偏見により重要度を3段階に分けました。
参考にしてみてください。
ぱっと思いついたパラメータについて書いているだけなので、
「ここにないから重要でない!」
とは思わずに、
「ここに書いてあるなら重要なんだろう」
という感じに眺めてください。
なお、ここでは5.6.26であることを前提に書いています。
概ね5.6であれば変更は少ないと思いますが、
メジャーバージョンが異なる場合は参考程度に見てください。
【1.超重要!】
MySQLに時間を割いている余裕はない!という方でも、
チェックしておいてほしいパラメータです。
これらを知らないとMySQLの性能が極端に低下したり、
障害が起こった時に取り返しのつかないことになる可能性が高くなったりします。
○ datadir
MySQLのデータをどこに保持するかを指定するパラメータ。
RPMパッケージではデフォルトで/var/lib/mysqlが指定されているので、
datadirのパラメータを知らなかった、という人はここにデータが格納されていることになります。
しかし、一般的にデータはルートディスクと同じディスクには持たないため、
ここを指定しない構成はかなり考えにくいです。
○ expire_logs_days
○ log_bin
○ log_bin_basename
バイナリログ関係のパラメータ。
バイナリログを知らない方のために補足しておきますと、
バイナリログとは、MySQLで実行した更新系クエリをそのまま書いてあるログのことです。
ロールフォワードリカバリに使える点はOracleのアーカイブREDOログにそっくりですが、
その他にも、MySQLのレプリケーションのベースになっています。
MySQLのレプリケーションはMasterのバイナリログにあるクエリをSlaveで実行させることによって
MasterとSlaveが同じデータを持つようにしているからです。
log_binはバイナリログを出力するかどうか(ON/OFF)
log_bin_basenameはバイナリログ名のベースです。
(バイナリログは、log_bin_basename.XXXXXX(Xは連番)という名前です)
expire_logs_daysはバイナリログを何日保管するかを指定します。
バイナリログは更新系クエリ全てを記録し続けますので、
定期的に削除しないと、あっというまにディスクを圧迫します。
デフォルトでは0(=削除しない)となっているので、
何らかの値を指定する必要があるでしょう。
通常、7日程度であれば十分と思いますが、
更新クエリの頻度、フルバックアップを取るタイミングなどによって
変更すると良いと思います。
○ log_error
エラーログを出力するディレクトリ、ファイルを指定します。
なにかMySQLで問題が起こった時は真っ先に確認するファイルなので、
これがどこにあるか分からないと障害発生時に非常に困ったことになります。
○ innodb_buffer_pool_size
ものすごく雑に言えば、MySQLがどれだけのメモリを使うことを許可するか、です。
Oracleで言えば、MEMORY_TARGETに近いです(自動メモリ管理などという高等機能はありませんが)。
もう少し正確に言うと、InnoDB型のテーブル(ほとんどのテーブルはそうです)の
データやインデックスをキャッシュしておくメモリサイズです。
MySQLに限らず、およそDBMSはいかにメモリを効率よく使うか、
ということはデータベースの性能に直結します。
あまり詳細にチューニングをする気はなくても、
この値をそれなりの値にしておかないと、性能は極めて低くなります。
データベース専用サーバにするのであれば、
物理メモリの70%ほどを割り当てると良いのではないでしょうか。
○ max_connections
MySQLへの最大接続数を指定します。
アプリケーションからの接続が多い場合、
この値を大きくしておかないとアプリケーションからアクセスした際に
Connection Refusedと言って接続をはねられてしまいます。
即システム障害につながりますので、
アプリケーションからどのくらいの接続が最大であるのかを
見積もって、それよりも大目の値を指定してください。
デフォルトは151です。
なお、接続待機しているのにもわずかながらメモリは使用するらしいので、
あまり極端に大きな値を指定しないことを勧めます。
【2.まあまあ重要】
普通にMySQLを利用し、ある程度仕組みも理解しておきたい、と言う方は
このレベルまで理解して頂きたいです。
このレベルまで分かっていれば、初歩的なミスは犯さないはずです。
○ binlog_format
この重要度レベルで抑えておく必要があるか微妙ですが、一応。
バイナリログの記録のフォーマットを指定します。
row、mixed、statementの3種類が指定できます。
rowでは、実行したクエリによって変更された行を記録します。
statementでは実行したクエリを記録します。
mixedは、rowとstatementのいいとこどりです(後で説明します)。
バイナリログに更新系クエリを記録する際、
たとえば、select now();とかやった場合、
実行時刻によって結果が変わりますよね。
これはレプリケーションをしている際には重要な問題で、
statement形式にすると、実行したクエリをそのまま記載するので、
マスターとスレーブで値が異なる可能性が出てきてしまいます。
これに対してrow形式であればそんな心配は無用ですが、
その代わり行全体を記録するので、
バイナリログが膨大な大きさになります。
mixedでは、statement形式では値が異なってしまう可能性のあるクエリについては
row形式、そうでないクエリについてはstatement形式、と
MySQL側で判断して記録してくれます。
なので、特に理由がなければmixedにするのがお勧めです。
○ character_set_client
○ character_set_server
これらはクライアントやサーバの文字コードを指定するパラメータです。
sjis、EUC、UTF-8など、お好きな文字コードを指定してください。
○ enforce_gtid_consistency
GTIDというバイナリログ記録方式を利用できるようMySQLの利用に制限をかけるパラメータです。
たとえば、GTIDレプリケーションではトランザクション制御ができる必要があるので、
MyISAMストレージエンジンの利用を禁止する、などです。
GTIDを利用しないのであれば無用の長物ですが、
近年GTIDを利用したツールがOracle社より提供され始めているので、
頭には入れておいたほうが良いと思います。
○ general_log
○ general_log_file
一般クエリログについてのパラメータです。
一般クエリログというのは、参照系、更新系問わず、全ての実行されたクエリを記録するログです。
大量のログが書き込まれることになりますので、
実運用時にはOFFにしておくことを強く勧めます。
障害の再発試験時、検証時などに利用します。
general_logで一般クエリログのON/OFFを、
general_log_fileで一般クエリログファイル名を指定します。
○ gtid_mode
GTIDモードでバイナリログを記載するかどうかを指定します。
enforce_gtid_consistencyの項でも書いたとおり、GTIDを利用する機会は多いので、
このパラメータの存在は知っておいて損はないです。
○ license
読んで字のごとく、ライセンスの種類です。
GPL版(無料版)、commercial版(有料版)があります。
○ log_slave_updates
MySQLサーバがレプリケーションのスレーブとして動作している際、
マスターから伝播されてきて自分で実行したクエリを
バイナリログに書くかどうかを指定します。
多段レプリケーション(Master→SlaveかつMaster→Slaveのような構成)を組む際などには
留意する必要があります。
OFFにしていると、mysqlfailoverやMHAなどでマスターのfailoverが起こった際、
新マスターには過去のマスターで実行したクエリのバイナリログが存在しないことになるので、
データの冗長性確保の要件がある場合などは注意が必要です。
○ long_query_time
○ slow_query_log
○ slow_query_log_file
スロークエリログファイル関係のパラメータ。
スロークエリログは、実行に一定以上の時間がかかったクエリを自動記録するログで、
クエリのチューニングなどに活かすことができます。
slow_query_logでスロークエリログのON/OFFを、
slow_query_log_fileでスロークエリログのファイル名を、
long_query_timeでスロークエリログ出力の閾値(秒)を、
それぞれ指定します。
○ pid_file
MySQLのPIDファイルのパスを指定します。
ここで指定したパスをOSのmysqlユーザがR/Wする権限を持っていないと、
MySQLが起動できないのでご注意ください。
○ read_only
読んで字のごとくです。
レプリケーションを行っている際のスレーブなどに指定し、
スレーブへのデータ更新を禁止し、レプリケーションのデータ不整合を防ぐ、
などの使い道があります。
○ server_id
レプリケーションをする際に、レプリケーション構成内で
MySQLサーバを識別するためにつけるIDです。
同一レプリケーション構成内で一意になっている必要があります。
○ socket
MySQLのソケットファイルのパスを指定します。
Linuxのソケット通信を利用する場合、このファイルを介してMySQLに接続します。
指定するパスによって性能が変わる、などはありませんが、
MySQLの初期起動時に失敗した際、接続できない際はこれが絡んでいることもあるので、
頭の片隅に入れておいてください。
○ tx_isolation
トランザクション分離レベルを指定します。
トランザクション分離レベルを知らない!
と言う方はWikipediaに説明があるので読んでみてください。
他のRDBMSと異なり、デフォルトはREPEATABLE-READです。
【3.チューニングをする人向け】
・・・書く気はあったんです。
ホントです。
でも、ここまで書くのにあまりにも疲れてしまったので、
続きは次の記事、とさせてください。
一番肝心なところ書けなくてすみません、、、
続きはまた近々書きます。
今日はここまで。
======================================================
続き、書きました。
UCO-Tech(MySQLサーバーパラメータ応用編)