home..

Postgresql documentation 살펴보기 - Performance Tips

July 2021 (660 Words, 4 Minutes)

Postgresql Documentation에서 Performance Tips을 직접 제공해줘서 살펴보기로 했다. https://www.postgresql.org/docs/current/performance-tips.html 를 참고하였다.

Using EXPLAIN(WIP)

EXPLAIN명령어로 query plan을 알 수 있다. query plan은 트리 구조의 plan node로 구성된다. 트리의 아래쪽에 나타나는 scan node는 table access 방법론 중 하나를 제시한다. seq scans, index scans, bitmap index scans이 있다. 그외 join, aggregate, sort 등이 포함된 node 등도 포함된다.

[예시]

EXPLAIN SELECT * FROM tenk1;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)

위의 예시에서는 WHERE 절이 없기에 모든 row를 한번씩 탐색하므로 Seq Scan이 적용되었다.

Populating Database

처음 database를 생성할 때 대용량 데이터를 삽입하는 상황에 대한 팁을 제시한다. 아래 중 COPY는 실제 사용해봤는데 확실히 다른 INSERT 쿼리에 비해 빠른 감이 있다.

autocommit 비활성화

autocommit 을 비활성하고 마지막에 한번에 commit 실행한다.

BEGIN ...
COMMIT 

autocommit이 활성화되어 있으면 매 row가 삽입될 때마다 commit이 발생하여 오래 걸린다.

`COPY` 사용

모든 rows를 load를 하는 경우 연속해서 INSERT를 실행하기보다 COPY를 실행한다. 단일 명령어이기에 autocommit을 비활성화할 필요가 없다..! COPY를 사용할 수 없다면 대신 PREPARE실행 후 INSERT 를 최대한 많이 사용하는 것이 좋다. COPY는 같은 transaction내에서 사용할 때 가장 빠른 방법이다.

Indexes 제거

방금 막 생성된 테이블에 로드하는 경우에는 COPY로 데이터를 삽입하고 그 다음에 indexes를 생성하는게 가장 빠르다. 기존에 이미 있는 테이블의 경우에는 indexes를 삭제하고 로드한 후 다시 indexes를 생성하는게 효율적이다.

Foreign Key Constraints 제거

Indexes를 제거하는 케이스와 마찬가지.

`maintenance_work_mem` 증가

임시적으로 maintenance_work_mem 설정 변수의 크기를 늘려주는 방식이다. 이는 CREATE INDEX나 ALTER TABLE ADD FOREGITH KEY쿼리 실행에서 속도를 높여준다.

`max_wal_size` 증가

임시적으로 max_wal_size 설정 변수의 크기를 늘려주는 방식이다. 대용량 데이터를 로드할 때 checkpoints(checkpoint_timeout에 의해 설정됨)를 더 자주 발생하게 되므로 이를 줄이기 위해 해당 변수 크기를 늘려준다. checkpoint가 발생할 때마다 dirty pages를 지우는 작업이 실행된다.

이후 `ANALYZE` 실행하기

ANALYZE(VACUUM ANALYZE)를 실행하는 것은 planner가 업데이트된 statistics 정보를 갖도록 한다. 업데이트가 되지 않으면 비효율적인 쿼리 계획을 구성할 수 있다. autovacuum이 활성화되어 있으면 ANALYZE가 자동으로 실행된다.

`pg_dump`

기본적으로 pg_dump는 COPY를 사용하는데 pg_dump를 효율적으로 실행하기 위해

maintenance_work_mem, max_wal_size 적절히 설정
병렬로 load 실행
단일 transaction으로 복구할 것

Statistics Used by the Planner(WIP)

Single-Column Statistics

[ 예시 ] 각 테이블의 전체 entries 갯수, disk blocks 갯수를 파악

SELECT relname, relkind, reltuples, relpages
FROM pg_class
WHERE relname LIKE 'tenk1%';

       relname        | relkind | reltuples | relpages
----------------------+---------+-----------+----------
 tenk1                | r       |     10000 |      358
 tenk1_hundred        | i       |     10000 |       30
 tenk1_thous_tenthous | i       |     10000 |       30
 tenk1_unique1        | i       |     10000 |       30
 tenk1_unique2        | i       |     10000 |       30
(5 rows)

Controlling the Planner with Explicit `JOIN` Clauses(WIP)

JOIN으로 쿼리 계획을 제어하는게 가능하다. 같은 결과여도 실행 시간이 현저히 차이날 수 있다.

SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

쿼리 자체는 논리적으로 동일하나 2, 3 번 쿼리의 경우 1번보다 시간이 덜 소요된다.

SELECT *
FROM x, y,
    (SELECT * FROM a, b, c WHERE something) AS ss
WHERE somethingelse;

서브 쿼리가 포함되는 경우도 전체 시간에 영향을 미친다. 보통 planner는 서브쿼리를 아래와 같이 메인으로 포함시키려 한다.

SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;

이는 서브쿼리로 따로 가져가는 것보다 더 나은 plan으로 동작한다.