開発者向け!MySQL my.cnfの設定

MySQLのmy.cnf設定の実態

初めてMySQLを触るエンジニアや、MySQLは使うだけであまり細かいことを気にしていない開発エンジニアは、真面目にパフォーマンスをよくしたいと考えた時に、あるいは障害などが発生してしまった時にMySQLのパラメーター設定を考え始めると思います。つまりmy.cnfの設定をどうしたらいいのかということですね。

特に今はAWSを使うことが多くなり、MySQLもRDSを使うことが多くなってきていてインフラエンジニアなしで環境構築してしまうことも多いため、今までであれば開発者はあまりMySQLの設定は考えなくてもよかったのですがそうはいかなくなっているのではないでしょうか。
色々なところにmy.cnfの設定サンプルがころがっていますが、実際に自分たちのサービスではどうしたらいいのかわからないままなんとなく真似て使ってみて失敗する、パフォーマンスが出ないということが多いのではないでしょうか。

インフラエンジニアと開発エンジニアとmy.cnf

インフラエンジニアやDBAなど、特に自社サービスのMySQLを任されている人にとってみれば、my.cnfの設定を細かく考えるのは当然のことでしょう。ただし、開発エンジニアに取ってみれば、MySQLの細かい設定よりもシステム開発の方が大事ですので、後回しになってしまうのは仕方がありません。

開発エンジニアの場合は、my.cnfの設定は後回しにしていたけれど、全然システムのパフォーマンスが上がらなくて、昔であればサーバの性能が悪いのかと思っていたけれど、AWSのインスタンスは特に問題なさそうなのでMySQLが原因なのかなということで設定を見直すパターンもあるかと思います。

my.cnfでパフォーマンスが上がる確率は?

勘違いしやすいのは、my.cnfの設定を変えたらパフォーマンスが大幅に良くなるのでは?ということです。
ただし、今まで経験した中では大体確率はこんなものです。

  • オンプレ環境の場合 ・・・ 30%くらい
  • RDS環境の場合 ・・・ 5%くらい

ここでパフォーマンスが上がるパターンとしては、ほとんどmysqlをデフォルトパラメータでやっていた場合です。RDS環境で改善する可能性が低い理由は、RDS環境の場合はデフォルトが環境に合わせてAWS側で考えられているためです。

  • システムの性能が悪い原因の比率は一般的に下記くらいです。
    • インフラ起因 10%
    • アプリケーション設計起因 30%
    •  MySQLのテーブル設計・インデックス不足 30%
    •  my.cnfの設定 30%

この割合は必ずしも正確ではないかもしれませんが、データベースを利用したシステムの性能が異常に悪い場合、ほとんどはインデックスがきちんと貼られていない、アプリケーションが大量のクエリを発行していることが原因で、パラメータ設定が悪いことはそれほど多くありません。

それでもmy.cnfの設定は最低限やっておくことが大切です。

最低限のmy.cnf

MyISAMとInnoDBを使っているケースがあると思いますが、MySQL5.5以上であれば基本的にはInnoDBの利用で問題ないでしょう。今それ以下のバージョンを使っていることは少ないでしょう。ケース・バイ・ケースでMyISAMの方がいいケースもありますが何もわからない場合はInnoDBを使いましょう。

InnoDBを使う場合の最低限の設定はこちらです。

[mysqld]
innodb_buffer_pool_size = メモリの70%くらい
innodb_data_file_path = ibdata1:300M:autoextend
innodb_file_per_table
innodb_log_file_size = 256M
innodb_log_files_in_group = 3

細かい設定を考えなければ正直これだけいれておけば、性能面やメンテナンス面では
ほとんど問題ないです。極論を言えばinnodb_bufferpool_sizeだけでも大丈夫です。

innodb_buffer_pool_sizeはMySQLでInnoDBを使っている場合、最も重要なパラメータです。デフォルトのまま使っていると、MySQLのバージョンにもよりますがとても小さい設定になっています。InnoDBの処理はほとんどinnodb_buffer_pool上でおこなわれます。ざっくりOSのメモリと同じだと思ってもらえればと思います。

ここだけきちんと設定しておけば、パフォーマンスチューニングの8割は終わりです。細かいことは後から考えましょう。
RDSの場合はデフォルト値がDBInstanceClassMemoryの75%に自動的に設定されるので、これも考えなくてもいいでしょう。
後々のメンテナンスだけ考えてinnodb_file_per_table程度が設定されていれば大丈夫です。

タイトルとURLをコピーしました