반응형

Multiversion Concurrency Control(MVCC)는 읽고/쓰기 작업 간의 동시성을 확보 준다.  (MVCC 지원하는 DB : Oracle/DB2 partial / Mysql innodb engine/Informix/Firebird/MSSQL)

"readers never block writers, and writers never block readers"

MVCC는 하나의 tuple에 대하여 xmin(created) , xmax(expired) 라는 트랜잭션 정보를 저장해서 읽기 일관성을 보장한다.

 

Read committed Isolation level 경우 SQL문이 수행 , 해당 시점의 커밋 가장 최근의 트랜잭션 ID 수집하고, SQL 수행 중인 트랜잭션 ID 수집하여 ,

해당 트랜잭션 ID값을 가지고 데이터를 조회 .



위의 그림에서 쿼리를 수행 당시 커밋 가장 트랜잭션 ID 100이다. 그러므로 가져올 있는 가장 xmax값은 100 것과 같거나 작아야 하며,

현재 변경 중인 데이터(활성 트랜잭션) 25, 50, 70 트랜잭션 ID 값은 확정 되지 않았으므로 읽어오는 조건에 만족한다면 이전 데이터를 읽어 와야 한다.

또한 삭제 되었거나, 변경된 데이터도 Commit 되지 않은 이전 이미지를 읽어 와야 한다.


  

테스트 사전 준비

사전 준비

 

# Test Table 생성

CREATE TABLE test_mvcc (id int) ;

COMMIT ;

 

# 익스텐션 설치

CREATE EXTENSION pageinspect;

COMMIT ;

 

# 페이지 사용내용

DROP VIEW test_mvcc_page ;

CREATE VIEW test_mvcc_page

AS

SELECT 

             '(0,' || lp || ')' as ctid ,

            case lp_flags when 0 then 'Unused'

                             when 1 then 'Normal'

                             when 2 then 'Redirect to ' || lp_off

                             when 3 then 'Dead' end flag ,

             t_xmin::text::int8 as xmin ,

             t_xmax::text::int8 as xmax ,

             t_field3::text::int8 as cmin_cmax ,

             (t_infomask::integer & X'0020'::integer)::bool as is_combocid ,

             t_ctid

FROM  heap_page_items(get_raw_page('test_mvcc',0))

order by lp ;

COMMIT ;

 

# 테이블 크기 확인

SELECT  pg_size_pretty(pg_relation_size('test_mvcc')) ;

 

# 테이블 파일 path

SELECT pg_relation_filepath('test_mvcc');                 

 

#조회쿼리

SELECT id , xmin , xmax , cmin , cmax FROM test_mvcc ;

SELECT  get_raw_page::text  FROM    get_raw_page('test_mvcc', 0);   

 

  

 

Insert 테스트

Insert 작업 시 xmin 값만 설정 됨 (created)  

 

session 1

session2

SELECT  pg_size_pretty(pg_relation_size('test_mvcc')) ;

+----------------+

| pg_size_pretty |

+----------------+

| 0 bytes        |

+----------------+

 

INSERT  INTO  test_mvcc VALUES (1) ;

 

SELECT id , xmin , xmax , cmin , cmax FROM test_mvcc ;

+----+---------+------+------+------+

| id |  xmin   | xmax | cmin | cmax |

+----+---------+------+------+------+

|  1 | 1381537 |    0 |    0 |    0 |

+----+---------+------+------+------+

 

 

 

 

 

 

 

 


commit ;

 

SELECT id , xmin , xmax , cmin , cmax FROM test_mvcc ;

+----+---------+------+------+------+

| id |  xmin   | xmax | cmin | cmax |

+----+---------+------+------+------+

|  1 | 1381537 |    0 |    0 |    0 |

+----+---------+------+------+------+

 

select * from test_mvcc_page ;

+-------+--------+---------+------+-----------+-------------+--------+

| ctid  |  flag  |  xmin   | xmax | cmin_cmax | is_combocid | t_ctid |

+-------+--------+---------+------+-----------+-------------+--------+

| (0,1) | Normal | 1381537 |    0 |         0 | f           | (0,1)  |

+-------+--------+---------+------+-----------+-------------+--------+

 

 

 

 

 

 

 

 

 

   

 

 

 

 

 

 

 

 

 

 

 

 

 


  

SELECT id , xmin , xmax , cmin , cmax FROM test_mvcc ;

+----+------+------+------+------+

| id | xmin | xmax | cmin | cmax |

+----+------+------+------+------+

+----+------+------+------+------+

 

 

     

 








 

 

 

  

 

 

 

 

 

 

SELECT id , xmin , xmax , cmin , cmax FROM test_mvcc ;

+----+---------+------+------+------+

| id |  xmin   | xmax | cmin | cmax |

+----+---------+------+------+------+

|  1 | 1381537 |    0 |    0 |    0 |

+----+---------+------+------+------+

 

 

Update 테스트

before 데이터는 xmax 값이 설정 되고, 업데이트 된 Row는 새로 insert 되므로 xmin 값이 설정 됨.

test_mvcc_page 뷰를 이용하여 조사해 보면 기존 값과 신규 값 두개의 row가 들어가 있음을 확인 할 수 있다

 

session 1

session2

update test_mvcc set id = id * 10 ;

 

SELECT id , xmin , xmax , cmin , cmax FROM test_mvcc ;

+----+---------+------+------+------+

| id |  xmin   | xmax | cmin | cmax |

+----+---------+------+------+------+

| 10 | 1381538 |    0 |    0 |    0 |

+----+---------+------+------+------+

 

select * from test_mvcc_page ;

+-------+--------+---------+---------+-----------+-------------+--------+

| ctid  |  flag  |  xmin   |  xmax   | cmin_cmax | is_combocid | t_ctid |

+-------+--------+---------+---------+-----------+-------------+--------+

| (0,1) | Normal | 1381537 | 1381538 |         0 | f           | (0,2)  |

| (0,2) | Normal | 1381538 |       0 |         0 | f           | (0,2)  |

+-------+--------+---------+---------+-----------+-------------+--------+

 

      

 

 

 

 

 

 

 

 


 

commit ;

 

SELECT id , xmin , xmax , cmin , cmax FROM test_mvcc ;

+----+---------+------+------+------+

| id |  xmin   | xmax | cmin | cmax |

+----+---------+------+------+------+

| 10 | 1381538 |    0 |    0 |    0 |

+----+---------+------+------+------+

 

select * from test_mvcc_page ;

+-------+--------+---------+---------+-----------+-------------+--------+

| ctid  |  flag  |  xmin   |  xmax   | cmin_cmax | is_combocid | t_ctid |

+-------+--------+---------+---------+-----------+-------------+--------+

| (0,1) | Normal | 1381537 | 1381538 |         0 | f           | (0,2)  |

| (0,2) | Normal | 1381538 |       0 |         0 | f           | (0,2)  |

+-------+--------+---------+---------+-----------+-------------+--------+

 

             

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SELECT id , xmin , xmax , cmin , cmax FROM test_mvcc ;

+----+---------+---------+------+------+

| id |  xmin   |  xmax   | cmin | cmax |

+----+---------+---------+------+------+

|  1 | 1381537 | 1381538 |    0 |    0 |

+----+---------+---------+------+------+

 

 

 

 

 

 

 

 

 

 

 

 




 

 

 

   

 

  

 

SELECT id , xmin , xmax , cmin , cmax FROM test_mvcc ;

+----+---------+------+------+------+

| id |  xmin   | xmax | cmin | cmax |

+----+---------+------+------+------+

| 10 | 1381538 |    0 |    0 |    0 |

+----+---------+------+------+------+

 

 

Delete 테스트

삭제된 데이터에 tmax 값이 설정 .

 

session 1

session2

delete from test_mvcc ;

 

SELECT id , xmin , xmax , cmin , cmax FROM test_mvcc ;

+----+------+------+------+------+

| id | xmin | xmax | cmin | cmax |

+----+------+------+------+------+

+----+------+------+------+------+

  

select * from test_mvcc_page ;

+-------+--------+---------+---------+-----------+-------------+--------+

| ctid  |  flag  |  xmin   |  xmax   | cmin_cmax | is_combocid | t_ctid |

+-------+--------+---------+---------+-----------+-------------+--------+

| (0,1) | Normal | 1381537 | 1381538 |         0 | f           | (0,2)  |

| (0,2) | Normal | 1381538 | 1381539 |         0 | f           | (0,2)  |

+-------+--------+---------+---------+-----------+-------------+--------+

 

 

 

 

 

 

 




 

commit ;

 

SELECT id , xmin , xmax , cmin , cmax FROM test_mvcc ;

+----+------+------+------+------+

| id | xmin | xmax | cmin | cmax |

+----+------+------+------+------+

+----+------+------+------+------+

 

select * from test_mvcc_page ;

+-------+--------+---------+---------+-----------+-------------+--------+

| ctid  |  flag  |  xmin   |  xmax   | cmin_cmax | is_combocid | t_ctid |

+-------+--------+---------+---------+-----------+-------------+--------+

| (0,1) | Normal | 1381537 | 1381538 |         0 | f           | (0,2)  |

| (0,2) | Normal | 1381538 | 1381539 |         0 | f           | (0,2)  |

+-------+--------+---------+---------+-----------+-------------+--------+

 

SELECT  pg_size_pretty(pg_relation_size('test_mvcc')) ;

+----------------+

| pg_size_pretty |

+----------------+

| 8192 bytes     |

+----------------+

 

 

 

 







  

 

SELECT pg_relation_filepath('test_mvcc');

+----------------------+

| pg_relation_filepath |

+----------------------+

| base/16983/41563     |

+----------------------+

 

 

 

 



 

 

vacuum test_mvcc ;

 

select * from test_mvcc_page ;

+-------+--------+------+------+-----------+-------------+--------+

| ctid  |  flag  | xmin | xmax | cmin_cmax | is_combocid | t_ctid |

+-------+--------+------+------+-----------+-------------+--------+

| (0,1) | Unused | NULL | NULL |      NULL | NULL        | NULL   |

| (0,2) | Unused | NULL | NULL |      NULL | NULL        | NULL   |

+-------+--------+------+------+-----------+-------------+--------+

 

단순 Vacuum 명령을 수행 시키면 사용하지 않는 공간을 FSM(Free space map) 등록 시키는 작업 하고, 기존에 사용하지 않는 공간에 대한 coalesce 작업은 진행 되지 않는다.

 

SELECT  pg_size_pretty(pg_relation_size('test_mvcc')) ;

+----------------+

| pg_size_pretty |

+----------------+

| 8192 bytes     |

+----------------+

 

 

 

 

 

 

 

 

 

 

 

 

 




 

vacuum full test_mvcc ;

 

SELECT  pg_size_pretty(pg_relation_size('test_mvcc')) ;

+----------------+

| pg_size_pretty |

+----------------+

| 0 bytes        |

+----------------+

   

select * from test_mvcc_page ;

 ERROR:  block number 0 is out of range for relation "test_mvcc"

 

SELECT pg_relation_filepath('test_mvcc');

+----------------------+

| pg_relation_filepath |

+----------------------+

| base/16983/41571     |

+----------------------+

 

full vacuum 명령 수행 시는 새로운 테이블을 만들고 기존 테이블의 데이터를 새로 insert 한다고 보면 간단하다, 대상 테이블에 대한 2배의 사이즈가 필요할 있으며, 실제 데이터가 들어 있는 사이즈로 테이블 사이즈가 축소 된다.

 

 

 

                   

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SELECT id , xmin , xmax , cmin , cmax FROM test_mvcc ;

+----+---------+---------+------+------+

| id |  xmin   |  xmax   | cmin | cmax |

+----+---------+---------+------+------+

| 10 | 1381538 | 1381539 |    0 |    0 |

+----+---------+---------+------+------+

  

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


 

 

SELECT id , xmin , xmax , cmin , cmax FROM test_mvcc ;

+----+------+------+------+------+

| id | xmin | xmax | cmin | cmax |

+----+------+------+------+------+

+----+------+------+------+------+

 

 

 

 

 

 



 

  

[enterprisedb@localhost 16983]$ ls | grep 41563

41563

 

 

 

 

 

 


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 




[enterprisedb@localhost 16983]$ ls | grep 41563

41563

41563_fsm

41563_vm

 

fsm 파일은 free space map 정보를 저장한 파일

vm 파일은 visibility map 테이블의 페이지 내에 dead tuple 존재 유무의 정보가 저장됨.

 

fsm 파일과 vm 파일은 최초 vacuum명령 수행 생성 되며, 추후 vacuum 명령 수행 업데이트 되어 관리된다.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  

 

 

 

 

  

[enterprisedb@localhost 16983]$ ls | grep 41563

기존 테이블 없어 .

 

[enterprisedb@localhost 16983]$ ls | grep 41571

41571

새로운 테이블 생성

 

 

FSM (Free Space Map)

새로운 Insert update 발생 경우 PostgreSQL FSM(Free Space Map)에서 사용 가능한 공간을 할당 받아서 데이터를 저장한다.

Update Delete 의하여 데이터는 삭제 되었으나, 실제 물리적인 공간을 차지하고 있는 dead tuple들은 Vacuum 명령을 이용하여 FSM 반환을 주어야 한다.

Vacuum명령을 이용하여 사용되지 않는 공간을 정리하지 않을 경우, insert update 빈번히 발생하는 테이블의 크기가 dead tuple 인하여 정상적으로 있다.


 

VM(Visibility Map)

Free Space Map 사용 가능한 공간의 정보가 저장되는 map 반면, Visibility Map(VM) 페이지 안에 dead tuple 없는 페이지를 나타내는 Map이다.

Vacuum수행 해당 페이지를 읽어야 하는지 말아야 하는지 판단 하는데 사용되며, Only index 스캔으로 쿼리에 대한 결과를 가져올 참조되는 파일.

테이블의 하나의 페이지당 하나의 Bit 표현한다.

Vacuum수행 연속된 32개의 all-visible page 있으면, vacuum Skip한다.

최초 vacuum 명령으로 생성되고, vacuum 명령으로 update 된다.   (dead tuple 없는 페이지가 1)


9.6 이하 버전에서는 단순히 페이지내의 dead tuple 있는지 없는지에 대한 정보만을 VM에 저장하였다. 이로 인하여 테이블이 autovacuum 동작할 age가되면 테이블에 변경 사항이 없더라도, 매번 불필요한 테이블 Full Scan 발생을 하게 되어, 이로 인하여 성능 이슈가 발생 있었다. (autovacuum을 꺼 놓아도 Anti-Wraparound를 위하여 autovacuum이 수행 됨)


그러나 9.6 버전에서는 VM안에 frozen bit 추가하여 테이블의 frozenxid 보다 페이지 내의 모든 Row xmin값이 작을 경우, 해당 row들은 insert 이후(또는 Update 이후) 변경이 없는 상태이 므로 vacuum 대상에서 제외하기 위하여 frozen Bit 1(true) 설정 한다.

이로 인하여 autovacuum 발생하게 되면 frozen bit 1 아닌 0 페이지들만 대상으로 autovacuum 수행하게 변경 .

반응형

+ Recent posts