業務で参加ですが，ひとまずログ記録．こんかいから 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 や，ログの別ディスク化とか，配置も考慮すべき．
