Linux/CGP作業メモ

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

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

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

　　・スロークエリログの分析
　　　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で解析
　　　処理の順番を測定し処理毎の時間を出せる
　　　テスト環境など別のタスクのない状況で行う
　　　使い方
[code]　　　　mysql> SET profiling=1;
　　　　mysql> 解析したいクエリ実行
　　　　mysql> SHOW PROFILE;[/code]
　　　履歴表示
[code]　　　　mysql> SHOW PROFILES;
　　　　mysql> SHOW PROFILE FOR 1;　何番目かの履歴
　　　　mysql> SET profiling_history_size=100;　履歴保存数設定[/code]
　　　表示オプション
[code]　　　　mysql> SHOW PROFILES ALL; 全表示
　　　　CPU　CPU使用率
　　　　SWAP　スワップしたかしないか　など[/code]

　　・show statusで統計情報表示
　　　show session status　現在セッションの情報表示
　　　show global status　サーバ全体の状況
　　　以下のように利用する
[code]　　　　mysql> FLASH STATUS;
　　　　mysql> SET profiling=1;
　　　　mysql> 解析したいクエリ実行
　　　　mysql> SHOW PROFILE;
　　　　mysql> SHOW SESSION STATUS;
　　　　mysql> SHOW GLOBAL STATUS;[/code]
　　　注意すべきパラメータ
　　　　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；空きメモリの７割くらい割当する
　　　　index以外もキャッシュされる
　　　innodb_log_file_size/innodb_log_file_in_group：INSERTが高速になる
　　　　が128M程度でOK
　　　key_buffer_size：空きメモリの３割程度
　　　　（MyISAMはindexしかキャッシュしない）
　　　セッションパラメータは256kbで十分　
　　　　大きくても２Mくらいで（メモリ使いすぎる）
　　　
　□システム全体でパフォーマンス対策

　　・MySQLのバージョン
　　　5.0以上を利用する　5.0と5.1は８コアまでスケール可能（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でも効果がある