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

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

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

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

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

キューイング

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

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

実行時間

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

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

  • 実行方法と、結果の読み取りを間違えると意味がなくなる
  • バージョンを変えると改善していることも
  • その他、ありがちな間違い
    ・データサイズが違う(本番10GBなのに1Gでテスト)
    ・データやリクエストのばらつきを考慮しない
    ・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 はインデックスを使わない
  • 必要最小限にとどめること
    ・ばらつきが少ないデータはインデックスが使われない場合がある(が、更新時にはオーバーヘッド)
    ・性別などに使うのは。。。
  • ユニークなインデックスにはUNIQUEをつける
  • 重複するようなインデックスは避ける
    ・key(a,b)があるならkey(a)はは削除

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

SQLオプティマイザの制御

SQL文に書かれたテーブルの順に処理を行う

通常はあまり必要とされないが、大量にデータの更新があった後などには実行することもある。

チューニングに関する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のオフィスで継続していくことを検討しているらしい。

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