Postgre 최적화신속한 테스트를 위한 SQL

Postgre 최적화신속한 테스트를 위한 SQL

Postgre로 전환합니다.SQLite의 SQL을 사용하여 일반적인 Rails 애플리케이션을 지원합니다.

문제는 PG에서 실행 사양이 느려졌다는 것입니다.
SQLite에서는 최대 34초, PG에서는 최대 76초로 2배 이상 느립니다.

이제 코드 수정 없이 사양의 성능을 SQLite와 동등하게 하기 위해 몇 가지 기술을 적용하려고 합니다(접속 옵션 설정만으로는 불가능할 수 있습니다).

내 머릿속에서 몇 가지 분명한 것은 다음과 같다.

• RAM 디스크 (OSX에서의 RSpec과의 적절한 셋업)
• 기록되지 않은 테이블(모든 스크립트를 변경할 필요가 없도록 데이터베이스 전체에 적용할 수 있습니까?)

아시겠지만 저는 신뢰성과 그 외 다른 것에 신경 쓰지 않습니다(여기서는 DB가 일회용일 뿐입니다).
PG를 최대한 활용해서 최대한 빨리 만들어야 해요.

가장 좋은 답변은 바로 그 요령, 설정 및 이러한 요령의 단점을 이상적으로 설명하는 것입니다.

★★★★★★★ fsync = off+full_page_writes = off65세~16세시작은 좋았지만 목표치인 34와는 거리가 멀었다.

업데이트 2: RAM 디스크를 사용하려고 했지만 성능 향상은 오류 범위 내에 있었습니다.그래서 그럴 가치가 없어 보인다.

업데이트 3:* 가장 큰 병목 현상을 발견하여 SQLite와 동일한 속도로 사양을 실행할 수 있게 되었습니다.

문제는 잘라낸 데이터베이스 청소입니다.SQLite가 너무 빠른 것 같아요.

이를 "수정"하기 위해 각 테스트 전에 트랜잭션을 열고 마지막에 롤백합니다.

700개까지의 테스트에 사용할 수 있는 수치입니다.

• 잘라내기:SQLite - 34s, PG - 76s
• 트랜잭션:SQLite - 17s, PG - 18s.

SQLite의 속도가 2배 향상되었습니다.PG의 경우 속도가 4배 향상됩니다.

첫째, 항상 최신 버전의 Postgre를 사용합니다.SQL. 성능 향상은 항상 이루어지기 때문에 이전 버전을 튜닝하는 경우 시간을 낭비하고 있을 수 있습니다.예를 들어 Postgre 입니다.SQL 9.2는 의 속도를 크게 향상시키고 인덱스 전용 검색을 추가합니다.마이너 릴리스도 항상 따라야 합니다.버전 정책을 참조해 주세요.

하지 않다

RAMDisk 또는 기타 내구성이 없는 스토리지에 테이블스페이스를 배치하지 마십시오.

테이블스페이스가 손실되면 전체 데이터베이스가 손상되어 큰 작업이 없으면 사용하기 어려울 수 있습니다. 쓰는 것에 UNLOGGED램

시스템을 ramdisk는 다음과 .initdb initdb a new Postgre " the 가능한 PostgreRamdisk" SQL "Postgre" 가 생성됩니다.SQL 스 sql sql sql

PostgreSQL 서버 구성

테스트 시에는 내구성은 없지만 동작속도가 빨라지도록 서버를 설정할 수 있습니다.

이것은 Postgre 설정에 대해 허용되는 유일한 사용법 중 하나입니다.SQL. 이 설정은 Postgre에게 거의 알려준다.SQL은 명령된 쓰기 작업이나 기타 데이터 무결성 보호 및 크래시 안전과 같은 번거로운 작업에 번거롭지 않습니다.이것에 의해, 전원이 끊어지거나 OS가 크래시 했을 경우에 데이터를 완전하게 폐기할 수 있습니다.

필요도 없이, 「 」 「 」 「 」 「 」를 유효하게 하지 주세요.fsync=offPg를 데이터의 임시 데이터베이스로 사용하지 않는 한 다른 곳에서 다시 생성할 수 있습니다.fsync를 끄려고 하는 경우에만 꺼질 수 있습니다.그때는 더 이상 도움이 되지 않습니다.주의해 주세요.fsync=off ★★★★★★★★★★★★★★★★★」full_page_writes클러스터 수준에서 적용되므로 Postgre의 모든 데이터베이스에 영향을 미칩니다.SQL 인스턴스

할 수 .synchronous_commit=off commit_delay 하다 보면을 많이 수 있기 fsync=off큰 데이터 손상 위험이 없습니다.비동기 커밋을 활성화하면 최근 데이터가 손실되는 시간이 적습니다만, 이것으로 끝입니다.

약간 DDL을 사용할 .UNLOGGED서버 크래시 시 테이블이 지워지는 대신 WAL 로깅을 완전히 회피하고 실질적인 속도를 높일 수 있습니다. 않는 을 로깅하지 않도록 설정할 .이 옵션은 다음 시간 동안 설정해야 합니다.CREATE TABLE이 기능은 테스트에 도움이 될 뿐만 아니라 데이터베이스에 생성되거나 중요하지 않은 데이터가 가득 차 있는 테이블이 있으면 편리합니다.데이터베이스에 안전한 데이터가 포함되어 있지 않으면 편리합니다.

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

★★shared_buffers작업 부하에 맞게 조정하십시오.이는 OS에 따라 다르며 사용 중인 머신에서 어떤 일이 일어나고 있는지에 따라 시행착오가 필요합니다.이치노 를 늘리는 의 메모리 가 있는 경우가 .shared_buffers Postgre에서SQL 9.2 이하, 9.3 이상은 이를 피하기 위해 공유 메모리를 사용하는 방법을 변경했습니다.

을 몇 하고 있는는, 「」을 늘려 주세요.work_mem더 많은 램을 가지고 놀 수 있도록 하는 등입니다.가 너무 높으면 .work_mem설정은 연결 단위가 아닌 패킷 단위가므로 하나의 쿼리에 다수의 중첩 정렬이 있을 수 있으므로 메모리 부족 문제가 발생할 수 있습니다.정말 늘리기만 하면 됩니다.work_mem「 to 」를 할 수 EXPLAIN또는 설정(권장)으로 로그가 되지만, 값이 높을수록 Pg는 보다 스마트한 플랜을 선택할 수도 있습니다.

다른 포스터에서 언급했듯이 xlog와 메인 테이블/인덱스를 가능하면 별도의 HDD에 저장하는 것이 현명합니다.개별 파티션은 별 의미가 없습니다. 드라이브를 따로 사용해야 합니다.같이 이 적습니다.fsync=off사용하시는 경우는 거의 없습니다.UNLOGGED를 참조할 수 있습니다.

마지막으로 쿼리를 조정합니다.하세요.random_page_cost ★★★★★★★★★★★★★★★★★」seq_page_cost 중인 시스템 퍼포먼스를 합니다.effective_cache_size하다. 사용하다EXPLAIN (BUFFERS, ANALYZE)쿼리 하고, 「」를 .auto_explain하여 모든 저속 합니다.적절한 인덱스를 작성하거나 비용 파라미터를 조정하는 것만으로 쿼리 성능을 대폭 향상시킬 수 있는 경우가 많습니다.

는 전체 클러스터를 AFIK로 할 수 UNLOGGED그렇게 할 수 있으면 재밌을 것 같아요.Postgre Postgre에서하는 것을 .SQL の sql sql sql sql

호스트 OS 튜닝

운영 체제 수준에서도 몇 가지 튜닝을 수행할 수 있습니다.가장 중요한 것은 디스크에 쓰기가 언제, 언제, 언제, 어떤 식으로든 상관없기 때문에 적극적으로 디스크에 쓰기를 플러시하지 않도록 운영체제를 설득하는 것입니다.

Linux에서는 가상 메모리 서브시스템의dirty_* 「」등)dirty_writeback_centisecs.

라이트백 설정을 조정할 때 너무 느슨한 유일한 문제는 다른 프로그램에 의한 플래시로 인해 모든 Postgre가 발생할 수 있다는 것입니다.SQL의 누적 버퍼도 플러시되므로 쓰기 작업이 차단되는 동안 크게 중단됩니다.Postgre를 실행하면 이 문제를 완화할 수 있습니다.SQL은 다른 파일 시스템에 있지만 일부 플러시는 파일 시스템 수준이 아닌 장치 수준 또는 전체 호스트 수준일 수 있으므로 이를 신뢰할 수 없습니다.

이 튜닝에서는 워크로드에 가장 적합한 설정을 고려해야 합니다.

알맹이에서는, 「알맹이 없다」라고 하는 것을 이 좋을지도 모릅니다.vm.zone_reclaim_mode는 0으로 설정되어 있습니다.이는 Postgre Postgre가 NUMA 시스템에 수 있기 때문입니다. 시스템(NUMA 0)과의 으로 인해 합니다.이 SQL을 관리하다shared_buffers.

쿼리 및 워크로드 조정

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

작업을 대규모 트랜잭션으로 일괄 처리하지 않을 경우 시작합니다.많은 작은 거래들은 비용이 많이 들기 때문에 가능한 한 실용적으로 물건을 배치해야 합니다.비동기 커밋을 사용하는 경우 이 기능은 그다지 중요하지 않지만 여전히 권장됩니다.

가능하면 임시 테이블을 사용하십시오.WAL 트래픽을 생성하지 않으므로 삽입 및 업데이트 속도가 훨씬 빠릅니다. 조작한 , 임시 테이블로 를 이동시키는 것이 수 있습니다.INSERT INTO ... SELECT ...마지막 테이블에 복사하기 위해서요.임시 테이블은 세션 단위입니다.세션이 종료되거나 연결이 끊기면 임시 테이블은 사라지며 다른 연결에서는 세션 임시 테이블의 내용을 볼 수 없습니다.

Postgre를 사용하는 경우SQL 9.1 이상에서는 세션 상태와 같이 손실될 수 있는 데이터에 대해 테이블을 사용할 수 있습니다.이들은 서로 다른 세션에 걸쳐 표시되며 연결 간에 유지됩니다.서버가 부정하게 셧다운되어 재작성할 수 없는 항목에 사용할 수 없는 경우, 이러한 파일은 잘립니다.그러나 캐시, 구체화 뷰, 상태 테이블 등에 매우 적합합니다.

마세요.DELETE FROM blah;을 사용하다TRUNCATE TABLE blah;대신 테이블에 모든 행을 덤프하는 것이 훨씬 빠릅니다.을 한 .TRUNCATE많이 할 때는 요.TRUNCATES단, 작은 테이블은 몇 번이고 반복한다. 참조: Postgresql 잘라내기 속도

키에 , " ]를 참조해 주세요.DELETE이러한 외부 키에 의해 참조되는 프라이머리 키가 관련된 것은 매우 느립니다." " "를 할 경우 인덱스를 하십시오.DELETE이치노은 필요 .TRUNCATE.

불필요한 인덱스를 작성하지 마십시오.각 인덱스에는 유지 보수 비용이 있습니다.최소 인덱스 세트를 사용하고 비트맵 인덱스 스캔을 통해 너무 많은 수의 거대하고 값비싼 다중 열 인덱스를 유지 관리하는 대신 인덱스를 결합하도록 하십시오.인덱스가 필요한 경우 먼저 테이블을 채운 다음 마지막에 인덱스를 만듭니다.

하드웨어

데이터베이스 전체를 저장할 수 있는 충분한 RAM을 갖추는 것은 관리할 수 있다면 큰 이점이 있습니다.

충분한 RAM이 없는 경우, 고속의 스토리지를 이용할 수 있습니다.값싼 SSD라도 회전하는 녹에 비하면 엄청난 차이가 납니다.그러나 저렴한 SSD를 프로덕션용으로 신뢰하지 마십시오. SSD는 크래시 세이프가 되지 않고 데이터가 손실될 수 있습니다.

학습

그렉 스미스의 책 포스트그레SQL 9.0 High Performance는 다소 오래된 버전을 참조하더라도 관련성이 있습니다.그것은 유용한 참고 자료가 될 것이다.

포스트그리에 가입SQL 일반 메일링 목록 및 그에 따릅니다.

읽기:

• 포스트그리의 조정SQL 서버 - PostgreSQL Wiki
• 데이터베이스 연결 수 - PostgreSQL Wiki

다른 디스크 레이아웃 사용:

• 다른 디스크로 $PGDATA
• $PGDATA/pg_xlog용 다른 디스크
• tem 파일의 다른 디스크(데이터베이스 $PGDATA//pgsql_tmp별)(work_mem에 대한 참고 참조)

postgresql.conf 조정:

• shared_memory: 사용 가능한 RAM의 30%이지만 6~8GB를 넘지 않습니다.쓰기 부하가 높은 워크로드에는 공유 메모리(2GB~4GB)를 줄이는 것이 좋을 것 같습니다.
• work_mem: 대부분 정렬/조회가 있는 선택 쿼리에 사용됩니다.이는 연결별 설정이며 쿼리는 해당 값을 여러 번 할당할 수 있습니다.데이터가 맞지 않으면 디스크가 사용됩니다(pgsql_tmp).필요한 메모리 용량을 확인하려면 "설명 분석"을 선택하십시오.
• fsync 및 synchronous_commit:기본값은 안전하지만 데이터 손실을 허용할 수 있는 경우 설정했다가 해제할 수 있습니다.
• random_page_cost: SSD 또는 고속 RAID 어레이를 사용하는 경우 이를 2.0(RAID)으로 낮추거나 SSD의 경우 1.1로 낮출 수 있습니다.
• checkpoint_target: 상위 32 또는 64로 이동하여 checkpoint_target을 0.9로 변경할 수 있습니다.값이 낮으면 충돌 후 복구가 고속화됩니다.

언급URL : https://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing