Read the signals, decide what to touch first

Goal

The most expensive mistake in a database incident is mistaking the victim for the cause. If you kill the four blocked sessions, they get blocked again, and the one that is actually blocking remains.

Here you read a snapshot shaped like the real thing and decide what to touch first.

Materials

/opt/lab/dbsignals/activity.tsv     pg_stat_activity 스냅숏 30줄
/opt/lab/dbsignals/locks.tsv        누가 누구를 막고 있는가
/opt/lab/dbsignals/statements.tsv   pg_stat_statements 상위 질의

mkdir -p /root/dbsignals && cp /opt/lab/dbsignals/* /root/dbsignals/ && cd /root/dbsignals
column -t -s $'\t' activity.tsv | head -5

What to leave behind

01-states.txt      상태별 개수
02-waits.txt       무엇을 기다리는가
03-root.txt        사슬의 뿌리
04-statements.txt  총 시간과 평균 시간
05-decide.md       지금 할 것과 나중에 할 것
06-prevent.md      다시 안 생기게
07-notes.md        왜 그런지

What is running right now

From activity.tsv, count the sessions by state and write into 01-states.txt how many seconds the oldest idle in transaction session has been open. Also write one line on why that state is dangerous.

awk -F'\t' 'NR>1 {print $3}' activity.tsv | sort | uniq -c

idle is just a connection sitting idle, and is usually not a problem. idle in transaction is different — it holds a transaction open and does nothing, so the locks it holds are never released and cleanup (vacuum) cannot get past that point.

What is it waiting for

Pick only the active sessions, count them by wait_event_type, write the result in 02-waits.txt, and write how the remedies differ for Lock waits and IO waits.

awk -F'\t' 'NR>1 && $3=="active" {print ($4=="" ? "(없음)" : $4)}' activity.tsv | sort | uniq -c

A session with an empty wait type is one that is actually using the CPU and running.

A Lock is released only when another session lets go, so you have to look at that other session. IO means storage is slow or the data is not in the cache, so you have to look at the query or the hardware. It is the same "slow," but where you look is completely different.

Find the root of the chain

Look at locks.tsv and write into 03-root.txt who is blocked and who is blocking. Also write what the root session is doing.

The blocked ones are victims. If you kill them, they get blocked again.

Once you find the root, look in activity.tsv at what state that pid is in and what query it last issued. It connects to what you saw in the earlier step.

Total time and average time are different problems

In statements.tsv, find the query with the largest total time and the query whose sum is large because it is called very many times, write them in 04-statements.txt, and write how the remedies for the two differ.

Say one query averages 3 seconds and another averages 0.2ms. If the latter is called 4.8 million times, the sums become similar.

For the one with the large average, you fix that single query (index, precomputed aggregation). For the one with many calls, you fix not the query but the caller (N+1, cache, batching).

What to do now and what to do later

Write what to do at this very moment in 05-decide.md. Split it into three groups — what to touch now, what to look at soon, and what to fix later — and give a reason for each.

There is one thing to do now. Also decide whether to cancel or terminate that session.

• pg_cancel_backend(pid) — cancels only the query that is currently running. The connection remains
• pg_terminate_backend(pid) — drops the connection. Any open transaction is rolled back with it

An idle in transaction session has no running query, so canceling does not release it.

Keep it from happening again

Write the settings and alerts that would keep the same incident from happening again in 06-prevent.md. Set the values as numbers.

idle_in_transaction_session_timeout prevents this incident outright. Also decide on lock_timeout and statement_timeout.

Saying "turn it on" is not enough — nobody can turn it on from that alone. Give an actual value so it can be carried out.

Settings alone will not let you see the next incident. Also write what you will be watching — something like the age of the oldest transaction.

For the next reader

Pick four or more of the things you saw here and organize them in 07-notes.md. Write not what you did but why it works that way.

Imagine you are the one reading it after being paged in the middle of the night. "I looked at pg_stat_activity" does not help, but "the blocked sessions are victims — kill them and they get blocked again" does.