Wadslog

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

Jul 7, 2008

6月29日にMySQLウィークリーセミナーに参加してきました。 相変わらす、勉強会とかセミナーのまとめが遅いので反省です（PHP勉強会も残っているな。。。）。 当初の予想通りやはり人気があったらしく、急拠２回にわけて開催されることになりました。 最初の申し込みには間に合わなかったのですが、追加募集で滑り込むことができました。 パフォーマンスとは ・パフォーマンスの指標

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

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

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

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

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

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

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

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

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

パフォーマンスチューニングTIPs MySQL アーキテクチャは以下のようになっている。``` クライアント ↓ コネクションスレッドプール ↓ クエリキャッシュ ↓ パーサー ↓ オプティマイザ ↓ ストレージエンジン

↓はクエリの流れ


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

show status like ‘Thr%’


*   sort\_buffer\_size(2M)
*   メモリサイズの見積もり = max\_connectons x (スレッドごとのバッファ\*thread\_stack) \* 1/2

**クエリキャッシュ**

*   query\_cache\_size(0)  
    ・クエリキャッシュに割り当てるメモリサイズ  
    ・まったく同じクエリのときにキャッシュを使う  
    ・一般的には32Mくらい

**ストレージエンジン** ストレージエンジンごとのチューニング ・InnoDB 特性は[前回](http://wadslab.net/2008/06/mysql_study/)を参照

*   innodb\_buffer\_pool\_size  
    ・MySQL&InnoDBのみ利用ならばメインメモリの80%を割り当てる  
    ・データとインデックスの両方をキャッシュ
*   innodb\_log\_file\_size  
    ・innodb\_buffer\_pool\_sizeの25～100%くらい  
    ・ログファイルがどの程度頻繁に切り替わっているかをチェック  
    ・値を大きくするとクラッシュ時のリカバリ時間が長くなる
*   innodb\_flush\_log\_at\_trx\_commit  
    ・基本的には変えないほうがよい

DBの稼動情報は```
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の稼動状況は```
show status lile 'key%'

```**インデックス**

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

**チューニングに関するコマンド＆ＳＱＬ** ＳＱＬオプティマイザの制御```
SELECT STRAIGHT\_JOIN \* from tbl1, tbl2...

```SQL文に書かれたテーブルの順に処理を行う```
 USE INDEX or FORCE INDEX or IGNORE INDEX

ANAYZE TABLE


*   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のオフィスで継続していくことを検討しているらしい。 **感想** パフォーマンスチューニングの初歩的な内容でした。自分のレベルにあっていてちょうどよかったです。 ここでのことをちゃんとやれば一般的なウェプサービスでは対応できそうですね。
comments powered by Disqus