モノノフ日記

普通の日記です

MySQLのパフォーマンスチューニング

とある勉強会でSunのエンジニアの人のプレゼンを直接聞く機会があったのでメモったことを公開します。基本的な事が多いんだろうけど、非常に参考になりました。

パフォーマンスとは

  • スループット
  • レスポンスタイム/レイテンシ
  • スケーラビリティ
  • 上記のコンビネーション

アーキテクチャとは

  • Connection Thread Pool
  • Query Cache
  • Parser
    • SQLクエリをパース
  • Optimizer
  • Storage Engines
    • アプリによって最適なエンジンを選択すべき

サーバのコネクション&スレッド

my.cnf
  • max_connection (100)
    • 多すぎるとメモリを消費しきる可能性あり
  • thread_cache_size (8)
    • スレッドをコネクションの切断後にもキャッシュしておく数
    • 一般的には max_connections / 3
  • sort_buffer_size(2M)
    • ソート用のメモリサイズ
    • 512K, 1M程度で十分なケースも
    • メモリの見積もり
      • max_connections * (スレッド毎のバッファ+thread_stack) * 1/2
show status;
  • max_used_connections
  • thread_stack
  • sort_merge_passes

コネクションプール

  • アプリ側で設定
  • 処理が終わっても切断せずに接続を再利用
  • 維持するためのバッファが必要
  • プリペアドステートメントを利用する場合、開放処理を忘れるとメモリを食いつぶす

クエリキャッシュ

my.cnf
  • query_cache_size(0)
    • テーブルが更新されると対応するキャッシュがクリアされる
    • 一般的には32MでOK 128Mまで増やすケースもある
  • query_cache_type(ON)

InnoDBストレージエンジン

InnoDBパフォーマンスTips

  • innodb_buffer_pool_size
    • これが1番重要
    • インメモリの80%程度
    • データとインデックスの両方をキャッシュ
  • innodb_log_file_size
    • 更新系の処理が多い場合に有効
    • 大きくするとクラッシュ後のリカバリー時間が長くなる
    • 128〜512Mくらい
  • innodb_flush_log_at_trx_commit
    • 1(遅い)
      • ログを完全にフラッシュ。真のACID
    • 2(速い)
      • OSのキャッシュにログをフラッシュ、ディスクとのシンクは毎秒1回

MyISAMパフォーマンス

  • key_buffer_size
    • インデックスのキャッシュ
    • インメモリの25%〜33%くらい
  • myisam_sort_buffer_size
    • インデックスの作成時には大きく
    • ソートのパフォーマンスにも影響

NDBストレージエンジン

  • MySQL Clusterで利用されてる
  • 超高速OLTP向けエンジン
  • HA・負荷分散・シェアードナッシング

ハードウェアの選択

  • CPUは64bitにするべき
    • 現時点ではEM64T/Opteronがコストパフォーマンスがよい
  • CPUキャッシュ
    • 大きい方がよい
  • Memoryバンド幅
    • CPU利用が中心の処理の際にネックに
  • CPUは8〜16コアもあれば(現状)MySQLでは十分

ディスクIOサブシステム

  • 安全性のためにはRAID
  • RAID10
    • 多くのデバイスでベスト
    • 2本しかないならRAID1
  • RAID5
    • ランダム書き込みが非常に遅い、再構成も低速
    • RAID0の方が良い場合が多い
  • ディスクのキャッシュのためにバッテリーがあると良い
  • データチャンクは大きめ(256K-1MB)
  • InnoDBのログは独立したRAID1に格納するとよい

OSの選択

  • Linuxがよく使われてる
  • MySQLが個別にパッケージを用意しているOSが特に良い
  • dtraceが使えるOSはサポートで有利

インデックス

  • 性能に関する90%以上の問題がインデックスを適切に設定することで解決
  • 特に結合で重要
  • UNIQUEのが速い
  • NOT NULLを使う
  • 分散のよいカラムを使う。BOOLやENUMは向かない
    • そういうケースはパーティショニングを使うべき
  • インデックスは必要最小限に留めること
    • 更新時はオーバーヘッドになるため
    • 1テーブル10個以下で
  • インデックス内で順序が先の列のみ利用可能
    • key(a,b) ... where b = 5とか
  • 重複するようなインデックスの利用は避ける
  • BTREEインデックスはソートされた結果を返す
  • covering indexesは高速、行のデータのフェッチが不要
  • optimize table

トランザクション

  • 量のバランスが重要
    • where句の無いupdateは厳禁
    • コミット数が多すぎると、ディスクフラッシュが多いので性能低下
      • autocommit時
  • InnoDBログ領域に高速なディスクを使用
    • ライトキャッシュつきRAIDシステム
    • RAMディスク

パーティショニング

  • 巨大なテーブルを操作する際に問題となるBTREEインデックスのオーバーヘッドを解消
  • 特定のパーティションだけがクエリの対象になるように
  • 種類は4つ
    • Range
      • 日時でパーティショニングするのに最適
    • List
      • ENUMなど値が限られているときに最速
    • Hash
      • 整数値
    • Key
      • 文字列
  • パラレルクエリは未実装
  • パーティショニングするとINSERTが速くなる

スキーマのデザイン

  • 正規化
    • データの冗長性を削減
  • 非正規化
  • データ型
    • できるだけ小さい型を
    • JOINに使う列は同じデータ型に
    • char(64)ではなくvarchar(64)
    • DECIMAL(9)ではなくINT
    • 可能なところはNOT NULL

アプリケーションレベルでの対応

  • アプリでパーティショニング
    • カラムの値によってDBサーバを使い分ける
  • ロードバランサでMySQLサーバを選択
    • MySQLの複製機能を使う
    • 負荷分散できるのは参照系のみ
  • memcached

チューニング

  • EXPLAIN基本
  • スロークエリログ
  • 開発環境では一般ログも利用
    • クエリの種類や数のチェック

SQLオプティマイザの制御

  • あまり賢くない
  • SELECT STRAIGHT_JOIN * from T1,T2
    • SQLに書かれたテーブル順に処理
  • USE INDEX/FORCE INDEX/IGNORE INDEX
    • インデックスを強制的に使わせる
  • ANALYZE TABLE
    • 大量のデータ更新をしたときに