[KITRI] My SQL 기반의 VLDB(Very Large Database) 시스템 구축·관리

My SQL 기반의 VLDB(Very Large Database) 시스템 구축·관리

하석재 강사/KITRI

교재 : Real MySQL 개발자와 DBA를 위한

Real MySQL 국내도서 저자 : 이성욱 출판 : 위키북스 2012.05.08 상세보기

첫 날 오전

VL-DBMS

Partitioning - 시스템 내부(mysql DB)

Sharding - 시스템 외부(서드파티)

R DBMS

Scalability - 확장성

Availability - 가용성

 정규화(Normalization)

1. 중복된 정보를 최소화 해라

2. 쪼개라.
   cf.  역정규화(Denormalization) : 필요한 정보를 중복시켜라 (ex. 댓글 갯수를 저장하는 컬럼)
   cf. PK
   cf. FK

3. 필요하면 테이블을 합쳐라(JOIN)

4. 원하는 정보를 찾아서 출력해라

5. 만들어진 큰 테이블을 삭제해라

OR DBMS(Object-Relational DBMS : 객체 관계형 DBMS = 오라클 입장)

RDBMS 장점/단점

1. 텍스트 문서 위주(동영상/이미지)

2. 객체(Object)형태의 데이터를 관리하기 쉽지 않음
   cf. ORM(Object Relation Mapping)

3. 데이터량(BigData)/쓰기성능(NoSQL)

DBMS의 특징

1. 읽기/검색에 최적화되어 있음
   (ex. 주민번호 등록/수정/삭제수 보다 검색 수가 많음)

2. CRUD(Create, Retrieve, Update, Delete)
   CUD(변경)/R(검색)

3. 인덱스(Index)를 추가해라
   cf.TPS(Transactions per second)

Index - > 정렬(Sorting)

정렬이 안되 있는 경우 1:n:n/2

정렬이 되어 있는 경우 1:최악(이진검색:log_2(n), B-tree:log_3(n))

이진검색 / B-tree계열 / Red-Black Tree

이진검색 : n/2 -> n/4 -> n/8 …… (Index: 미리 중간값을 정렬해둠)
* CUD -> Index Update 발생(수정/삭제시 시간이 더걸림)

B-tree(RDBMS 인덱스에서 주로 사용함)
alpha=m, beta=s

최악 log_3(n)

실습 환경
VMWare Player

Ubuntu Server

Mysql Community Server - Console / MySQL Workbench

첫 날 오후

Storage Engine

MyISAM

InnoDB - 고성능

GPL(Open source licence)

Scale-Out vs Scale-Up

리눅스

RedHat(Fedora, RHEL, CentOS) - > yum(.rpm)

Debian(Ubuntu) - > apt(.deb)

Mysql 설치

Id : root

Pw : 설정

Port : 3306

cf. 1521(Oracle)

Config : my.cnf

명령어 : service mysql start|stop|restart //시작, 정지, 재시작

: /etc/init.d/mysql  start|stop|restart //시작, 정지, 재시작

: mysql -uroot -p // 로그인

: desc smaple // smaple테이블의 스키마보기

SQL

DML : 데이터 조작 언어 : 레코드에 대해서 CRUD(INSERT, SELECT, UPDATE, DELETE)

DDL : 데이터 정의 언어 : 테이블 스키마에 대해서(CREATE, ALTER)

DCL : 데이터 제어 언어 : 접근 권한 부여/삭제(DBA : GRANT/REVOKE)

실습환경 구축

VMWare install / Ubuntu install

sudo apt-get install msql-server

sudo apt-get install openssh-servier

ifconfig

mysql  -uroot -p

wget http://……

tar xvfz ……

Mysql 구조 p.102

   mysql 엔진

커넥션핸들러

sql interpreter

sql parser : 해석기

sql 옵티마이저

캐시&버퍼

   storage 엔진

InnoDB - Trasaction 지원, Concurrency control(동시성제어)좋음, 레코드레벨락

MyISAM - Trasaction 불가, 테이블레벨 락

cf. Fault Tolerance(장애내성)

cf. Locking(락킹)
- Granularity(락킹 대상의 덩어리)
- 레코드 레벨 Locking
- 테이블 레벨 Locking

메모리 p.105

   Global - 전역 설정

   Session - 지역 설정, connection들의 묶음

SSD / HDD

VFAT/NTFS(WINDOWS), ZFS/EXT4FS(LINUX)

   SSD

1. seek time이 없음

2. Write횟수가 제한이 있음
   - Wearing (flat/locality)
   - wear leveling : 셀의 write횟수를 평준화 시킴

3. SATA3(550MB/s), PCI-E(r2GB/s, w1GB/s)
   - 큰 파일에 속도 빠름

4. overwrite가 안 됨(erase 후 기록)
   HDD는 실제 지우지 않고 지웠다고 표시만 하고 실제 overwrite 할 때 덮어씀
   TRIM기능 - 놀때 삭제 하는 기능
       - 명령어 vs OS
   cf.Flash File System

복제 p.111

   Replication(복제) vs Backup

Read 성능을 올리기 위한 방법

• 웹서버 계열

• 마스터는 1개만 가능, 슬레이브 DBMS에 웹서버를 붙임

• Write는 Master에 Read는 Slave

cf.galera cluster

실습

apt-get install unzip : zip 압축 해제를 위한 패키지 설치

unzip master.zip : zip 압축 해제

Replication Method

   Statement based - 쿼리 날림(시간차가 있어 시간관련 컬럼에 문제가 생김:now())

   Row based - 변경 Rows를 찾음
   Hybrid - 위에 두 개를 조합

Isolation level(격리레벨) p.191

   Read Uncommited - 성능 좋음, 값이 달라 질 수 있음 - 격리 레벨로 안보는 사람이 많음

   Read Commited - 트랜젝션이 끝난 것만 (general situation)

   Reapeatable Read (replication master required isolation level)

   Serializable - 데이터 확실, 성능이 낮음, thread의 이점이 없어짐- 단일 코어수행

Buffer(=Queue)

   FIFO

   대기 시간(delay) 발생 -> Real-time 처리가 어려움

   큐가 차야 비워짐(나감)

   Throughput(전송성능)이 중요하면 큐를 길게  

   Delay가 중요하면 큐를 짧게

   cf.Write시 flush(바로바로 비워서 실행하라)

Cache : 용량은 작은데 속도는 빠름

   memory hierachy

   cache(코어캐시:L1/L2, 굥유캐시:L3)

   Locality(Spatial, temporal)지역성

    WORD(64bit) -> 1000번지 - 1007번지까지 한번에 로드

   Hit ratio(85%)

1. Replacement Policy
   LRU(Least Recently Used) : 유휴 시간이 제일 긴 메모리 날림 - 요즘 대세
   <->MRU(mysql은 함께사용)
   LFU(Least Frequently Used) : 사용 빈도 낮은 메모리 날림

2. Write Policy
   Write Through : 캐시와 메모리 동시에 업데이트 -
   Write Back : 캐시 우선 수정 후 메모리 수정 - 성능 좋음

전문 검색 엔진 p.154

   패턴매칭

   … where name like ‘%하%’;

   … where name like ‘하__’;

   Like는 비싼 작업

   

   Full-Text Search

• DBMS를 검색엔진 스타일로 사용할 수 있음

• LIKE 연산자 보다 부하가 적음

• 구분자 기반 처리 vs N-gram

   cf. against(), match()

Mysql Log 파일 p.158

   *슬로우 쿼리 로그

   

이 일차 오전

Lock vs Transaction(Thread Synchronization)

   All or nothing(Commit or Rollback)

   autoCommit 모두 (줄단위 커밋) -> conn.setAutoCommit(false)

   local transaction -> disbributed transaction / global transaction

2 phase commit protocol

ACID(https://ko.wikipedia.org/wiki/ACID )

CAP이론(http://wiki.nex32.net/%EC%9A%A9%EC%96%B4/cap%EC%A0%95%EB%A6%AC)

Shared Medium

RAR(pessimistic, optimistic)

RAW()

WAR

WAW(auto_increment) - 제일 문제

         CoC Naming(ID:int, auto_increment) recommend

wait / blocked / locked(dead lock)

Global lock

Table lock

Record lock

required, mandatatory, compulsory

recommended

optional

snapshot

metadata lock

MVCC(Multi Version Concurrency Control)

For update

세마포어

RAID(고가용성을 고려)

   Redundancy(중복성, 이중화, 다중화)

   RAID 0,1,5,6, 10

RAID 0 : Stripping + 1:mirroring

RAID 5, 6 : 실제 사용 가능성이 높음(회사)- 체크섬, 페러티

   Software RAID vs Hardware RAID

Server Hotswap

High Availablity(고가용성)

Dual power supply + UPS

Dual port router

balanced tree vs skewed tree

   cf. 포화 이진 트리, 완전 이진 트리, 스큐드 트리, 균형트리

Red-black tree(Self balanced tree)

B-tree/Binary Tree의 문제점

   Search에 강함

   Range search가 쉽지 않음
- 앞, 뒤 검색이 쉽지 않음

- linked list

CDN(Content Delivery Network)

   Cloud

   Amazaon  AWS/EC2

autonomous

Hash -> 검색에서 제대로 잘 안 씀(갯수가 많아지면 충돌이 발생함)

R-tree index - 지리정보 관련 등등

쿼리 실행 계획 p.264

EXPLAIN - 쿼리 앞에 입력 하면 쿼리 계획 출력

비절차적(SQL)

이 일차 오후

Analyze

Best practice / rule of thumb

• Index 유무

   

   JOIN / Group by / Subquery

• cost / logic

   Optimize(적절한 수준)

모델링

   대응수 (Mapping Cardinality)

• 1:1, 1:N, M:N

Queue(buffer)

• 속도 차이가 나는 곳에서 갭을 메꾸기 위해 주로 사용

• 한 번에 보낼 수 있는 양이 제한

정규식(regular expression) : regexp

[abc]

‘=’ vs like

• ‘=’  : 매칭 되는 조건의 레코드를 찾으면 끝

• like : 매칭 되는 조건의 레코드를 다 찾아야 끝

between < in(or) : between 보다 in 을 써라.

널함수(Null funtion)

   NVL(oracle), ifnull(mysql)

Paging

   limit으로 페이징하면 느림

   sample<= 181 or smaple >=200

charset vs collation

• ascii(iso8859-1) / euc-kr(ksc5601) / utf-8(recommended) / unicode / ms949(ms)

• ascii 1자 1바이트

• 유니코드 1자 2바이트(3바이트)

• utf8 1자 1바이트~4바이트

정렬(collation)

   대소문자 구분 여부

• case sensitive(CS) vs case insensitive(CI)

검색엔진

apache lucene(루신) -> apache Hadoop 더그커팅

파티셔닝(DB 내부) / 샤딩(DB 외부)

   Mysql  Partitioning(Mysql 5.0이후)

   Mission Critical System

   Transaction
- middleware(BEA Tuxedo) / WAS(Weblogic)

- TP(Transaction Processing) monitor

샤딩 플랫폼

• Join이 어려움 -> 역정규화 필요
  Join은 Table 단위 연산

• 인덱스 분리

• Write성능을 높인다. (N배로 증가)

• 게임서버에서 주로사용

• FK를 쓰지 않음

FK의 개념 / 용도

• 레코드 추가 / 삭제 순서가 생김

• 참조무결성(Referential Integrity)
  + 외래키에 해당하는 원 레코드 존재해야 함
  + Null을 허용하는 경우 VS 안하는 경우

• 비용이 많이 든다

• FK 제약 조건을 삭제함

참고 : http://bcho.tistory.com/670

DNS RR(Round robin)

Static vs Dynamic(L4/L7 switch)

L4 : TCP/UDP port

HTTP/Multimedia

Explain partitions - 파티션관련 분석 추가

처음부터 파티션을 생성 vs 파티션 추가 / 변경 / 삭제

정렬

버블소트 -> 힙소트 / 퀵소트 0(NlogN)

0(1) < 0(logN) < 0(N) < 0(NlogN) < 0(N^2) < 0(N#3) ……

삼 일차 오전

데이터량이 많아지면 정렬이 쉽지 않음

   TopN

   Min/Max -> 0(N)

   SortedList(PriorityQueue in java) : 데이터를 넣으면 자동으로 정렬

Partitioning -> Sharding

   subpartitioning

ENUM -> List Partition

Hashing

   Modular(mod, %)

   hashing function -> 찾아보기

B-tree(쪼개서 찾고, 쪼개서 찾고)

hashing funtion(한번에 찾고, 문제 : 충돌 현상)->보안 : 원본 조작여부

   ISDN : 물리적 망하나로 모든 서비스를 다하자.

• Integrated Service(ATM : Telephone + Data)

• RSVP, Diffserv

• TPS(Triple Play Service)

Silver bullet

Legacy(은행 : 기간시스템)

Conscensus(합의)

Autonomous(자동)

Explain partitions

1-tier / 2-tier / 3-tier/….../n-tier

• 1 : C/S(Sun Microsystem) Downsizing
     TCO(Total cost of ownership)

• 2 : Web Browser / Web Server / DB Client - DB Server
     Web Architecture

• 3 : WB-WS-WAS / Middleware / Framework - DB Server
     Transaction / Concurrency Control (Thread Synchronizaion) / Load Balancing / Shard / Replication
  
  

• Scale-Up / Scale -Down(IT 기술 - reference site)

개발자 VS DBA(Trigger / Stored Procedure)

   Business Logic

   개발자 -> DB : DataStore

   

Dynamic SQL vs Static SQL

   Dynamic SQL : Interpreter

   Static SQL    : Precompiler

Migration

Datavase 접속 API(CRUD)

1. JDBC

2. DataSource(Connection Pooling)

3. Hivernate(ORM)
   ORM : Django...

4. MyBatis(SQLMapper)

MySql Connector / J

1. Driver Loading(Class.forName)

2. DB 접속(DriverManager.getConnection())
   - Connection 생성(접속됨)

3. Statement 생성(conn.createStatement()))

4. SQL 실행 : (R)executeQuery / (CUD)excuteUpdate
   ResulSet으로 리턴

삼 일차 오후

   Statement / PreparedStatement(recommended) / CallableStatement

문자열 escape 처리(PreparedStatement : 내장 / Statement : 따로 처리)

p.777

RelicationDriver

conn.setReadOnly(false);

on-Demand

• close 후 connect

Resource Pooling

   Thread, Process, memory

   Connection Pooling

• 처음부터 connection을 모두 생성함

• start / stop이 시간이 걸림

• memory가 커야함

추상화 / IoC-DI(dependency injection)

-> inter face(java)
        IoC : Inversion of Control(제어 역전)

- Framework

Object-Oriented

   source code level reuse

Component

   Binary level reuse

   CBD : component base  

   

Service oriented Architecture(SOA)

   WebService(WS)

MVC(Model-View-Controller)

   MultiView

   Model : Data를 가지는 클래스

• Object(Class)-Record(Table)

• new Object => insert(SQL)

• delete object => delete(SQL)

• ORM(object - Relational Mapping)

• java : Hibernate

• Table-Class로 맵핑

• 스키마 변경이 클래스 계층 구조의 변경

SQLMapper

   MyBatis

BPR(Business Process Reengineering)

CoC(Convention over Configuration) : 기계친화적

   ORM(Class명 - Table명)

• Sample(Class Name) - samples(Table Name)

• PK : ID(integer, auto_increment)

• FK : TableName_id(integer)

제 2 정규화

   각 컬럼들이 PK랑 직접 연관관계를 가지도록 한다.

제 3 정규화

   간접 관계 제외(컬럼끼리의 종속 관계)

p.867, p.884

길이 1/2/3/4 byte

tinyint (255) unsigned / signed

255/65535/1677만/43억

Decimal - 10진 표현을 위한 자료형

Incremental backup(diff식)

• 차등백업

백업 p.970

   mysqldump - global lock이 걸림

   

자동 수행 cron

   shell script -> 백업 / 압축 / 삭제

   python

partitioning 실습

replication 실습

 서버가 두 개

 동일한 스키마 / 데이터

VMWare 네트워크

  NAT->Bridged

MySQL 5.6.x Replication(MySQL 5.6 이중화)

http://kit2013.tistory.com/157

lagging p.995

   Master / Slave

   싱글쓰레드로 master가 slave쪽으로 Replication을 한다

   ver.5.6 : 멀티쓰레드 사용(DB별 쓰레드를 생성)

memcached(cache service)->redis(nosql)