Azure Database for MySQL - フレキシブルサーバーのトラブルシューティングのベストプラクティス

[アーティクル]
12/19/2023

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

重要

Azure Database for MySQL の単一サーバーは提供終了パスにあります。 Azure Database for MySQL フレキシブルサーバーにアップグレードすることを強くお勧めします。 Azure Database for MySQL フレキシブルサーバーへの移行の詳細については、「Azure Database for MySQL 単一サーバーの動作」を参照してください。

次のセクションを使用して、Azure Database for MySQL フレキシブルサーバーデータベースをスムーズに実行し続け、この情報を、スキーマが最適に設計され、アプリケーションに最適なパフォーマンスを提供するための基本原則として使用します。

インデックスの数をチェックする

ビジー状態のデータベース環境では、I/O 使用率が高くなる可能性があります。これは、データアクセスパターンの低下を示す可能性があります。未使用のインデックスは、ディスク領域とキャッシュを消費し、書き込み操作 (IN Standard Edition RT/DELETE/UPDATE) の速度が低下するため、パフォーマンスに悪影響を与える可能性があります。未使用のインデックスでは、不必要に多くのストレージ領域が消費され、バックアップサイズが大きくなります。

インデックスを削除する前に、十分な情報を収集して、それが使われなくなっていることを確認してください。この検証は、四半期または年に 1 回だけ実行されるクエリにとって重要なインデックスを誤って削除するのを防ぐのに役立ちます。また、インデックスが一意性または順序付けの適用に使われているかどうかを検討してください。

注意

忘れずにインデックスを定期的に確認し、テーブルのデータに対する変更に基づいて必要な更新を行ってください。

SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_name;

(または)

use information_schema; select tables.table_name, statistics.index_name, statistics.cardinality, tables.table_rows from tables join statistics on (statistics.table_name = tables.table_name and statistics.table_schema = '<YOUR DATABASE NAME HERE>' and ((tables.table_rows / statistics.cardinality) > 1000));

サーバー上で最も混雑しているインデックスを一覧表示する

次のクエリからの出力は、データベースサーバー上のすべてのテーブルとスキーマで最もよく使用されるインデックスに関する情報を提供します。この情報は、各インデックスに対する読み取りに対する書き込みの比率と、読み取りの待機時間の数値、および個々の書き込み操作を識別するのに役立ちます。これは、基になるテーブルと依存クエリに対してさらにチューニングが必要であることを示す可能性があります。

SELECT 
object_schema AS table_schema, 
object_name AS table_name, 
index_name, count_star AS all_accesses, 
count_read, 
count_write, 
Concat(Truncate(count_read / count_star * 100, 0), ':', 
Truncate(count_write / count_star * 100, 0)) AS read_write_ratio, 
 count_fetch AS rows_selected , 
 count_insert AS rows_inserted, 
 count_update AS rows_updated, 
 count_delete AS rows_deleted, 
 Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency , 
 Concat(Round(sum_timer_fetch / 1000000000000, 2), ' s') AS select_latency, 
 Concat(Round(sum_timer_insert / 1000000000000, 2), ' s') AS insert_latency, 
Concat(Round(sum_timer_update / 1000000000000, 2), ' s') AS update_latency, 
 Concat(Round(sum_timer_delete / 1000000000000, 2), ' s') AS  delete_latency 
FROM performance_schema.table_io_waits_summary_by_index_usage 
WHERE index_name IS NOT NULL AND count_star > 0 
ORDER BY sum_timer_wait DESC

主キーの設計を確認する

Azure Database for MySQL フレキシブルサーバーでは、すべての非一時テーブルに InnoDB ストレージエンジンが使用されます。 InnoDB では、データは B ツリー構造を使ってクラスター化インデックス内に格納されます。テーブルは主キーの値に基づいて物理的に編成されます。つまり、行は主キーの順序で格納されます。

InnoDB テーブルの各セカンダリキーエントリには、データが格納されている主キー値へのポインターが含まれています。言い換えると、セカンダリインデックスのエントリには、エントリが指している主キーの値のコピーが含まれます。したがって、主キーの選択は、テーブルでのストレージオーバーヘッドの量に直接影響します。

キーが実際のデータ (ユーザー名、メールアドレス、SSN など) から派生する場合は、"ナチュラルキー" と呼ばれます。キーが人工であり、データから派生していない場合 (自動インクリメント整数など)、合成キーまたは代理キーと呼ばれます。

通常は、ナチュラルな主キーを使わないことをお勧めします。多くの場合、このようなキーは非常に幅が広く、1 つまたは複数の列の長い値を含みます。これにより、主キーの値が各セカンダリキーエントリにコピーされるときに、大きなストレージオーバーヘッドが発生する可能性があります。さらに、通常、自然キーは事前に決められた順序に従わないため、パフォーマンスが大幅に低下し、行が挿入または更新されたときにページの断片化が引き起こされます。これらの問題を回避するには、ナチュラルキーの代わりに単調に増加する代理キーを使います。自動インクリメント (big) 整数列は、単調に増加するサロゲートキーの良い例です。列の特定の組み合わせが必要な場合は、一意にして、それらの列を一意のセカンダリキーとして宣言します。

アプリケーションの構築の初期段階では、テーブルが 20 億行に近づき始める時期を想像するとは思えないかもしれません。その結果、ID (主キー) 列のデータ型に符号付き 4 バイト整数を使用することを選択できます。すべてのテーブル主キーをチェックし、8 バイト整数 (BIGINT) 列を使用して、大量または増加の可能性に対応するように切り替えてください。

Note

データ型とその最大値について詳しくは、MySQL リファレンスマニュアルの「データ型」をご覧ください。

カバリングインデックスを使用する

前のセクションでは、MySQL のインデックスが B ツリーとして編成されることを説明しました。クラスター化インデックスでは、リーフノードには基になるテーブルのデータページを含まれます。セカンダリインデックスは、クラスター化インデックスと同じ B ツリー構造を持ち、クラスター化インデックスまたはヒープを使用するテーブルまたはビューでそれらを定義できます。セカンダリインデックスの各インデックス行には、非クラスター化キー値と行ロケーターが含まれます。このロケーターは、キー値があるクラスター化インデックスまたはヒープのデータ行を指します。その結果、セカンダリインデックスを含む検索で主キー値を取得するには、ルートノードからブランチノードを経由して正しいリーフノードに移動する必要があります。その後、システムは主キーインデックスでランダムな IO 読み取りを実行して (もう一度ルートノードからブランチノードを通して正しいリーフノードに移動します)、データ行を取得します。

データ行を取得するための主キーインデックスでのこのような余分なランダム IO 読み取りを避けるには、クエリで必要なすべてのフィールドを含むカバリングインデックスを使います。一般に、この方法を使うと、I/O にバインドされるワークロードやキャッシュされるワークロードにメリットがあります。そのため、ベストプラクティスとして、カバリングインデックスはメモリに収まり、すべての行をスキャンするより小さく、読み取り効率が高いため、カバーインデックスを使用します。

たとえば、2000 年 1 月 1 日以降に入社したすべての従業員を検索するために使用するテーブルについて考えます。

mysql> show create table employee\G
*************************** 1. row ***************************
       Table: employee
Create Table: CREATE TABLE `employee` (
  `empid` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(10) DEFAULT NULL,
  `lname` varchar(10) DEFAULT NULL,
  `joindate` datetime DEFAULT NULL,
  `department` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`empid`)
  ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)`

`mysql> select empid, fname, lname from employee where joindate > '2000-01-01';

このクエリで EXPLAIN プランを実行すると、現在はインデックスが使われておらず、従業員レコードのフィルター処理に where 句のみが使われていることがわかります。

mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

ただし、where 句の列をカバーするインデックスを、投影列と共に追加すると、インデックスを使用して列をより迅速かつ効率的に見つけることができます。

mysql> CREATE INDEX cvg_idx_ex ON employee (joindate, empid, fname, lname);

ここで、同じクエリで EXPLAIN プランを実行すると、"Extra" フィールドに "Using Index" という値が表示されます。これは、InnoDB が前に作成したインデックスを使ってクエリを実行することを意味し、これがカバリングインデックスであることを確認できます。

mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: range
possible_keys: cvg_idx_ex
          key: cvg_idx_ex
      key_len: 6
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)

Note

クエリを正しく処理するには、カバリングインデックスの列を正しい順序で選ぶことが重要です。一般的なルールは、最初にフィルター処理 (WHERE 句)、次に並べ替えとグループ化 (ORDER BY と GROUP BY)、最後にデータプロジェクション (SELECT) を行うように列を選びます。

前の例から、クエリにカバリングインデックスを使うと、レコード取得パスがいっそう効率的になり、同時実行が非常に多いデータベース環境でのパフォーマンスが最適化されます。

次のステップ

重要な質問に対する回答を見つけたり、質問を投稿または回答したりするには、Stack Overflow にアクセスしてください。

Azure Database for MySQL - フレキシブル サーバーのトラブルシューティングのベスト プラクティス