AnalyticDB for MySQL は、ページングキャッシュ機能を提供します。 ページングキャッシュ機能を使用すると、LIMIT、OFFSET、および ORDER BY 句で大量のデータを含むページングクエリの効率を向上させることができます。 これにより、深いページングクエリのパフォーマンスの問題とリソースのボトルネックを解消できます。 このトピックでは、ページングキャッシュ機能を使用してページングクエリのパフォーマンスを最適化する方法について説明します。
前提条件
V3.2.3 以降の AnalyticDB for MySQL クラスターが作成されていること。
AnalyticDB for MySQL クラスターのマイナーバージョンを表示および更新するには、マイナーバージョンを表示および更新するには、AnalyticDB for MySQL コンソール にログインし、構成情報 セクションの クラスター情報 ページに移動します。
概要
E コマース プラットフォームのユーザーは、売上高または総合スコアに基づいて商品を並べ替え、高品質の商品を優先表示したいと考えています。 ほとんどの場合、アプリケーションはページネーション方式を使用して結果を表示し、一度に大量のデータを読み込むことによって発生するパフォーマンスの問題を防ぎ、ページの応答時間を短縮します。
データベースの一般的な方法は、列に基づいてデータをソートし、LIMIT句と OFFSET 句を使用してページングクエリを実行することです。 LIMIT 句はページごとに表示されるデータエントリの数を指定し、OFFSET 句はページごとのデータの開始オフセットを指定します。 たとえば、ページごとに 100 個のデータエントリを表示するとします。最初のページのデータをクエリするには、次のステートメントを実行します。 SELECT * FROM t_order ORDER BY id LIMIT 0, 100。 10,001 ページ目のデータをクエリするには、次のステートメントを実行します。 SELECT * FROM t_order ORDER BY id LIMIT 1000000, 100。
大量のデータに対してページングクエリを実行すると、グローバルソートと頻繁なルックアップ操作のオーバーヘッドが原因で、深いページングクエリによってデータベースのパフォーマンスが大幅に低下する可能性があります。 この問題は、AnalyticDB for MySQL ではより複雑になります。 異なるノード間でシャッフルされるデータ量を削減するために、各ストレージノードは topN 計算を実行し、Gather ノードはすべてのストレージノードの実行結果を集計して最終的な結果セットを取得します。
最終的な結果セットが正しいことを確認するために、システムは書き換えられた SQL ステートメントを各ストレージノードに割り当てて実行する必要があります。 たとえば、10,001 ページ目のデータエントリをクエリするために、各ストレージノードは次の SQL ステートメントを受け取ります。 SELECT * FROM t_order ORDER BY id LIMIT 0, 1000100。 その結果、Gather ノードは 1,000,100 × ストレージノード数 のデータエントリをソートして、100 エントリを取得する必要があります。
ソートされるデータ量はページネーションの深さに比例して増加し、パフォーマンスは急激に低下します。 これにより、メモリと CPU リソースに大きな負担がかかり、メモリ不足 (OOM) エラーのリスクが高まります。 これが、深いページングクエリによって発生するパフォーマンスの問題です。
深いページングクエリによって発生するパフォーマンスの問題を解決するために、AnalyticDB for MySQL はページングキャッシュ機能を提供します。 ページングクエリを初めて開始すると、システムはデータベースからデータをクエリし、クエリ結果を一時キャッシュテーブルに格納します。 同じ SQL パターンを共有する後続のページングクエリを開始すると、システムは一時キャッシュテーブルからデータを読み取り、ソート操作の繰り返しを防ぎます。 これにより、深いページングクエリによって発生するパフォーマンスの問題が効果的に解決され、ORDER BY 句によって発生する OOM エラーが防止されます。 AnalyticDB for MySQL は、立ち退きポリシーに基づいて不要になったキャッシュデータを自動的にクリアし、リソースが適切に使用されるようにします。
ページングキャッシュ機能は、次のシナリオに適しています。
使用方法
キャッシュデータベースを構成する
ページングキャッシュ機能を使用してクエリ結果をキャッシュする前に、ページングクエリの 一時キャッシュテーブル を格納するデータベースを指定することをお勧めします。 データベースを指定しない場合、一時キャッシュテーブルは接続されている内部データベースに格納されます。 ページングキャッシュ機能を有効にすると、一時キャッシュテーブルが自動的に作成されます。
外部データベースをキャッシュデータベースとして指定することはできません。
たとえば、paging_cache データベースをキャッシュデータベースとして指定できます。 他のデータベースを指定することもできます。
SET ADB_CONFIG PAGING_CACHE_SCHEMA=paging_cache;ページングクエリに対してページングキャッシュ機能を有効にする
複数のページングクエリが同じ SQL パターンを共有している場合は、SQL ステートメントにヒントを追加してパフォーマンスを向上させることができます。 ヒントを含むページングクエリを初めて開始すると、システムは一時キャッシュテーブルを作成してページングクエリ結果を格納します。 同じ SQL パターンを共有する後続のページングクエリを開始するときに、SQL ステートメントに同じヒントを追加できます。 これにより、システムはデータベースに繰り返しアクセスすることなく、一時キャッシュテーブルからデータを読み取ります。
制限
LIMIT 句と OFFSET 句を削除した後、ページングクエリを実行するデータエントリ数は 1 億未満である必要があります。
クエリ対象のデータエントリ数が 1 億を超える場合は、チケットを起票 して、データエントリ数の上限を調整してください。
ページングキャッシュ機能を有効にする方法
次のいずれかのヒントを使用して、ページングキャッシュ機能を有効にできます。
paging_id=<paging_id>paging_idパラメーターは、同じ SQL パターンを共有するがLIMIT句とOFFSET句の値が異なる一連のページングクエリ用に作成されるキャッシュテーブルを指定します。 クライアントは、一連のページングクエリのキャッシュテーブルを一意に識別するための一意の ID を生成する必要があります。指定された
paging_idパラメーターが存在しない場合は、キャッシュテーブルが作成されます。クエリされた
paging_idパラメーターが存在し、クエリに含まれる SQL パターンがpaging_idパラメーターに対応する SQL パターンと一致する場合は、クエリはキャッシュにヒットします。クエリされた
paging_idパラメーターが存在するが、クエリに含まれる SQL パターンがpaging_idパラメーターに対応する SQL パターンと一致しない場合は、エラーが発生します。paging_idパラメーターがすでに使用されているかどうかを確認できます。 詳細については、このトピックの「キャッシュテーブルに関する情報をクエリする」セクションをご参照ください。
説明paging_idパラメーターは、次の命名規則を満たしている必要があります。パラメーター名は 1 ～ 127 文字で、文字、数字、およびアンダースコア (_) を使用できます。 パラメーター名は、文字またはアンダースコア (_) で始まる必要があります。 パラメーター名には、一重引用符 (')、二重引用符 (")、感嘆符 (!)、またはスペースを含めることはできません。 パラメーター名は、SQL の 予約語 にすることはできません。たとえば、ページングキャッシュ機能を使用する一連のページングクエリの結果のページング ID を
paging123に設定できます。/*paging_id=paging123*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;paging_cache_enabled=trueこの方法では、ヒントを頻繁に変更する必要はありません。 サーバーは、
LIMIT句とOFFSET句を削除した SQL パターンを使用してページング ID を生成し、一連のページングクエリを識別します。SQL パターンのマッチングへの依存により、この方法の柔軟性が制限されます。 LIMIT 句と OFFSET 句を除く同じ SQL パターンを共有するページングクエリ用のキャッシュテーブルが存在しない場合は、キャッシュテーブルが作成されます。 LIMIT 句と OFFSET 句を除く同じ SQL パターンを共有するページングクエリ用のキャッシュテーブルが存在する場合は、キャッシュテーブルを使用してデータがクエリされます。
ステートメントの例:
/*paging_cache_enabled=true*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;
キャッシュテーブルの作成に失敗した場合は、ページングクエリのキャッシュデータをクリアし、ページングクエリを再起動してキャッシュテーブルを作成する必要があります。
キャッシュテーブルに関する情報をクエリする
現在のクラスター内のすべてのページングクエリのキャッシュテーブルに関する情報 (ページング ID、キャッシュサイズ、キャッシュステータスなど) をクエリできます。
SELECT * FROM INFORMATION_SCHEMA.KEPLER_PAGING_CACHE_STATUS_MERGED;キャッシュテーブルの最大数を指定する
現在のクラスター内のキャッシュテーブルの最大数を指定できます。 デフォルト値: 100。
SET ADB_CONFIG PAGING_CACHE_MAX_TABLE_COUNT=100;キャッシュテーブルの合計数が制限を超えているときにキャッシュテーブルを作成しようとすると、エラーが発生します。 エラーメッセージの例:
ページングキャッシュ数が制限を超えています。 使用されていないキャッシュをクリーンアップするか、SET ADB_CONFIG PAGING_CACHE_MAX_TABLE_COUNT=xxx を使用して関連パラメーターを増やしてください。エラーメッセージに基づいて、不要になったキャッシュデータをクリアするか、キャッシュテーブルの最大数を増やしてください。
キャッシュテーブルの有効期間を指定する
キャッシュテーブルの有効期間を指定できます。 単位: 秒。 有効期間が終了すると、キャッシュテーブルは無効になります。 同じ SQL パターンを共有する後続のページングクエリを開始すると、システムはデータベースアクセスを実行し、キャッシュテーブルを更新します。 ほとんどの場合、レポートの同時実行制御シナリオでキャッシュテーブルの有効期間を指定できます。
たとえば、/*paging_cache_enabled=true, paging_cache_validity_interval=300*/ ヒントを使用して、作成後 300 秒以内にキャッシュテーブルを有効にすることができます。 ステートメントの例:
/*paging_cache_enabled=true, paging_cache_validity_interval=300*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;ページングクエリのキャッシュデータをクリアする
ページングキャッシュ機能を使用してページングクエリ結果をキャッシュする場合、キャッシュデータは AnalyticDB for MySQL のホットストレージスペースにキャッシュされます。 特定のキャッシュデータが不要になった場合は、キャッシュデータをクリアして使用可能なストレージを増やすことができます。
手動クリア
SQL パターンで指定されたページングクエリのキャッシュデータ
/*paging_cache_enabled=true, invalidate_paging_cache=true*/ヒントを使用して、SQL パターンで指定されたページングクエリのキャッシュデータをクリアします。ステートメントの例:
/*paging_cache_enabled=true,invalidate_paging_cache=true*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;paging_idパラメーターで指定されたページングクエリのキャッシュデータpaging_idパラメータで指定されたページングクエリのキャッシュデータをクリアします。ステートメントの例:
CLEAN_PAGING_CACHE paging123;説明paging_idパラメーターの値を取得する方法については、このトピックの「キャッシュテーブルに関する情報をクエリする」セクションをご参照ください。
自動クリア
キャッシュの有効期限を指定して、指定された時間範囲内にアクセスされなかったページングクエリのキャッシュデータをクリアできます。 デフォルト値: 600。 単位: 秒。 デフォルトでは、10 分以内にアクセスされなかったキャッシュデータは自動的にクリアされます。
SET ADB_CONFIG PAGING_CACHE_EXPIRATION_TIME=600;クラスターのページングキャッシュ機能を無効にする
クラスターのページングキャッシュ機能を無効にすると、この機能に関連するすべてのヒントが無効になり、ページングクエリは元のクエリ方法を使用して処理されます。
SET ADB_CONFIG PAGING_CACHE_ENABLE=false;ページングキャッシュ機能を無効にした後、SHOW ADB_CONFIG KEY=PAGING_CACHE_ENABLE; ステートメントを実行して、構成が有効になっているかどうかを確認できます。
一般的なエラーとトラブルシューティング
ページングキャッシュの準備に失敗し、キャッシュが使用できません
エラーメッセージ:
ページングキャッシュの準備に失敗し、キャッシュが使用できません。 /*paging_cache_enabled=true,invalidate_paging_cache=true*/ を使用して使用できないキャッシュをクリーンアップするか、/*paging_id=xxx*/ で特定の pagingId を設定して新しいキャッシュを生成してください。 新旧のキャッシュデータに不整合が生じる可能性があることに注意してください。原因: ページングキャッシュ機能を使用してデータをクエリすると、ノードの再起動やスケーリングなどの例外が発生する可能性があります。 ページングクエリが作成に失敗したキャッシュテーブルにヒットした場合、サーバーはデータベースアクセスを実行したり、キャッシュテーブルを自動的に再作成したりしません。 サーバーはエラーをスローします。
解決策: データの整合性を確保するために、次の操作を実行します。データエクスポートシナリオでは、エクスポートされたデータと使用できないキャッシュデータをクリアしてから、ページングクエリを再起動してキャッシュテーブルを再作成することをお勧めします。 他のシナリオでは、使用できないキャッシュデータをクリアするか、paging_id パラメーターに新しい値を指定してから、ページングクエリを再起動してキャッシュテーブルを再作成することをお勧めします。
パフォーマンスの比較
100 GB の TPC-H データセットを使用して、データエクスポートシナリオにおけるページングクエリに対するページングキャッシュ機能の最適化効果を評価します。
このテストでは、100 万エントリのエクスポートデータを使用します。 各ページには 100,000 エントリが含まれています。 次のステートメントを実行して、最初のページでページングクエリを実行します。
-- ページングキャッシュ機能を使用しない一般的なページングクエリ
SELECT * FROM lineitem ORDER BY l_orderkey,l_linenumber LIMIT 0,100000;
-- ページングキャッシュ機能を使用するページングクエリ (データエクスポートシナリオでは ORDER BY が削除されます)
/*paging_cache_enabled=true*/ SELECT * FROM lineitem LIMIT 0,100000;クエリリクエストは単一のノードで実行されます。 ページングキャッシュ機能を使用しない場合、最初のページのクエリにかかる時間は約 20 秒です。 ページングキャッシュ機能を使用する場合、最初のページのクエリにかかる時間は約 15 秒です。 ページングキャッシュ機能を使用すると、クエリのパフォーマンスが向上します。 10 ページ目のデータをクエリする場合、ページングキャッシュ機能を使用しない場合、クエリにかかる時間は約 40 秒です。 ページングキャッシュ機能を使用する場合、クエリにかかる時間は約 0.1 秒です。 ページネーションの深さが増すにつれて、ページングキャッシュ機能を使用することで得られるパフォーマンスの向上はより顕著になります。
クエリリクエストは、単一のコンカレンシーモードで実行されます。 データエクスポートプロセス中、一般的なページングクエリ の平均応答時間は 54,391 ms です。 ページングキャッシュ機能が有効になると、平均応答時間は 525 ms になります。 パフォーマンスは約 103 倍 向上します。 CPU 使用率とメモリ使用量は大幅に減少します。
手順
