Posted inAI

PostgreSQL向けAI DBAの構築：DeepSeek-R1でスロークエリ分析を自動化する

AI tutorial - IT technology blog
AI tutorial - IT technology blog

現代のDevOpsにおけるDBAのボトルネック

ある金曜日の夜,本番環境のデータベースが限界に達した時のことを覚えています。RDSインスタンスのCPU使用率は98%の直線を描いて張り付きました。原因は、1万行のステージング環境では完璧に動作したものの、2千万行の本番環境では動作が停止してしまった複雑なトリプルジョインクエリでした。当時、PostgreSQLのEXPLAIN ANALYZEの出力を解読できる唯一の担当者だった私は、不足しているインデックスを一つ見つけるために、シーケンシャルスキャンやネステッドループをその後2時間も凝視し続けました。

この経験から、私はこの単調な作業を自動化することを決意しました。パフォーマンスチューニングには通常、シニアデータベース管理者（DBA）や、長年のSQL経験を持つDevOpsエンジニアが必要です。しかし、DeepSeek-R1がこの状況を変えました。このモデルは複雑な推論のために構築されているため、高度な分析を代行してくれるAIアシスタントを構築できるようになったのです。私はこれをワークフローに統合し、手動のトラブルシューティング時間を約90%短縮することに成功しました。

パフォーマンスチューニング手法の比較

アシスタントを構築する前に、AIが従来の手法と比べてどう優れているかを見てみましょう。これらは、高トラフィックなクラスターを管理してきた私の経験に基づいて分類したものです。

機能 手動分析 従来のツール (pgBadger) AI駆動 (DeepSeek-R1)
スピード 遅い (30分以上) 速い (静的レポート) 即時 (10秒未満)
複雑性 専門知識が必要 解釈が必要 平易な言葉による説明
コンテキスト認識 はい いいえ はい (スキーマ注入による)
出力 高品質 汎用的な警告 そのまま実行可能なSQLスニペット

なぜデータベースにDeepSeek-R1なのか？

LLMを使ってデータベースのパフォーマンスを管理することは魔法の杖ではありませんが、ほとんどのWebアプリにとって、それは強力な武器になります。

強み

  • パターンマッチングを超える推論能力: 次の単語を予測するだけの標準的なモデルとは異なり、DeepSeek-R1は思考の連鎖（Chain-of-Thought）プロセスを使用します。「フィルタがインデックスのないタイムスタンプにあり、テーブルが5000万行あるため、Seq ScanがI/Oを圧迫している」といった推論が可能です。
  • 大幅なコスト削減: DeepSeek-R1の価格はGPT-4oの約10分の1です。Ollamaを使用してローカルでホストすることもできるため、機密性の高いクエリ構造をインフラ外に出さないようにすることも可能です。
  • 24時間365日の監視: AIは疲れません。ログを監視し、クエリが500msのしきい値を超えた瞬間に、解決策をSlackチャンネルに通知することができます。

リスク

  • ハルシネーション: AIがすでに存在するインデックスを提案したり、使用していないPostgresバージョンの構文を使用したりする可能性があります。
  • プライバシー: クラウドAPIを使用する場合、クエリ構造をサードパーティに送信することになります。セキュリティ要件の高い環境では、必ずローカルインスタンスを使用してください。

推奨されるスタック

信頼性の高いAI DBAを構築するために必要なコンポーネントは3つだけです。

  • PostgreSQL: pg_stat_statements拡張機能を使用して、実行中のスロークエリを捕捉します。
  • DeepSeek-R1: ローカルのプライバシーを確保するため、Ollama経由で8Bまたは32Bモデルを実行します。
  • Python: データベースとAIを仲介するシンプルなスクリプトを作成します。

実装ガイド

ステップ1：PostgreSQLの設定

PostgreSQLはデフォルトでは詳細なクエリ統計を記録しません。postgresql.confファイルでpg_stat_statementsを有効にする必要があります。これは本格的なチューニングにおいて必須の設定です。

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

# Postgresの再起動後、以下を実行します：
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

これで、データベースはすべてのクエリ、実行時間、メモリ使用量を記録するようになります。

ステップ2：実行計画の取得

生のSQLだけでは、AIが適切な回答を出すには不十分です。実行計画（Execution Plan）が必要です。EXPLAIN (ANALYZE, COSTS, BUFFERS, FORMAT JSON)を使用してください。JSON形式は、標準のテキストツリーよりもAIが解析しやすくなります。

ステップ3：プロンプトエンジニアリング

修正案の質はプロンプトに完全に依存します。AIに「シニアスタッフDBA」として振る舞うよう指示し、テーブルスキーマを提供することで、はるかに優れた結果が得られることがわかりました。以下は私が日常的に使用しているテンプレートです：

SYSTEM_PROMPT = """
あなたはシニアPostgreSQL DBAです。提供された実行計画（EXPLAIN PLAN）を分析してください。
1. ボトルネックを特定する（例：Seq Scan、高I/O、Hash Joinなど）。
2. 非効率な理由を一文で説明する。
3. CREATE INDEX CONCURRENTLYコマンドを提示する。
4. 書き込みパフォーマンスへの潜在的な影響について警告する。
"""

ステップ4：自動化スクリプト

このPythonスニペットは、ollamaライブラリを使用してスロークエリを処理します。私たちはこのロジックを使用して、レポートダッシュボードのクエリ時間を15秒から120msまで短縮しました。

import ollama

def analyze_slow_query(query_text, explain_output):
    prompt = f"SQL: {query_text}\nPlan: {explain_output}"

    response = ollama.chat(model='deepseek-r1:8b', messages=[
        {'role': 'system', 'content': SYSTEM_PROMPT},
        {'role': 'user', 'content': prompt},
    ])

    return response['message']['content']

# スロークエリのデータ例
sql = "SELECT * FROM logs WHERE user_id = 9921 AND event_type = 'login';"
plan = "Seq Scan on logs (cost=0.00..5421.10 rows=5 width=200) (Filter: ...)"

print(analyze_slow_query(sql, plan))

現場からの教訓

最初にこれを導入したとき、AIは100GBのテーブルに対して40GBのカバリングインデックスを提案しました。読み取り速度の問題は解決しましたが、書き込みスループットが危うくクラッシュしそうになりました。今では、必ずプロンプトに制約を含めるようにしています：「読み取り速度と書き込みオーバーヘッドのトレードオフを考慮してください。」

もう一つのヒント：AIに直接コードを実行させてはいけません。AIに提案を生成させ、SQLが実行される前に人間が「承認」をクリックするようにします。これにより、AIにログの読み取りやコスト計算といった退屈な作業を任せつつ、制御を維持することができます。

DeepSeekに初期分析を任せることで、私は「ログの読者」であることをやめ、「レビュアー」になりました。これは、本番環境をスムーズに稼働させ続けるための、より速く、より賢明な方法です。

Share:
Tags: