Cloud SQL PostgreSQLのlog_lock_waits設定について

2025.08.15

このブログシリーズ「クラウドセキュリティ実践集」では、一般的なセキュリティ課題を取り上げ、「なぜ危険なのか？」というリスクの解説から、「どうやって直すのか？」という具体的な修復手順（コンソール、gcloud CLI、Terraformなど）まで、分かりやすく解説します。

この記事では、Cloud SQLのPostgreSQLインスタンスでlog_lock_waitsパラメータが無効化されている問題について、リスクと対策を解説します。

ポリシーの説明

Cloud SQLのPostgreSQLインスタンスでlog_lock_waitsパラメータを有効化することで、deadlock_timeoutを超えるロック待機を記録し、デッドロックやパフォーマンス問題の早期発見を可能にします。

このパラメータが無効化されていると、アプリケーションのハングやタイムアウトの原因となるロック競合を特定できず、サービスの可用性とパフォーマンスに重大な影響を与える可能性があります。

log_lock_waitsを有効化すると、deadlock_timeout（デフォルト1秒）を超えてロックを待機しているプロセスについて、ログエントリが生成されます。これにより、ロック待機のパターンを分析し、ボトルネックを特定できます。

修復方法

コンソールでの修復手順

Google Cloud コンソールを使用して、Cloud SQLのPostgreSQLインスタンスでlog_lock_waitsパラメータを有効にします。

Google Cloud Consoleにログインし、Cloud SQLのページに移動します
- ナビゲーションメニューから「SQL」を選択
対象のPostgreSQLインスタンスを選択
- インスタンス一覧から設定を変更したいインスタンスをクリック
「編集」ボタンをクリック
- インスタンスの詳細ページ上部にある「編集」をクリック
「構成オプション」セクションを展開
- 左側のメニューから「構成」を選択
- 「データベースのフラグ」セクションを開く
log_lock_waitsフラグを追加
- 「＋項目を追加」をクリック
- フラグ名のドロップダウンから「log_lock_waits」を選択
- 値を「on」に設定
関連するデッドロック設定も調整（推奨）
- 「＋項目を追加」をクリック
- 「deadlock_timeout」フラグを追加（デフォルト: 1s）
- 必要に応じて値を調整（例: 500ms = 0.5秒）
- 「log_line_prefix」フラグを追加して、’%m [%p] %q%u@%d ‘のようなフォーマットを設定（ログの可読性向上）
設定を保存
- ページ下部の「保存」ボタンをクリック
- 変更の確認ダイアログで「保存して再起動」を選択
設定の確認
- インスタンスが再起動後、「データベースのフラグ」セクションでlog_lock_waitsが「on」になっていることを確認

Terraformでの修復手順

Cloud SQLのPostgreSQLインスタンスでロック待機ログを有効にするTerraformコードと、主要な修正ポイントを説明します。

# Cloud SQL PostgreSQL インスタンスの設定
resource "google_sql_database_instance" "postgresql" {
    >>> Skip
    # ロック待機ログの有効化
    database_flags {
      name  = "log_lock_waits"
      value = "on"
    }

    # デッドロックタイムアウトの設定（ミリ秒単位）
    database_flags {
      name  = "deadlock_timeout"
      value = "1000"  # 1秒（デフォルト）
    }
}

ベストプラクティス

ベストプラクティスとして以下をまとめておきます。

deadlock_timeoutの適切な設定
- アプリケーションの特性に応じて調整（デフォルト1秒）
- 短すぎると誤検知、長すぎると問題検出の遅延になるため、適切にテストした上で設定することを推奨します。
- 推奨値:
  - OLTPシステム: 100ms〜500ms
  - バッチ処理システム: 1000ms〜3000ms
  - 分析システム: 3000ms〜5000ms
ロック待機の定期監視
- pg_stat_activityビューを定期的に確認
- 長時間のロック待機をアラート化するようにしましょう。
- ブロッキングクエリの自動終了スクリプトの準備
アプリケーション側の対策
- トランザクションは可能な限り短く保つ
- テーブルアクセスの順序を統一（ABC順など）
- SELECT FOR UPDATE NOWAIT/SKIP LOCKEDオプションの活用
- バッチ処理では適切なサイズに分割
- ロックエスカレーションを避けるため、インデックスの最適化
- リトライ機構の実装（デッドロック発生時の自動再試行）
ログ管理
- ロック待機ログの長期保存（最低30日間）
- 定期的なパターン分析（週次レビュー）
- 問題の多いクエリの最適化
- Cloud Monitoringでのダッシュボード作成
- パフォーマンスオーバーヘッドの監視（ログによるI/O負荷）
ロック待機の分析手法
- 時間帯別のロック待機発生傾向の把握
- 特定のテーブルやクエリパターンの特定
- ユーザーやアプリケーション別のロック競合分析
- ロック待機時間の分布とトレンド分析