Postgresql 잘림 속도

Postgresql 잘림 속도

---

우리는 Postgresql 9.1.4DB 서버로 사용 하고 있습니다. 나는 내 테스트 스위트의 속도를 높이려고 노력해 왔기 때문에 정확히 무슨 일이 일어나고 있는지보기 위해 db를 프로파일 링하는 것을 주시했다. 테스트가 끝날 때 테이블을 자르기 위해 database_cleaner 를 사용하고 있습니다 . 예 거래가 더 빠르다는 것을 알고 있으며 특정 상황에서 사용할 수 없으므로 걱정하지 않습니다.

내가 염려하는 것은 TRUNCATION이 DELETE를 사용하는 것보다 오래 걸리는 이유와 내 CI 서버에서 더 오래 걸리는 이유입니다.

현재 로컬에서 (Macbook Air에서) 전체 테스트 도구 모음에는 28 분이 걸립니다. 로그를 뒤따라 테이블을자를 때마다 ... 즉 :

TRUNCATE TABLE table1, table2  -- ... etc

잘림을 수행하는 데 1 초 이상 걸립니다. CI 서버 (Ubuntu 10.04 LTS)에서 로그를 추적하고 테이블을 자르는 데 8 초가 걸리고 빌드에는 84 분이 걸립니다.

:deletion전략으로 전환했을 때 로컬 빌드는 20 분이 걸렸고 CI 서버는 44 분으로 줄었습니다. 이것은 중요한 차이점이며 이것이 왜 그럴 수 있는지에 대해 정말 놀랐습니다. CI 서버 에서 DB를 조정 했는데 16GB 시스템 램, 4GB shared_buffers ... 및 SSD가 있습니다. 모든 좋은 것들. 그게 어떻게 가능해:

ㅏ. 2GB의 램
b를 사용하는 내 Macbook Air보다 훨씬 느립니다 . TRUNCATION은 postgresql 문서가 명시 적 으로 훨씬 더 빨라야한다고 명시 할 때 DELETE보다 훨씬 느립니다 .

이견있는 사람?

---

이것은 최근에 SO와 PostgreSQL 메일 링리스트에서 몇 차례 등장했습니다.

TL; DR 마지막 두 포인트 :

(a) shared_buffers가 클수록 CI 서버에서 TRUNCATE가 느려질 수 있습니다. 다른 fsync 구성이나 SSD 대신 회전 미디어를 사용하는 것도 문제가 될 수 있습니다.

(b) TRUNCATE고정 비용이 있지만 반드시보다 느리지는 DELETE않으며 더 많은 작업을 수행합니다. 다음에 나오는 자세한 설명을 참조하십시오.

업데이트 : 이 게시물에서 pgsql-performance에 대한 중요한 논의 가 발생했습니다. 이 스레드를 참조하십시오 .

업데이트 2 : 이 문제를 해결하는 데 도움이되는 9.2beta3에 개선 사항이 추가되었습니다 . 이 게시물을 참조하십시오 .

TRUNCATEvs에 대한 자세한 설명DELETE FROM :

주제에 대한 전문가는 아니지만, 내 이해는 TRUNCATE테이블 당 거의 고정 된 비용이있는 반면 DELETEn 행의 경우 최소 O (n)입니다. 삭제되는 테이블을 참조하는 외래 키가 있으면 더 나쁩니다.

나는 항상 a의 고정 비용이 거의 비어있는 테이블 TRUNCATE의 비용보다 낮다고 가정 DELETE했지만 이것은 전혀 사실이 아닙니다.

TRUNCATE table; 더 많은 일을 DELETE FROM table;

a 이후의 데이터베이스 상태는 TRUNCATE table대신 실행하는 것과 거의 같습니다.

• DELETE FROM table;
• VACCUUM (FULL, ANALYZE) table; (9.0+ 만 해당, 각주 참조)

... 물론 TRUNCATE실제로 a DELETE및 a로 그 효과를 얻지는 못합니다 VACUUM.

포인트는 것입니다 DELETE및 TRUNCATE그래서 당신은 단지 동일한 결과 두 개의 명령을 비교하지 않는, 다른 일을.

A DELETE FROM table;는 데드 행과 부풀림을 유지하고, 인덱스가 데드 항목을 전달하도록 허용하고, 쿼리 플래너에서 사용하는 테이블 통계를 업데이트하지 않습니다.

A TRUNCATE는 마치 CREATE편집 된 것처럼 완전히 새로운 테이블과 인덱스를 제공합니다 . 모든 레코드를 삭제하고 테이블을 다시 인덱싱하고 VACUUM FULL.

테이블을 다시 채울 것이기 때문에 테이블에 잔해가 남아 있는지 상관하지 않는다면를 사용하는 것이 더 나을 수 있습니다 DELETE FROM table;.

실행 중이 아니기 때문에 VACUUM죽은 행과 인덱스 항목이 부풀려 누적되어 스캔 한 다음 무시해야합니다. 이렇게하면 모든 쿼리가 느려집니다. 테스트가 실제로 그렇게 많은 양의 데이터를 생성하고 삭제하지 않는다면 눈치 채지 못하거나 신경 쓰지 않을 수 있으며, 그렇게 VACUUM한다면 테스트 실행을 통해 항상 한두 번의 부분을 수행 할 수 있습니다. 더 나은 방법은 공격적인 autovacuum 설정을 통해 autovacuum이 백그라운드에서이를 수행하도록하는 것입니다.

전체 테스트 스위트가 실행 된 TRUNCATE후에도 여전히 모든 테이블을 사용하여 많은 실행에서 효과가 축적되지 않도록 할 수 있습니다. 9.0 이상에서는 전 세계적으로 테이블에서 적어도 좋지는 않지만 좋으며 훨씬 쉽습니다.VACUUM (FULL, ANALYZE);

IIRC Pg에는 몇 가지 최적화 기능이있어 트랜잭션이 테이블을 볼 수있는 유일한 트랜잭션임을 알아 차리고 어쨌든 즉시 블록을 무료로 표시 할 수 있습니다. 테스트에서 팽창을 만들고 싶을 때이를 수행하려면 둘 이상의 동시 연결이 필요했습니다. 그래도 나는 이것에 의존하지 않을 것입니다.

DELETE FROM table; f / k ref가없는 작은 테이블에 매우 저렴합니다.

에 DELETE그것없이 외래 키 참조가 테이블에서 모든 레코드, 모든 대학원 순차 테이블 스캔을하고 설정이 xmax직면 한 튜플을. 이것은 매우 저렴한 작업입니다. 기본적으로 선형 읽기와 반 선형 쓰기입니다. AFAIK는 인덱스를 건드릴 필요가 없습니다. 그들은 죽은 튜플 VACUUM만을 포함하는 테이블의 블록을 비어있는 것으로 표시 하는 나중에 정리 될 때까지 계속해서 죽은 튜플을 가리 킵니다 .

DELETE단지 거기 경우 비싼 얻을 수 많은 기록을 선택해야 외래 키 참조가 많이있는 경우, 또는 후속 계산하면 VACUUM (FULL, ANALYZE) table;일치하는 데 필요한 TRUNCATE의 당신의 비용 내에서 효과를 DELETE.

여기 내 테스트에서 a DELETE FROM table;는 일반적으로 TRUNCATE0.5ms 대 2ms 보다 4 배 더 빠릅니다 . fsync=off이 데이터를 모두 잃어도 상관 없기 때문에 실행되는 SSD의 테스트 DB입니다 . 물론 DELETE FROM table;똑같은 작업을 수행하지 않으며 후속 조치를 VACUUM (FULL, ANALYZE) table;취하면 21ms가 훨씬 더 비싸므로 DELETE실제로 테이블이 필요하지 않은 경우에만 승리합니다.

TRUNCATE table; 더 많은 고정 비용 작업과 가사를 DELETE

반대로 a TRUNCATE는 많은 일을해야합니다. 테이블, TOAST 테이블 (있는 경우) 및 테이블에있는 모든 인덱스에 대해 새 파일을 할당해야합니다. 헤더는 해당 파일에 기록되어야하며 시스템 카탈로그도 업데이트해야 할 수 있습니다 (그 시점에서 확실하지 않고 확인하지 않음). 그런 다음 이전 파일을 새 파일로 교체하거나 이전 파일을 제거해야하며 일반적으로 모든 버퍼를 디스크에 플러시하는 동기화 작업 (fsync () 또는 이와 유사한 작업)을 통해 파일 시스템이 변경 사항을 따라 잡았는지 확인해야합니다. . (data-eating) 옵션으로 실행중인 경우 동기화를 건너 뛰었는지 확실하지 않습니다 fsync=off.

I learned recently that TRUNCATE must also flush all PostgreSQL's buffers related to the old table. This can take a non-trivial amount of time with huge shared_buffers. I suspect this is why it's slower on your CI server.

The balance

Anyway, you can see that a TRUNCATE of a table that has an associated TOAST table (most do) and several indexes could take a few moments. Not long, but longer than a DELETE from a near-empty table.

Consequently, you might be better off doing a DELETE FROM table;.

--

Note: on DBs before 9.0, CLUSTER table_id_seq ON table; ANALYZE table; or VACUUM FULL ANALYZE table; REINDEX table; would be a closer equivalent to TRUNCATE. The VACUUM FULL impl changed to a much better one in 9.0.

---

Brad, just to let you know. I've looked fairly deeply into a very similar question.

Related question: 30 tables with few rows - TRUNCATE the fastest way to empty them and reset attached sequences?

Please also look at this issue and this pull request:

https://github.com/bmabey/database_cleaner/issues/126

https://github.com/bmabey/database_cleaner/pull/127

Also this thread: http://archives.postgresql.org/pgsql-performance/2012-07/msg00047.php

I am sorry for writing this as an answer, but I didn't find any comment links, maybe because there are too much comments already there.

---

A couple of alternate approaches to consider:

• Create a empty database with static "fixture" data in it, and run the tests in that. When you are done, just just drop the database, which should be fast.
• Create a new table called "test_ids_to_delete" that contains columns for table names and primary key ids. Update your deletion logic to insert the ids/table names in this table instead, which will be much faster than running deletes. Then, write a script to run "offline" to actually delete the data, either after a entire test run has finished, or overnight.

The former is a "clean room" approach, while latter means there will be some test data will persist in database for longer. The "dirty" approach with offline deletes is what I'm using for a test suite with about 20,000 tests. Yes, there are sometimes problems due to having "extra" test data in the dev database but at times. But sometimes this "dirtiness" has helped us find and fixed bug because the "messiness" better simulated a real-world situation, in a way that clean-room approach never will.

---

I've encountered similar issue lately, i.e.:

1. The time to run test suite which used DatabaseCleaner varied widely between different systems with comparable hardware,
2. Changing DatabaseCleaner strategy to :deletion provided ~10x improvement.

The root cause of the slowness was a filesystem with journaling (ext4) used for database storage. During TRUNCATE operation the journaling daemon (jbd2) was using ~90% of disk IO capacity. I am not sure if this is a bug, an edge case or actually normal behaviour in these circumstances. This explains however why TRUNCATE was a lot slower than DELETE - it generated a lot more disk writes. As I did not want to actually use DELETE I resorted to setting fsync=off and it was enough to mitigate this issue (data safety was not important in this case).

참고URL : https://stackoverflow.com/questions/11419536/postgresql-truncation-speed