mysql storage engine - InnoDB

InnoDB (이노데비)는 MySQL 를위한 데이터베이스 엔진 이며, MySQL AB 가 배포하고있는 모든 바이너리 표준 탑재되고있다. MySQL로 사용할 수있는 다른 데이터베이스 엔진에 대한 개선 사항으로PostgreSQL 을 닮은 ACID 호환 트랜잭션 에 대응하고있는 것이있다. 또한 외래 키 도 지원하고있다. (이것을 선언적 참조 무결성의)

2005 년 10 월 Innobase 사가 오라클 사에 매수 후, InnoDB는 오라클의 제품이되었다. ^[1] 이 소프트웨어는 듀얼 라이센스 이며, GNU General Public License 하에서 유통되고 있지만, InnoDB를 독점 소프트웨어와 융합시키고 싶은 단체에 대한 라이센스가 판매되는 경우도있다. ^[2]

목차   [ 숨기기 ]  1 MyISAM의 비교 2 파생 소프트웨어 3 참고 문헌 4 관련 항목 5 외부 링크

MyISAM의 비교 [ 편집 ]

파생 소프트웨어 [ 편집 ]

OurDelta.org에서는 Google, Percona 회사 등에 의한 InnoDB에 패치를 포함한 MySQL 바이너리를 배포하고있다. ^[3]

2008 년 12 월 26 일 Percona 사는 InnoDB 분기이다 XtraDB을 발표했다. ^[4]

참고 문헌 [ 편집 ]

1. ^ " Oracle Announces the Acquisition of Open Source Software Company, Innobase " Oracle . 2008 년 7 월 31 일 보기.
2. ^ " Licensing MySQL and InnoDB "InnoDB.com. 2008 년 7 월 31 일 보기.
3. ^ " OurDelta - Builds for MySQL " 2008 년 12 월 26 일 보기.
4. ^ " Announcing Percona XtraDB Storage Engine : a Drop-in Replacement for Standard InnoDB " 2008 년 12 월 26 일 보기.

관련 항목 [ 편집 ]

• Falcon
• ISAM
• MyISAM
• Maria (storage engine)

외부 링크 [ 편집 ]

• 공식 사이트
• InnoDB and other table types in M​​ySQL
• InnoDB in the MySQL manual

=============================================================================================





최근 개발을 하면서 처음 듣게된 개념이 있다.
바로 MySQL DB의 "스토리지엔진"이라는 개념과 innoDB, MyISAM 개념이다.

간단하게 말하면

스토리지엔진은 DB에서 데이터를 어떠한 방식으로 저장하고 접근할 것인지에 대한 기능을 제공한다.
스토리지엔진의 특성에 따라 데이터 접근이 얼마나 빠른지, 얼마나 안정적인지, 트랜잭션 등의 기능을 제공하는지 등의 차이점이 발생한다

MySQL 스토리지엔진에는 몇가지 타입이 있는데

1. MyISAM
MySQL의 기본 스토리지 엔지으로 데이터 저장에 실제적인 제한이 없고 매우 효율적으로 저장한다. Full-Text 인덱스를 지원하며 특정 인덱스에 대해 메모리 캐쉬를 지원한다. 트랜잭션은 미지원/ 테이블 레벨의 락을 지원 잦은 변경및 삭제에는 좋은 성능이 나오지 못하나 데드락 발생은 예방
2. InnoDB
ACID 트랜잭션을 지원하며, MyISAM보다 데이터 저장비율이 낮고, 데이터 로드 속도가 느리다. 특정 데이터와 인덱스에 대해서 메모리 캐쉬를 지원하며 외부티를 지원한다. 데이터 압축이 불가능하고 자동 에러 복구 기능이 있다. 테이블 레벨이 아닌 ROW 레벨의 락을 지원한다.
3. Cluster (NDB)
트랜잭션을 지원하고 모든 데이터와 인덱스가 메모리에 존재하여 매우 빠른 데이터 로드 속도를 자랑하며 PK 사용시 최상의 속도를 나타낸다.
4. Archive
MySQL 5.0부터 새롭게 도입된 엔진으로 자동적으로 데이터 압축을 지원하며 다른 엔진에 비해 80% 저장공간 절약 효과를 자랑한다. 그리고 가장 빠른 데이터 로드 속도 또한 자랑하지만, INSERT와 SELECT만이 가능하다.
5. Federated
MySQL 5.0부터 새롭게 도입된 엔진으로 물리적 데이터베이스에 대한 논리적 데이터베이스를 생성하여 원격 데이터를 컨트롤 할 수 있다. 실행속도는 네트워크 요소에 따라 좌우되면 테이블 정의를 통한 SSL 보안 처리를 한다. 분산 데이터베이스 환경에 사용한다

출처 : http://www.thegreatgoodplace.com/tt/study/168?category=30

대표적인 엔진타입으로는 위에 설명한 MyISAM 방식과 InnoDB 방식이 있다.
이 두가지를 예를 들어 설명하자면,

MyISAM 방식은 속도가 빠르고, 트랜잭션을 지원하지 않으며,
InnoDB 방식은 속도가 느린대신 트랜젝션을 지원한다

위 두가지 방식의 구체적인 내용 및 장단점을 보고 싶으면....

http://jsp.pe.kr/208

출처 -  http://www.letsmakegame.net/35049 

===============================================================================================

MySQL InnoDB Engine 사용 시 반드시 알아야 할 세 가지

June 10, 2011

by kth MIS팀 성동찬 ( DBA )

 

Overview

MySQL에서 Built-in 제공되는 InnoDB는 DB로 작업을 하시는 분이라면 누구나 한번쯤은 들어본 엔진이라고 생각이 듭니다. 최근 Oracle사가 SUN을 인수한 후 MySQL쪽으로 가장 많이 성능 개선을 하고 있는 부분 또한 InnoDB입니다.

무엇보다 HandlerSocket이나 MySQL 5.6 Memcached Plugin 모두 InnoDB 스토리지 엔진 API과 직접적인 통신을 하여 엄청난 퍼포먼스를 보여주게 됩니다.

이렇게 점차적으로 활용도가 높은 엔진, InnoDBB는 무엇이고 도대체 어떠한 특성을 가지고 있을까요?

여기서 말씀드리고자 하는 특성 세 가지 특성을 먼저 말씀드리겠습니다.

• MySQL Built-in Engine 중 유일하게 트랜잭션을 지원하는 엔진
• Primary Key(PK) 순으로 데이터가 쌓이는 엔진
• Secondary Key는 PK를 Value로 가지고 있는 엔진

자~ 그럼 길~게 풀어보도록 하죠~^^;

MySQL Built-in Engine 중 유일하게 트랜잭션 지원

Innodb가 트랜잭션을 지원하는 엔진이라는 사실은 대부분 아시는 내용이라고 봅니다.

여기에 저는 추가적으로 Row-Level Locking으로 데이터를 처리하는 트랜잭션 지원 엔진 이라고 소개하고 싶네요^^;

Row-Level Locking (행단위 잠금) 이란 무엇일까요? 또다른 Lock으로는  Table-Level Locking이 있습니다.

Lock(잠금)이란 DB 테이블에서 데이터를 변경할 경우, 다른 접속자는 해당 데이터를 변경하지 못하도록 상태를 변경한다는 것을 의미합니다.

화장실의 변기 칸을 예를 들겠습니다. (조금 지저분해도..^^;;)

네 칸 모두 비어 있을 때는 누구나 사용할 수 있는 상태입니다.

이때 한 사람이 들어가서 화장실 문을 잠궈버리면 해당 칸은 다른 사람이 이용하지를 못하죠. 하지만 나머지 세 칸은 여전히 다른 누군가가 사용할 수 있는 상태입니다.

바로 이 경우가 Row-Level Locking과 동일한 경우라고 보시면 되겠네요.

그런데 갑자기 화장실 쪽 파이프 관이 파열되어 물이 단수되는 사고가 발생했습니다. 수리공을 불러서 화장실을 파이프 관을 고쳐야할 순간이 생긴 것이죠.

수리공이 와서 제일 먼저 각 화장실 칸마다 “수리중” 이라는 팻말을 올려놓고 사람들이 사용을 못하게 합니다. 만약 누군가가 그 사실을 모르고 사용을 하게 되면 수리공에게는 참으로 곤혹스러운 사실일테니까요.^__,^

바로 이 경우가 Table-Level Locking과 동일한 경우입니다.

조금 지저분한 얘기를 예로 들기는 했는데, 각각 Lock의 특성은 정확하게 이해하셨죠? ㅎㅎ

다시 DB영역으로 돌아와서 아래 그림을 보시죠.

왼쪽은 Row-Level Locking을 의미하는 것으로 데이터 변경 시 자신이 변경하고자 하는 행에만 Lock을 거는 것을 의미합니다. 바로 MySQL InnoDB처럼요.

반대로 오른쪽은 특정 Row 한 건 변경(Insert/Update/Delete)을 위해 전체 테이블을 모두 Lock상태로 만드는 Table-Level Locking을 의미합니다. MySQL MyISAM 이 대표적이죠.

 

Row-Level Locking을 지원하는 스토리지 엔진은 Innodb와 Archive가 현재 있고, Table-Level Locking은 MyISAM, Memory, CSV 등등 있습니다.

Row-Level Lock과 Table-Level Lock은 각각 장단점이 있겠지만, 동시성 데이터 처리에서는 반드시 Row-Level Lock이 필요하다고 생각이 드네요.

Table-Level Lock은 단 한 건 수정을 하기 위해서 테이블 자체에 아무도 변경을 못하게 합니다. 한 건 데이터 수정이 10초 이상 걸린다고 가정을 해보면, 초당 처리 건 수가 0.1이 될려나요? 10초에 한 건씩 처리가 될테니요.ㅎㅎ 반대로 처리 시간은 0.1초에 불가하나 동시에 100명이 데이터 변경 요청을 하게된다면 이 또한 초당 처리 건수는 0.1이 되겠죠. 헐~

결국 단 한 건의 수정에 걸리는 시간이 오래 걸리거나, 동시에 여러 접속자가 각기 자신의 데이터를 변경하는 이슈가 발생한다면 Table-Level Locking은 커다란 난관에 부딪힐 수 밖에 없습니다.

특히나 몇 백만 건 데이터 테이블에 동시에 여러 접속자가 자신의 데이터를 변경하려 한다면, Table-Level Locking에서는 대다수의 세션들이 Table Lock Wait 상태로 대기하는 결과를 초래합니다.

실제로 MyISAM을 주 스토리지로 사용하는 서비스에서 데이터가 크게 증가함에 따라 DB Lock(Table Lock)이 급증하여 서비스가 정상적으로 진행되지 않는다는 문의를 받은 적도 있습니다. (이 경우 미봉책으로는 InnoDB로 변경을 하거나 그것이 불가하면 파티셔닝을 고려하라고 답변을 한 듯 하네요.^^a)

그렇다고 Table-Level Locking이 무조건 나쁘다는 것이 아닙니다. 복잡함을 버리는 대신 단순하게 데이터를 처리할 수 있으므로, 단순 데이터 처리에는 압도적인 성능을 보이겠죠.

이야기가 조금 길어지기는 했는데, InnoDB는 Row-Level Locking을 지원하는 트랜잭션 엔진으로 동시성을 보장합니다.

그리고 트랜잭션을 지원하는 만큼 ACID를 FULL로 지원하는데 ACID에 관한 설명은 위키피디아의 말을 빌리면 다음과 같습니다. ㅎㅎ

• 원자성(Atomicity)
  트래잭션과 관련된 작업들이 모두 수행되었는지 아니면 모두 실행이 안되었는지를 보장하는 능력이다. 자금 이체는 성공할 수도 실패할 수도 있지만 원자성은 중간 단계까지 실행되고 실패하는 일은 없도록 하는 것이다.
• 일관성(Consistency)
  트랜잭션이 실행을 성공적으로 완료하면 언제나 일관성 있는 데이터베이스 상태로 유지하는 것을 의미한다. 무결성 제약이 모든 계좌는 잔고가 있어야 한다면 이를 위반하는 트랜잭션은 중단된다.
• 고립성(Isolation)
  트랜잭션을 수행 시 다른 트랜잭션의 연산 작업이 끼어들지 못하도록 보장하는 것을 의미한다. 이것은 트랜잭션 밖에 있는 어떤 연산도 중간 단계의 데이터를 볼 수 없음을 의미한다. 은행 관리자는 이체 작업을 하는 도중에 쿼리를 실행하더라도 특정 계좌간 이체하는 양 쪽을 볼 수 없다. 공식적으로 고립성은 트랜잭션 실행내역은 연속적이어야 함을 의미한다. 성능관련 이유로 인해 이 특성은 가장 유연성 있는 제약 조건이다. 자세한 내용은 관련 문서를 참조해야 한다.
• 지속성(Durability)
  성공적으로 수행된 트랜잭션은 영원히 반영되야 함을 의미한다. 시스템 문제, DB 일관성 체크 등을 하더라도 유지되야 함을 의미한다. 전형적으로 모드 트랜잭션은 로그로 남고 시스템 장애 발생 전 상태로 되돌릴 수 있다. 트랜잭션은 로그에 모든 것이 저장된 후에만 commit 상태로 간주될 수 있다.

InnoDB는 MVCC (multiversion concurrency control)을 완벽하게 지원하므로 Isolation Level도 적절하게 지정할 수 있습니다. MVCC란 데이터를 읽는 작업은 기록작업을 절대 방해하지 않고, 기록작업은 읽는 작업을 절대로 방해하지 않는다는 것을 의미합니다. 4가지 Isolation Level은 다음과 같습니다.

• READ UNCOMMITTED
  - 다른 트랜잭션이 Commit 전 상태를 볼 수 있음
• READ-COMMITTED (Oracle Default)
  - Commit된 내역을 읽을 수 있는 상태로, 트랜잭션이 다르더라도 특정 타 트랜잭션이 Commit을 수행하면 해당 데이터를 Read할 수 있음
• REPEATABLE READ (MySQL Default)
  - 트랜잭션이 완료될 때까지 SELECT 문장이 사용하는 모든 데이터에 Shared Lock이 걸리는 레벨로 트랜잭션이 종료될 때까지 다른 사용자가 해당 행을 수정하지 못하며, 여러 번 데이터를 읽어도 같은 값이 유지되도록 함. 다른 트랜잭션에 의한 변경 사항을 볼 수 없고, 변경 사항을 확인하기 위해서는 트랜잭션을 새로 시작해야 함
• SERIALIZABLE
  - 가장 높은 Isolation Level로 트랜잭션이 완료될 때까지 SELECT 문장이 사용하는 모든 데이터에 Shared Lock이 걸리는 레벨로 다른 사용자는 해당 영역에 관한 데이터 변경 뿐만 아니라 입력도 불가함

여기서는 InnoDB가 Row-Level Locking으로 트랜잭션을 완벽하게 지원해주는 엔진이라는 사실만 기억해 주세요^^

Primary Key(PK) 순으로 데이터가 저장

중요한 사실입니다. Oracle과 조금 많이 친하신 분들은 IOT(Index Organized Table)라는 테이블을 들어보셨을 것입니다. IOT와 동일한 개념으로 InnoDB에서는 Cluster Index라고 하는데, Cluster Index가 곧 Primary Key입니다. (Oracle 과 친하신 분들~! Cluster Table과 헷갈리지 마세요^^)

즉 “인덱스 순서”로 데이터가 “정렬되어 디스크에 저장”된다는 것을 의미합니다.

아래 그림을 예로 들겠습니다.
다음과 같이 첫번째,두번째 칼럼(주황색 칼럼)으로 Primary Key가 구성됐다고 가정하면, 아래와 같이 데이터가 디스크에 저장이 됩니다.

그런데 만약 위 그림에서 Primary Key값이 (21, 30)인 데이터를 넣는다고 하면 어떻게 될까요?

헐~ 데이터 1 건을 Insert 하기 위해서 4 건의 데이터가 한칸 씩 물리적으로 이동하는 결과가 초래되었군요.

어느 경우나 마찬가지겠지만 데이터가 적을 때는 문제가 되지 않습니다.

그러나!! 데이터 사이즈가 대용량 데이터로 비대해지게 된다면 위와 같은 현상은 큰 문제가 됩니다.
특히 Primary Key값이 Random한 UUID값 필드로 설정된다면..
심각한 DISK IO현상과 함께 DB가 불능인 상태로 빠질 수 있습니다.. OTL

하단 표는 InnoDB에서 Random PK와 Sequectial PK(Auto_increment사용)으로 각 수행마다 200만 데이터를 각각  Insert 테스트 결과입니다.

5회 시도 시 24분 소요.. InnoDB에서 Primary Key 선택이 얼마나 중요한지 아시겠죠?

Oracle에서는 rowid라는 놈이 있고 rowid에는 데이터가 존재하는 물리적인 정보를 모두 가지고 있습니다. 그래서 당연히 rowid로 접근하는 조회는 빠르겠죠.

이에 반해 MySQL의 InnoDB에서는 Primary Key가 rowid역할을 대신 수행합니다. PK순으로 데이터가 정렬되어 저장되기 때문에 PK로 접근하는 조회는 Oracle의 rowid처럼 무척이나 빠르겠죠.

그렇다면 Primary Key를 저정하지 않았다면 어떻게 될까요? 과연 위 같은 문제가 없을까요?

• 인덱스 중 Unique 속성이 있는 Key를 Primary Key로 대체 사용
• PK선언도 없고, Unique Key도 없으면 내부적으로 6Byte PK를 생성하여 사용

결과적으로 모든 InnoDB는 PK를 선언하지 않아도 내부적으로는 PK를 가지고 있다는 말이겠죠.

단, 두번째 경우는 타 필드에 NULL옵션이 포함된 경우는 상당히 위험합니다.  자세한 내용은 MySQL 이중화 상태에서 Replication Fail Bug 해결법 을 읽어보세요^^

Secondary Key는 PK를 Value로 가짐

Index란 무엇을까요?  네.. 해석하면 말 그대로 색인이죠^^;;

그러면 색인을 왜 쓸까요? 네.. 당연히 원하는 내용을 빠르게 찾기 위함입니다.

일반적으로 책들(특히나 기술 서적)은 모두 맨 뒤에 색인을 가지고 있고, 필요한 내용이 있으면 색인을 통하여 바로 한번에 페이지를 찾을 수 있습니다.

전체 책에 비해서 색인이 차지하는 페이지 수는 극히 적습니다. 보통 10~20Page 정도가 일반적이라고 생각이 되는데요..

그런데 만약!! 색인 장수가 책 내용의 페이지 수 보다 훨씬 더 많다면 어떨까요?

원하는 내용을 찾는데 오히려 손가락의 움직임이 훨씬 더 분주해 지겠죠.

특히나 원하는 내용을 찾기 위해서 색인 안에 있는 내용들을 쭉 검토해야 하는 노고가 필요합니다. T.T

그렇다면 어떤 경우에 색인 총 페이지 수가 늘어날까요?

• 너무 자세하게 내용들을 색인하는 경우 (이건 너무나도 당연한 얘기겠지요)
• 색인이 가르키고 있는 내용이 상당히 긴 경우 (어떤 경우인지는 아래서 설명 드릴께요^^)

이제 MySQL로 돌아와서 반대로 생각해보도록 해보죠^^

<너무 자세하게 인덱스를 한 경우>

만약 너무 많은 인덱스를 생성하게 된다면, 인덱스 크기가 아무리 작아도 결과적으로 전체 인덱스 사이즈는 비대해집니다.

이와 반대로 너무 자세하게 인덱스를 구성(여러 필드를 묶어서 생성)해도, 그것 또한 인덱스 사이즈를 비대하게 만들겠죠.

물론 인덱스 두 개 만들 것을 복합 인덱스 하나로 처리 가능하다면 당연히 복합 인덱스 만드는 것이 맞겠지요.

즉, 너무 자세하고 다양하게 인덱스를 생성하면 곧 Data 처리에 비효율을 만듭니다.

특히나 DML(INSERT/UPDATE/DELETE) 효율이 상당히 떨어지게 되는데, 일반 도서의 색인과는 다르게 DB에서는 인덱스 칼럼 데이터 변경 시마다 인덱스가 조금씩 변경되기 때문이죠.

<색인이 가르키고 있는 내용이 상당히 긴 경우>

저는 이 자리에서 이 내용을 말씀드리고 싶네요.

가시적이지 않고 내부적으로 보이는 내용인지라, 아무리 필수 인덱스로만 적절하게 인덱스를 구성했다고 하더라도 인덱스 비효율이 발생할 수 있습니다.

앞서 말씀드렸다시피, InnoDB에서는 “Primary Key 순으로 데이터가 정렬되어 물리적으로 저장”이 됩니다.

PK는 곧 물리적인 위치, 서적에서 언급하면 페이지 번호라고 보시면 됩니다.

그리고 PK를 제외한 타 인덱스들은 데이터의 페이지 번호 즉 PK를 Value로 가지고 있습니다.

즉 이 말은 곧 PK가 커지면 인덱스 사이즈도 동시에 커진다는 것을 의미하죠.

단순한 이미지이기는 하지만, “인덱스 구조”이미지를 보시면 각 인덱스 항목 별 Primary Key를 가지고 있는 것을 보실 수 있습니다.

그러나 다음과 같은 상황을 한번 상상해 보세요.

<테이블 구조>

1) Primary Key 를 <COL1, COL2, COL3>으로 선언

2) index01<COL4, COL5>, index02<COL5>, index03<COL6> 등 총 세 개 인덱스 추가

인덱스 요소 별로 PK를 가지고 있다고 말씀드린 것 처럼, 각각의 데이터 ROW를 인덱싱 하기 위해서는 각각 INT데이터 사이즈 4Byte외 150Byte가 추가적으로 들어가게 됩니다.

그런 인덱스가 3개 있다면.. 곧 데이터 1 Row당 인덱싱되는 칼럼 크기 외 450Byte의 데이터가 더 들어간다는 말이 되겠죠.

인덱스 유지를 위해서 엄청난 비용이 발생한다는 사실을 아시겠죠?

특히나 COL1이 UUID로 이루어져 있고, 점차적으로 데이터 사이즈가 꾸준히 늘어난다면..

일정 시점부터 급격하게 성능이 저하되는 것을 아시게 될 것입니다.

아래는 특정 테이블 데이터/인덱스 사이즈를 조사해본 내용입니다.(테이블 이름은 지웠습니다.)

인덱스 크기가 데이터 크기보다 큰 기이한 현상을 확인하실 수 있겠죠? ^^

Primary Key 의 비효율로 인해 트랜잭션을 포기하더라도 MyISAM engine으로 전환하면 되지 않냐라고 제게 물으신다면.. 대답은 NO!!입니다.

물론 MyISAM에서는 인덱스 사이즈를 최대한 줄이기 위해서 내부적으로 Prefix Compressed Index를 사용하고, PK와 그타 인덱스 간 관계가 그다지 밀접하지 않지만, 위같은 문제가 붉어져 나오는 시점은 이미 데이터가 어느정도 존재하는 상태이고, 동시성 문제가 분명 대두될 것입니다.

단, 만약 동시적으로 데이터 처리도 없고, 단일 프로세스에서만 데이터 작업이 이루어진다면 몰라도, 차라리 auto_increment 가 적용된 필드 하나를 추가하고 해당 칼럼을 PK로 사용하는 것이 훨씬 성능 향상에 도움이 될 것이라고 생각이 듭니다.

끝 맺으며..

무조건 InnoDB를 택해야 하냐고 제게 물으신다면 그러실 필요는 없다고 말씀드리고 싶습니다. MySQL에서는 자체적으로 훌륭한 스토리지 엔진을 제공합니다.

데이터 변경이 크게 없고 단순하게 READ위주의 테이블이라면, 어느정도 대용량일지라도 MyISAM이 더 좋은 대안일 수도 있습니다.

로그 데이터와 같이 데이터가 쌓이기만 하고 변경 삭제가 필요없다면 오히려 Archive Storage Engine을 고려해보시는 것도 괜찮겠구요.

추후에 각 엔진 별 특성을 비교해서 말씀드릴께요.^^

모든 것에는 정답은 없는 것 같습니다.

그냥~ 목적에 맞게 “적절하게” 쓰시는게 가장 좋지 않을까요? ^^

많은 이야기와 사례를 담지 못해서 조금은 아쉽네요.

다음에는 좋은 내용으로 찾아뵐께요 ㅎㅎ(연재 시작 공지? ㅋㅋ)

출처 -  http://dev.paran.com/2011/06/10/mysql-innodb-engine-3-tips-you-must-know/