DBMS 공부 7 - 데이터베이스 파티셔닝 , 샤딩 ,리플리케이션

1. 데이터베이스 파티셔닝(Partitioning)'

 

VLDB(Very Large DBMS)

• 전체 DB가 하나의 DBMS시스템에 다 들어가기 힘들어지는 경우
• 테이블 들을 여러 개의 군으로 나눠서 분산 저장
• 하나의 테이블이 방대한 경우에는 사전방식(a-m , n-r , s-z)와 같이 나눠서 저장

파티셔닝의 제약사항 

 

1. 테이블 단위 연산이 힘들어진다 (비용문제)

• 조인연산 어려움 -> 정규화(Nomalization) 문제
• 역정규화(Denormalization) -> 중복허용으로 해결

2. 외래키(FK)의 효용문제

• 레코드 추가시 참조 무결성 조건 체크 -> 시스템 부담증가로 수동전환
• CRUD시 위치(LOCATION)를 인식해야 함 (파티셔닝 / 샤딩 다름)

파티셔닝의 이점

• 데이터 전체 검색시 필요한 부분만 탬색해 성능을 증가 시킬 수 있다. 
• 전체 데이터를 손실할 가능성이 줄어든다 -> 가용성(Avilability) 향상
• 파티션별 백업 / 복구가 가능하다 
• 파티션 단위로 I/O 분산이 가능하다. -> 업데이트 성능 증가

파티셔닝 방식들

 

1 . 범위(Range) 기반

- 알파벳 순 , 연도별 등으로 범위를 나눠서 파티셔닝을 한다. 

- 각 파티션별로 데이터 양의 차이가 있을 수 있다.  

 

2 . 해시(hash)

- 해시 함수를 사용해 파티션별로 크기를 비슷하게 나눈다

 

3 . 리스트(List)

- 특별한 컬럼을 기준으로 파티셔닝 하는 방식

 

4 . 컴포짓(composite)

-  range-hash  , range-lst 와 같이 여러개의 파티션 기법을  혼합하여 사용하는 방식

 

MySQL 에서의 파티셔닝

 

- MySQL 5.x 이상부터 지원한다 

- 최대 1024개까지 지원한다. 

- 모든 파티션은 동일한 스토리지 엔진을 사용해야한다.

- 파티션은 외래키 / Full Text 인덱스를 지원하지 않는다. 

 

 

BusinessCard 테이블 생성하고  creationdate 별로 파티셔닝 하라 .
BusinessCard에 creationtime이 2020 이하인 데이터를 담는 파티션 p4을 추가 하라.
파티션 p4를 삭제하라
BusinessCard에서 p2 에 속한 데이터를 확인하라

create table Businesscard(
     id int not null ,  
     name varchar(255), 
     address varchar(255),
     telephone varchar(255), 
     creationtime date)
partition by range(year(creationtime))(
    partition p0 values less than(2013),
    partition p1 values less than(2014),
    partition p2 values less than(2015),
    partition p3 values less than maxvalue
);

alter table businesscard add partition(partition p4 values less than 2020);
alter tables businesscard drop partion p4;
show create table businesscard; // 테이블 스키마 확인 명령어 
select * from businesscard partition(p1);

 

파티션 분할 병합

 

- 크기가 너무 큰 파티션을 분할하거나 너무 적은 파티션들을 병합할 수 있다 .

 

파티션 p3를 p3와 p4로 나눠라

alter table businesscard
reorganize partition p3 into(
  partition p3 values less than(2015),
  partition p4 values less than maxvalue
)

파티션 p2,p3를 파티션 p23 으로 병합하라

alter table businesscard 
reorganize partion p2,p3 into(
  partition p23 values than(2014)
)

 

2. 데이터베이스 복제 (Replication)

• DBMS의 내용을 복제해 동일한 DB내용을 유지 
• 두 개 이상의 DBMS 시스템을 마스터 / 슬레이브로 나눠  마스터 DBMS -> 슬레이브 DBMS로 SQL 쿼리 복제(select 제외)
•  데이터 업데이트( CUD )는 마스트에서  읽기(R)는 슬레이브에서 한다.
• 읽기 성능 향상
• 웹서버 시스템 성능확장 scale out 에 적합하다.

MySQL 에서는 master의 Binary Log를 slave에서 읽어가는 방식으로 Replication이 동작하며

Replication을 구성하는 방식에는   Statement  , Row, mixed 3가지 방식이 있다.

 

statement based 

 

- SQL을 복제 한다.

- TimeStamp , Auto_increment와 같은 값들로 인해 master와 slave에 저장되는 값이 달라질 수 있다.

 

Row Based

 

- SQL에 따라 변경된 라인만 기록하는 방식 

- 데이터가 많이 변경된 경우 데이터가 커진다.

 

Mixed

 

-statement 와 Row based 방식을 혼합한 방식

 

MySQL Replication reference 

 

https://dev.mysql.com/doc/refman/5.7/en/replication-setup-replicas.html