[MySQL][勉強会]MySQLウィークリーセミナーに参加（パフォーマンスチューニング編）

6月29日にMySQLウィークリーセミナーに参加してきました。
相変わらす、勉強会とかセミナーのまとめが遅いので反省です（PHP勉強会も残っているな。。。）。

当初の予想通りやはり人気があったらしく、急拠２回にわけて開催されることになりました。
最初の申し込みには間に合わなかったのですが、追加募集で滑り込むことができました。

パフォーマンスとは
・パフォーマンスの指標

• スループット
• レスポンスタイム/レイテンシ(遅延）
• スケーラビリティ
• 上記の組み合わせ

これらの指標について、平均値だけではなくばらつきも見る必要ある。

キューイング

• 複数のユーザ/リクエストがある場合に発生
• レスポンスタイム　= キューイングによる遅延 + 実行時間
• ホッケースティック(システムが飽和状態になると、キューイングによる遅延が急激に増大する現象)

パフォーマンスの改善時に、キューイングの遅延と実行時間のどちらを改善するか重要（実行時間の短縮がキューイングの遅延を改善する）

実行時間

• ボトルネックの特定
  ・ネットワーク
  ・CPU
  ・ディスク
  ・ロック
  ・…
• 間接的な計測
  ・webページ表示のためのクエリ実行時間の合計
• 直接的な計測
  ・CPU利用率
  ・ディスクIOのレイテンシ
  ・ネットワークトラフィック
  ・処理待ちプロセス数
  ・同時実行中のクエリ数
  ・…

ベンチマークテストの注意点

• 実行方法と、結果の読み取りを間違えると意味がなくなる
• バージョンを変えると改善していることも
• その他、ありがちな間違い
  ・データサイズが違う（本番１０GBなのに１Gでテスト）
  ・データやリクエストのばらつきを考慮しない
  ・1ユーザだけでテストするとか
  ・実際の運用環境と違うアプリケーション特性下でのテスト
  ・ベンチマークテストのクライアントに負荷がかかっていて性能が出ない

パフォーマンスチューニングをする際の注意事項

• 他の可能性を考える（メモリ増やすとか）
• 本当にそのパフォーマンスが必要かどうか
• 常に全体像を把握しておく（ボトルネックとか。）
• チューニングのプライオリティーを確認しておく
  ・全部をやることは多くの場合無駄
  ・コストとのトレードオフ

パフォーマンスチューニングTIPs
MySQL アーキテクチャは以下のようになっている。

各レイヤーでパフォーマンスチューニングを説明
基本的に設定はmy.cnfでいいのかな

コネクションスレッドプール

・サーバコネクション＆スレッド

• max_connections(100)
  ・コネクション数。多すぎるとメモリを消費しすぎるかも
  ・一般的なweb計のシステムでは1000強に設定し、有事に備えサーバには2倍の余裕を持たせておく
• thread_cache_size(8)
  ・一般的には max_connectons/3
  ・スレッドをコネクションの切断後にキャッシュしておく数

などで設定値を確認

・バッファ
マニュアルでバッファあたりの項目を見ておくとよいとのこと

• 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の稼動情報は

で確認

・MyISAM

• key_buffer_size(8M)
  ・MySQL&MyISAMのみ利用ならばメインメモリの25～33%くらい
• myisam_sort_buffer_size(8M)
  ・インデックス作成時には大きくしておく
• myisam_recover=FORCE,BACKUP
  ・テーブルアクセス時に破損をチェック
• delay_key_write=ALL
  ・インデックスをディスクに反映させるのをテーブルが閉じられたタイミングのみにする
  ・クラッシュ時にデータが破損する可能性が高くなるので注意が必要

DBの稼動状況は

インデックス

• 参照時の性能は向上するが、更新時はオーバーヘッド（インデックスも更新するため）
• インデックス内での順序が先の列のみ利用可能
  ・key(a,b) … where b=5 はインデックスを使わない
• 必要最小限にとどめること
  ・ばらつきが少ないデータはインデックスが使われない場合がある（が、更新時にはオーバーヘッド）
  ・性別などに使うのは。。。
• ユニークなインデックスにはＵＮＩＱＵＥをつける
• 重複するようなインデックスは避ける
  ・key(a,b)があるならkey(a)はは削除

チューニングに関するコマンド＆ＳＱＬ

ＳＱＬオプティマイザの制御

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のオフィスで継続していくことを検討しているらしい。

感想
パフォーマンスチューニングの初歩的な内容でした。自分のレベルにあっていてちょうどよかったです。
ここでのことをちゃんとやれば一般的なウェプサービスでは対応できそうですね。