SQLクエリによる例外

実際の運用において、ハードウェアやインフラストラクチャの障害を除けば、データベースサービスの異常を引き起こすさまざまな要因の中で、SQLクエリによる例外は最も一般的なケースとなり得ます（おそらく他に類を見ないほどです）。ユーザーが特に注目する重要な問題として、本節ではOceanBaseにおいてSQLクエリによる例外をどのように分析し処理するかに焦点を当てて説明します。

シナリオの説明

データベースが正常に稼働している過程で、異常なSQL実行によりCPU使用率が急上昇したり、I/O負荷が高くなったり、メモリ使用量が急増したりするケースがよくあります。また、SQLの異常は通常以下のような状況に分類されます：

1. 業務アプリケーションに新しいバージョンがリリースされておらず、新たなSQL文も追加されていない場合。この状況はさらに以下の2つに分類されます：

   1. 外部業務トラフィックの変化により、SQLの実行回数が急増したり、データ量が急増したりします。

   2. 元のSQL実行計画に異常が発生します。

2. 業務アプリケーションに新しいバージョンがリリースされ、新しいSQLクエリロジックが導入された場合。その中には、スローSQLが含まれています。

ビジネスへの影響

SQLの問題は、CPU使用率の急上昇やメモリ不足などを引き起こし、業務の遅延やクラッシュなどの影響を及ぼす可能性があります。

緊急時の対応プロセス

以下では、それぞれの状況における迅速な特定と処理方法を説明します。

ビジネスで新しいバージョンがリリースされておらず、新しいSQLも追加されていない場合

ケース1：SQL実行回数またはデータ量の変化

データベースが正常にサービスを提供している場合、ビジネスのマーケティング活動やユーザー訪問数の増加などの理由により、データベース内の既存の特定の種類のSQLの実行回数またはデータ量が増加することがあります。実行計画が正常であれば、通常はビジネスレベルでのダウングレードまたは制限を優先的に選択します。ビジネスで直接制限やダウングレードが不可能な場合、この時点でインスタンスのCPU/メモリ構成を増やすことができれば、データベースの一時的な拡張を優先的に実行します。詳細については、データベース基本管理章のスケーリングアップとスケーリングダウンを参照してください。

この時点でクラスタ内に拡張用の余分なリソースがない場合、OBカーネルレベルで指定されたSQLの制限を実行することを検討できます。SQLの実行計画にhint max_concurrentを追加して結合することで、SQLの並列処理を制限し、SQLの制限を実現できます。例：

CREATE OUTLINE outline_name ON sql_id USING HINT /*+max_concurrent(1)*/;

アウトラインの結合操作の詳細については、「SQLリファレンス(MySQLモード)」のCREATE OUTLINEおよび**「SQLリファレンス(Oracleモード)」のCREATE OUTLINE**を参照してください。

ケース2：既存のSQL実行計画が異常

ビジネスで新しいバージョンがリリースされていないにもかかわらず、データベース内の既存のSQL実行計画が異常な場合、一般的に以下のような状況があります：

1. 実行計画の選択が誤っている場合、まずはplan cacheをクリアする方法で強制的にSQLを再ハードパーシュすることで、正しい計画を生成させることができます。

   ALTER system flush plan cache [tenant_list] [global];
   

   説明

   • その中でtenant_listとglobalはオプションフィールドです。tenant_listが指定されていない場合は、すべてのテナントの実行計画キャッシュをクリアし、そうでない場合は指定されたテナントのみをクリアします。globalが指定されていない場合は、このマシンの実行計画キャッシュをクリアし、そうでない場合はそのテナントが存在するすべてのサーバー上の計画キャッシュをクリアします。デフォルトは最小範囲です。
   • tenant_listの例：tenant = 'tenant1, tenant2, tenant3....'

2. インデックスの選択が誤っています。

   調査の結果、テーブル内により適切なインデックスが存在し、実行計画が正しいインデックスにアクセスしていないことが判明しました。実行計画キャッシュをクリアしても問題が解決しない場合、アウトライン結合の方法でオプティマイザーに正しい実行計画を指示することができます。

   インデックス結合は現在OCPでグラフィカル操作がサポートされています。OCPユーザーガイドを参照して対応する操作を実行してください。

   コマンドラインで結合操作を実行する場合、以下の例を参照してください：SELECT * FROM t1 WHERE v1 = 3というSQLを主キーインデックスに結合します。

   1. ビジネステナントとしてOceanBaseにログインします（mysqlコマンドに-cパラメータを追加することに注意してください）。

   2. アウトラインを作成します。

      create outline bind_to_primary_key on SELECT/*+ index(t1 primary)*/ * FROM t1 WHERE v1 = 3;
      

   3. アウトラインの作成が成功したことを確認します。

      SELECT * FROM oceanbase.gv$outline WHERE OUTLINE_NAME = 'bind_to_primary_key';
      

   4. 新しいSQLが結合されたアウトラインによって新しい実行計画を生成したことを確認します。

      アウトラインに結合されたSQLが新しいクエリを実行した後、クエリgv$plan_cache_plan_statテーブルの該当するSQLの計画情報のoutline_idを確認します。outline_idがgv$outlineで見つかったoutline_idと同じであれば、結合されたアウトラインに基づいて生成された実行計画であることを示します。そうでなければそうではありません。

      SELECT SQL_ID, PLAN_ID, STATEMENT, OUTLINE_ID, OUTLINE_DATA
            FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT
             WHERE STATEMENT LIKE '%SELECT * FROM t1 WHERE v1 = 3%';
      

   5. 生成された実行計画が期待通りであることを確認します。

      結合されたアウトラインによって生成された計画であることを確認した後、生成された計画が期待通りであるかどうかを確認する必要があります。これは、gv$plan_cache_plan_statテーブルを照会してplan_cacheにキャッシュされている実行計画の形状を確認することで行えます。

      SELECT OPERATOR, NAME FROM oceanbase.V$OB_PLAN_CACHE_PLAN_STAT
            WHERE TENANT_ID = ビジネステナントID AND IP = 'xxx.xxx.xxx.xxx'
             AND PORT = xxxx AND PLAN_ID = 前のステップで調べたplan_id;
      

3. インデックスが不足しています。

   調査の結果、現在のSQLが関与するテーブルに最適なインデックスが欠けていることが判明しました。必要に応じて対応するインデックスを作成してください。

   注意

   • インデックスの作成は、照会するすべての列を含むようにしてください。含まれる列が多いほど良く、これによりテーブルへの行数をできるだけ減らすことができます。

   • 等価条件は常に複合インデックスに含まれる列の最初に置いてください。

   • フィルタリングおよびソートデータ量の大きいフィールドは、複合インデックスに含まれる列の前に置いてください。

ビジネスの新バージョンリリースに伴い、新しいSQLロジックが発生する

ビジネスが新しくリリースされると、新しいSQLが生成されます。場合によっては、レビューされていない遅いSQLが本番環境で過剰なリソースを占有し、主要な業務に影響を与える可能性があります。このような場合、データベース内の遅いSQLを調査して問題の原因を特定し、その後の最適化処理を行う必要があります。

1. OCPを使用して遅いSQLを検索します。

   OCPのTOP SQLページで、最近一定期間における実行時間や実行回数が最も高いクエリを確認できます。これらは高い順にソートされており、計画が誤っているSQLについてはオンラインで直接バインドします。また、制限が必要なSQLについては、ビジネス開発部門の確認情報を組み合わせて、制限可能なSQLを特定する必要があります。

2. コマンドラインを使用して遅いSQLを検索します。

   1. 特定のテナントでCPU消費量が最も多いトップSQLを照会します。

      SELECT
       sql_id,
        avg(execute_time) avg_exec_time,
        count(*) cnt,
        avg(execute_time - TOTAL_WAIT_TIME_MICRO) cpu_time,
      RETRY_CNT,QUEUE_TIME,IS_HIT_PLAN
      FROM
       OCEANBASE.V$OB_SQL_AUDIT
      WHERE
        tenant_id = 1002
      GROUP BY
       1
      ORDER BY
       (avg_exec_time * cnt) desc
      limit
       5;
      

   説明

   • EXECUTE_TIME値が大きすぎる場合、待機イベントや論理読み取り回数が異常に多いことが考えられます。

   • RETRY_CNTフィールドはretry回数を示しており、回数が多い場合、ロック競合やプライマリサーバーの切り替えなどが発生している可能性があります。

   • QUEUE_TIMEが大きすぎる場合、CPUリソースが不足していることを意味します。

   • GET_PLAN_TIMEは実行計画を取得するための時間であり、時間が長い場合、通常IS_hit_PLAN＝0となり、計画がヒットしていないことを示します。

   2. その他の関連クエリ。

      OceanBaseは、最近一定期間のSQL実行履歴を記録するために、V$OB_SQL_AUDITとGV$OB_SQL_AUDITという2つの仮想テーブルを提供しています。V$OB_SQL_AUDITはローカルマシンのSQL実行履歴を保存し、GV$OB_SQL_AUDITはクラスタ全体のSQL実行履歴を保存します。必要に応じて関連クエリを実行できます。

      1. GV$OB_SQL_AUDITテーブルを照会します。例えば、あるテナントで実行時間が1秒（1000000マイクロ秒）を超えるSQLを照会します。

         SELECT * FROM oceanbase.GV$OB_SQL_AUDIT WHERE tenant_id= <tenantid> AND elapsed_time> 1000000 limit 10;
         

      2. SQL実行時間の秒ごとのヒストグラムを照会します。

         SELECT round(elapsed_time/1000000), count(*) FROM oceanbase.V$OB_SQL_AUDIT WHERE tenant_id = <tenant_id> GROUP BY  1;
         

3. OceanBaseログから遅いSQLを検索します。

   OceanBaseにおいて、SQL実行時間がtrace_log_slow_query_watermark（システムパラメータ）を超える場合、observerログにslow queryメッセージが出力されます。このパラメータのデフォルト値は100msです。observerログを通じて遅いSQLを検索できます。この方法は上記の2つの方法ほど効率的ではなく直感的ではありませんが、sql_auditのキャッシュが既に破棄されているなど、特定の状況下では役立つ場合もあります。

   遅いSQLログの記録場所：/home/admin/oceanbase/log

   1. ログ内のすべてのslow queryを確認します。

      fgrep '[slow query]' observer.log |sed -e 's/|/\n/g' | more

   2. trace_idに基づいて特定のslow queryを照会します。

      fgrep "<trace_id>" observer.log |sed -e 's/|/\n/g' | more