MySQL 엔진 아키텍처 & InnoDB 스토리지 엔진 아키텍처 정리 (4.1 ~ 4.2)

Real MySQL 4.1 ~ 4.2 학습 후 정리한 내용입니다.

 

4.1 MySQL 엔진 아키텍처

4.1.1 MySQL 의 전체 구조

- MySQL 은 MySQL 엔진과 스토리지 엔진으로 구분한다.

• MySQL 엔진 - 커넥션 핸들러, SQL 파서, 전처리기, 옵티마이저 가 중심을 이룬다.
  • 커넥션 핸들러
    • 클라이언트로부터의 접속 및 쿼리 요청을 처리, 스토리지 엔진에게 쓰기 또는 읽기를 요청
    • 'SHOW GLOBAL STATUS LIKE Handler%'; 명령으로 데이터 작업의 개수를 알 수 있다.
  • SQL파서 - SQL문장을 쪼개서 문법 오류를 체크하고 Parse Tree 를 만듬
  • 전처리기 - Parse Tree 를 기반으로 SQL 문장구조에 문제가 없는지 체크
  • 옵티마이저 - 쿼리의 최적화된 실행을 계획
• 스토리지 엔진 - 실제 데이터를 디스크에 저장하거나 읽어온다.

- MySQL 서버에서 MySQL 엔진은 1개지만, 스토리지 엔진은 여러 개를 동시에 사용할 수 있다.

• CREATE TABLE table_name (column_name, type) ENGINE=INNODB;

- 스토리지 엔진은 디스크I/O를 줄이기 위해 캐싱 기능을 내장하고 있다.

• MyISAM 스토리지 엔진 - 키 캐시, InnoDB 스토리지 엔진 - InnoDB 버퍼 풀 기능을 내장하고 있다.

 

4.1.2 MySQL 스레딩 구조

MySQL 서버는 프로세스 기반이 아닌 스레드 기반으로 작동한다.

크게 포그라운드 스레드와 백그라운드 스레드로 구분한다.

 

- 포그라운드 스레드

• 최소 MySQL서버에 접속된 클라이언트 수 만큼 존재
• 클라이언트가 커넥션을 종료하면 해당 커넥션을 담당하는 스레드는 스레드 캐시로 되돌아간다.
  • 스레드 캐시에 유지할 수 있는 최대 스레드 개수는 thread_cache_size 시스템 변수로 설정.
  • 만약, 이 값을 넘어가면 커넥션이 종료된 스레드를 캐시에 넣지않고 종료시킨다.
• 데이터를 MySQL의 버퍼나 캐시에서 가져오며, 이 곳에 데이터가 없는 경우 디스크에서 데이터를 읽어온다.

- 백그라운드 스레드

• 주로 버퍼나 캐시에 있는 데이터를 디스크에 쓰는 작업을 담당한다.
  • 로그를 디스크로 쓰기, InnoDB 버퍼 풀의 데이터를 디스크에 쓰기, 잠금/데드락을 모니터링하기

* 스토리지 엔진 종류에 따른 버퍼링 기능

InnoDB - 버퍼링 기능 탑재되서 쓰기 작업을 일괄 처리함 -> 디스크의 데이터 변경이 될 때까지 기다리지 않음

MyISAM - 버퍼링 기능이 없어서 쓰기 작업 건당 디스크의 데이터 변경이 될 때까지 기다려야 한다. 

 

4.1.3 메모리 할당 및 구조

MySQL의 메모리 공간은 글로벌 메모리 영역과 로컬 메모리 영역으로 구분

 

- 글로벌 메모리 영역

• 이 영역에 해당하는 모든 메모리 공간은 OS로부터 할당된다.
• 생성된 글로벌 영역이 N개 이더라도 모든 스레드에 의해 공유된다.
• 테이블 캐시, InnoDB 버퍼 풀, InnoDB 어댑티브 해시 인덱스, InnoDB 리두 로그 버퍼가 있다.
  • 테이블 캐시 : 자주 사용하는 테이블 정보를 메모리에 캐싱
  • InnoDB 버퍼 풀 : 자주 사용하는 데이터와 인덱스를 메모리에 캐싱
  • InnoDB 어댑티브 해시 인덱스 : 자주 조회되는 데이터에 대해 해시 인덱스 생성하여 조회 이점 제공
  • InnoDB 리두 로그 버퍼 : 데이터 변경 내용을 메모리에 임시 저장한 후, 리두 로그 파일에 기록하는 버퍼

- 로컬 메모리 영역

• 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역
• 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되지 않는 영역
• 쿼리 용도별로 필요할 때만 공간을 할당 받는다.
• 커넥션 버퍼, 정렬 버퍼

 

4.1.6 쿼리 실행 구조

하나의 쿼리 작업은 여러 하위 작업으로 나뉜다.

각 하위 작업이 MySQL 엔진 영역에서 처리되는지, 스토리지 엔진에서 처리되는지 구분할 줄 알아야 한다.

 

- MySQL 엔진 영역

• 쿼리 파서
  • 쿼리 문장을 토큰(가장 작은 단위)로 분리해 트리 형태의 구조로 만든다.
  • 기본 문법 오류는 이 과정에서 발견되며, 사용자에게 오류 메시지를 전달
• 전처리기
  • 트리를 기반으로 테이블/컬럼 이름, 함수와 같은 존재 여부, 접근 권한을 확인한다.
• 옵티마이저
  • 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할 방법을 결정
• 실행 엔진(쿼리 실행기)
  • 실행 엔진은 각 핸들러(스토리지 엔진)에게 요청해서 결과를 받거나, 결과를 다른 핸들러에게 준다.
  • Order By, Group By 등 복잡한 처리는 MySQL 엔진의 처리 영역인 이곳 '쿼리 실행기' 에서 처리된다.

- 스토리지 엔진(핸들러)

• MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하거나 읽어 오는 역할을 한다.

 

4.1.8 쿼리 캐시

쿼리 캐시 - SQL 실행 결과를 메모리에 캐시하여 읽기 성능이 좋았다.

하지만, 동시 처리시의 성능 저하와 많은 버그의 원인으로 8.0 이후로는 관련 기능 및 변수가 완전히 제거되었다.

 

4.2 InnoDB 스토리지 엔진 아키텍처

스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금을 제공한다 -> 동시성 처리에 뛰어나며 안정적

4.2.1 프라이머리 키에 의한 클러스터링

- InnoDB의 모든 테이블은 기본으로 프라이머리 키를 기준으로 클러스터링되어 저장된다.

• 테이블의 레코드들은 프라이머리 키 값의 순서대로 디스크에 저장된다.
• 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용
• 프라이머리 키를 이용한 인덱스 레인지 스캔은 상당히 빨리 처리될 수 있다.

- MyISAM 스토리지 엔진은 클러스터링 키를 지원하지 않고, PK를 포함한 모든 인덱스는 물리적인 레코드의 주소 값을 갖는다.

 

4.2.2 외래 키 지원

InnoDB는 지원하지만, MyISAM 과 MEMORY 에서는 사용할 수 없다.

 

InnoDB에서의 외래키 특징

• 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요
• 변경 시에는 반드시 부모/자식 테이블에 데이터가 있는지 체크하기 때문에 잠금이 여러 테이블로 전파된다.
  • 이로 인해 데드락이 발생할 때가 많으므로 외래키 존재에 주의한다.

InnoDB에서의 외래키 체크 해제( 서비스에 긴급 조치를 위해 외래키 제약조건을 꺼야할 때 )

•  SET SESSION foreign_key_checks=OFF 를 통해 현재 세션에 대해 시스템 변수를 OFF로 설정하면 외래키 관계에 대한 체크 작업을 일시적으로 멈출 수 있다.
  • 만약, SESSION 을 빼먹는다면 GLOBAL 로 적용한다는 의미로 받아들여 MySQL서버는 모든 테이블의 외래키 체크를 끄게 되니 주의하자.

 

4.2.3 MVCC (Multi Version Concurrency Control)

- 개념

• 잠금을 사용하지 않는 일관된 읽기를 제공하기 위함
• InnoDB 는 언두 로그(Undo log)로 구현한다.
• 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미

- 예시

id = 1 이고, name = '그로밋' 인 데이터가 있습니다.

이 데이터에 대해 "UPDATE table_name SET name='월레스' WHERE id=1;" 쿼리가 실행되는 걸 가정합니다.

 

UPDATE 쿼리가 실행될 때, MVCC를 통해 일관된 읽기를 보장하는 과정

• 디스크에서 데이터를 읽어, id = 1 이고, name = '그로밋'  을 InnoDB 버퍼 풀에 적재한다.
• 쿼리가 실행되면, id = 1 이고, name = '그로밋' 을 언두 로그에 복사하고, id = 1 이고, name='월레스' 를 InnoDB 버퍼 풀에 적재한다.
• 만약, 격리 수준이 READ_UNCOMMITTED 라면 변경하려는  id = 1 이고, name='월레스' 를 버퍼 풀에서 읽어 반환한다.
  • 만약, 그 이상의 격리 수준이라면 기존 값인 id = 1 이고, name = '그로밋' 을 반환한다.

- 결론

즉, 하나의 레코드( id = 1 이고, name = '그로밋' ) 에 대해 2개의 버전이 유지되고, 필요에 따라 어느 데이터가 읽어 지는지 격리 수준에 따라 달라지는 구조이다.

 

만약, 트랜잭션이 길어지면 언두로그가 관리하는 데이터가 삭제되지 못하고 무한히 많아질 수 있다.

• 이는 MySQL 서버가 느려지거나 문제가 발생하게 된다.

언두 영역의 데이터는 이 영역을 필요로 하는 트랜잭션이 없을 때 비로소 삭제된다.

• 그러니 트랜잭션이 시작됐다면 가능한 빨리 롤백이나 커밋을 통해 트랜잭션을 완료하자

 

4.2.5 자동 데드락 감지

InnoDB 는 데드락 감지 스레드를 가지고 있다.

데드락 발생 시 2개의 트랜잭션 중 언두 로그 양이 적은 트랜잭션을 강제 종료한다, 이는 롤백을 해도 언두 처리를 해야 할 내용이 적기 때문이다.

 

innodb_table_locks

• 테이블 잠금을 활성화 하는 시스템 변수로 테이블 단위 데드락을 감지한다.

innodb_deadlock_detect

• 데드락 감지 스레드인 시스템 변수이다.
• 동시 처리 스레드가 매우 많거나 각 트랜잭션이 가진 잠금의 개수가 많아지면 데드락 감지 스레드는 느려진다.
• 데드락 감지 스레드가 느려지면 서비스 쿼리를 처리 중인 스레드가 느려진다.

innodb_lock_wait_timeout

• 데드락 감지 스레드가 성능에 영향을 끼쳐서 OFF했을 때, 해당 시스템 변수를 사용하여 데드락 상황에서 일정 시간이 지나면 자동으로 요청이 실패하도록 한다.
• 초단위 설정 가능이며, 기본값 50초 보다 훨씬 낮은 시간으로 변경해서 사용하자

이러한 기능은 Google 이 오라클에 요청해서 생기게 되었다. Google 은 동시 처리 스레드가 매우 많고, 트랜잭션이 가진 잠금의 개수가 많아서 데드락 감지 스레드 때문에 성능이 낮아졌었다.

즉, PK 또는 세컨더리 인덱스를 기반으로 매우 높은 동시성 처리를 요구하는 서비스가 있다면 innodb_deadlock_detect 를 비활성화해서 성능 비교를 해보는 것도 한 기회라고 한다.

 

4.2.7 InnoDB 버퍼 풀

버퍼 풀의 크기 설정

• 쓰기작업을 지연시켜 일괄 작업으로 처리해서 디스크의 랜덤I/O 를 줄인다.
• 운영체제의 전체 메모리가 8GB 미만이라면 50% 만 버퍼풀 로 설정하고 나머지는 다른 프로그램이 사용할 수 있게 한다.
• 8GB 이상인 경우, 전체 메모리의 50% 에서 시작해서 조금씩 올려가며 최적점을 찾는다.
• innodb_buffer_pool_size 시스템 변수로 크기를 설정할 수 있다.
  • 풀 사이즈를 크게 하는 것은 시스템 영향도가 크지 않지만, 풀 사이즈를 줄이는 것은 서비스 영향이 매우 큰 작업이니 하지 않도록 주의

 

4.2.9 언두 로그

• 트랜잭션 롤백을 위해 사용되는 영역이다.
  • 만약, 트랜잭션이 길어지면 언두로그가 관리하는 데이터가 삭제되지 못하고 무한히 많아질 수 있다.
  • 이는 MySQL 서버가 느려지거나 문제가 발생하게 된다.
• 언두 영역의 데이터는 이 영역을 필요로 하는 트랜잭션이 없을 때 비로소 삭제된다.
  • 그러니 트랜잭션이 시작됐다면 가능한 빨리 롤백이나 커밋을 통해 트랜잭션을 완료하자
• MySQL 8.0 버전에서 아래 쿼리를 실행하여 언두 로그 레코드 건수를 확인할 수 있다.

SELECT count
FROM information_schema.innodb_metrics
WHERE SUBSYSTEM='transaction' AND NAME='trx_rseg_history_len';

 

4.2.10 체인지 버퍼

INSERT, UPDATE 될 때는 인덱스도 업데이트 해줘야 한다.

이 작업은 디스크를 랜덤하게 읽게 하여 테이블에 인덱스가 많다면 이는 많은 자원을 소모하게 된다.

 

그래서, 디스크에서 읽어와서 디스크에 업데이트 해야한다면 즉시 반영하지 않고, 임시 공간에 저장하고 사용자에게 반영한 결과를 반환하는데, 이 때 사용하는 임시 공간을 체인지 버퍼라고 한다.

 

- 특징

• 기본적으로 InnoDB 버퍼 풀의 메모리 25% 정도까지 사용한다.(설정으로 50%까지 가능하다)
• 읽기 작업이 빈번할 경우에는 사용할 필요가 없다.
• 유니크 인덱스는 체인지 버퍼를 사용할 수 없다.(세컨더리 인덱스에만 적용이 가능하다)
  • 중복확인을 디스크에서 즉시 확인해야 하기 때문

 

4.2.11 리두 로그

• MySQL 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 읽지 않게 해주는 안전장치
• 트랜잭션이 커밋돼도 데이터는 즉시 디스크로 동기화 되지 않지만, 리두 로그는 항상 디스크로 기록된다.
• MySQL 8.0 부터 데이터를 복구하거나 대용량 데이터를 한 번에 적재하는 경우 리두로그를 비활성화 해서 데이터의 적재 시간을 단축할 수 있다.

ALTER INSTANCE DISABLE INNODB REDO_LOG; // 리두로그 비활성화
LOAD DATA ... // 대량 데이터 적재
ALTER INSTANCE ENABLE INNODB REDO_LOG; // 리두로그 활성화

• SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled'; 를 통해 리두로그 활성화 여부를 확인한다.

 

4.2.12 어댑티브 해시 인덱스

개념

• 사용자가 수동으로 생성하는 인덱스가 아닌 InnoDB 에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스
• innodb_adaptive_hash_index 로 활성화/비활성화 할 수 있다.
• B-Tree 의 검색인 루트노드부터 리프노드까지 검색 시간을 줄여주기 위해 도입된 기능
  • 어떻게? 해시 인덱스의 Key는 'B-Tree 인덱스의 고유 번호', Value 는 'B-Tree 의 실제 키 값인 메모리 주소' 의 조합이다.

성능 향상에 도움되는 경우

• 디스크의 데이터가 innoDB 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)
• 동등 조건 검색( = 비교와 IN 연산자)이 많은 경우
• 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우

성능 향상에 도움되지 않는 경우

• 디스크 읽기가 많은 경우
• 특정 패턴의 쿼리가 많은 경우(join 이나 LIKE 패턴)
• 매우 큰 데이터를 가진 테이블의 레코드를 폭 넓게 읽는 경우

특징

• 해시 인덱스는 기본적으로 활성화
• 해시 인덱스는 데이터 페이지를 메모리(버퍼 풀)내에서 접근하는 것을 더 빠르게 만드는 것이기 때문에 디스크에서 읽어오는 경우가 빈번한 데이터베이스 서버에서는 아무런 도움이 되지 않는다.
• 해시 인덱스 또한 메모리를 사용 하기에 인덱스 크기가 큰 경우 해시 인덱스도 큰 메모리를 갖게 된다.
• 해시 인덱스에 있는 인덱스를 삭제/변경 할 경우, 상당히 많은 CPU 자원을 소모한다.

 

어댑티브 해시 인덱스가 도움이 되는지 판단할 수 있는 가장 쉬운 방법은 해시 인덱스 히트율, 해시 인덱스가 사용중인 메모리 공간, 서버의 CPU 사용량을 종합해서 판단해야 한다.