Postgres 성능 이해하기
이 글은 다음 원문 을 번역한 글 입니다.
많은 응용 프로그램 개발자에게 데이터베이스는 블랙 박스이다. 개발자들은 데이터가 입력되고 다시 조회되는 과정이 짧은 시간에 끝나길 바란다. DBA가 되지 않고도 응용 프로그램 개발자가 데이터베이스가 적절하게 수행되는지 여부를 이해하는 데 도움을 주는 데이터가 있다. 이번 포스트는 데이터베이스의 성능이 앱의 속도를 저하시키는지 여부를 판단 할 수 있는 몇 가지 빠른 팁을 제공한다.
캐시 및 히트 율 이해하기
대부분의 응용 프로그램에 대한 일반적인 규칙은 데이터의 일부만 정기적으로 액세스한다는 것이다. 다른 많은 것들과 마찬가지로, 데이터의 20 %가 80 %의 읽기를 차지하는 80/20 규칙을 따르는 경향이 있으며 종종 그 이상이 되기도 한다. Postgres 자체는 실제로 데이터의 액세스 패턴을 추적하며 자주 액세스하는 데이터를 캐시에 보관할 것이다. 일반적으로 데이터베이스의 캐시 적중률은 약 99 % 이다. 다음을 사용하여 캐시 적중률을 확인할 수 있다.
SELECT
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM
pg_statio_user_tables;이 데이터 클립 에서 Heroku Postgres 의 캐시 속도 가 99.99 % 임을 알 수 있다. 99 %보다 훨씬 낮은 비율의 히트율을 발견하면 데이터베이스에서 사용할 수있는 캐시를 늘리는 것이 좋다. Heroku Postgres에서 빠른 데이터베이스로의 전환 을 수행하거나 EC2 와 같은 환경에서 더 큰 인스턴스로 변경하기 위해 dump/retore 작업을 할 수 있다.
Index 사용법 이해하기
성능 향상을 위한 또 다른 중요한 요소는 인덱스 이다. 몇몇 프레임 워크는 기본 키에 인덱스를 추가한다. 기본키 외의 다른 필드에서 검색하거나 조인을 많이하면 인덱스를 수동으로 추가해야 할 수도 있다.
큰 테이블에서도 인덱스는 유용하다. 캐시에서 데이터에 액세스하는 것이 디스크보다 빠르지만, Postgres가 특정 조건을 충족하는지 확인하기 위해 수십만 개의 행을 구문 분석해야하는 경우 메모리 내의 데이터도 느려질 수 있다. 데이터베이스에서 테이블 목록과 인덱스를 사용하는 시간의 백분율을 생성하려면 다음을 실행해보자.
SELECT
relname,
100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used,
n_live_tup rows_in_table
FROM
pg_stat_user_tables
WHERE
seq_scan + idx_scan > 0
ORDER BY
n_live_tup DESC;완벽한 대답은 없지만 10,000 행을 넘는 테이블에서 약 99 %가 아닌 곳에서 색인을 추가하는 것이 좋다. 색인을 추가 할 위치를 검토 할 때 실행중인 조회의 유형을 찾아야한다. 일반적으로는 다른 id 나 create_at 필드와 같이 필터링하는 값을 사용하여 찾고있는 위치에 인덱스를 추가한다.
첨언 : 패키지에서 사용하는 테이블도 위의 집계에 포함된다.
팁 : 프로덕션 데이터베이스에 인덱스를 추가하는 경우, 인덱스를 백그라운드에서 빌드하고 테이블에 잠금을 설정하지 않기 위해 CREATE INDEX CONCURRENTLY 를 사용하자. 인덱스를 concurrently 옵션으로 생성하는 것은 일반적으로 생성하는 것과 비교해 2–3 배의 시간이 더 걸리고 트랜잭션 내에서 실행할 수 없다는 제약 사항이 있다. 대규모 프로덕션 사이트의 경우 이러한 trade-offs 는 최종 사용자 경험의 trade-offs 에 비해 가치가 있다.
덧글 : concurrently 옵션으로 호출하는 경우, 테이블 스캔이 두번 발생하며 잠재적으로 데이터를 수정하거나 색인을 사용하여 종료할 수 있는 모든 기존 트랜젝션을 기다려야만 한다. 그러나, 인덱스가 작성되는 동안에는 정상적인 동작을 보장하기 때문에 프러덕션 환경에서 새 인덱스를 추가할 때 유용하다. 인덱스 생성으로 인한 추가 CPU 사용 및 disk I/O 로드로 인해 다른 작업이 느려질 수는 있다.
Heroku 대시 보드 예제
최근에 출시 된 Heroku 대시 보드에서 쿼리를 실행하고 결과를 볼 수 있다.
# SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table FROM pg_stat_user_tables ORDER BY n_live_tup DESC;
relname | percent_of_times_index_used | rows_in_table
---------------------+-----------------------------+---------------
events | 0 | 669917
app_infos_user_info | 0 | 198218
app_infos | 50 | 175640
user_info | 3 | 46718
rollouts | 0 | 34078
favorites | 0 | 3059
schema_migrations | 0 | 2
authorizations | 0 | 0
delayed_jobs | 23 | 0위 결과로, 약 700,000 개의 행을 가진 이벤트 테이블에 인덱스가 없다는 것을 알 수 있다. 애플리케이션을 조사하고 사용되는 일반적인 쿼리 중 일부를 볼 수있다. 한 가지 예는 현재 읽고 있는 블로그 게시물의 이벤트를 가져 오는 것 이다. 특정 쿼리의 성능을 보다 잘 파악할 수 있는 EXPLAIN ANALYZE 을 통해 실행 계획 을 확인할 수 있다.
EXPLAIN ANALYZE SELECT * FROM events WHERE app_info_id = 7559; QUERY PLAN
-------------------------------------------------------------------
Seq Scan on events (cost=0.00..63749.03 rows=38 width=688) (actual time=2.538..660.785 rows=89 loops=1)
Filter: (app_info_id = 7559)
Total runtime: 660.885 ms데이터를 순차적으로 스캔하고 있으므로 인덱스를 통해 최적화 할 수 있다. index concurrently 로 추가하여 해당 테이블에 대한 잠금을 방지하고 성능이 어떻게 되는지 볼 수 있다.
CREATE INDEX CONCURRENTLY idx_events_app_info_id ON events(app_info_id);
EXPLAIN ANALYZE SELECT * FROM events WHERE app_info_id = 7559;
----------------------------------------------------------------------
Index Scan using idx_events_app_info_id on events (cost=0.00..23.40 rows=38 width=688) (actual time=0.021..0.115 rows=89 loops=1)
Index Cond: (app_info_id = 7559)
Total runtime: 0.200 ms쿼리결과에서 명백한 개선점을 볼 수 있지만 New Relic 에서의 결과를 검토 하고 이 인덱스 외에 몇 가지 다른 인덱스를 추가하여 데이터베이스 상에서의 시간을 크게 단축했다.
인덱스 캐시 Hit Rate
마지막으로 캐시 내에 인덱스의 수에 관심이 있다면 두 가지를 결합하여 다음을 실행할 수 있습니다.
SELECT
sum(idx_blks_read) as idx_read,
sum(idx_blks_hit) as idx_hit,
(sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM
pg_statio_user_indexes;일반적으로 이 값이 일반 캐시 적중률인 99 %과 같아야 한다.
