[sql] 빠른 테스트를 위해 PostgreSQL 최적화

먼저 항상 최신 버전의 PostgreSQL을 사용하십시오. 성능 향상은 항상 제공되므로 이전 버전을 조정하는 경우 시간이 낭비 될 수 있습니다. 예를 들어 PostgreSQL 9.2는 속도를 크게 향상시키고TRUNCATE 인덱스 전용 스캔을 추가합니다. 사소한 릴리스조차도 항상 따라야합니다. 버전 정책을 참조하십시오 .

하지마

RAM 디스크 또는 기타 비 영구 스토리지에 테이블 스페이스를 두지 마십시오 .

테이블 스페이스가 손실되면 전체 데이터베이스가 손상되어 상당한 작업없이 사용하기 어려울 수 있습니다. UNLOGGED어쨌든 테이블을 사용 하고 캐시를 위해 많은 RAM을 사용하는 것과 비교할 때 이점이 거의 없습니다 .

램 디스크 기반 시스템을 원한다면 램 디스크 에서 새로운 PostgreSQL 인스턴스를 사용하여 램 initdb디스크에 완전히 새로운 클러스터 initdb가 있으므로 완전히 일회용 PostgreSQL 인스턴스가 있습니다.

PostgreSQL 서버 구성

테스트 할 때 내구성이 있지만 더 빠른 작동을 위해 서버를 구성 할 수 있습니다 .

이것은 fsync=offPostgreSQL 의 설정에 허용되는 유일한 용도 중 하나입니다 . 이 설정은 PostgreSQL에게 명령 된 쓰기 나 다른 불쾌한 데이터 무결성 보호 및 충돌 안전 항목을 신경 쓰지 않도록 지시합니다.

말할 것도없이, fsync=offPg를 다른 곳에서 재생성 할 수있는 데이터의 임시 데이터베이스로 사용하지 않는 한 프로덕션 환경에서 사용하도록 설정해서는 안됩니다 . fsync를 해제하려는 경우에만 full_page_writes더 이상 아무런 효과가 없으므로 fsync를 끌 수도 있습니다 . 그 조심 fsync=off하고 full_page_writes상기 적용 클러스터 가 영향을 미치는, 그래서 수준 의 모든 PostgreSQL의 인스턴스에서 데이터베이스를.

대규모 데이터 손상 위험이없는 것과 동일한 이점을 많이 얻을 수 있으므로 프로덕션 용도로는를 사용 synchronous_commit=off하고 설정할 수 있습니다 . 비동기 커밋을 활성화하면 최근 데이터가 손실되는 작은 창이 있지만 그게 전부입니다.commit_delayfsync=off

DDL을 약간 변경하는 옵션이있는 경우 UNLOGGEDPg 9.1+의 테이블을 사용 하여 WAL 로깅을 완전히 피하고 서버가 충돌하는 경우 테이블이 삭제되는 대신 실제 속도를 높일 수 있습니다. 모든 테이블을 로그되지 않도록하는 구성 옵션이 없으므로이 시간 동안 설정해야합니다 CREATE TABLE. 테스트에 유용 할뿐만 아니라 데이터베이스에 생성되거나 중요하지 않은 데이터로 가득 찬 테이블이 있거나 그렇지 않으면 안전해야하는 항목이 포함 된 경우 유용합니다.

로그를 확인하고 너무 많은 체크 포인트에 대한 경고가 표시되는지 확인하십시오. 그렇다면 checkpoint_segments를 늘려야합니다 . 원활한 쓰기를 위해 checkpoint_completion_target을 조정할 수도 있습니다.

shared_buffers워크로드에 맞게 조정 하십시오. 이것은 OS에 따라 다르며 시스템에서 수행되는 다른 작업에 따라 다르며 시행 착오가 필요합니다. 기본값은 매우 보수적입니다. shared_buffersPostgreSQL 9.2 이하 에서 증가 시키면 OS의 최대 공유 메모리 제한을 늘려야 할 수도 있습니다. 9.3 이상은이를 피하기 위해 공유 메모리를 사용하는 방법을 변경했습니다.

많은 작업을 수행하는 연결을 몇 개만 사용하는 경우 work_mem정렬 등을 위해 더 많은 RAM을 사용할 수 있도록 연결을 늘리십시오 . 너무 높은 work_mem설정은 정렬 기준이 아니기 때문에 메모리 부족 문제를 일으킬 수 있습니다. 하나의 쿼리에 많은 중첩 정렬이있을 수 있습니다. 디스크에 쏟아 지거나 설정으로 기록 된 (권장) 항목 을 볼 수있는 경우 에만 실제로 증가 work_mem해야 하지만 값이 높을수록 Pg가 더 스마트 한 계획을 선택할 수 있습니다.EXPLAINlog_temp_files

다른 포스터에서 말했듯이 가능한 경우 xlog와 기본 테이블 / 인덱스를 별도의 HDD에 배치하는 것이 좋습니다. 별도의 파티션은 의미가 없습니다. 실제로 별도의 드라이브를 원합니다. 이 분리는 테이블을 fsync=off사용하는 경우 거의 효과가 없으며 거의 ​​사용하지 않는 경우 이점이 적습니다 UNLOGGED.

마지막으로 쿼리를 조정하십시오. 확인하십시오 확인 random_page_cost하고 seq_page_cost시스템의 성능을 반영, 당신의 확인 effective_cache_size등 올바른 사용을 EXPLAIN (BUFFERS, ANALYZE)개별 쿼리 계획을 검토하고 돌려 auto_explain모두 느린 쿼리를보고하기에 모듈을. 적절한 인덱스를 만들거나 비용 매개 변수를 조정하여 쿼리 성능을 크게 향상시킬 수 있습니다.

AFAIK 전체 데이터베이스 또는 클러스터를로 설정하는 방법은 없습니다 UNLOGGED. 그렇게 할 수 있다는 것은 흥미로울 것입니다. PostgreSQL 메일 링리스트에 문의하십시오.

호스트 OS 튜닝

운영 체제 수준에서 수행 할 수있는 조정도 있습니다. 가장 중요한 것은 운영 체제가 디스크에 쓰기 작업을 수행 할 때 디스크의 시간과 시간을 신경 쓰지 않기 때문에 디스크에 쓰기 작업을 적극적으로 플러시하지 않도록 설득하는 것입니다.

리눅스에서 당신은 이것을 제어 할 수있는 가상 메모리 서브 시스템 의 dirty_*같은 설정 dirty_writeback_centisecs.

쓰기 저장 설정 조정이 너무 느슨해지는 유일한 문제는 다른 프로그램에 의한 플러시로 인해 모든 PostgreSQL의 누적 버퍼가 플러시되어 모든 쓰기가 차단되는 동안 큰 중단이 발생할 수 있다는 것입니다. 다른 파일 시스템에서 PostgreSQL을 실행하여이를 완화 할 수 있지만 일부 플러시는 파일 시스템 수준이 아닌 장치 수준 또는 전체 호스트 수준 일 수 있으므로 이에 의존 할 수는 없습니다.

이 튜닝에는 실제로 작업 부하에 가장 적합한 설정을 확인하기 위해 설정을 조정해야합니다.

최신 커널에서는 vm.zone_reclaim_modePostgreSQL이 관리하는 방식과의 상호 작용으로 인해 NUMA 시스템 (요즘 대부분의 시스템)에서 심각한 성능 문제가 발생할 수 있으므로이를 0으로 설정해야합니다 shared_buffers.

쿼리 및 워크로드 튜닝

이것들은 코드 변경이 필요한 것들입니다. 그들은 당신에게 적합하지 않을 수 있습니다. 적용 할 수있는 것들도 있습니다.

더 큰 트랜잭션으로 작업을 일괄 처리하지 않는 경우 시작하십시오. 많은 소규모 거래는 비싸므로 가능하고 실용적 일 때마다 배치해야합니다. 비동기 커밋을 사용하는 경우 덜 중요하지만 여전히 권장됩니다.

가능하면 임시 테이블을 사용하십시오. WAL 트래픽을 생성하지 않으므로 삽입 및 업데이트 속도가 훨씬 빠릅니다. 때로는 많은 양의 데이터를 임시 테이블로 슬러 핑하여 필요에 따라 조작 INSERT INTO ... SELECT ...하고 최종 테이블에 복사하는 것이 좋습니다. 임시 테이블은 세션 당입니다. 세션이 종료되거나 연결이 끊어지면 임시 테이블이 사라지고 다른 연결은 세션 임시 테이블의 내용을 볼 수 없습니다.

PostgreSQL 9.1 이상을 사용하는 경우 UNLOGGED세션 상태와 같이 손실 될 수있는 데이터에 테이블을 사용할 수 있습니다 . 다른 세션에서 볼 수 있으며 연결간에 유지됩니다. 서버가 부정확하게 종료되면 잘리지 않으므로 다시 만들 수없는 항목에는 사용할 수 없지만 캐시, 구체화 된 뷰, 상태 테이블 등에 유용합니다.

일반적으로하지 마십시오 DELETE FROM blah;. TRUNCATE TABLE blah;대신 사용하십시오 . 테이블의 모든 행을 덤프하면 훨씬 빠릅니다. TRUNCATE가능하면 한 번의 호출로 많은 테이블을 자릅니다 . TRUNCATES그래도 많은 작은 테이블을 반복해서 수행하는 경우 경고가 있습니다 . 참조 : PostgreSQL 절단 속도

외래 키에 대한 색인이 없으면 DELETE해당 외래 키가 참조하는 기본 키와 관련된 항목이 엄청나게 느려집니다. DELETE참조 된 테이블에서 예상되는 경우 이러한 인덱스를 작성하십시오 . 에 대한 색인은 필요하지 않습니다 TRUNCATE.

필요없는 인덱스를 만들지 마십시오. 각 인덱스에는 유지 관리 비용이 있습니다. 최소한의 인덱스 집합을 사용하고 비트 맵 인덱스 스캔에서 너무 많은 비용이 많이 드는 다중 열 인덱스를 유지하는 대신 이들을 결합 시키십시오. 인덱스가 필요한 경우 먼저 테이블을 채우고 끝에 인덱스를 작성하십시오.

하드웨어

전체 데이터베이스를 보유 할 수있는 충분한 RAM이 있으면 관리 할 수 ​​있다면 큰 승리입니다.

RAM이 충분하지 않으면 스토리지가 빠를수록 더 좋습니다. 싸구려 SSD조차도 방적 녹에 비해 큰 차이를 만듭니다. 생산 용으로 저렴한 SSD를 신뢰하지 마십시오. SSD는 종종 충돌에 안전하지 않으며 데이터를 먹을 수도 있습니다.

배우기

Greg Smith의 저서 인 PostgreSQL 9.0 High Performance 는 다소 오래된 버전을 언급하더라도 여전히 관련성이 있습니다. 유용한 참조 자료가되어야합니다.

PostgreSQL 일반 메일 링리스트에 가입하고 팔로우하십시오.

독서:

• PostgreSQL 서버 튜닝-PostgreSQL Wiki
• 데이터베이스 연결 수-PostgreSQL Wiki

---

답변