[펌] MySql Storage Engines

출처 : 팡두 님의 네이버 블로그
원문 : Mysql Storage Engine

Mysql Storage Engine

 

plug in storage engine 구조

             Table 별로 각자 다른 Storage Engine을 사용가능

하나의 Storage Engine에서 다른 Storage Engine으로의 변화를 간단한 SQL명령만으로 가능

                           ALTER TABLE mytable ENGINE-MyISAM;

#1. Storage Engin 별 특징

Feature	MyISAM	BDB	Memory	InnoDB	Archive	NDB
Storage Limits	No	No	Yes	64TB	No	Yes
Transactions(commit, rollback ,etc)		√		√		√
Locking granularity	table	page	table	row	row	row
MVCC/Snapshot Read				√	√	√
Geospatial support	√
B-tree indexes	√	√	√	√		√
Hash indexes			√	√		√
Full text serch index	√
Clustered index				√
Data Caches			√	√		√
Index Caches	√		√	√		√
Compressed data	√				√
Encrypted data(via function)	√	√	√	√	√	√
Store cost(space used)	low	low	N/A	high	very low	low
Memory cost	low	low	medium	high	low	high
Bulk Insert Speed	high	high	high	low	very high	high
Cluster database support						√
Replication support	√	√	√	√	√	√
foreign key support				√
Backup/Point-in-time recovery	√	√	√	√	√
Query cache support	√	√	√	√	√	√
Update Statistics for Data Dictionary	√	√	√	√	√	√

 

1. MyISAM

             1.1 기본적인 특징

l        MySQL의 기본 Storage Engine

l        Data 저장에 실제적인 제한이 없슴(논리적-물리적 제한은 있음)

l        Data를 매우 효율적으로 저장

l        빈번한 Data 사용의 부하를 잘 소화함

l        B-tree, R-tree그리고 Full-text Index를 지원

l        특정 Index에 대한 Memory Cache를 지원

l        Data 압축에 대한 옵션을 제공

l        지리적 Data를 지원

l        Table 레벨의 Lock을 지원

l        Transaction을 미 지원

l        Backup 및 특정 시점으로의 복구 지원

 

.frm	Table구조정보(스키마정보)
.myd	Data
.myi	Index 정보

DB는 디렉토리별로 생성/관리된다.
Table은 디렉토리내 파일로 생성/관리되고, 하나의 Table당 3개의 Data파일로 구성된다.

Table을 구성하는 row는 다음과 같이 3가지 형식으로 분류할 수 있다.

** 고정포맷(fixed row format)
컬럼타입으로 varchar, text, blob을 사용하지 않을때.
가능하다면 가급적 고정포맷을 사용하는 것이 좋다.
동적포맷보다 Memory사용이 적고, Index파일크기도 작아진다. 당연히 속도 또한 향상된다.
파일구조의 고정길이레코드가 고정포맷, 가변길이레코드가 동적 포맷이라고 생각해주면 이해하기 쉬울 것이다.

** 동적포맷(dynamic row format)
컬럼타입으로 varchar, text, blob을 사용할때. 주의) varchar(3)보다 작다면 고정포맷이 사용됨
고정포맷에 비해 디스크사용에 있어 효율성을 가지나, 속도는 상대적으로 느리다.
Table에 빈번한 레코드의 수정/삭제가 이루어지면 단편화가 유발하므로 주기적인 optimize table이 요구된다.
text, blob은 별도로 저장되므로, optimize table을 수행할 필요성이 없다.

** 압축포맷(compressed row format)
읽기전용이다.

myisampack명령어로 만들 수 있다.
디스크공간을 적게 차지하므로 CD에 Backup할 때 사용하면 된다.

MyISAM은 동시성제어를 위해 Table단위 Lock킹(table-level locking)을 사용한다.
참고로, 대부분의 상용 DBMS들은 행 단위 Lock킹(row-level locking)을 사용한다.
행 단위 Lock킹이 더 세밀하고 정밀한 제어가 가능한 반면, Table단위 Lock킹은 단순하다.

READ LOCAL lock
query문(select)에서만 사용됨.
갱신작업들을 블록. 다른 query문들은 블록 안됨.
insert문에서 .myd 파일의 끝에 Data를 추가하는 경우에는 블록 되지 않음.

READ, or shared locks
모든 갱신작업들(insert는 모두 적용됨)이 블록 됨. myisamcheck는 이 Lock을 사용.

WRITE, or exclusive locks
insert(몇몇 종류만), update, delete시 사용됨. 다른 모든 읽기작업/쓰기작업이 블럭됨.

Index : key buffer에 캐싱되어 모든 MySQL 스레드들이 공유
Data : OS의 캐싱에 의존.

주의) 캐싱에 대해
InnoDB가 Index/Data 캐싱 모두를 관리하는 것에 비해 MyISAM은, Index만 MySQL서버가 관리하고, Data는 관리하지 않는다.(Data는 OS캐싱에 의존한다는 의미)
InnoDB는 innodb_buffer_pool_size, MyISAM은 key_buffer_size변수를 사용한다.

자주 사용되는 Table들의 .myi파일크기를 합하면 대략적은 Index 캐싱크기를 구할 수 있다.

3가지 Index 사용가능: b-tree, r-tree, full text 트랜잭션 지원 안됨.
mysqldump, mysqlhotcopy로 Backup가능

MyISAM MERGE
Table들을 union으로 묶은 일종의 view.
실제 Data는 기반Table들에 있음.
보통 history Data나 로그를 가지는 Table들에서 사용됨.
오라클의 파티셔닝과 그 개념이 유사.

1.2 적합한 사용처

- 트레픽이 많은 웹사이트

- Data ware house

- 정적인 Table, 로그 Table
- 쓰기작업이 별로 없는 select 위주의 Table.
- current insert기능이 read시에 insert가 가능하게 하므로 로그 Table에 사용될 수 있다.

 

2. InnoDB

2.1 기본적인 특징

l        ACIDTransaction 지원

l        Table space당 64TB Data의 저장을 지원

l        MyISAM보다 Data 저장 비율이 낮음

l        다른 Engine들에 비해 느린 Data 로드 속도

l        MVCC/Snapshot read를 지원

l        B-tree와 clusteredIndex를 지원

l        특정 Data와 Index에 대한 Memory Cache 지원

l        외부키 지원

l        Data 압축옵션을 제공하지 않음

l        row레벨 Lock을 지원하며 여러가지 isolation레벨을 지원

l        자동 에러복구 기능

l        Backup 및 특정시점으로의 복구지원

 

ACID 트랜잭션, multi-versioning, row-level locking, foreign key제약조건 지원됨.
크래쉬후 자동복구 지원
Data와 Index가 모두 저장되는 Tablespace 개념이 사용됨.
오라클의 Tablespace와 같이 여러개의 파일들로 구성될 수 있다.
select는 Lock킹이 필요치 않으며, 갱신작업들은 행단위 Lock킹을 사용.
높은 동시성을 제공하지만, MyISAM에 비해 3배정도의 디스크 사용량을 요구함.
최적의 성능을 위해 많은 Memory가 InnoDB buffer pool에 할당되어야 함.
Cluster된 Primary key로 btreeIndex 사용
commit된 트랜잭션은 redo log에 기록되고, 이는 적정한 시간에 Tablespace에 기록된다.
mysqldump로 Backup가능.

참고>
show variables는 서버변수 값을 파악 시 사용한다. 오라클에서의 show parameter와 유사하다.
mysql> show variables like 'have%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_bdb         | NO    |
| have_crypt       | NO    |
| have_compress    | YES   |
| have_innodb      | YES   |
| have_isam        | NO    |
| have_raid        | NO    |
| have_symlink     | YES   |
| have_openssl     | NO    |
| have_query_cache | YES   |
+------------------+-------+
9 rows in set (0.00 sec)

SQL> show parameter timed
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
timed_os_statistics                  integer     0
timed_statistics                     boolean     TRUE

2.2 적합한 사용처

- Online Transaction을 지원하는 Application

- 민감한 정보를 갖는 Table(회원Table, 돈에 관련된 Table)
- 갱신(읽기/쓰기) 위주의 트랜잭션이 요구되는 Table.
- Index가 많이 걸린 대량의 Table.

3. MEMORY(HEAP)
모든 Data들은 Memory에 저장됨.
다른 Table타입들에 비해 속도가 월등히 빠르지만, 서버가 셧다운 시 Data는 모두 소실된다.
동등조건( ex) where a=10) 검색에 HASH기반 검색을 제공한다.
이는 범위 검색 시 Index가 사용되지 못한다는 것을 의미한다. varchar, blob, text컬럼 사용 못 함.
4.1버전부터는 tree기반 Index도 사용가능 함.

일시적으로만 사용되는 임시 Table.

 

4. Cluster(NDB)

4.1 기본적인 특징

l        Transaction 지원

l        모든 Data와 Index가 Memory에 존재

l        Table별 Memory 제한은 5.1버전부터 사라짐

l        매우 빠른 Data 로드 속도

l        MVCC/Snapshot read지원

l        B-tree Index 지원

l        Primary key 사용시 최상의 속도를 나타냄

l        99.999% uptime을 제공

l        Cluster간 어떤것도 공유하지 않는 구조(shared Nothing)

l        SQL API와 함께 고속의 접근을 위한 API제공

l        Online Backup과 특정 시점으로의 복구 지원

 

4.2적합한 사용처

고가용성이 반드시 필요한 Application

고속의 Data/key look up이 필요한 Application

 

5. Archive

5.1 기본적인 특징

l        5.0에 새롭게 도입

l        자동적인 Data 압축을 지원

l        다른 Storage Engine 대비 80%의 저장공간 절약

l        실제적인 저장용량의 제한 없슴

l        가장 빠른 Data 로드 속도

l        MVCC/Snapshot read를 제공

l        Index 미지원

l        빠른 Insert 속도를 위해 특별한 입력 버퍼를 제공

l        Insert와 select만을 지원

l        row레벨 Lock을 지원

l        Backup 및 특정 시점으로의 복구 지원

Data베이스에는 빈번하게 사용되는 Data뿐만 아니라 의사 결정이나 통계에 사용하기 위해 계속 누적되는 Data도 많은 부분 존재한다. 이런 경우 Data베이스의 용량은 필연적으로 계속해서 증가하기 마련이고 이러한 Data가 차지하는 공간을 줄이고 효율적으로 사용하기 위해 도입된 것이 바로 Achive Storage Engine이다.

 

MySQL에서는 그 동안 빈번히 사용되지 않는 대용량 Data를 처리하기 위해 압축 MyISAM Table이라는 것을 사용했다. Achive Storage Engine이 압축 MyISAM Table에 비해 어떤 장점이 있는지 살펴보자.

 

• 압축 MyISAM Table은 MyISAM Table을 압축해 보관하는 것으로써 반드시 Table이 오프라인 상태여야 했지만 Achive Table은 Online 상태에서 모든 작업을 할 수 있다.

• MyISAM Table을 압축하기 위해서는 DBA가 운영체제 상에서 myisampack이라는 유틸리티를 실행시켜야 했지만 Achive Table은 MySQL 클라이언트 상에서 MySQL SQL 커맨드로써 가능하다.

• 압축 MyISAM Table은 오직 SELECT만 가능하였지만 Achive Table은 SELECT와 INSERT가 모두 가능하며 끊김 없는 읽기 메커니즘을 통해 쓰는 동안 읽기 작업이 중단되지 않는다.

• Achive Table은 일반 MyISAM Table에 비해 75%나 용량이 감소하며 압축 MyISAM Table에 비해서도 7% 이상 용량이 작다.

 

5.2 적합한 사용처

해를 두고 계속되는 Data를 위한 Data ware house

Data 저장 Application

Data 검사

 

6. Federated

6.1 기본적인 특징

l        5.0에 새롭게 도입됨

l        원격의 물리적 Data 베이스에 대한 논리적 Data 베이스를 생성

l        하나의 Data 베이스에서 다른 타겟 오브젝트로의 ‘포인터’역할

l        원격 Data 접근을 위한 특별한 미들웨어가 필요하지 않음

l        실행 속도는 네트워크 요소에 따라 좌우됨

l        실제 Data 베이스의 Engine 특성에 따라 처리됨

l        Table 정의를 통한 SSL보안 처리

l        모든 SQL 오퍼레이션 지원( as per target object)

 

6.2 적합한 사용처

분산 Data 베이스 환경

Federated Storage Engine은 오라클의 DB Link와 같은 기능의 Storage Engine으로써 원격 서버에 있는 Table을 로컬 Table과 같이 사용할 수 있도록 한다. 이를 통해 여러 대의 MySQL 서버를 용도에 따라 구분하고 필요한 경우에는 서로 참조해 사용할 수 있다.

 

Federated Storage Engine의 사용은 매우 간단한 작업만으로 가능하다. 두 대의 MySQL 서버를 사용한다고 가정하고 어떻게 Federated Storage Engine을 사용할 수 있는지 알아보자. 먼저 실제로 Table이 생성되고 Data가 저장될 MySQL 서버에 다음과 같이 Table을 만들었다.

 

그리고 이 Table은 원격에서 사용하는 서버에 다음과 같이 Federated Storage Engine 기반의 Table을 만들어서 바로 사용할 수 있다.

 

 

7. 기타 StorageEngine

7.1 기본적인 특징

l        RAM에 상주하는 Memory Table; Data가 셧다운시 사라짐

l        Memory Table은 B-tree와 Hash Index 둘다 지원함

l        BDB Table은 Commit/rollback Transaction 지원을 제공

l        Merge Table은 기본적으로 MyISAM Table의 모음

l        Merge Table은 Data 파티셔닝의 한 방법

l        커스텀 Storage Engine 역시 Mysql에 사용가능

 

7.2 적합한 사용처

l        Memory: Data 오브젝트의 빠른 검색

l        BDB : Online Transaction 프로세싱

l        Merge : 분할된 Data를 가진 대용량 Data 베이스

l        커스텀 : 특별 Application 상황

로깅이나 검색에서는 MyISAM

등록정보나 배너시스템에서는 InnoDB
임시Table, 뉴스의 헤드라인, 로드가 많은 페이지의 Data에 대해선 heap을 사용한다.