[SQL] 3 백만 행 PostgreSQL 데이터베이스에 느린 간단한 업데이트 쿼리

3 백만 행 PostgreSQL 데이터베이스에 느린 간단한 업데이트 쿼리

나는 Postegres 8.4에 ~ 300 만 개 행이 테이블에 간단한 UPDATE 테이블 SET의 컬럼 1 = 0을 시도하고 있지만 끝까지 영원히하고있다. 그것은 이상 10 분 동안 실행되었습니다. 지금 내 마지막 시도한다.

전에, 나는 VACUUM을 실행하고 해당 테이블에서 명령을 분석하기 위해 노력하고 있지만 아무도 도움을 보인다 (I는이 경우에 어떤 차이를 만들 것입니다 의심하지만) 나는 또한 일부 인덱스를 만들려고.

어떤 다른 아이디어?

감사, 리카르도

최신 정보:

이 테이블 구조입니다 :

CREATE TABLE myTable
(
  id bigserial NOT NULL,
  title text,
  description text,
  link text,
  "type" character varying(255),
  generalFreq real,
  generalWeight real,
  author_id bigint,
  status_id bigint,
  CONSTRAINT resources_pkey PRIMARY KEY (id),
  CONSTRAINT author_pkey FOREIGN KEY (author_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT c_unique_status_id UNIQUE (status_id)
);

나는 UPDATE myTable에 SET generalFreq = 0을 실행하려합니다;

해결법

1. ==============================

   1.이 답변에서보세요 : PostgreSQL의이 배열 및 업데이트의 많은 큰 테이블에 둔화

   이 답변에서보세요 : PostgreSQL의이 배열 및 업데이트의 많은 큰 테이블에 둔화

   제 1 힘 테이블 재 작성에 VACUUM FULL을, 더 나은 FILLFACTOR로 시작하고 UPDATE 쿼리 후 HOT 업데이트를 확인 :

   SELECT n_tup_hot_upd, * FROM pg_stat_user_tables WHERE relname = 'myTable';
   

   업데이트에 대한 기록이 많이있을 때 HOT 업데이트가 훨씬 빠릅니다. HOT에 대한 자세한 내용은이 문서에서 찾을 수 있습니다.

   추신. 당신은 버전 8.3 이상이 필요합니다.

2. ==============================

   2.나는 각 행에 대한 다양한 값으로 1 억 행의 테이블을 업데이트해야합니다. 각 실행은 100 ~ 수백만의 변화 (10 %)한다. 당신은 파티션을 사용하는 경우 PostgreSQL을 항상 최적화 쿼리를 준비하지으로 내 첫 번째 시도는 직접 특정 파티션에 300K 업데이트 트랜잭션에 그룹화이었다.

   나는 각 행에 대한 다양한 값으로 1 억 행의 테이블을 업데이트해야합니다. 각 실행은 100 ~ 수백만의 변화 (10 %)한다. 당신은 파티션을 사용하는 경우 PostgreSQL을 항상 최적화 쿼리를 준비하지으로 내 첫 번째 시도는 직접 특정 파티션에 300K 업데이트 트랜잭션에 그룹화이었다.

   CREATE TEMP TABLE tempTable (id BIGINT NOT NULL, field(s) to be updated,
   CONSTRAINT tempTable_pkey PRIMARY KEY (id));
   

   사용 가능한 RAM의 따라 버퍼에 업데이트 잔뜩 축적 이 채워진, 또는 필요 테이블 / 파티션의 변화에 ​​때, 또는 완료 :

   COPY tempTable FROM buffer;
   UPDATE myTable a SET field(s)=value(s) FROM tempTable b WHERE a.id=b.id;
   COMMIT;
   TRUNCATE TABLE tempTable;
   VACUUM FULL ANALYZE myTable;
   

   즉, 실행 지금 100 수백만 업데이트 1.5 시간 대신 18 시간 소요, 진공이 포함되어 있습니다. 시간을 절약하기 위해, 마지막에 진공 FULL을 할 필요는 없습니다 만도 빠른 일반 진공는 데이터베이스에 트랜잭션 ID를 제어하고 출퇴근 시간에 원치 않는 자동 진공을 얻을 수 유용합니다.

3. ==============================

   3.35 분 대기 후. 내 UPDATE 쿼리 (아직하지 않았다) 끝날 때까지 나는 뭔가 다른 시도하기로 결정했다. 내가 명령했다 무슨 짓을했는지 그래서 :

   35 분 대기 후. 내 UPDATE 쿼리 (아직하지 않았다) 끝날 때까지 나는 뭔가 다른 시도하기로 결정했다. 내가 명령했다 무슨 짓을했는지 그래서 :

   CREATE TABLE table2 AS 
   SELECT 
     all the fields of table1 except the one I wanted to update, 0 as theFieldToUpdate
   from myTable
   

   그런 다음, 인덱스를 추가 이전 테이블을 삭제하고 그 자리를 취할 수있는 새 이름을 바꿉니다. 즉 1.7 분했다. 인덱스 및 제약 조건을 다시 몇 가지 여분의 시간이 플러스를 처리합니다. 그러나 도움을했다! :)

   아무도 데이터베이스를 사용하지 않았기 때문에 단지 일했다 물론. 이 프로덕션 환경에 있었다면 내가 먼저 테이블을 잠글 필요가있다.

4. ==============================

   4.오늘은 유사한 문제와 함께 많은 시간을 보냈습니다. 업데이트하기 전에 모든 제약 / 인덱스를 드롭 : 나는 해결책을 발견했습니다. 열이 인덱싱되지 않았거나 업데이트되고 있는지 여부에 상관없이, 그것은 psql의 업데이트처럼 모든 갱신 된 행에 대한 모든 인덱스를 보인다. 업데이트가 완료되면, 제약 / 인덱스를 다시 추가합니다.

   오늘은 유사한 문제와 함께 많은 시간을 보냈습니다. 업데이트하기 전에 모든 제약 / 인덱스를 드롭 : 나는 해결책을 발견했습니다. 열이 인덱싱되지 않았거나 업데이트되고 있는지 여부에 상관없이, 그것은 psql의 업데이트처럼 모든 갱신 된 행에 대한 모든 인덱스를 보인다. 업데이트가 완료되면, 제약 / 인덱스를 다시 추가합니다.

5. ==============================

   5.이 (generalFreq 유형의 REAL 및 숙박 동일하게 시작합니다)를보십시오 :

   이 (generalFreq 유형의 REAL 및 숙박 동일하게 시작합니다)를보십시오 :

   ALTER TABLE myTable ALTER COLUMN generalFreq TYPE REAL USING 0;
   

   이것은 +가 만든 DROP 유사한 테이블을 다시 작성하고, 모든 인덱스 다시 작성됩니다. 그러나 모두 하나의 명령. 훨씬 더 빨리 (배에 대한) 당신이 그것을 테이블 잠금 않지만, 종속성 및 다시 인덱스 및 기타 물건을 처리 할 필요가 없습니다 (액세스 전용 -. 즉을 전체 잠금) 기간 동안. 아니면 당신이 뒤에 대기하는 다른 모든를 원한다면 당신이 원하는 것을의 그. 당신이 "너무 많은"행을 업데이트하지 않을 경우이 방법은 업데이 트보다 느립니다.

6. ==============================

   6.나는 (https://dba.stackexchange.com/questions/118178/does-updating-a-row-with-the-same-value-actually-update-the-row에서) 좋을 것 우선 만에입니다 업데이트 행이 "필요"가, 예 :

   나는 (https://dba.stackexchange.com/questions/118178/does-updating-a-row-with-the-same-value-actually-update-the-row에서) 좋을 것 우선 만에입니다 업데이트 행이 "필요"가, 예 :

    UPDATE myTable SET generalFreq = 0 where generalFreq != 0;
   

   (또한 generalFreq에 인덱스를해야 할 수도 있습니다). 그럼 당신은 적은 수의 행을 업데이트 할 수 있습니다. 에 관계없이 값이 변경 여부에 그들과 모든 인덱스를 업데이트, 그렇지 않은 경우는 아니지만 값이 이미 모든 비 제로, 그러나 적은 수의 행을 업데이트하는 경우 "도움이 될 수 있습니다".

   또 다른 옵션 : 별이 기본값과 null이되지 제약의 관점에서 정렬하는 경우, 당신은 이전 열을 삭제 단지, 인스턴트 시간을 메타 데이터를 조정하여 다른를 만들 수 있습니다.

7. ==============================

   7.당신은 어떻게 그것을 실행하는? 각 행을 반복하고 업데이트 문을 수행하는 경우, 당신은 믿을 수 없을만큼 느리게 수행 할 이유입니다 잠재적으로 개별 업데이트의 수백만을 실행하고 있습니다.

   당신은 어떻게 그것을 실행하는? 각 행을 반복하고 업데이트 문을 수행하는 경우, 당신은 믿을 수 없을만큼 느리게 수행 할 이유입니다 잠재적으로 개별 업데이트의 수백만을 실행하고 있습니다.

   당신은 하나 개의 문장에있는 모든 레코드에 대한 하나의 UPDATE 문을 실행하는 경우가 훨씬 더 빨리 실행됩니다,이 과정이 느린 경우 다음 아래 하드웨어에 무엇보다도 아마. 3 백만 레코드가 많이있다.

8. ==============================

   8.내 테스트에서 나는 큰 업데이트 200 개 이상의 000 행, 심지어 임시 테이블, 100 000 행보다 느린 2 업데이트입니다 것으로 나타났습니다.

   내 테스트에서 나는 큰 업데이트 200 개 이상의 000 행, 심지어 임시 테이블, 100 000 행보다 느린 2 업데이트입니다 것으로 나타났습니다.

   내 솔루션은 각 루프에서 다음, 내 값을 계산이 표에, 200 000 행의 임시 테이블을 만들 아소 새 값으로 내 주요 테이블을 업데이트, 루프입니다 ...

   모든 2 000 000 행, 나는 수동으로 나는 자동 진공는 업데이트를 위해 그 일을하지 않는 것으로 나타났습니다, "VACUUM는 MYTABLE ANALYZE".

9. ==============================

   9.시험

   시험

   UPDATE myTable SET generalFreq = 0.0;
   

   아마 캐스팅 문제는

from https://stackoverflow.com/questions/3361291/slow-simple-update-query-on-postgresql-database-with-3-million-rows by cc-by-sa and MIT license