Osc2015北海道 札幌my sql勉強会_波多野_r3
17 likes10,114 views
MySQL パフォーマンスを監視する Cacti グラフの見方、InnoDB の I/O の仕組みの説明
![[GKE & Spanner 勉強会] Cloud Spanner の技術概要](https://cdn.slidesharecdn.com/ss_thumbnails/gke02-200121091040-thumbnail.jpg?width=560&fit=bounds)
![[Aurora事例祭り]Amazon Aurora を使いこなすためのベストプラクティス](https://cdn.slidesharecdn.com/ss_thumbnails/amazonauroratips-170307140000-thumbnail.jpg?width=560&fit=bounds)
More Related Content
What's hot (20)
Viewers also liked (20)
Similar to Osc2015北海道 札幌my sql勉強会_波多野_r3 (20)
![運用中のゲームにAIを導入するには?プロジェクト推進?ユースケース?運用? [DeNA TechCon 2019]](https://cdn.slidesharecdn.com/ss_thumbnails/techcon2019okumuraokada-190214063249-thumbnail.jpg?width=560&fit=bounds)
Recently uploaded (7)
Osc2015北海道 札幌my sql勉強会_波多野_r3
- 2. 自己紹介 ● 波多野 信広 (twitter @nobuHatano) ● 日本MySQLユーザー会 (札幌MySQL勉強会) ● 1969年生まれ ● 好きなもの SF、ゲーム、美術鑑賞 ● 超並列サーバーのハード／ソフトサポートを十数年 ● 札幌のIT企業のインフラとしてMySQL歴三年
- 5. 内容 ● Cactiとは ● グラフの見方一般編 ● Percona MySQL Monitoring Template ● MySQLグラフ詳解 ● トラブルシューティングのケース ● クエリのチューニング ● InnoDB I/O チューニング ● まとめ
- 7. インストール ● さくらのナレッジの「モニタリングツール「Cacti」でのリソース監視」がよくまとまっ ています http://knowledge.sakura.ad.jp/tech/618/ ● 他にも入れてみた、使ってみた系の記事は多数 ● 著名なプロジェクトなのにわりとバグが多いので、誰か上手く動いているのと 同じバージョンにしてみたり、フットワーク軽めに対応するのがコツ ● epel のパッケージを yum で入れて依存パッケージの解決をさせてから、公 式の最新 tar ボールを別ディレクトリにダウンロードして入れたりなどの方法 もおススメ
- 8. データ取得とグラフ化の仕組み MySQL 管理データ デバイス snmp / ssh ● コマンド実行 ● 状態値取得 poller.php rrd データ rrdtool Web UI Cactiサーバー ● Poller.php が定期的に snmp, ssh でデバイスから値を取得 ● rrdtool が round robin DB(rrd) にデータを格納 ● Web UI からのリクエストに応じて rrdtool がデータをグラフ化し 画像を生成する
- 10. グラフの見方一般編 100 mili（ミリ）です 現在値のグラフで、データは整数秒のみのハズなのに、800m とか 30m とかグラフにありますが、これは何？ ポーリング間隔が5分なの で、その平均ですか？ RRDtool によるグラフの補正です 単位の G（ギガ）, M（メガ）, K （キロ）はよいとし て、100m とかの m って何？
- 15. Percona MySQL Monitoring Template MySQL の教育やコンサルティングを行う Percona 社は MySQLをフォークした Percona サーバーや、XtraDB Cluster, TokuDB など多彩な MySQL 関連製品も扱っています オープンソースで無償で利用可能な以下のツール Percona toolkit MySQL のスロークエリログの分析ツール pt-query-digest Perconoa Monitoring Plugins Cacti、Nagios, Zabbix のテンプレートを提供
- 16. Percona MySQL Monitoring Template
- 17. Percona MySQL グラフの読み方 例1 サーバーステータス変数をグラフ化したもの Questions と Com ～ 公式リファレンスに説明があります http://dev.mysql.com/doc /refman/5.6/ja/server- status-variables.html 例 Questions “サーバーによって実行されたステートメントの数。これは Queries 変数とは異なり、クライアントによって サーバーに送信されたステートメントのみを含み、ストアドプロシージャー内で実行されたステートメントは含 みません“
- 18. Percona MySQL グラフの読み方 例2 Percona テンプレートのオリジナル項目 Uncheckpointed Bytes SHOW STATUS にはありません （MySQLのリファレンスにもない） Cactiインストールディレクトリ /scripts/ss_get_mysql_stats.php を読み解くと - - - LOG - - - Log sequence number 12295546428 Log flushed up to 12295546428 Last checkpoint at 12295533453 SHOW ENGINE INNODB STATUS の (Log sequence number) – (Last checkpoint at) = Uncheckpointed_Bytes
- 19. MySQLはこれだけみれば大丈夫！ グラフ詳解 ● トラブルシューティングのケース ● MySQL Command Counters ● InnoDB Current Lock Waits ● MySQL Transaction Hundler ● クエリのチューニング ● MySQL Select Types ● MySQL Handlers ● システム、I/O回りのチューニング ● InnoDB Checkpoint Age これだけみればというツボなグラフたち
- 20. トラブルシューティングのケース ● MySQL Command Counters で全体を観察 クエリほぼ一定で Questions だけ急増 システムも重かった ● クエリでないSQL文というと、、、 SET NAMES utf8; USE database; BEGIN; COMMIT; など補助的なもの。 ● クエリ本体以外は完了している ● リトライ？
- 21. トラブルシューティングのケース ● InnoDB Current Lock Wait でロックの量を知る 同じサーバーで Questions だけ急増 したのと同じところ Innodb Lock Wait Secs SHOW ENGINE INNODB STATUS で表示されるトランザクション情報のう ち、”TRX HAS BEEN WAITING n SEC FOR THIS LOCK TO BE GRANTED” の n をシステムのトランザクション全部で合計したもの デッドロック？ 違ったシステム全体のロック待ち 増加
- 22. トラブルシューティングのケース ● MySQL Transaction Handler でロールバックを探せ 同じサーバーで Questions, Lockが 増えたところ 普段ほゼロな Handler Rollback が増加 ロールバックされ、アプリによるリトライによってトランザクションが多数再投入され、 ロックはとりつつ、またロールバック、こうした挙動を繰り返していたもののと推察 ここからはアプリ開発チームと相談して調査！
- 23. クエリのチューニング ● MySQL Select Types でアプリのSELECTの書き方を判定 テーブルまたはイン デックスで ● Select Range where で範囲を 制限してselect ● Select Scan 全件検索 Select Range の割合 >> Select Scan の割合 が望ましいのでこれは NG
- 24. クエリのチューニング ● MySQL Select Types m(ミリ) なのでグラフ では見えませんが ● 「JOIN するときはインデックス使って行う」 という鉄則 ● 誰か破ったやつ（クエリ）がいるぞ！！！
- 25. クエリのチューニング ● MySQL Handler でI/O量を把握 MySQL セッションまわり クエリ実行計画 最適化 KVS的 Handler 命令 ストレージエンジンデータ 操作
- 26. クエリのチューニング ● Handler Read First テーブルやインデックスの全件検索スキャンで最初に先頭レコードの取得を 行います。その回数。少ないほうが良い ● Handler Read Key インデックスのキー値に基づいて行を読んだ回数。 ● Handler Read Next キー値に基づいて行を特定した後、後続の行を読んだ回数。 ● Handler Read Prev キー値で行を決めた後、その前の行を取得した回数。 ● Handler Read Rnd InnoDB でプライマリキーの値を指定して1行読んだ回数。 ディスクへのアクセス方法がシーケンシャルアクセスではなくランダムアクセスということで、MySQL の世界では歴史的にピンポイント で１行読み込む動作に Random Read という用語 ● Handler Read Rnd Next Read Rnd によって行を読んだ後、後続行を読み取った回数。 ● Read Key < Read Next インデックス使っていても範囲で読み込みしている ● Read Rnd < Read Rnd Next Rnd Next の比率が高いと、プライマリキーを使っていても広 範に読んでいるのでやはりよくない傾向 グラフの形で観察できます！！！
- 27. クエリのチューニング ● 前出の MySQL Handler グラフだと Read Next で読み込ん だ回数が圧倒的なので、 インデックスを使った範囲 読み込みの割合が多いこ とがわかります
- 28. システム、I/O回りのチューニング InnoDB ディスクI/O のしくみ （InnoDB ログファイル） 123 更新データ MySQLのメモリ InnoDB ログファイル ● 連続データをシリアルにディスクに書き込むので非常に高速 ● 磁気ディスクでも SSD のランダム書き込みより速い ● バッファなどありますが、常時ディスクに書いている（というイメージ） ● ログファイルに書いて更新トランザクション終了（というイメージ） ● 最大 4GB (MySQL 5.5), 512GB (MySQL 5.6+)
- 29. システム、I/O回りのチューニング InnoDB ディスクI/O のしくみ （InnoDB データファイル） 23 更新データ MySQLのメモリ InnoDB データファイルバッファープール 1 テーブル インデックス 23 1 23 121 3 ● バッファープールに格納するところまでで更新終了 ● 後続クエリの更新はメモリ上で完結 ● まとめてディスクに書き出す（チェックポイント） ● データ量も多く、チェックポイントは重い処理 ● 速度は メモリ > Fusion-io > SSD > 磁気ディスク > 仮想ディスク
- 30. システム、I/O回りのチューニング InnoDB ディスクI/O のしくみ （InnoDB データファイル） ログファイル バッファープール 今、更新がここ メモリのみに更新データ ログファイルで消えないデータ確保 データファイル というのは許されない 全ての更新トランザクショ ンを止めてでもディスクへ の書き出しを行う この量が Checkpoint Age
- 31. システム、I/O回りのチューニング InnoDB ディスクI/O のしくみ （InnoDB データファイル） Fuzzy Checkpoint ● 定期的に常時発動 ● バッファープール上の古いダーティページから1回あたり少量の書き出しを行う ● （アイドル時に書き出しが行われていくのはこのしくみ） Sharp Checkpoint ● ログファイルサイズの閾値(75～90%)を超えると発動し全てのダーティページを書き出す ● ログファイルサイズが大きいとディスクのWrite量も多くなり高負荷 ● 磁気ディスクだとさらに高負荷 ● Write 中でも更新は来ますが速度で負けて 100% になるとトランザクションは受付停止 Adaptive Flushing (MySQL5.6+) ● 更新量が少ない段階から、ある程度の書き出しを定期的に追加で行う仕組み ● SSDなど高速ディスクであれば Sharp Checkpoint の回避で利点が多い ● 低速ディスクだと低負荷なのに定期的に遅延が発生する原因となることも ● デフォルトON
- 32. MySQLはこれだけみれば大丈夫！ グラフ詳解 ● ツボ中のツボ InnoDB ログファイル見積もり ログファイル 128MB など バッファープール （サーバーのメモリの6－8割） 安全第一法：クラッシュ時の処理も考慮してログファイルサイズほどほど 利点： ● クラッシュ時のリカバリも可能 ● ときどき Sharp Checkpoint が発生するが量が少ないので処理のインパクトが小さい ● １日の更新量を正確に予測しなくてよい 欠点： ディスクへの書き込みが時々発生するので瞬間パフォーマンスは最大値ではない
- 33. MySQLはこれだけみれば大丈夫！ グラフ詳解 ● ツボ中のツボ InnoDB ログファイル見積もり ログファイル バッファープール （サーバーのメモリの6－8割） ハイリスクハイリターン法： ログファイルサイズはバッファープールと同等に 利点： ● 上手く見積もれば、長時間、データはログとメモリ上だけで動作 欠点： ● クラッシュ時の処理は現実的には終わらないことも ● 更新量予測を誤ると、長いトランザクション停止（数分?）が発生して絶大なダメージ ● 磁気ディスクでAdaptive Flushing を使うと低負荷なのに頻繁にレスポンス悪化
- 34. MySQLはこれだけみれば大丈夫！ グラフ詳解 ● ツボ中のツボ InnoDB Checkpoint Age ● SSD や Fusion-io などの高速ディスクを使っていない ● 仮想サーバーでディスクも仮想ディスクか良くて磁気ディスク ● 仮想といってもメモリは大きい ● MySQL 5.5 ないし ● MySQL 5.6 + InnoDB Adaptive Flushing = OFF を使いましょう
- 35. MySQLはこれだけみれば大丈夫！ グラフ詳解 ● ツボ中のツボ InnoDB Checkpoint Age ● バッファプール上に更新が溜まる速度に対して、fuzzy checkpoint が打ち勝っている状態 ● I/O の能力を使い切っていないのでまだ更新増やせそう
- 36. MySQLはこれだけみれば大丈夫！ グラフ詳解 ● ツボ中のツボ InnoDB Checkpoint Age Sharp checkpoint がおこ なわれているがすぐ打ち勝っ ているので影響なし ● Sharp Checkpoint が断続的に ● ディスクの能力を存分に発揮している状態 ● I/O 負荷による微小な遅延がアプリに影響出ていないか確 認しましょう
- 37. MySQLはこれだけみれば大丈夫！ グラフ詳解 ● ツボ中のツボ InnoDB Checkpoint Age ● 水平線になった場合 sharp checkpoint が常時走っている状態 ● ディスクの性能をフルに発揮している状態 ● Checkpoint Age 100%到達時のトランザクション停止が微小で済んでいるか、長時 間に及んでいるか、グラフでは区別がつきません ● 必ずアプリケーションのログで確認しましょう
- 38. まとめ ● １秒未満のクエリ実行時間に対しCactiのグラフは ポーリング(5分)単位ですが、それでも見えてくるもの はたくさんあります ● Cacti での MySQL のパフォーマンス監視に今回紹 介したツボをご活用ください!!!
Editor's Notes
- #8: Yum や apt などでインストール可能なディストリビューションのパッケージを使うケースも多い Cacti 公式サイトにはマニュアルなどが用意されていますが、インストール直後の初期状態が Cacti のマニュアルで書かれている状態とパッケージの方では少し異なってます。 Cacti はリリース後の緊急訂正をパッチという形で行いますが、このパッチが適用済みなのかどうかなどかえって混乱してしまう場合もあります。 そのため tar を展開するだけのパッケージを使わないインストールの方がシンプルで手間もかかりませんのでここではお勧めいたします
- #9: 具体的には3種 対象サーバーへのSnmp で現在取得可能な数値についてグラフ化 cacti サーバーでスクリプトを動かして対象サーバーでの値を得てグラフ化 対象サーバーにコマンドを作成しsnmpにも設定してsnmpコマンドで対象サーバーの値を得られるようにしてグラフ化