PostgreSQLのテーブルロックを観測する方法

SREの古越です。PostgreSQLでテーブルロックを観測したい事例があったため、調査した事と観測方法をまとめます。重要なシステムのテーブルを変更したいがオンラインで実行して良いか自信が無い、という方は参考にして頂ければ幸いです。

はじめに

オンラインでPostgreSQLのテーブル構成を変更することは良く有る事と思います。また、重要なデータベースのALTER TABLEクエリなど影響が大きいクエリは流す前に影響を把握したいケースがあるかと思います。

例えば

大量レコードが格納されたテーブルにINDEXや外部キー制約を追加する場合
パーティション化したテーブルでパーティション再配置作業を行う場合

などです

PostgreSQLのテーブルロックは幾つか種類が有り、種類によってロックの深刻さも変わってきます。レコード数が少ない場合は強いロックでも短時間で開放されるため問題にならないことが多いです。

しかし数千万~1億レコードを超える規模のINDEX付与やパーティションの修正作業をするような場合は「ちょっとした操作」と思っていたものが長時間のロック獲得になり想定外の障害を引き起こすケースがあります。

事前予測のために、ロック状況が多少なりとも観測出来ると好ましいですよね。

PostgreSQLにおけるロック

ロックそのものはリアルタイムにデータの同時アクセスを制御し、排他制御として特定タイムスタンプで整合性を取るために存在するものです。先にロックを取得したクエリが完了するまで、競合するロックを取ろうとするクエリをブロックする(待たせる)ことで実現されてます。

PostgreSQLにおいては大きく分けて3種類のレベルのロックがあります。

テーブルレベルロック
行レベルロック
ページレベルロック

PostgreSQLユーザが考えるべきロックはテーブルレベルロックと行レベルロックの2つです。

ページレベルロックは公式ドキュメントに一応触れられているものの、PostgreSQL内部で迅速に処理されており、ユーザ側では意識する必要ありません。

ここから触れる部分は公式ドキュメント 13.3 Explict Locking にも詳細が書かれているため、細かくは公式ドキュメントを確認してください。

テーブルレベルロック

1テーブルまるごとロックをかけるもので、ロックモードとして8種類ほど用意されています。

ユーザ自身がロックをかける事も可能ですが、多くはSQL文次第でPostgreSQL内部で自動的に獲得/解除される動きをするため、想定外のロック競合が発生してトラブルに発展するケースがあります。

特徴

テーブルレベルロックはほぼすべてのクエリが獲得する
オンメモリで処理される
競合が少ない(影響が小さい)ものから、競合が多い(影響が大きい)ものまで8種類ある
ロックの状態を観測するために pg_locks というビューが用意されている

競合するロックが先に有った場合にはロック元のトランザクションが終わるまでクエリの実行がブロックされ続けます。SELECT, INSERT, UPDATE, DELETEなどのCRUD系クエリ実行時にも弱いロックが獲得されるため、ロックモード次第ではCRUDが競合して止まる事になります。

各ロックモードの詳細は公式ドキュメントに記載があるため割愛しますが、ロックモードの競合はこちらの表にわかりやすくまとまっています。

出典: PostgreSQL Docs/13.3 Explict Locking

表の SHARE 以下がCRUDをブロックする動きになるため注意が必要です

要注意のロックモード

競合を含めて改めて整理すると、注意が必要なロックモードは以下の5通りです。

ACCESS EXCLUSIVE … 競合範囲が広く、獲得されたテーブルは開放されるまで全ロックをブロックする最強のロック。SELECTクエリをブロックするのはこのロックだけ。ALTER TABLEの殆どはこのロックを獲得するため、ロック継続時間に注意が必要。
EXCLUSIVE … 獲得された場合、一部のSELECTクエリ以外がブロックされる。注意が必要に見えるが、マテリアライズドビューの置換クエリで発生するロックモードのため、マテリアライズドビューを利用してない場合は気にする必要が無い。
SHARE ROW EXCLUSIVE … SELECT以外のほぼすべてのクエリをブロックする。INSERT, UPDATE, DELETEができないためWrite Heavyなテーブルでは注意が必要。ただし発生頻度は高くない。
SHARE … CONCURRENTLYオプションのないCREATE INDEXで獲得され、SELECT以外はほぼすべてをブロックする。例えばWrite Heavyでデータが蓄積した巨大なテーブルに対してCREATE INDEXを叩いた場合、長時間このロックが発生してWriteをブロックしてしまう※。

※解説は割愛しますが、特別な理由無い限りCONCURRENTLYオプションを使うほうが無難です

SHARE UPDATE EXCLUSIVE … CRUDをブロックしないため殆どのケースで問題にならない。テーブルの定義変更をブロックするため、デプロイタイミングには注意。巨大なテーブルになるとVACCUM / ANALYZEなどに時間がかかり、このロックが長時間獲得され続けるケースがある。

その中から発生頻度 x ロックの強さという観点で見ると、特に注意すべきは ACCESS EXCLUSIVE, SHARE の2つになります。

行レベルロック

行レベルロックは行単位の粒度の細かいロックです。同じ行に対する書き込みとロックのみブロックします。SELECT、INSERTクエリはブロックしないためPostgreSQLでは問題になりにくいロックです。行レベルロックはテーブルレベルロックの ROW SHARE を用いながらDisk上で処理されます。

行レベルロックはpg_locksビューでは観測することができず、pgrowlocks extentionを利用することで観測出来るようになります。

行レベルロックの観測方法については本記事では触れません。

テーブルロックを観測する方法

クエリの影響を判断する場合、実際の状況を模した検証環境を作った後で

ACCESS EXCLUSIVE, SHARE が長時間発生しないこと
競合するロックの解除待ちクエリが少ない事

が確認出来れば安全と判断して良いかと思います。

pg_locksビューやpsql、shellを使って観測すれば安全確認が出来るので紹介します。

検証環境作成

参考までに手元で検証する方法を含めて紹介します。

この例では3つのターミナルを使って検証します。

terminal1

環境起動と強いロックをかけるクエリを流します

1-1. dockerでpostgresコンテナを起動

docker run --rm --name test-postgres \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=main \
  -d -p 5432:5432 postgres

# 環境変数に接続情報を記載
export PGPORT=5432
export PGHOST=localhost
export PGUSER=postgres
export PGPASSWORD=postgres
export PGDATABASE=main

# localhost postgresにログイン
psql

1-2. 検証テーブル作成

-- 検証用テーブル作成
CREATE TABLE messages (
  id serial NOT NULL,
  message text
);

-- ダミーデータ挿入 (任意)
INSERT INTO messages (message)
  SELECT md5(clock_timestamp()::text) FROM generate_series(1,5);

1-3. ロックをかける検証クエリを実行

BEGIN;
ALTER TABLE messages ADD COLUMN created_at timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL;
-- ロック状態を確認するためEND; は実行せず、そのままま置いておく

terminal2

ロック待ちを再現する検証クエリを実行します

# 環境変数に接続情報を記載
export PGPORT=5432
export PGHOST=localhost
export PGUSER=postgres
export PGPASSWORD=postgres
export PGDATABASE=main

# localhost postgresにログイン
psql

-- ロック待ちになるSELECTクエリを投入する
SELECT * FROM messages;

terminal3

観測SQLを実行します

# 環境変数に接続情報を記載
export PGPORT=5432
...(省略)

# localhost postgresにログイン
psql

terminal3で後述のSQLを実行して観測してください

観測SQLについて

pg_locksビューを見ることでロックの状態を観測できます。

ただ、pg_locksのそのままでは情報の過不足が有るため、以下のようにpg_class等とjoinしたクエリを叩くと見やすくなります。

SELECT
  pn.nspname,
  pc.relname,
  lc.locktype,
  lc.pid,
  lc.granted,
  lc.mode
FROM
  pg_locks lc
  INNER JOIN pg_class pc
    ON pc.oid = lc.relation
  INNER JOIN pg_namespace pn
    ON pc.relnamespace = pn.oid
  INNER JOIN pg_database pd
    ON pd.oid = lc.database
WHERE relname NOT LIKE 'pg_%' -- 見やすくするためpostgresqlシステム系ビューを除外
ORDER BY lc.mode;

このクエリをterminal3で実行すると、以下のように出力されます。

クエリ実行結果

 nspname | relname  | locktype | pid | granted |        mode
---------+----------+----------+-----+---------+---------------------
 public  | messages | relation | 347 | t       | AccessExclusiveLock
 public  | messages | relation | 460 | f       | AccessShareLock
(2 rows)

ALTER TABLEの実行中のため、AccessExclusiveLockがかかり
SELECTクエリがAccessShareLockロックを獲得しようとしますが、解除待ち状態(granted=f)

ということがわかりますね。

この状態で新しいターミナルを開き、SELECTやINSERTクエリを投げてみると具体的な挙動が確認出来るためお試しください。

クエリ抽出と観測

本番環境で同様のクエリを投げると AccessShareLock 等の高頻度で発生するロックが大量に表示されることになるため、危険なロックを観測するのが難しいことがあります。

↓のような危険なロックのみ抽出するクエリも有用です。

SELECT
  pn.nspname,
  pc.relname,
  lc.locktype,
  lc.pid,
  lc.granted,
  lc.mode
FROM
  pg_locks lc
  INNER JOIN pg_class pc
    ON pc.oid = lc.relation
  INNER JOIN pg_namespace pn
    ON pc.relnamespace = pn.oid
  INNER JOIN pg_database pd
    ON pd.oid = lc.database
WHERE relname NOT LIKE 'pg_%'
　　　　-- 影響の小さいロックを除外
  AND lc.mode != 'AccessShareLock'
  AND lc.mode != 'RowShareLock'
  AND lc.mode != 'RowExclusiveLock'
  AND lc.mode != 'ShareUpdateExclusiveLock'
ORDER BY lc.mode;

さらに、このSQLをファイルに保存してwatchコマンドで繰り返し叩くようにすれば、ロックの発生状況を容易に観測することができます。

vi pg_locks.sql # 上記のSQLを書く
watch -n 1 psql -f pg_locks.sql

jsonl形式でログ保存する

手元でコントロール出来るクエリを観測したい場合は上記のwatchコマンドを組み合わせた方法で十分かと思いますが、本番DBと同じデータ量でどの程度ロックが継続するか、エビデンスを取りつつ観測したいケースが有るかと思います。

自分が経験した事例としては、恒久的ではなく一時的な記録を取りたい場面が幾つか有りました。

パーティションの再配置を行う場合の事前検証
パーティションなどの特定の制約があり CONCURRENTLY オプション無しでINDEX付与を行う場面の事前検証

といったケースです。

このような場合、shellとpsqlを使ってjsonl形式でクエリ結果を出力するスクリプトを作るだけで簡単にログを取ることができます。

#!/bin/bash

INTERVAL=0.5
SQL_OBSERVING_LOCKS=$(cat <<EOS
WITH locks AS (
  SELECT
    pd.datname,
    pn.nspname,
    pc.relname,
    lc.locktype,
    lc.pid,
    lc.granted,
    lc.mode
  FROM
    pg_locks lc
    INNER JOIN pg_class pc
      ON pc.oid = lc.relation
    INNER JOIN pg_namespace pn
      ON pc.relnamespace = pn.oid
    INNER JOIN pg_database pd
      ON pd.oid = lc.database
  WHERE relname NOT LIKE 'pg_%'
)
SELECT json_build_object('timestamp',now(),'locks',array_to_json(array_agg(row_to_json(locks)))) FROM locks;
EOS
)

while true;
do
    psql -t -A -c "${SQL_OBSERVING_LOCKS}"
    sleep ${INTERVAL}
done

↑のshellscriptをこのように実行すると、

vi pg_locks_to_json.sh # 上記のスクリプトを作成
chmod a+x pg_locks_to_json.sh

# 環境変数に接続情報を記載
export PGPORT=5432
export PGHOST=localhost
export PGUSER=postgres
export PGPASSWORD=postgres
export PGDATABASE=main
./pg_locks_to_json.sh > pg_locks.jsonl

JSON Lines(jsonl)形式で継続的に出力できます

{"timestamp" : "2023-11-28T01:25:31.894288+00:00", "locks" : [{"datname":"main","nspname":"public","relname":"messages","locktype":"relation","pid":347,"granted":true,"mode":"AccessExclusiveLock"},{"datname":"main","nspname":"public","relname":"messages","locktype":"relation","pid":460,"granted":false,"mode":"AccessShareLock"}]}
{"timestamp" : "2023-11-28T01:25:32.483557+00:00", "locks" : [{"datname":"main","nspname":"public","relname":"messages","locktype":"relation","pid":347,"granted":true,"mode":"AccessExclusiveLock"},{"datname":"main","nspname":"public","relname":"messages","locktype":"relation","pid":460,"granted":false,"mode":"AccessShareLock"}]}
{"timestamp" : "2023-11-28T01:25:33.058651+00:00", "locks" : [{"datname":"main","nspname":"public","relname":"messages","locktype":"relation","pid":347,"granted":true,"mode":"AccessExclusiveLock"},{"datname":"main","nspname":"public","relname":"messages","locktype":"relation","pid":460,"granted":false,"mode":"AccessShareLock"}]}

このスクリプトを検証環境に仕込んでおいて、効果測定したいクエリや任意のジョブを実行すればロック状況の詳細なログを取ることが可能になります。

jsonlログを解析する

上記したjsonlログはそのままだと解析し辛いため、1行のjsonを抽出した上でjqコマンドを使うと元々のviewに近い形に復元できます。

1. 解析したいタイミングを抽出する

jsonlとは言えシンプルなテキストと変わり有りませんので、grepやawkを使って1行を抽出できます。

抽出例1 . 強いlockが発生するタイミングを抜き出す

grep 'AccessExclusiveLock' pg_locks.jsonl -m1 > lock.json

抽出例2. lockが最も滞留しているタイミングを抜き出す

cat pg_locks.jsonl | awk '{print length() ,$0}' | sort -nr | awk '{sub(/^[^ ]+ /,"");print $0}' | head -n 1 > lock.json

2. jsonをテーブル表示して解析する

抽出したjsonをjqを使って元の形式に近い形に復元していきます。

tsv表示

tsv表示にするのは比較的簡単にできます。

以下のようにクエリすると、

JSON_FILE=lock.json
jq -r '.locks[] | [.datname, .nspname, .relname, .locktype, .pid, .granted, .mode] | @tsv' $JSON_FILE | column -t -s$'\t'

このように出力されます。

main  public  messages  relation  347  true   AccessExclusiveLock
main  public  messages  relation  460  false  AccessShareLock

ただ、このままだと列名や区切りが分かりにくいですね。

人に見せる場合は別の手を使った方が良さそうです。

markdown表記で出力

tsvだと見ずらいため、markdownで表記する方法を紹介します。

jqコマンド内に簡単に展開する方法があったら良かったのですが、用意されてないため

少し手間ですが echoコマンドを使ってmarkdownぽく表示します。

JSON_FILE=lock.json
echo "| datname | nspname | relname | locktype | pid | granted | mode |" && \
echo "|---------|---------|---------|----------|-----|---------|------|" && \
jq -r '.locks[] | "| \(.datname) | \(.nspname) | \(.relname) | \(.locktype) | \(.pid) | \(.granted) | \(.mode) |"' $JSON_FILE

これを実行すると、こちらのように表示されます。

| datname | nspname | relname | locktype | pid | granted | mode |
|---------|---------|---------|----------|-----|---------|------|
| main | public | messages | relation | 347 | true | AccessExclusiveLock |
| main | public | messages | relation | 460 | false | AccessShareLock |

コピー & ペーストでレポート化しやすくなるためこちらの方法をおすすめします。

全ロックを記録した後、強いlockをselectして抜き出す

上述していた例だとロック数が少ないため問題は無いのですが、実際に本番環境などでデータを観測する場合はかなりのロックが表示されることになります。AccessShareLockを除外せずにログ記録している場合、上述のようにテーブル表記しても100行以上表示されて解析が難しくなってきます。

そういった場合、こちらのように、jqコマンドの中で更にselectすると良いかと思います。

JSON_FILE=lock.json
jq -r '.locks[] | select(.mode != "AccessShareLock" and .mode != "RowShareLock" and .mode != "RowExclusiveLock" and .mode != "ShareUpdateExclusiveLock" ) | [.datname, .nspname, .relname, .locktype, .pid, .granted, .mode] | @tsv' $JSON_FILE | column -t -s$'\t'

これにより広い条件でロックを記録し、後から強いロックをフィルターする事も出来るようになりました。jsonl形式のログ記録を仕込んでおけば、様々な観点で解析出来るようになると思います。

終わりに

PostgreSQLのロックの振り返り、json形式のログ出力、jqを使った解析について触れていきました。事前検証した後、プロダクション環境に投入する段階においても紹介した内容が活かせるかと思います。

仮に恒久的にメトリクス化したりアラート化したい場合、紹介した方法だとデータ量が肥大化するため、postgres_exporterを使うなど異なるアプローチを検討したほうが良いと思います。

今回dockerで起動した検証環境は以下のコマンドで終了しておきましょう。

docker stop test-postgres

We're Hiring!

📢

GO株式会社ではともに働くエンジニアを募集しています。

興味のある方は採用ページも見ていただけると嬉しいです。

Twitter @goinc_techtalk のフォローもよろしくお願いします！