Tagged: 데이터베이스

Better VACCUM FULL For PostgreSQL 9.0

원문:https://wiki.postgresql.org/wiki/What’s_new_in_PostgreSQL_9.0#Better_VACUUM_FULL

지금까지 VACUUM FULL 은 매우 느렸다. 이 구문(Statement)는 테이블로부터 빈공간을 확보하고 그것의 크기(Size)를 줄였지만 VACUUM 은 만족할만큼 빠르게 동작하지 않았다.

이것이 느린 이유는 동작 방법에 문제가 있었기 때문이다. 레코드를 그들의 소스블럭으로부터 테이블에 시작점에 가까운 블럭으로 하나하나씩 읽어서 옮겼었다. 그리고 테이블에 끝이 빈공간이면 그 부분을 지웠다.

전략적으로 이러한 방법은 매우 비효율적(Inefficient) 이다. 레코드를 하나하나씩 옮기는 것은 랜덤 I/O를 발생시킨다. 게다가, 인덱스(Index)를 재구성하는 동안에 그것을 유지하고, 더 많은 비용이 들어가는, 인덱스는 파편화된다. 이럴바에는 VACUUM FULL이 다 끝나고 나서 재인덱스(reindex)를 하는 것이 낫다.

Version 9.0 에서 VACUUM FULL은 테이블과 동일한 또 하나의 테이블을 만들고 거거에 모든 레코드를 순차적이게 복사한다. 모든 레코드가 복사되고 나면, 인덱스를 재생성하고 예전 테이블은 삭제되고 새로운 테이블로 대체된다.

이것은 VACUUM 을 보다 빠르게 한다. 하지만 VACUUM FULL은 동작하는 동안에 AccessExclusiveLock 을 필요로 한다. 이전 방법과 비교했을때 이 방법의 결점은 VACUUM FULL을 했을 경우 새로운 버전의 테이블을 만들기 위해서 디스크에 테이블 크기의 2배의 용량을 사용할 수 있다는 것이다.

이제, 두가지 방법에 대해서 실시간으로 비교를 해보자. 먼저 아래와 같은 방법으로 테스트 데이터를 준비 했다.

Vaccum Test
PgSQL
1
2
3
4
5
6
7
8
9
10
marc=# CREATE TABLE test (a int);
CREATE TABLE
marc=# CREATE INDEX idxtsta on test (a);
CREATE INDEX
marc=# INSERT INTO test SELECT generate_series(1,1000000);
INSERT 0 1000000
marc=# DELETE FROM test where a%3=0; -- making holes everywhere
DELETE 333333
marc=# VACUUM test;
VACUUM

8.4 버전에서는.

Vaccum for 8.4 version
PgSQL
1
2
3
4
5
6
7
8
marc=# \timing
Timing is on.
marc=# VACUUM FULL test;
VACUUM
Time: 6306,603 ms
marc=# REINDEX TABLE test;
REINDEX
Time: 1799,998 ms

다 합해 대충 9초 정도 걸렸다.

9.0 버전에서는.

Vaccum for 9.0 version
PgSQL
1
2
3
4
5
marc=# \timing
Timing is on.
marc=# VACUUM FULL test;
VACUUM
Time: 2563,467 ms

아직 이것은 제품에서(아마 서비스중에라는 뜻인듯..) VACUUM FULL 이 좋은 아이디어라는 뜻은 아니다. 만약 이것이 필요하다면, 아마도 VACUUM 정책이 올바르지 않기 때문일 것이다. (응?)

명시적 락킹(Explicit Locking)

이 문서는 PostgreSQL 9.1.2 문서중에 13.3 챕터를 해석한 것입니다. 전문 번역가도 아니고 중학교 영어만으로 해석한 것이어서 의미전달이 제대로 되지 않을 수도 있음을 미리 밝힙니다.

원문: http://www.postgresql.org/docs/9.1/static/explicit-locking.html

13.3 명시적 락킹(Explicit Locking)

PostgreSQL 은 테이블의 데이터에 동시적 접근(concurrent access)을 제어하기 위해서 다양한 락 모드(Lock mode)를 제공합니다. 이러한 모드들은 MVCC(다중 버전닝 동시성 제어)가 바라던 해동을 제공해주지 않을때에 애플리케이션 제어 락킹을 하려고 할때에 사용되어질수 있습니다.  또 대부분의 PostgreSQL 명령어들(commands)은 명령어가 실행되는 동안에 비호환적인 방법으로 참조하는 테이블이 변경되거나 버려지진(Dropped)않도록 하기위해서 자동적으로  적절한 락을 획득합니다. (예를들어, TRUNCATE는  같은 테이블에서 다른 연산들을 가지고 안전하게 동시에 실행되어지지 않을 수 있습니다. 그래서 안전성을 획득하기 위해서 강정적으로 테이블에 배타적 락(Exclusive Lock)을 얻얼 수 있습니다.)

13.3.1 테이블 수준의 락들(Table-Level Locks)

아래의 리스트는 PostgreSQL에 의해서 자동적으로 사용되어지는 사용가능한 락 모드와 문맥(contexts)들을 보여줍니다. 당신은 LOCK 이라는 명령어를 통해서 명시적으로 이러한 락들을 획득할 수 있습니다. 한가지 주목할 것은, 비록 이름에 “row”라고 되어있어도 테이블 수준의 락들(Table-Level Locks)이라는 것입니다. 락 모드의 이름들은 역사적입니다.

몇몇 확장된(extent) 이름들은 각 락 모드마다 전통적인 사용법과 반대적입니다. 하지만 의미론적으로는 모두 같습니다. 하나의 락 모드와 다른 락과의 차이에 대한 한가지 사실은 각각 상충되는 락 모드가 설정된다는 것입니다. 두개의 트랜잭션(transaction)은 같은 시점에 같은 테이블에서 상충적인 락 모드(locks of conflicting, 서로 모순되는 락 모드) 를 가질 수 없습니다. (하지만, 하나의 트랙잭션은 그 자체적으로 출돌이 나지 않습니다. 예를 들면, 같은 테이블에서 ACCESS EXCLUSIVE LOCK을 획득하고 후에 ACCESS SHARE LOCK를 획득할 수 있습니다. ) 상충적이지 않은 락 모드는 많은 트랜잭션에 의해서 동시적으로 가질 수 있습니다. 주목할 것은 특정 상황에서 어떤 락 모드는 자체 상충적(self-conflicting)이 됩니다. (예를들어, ACCESS EXCLUSIVE LOCK는 같은 시점에서 하나의 트랜잭션보다 더 많이 가질 수 없습니다.)

Table-level Lock Modes

ACCESS SHARE

오직 ACCESS EXCLUSIVE 락 모드와 상충적입니다.

SELECT 명령어는 참조하는 테이블에 이 모드의 락을 획득합니다. 일반적으로, 테이블에서 수정은 하지 않고 오직 읽기만을 하는 모든 쿼리들은 이 락 모드를 획득합니다.

ROW SHARE

이것은 EXCLUSIVE 와 ACCESS EXCLUSIVE 락 모드와 상충됩니다.

SELECT FOR UPDATE 와 SELECT FOR SHARE 명령어들은 대상 테이블에서 이 모드의 락을 획득합니다. (추가적으로 ACCESS SHARE 는 selected FOR UPDATE/FOR SHARE 가 아닌 참조되어지는 다른 모든 테이블을 락 합니다.

ROW EXCLUSIVE

이것은 SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE 와 ACCESS EXCLUSIVE 락 모드와 상충됩니다.

UPDATE, DELETE, INSERT 명령어는 대상 테이블에서 이 락 모드를 획득합니다. (추가적으로 ACCESS SHARE 는 다른 참조되어지는 모든 테이블을 락 합니다. 일반적으로, 이 모드는 테이블에서 데이터를 수정하는 모든 명령어들에 의해서 이 모드는 획득되어 집니다.

SHARE UPDATE EXCLUSIVE

SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 락 모드와 상충됩니다. 이 모드는 VACUUM 실행과 동시적 스키마 변화에 대해서 테이블을 보호합니다.

VACUUM( FULL 제외), ANALYZE, CREATE INDEX CONCURRENTLY, 그리고 특정한 ALTER TABLE 폼에 의해서 획득됩니다.

SHARE

ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE 그리고 ACCESS EXCLUSIVE 락 모드와 상충됩니다. 이 모드는 동시적으로 데이터를 변경하는 것에 대해 테이블을 보호 합니다.

CREATE INDEX(CONCURRENTLY 제외) 에 의해서 획득되어 집니다.

SHARE ROW EXCLUSIVE

ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 락 모드와 상충 됩니다. 이 모드는 동시적 데이터 변경과 특정시점에서 한 세션이 그것을 가지고 있기위해서 자체 exclusive(self-exclusive) 한 것에 대해서 테이블을 보호 합니다.

이 락 모드는 PostgreSQL의 어떤 명령어로도 자동적으로 획득되어지지 않습니다.

EXCLUSIVE

ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 락 모드와 상충 됩니다. 이 모드는 오직 동시적 ACCESS SHARE 락만을 허용합니다. 예를들어, 테이블로부터 읽을때에 한 트랜잭션이 이 락 모드로 홀딩되면 다중으로 실행을 할수 있습니다.

이 락 모드는 PostgreSQL의 어떤 명령어로도 테이블에 자동적으로 획득되어지지 않습니다.

ACCESS EXCLUSIVE

모든 모드의 락과 상충됩니다. 이 모드는 어떤 방법으로든 트랜잭션이 테이블에 접근하는 것을 보장합니다.

ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, 그리고 VACUUM FULL 명령어에 의해서 획득되어 집니다. 또, 이 모드는 LOCK TABLE 구문에 대한 기본적인 락 모드 입니다.

Tip: 오직 ACCESS EXCLUSIVE 는 SELECT 구문을 블럭하는 락입니다.

한번 획득되어진 락은 트랜잭션이 끝날때가지 들고 있습니다. 하지만 만약에 저장시점이 설립된 이후에 락이 획득되어진 것이라면, 저장시점이 롤백되는 즉시 해제 됩니다. This is consistent with the principle that ROLLBACK cancels all effects of the commands since the savepoint. The same holds for locks acquired within a PL/pgSQL exception block: an error escape from the block releases locks acquired within it.