Mysql 스토리지 엔진

프로젝트 당시, transaction이 안됐던게 짜증이나서 Mysql 관련책을 샀습니다. mysql 더 자세히 알고 싶어서 유명한 real Mysql 책을 읽으면서 하나하나 정리하고자합니다.

아키텍쳐

mysql 서버는 mysql 엔진과 스토리지 엔진으로 구분할 수 있습니다.

MySQL 엔진

MySQL엔진은 접속 및 쿼리 요청을 처리하는 커넥션 핸들러, SQL 파서, 전처리기, 옵티마이저가 중심을 이룹니다.

스토리지 엔진

실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분을 전담합니다.

---

1. 스토리지 엔진

(1) MySQL 스토리지 엔진 종류

• InnoDB 엔진
• MyISAM 엔진
• Memory 엔진
• Archive 엔진
• CSV 엔진
• Federated 엔진

(2) InnoDB

A. 특징

• primary key 로 클러스터링
  InnoDB은 모든 테이블은 primary key를 기준으로 클러스터링되어 저장합니다. 그러다보니 다른 보조 인덱스에 비해서 비중이 높게 설정됩니다.

• 외래 키 지원
  Inno DB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 컬럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모 테이블과 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하고 잠금이 여러 테이블로 전파되므로, 데드락이 발생할 때가 많아서 개발할 때 외래 키의 존재에 주의하는게 좋습니다.

• MVCC(Multi Version Concurrency Control)
  MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기 제공에 있습니다. InnoDb는 Undo log를 이용해서 이 기능을 구현합니다.

• 잠금 없는 일관된 읽기(Non-Locking Consistent Read)
  데이터가 업데이트시 업데이트 하기전의 데이터와 업데이트 후의 데이터로 나누어지게됩니다. 업데이트 과정 가운데 데이터를 불러오기를 한다면, 업데이터하기 전의 데이터를 불러옵니다. 이게 '잠금없는 일관된 읽기'입니다.

• 자동 데드락 감지
  Inno DB에서 잠금이 교착상태에 빠지지않게 하기위해 잠금 대기 목록을 그래프(Wait-for- List) 형태로 관리합니다.

• 자동화된 장애 복구
  Inno DB는 Mysql 서버가 시작될 떄 완료되지 못한 트랜잭션이나 기스크에 빌무나 기록된 데이터 페이지 등에 대한 일련의 복구 작업을 자동으로 진행합니다.

B. InnoDB 버퍼 풀

가장 색심적인 부분으로, 데스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간입니다.

• 버퍼 풀의 크기 설정 가능

• 버퍼 풀의 구조
  Inno DB 엔진은 버퍼 풀이라는 메모리공간을 조각으로 쪼개어, 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장합니다.
  이 페이지 크기 조각을 관리하기 위해 LRU(Least Recently User), Flush, Free 리스트 자료구조를 관리합니다.

Flush 리스트
플러시 리스트에서 오래전에 변경된 데이터 페이지 순서대로 디스크에 동기화하는 작업을 수행합니다.

LRU 리스트 플러시
사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들 읽어올 공간을 만듭니다

InnoDB에서 데이터 찾는 과정

1. 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사

   • InnoDB adative 해시 인덱스를 이용해 페이지 검색
   • 해당 테이블의 인덱스(B-Tree)를 이용해 버퍼 풀에서 페이지르 ㄹ검색
   • 버퍼 풀에 이미 데이터 페이지가 있다면 해당 페이지의 포인터를 MRU 방향으로 승급

2. 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가합니다.

3. 버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동

4. 버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했었는지에 따라 나이가 부여되며, 버퍼 풀에 상주하는 동안 쿼리에서 오랫동안 사용되지 않았다면 데이터 페이지에서 부여된 나이가 오래되고 결국 해당 페이지는 제거됩니다.

5. 필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가

• 버퍼 풀과 리두 로그
  

InnoDB 엔진에서 리두 로그는 1개 이상의 코정 크기 파일을 연결해서 순환고리처럼 사용합니다. 거기서 재사용 가능한 공간과 당장 재사용 불가능한 공간(활성 리두 로그)을 나누어 관리합니다.

• 버퍼 풀의 적재 내용 확인
  information_schema.innodb_cached_indexes 로 확인할 수 있습니다.

C. Double Write Buffer

InnoDB 스토리지 엔진릐 리두 로그는 리두 공간의 낭비를 막기위해 페이지의 변경된 내용만을 기록합니다. 이때 더티페이지를 디스크 파일로 플러시할 때 일부만 기록하는 문제가 생길 수 있습니다. 이러한 문제를 막기위해서 Double-write 기법을 사용합니다.

Double-write는 데이터 변경 내용을 기록하기 전에 더티 페이지를 Double Write Buffer에 기록합니다. 그리고 랜덤으로 쓰기를 싱행합니다. 실제 데이터 파일에 더티 페이지가 기록되면 더 이상 필요가 없어집니다.

D. 언두 로그

언두 로그는 데이터가 변경되기 전의 데이터를 보관하는 곳입니다. 이는 트랜잭션시 롤백 대비용과 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공하기위해서 입니다.

언두로그 레코드를 확인하고 싶다면 information_schema.innodb_metrics

• 언두 테이블 스페이스 관리
  언두 로그가 저장되는 공간을 언두 테이블 스페이스라고 합니다.
  CREATE UNDO TABLESPACE나 DROP TABLESPACE 로 새로운 언두 스페이스를 동적으로 추가하고 삭제할 수 있게 개선되었습니다.

E. 체인지 버퍼

InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만, 디스크로부터 읽어와서 업데이트 해야한다면 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향샹시키는데, 이때 임시 메모리 공간을 '체인지 버퍼'라고 합니다.

F. 리두 로그 및 로그 버퍼

리두 로그는 ACID 중에서 D와 밀접하게 연관되어있습니다. 서버가 비정상적으로 종료되었을 때 데이터 파일에 기록되지 못한 데이터를 잃지않게 해주는 안전장치입니다.

• 리두 로그 아카이빙

• 리두 로그 활성화 및 비활성화

G.어댑티브 해시 인덱스

InnoDb 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스입니다.
어댑티브 해시는 B-Tree 검색 시간을 줄여주기 위해 도입된 기능입니다.

하지만 어뎁티브 해시가 성능 향상에 크게 도입되지않는 경우가 있습니다.
- 디스크 읽기가 많은 경우
- 특정 패턴의 쿼리가 많은 경우(조인 or Like검색)
- 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

H. InnoDB와 MyISAM, Memory 스토리지 엔진 비교

(3) MyISAM 스토리지 엔진

A. 키 캐시

MyISAM 키 캐시는 인덱스만을 대상으로 작동합니다. 인덱스의 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할을 합니다

B. 데이터 파일과 프라이머리 키(인덱스) 구조

InnoDB 스토리지 엔진을 사용하는 테이블은 프라이머리 키에 의해서 클러스터링되어 저장되면 반면,
MyISAM 테이블은 프라이머리 키에 의한 클러스터링 없이 데이터 파일이 힙 공간처럼 활용됩니다.

C. MySQL 로그 파일

• 에러 로그 파일
  에러 로그 파일의 위치는 MySQL 설정 파일(my.cnf)에서 log_error라는 이름의 파라미터로 정의된 경로에 생성됩니다. 다음의 에러 메시지들을 볼 수 있습니다.
  - MySQL이 시작하는 과정과 관련돈 정보성 및 에러 메세지
  - 마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB 트랜잭션 복구 메세지
  - 쿼리 처리 도중에 발생하는 문제에 대한 에러 메세지
  - 비정상적으로 종료된 커넥션 메세지
  - InnoDB의 모니터링 또는 상태 조회명령의 결과 메세지
  - MySQL의 종료 메세지

• 제네럴 쿼리 로그 파일
  서버에서 실행되는 쿼리로 어떤 것들이 있는지 나타냅니다.
  명령어는 SHOW GLOBAL VARIABLES LIKE 'general_log_file' 입니다

• 슬로우 쿼리 로그
  서비스에서 사용되는 쿼리 중에서 어떤 쿼리가 문제인지 판단하는데 이 슬로우 쿼리 로그가 도움이 됩니다.
  슬로그 쿼리 로그에 기록되는 쿼리는 정상적으로 실행이 완료돼었지만 실행하는데 걸린시간이 long_query_time에 정의 된 시간보다 더 걸린 쿼리입니다.

끝

realMYSQL 책을 통해 공부하면서 이걸 어떻게 정리하지 하고있는데 어찌저찌 한번 겨우 본거 같습니다. 보고나서는.. 내가 사용했던 transaction 뒤편에 어떤 엔진이 작동하고 있었구나를 볼 수 있었고, commit과 rollback이 어떻게 이루어지는지 볼 수 있었습니다.
앞에 내용은 짤랐지만, 나중에 다시 보도록하겠습니다

출처

https://nomadlee.com/mysql-%EC%8A%A4%ED%86%A0%EB%A6%AC%EC%A7%80-%EC%97%94%EC%A7%84-%EC%A2%85%EB%A5%98-%EB%B0%8F-%ED%8A%B9%EC%A7%95/#MySQL