database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
ㆍPostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
PostgreSQL Q&A 9728 게시물 읽기
No. 9728
Postgres 설정 관련
작성자
이용하(yongha3546)
작성일
2016-11-04 16:13
조회수
8,971
 현재 시스템 전체 물리 메모리는 256G 입니다. 데이터공간은 5TB입니다.
 
통계분석용으로 R 과 같이 사용되고 있으며.
 
매일매일 200개 이상의 JOB이 배치로 돌고있으며,
 
약 5개의 큰 테이블에 각각 몇억건씩 데이터가 생성되며.
 
25개정도의 파티션으로 나누어집니다. 작업시간은 약 7시간이며.
 
최대한 속도를 높이기 위해서 아래 설정중 추가하거나 변경해야할 사항이 있을까요...?
 
 
 
max_connections = 50
 
shared_buffers = 96GB
 
effective_cache_size = 192GB
 
work_mem = 2048MB
 
maintenance_work_mem = 2GB
 
min_wal_size = 4GB
 
max_wal_size = 8GB
 
checkpoint_completion_target = 0.9
 
wal_buffers = -1
 
default_statistics_target = 500
 
fsync = off
 
synchronous_commit = off
 
autovacuum = on
 
temp_buffer = 1024MB
 
dynamic_shared_memory_type = posix
 
log_timezone = 'ROK'
 
 
 
wal_writer_delay = 10ms
 
log_autovacuum_min_duration = 0
 
autovacuum_naptime = 30min
 
max_locks_per_transaction = 96
 
 
 
'enable은 생략하겠습니다.
 
bitmapscan = on 
 
hashagg = on
 
hashjoin = on
 
indexscan = off
 
indexonlyscan = off
 
meterial = off
 
mergejoin = off
 
nestloop = off
 
seqscan = off
 
sort = off
 
tidscan = off
 
 
 
최근 잦은 배치 테스트때문 트랜잭션 ID가 겹쳣는지.
 
SINGLE MODE에서의 VACUUM을 해야하는 현상이 자주일어나고 있습니다. 시간은 4일 이상걸리구요.
 
AUTOVACUUM을 켜놧는 데도 불구하고. 이런현상이 일어나.
 
테이블 단위로 백업받고. 디비를 통째로 날린 후 새로운 디비에 다시 복구했습니다.
 
이런 용도로 사용하는 Postgres에서 과연 AUTOVACUUM을 켜놓은게 좋은건지.
 
아니면 AUTOVACUUM을 끄고 잡이 안도는 시간에 VACUUM을 날려야 할지 궁금합니다.
 
 
그리고 그동안 속도 이슈때문에 아카이브모드를 안쓰고있었는데.
 
트랜잭션 겹침 현상때문에 사용할까 고민중인데.
 
사용한다고 하면 7시간 걸리는 JOB이 어느정도 더 지연될지도 궁금합니다.
 
 
 
 
 
 
 
 
 
 
 
 
 
이 글에 대한 댓글이 총 1건 있습니다.

min_wal_size, max_wal_size 값은 pg_xlog 공간의 여유가 있을 만큼 크게 잡아두어도 좋습니다.

쿼리 최적화기 관련 설정값들은 초기값으로 돌려놓는 것이 무난합니다.

off 하는 경우는 정말 이 데이터베이스에서 사용하는 모든 쿼리에 대한 쿼리 실행 계획을 완벽하게 파악하고 있다고 판단할 때입니다. 실무 환경에서는 불가능하죠.

그래서, 이 설정값들은 응용프로그램에서 쿼리를 실행하기 전에, set 명령으로 임의로 바꾸고,

쿼리가 끝나면, 다시 원래 대로 돌려놓는 작업을 하는 것이 일반적입니다.

 

트랜잭션ID 겹침 방지 작업의 핵심은 일단

autovacuum_naptime 값은 기본값으로 돌리고,

autovacuum_max_workers 값을 서버가 감당하는 범위에서 충분히 크게 잡아주세요.

테이블 큰 놈들의 개수보다 몇개 더 많게 설정합니다. 여건이 안되면, 그래도 5개 이상은 잡아주세요.

 

다음 빨리 늙어가는 table을 찾아서 그들의 autovacuum_freeze_table_age, autovacuum_freeze_max_age, autovacuum_freeze_min_age 값을 테이블 별로 개별 설정해 줍니다. 

통상 늙어가는 나이보다 더 적게 잡습니다.  확인은 age(pg_class.relfrozenxid) 값으로 확인합니다.

이 부분은 일단 해당 테이블에 대해서 autovacuum worker가 작업을 진행할 때 파악할 값이기 때문에,

해당 테이블을 대해서, autovacuum_vacuum_scale_factor 값도 기본값(20%)도 많이 줄여주셔야할 것 같네요. 물론 update, delete가 일어난다면.

그렇지 않고, insert 전용 테이블이라면, 대량 트랜잭션이 발생한 뒤라면, vacuum freeze table 형태의 수동 vacuum 작업을 해주, 각 테이블을 최대한 젊게 가져가는 것이 중요합니다.

autovacuum 로그와, age(pg_class.relfrozenxid) 값을 같이 보면서 최적의 autovacuum 설정을 해 주어야 할 것 같습니다.

이런 작업에도 불구하고, 계속 위와 같은 장애가 생기면, PostgreSQL을 쓰지 말아야죠.

 

김상기(ioseph)님이 2016-11-04 17:16에 작성한 댓글입니다.
이 댓글은 2016-11-04 18:21에 마지막으로 수정되었습니다.
[Top]
No.
제목
작성자
작성일
조회
9732DB 복구 관련 문의 [1]
오진홍
2016-11-15
7694
9730Active-Hot_standby 구성 시 select 쿼리 처리 [2]
김성수
2016-11-10
7793
9729함수 일괄삭제 쿼리 [1]
slonik
2016-11-08
8009
9728Postgres 설정 관련 [1]
이용하
2016-11-04
8971
97271억건의 데이터.... [2]
초보
2016-11-03
7932
9726postgres 성능에 관련하여 질문드립니다. [5]
황하진
2016-11-01
7758
9725서버에 있는 sql과 윈도우상의 C프로그램 연동 [5]
김태훈
2016-10-27
7719
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.049초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다