6月29日にMySQLウィークリーセミナーに参加してきました。
相変わらす、勉強会とかセミナーのまとめが遅いので反省です(PHP勉強会も残っているな。。。)。
当初の予想通りやはり人気があったらしく、急拠2回にわけて開催されることになりました。
最初の申し込みには間に合わなかったのですが、追加募集で滑り込むことができました。
パフォーマンスとは
・パフォーマンスの指標
- スループット
- レスポンスタイム/レイテンシ(遅延)
- スケーラビリティ
- 上記の組み合わせ
これらの指標について、平均値だけではなくばらつきも見る必要ある。
キューイング
- 複数のユーザ/リクエストがある場合に発生
- レスポンスタイム = キューイングによる遅延 + 実行時間
- ホッケースティック(システムが飽和状態になると、キューイングによる遅延が急激に増大する現象)
パフォーマンスの改善時に、キューイングの遅延と実行時間のどちらを改善するか重要(実行時間の短縮がキューイングの遅延を改善する)
実行時間
- ボトルネックの特定
・ネットワーク
・CPU
・ディスク
・ロック
・… - 間接的な計測
・webページ表示のためのクエリ実行時間の合計 - 直接的な計測
・CPU利用率
・ディスクIOのレイテンシ
・ネットワークトラフィック
・処理待ちプロセス数
・同時実行中のクエリ数
・…
ベンチマークテストの注意点
- 実行方法と、結果の読み取りを間違えると意味がなくなる
- バージョンを変えると改善していることも
- その他、ありがちな間違い
・データサイズが違う(本番10GBなのに1Gでテスト)
・データやリクエストのばらつきを考慮しない
・1ユーザだけでテストするとか
・実際の運用環境と違うアプリケーション特性下でのテスト
・ベンチマークテストのクライアントに負荷がかかっていて性能が出ない
パフォーマンスチューニングをする際の注意事項
- 他の可能性を考える(メモリ増やすとか)
- 本当にそのパフォーマンスが必要かどうか
- 常に全体像を把握しておく(ボトルネックとか。)
- チューニングのプライオリティーを確認しておく
・全部をやることは多くの場合無駄
・コストとのトレードオフ
パフォーマンスチューニングTIPs
MySQL アーキテクチャは以下のようになっている。
1 2 3 4 5 6 7 8 9 10 11 12 13 |
クライアント ↓ コネクションスレッドプール ↓ クエリキャッシュ ↓ パーサー ↓ オプティマイザ ↓ ストレージエンジン ↓はクエリの流れ |
各レイヤーでパフォーマンスチューニングを説明
基本的に設定はmy.cnfでいいのかな
コネクションスレッドプール
・サーバコネクション&スレッド
- max_connections(100)
・コネクション数。多すぎるとメモリを消費しすぎるかも
・一般的なweb計のシステムでは1000強に設定し、有事に備えサーバには2倍の余裕を持たせておく - thread_cache_size(8)
・一般的には max_connectons/3
・スレッドをコネクションの切断後にキャッシュしておく数
1 |
show status like 'Thr%' |
などで設定値を確認
・バッファ
マニュアルでバッファあたりの項目を見ておくとよいとのこと
- sort_buffer_size(2M)
- メモリサイズの見積もり = max_connectons x (スレッドごとのバッファ*thread_stack) * 1/2
クエリキャッシュ
- query_cache_size(0)
・クエリキャッシュに割り当てるメモリサイズ
・まったく同じクエリのときにキャッシュを使う
・一般的には32Mくらい
ストレージエンジン
ストレージエンジンごとのチューニング
・InnoDB
特性は前回を参照
- innodb_buffer_pool_size
・MySQL&InnoDBのみ利用ならばメインメモリの80%を割り当てる
・データとインデックスの両方をキャッシュ - innodb_log_file_size
・innodb_buffer_pool_sizeの25~100%くらい
・ログファイルがどの程度頻繁に切り替わっているかをチェック
・値を大きくするとクラッシュ時のリカバリ時間が長くなる - innodb_flush_log_at_trx_commit
・基本的には変えないほうがよい
DBの稼動情報は
1 |
SHOW INNODB STATUS; |
で確認
・MyISAM
- key_buffer_size(8M)
・MySQL&MyISAMのみ利用ならばメインメモリの25~33%くらい - myisam_sort_buffer_size(8M)
・インデックス作成時には大きくしておく - myisam_recover=FORCE,BACKUP
・テーブルアクセス時に破損をチェック - delay_key_write=ALL
・インデックスをディスクに反映させるのをテーブルが閉じられたタイミングのみにする
・クラッシュ時にデータが破損する可能性が高くなるので注意が必要
DBの稼動状況は
1 |
show status lile 'key%' |
インデックス
- 参照時の性能は向上するが、更新時はオーバーヘッド(インデックスも更新するため)
- インデックス内での順序が先の列のみ利用可能
・key(a,b) … where b=5 はインデックスを使わない - 必要最小限にとどめること
・ばらつきが少ないデータはインデックスが使われない場合がある(が、更新時にはオーバーヘッド)
・性別などに使うのは。。。 - ユニークなインデックスにはUNIQUEをつける
- 重複するようなインデックスは避ける
・key(a,b)があるならkey(a)はは削除
チューニングに関するコマンド&SQL
SQLオプティマイザの制御
1 |
SELECT STRAIGHT_JOIN * from tbl1, tbl2... |
SQL文に書かれたテーブルの順に処理を行う
1 |
USE INDEX or FORCE INDEX or IGNORE INDEX |
1 |
ANAYZE TABLE |
通常はあまり必要とされないが、大量にデータの更新があった後などには実行することもある。
チューニングに関するSQL文
- EXPLAINでオプティマイザの解析結果を確認
- スロークエリを有効に
・–log-slow-queries –long-query-time=2 –log-long-format
・mysql_explain_log ログの内容を表示するコマンド(5.0のみ)
・mysqldumpslow ログの内容を集計するコマンド - 開発環境では一般ログ(general query log)も利用
・クエリの重複や数が多すぎないか - SHOW [FULL] PROCESSLIST
・遅いクエリ、終わらないクエリを検出 - クエリが実際に行っている内容の確認
・FLUSH STATUS;; SHOW STATUS;
ハードウェア関連のチューニング
ハードウェアの選択
- CPU:64bit CUPの利用を検討
- CPUキャッシュ 大きいほうがよい
- Memoryバンド幅 CPU利用が中心の処理の際にネックになる
- CPU数/コア数/スレッド数 ひとつのクエリはひとつのCPUを使用
その他
結構好評だったので、今後SUNのオフィスで継続していくことを検討しているらしい。
感想
パフォーマンスチューニングの初歩的な内容でした。自分のレベルにあっていてちょうどよかったです。
ここでのことをちゃんとやれば一般的なウェプサービスでは対応できそうですね。