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パフォーマンスTips
MyISAMパフォーマンス
NDBストレージエンジン
- MySQL Clusterで利用されてる
- 超高速OLTP向けエンジン
- HA・負荷分散・シェアードナッシング
ハードウェアの選択
ディスクIOサブシステム
- 安全性のためにはRAID
- RAID10
- 多くのデバイスでベスト
- ２本しかないならRAID1
- RAID5
- ランダム書き込みが非常に遅い、再構成も低速
- RAID0の方が良い場合が多い
- ディスクのキャッシュのためにバッテリーがあると良い
- データチャンクは大きめ(256K-1MB)
- InnoDBのログは独立したRAID1に格納するとよい
インデックス
- 性能に関する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
- 文字列
- Range
- パラレルクエリは未実装
- パーティショニングするとINSERTが速くなる
スキーマのデザイン
- 正規化
- データの冗長性を削減
- 非正規化
- データ型
- できるだけ小さい型を
- JOINに使う列は同じデータ型に
- char(64)ではなくvarchar(64)
- DECIMAL(9)ではなくINT
- 可能なところはNOT NULL
アプリケーションレベルでの対応
チューニング
- EXPLAIN基本
- スロークエリログ
- 開発環境では一般ログも利用
- クエリの種類や数のチェック
