MySQLチューニングメモ □パフォーマンスチューニングのテクニック

  ・まずは状況把握
   原因の推測のための情報収集
   各種ツールの利用
   ボトルネックをひとつづつ潰す

  ・パフォーマンス指標
   スループット
   レスポンスタイム
   スケーラビリティ
   上記の組み合わせ

  ・スロークエリログの分析
   5.1からは0.x秒単位でログ指定が可能
   頻繁に記録されるslowログから対応する
   mysqldumpslowで統計処理して見極め
   クエリアナライザの利用(高機能版mysqldumpslow)
    MySQL Enterprise Monitor + MySQL Proxy
    Proxyでクエリと統計を収集、Monitorで記録分析表示

  ・EXPLAINで解析
   遅いクエリをチェックしていく
   EXPLAIN typeでALL、index、index_subqueryが出たら注意
   key_lenが大きすぎるとNG
   indexを使っていない場合にはつけてみる
    (テーブルにindex多すぎるとかえって遅くなるので注意)
   INサブクエリは遅いので、JOINで書き直す
   EXPLAIN Extraで以下の値が出たら注意
    Using temporary:indexを使う
    Using filesort:頻発よくない複合indexを検討
    Full scan on NULL key:値によってパーテショニング検討
    Using join buffer:index使ってないところにつける

  ・show profileで解析
   処理の順番を測定し処理毎の時間を出せる
   テスト環境など別のタスクのない状況で行う
   使い方

  1.     mysql> SET profiling=1;
  2.     mysql> 解析したいクエリ実行
  3.     mysql> SHOW PROFILE;

   履歴表示

  1.     mysql> SHOW PROFILES;
  2.     mysql> SHOW PROFILE FOR 1; 何番目かの履歴
  3.     mysql> SET profiling_history_size=100; 履歴保存数設定

   表示オプション

  1.     mysql> SHOW PROFILES ALL; 全表示
  2.     CPU CPU使用率
  3.     SWAP スワップしたかしないか など

  ・show statusで統計情報表示
   show session status 現在セッションの情報表示
   show global status サーバ全体の状況
   以下のように利用する

  1.     mysql> FLASH STATUS;
  2.     mysql> SET profiling=1;
  3.     mysql> 解析したいクエリ実行
  4.     mysql> SHOW PROFILE;
  5.     mysql> SHOW SESSION STATUS;
  6.     mysql> SHOW GLOBAL STATUS;

   注意すべきパラメータ
    Create_tmp*;テンポラリ作成が多いとNG
    Handler_*;indexを使わない行アクセスが多いとNG
    Key_*:キャッシュミス率が高いときはNG
    Qcache_*:クエリキャッシュのヒット率が悪い時は無効に
    Select_*;遅いSELECTがわかる、FULL JOINが出ていたら無くす

  ・show innodb statusで分析
   CREATE TABLE innodb_monitor(a int) ENGINE INNODB; で有効
   再起動すると消える
   mysql> SHOW INNODB STATUS\G; とすると見やすい
   注意すべきパラメータ
    SEMAPHORE:ロック状況がわかる OS waitが多いとNG
    LOG:消費量がわかる
    BUFFER POOL AND MEMORY:ヒット率でサイズ調整必要
  ・クエリを書き換える
   検索条件がOR:index MergeがNG時はUNION DISTINCTで解決
   適合indexがない:マルチカラムの場合、左から順に指定必須
    1,2,3 の時 1,2と1,2,3は有効、1,3と2,3は無効
   JOINではLIMIT効かないので注意
   INサブクエリはJOINで書き直す

  ・テーブルデフラグ
   OPTIMIZE TABLEで解決
   連続データはパーテショニングしやすい(ログとか日付で連続する)
   パーテショニングしたデータはフラグメントしない、削除が高速

  ・パラメータチューニング
   innodb_buffer_pool_size;空きメモリの7割くらい割当する
    index以外もキャッシュされる
   innodb_log_file_size/innodb_log_file_in_group:INSERTが高速になる
    が128M程度でOK
   key_buffer_size:空きメモリの3割程度
    (MyISAMはindexしかキャッシュしない)
   セッションパラメータは256kbで十分 
    大きくても2Mくらいで(メモリ使いすぎる)
   
 □システム全体でパフォーマンス対策

  ・MySQLのバージョン
   5.0以上を利用する 5.0と5.1は8コアまでスケール可能(innoDB)

  ・ハードウェア
   64bitマシンを導入する(メモリ空間重要)
   ライトキャッシュ付きハードRAID推奨(キャッシュあればRAID5もOK)
   IOPSの高い構成を(SSDもあり!)

  ・OS
   64bit推奨になる
   LinuxではI/Oスケジューラに注意 noop deadline推奨
   noopはinnoDBで利用するのに適している
   I/Oキューの数値を上げる デフォルト128、10000?

  ・データベースエンジン
   innoDBはinsert bufferがあるのでログ等にも向く
   MyISAMはHDD性能に依存する 限界性能低い
    でもSSDならinnoDBより速くなる
   innoはHDDでもSSDでも限界性能があまり変化しない

  ・データベースパーテショニングを利用する
   アクセスする全てのインデックスがメモリ上にあれば高速に動作
   インデックスを細かくするためパーテショニングを使う(5.1以降必須)
   innoDBでもMyISAMでも効果がある

,
とりあえず付けておく無駄ではなかったなまぁまぁ読めたちょっと役に立ったかなかなり良かったかも (1 投票, 平均値/最大値: 1.00 / 5)
Loading...
Trackback

4 comments untill now

  1. Linux/CGP作業メモ » MySQLチューニングメモ http://icio.us/qbit4b

  2. RT @key3: Linux/CGP作業メモ » MySQLチューニングメモ http://icio.us/qbit4b

  3. RT @key3: Linux/CGP作業メモ » MySQLチューニングメモ http://icio.us/qbit4b

  4. MySQLチューニングメモ: http://bit.ly/fzuNOh