Shared Buffer의 동작 원리에 이어서, Shared Buffer 내 동작 과정에서 발생할 수 있는 대표적인 Wait Event인 LWLock:BufferMapping과 LWLock:BufferContent에 대해서 알아보겠습니다.
LWLock:BufferMapping
프로세스가 Hash Table에 대한 BufferMapping Lock 획득을 대기할 때 LWLock:BufferMapping이라는 Wait Event가 발생됩니다.
동일한 Hash Table 영역에 여러 프로세스가 접근하거나 Buffer Pool에 새롭게 저장해야 하는 Data Page가 많아지면 발생 가능성이 높아집니다.
📢 BufferMapping Lock
BufferMapping Lock은 'Hash Table - Buffer Descriptor - Buffer Pool'의 대응 관계를 보호하기 위해 사용되는 LWLock 유형의 Lock입니다. BufferMapping Lock을 획득한 하나의 프로세스만 Hash Table에 접근할 수 있으며, 동시에 다른 프로세스의 접근은 제한됩니다.
- Hash Table 탐색할 때 반드시 BufferMapping Lock을 Shared Mode로 획득해야 합니다.
- 새로운 Data Page를 Buffer Pool에 저장하기 위해선, 반드시 BufferMapping Lock을 Exclusive Mode로 획득한 후 Data Entry를 생성해야 합니다.
주요 발생 원인
- 쿼리가 수행될 때 Sequential Scan을 거치거나 한 번에 많은 양의 데이터를 수정하면, 처리해야 할 데이터가 많아져 여러 버퍼 페이지에 대하여 Access가 발생합니다. 이 과정에서 BufferMapping Lock이 빈번하게 요청되며, 프로세스 간 경합 가능성이 높아집니다.
- 실제 Workload 대비 Shared Buffer가 크기가 작은 경우, 프로세스가 데이터를 Buffer Pool에서 찾지 못할 가능성이 높습니다. 그만큼 Buffer Pool에 Data Page를 새롭게 적재해야 하는 횟수가 많아지면서 BufferMapping Lock 경합이 늘어날 수 있습니다.
해결 방안
- Sequential Scan이 자주 발생하는 경우, 적절한 인덱스를 사용하도록 하여 Scan 범위를 줄일 수 있습니다. 필요하다면 자주 조회 되는 컬럼이나 조건 절에 사용되는 컬럼에 인덱스를 생성합니다.
- 대량 데이터를 처리할 때는 다음과 같은 방법으로 프로세스 간 경합을 분산합니다.
- 대량의 Update/Delete 작업은 Batch를 사용하여 한 번에 처리하는 대신 여러 작은 단위로 나누어 실행하면 각 쿼리의 처리 범위가 줄어들어 경합을 줄일 수 있습니다.
- 데이터 분할(Partitioning)을 통해 테이블을 작은 단위로 나눠서 관리하면 각 파티션은 서로 다른 Data Page에 저장되므로, 여러 프로세스가 동시에 쿼리를 실행하더라도 서로 다른 Data Page에 Access 할 가능성이 높습니다.
- 동시 연결 가능한 Backend Process 수를 제한하여 동시에 실행되는 쿼리 수를 제어합니다.
- 더 많은 Data Page를 메모리에 저장할 수 있도록 shared_buffers 파라미터를 통해 Shared Buffer 크기를 조정하는 것도 하나의 방법입니다.
이 값이 너무 작은 값이면, 디스크에서 Data Page를 자주 가져와서 저장해야 하므로 대기 시간이 길어집니다. 반면 너무 큰 값인 경우에는 관리해야 할 Hash Table과 Buffer Pool 영역이 늘어나는 만큼 각 영역에 연계된 Lock 경합 가능성이 함께 증가합니다.
LWLock:BufferContent
프로세스가 Buffer Pool에 대한 BufferContent Lock 획득을 대기할 때 Wait Event 중 LWLock:BufferContent가 발생할 수 있습니다.
동일한 Data Page에 대한 작업을 요청한 프로세스가 많을 수록 해당 Wait Event 발생 가능성이 높습니다.
📢 BufferContent Lock
BufferContent Lock은 Buffer Pool 내부의 Data Page를 보호하기 위해 설정하는 LWLock 유형의 Lock입니다. Lock이 설정되는 위치는 Buffer Descriptor이지만, Buffer Pool과 Buffer Descriptor는 1:1 대응 관계로서 Data Page를 보호합니다.
BufferContent Lock은 Data Page에 대한 작업의 종류에 따라 다음과 같이 Lock Mode가 결정됩니다.
- Read 작업 시에는 Shared Mode로 획득합니다.
- Write 작업을 할 때에는 반드시 BufferContent Lock을 Exclusive Mode로 획득해야 합니다.
주요 발생 원인
BufferContent Lock은 Buffer Pool 내부의 Data Page, 즉 Bufffer의 내용(Content)에 대한 작업을 제어하기 때문에 작업의 종류 및 대상이 되는 데이터와 더 밀접하게 연관되어 있습니다. 앞서 설명한 LWLock:BufferMapping의 주요 발생 원인과 유사하지만, BufferContent Lock은 주로 버퍼 내 데이터 수정 및 읽기에 집중되는 경합입니다. 따라서 데이터 처리 중 발생하는 빈번한 Read와 Write 작업에서 주로 나타납니다.
- 특히 Write 작업(Insert/Update/Delete Command)은 다른 프로세스의 작업을 제한하기 위해서 BufferContent Lock을 Exclusive Mode로 획득해야 합니다. Write 작업 시 Lock 경합 발생 확률이 Read 작업보다 훨씬 높습니다.
- 테이블 Update와 마찬가지로 인덱스 Update 역시, Exclusive Mode로 BufferContent Lock을 필요로 합니다. 따라서 테이블에 생성된 인덱스가 많을수록 이에 수반되는 인덱스 작업으로 인한 LWLock:BufferContent 발생 가능성이 더욱 높아집니다.
- 외래키(Foreign Key) 제약 조건을 사용하는 경우도 LWLock:BufferContent 발생의 원인이 될 수 있습니다. 외래키가 설정된 테이블에서 Write 작업을 수행하면, 상위 테이블을 Read 하는 과정에서 Shared Mode의 BufferContent Lock을 획득해야 합니다. 이에 따라, 외래키 제약 조건이 없는 테이블보다 Lock 경합이 더 자주 발생할 수 있습니다.
📢 외래키는 두 테이블 간의 데이터 간 링크를 설정하고자 할 때 사용되는 하나 이상의 컬럼입니다. 외래키가 포함된 테이블을 하위 테이블(Referencing Table)이라고 하며, 외래키가 참조하는 테이블을 상위 테이블(Referenced Table)이라고 합니다.
해결 방안
앞서 설명한 LWLock:BufferMapping을 줄이기 위한 해결 방안에 더하여, LWLock:BufferContent 발생을 최소화하기 위한 추가적인 방안을 다음과 같습니다.
- 인덱스가 많은 테이블은 불필요한 인덱스 및 중복 인덱스는 정리하여 인덱스 효율성을 개선합니다.
- 테이블 Update 수행 시 동반되는 인덱스 작업을 줄일 수 있습니다.
- 인덱스 검색 효율성을 높여서 Lock 경합 가능성을 낮춥니다.
- 외래키 제약 조건이 있는 테이블이 있을 땐, 제약 조건을 최소화하는 것이 Lock 경합을 줄이는데 도움이 될 수 있습니다.
- 자주 변경되는 테이블에는 외래키 제약 조건을 사용하지 않도록 하며, 불필요한 외래키 제약 조건이 있다면 이를 제거합니다.
- 외래키를 사용해야 하는 경우에는 deferred constraints 방식을 사용하여 외래키 체크 시점을 변경하는 방법이 있습니다.
이제 테스트를 통해 Wait Event 발생을 직접 확인해보고 해결 방법을 적용하여 이를 개선해 보도록 하겠습니다.
[CASE 1] Scan 범위
먼저 살펴볼 CASE는 Scan 범위에 따라 Query Plan이 달라지는 경우입니다.
select c1 , c2 , c3 from tt1 t where c1 = $1;1) Sequential Scan
QUERY PLAN
----------------------------------------------------
Seq Scan on tt1
Filter: (c1 = 5000)
## [pg_lw_lock_tracer] Locks per tranche
Locks per tranche
+--------------------+----------+--------------------------+------------------------+-------------------------------+-----------------------------+-------+----------------+
| Tranche | Acquired | AcquireOrWait (Acquired) | AcquireOrWait (Waited) | ConditionalAcquire (Acquired) | ConditionalAcquire (Failed) | Waits | Wait time (ns) |
+--------------------+----------+--------------------------+------------------------+-------------------------------+-----------------------------+-------+----------------+
| BufferContent | 102 | 0 | 0 | 0 | 0 | 0 | 0 |
| BufferMapping | 102 | 0 | 0 | 0 | 0 | 0 | 0 |
...
+--------------------+----------+--------------------------+------------------------+-------------------------------+-----------------------------+-------+----------------+2) Index Scan
QUERY PLAN
-----------------------------------------------------------------------
Index Only Scan using tt1_idx on tt1
Index Cond: (c1 = 5000)
## [pg_lw_lock_tracer] Locks per tranche
Locks per tranche
+--------------------+----------+--------------------------+------------------------+-------------------------------+-----------------------------+-------+----------------+
| Tranche | Acquired | AcquireOrWait (Acquired) | AcquireOrWait (Waited) | ConditionalAcquire (Acquired) | ConditionalAcquire (Failed) | Waits | Wait time (ns) |
+--------------------+----------+--------------------------+------------------------+-------------------------------+-----------------------------+-------+----------------+
| BufferContent | 28 | 0 | 0 | 0 | 0 | 0 | 0 |
| BufferMapping | 29 | 0 | 0 | 0 | 0 | 0 | 0 |
...
+--------------------+----------+--------------------------+------------------------+-------------------------------+-----------------------------+-------+----------------+
- Sequential Scan으로 실행한 경우(
Seq Scan on tt1), Index Scan보다BufferContent와BufferMapping모두 호출 건수가 더 많음을 확인할 수 있습니다. - Sequential Scan의 경우, Hash Table을 자주 탐색하고 더 많은 Data Page에 Access 한다는 것을 나타냅니다. 그로 인해 BufferContent Lock과 BufferMapping Lock을 획득해야 하는 상황도 늘어나기 때문에 관련 함수의 호출 횟수가 증가한다는 것을 알 수 있습니다.
- Index Scan으로 실행 계획이 변경된 경우(
Index Only Scan using tt1_idx on tt1),BufferContent와BufferMapping호출 횟수가 줄어든 것을 확인할 수 있습니다. 이는 곧 쿼리 수행 시 Scan 범위는 Lock 획득 빈도에 영향을 줄 수 있으며, LWLock:BufferMapping, LWLock:BufferContent와 같은 Wait Event 발생과도 연관된다는 것을 의미합니다.
[CASE 2] Shared Buffer 크기 조정
이번에는 Shared Buffer 크기에 따른 Wait Event 발생을 살펴보겠습니다.
다양한 값으로 Shared Buffer 크기를 설정하면서, 크기 별로 LWLock:BufferMapping의 발생 빈도를 확인합니다.
(Shared Buffer 크기는 shared_buffers 파라미터를 통해 설정할 수 있으며, 이 값이 클수록 더 많은 데이터를 메모리 영역에 저장할 수 있습니다.)
select id, user_id, content from tt2 where user_id = $1;세 개의 프로세스에서 해당 쿼리를 동일하게 수행한 후, pg_wait_sampling을 통해 Wait Event 발생을 조회하면 다음과 같습니다.
1) shared_buffers=128MB
pid |event_type|event |queryid |count|query |
------+----------+-------------+--------------------+-----+-------------------------------------------------------+
407611|LWLock |BufferMapping|-6117416793995610507| 15|select id, user_id, content from tt2 where user_id = $1|
407639|LWLock |BufferMapping|-6117416793995610507| 27|select id, user_id, content from tt2 where user_id = $1|
407668|LWLock |BufferMapping|-6117416793995610507| 19|select id, user_id, content from tt2 where user_id = $1|2) shared_buffers=1GB
pid |event_type|event |queryid |count|query |
------+----------+-------------+--------------------+-----+-------------------------------------------------------+
409602|LWLock |BufferMapping|-6117416793995610507| 12|select id, user_id, content from tt2 where user_id = $1|
409609|LWLock |BufferMapping|-6117416793995610507| 18|select id, user_id, content from tt2 where user_id = $1|
409619|LWLock |BufferMapping|-6117416793995610507| 21|select id, user_id, content from tt2 where user_id = $1|3) shared_buffers=4GB
pid |event_type|event |queryid |count|query |
------+----------+-------------+--------------------+-----+-------------------------------------------------------+
412475|LWLock |BufferMapping|-6117416793995610507| 1|select id, user_id, content from tt2 where user_id = $1|
412490|LWLock |BufferMapping|-6117416793995610507| 9|select id, user_id, content from tt2 where user_id = $1|
- Shared Buffer 크기가 클수록
LWLock:BufferMapping에 대한 count 값이 점차 줄어든 것을 볼 수 있습니다. - Shared Buffer를 큰 값으로 설정했다는 것은 메모리 영역에 많은 데이터를 적재하고 있을 수 있다는 것을 의미합니다. 프로세스가 원하는 데이터를 메모리 영역에서 바로 찾을 수 있는 확률이 높아, 디스크로부터 데이터를 가져와서 Shared Buffer에 적재해야 하는 상황이 줄어듭니다.
- BufferMapping Lock 획득 빈도 자체가 줄어들기 때문에
LWLock:BufferMapping발생 가능성도 낮아집니다.
[CASE 3] 중복 인덱스
다음으로 인덱스가 많은 테이블에서 Update Command를 수행했을 때 LWLock:BufferContent 발생을 확인하고, 중복 인덱스를 제거 후 동일한 쿼리를 수행했을 때 어떤 결과를 나타나는지 확인해 보겠습니다.
update tt3 set c1 = c1*2 where mod(c1,2) = 0;1) 중복 인덱스 제거 전
select * from pg_catalog.pg_indexes where tablename = 'tt3';
schemaname|tablename|indexname|tablespace|indexdef |
----------+---------+---------+----------+------------------------------------------------------------+
public |tt3 |tt3_idx1 | |CREATE INDEX tt3_idx1 ON public.tt2 USING btree (c1) |
public |tt3 |tt3_idx2 | |CREATE INDEX tt3_idx2 ON public.tt2 USING btree (c1, c3) |
public |tt3 |tt3_idx3 | |CREATE INDEX tt3_idx3 ON public.tt2 USING btree (c1, c2, c3)|
## [pg_lw_lock_tracer] Locks per tranche
+--------------------+----------+--------------------------+------------------------+-------------------------------+-----------------------------+-------+----------------+
| Tranche | Acquired | AcquireOrWait (Acquired) | AcquireOrWait (Waited) | ConditionalAcquire (Acquired) | ConditionalAcquire (Failed) | Waits | Wait time (ns) |
+--------------------+----------+--------------------------+------------------------+-------------------------------+-----------------------------+-------+----------------+
| BufferContent | 62 | 0 | 0 | 0 | 0 | 0 | 0 |
| BufferMapping | 51 | 0 | 0 | 0 | 0 | 0 | 0 |
...
+--------------------+----------+--------------------------+------------------------+-------------------------------+-----------------------------+-------+----------------+2) 중복 인덱스 제거 후
select * from pg_catalog.pg_indexes where tablename = 'tt3';
schemaname|tablename|indexname|tablespace|indexdef |
----------+---------+---------+----------+------------------------------------------------------------+
public |tt3 |tt3_idx3 | |CREATE INDEX tt3_idx3 ON public.tt2 USING btree (c1, c2, c3)|
## [pg_lw_lock_tracer] Locks per tranche
+--------------------+----------+--------------------------+------------------------+-------------------------------+-----------------------------+-------+----------------+
| Tranche | Acquired | AcquireOrWait (Acquired) | AcquireOrWait (Waited) | ConditionalAcquire (Acquired) | ConditionalAcquire (Failed) | Waits | Wait time (ns) |
+--------------------+----------+--------------------------+------------------------+-------------------------------+-----------------------------+-------+----------------+
| BufferContent | 17 | 0 | 0 | 0 | 0 | 0 | 0 |
| BufferMapping | 14 | 0 | 0 | 0 | 0 | 0 | 0 |
...
+--------------------+----------+--------------------------+------------------------+-------------------------------+-----------------------------+-------+----------------+
- 중복 인덱스를 제거한 후
BufferContent와BufferMapping모두 적게 호출되었습니다. 이는 중복 인덱스 제거로 인해 Row Update 시 수반되는 인덱스 Update 작업이 줄어들었기 때문입니다. - 이번 테스트와 “[CASE 1] Scan 범위”를 통해 Wait Event 발생을 완화하기 위해서 효율적인 인덱스 설계가 중요하다는 것을 알 수 있습니다.
- LWLock:BufferContent와 LWLock:BufferMapping 두 가지의 Wait Event 발생을 모두 줄이기 위해서는 최적화된 구성으로 인덱스를 생성해야 하며, 테이블에 생성된 인덱스의 개수도 함께 고려해야 합니다.
[CASE 4] 외래키 제약 조건
마지막으로 외래키 제약 조건의 유무에 따라 Update Command 수행 후 발생한 Wait Event 통계에 어떤 차이가 있는지 확인해 보겠습니다.
update tt4_fk set c1 = 1 where mod(c1,9) = 0 ;1) 외래키 제약 조건 설정
SELECT tc.table_name AS child_table,
kcu.column_name AS child_column,
ccu.table_name AS foreign_table,
ccu.column_name AS foreign_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name::text = kcu.constraint_name::text
JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name::text = tc.constraint_name::text
WHERE tc.constraint_type = 'FOREIGN KEY' and tc.table_name = 'tt4_fk';
child_table|child_column|foreign_table|foreign_column|
-----------+------------+-------------+--------------+
tt4_fk |c1 |tt4 |c1 |
## [pg_lw_lock_tracer] Locks per tranche
+--------------------+----------+--------------------------+------------------------+-------------------------------+-----------------------------+-------+----------------+
| Tranche | Acquired | AcquireOrWait (Acquired) | AcquireOrWait (Waited) | ConditionalAcquire (Acquired) | ConditionalAcquire (Failed) | Waits | Wait time (ns) |
+--------------------+----------+--------------------------+------------------------+-------------------------------+-----------------------------+-------+----------------+
| BufferContent | 249 | 0 | 0 | 1 | 0 | 0 | 0 |
| BufferMapping | 226 | 0 | 0 | 0 | 0 | 0 | 0 |
...
+--------------------+----------+--------------------------+------------------------+-------------------------------+-----------------------------+-------+----------------+
## [pg_lw_lock_tracer] Locks per type
+--------------+----------+
| Lock type | Requests |
+--------------+----------+
| LW_EXCLUSIVE | 244 |
| LW_SHARED | 422 |
+--------------+----------+2) 외래키 제약 조건 삭제
SELECT tc.table_name AS child_table,
kcu.column_name AS child_column,
ccu.table_name AS foreign_table,
ccu.column_name AS foreign_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name::text = kcu.constraint_name::text
JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name::text = tc.constraint_name::text
WHERE tc.constraint_type = 'FOREIGN KEY' and tc.table_name = 'tt4_fk';
child_table|child_column|foreign_table|foreign_column|
-----------+------------+-------------+--------------+
(0 rows)
## [pg_lw_lock_tracer] Locks per tranche
+--------------------+----------+--------------------------+------------------------+-------------------------------+-----------------------------+-------+----------------+
| Tranche | Acquired | AcquireOrWait (Acquired) | AcquireOrWait (Waited) | ConditionalAcquire (Acquired) | ConditionalAcquire (Failed) | Waits | Wait time (ns) |
+--------------------+----------+--------------------------+------------------------+-------------------------------+-----------------------------+-------+----------------+
| BufferContent | 151 | 0 | 0 | 1 | 0 | 0 | 0 |
| BufferMapping | 127 | 0 | 0 | 0 | 0 | 0 | 0 |
...
+--------------------+----------+--------------------------+------------------------+-------------------------------+-----------------------------+-------+----------------+
## [pg_lw_lock_tracer] Locks per type
+--------------+----------+
| Lock type | Requests |
+--------------+----------+
| LW_EXCLUSIVE | 242 |
| LW_SHARED | 227 |
+--------------+----------+📢외래키 제약 조건이 존재할 때 Update가 수행된 테이블의 구성을 살펴보면, 테이블 tt4_fk의 c1 컬럼은 각각 tt4 테이블의 c1 컬럼을 참조하고 있습니다. 그리고 외래키 제약 조건을 삭제한 후에는 어떠한 외래키도 조회되지 않았음을 확인할 수 있습니다.
- 외래키 제약 조건을 삭제한 후,
BufferContent와BufferMapping호출 횟수가 감소한 것을 볼 수 있습니다. - Shared Mode로 요청되는 건수에 차이가 있는데, 이는 외래키 제약 조건이 설정된 하위 테이블에서 Write 작업을 하는 경우에는 상위 테이블에 대한 Read 작업이 추가되기 때문입니다. (Lock type=
LW_SHARED의 Requests 참고) - Lock 요청 건 수의 증가하면 Lock 경합 발생 가능성을 높아지는 것을 의미하며, 즉 관련된 Wait Event 발생할 확률이 증가합니다.
이상으로 Shared Buffer 내부 동작 과정에서 발생할 수 있는 대표적인 Wait Event인 LWLock 유형의 BufferMappping과 BufferContent에 대한 설명을 모두 마치겠습니다.
마무리
- BufferMappingLock은 Hash Table - Buffer Descriptor - Buffer Pool의 대응 관계를 보호하기 위해 사용되는 LWLock 유형의 Lock이다. (Hash Table 탐색 : Shared Mode, Data Entry 생성/제거 : Exclusive Mode)
- LWLock:BufferMapping은 프로세스가 Hash Table에 대한 BufferMapping Lock 획득을 대기할 때 발생되며, 동일한 Hash Table 영역에 여러 프로세스가 접근하거나 Buffer Pool에 새롭게 저장해야 하는 Data Page가 많아지면 증가한다.
LWLock:BufferMapping 발생을 줄이기 위해서 Shared Buffer 크기가 Workload에 맞게 설정되어 있는지 확인해야 한다. 그리고 쿼리 수행 시 Scan 하는 데이터 범위를 줄이기 위해 인덱스를 생성하거나 쿼리를 재작성하거나, 대량 데이터를 처리할 때는 다양한 방법(ex. Batch, Data Partitioning, Process 수 제한)으로 프로세스 간 경합을 분산한다. - BufferContent Lock은 Buffer Pool 내부의 Data Page를 보호하기 위해 설정하는 LWLock 유형의 Lock이다. (Read 작업 : Shared Mode, Write 작업 : Exclusive Mode)
- LWLock:BufferContent는 프로세스가 Buffer Pool에 대한 BufferContent Lock 획득을 대기할 때 발생되며, 동일한 Data Page에 대한 작업을 요청한 프로세스가 많을수록 발생 가능성이 높다.
불필요한 인덱스 및 중복 인덱스를 정리하고 외래키 제약 조건 사용을 최소화하는 것은 LWLock:BufferContent 발생을 줄이는데 도움이 된다.
'엑셈 경쟁력 > DB 인사이드' 카테고리의 다른 글
| DB 인사이드 | PWI - Shared Buffer > 동작원리 (0) | 2024.10.25 |
|---|---|
| DB 인사이드 | PWI - LOCK > Other Locks (0) | 2024.09.25 |
| DB 인사이드 | PWI - LOCK > Row-level Lock(2) (0) | 2024.08.05 |
| DB 인사이드 | PWI - LOCK > Row-level Lock(1) (0) | 2024.08.05 |
| DB 인사이드 | PWI - LOCK > Relation-level Lock (0) | 2024.06.11 |
댓글