MySQL パフォーマンスチューニング on MySQL Weekly Seminor 2008/06/27

業務で参加ですが，ひとまずログ記録．こんかいから howm でもはてな記法で書いたのでコピペが楽です(ノ∀`)．

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

MySQL は Orcale と同程度の安定性とスケーラビリティがあると評価されている(2005年)

パフォーマンスとは？

パフォーマンスの指標
- スループット
- レスポンスタイム・レイテンシ
- スケーラビリティ
- 上記のコンビネーション
  - CPU やサーバ環境によって変わるのか，など
  - 指標は平均値だけでみるのではなく，ばらつきを調べるのも重要
キューイング
- 複数のユーザ・リクエストがある場合に発生
- レスポンスタイム = キューイングによる遅延 + 実行時間
- 飽和するとキューイングによる遅延が増大する
  - 天王山トンネルとかと同じ原理
  - 事前の性能テストでは見えない部分でもある
    - 性尿評価の基準作りが重要
実行時間 : Key to the hotspot
- 確認時間
  - ネットワーク, CPU, I/O, ロック待ち, ....
    - 原因を見極めないと，高速化に繋がらない可能性がある
- 計測方法は？
  - 1ページあたりのクエリ実行時間の合計
ベンチマークテスト
- 実行方法に注意が必要
- 結果を正しく読み取れることが重要
  - MySQL のサイトに「なぜ MySQL を使うのか」があり，ベンチマーク結果が載っているが，どのような結果かを見極めなけらばならない．
- ありがちな間違い
  - 本環境を想定しているかどうか
  - テストデータのばらつき具合
  - 1ユーザだけのテスト
    - Lock 待ちに気がつかない
  - 特性の違うテストをしても意味がない
    - Web 系とバッチ処理など
  - ベンチマーク実行環境の性能不足で，期待した値が出ない状況がありうる
ビジネス面からの考慮
- コストパフォーマンスを調べる
- パフォーマンス・スケーラビリティ・信頼性は本当に必要か？
- 常に全体像を把握しておく．
  - 部分的に改善しても，その他にボトルネックがある可能性がある．
- どのチューニングが効果的かを判断しながら

パフォーマンスチューニングTIPS

MySQLサーバの設定

様々なストレージエンジンの設定
それ以前の処理は，どれも同じ
サーバのコネクション＆スレッド
- max_connections : default 100
  - サーバが許容可能なコネクション
  - 大きくしすぎるとメモリを使い切るかも
- thread_cache_size : default 8
  - 一般的には max_connections / 3
    - connectoin pool を使うのなら，多くなくても大丈夫
- 調べるには
  - show status like 'Thr%'
  - Thread_created が大きいと接続・切断を繰り返している
    - thread_cache_size を大きくする
コネクションスレッド毎のバッファ
- MySQL のマニュアルに，MySQL はどのようにメモリを使うのかの記事がある
- sort_bufer_size
  - ソート用のメモリサイズ
- メモリサイズの見積もり = max_connections * ( スレッド毎のバッファ + thread_stack ) * 1/2(計数)
- 調べるには
  - show status -> Sortmerge_passes
    - ファイルを利用したマージソートのパス数
      - 大きければバッファを増やす
クエリキャッシュ
- SQl が厳密に同じであれば，キャッシュされる
  - 完全に同じである必要がある
- SELECT の比率が高い環境で効果的
- 調べるには
  - show status like 'Qc%'
    - Qcache_hits : ヒット率をしらべて有効かどうか
    - Qcache_lowmem_pruned : キャッシュが削除された回数なので，ここが大きければキャッシュサイズを増やす

ストレージエンジンの選択＆設定

InnoDB
- MySQL 5.0 からは InnoDB の方が性能が良いらしい
- メモリ上のデータとディスク上のデータがあり，COMMIT などのタイミングで，メモリからディスクに書き込まれる
- パフォーマンスTIPS
  - innodb_buffer_pool_size
    - メインメモリの80%を割り当てる
  - innodb_log_file_size
    - 値を大きくするとクラッシュリカバリ時間が長くなる
  - 調べるには
    - show status like 'Inno%' / show innodb status
      - innodb_buffer_pool... でバッファの使われ方を見ることができる
MyISAM
- インデックスのみがキャッシュされる
- データは OS まかせ
パフォーマンスTIPS
- myisam_sort_buffer_size
  - インデックス作成時にしか使われない
- 調べるには
  - show status like 'key%'
    - Key_blocks_unused
      - 値が大きすぎる場合にはキャッシュサイズが大きすぎる

スキーマ＆クエリのチューニング

スキーマのデザイン
- 正規化と非正規化
  - JOIN はわりとコストが高い
- 適切なデータ型か
  - ディスクの領域が小さいと，I/O が小さくなる
  - JOIN する列は同じ型に
    - でなければ型変換が実行されてしまう
  - 可能なところは NOT NULL
- インデックスは適切にはられているか
インデックス
- 参照時は性能向上，更新時はオーバーヘッド
- 小さなインデックスが好ましい
- カラムの一部のみを使うのも可能
- 複合キーは，先の列のみ利用可能
- ばらつきの少ないものには使わない方が良い
  - 更新時のオーバーヘッドにしかならない
- key(a, b) があるなら，key(a) は削除すべき
- 検索に必要なカラムがインデックスがあるものばかりだと，検索はかなり高速

チューニングに関するコマンド＆SQL

SQLオプティマイザの制御
- STRAIGHT_JOIN
  - SQL に書かれた順にテーブルを処理する
チューニングに関するSQL文
- EXPLAINで解析
  - OPTIMIZER がどのように実行しようとしているかの結果
  - Extra にある情報が重要
    - 「実際にどのようにデータを取ってきたか」ではない
- sloq query log で確認
- 一般ログでクエリの重複を調べる
- show full processlist ( full をつけないと出力が 100 byte に制限される)
  - Time でかかった時間が現れる
- show status はサーバ起動時からの蓄積結果
  - flush status で削除して調べることも可能

ハードウェア関連のチューニング

ハードウェアの選択
- CPU は 64bit をつかってメモリを多く搭載できるように
- CPU キャッシュも性能に影響を与える可能性がある
- Memor バンド幅も重要
- CPU数/コア数/スレッド数
  - 1つのクエリは1つのCPUを使用する
Database/OS メモリバッファ
- メモリを積めば高速化に繋がる可能性大
- スワップは発生しないようにするべき
- スレッド毎のバッファなどはダイナミックアロケート
ディスクI/Oサブシステム
- RAID1/0が適切
- slave は RAID0 という選択も
- InnoDB のログは独立した RAID1 に格納すると，障害体制が高くなる
OS の選択
- MySQL がパッケージを個別に用意している OS が特におすすめ
- ソースコードよりもバイナリパッケージの利用がおすすめ

コンサルティングサービス

エンジニア向けパフォーマンスチューニング研修も今後開催予定
- 有償で，4日で25万強
  - 1日 52.5k なんだが，それってかなり高めかな？

MySQL User Conference Japan 2008

2008/10/30 〜 2008/10/31 に東京ステーションコンファレンスにて開催
ユーザ事例・技術系セッションを予定
セミナー終了後には，MySQL の開発者との BOF を開催
- これには是非とも参加したい．

まとめ

大前提として，MySQL レベルでのチューニングするかしかいかの判断をしなくてはいけない．
- サーバ増やすとかで解決できるとか，そもそも何もしないという判断も．
各種レベル(ネットワーク・環境・サーバ・クエリ)それぞれで，どうするのかを正確に判断すること．
- 数%の向上に，90% のコストをかけても意味が薄い．
単純に設定したのでは，意外にメモリを食う．
- thread 毎に要求するメモリが多ければ，Web サービス系では結構大食いに．
当然 I/O も大事．
- RAID 1/0 や，ログの別ディスク化とか，配置も考慮すべき．