DB 파티셔닝 (Partitioning) 개념

 

 

 

 

 

 

 

 

목차

 

 

 

 

 

 

 

배경

실시간 주기 보고 시스템 DB에 저장하는 데이터 규모가 커질 것으로 예상되어 DB Partitioning 기법을 도입하려고 한다. AWS RDS에서 Legacy DB는 db.t2.micro 크기로 사용하고 있으며, 상용화 시 db.r5.large를 사용할 예정이다.

모델	코어 개수	vCPU*	시간당 CPU 크레딧	메모리(GiB)	네트워크 성능(Gbps)
db.t2.micro	1	1	6	1	낮음에서 중간

 

모델	코어 개수	vCPU	메모리(GiB)	스토리지(GiB)	전용 EBS 대역폭(Mbps)	네트워킹 성능(Gbps)
db.r5.large	1	2	16	EBS 전용	최대 3,500	최대 10

 

DB 파티셔닝(Partitioning)이란 ? 

데이터베이스 분할(Partitioning) 또는 파티셔닝은 데이터베이스를 여러 부분으로 분할하는 것이다. 데이터베이스 분할은 중요한 튜닝기법으로 데이터가 너무 커져서, 조회하는 시간이 길어질 때 또는 관리 용이성, 성능, 가용성 등의 향상을 이유로 행해지는 것이 일반적이다. 분할된 각 부분을 ‘파티션’이라고 부른다. 물리적인 데이터 분할이 있지만 DB에 접근하는 app의 입장에서는 이를 인식하지 못함. 

 

* 참고 DB 샤딩(Sharding)과 동일한 개념이다. 같은 테이블 스키마를 가진 데이터를 다수의 데이터베이스에 분산하여 저장하는 것을 의미한다.  (=Horizontal Partitioning)

DB 파티셔닝(Partitioning) 종류

수평 분할 (Horizontal Partitioning)

= 샤딩(Sharding)과 동일한 개념 

스키마를 복제하여 분할 키(Partitioning Keys)를 기준으로 데이터를 나눈다. 

여기서 분할 키는 id가 된다. id가 1~2에 대한 정보는 하나의 샤드에 저장, 나머지 id가 3~5에 해당하는 정보는 다른 샤드에 저장한다. 

 

수직 분할 (Vertical partitioning)

스키마를 나누어 데이터가 옮겨가는 것을 말한다. 테이블의 일부 열을 분할하는 형태. RDB의 3정규화 개념으로 접근 (단, 수직 분할은 이미 정규화 된 데이터를 분리하는 과정임)

user 관리 데이터의 유연성을 위해 id를 참조하도록 하여 일부 정보를 분리한다.

 

분할 기준

1. 범위 분할 (range partitioning) : BY RANGE

분할 키 값이 범위 내에 있는지 여부로 구분한다. 예를 들어, 우편 번호를 분할 키로 수평 분할하는 경우이다.

테이블 생성 및 데이터 입력

year 범위로 정렬한다. 1991년 미만이면 p0, 1991~1994면 p1, 1995~1998이면 p2로 분할된다.

CREATE TABLE t1 (
    id INT,
    year_col INT
)
PARTITION BY RANGE (year_col) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (1999)
);

INSERT INTO t2 (id, year_col)
VALUES (1, 1990),(2, 1991),(3,1996),(4,1999),(5,1995),(6,1989),(7,1891),(8,1892);

조회

partition p2를 조회한다.

SELECT * FROM t2 PARTITION (p2);

결과

조회

데이터가 정상적으로 옮겨졌는지 확인한다.

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME =  't1';

결과

-----> 이부분은 이해가 안가니 다시 확인해보도록 하자..

왜 p1, p2 가 각각 1, 2가 아니고 0,1이지?

2. 목록 분할 (list partitioning)

값 목록에 파티션을 할당 분할 키 값을 그 목록에 비추어 파티션을 선택한다. 예를 들어, Country 라는 컬럼의 값이 Iceland , Norway , Sweden , Finland , Denmark 중 하나에 있는 행을 빼낼 때 북유럽 국가 파티션을 구축 할 수 있다.

테이블 생성 및 데이터 입력

store_id 에 따라서 pNorth, pEast, pWest, pCentral 로 분할된다.

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

INSERT INTO employees (id, fname, lname, hired, separated, job_code, store_id)
VALUES (1, 'soyeon', 'yoon', '2013-12-26', '9999-12-31', 1, 20)
,(2, 'gildong', 'hong', '2011-01-01', '9999-12-31', 1, 3)
,(2, 'jungwoo', 'ha', '2011-04-12', '9999-12-31', 1, 7)
,(2, 'suzy', 'bae', '2012-06-21', '9999-12-31', 1, 6)
,(2, 'you', 'kong', '2012-09-04', '9999-12-31', 1, 12)
,(2, 'hyogin', 'kong', '2013-01-23', '9999-12-31', 1, 1)
,(2, 'jungmin', 'hwang', '2015-12-26', '9999-12-31', 1, 8)
,(2, 'nayeong', 'lee', '2011-01-01', '9999-12-31', 1, 2);

조회

SELECT * FROM employees PARTITION (pNorth);

결과

3. 해시 분할 (hash partitioning) : BY HASH

해시 함수의 값에 따라 파티션에 포함할지 여부를 결정한다. 예를 들어, 4개의 파티션으로 분할하는 경우 해시 함수는 0-3의 정수를 돌려준다. 범위 분할이나 목록 분할을 사용할 때에는 지정된 열 값이나 집합을 저장할 파티션을 명시적으로 지정해야 하지만, 해시 분할을 사용할 때에는 mySQL이 알아서 처리하므로, 해시 할 컬럼 값과 분할 된 테이블을 나눌 분할 수를 지정하면 된다. 즉 특정 Data가 어느 Hash Partition 에 있는지 판단이 불가하다. Hash Partition은 파티션을 위한 범위가 없는 데이터에 적합하다.

 

테이블 생성 및 데이터 입력

CREATE TABLE t1 (
    id INT,
    year_col INT
)
PARTITION BY HASH(id)
PARTITIONS 8;

INSERT INTO t1 (id, year_col)
VALUES (1, 1990),(2, 1991),(3,1996),(4,1999),(5,1995),(6,1989),(7,1891),(8,1892),(9,1891),(10,1810),(11,1990),(12,1993);

조회

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME =  't1';

결과

조회

SELECT * FROM t1 PARTITION (P4);

결과

p0 : (8, 1892)

p1 : (1, 1990) , (9, 1891)

p2 : (2, 1991) , (10, 1810)

p3 : (3, 1996) , (11, 1990)

p4 : (4, 1999) , (12, 1993)

p5 : (5, 1995)

p6 : (6, 1989)

p7 : (7, 1891)

 

참고

hash 값이 모두 같으면 어떻게 될까? 궁금해서 id를 1로 다바꿔봄.

p1 파티션에 몰빵됨..

4. 합성 분할 (Composite partitioning)

상기 기술을 결합하는 것을 의미하며, 예를 들면 먼저 범위 분할(Range Partitioning)하고, 다음에 해시 분할(Hash Partitioning) 같은 것을 생각할 수 있다. 컨시스턴트 해시법은 해시 분할 및 목록 분할의 합성으로 간주 될 수 있고 키 공간을 해시 축소함으로써 일람할 수 있게 한다.

테이블 생성 및 데이터 입력

테이블 ts는 3개의 RANGE 파티션(p0, p1, p2)이 있다.  해당 파티션은 각각 2개의 하위 파티션으로 나뉜다. 

1990년 미만인 경우 p0, 1990~1999인경우 p1, 2000~인 경우 p2로 분할된다. 실제로 전체 테이블은 3*2=6개의 파티션으로 나뉜다. 그러나 Partitioning By Range로 인해서 이 중 첫번째 2개는 purchased 열에 1990보다 작은 값을 가진 레코드만 저장한다. 

------->뭔소린지 모르겠다 ?

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );
    
INSERT INTO ts (id, purchased)
VALUES (1, '1991-12-26')
,(2 , '1989-01-01')
,(3, '1982-04-12')
,(4, '1999-06-21')
,(5, '2000-09-04')
,(6, '1983-01-23')
,(7, '1992-12-26')
,(8, '1994-01-01')
,(9, '1964-01-01')
,(10, '1934-01-01')
,(11, '1996-01-01')
,(12, '1996-01-01')
,(13, '1922-01-01');

조회

SELECT * FROM ts PARTITION (p0);

결과

조회

SELECT * FROM ts PARTITION (p1);

결과

조회

SELECT * FROM ts PARTITION (p2);

결과

ALTER TABLE

1. Non-partitioning table -> partitioning table

CREATE TABLE t1 (
    id INT,
    year_col INT
);

ALTER TABLE t1
    PARTITION BY HASH(id)
    PARTITIONS 8;

2. Add range

ALTER TABLE t2 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));

 

개념을 공부하며 궁금했던 것들?

Q1. 분할 종류는 수직/ 수평 두가지가 있는데, 분할 기준 4가지가 각각의 종류에 해당하는지? 아니면 수평 분할에만 해당되는지 모르겠다. 테스트를 해보니 수평 분할 된것처럼 보임. 

Reference

Database의 샤딩(Sharding)이란? : https://nesoy.github.io/articles/2018-05/Database-Shard

DB 파티셔닝(Partitioning)이란? : https://gmlwjd9405.github.io/2018/09/24/db-partitioning.html

RDB 정규화 ? : https://wkdtjsgur100.github.io/database-normalization/

데이터베이스 분할 위키백과 : https://ko.wikipedia.org/wiki/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4_%EB%B6%84%ED%95%A0

MySql Reference Manual : https://dev.mysql.com/doc/refman/5.7/en/partitioning-subpartitions.html

파티션 관리 : https://purumae.tistory.com/210