sys_schema を使ってパフォーマンスを調整して Azure Database for MySQL - フレキシブル サーバーのデータベースを保守する

適用対象: Azure Database for MySQL - 単一サーバー Azure Database for MySQL - フレキシブル サーバー

MySQL 5.5 で初めて導入された MySQL performance_schema では、メモリ割り当て、ストアド プログラム、メタデータ ロックなど、多くの重要なサーバー リソースのためのインストルメンテーションが提供されています。ただし、performance_schema には 80 以上のテーブルが含まれ、必要な情報を入手するには performance_schema 内のテーブルや information_schema のテーブルの結合が必要になることがよくあります。 performance_schema と information_schema の両方を基にして作成されている sys_schema は、読み取り専用のデータベースでユーザー フレンドリなビューの強力なコレクションを提供し、Azure Database for MySQL フレキシブル サーバーのバージョン 5.7 では完全に有効になっています。

sys_schema には 52 個のビューがあり、各ビューには次のいずれかのプレフィックスが付いています。

• host_summary または io: I/O 関連の待機時間。
• innoDB: InnoDB バッファーの状態とロック。
• メモリ:ホストとユーザーによるメモリ使用量。
• スキーマ:自動インクリメントやインデックスなどのスキーマ関連の情報。
• statement: SQL ステートメントに関する情報。フル テーブル スキャンや長いクエリ時間が発生するステートメントである場合があります。
• ユーザー:消費され、ユーザーごとにグループ化されたリソース。 ファイル I/O、接続、メモリなどです。
• wait: ホストまたはユーザーごとにグループ化された待機イベント。

では、sys_schema の一般的な使用パターンをいくつか見てみましょう。 まず、使用パターンをパフォーマンスのチューニングとデータベース メンテナンスの 2 つのカテゴリにグループ化します。

パフォーマンスのチューニング

sys.user_summary_by_file_io

IO は、データベースで最もコストのかかる操作です。 sys.user_summary_by_file_io ビューのクエリを行うことにより、平均 IO 待機時間がわかります。 既定値の 125 GB でプロビジョニングされたこの例のストレージでは、IO 待機時間は約 15 秒です。

Azure Database for MySQL のフレキシブル サーバーではストレージに応じて IO が増減するので、このプロビジョニングされたストレージを 1 TB に増やすと、IO 待機時間は 571 ミリ秒に減ります。

sys.schema_tables_with_full_table_scans

慎重に計画しても、多くのクエリでフル テーブル スキャンが行われる可能性があります。 インデックスの種類とそれを最適化する方法について詳しくは、「クエリのパフォーマンスをトラブルシューティングする方法」をご覧ください。 フル テーブル スキャンはリソースを大量に消費し、データベースのパフォーマンスを低下させます。 フル テーブル スキャンが行われたテーブルを調べる最も簡単な方法は、sys.schema_tables_with_full_table_scans ビューのクエリを行うことです。

sys.user_summary_by_statement_type

データベースのパフォーマンスの問題をトラブルシューティングするには、データベースの内部で起こっているイベントを明らかにすると役に立つ場合があり、sys.user_summary_by_statement_type ビューがそれに使えることがあります。

この例の Azure Database for MySQL フレキシブル サーバーは、低速クエリ ログを 44579 回フラッシュするのに 53 分かかっています。 それは、長い時間と多数の IO です。 低速のクエリ ログを無効にするか、Azure portal への低速のクエリ ログインの頻度を減らすことで、このアクティビティを削減できます。

データベース メンテナンス

sys.innodb_buffer_stats_by_table

[! 重要]

このビューにクエリを実行すると、パフォーマンスに影響する場合があります。 このトラブルシューティングは、ピーク時以外の営業時間に実行することをお勧めします。

InnoDB バッファー プールはメモリ内に存在し、DBMS とストレージの間の主なキャッシュ メカニズムです。 InnoDB バッファー プールのサイズはパフォーマンス レベルに関連付けられており、別の製品 SKU を選ばない限り変更できません。 オペレーティング システムのメモリと同様に、古いページはスワップ アウトされて新しいデータのための領域が確保されます。 InnoDB バッファー プールのメモリを最も多く消費しているテーブルを調べるには、sys.innodb_buffer_stats_by_table ビューのクエリを行います。

上の図では、システム テーブルとビューを除くと、WordPress サイトの 1 つをホストしている mysqldatabase033 データベース内の各テーブルが、16 KB つまり 1 ページのデータでメモリを占めていることがわかります。

Sys.schema_unused_indexes と sys.schema_redundant_indexes

インデックスは、読み取りのパフォーマンスを向上させる優れたツールですが、挿入とストレージの追加コストがかかります。 Sys.schema_unused_indexes と sys.schema_redundant_indexes は、使われていないインデックスまたは重複するインデックスについての詳しい情報を提供します。

まとめ

まとめると、sys_schema はパフォーマンスのチューニングとデータベースのメンテナンスの両方に対して優れたツールです。 お使いの Azure Database for MySQL フレキシブル サーバー インスタンスで、この機能を活用してください。

次のステップ

• 最も気になる質問への回答を探したり、新しい質問や回答を投稿したりするには、Stack Overflow をご覧ください。