SQLクエリによる異常|V4.4.2|OceanBaseデータベース|OceanBaseデータベースドキュメント|分散データベース

本番運用において、ハードウェアやインフラストラクチャの障害を除けば、データベースサービスの異常を引き起こす多様な要因の中で、SQLクエリによる異常は最も一般的なケースであると言えます。ユーザーが特に注目すべき課題として、このセクションでは、OceanBaseにおいてSQLクエリによる異常をどのように分析・処理するかに焦点を当てて説明します。

シナリオの概要

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

業務アプリケーションの新しいバージョンがリリースされておらず、新規のSQLが追加されていない場合。この場合はさらに以下の2つに分類されます：
1. 外部業務トラフィックの変化により、SQLの実行回数が急増したり、データ量が急増したりする場合。
2. 元のSQL実行計画に異常がある場合。
業務アプリケーションの新しいバージョンがリリースされ、新しい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)*/;

アウトラインのバインディング操作の詳細については、計画のバインディングを参照してください。

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

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

実行計画の選択が誤っています。この場合、まずplan cacheをクリアする方法を実行し、SQLを強制的に再びハードパースに送り直して正しい計画を生成させることができます。
```
ALTER system flush plan cache [tenant_list] [global];
```
説明
- ここでtenant_listとglobalはオプションフィールドです。tenant_listが指定されていない場合、すべてのテナントの実行計画キャッシュをクリアします。指定されている場合は、指定されたテナントのみをクリアします。globalが指定されていない場合、ローカルの実行計画キャッシュをクリアします。指定されている場合は、そのテナントが存在するすべてのサーバー上の計画キャッシュをクリアします。デフォルトでは最小範囲です。
- tenant_listの例：tenant = 'tenant1, tenant2, tenant3....'
インデックスの選択が誤っています。

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

インデックスのバインディングは現在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$OB_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$OB_PLAN_CACHE_PLAN_STAT テーブルをクエリして、plan_cache にキャッシュされた実行計画の形を確認することができます。
```
SELECT OPERATOR, NAME FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT
      WHERE TENANT_ID = ビジネステナントID AND IP = 'xxx.xxx.xxx.xxx'
       AND PORT = xxxx AND PLAN_ID = 前のステップで調べた plan_id;
```
インデックスが欠落しています。

調査の結果、現在のSQLが関与するテーブルに最適なインデックスが欠けていることが判明しました。必要に応じて対応するインデックスを作成してください。
注意
- インデックスの作成は、可能な限りすべてのクエリ列を含めるようにしてください：含まれる列が多いほど良く、これによりテーブルへの戻り行数をできるだけ減らすことができます。
- 等値条件は常に複合インデックスが含む列の最初に置いてください。
- データ量の多いフィールドのフィルタリングとソートは、複合インデックスが含む列の前に置いてください。

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

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

OCPを使用してスローSQLを検索する。

OCPのTOP SQLページで、直近の期間において実行時間と実行回数が最も高いクエリを確認できます。これらは高い順に並べ替えられており、実行計画が誤っているSQLについては、オンラインで直接バインドできます。リソース制限が必要なSQLについては、ビジネス開発部門の確認情報と照合し、リソース制限が可能なSQLを特定する必要があります。
コマンドラインでスロー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を伴い、計画にヒットしていないことを意味します。
1. その他の関連クエリ。
  
  OceanBaseは、最近のSQL実行履歴を記録するために、仮想テーブルV$OB_SQL_AUDITとGV$OB_SQL_AUDITを提供しています。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;
```
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