変更する必要があるかもしれない設定パラメータについて書いていきます

MySQL5.6インストール後に調整する設定項目等.md

まずはInnoDB の設定パラメータ

• innodb_buffer_pool_size

  デフォルトは128M。とても重要なパラメータです。 InnoDBデータ＋indexをどのくらいメモリにロードさせるかの上限値。 MySQL専用サーバーの場合、推奨サイズは最大メモリ量の50%〜80%です。 例えば64GBメモリを積んでる場合、50GB(78%)とか。

  ※設定値を高くしすぎる危険性。 OSとバイナリログ、トランザクションログ等ファイルシステムキャッシュに依存しているこれらに割り当てるメモリが足りなくなることです。

• innodb_log_file_size

  デフォルトは48M。4GBまでであれば安全な設定値です。 以前は大きなログファイルだったらクラッシュリカバリ時間が長かったが5.6で改善されました。

• innodb_flush_neighbors

  デフォルトは1。シーケンシャルI/Oのパフォーマンスを最適化出来ないSSDは無効(0)に設定する必要があります。

---

続いてReplication

• log-bin

  binary-logを出力するための設定です。以下の設定がオススメ。 sync_binlog=1, sync_relay_log=1, relay-log-info-repository=TABLE and master-info-repository=TABLE

• expire-logs-days

  デフォルトではログは永遠に残ります。1-10日間がオススメ。

• server-id

  ユニークなidが必要です。

• binlog_format=ROW

  行ベースのレプリケーション設定にすると、ロックを減らすことが出来パフォーマンスが向上します。

  transaction-isolation=READ-COMMITTED and innodb_autoinc_lock_mode = 2

---

その他

• timezone=GMT

  グローバル展開を意識するならGMTがおすすめです。

• character-set-server=utf8 and collation-server=utf8_general_ci

  他言語対応を意識するならutf8がおすすめです。

• sql-mode

  デフォルトだと、暗黙的にデータが切り捨てられます。

• skip-name-resolve

  名前解決が必要無いならば、設定を有効にします。

• max_connect_errors

• max-connections

  デフォルトは151。300-1000が目安。 thread pool plugin

---

結論

以下のMySQLサーバースペックを前提とすると：

• 64GB Memory
• ハードウェアRAIDコントローラー(2,000IOPS 以上と仮定)
• レプリケーションします
• Firewall を利用します
• ホスト名の逆引きはしない
• グローバル展開で複数のタイムゾーン

sample configuration:

# InnoDB settings
innodb_buffer_pool_size=50G
innodb_log_file_size=2G
innodb_flush_method=O_DIRECT
innodb_io_capacity=2000
innodb_io_capacity_max=6000
innodb_lru_scan_depth=2000

# Binary log/replication
log-bin
sync_binlog=1
sync_relay_log=1
relay-log-info-repository=TABLE
master-info-repository=TABLE
expire_logs_days=10
binlog_format=ROW
transaction-isolation=READ-COMMITTED
innodb_autoinc_lock_mode = 2

# Other
timezone=GMT
character-set-server=utf8
collation-server=utf8_general_ci
sql-mode="STRICT_TRANS_TABLES,
 ERROR_FOR_DIVISION_BY_ZERO,
 NO_AUTO_CREATE_USER,
 NO_AUTO_VALUE_ON_ZERO,
 NO_ENGINE_SUBSTITUTION,
 NO_ZERO_DATE,
 NO_ZERO_IN_DATE,
 ONLY_FULL_GROUP_BY"
skip-name_resolve
max-connect-errors=100000
max-connections=500

# Unique to this machine
server-id=123

@see http://yakst.com/ja/posts/61