Skip to content

Latest commit

 

History

History
300 lines (249 loc) · 19.8 KB

ch04-architecture.md

File metadata and controls

300 lines (249 loc) · 19.8 KB

Chapter04. 아키텍처

MySQL 엔진 아키텍처

MySQL 전체 구조

  • MySQL은 다른 DBMS에 비해 독특한 구조

    • 이로 인해 장/단점이 존재

  • MySQL 서버는 MySQL 엔진과 스토리지 엔진으로 구분할 수 있음

    MySQL Server = MySQL Engine(쿼리 분석, 최적화) + Storage Engine(실제 데이터 접근)
    • MySQL Engine: SQL interface, SQL parser, SQL optimizer, Cache & Buffer, Connection Handler
    • Storage Engine: InnoDB, MyISAM, Memory...

MySQL 엔진

  • 클라이언트로부터의 접속 및 쿼리 요청을 처리(커넥션 핸들러)
  • SQL 파싱 및 전처리
  • 쿼리의 최적화된 실행

Storage 엔진

  • 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분을 담당
  • MySQL 서버에서 MySQL 엔진은 하나지만 Storage 엔진은 여러 개를 사용할 수 있음
  • 아래 쿼리처럼 테이블이 사용할 스토리지 엔진을 지정하면 이후 해당 테이블의 읽기/변경 작업은 해당 스토리지 엔진이 처리 
    mysql> CREATE TABLE users (col1 INT, col2 INT) ENGINE=INNODB
  • MySQL의 스토리지 엔진은 플러그인 방식이며, 기본적으로 8가지의 스토리지 엔진이 탑재 
    mysql> SHOW ENGINES;

Ref. Storage 엔진 설명

MySQL 스레딩 구조

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

  • 포그라운드 스레드와 백그라운드 스레드로 구분

    mysql> SELECT thread_id, name, type, proccesslist_user, processlist_host
       FROM performance_schema.threads ORDER BY type, thread_id;

포그라운드 스레드(클라이언트 스레드)

  • 포그라운드 스레드는 최소 MySQL 서버에 접속된 클라이언트 수만큼 존재, 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리
  • 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하는 스레드는 다시 스레드 캐시로 되돌아감
    • 이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면 스레드를 종료 -> 일정 개수의 스레드 캐시 수(thread_chach_size)를 유지
  • 포그라운드 스레드는 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오며, 버퍼나 캐시에 없는 경우에는 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어옴

InnoDB

  • 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리

MyISAM

  • 포그라운드 스레드가 디스크 쓰기 작업까지 처리
  • MyISAM도 지연 쓰기를 활용할 수 있지만 일반적이지 않음

백그라운드 스레드

  • MyISAM의 경우 별로 해당 사항 없음(디스크 쓰기 작업까지 처리하기 때문)
  • InnoDB는 아래 작업을 백그라운드로 처리
    • 인서트 버퍼(Insert buffer)를 병합하는 스레드
    • 로그를 디스크로 기록하는 스레드 (로그 스레드)
    • InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드 (쓰기 스레드)
    • 데이터를 버퍼로 읽어 오는 스레드
    • 잠금이나 데드락을 모니터링하는 스레드
  • MySQL 5.5 버전부터 데이터 쓰기 스레드와 읽기 스레드의 개수를 2개 이상 지정할 수 있게 됨
    • innodb_write_io_threads, innodb_read_io_threads로 스레드 개수 설정
  • InnoDB에서도 데이터를 읽는 작업은 주로 클라이언트 스레드에서 실행되기 때문에 읽기 스레드는 많이 설정할 필요 없음
  • 쓰기 스레드는 대부분의 많은 작업을 백그라운드로 처리하기 때문에 일반적으로 2~4 정도 설정

메모리 할당 및 사용 구조

글로벌 메모리 영역 로컬 메모리 영역
InnoDB 버퍼 풀
MyISAM 키 캐시
바이너리 로그 버퍼
리두 로그 버퍼
테이블 캐시		 조인 버퍼
정렬 버퍼
네트워크 버퍼
리드 버퍼
  • MySQL 메모리 공간은 글로벌 메모리 영역과 로컬 메모리 영역으로 구분

글로벌 메모리 영역

  • 글로벌 메모리 영역의 모든 메모리 공간은 MySQL 서버가 시작되면서 OS로부터 할당
    • OS에 따라 한 번에 100%를 할당할 수도 있고, 조금씩 할당해주는 경우도 존재
  • 일반적으로 클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당

로컬 메모리 영역(세션 메모리 영역)

  • MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역
  • 로컬 메모리는 각 클라이언트 스레드 별로 독립적으로 할당되며 절대 공유하지 않음

플러그인 스토리지 엔진 모델

  • 플러그인 모델이기 때문에 원하는 플러그인을 갈아끼워서 적용할 수 있음

컴포넌트

  • 플러그인 아키텍처에는 아래와 같은 단점이 존재
    • 플러그인은 오직 MySQL 서버와 인터페이스할 수 있고, 플러그인끼리 통신할 수 없음
    • 플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전한지 않음(캡슐화 X)
    • 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화에 어려움 존재
  • 이와 같은 단점들을 보완하기 위해 컴포넌트 아키텍처를 지원

쿼리 실행 구조

0. 사용자의 SQL 요청
1. 쿼리 파서            // 토큰 분리, 트리 생성, 문법 오류 파악
2. 전처리기             // 토큰 매핑, 접근 권한 확인
3. 옵티마이저            // 쿼리 변환, 비용 최적화, 실행 계획 수립
4. 쿼리 실행기 
5. 스토리지 엔진 접근
6. SQL 결과

쿼리 파서

  • 사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 기호)으로 분리해서 트리 구조를 생성
  • 이 과정에서 기본 문법 오류를 파악

전처리기

  • 트리 구조의 쿼리 문장에 구조적인 문제점이 있는지 파악
  • 테이블이나 컬럼 이름, 내장 함수같은 개체를 매핑해서 해당 객체의 존재 여부와 객체의 접근 권한을 확인

옵티마이저

  • 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지 결정하는 역할
  • 쿼리 변환 비용 최적화, 실행 계획 수립

쿼리 실행기(실행 엔진)

  • 옵티마이저에 의해 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸드렁의 요청의 입력으로 연결

스토리지 엔진(핸들러)

  • 데이터를 디스크에 저장, 읽어오는 역할 담당

복제

  • 16장에서 자세히

쿼리 캐시

  • SQL 실행 결과를 메모리에 캐시하고 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 결과를 반환
  • 테이블의 데이터가 변경되면 캐시에 저장된 결과 중 관련된 것들은 모두 삭제
  • 성능 저하 및 버그의 원인이기 때문에 사용 권장 X
  • 8.0으로 오면서 관련 기능은 Deprecated

스레드 풀

  • MySQL 서버 엔터프라이즈 에디션은 스레드 풀 기능을 제공하지만, 커뮤니티 에디션은 스레드 풀 기능을 지원하지 않음
  • 결국 목적은 제한된 개수의 스레드 처리에만 집중하고, 반복해서 생성.삭제하는 코스트를 줄이는 역할

InnoDB 스토리지 엔진 아키텍처

  • InnoDB는 MySQL에서 사용할 수 있는 스토리지 엔진 중 거의 유일하게 레코드 기반 잠금을 제공

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

  • InnoDB의 모든 테이블은 PK를 기준으로 클러스터링되어 저장
    • PK 값의 순서대로 디스크에 저장
    • PK가 클러스터링 인덱스이기 때문에 PK를 이용한 range scan 효율이 좋음
    • 다른 보조 인덱스에 비해 비중이 높게 설정(쿼리 실행 계획에서 다른 보조 인덱스보다 PK가 선택될 확률이 높음)
    • 8장에서 클러스터링 인덱스에 대한 설명 보충
  • MyISAM에서는 InnoDB와 달리 클러스티링 키를 지원하지 않음
    • MyISAM에서는 PK와 보조 인덱스는 구조적으로 차이가 없음(PK는 단순히 unique 제약을 가진 인덱스)

외래 키 지원

  • 외래 키 지원은 InnoDB 스토리지 엔진 레벨에서 지원하는 기능
    • MyISAM이나 Memory 테이블에서 사용할 수 없음
  • 외래 키는 DB 서버 운영의 불편함 때문에 서비스용 데이터베이스에서 생성하지 않은 경우가 존재
    • 부모, 자식 테이블의 해당 컬럼에 인덱스 생성이 필요하고, 테이블 변경 시 양쪽 테이블에 데이터가 있는지 체크
    • 데이터가 있는지 체크할 때 잠금이 여러 테이블로 전파되고, 이로 인해 데드락이 발생할 수도 있음

MVCC(Multi Version Concurrency Control)

  • 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능

  • 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공

    • InnoDB에서는 Undo log를 이용하여 해당 기능을 구현

      언두(Undo) 로그

      • 언두 영역은 UPDATE 문장이나 DELETE와 같은 문장으로 데이터를 변경했을 때 변경되기 전의 데이터(이전 데이터)를 보관하는 공간

      mysql > UPDATE member SET name = '김희동' WHERE member_id = '2';

      • 위 쿼리가 실행되면 트랜잭션을 커밋하지 않아도 실제 데이터 파일 내용은 "김희동"으로 변경
      • 그리고 변경되기 전의 값이 "홍길동"이라면 언두 영역에는 "홍길동"이라는 값이 백업
      • 이 상태에서 만약 사용자가 커밋하게 되면 현재 상태(김희동)가 그대로 유지되고, 롤백하게 된다면 언두 영역의 백업된 데이터(홍길동)를 다시 데이터 파일로 복구

      언두의 데이터 사용 용도

      • 트랜잭션의 롤백 대비용
      • 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공

  • MVCC의 MV(Multi version)은 하나의 레코드에 대해 여러 개의 버전이 동시에 관리됨을 의미

트랜잭션 격리 수준

  • READ UNCOMMITTED(커밋되지 않은 읽기)
  • READ COMMITTED(커밋된 읽기)
  • REPEATABLE READ(반복 가능한 읽기)
  • SERIALIZABLE(직렬화 가능)

트랜잭션 격리 수준과 관련하여 읽어볼만한 글

DB별 Default 격리 수준

  • MySQL(InnoDB): REPEATABLE READ
  • Oracle: READ COMMITTED
  • Postgre: READ COMMITTED

잠금 없는 일관된 읽기(Non-Locking Consistent Read)

  • InnoDB 스토리지 엔진은 MVCC를 이용해서 잠금을 걸지 않고 읽기 작업을 수행
  • SERIALIZABLE가 아닌 경우 읽기(select) 작업은 다른 트랜잭션의 변경 작업과 관계없이 바로 실행

자동 데드락 감지

  • InnoDB는 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프(wait-for list) 형태로 관리
  • 데드락 감지 스레드를 통해 주기적으로 잠금 대기 그래프를 검사

자동화된 장애 복구

  • InnoDB는 견고한 편이어서 데이터 파일이 손상되거나 서버가 시작되지 못 하는 경우는 거의 발생하지 않음
  • 하지만 서버와 무관하게 디스크, 서버 HW로 InnoDB 엔진이 복구를 못 하는 경우 발생할 수 있음

InnoDB 버퍼 풀

  • 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해두는 공간
  • 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 수행
    • insert, update, delete처럼 데이터를 변경하는 쿼리는 데이터 파일의 레코드를 변경하기 때문에 랜덤한 디스크 작업을 발생
    • 버퍼 풀은 이런 변경된 데이터를 모아서 처리하기 때문에 랜덤한 디스크 작업의 횟수를 감소

버퍼 풀의 크기 설정

  • 일반적으로 전체 물리 메모리의 80%를 버퍼 풀로 사용하는 내용이 있는데 그렇게 하면 안 됨
    • os와 client thread가 사용할 메모리도 고려해서 함께 설정
  • 5.7버전부터 버퍼 풀 크기를 동적으로 조절할 수 있게 개선
    • 처음에 50% 정도로 설정했다가 조금씩 올려가면서 최적점을 찾아야 함
    • innodb_buffer_pool_size 시스템 변수로 크기 설정 가능

버퍼 풀과 리두 로그

  • InnoDB 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정하면 할수록 쿼리의 성능이 빨라짐
    • 이미 디스크의 모든 데이터 파일이 버퍼 풀에 적재될 정도의 버퍼 풀 공간이면 성능에 도움이 되지 않음
  • InnoDB 버퍼 풀은 데이터베이스 성능 향상을 위해 캐시와 쓰기 버퍼링 두 가지 용도로 활용되는데, 버퍼 풀 메모리 공간을 증가시키느 것은 캐시 기능만 향상시키는 것

용어

  • 클린 페이지(clean page): 디스크에서 읽은 상태로 전혀 변경되지 않은 페이지
  • 더티 페이지(dirty page): 변경(insert, update, delete)된 데이터를 가진 페이지, 언제간 디스크에 기록될 페이지
  • LSN(Log Sequence Number): 리두 로그 파일 공간은 계속 순환되어 재사용되지만, 매번 기록될 때마다 로그 포지션은 계속 증가된 값을 갖음. 이 값이 LSN
  • 활성 리두 로그(Active Redo Log): 재사용 불가능한 공간
  • 체크포인트 에이지(checkpoint age): 활성 리두 공간의 크기
  • InnoDB 엔진에서 리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환고리처럼 사용
    • 데이터 변경이 계속해서 발생하면 리두 로그 파일에 기록됐던 로그 엔트리는 다시 새로운 로그 엔트리로 덮어 쓰임
  • 따라서 전체 리두 로그 파일에서 재사용 가능한 공간과 당장 재사용 불가능한 공간을 구분해서 관리해야 함
    • 재사용 불가능한 공간은 활성 리두 로그(Active Redo log)라고 일컫음
  • InnoDB 엔진은 주기적으로 체크포인트 이벤트를 발생시켜 리두 로그와 버퍼 풀의 더티 페이지를 디스크로 동기화
    • 이렇게 발생한 체크포인트 중 가장 최근 체크포인트 지점의 LSN이 활성 리두 로그 공간(재사용 불가능한 공간)의 시작점이 됨
  • 버퍼풀 크기 100GB면 리두 로그 파일은 5~10GB 수준이 적정
    • 버퍼 풀 크기가 너무 크고, 리두 로그 파일 크기가 너무 작으면 쓰기 버퍼링 효과를 거의 보지 못함
    • 버퍼 풀 크기가 너무 작고, 리두 로그 파일 크기가 너무 크면, 버퍼 풀 크기가 작아서 더티 페이지를 버퍼 풀 크기 밖에 가지지 못 해서 버퍼링 효과를 거의 보지 못함
      • 추가로 더티 페이지 크기가 너무 크면 갑자기 버퍼 풀이 필요해지는 상황에 더티 페이지를 한 번에 기록해야 되는 상황이 발생할 수 있음

버퍼 풀 플러시

  • 굳이 조정할 필요 없음

언두 로그

  • 트랜잭션 격리 수준을 보장하기 위한 데이터를 백업

체인지 버퍼

  • 레코드가 변경(insert, update)되는 작업뿐만 아니라 테이블에 포함된 인데스를 수정하는 작업도 존재
    • 인덱스를 업데이트하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱스가 많다면 이 작업에 많은 자원을 소모
  • 그래서 InnoDB는 변경해야될 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만, 디스크로부터 읽어와서 업데이트해야 한다면 즉시 실행하지 않고, 임시 공간에 저장해두고 사용자에게 결과를 반환하는 형태로 성능을 향상시킴
    • 이 때 사용하는 임시 메모리 공간이 체인지 버퍼

어뎁티브 해시 인덱스(Adaptive Hash Index)

  • 일반적으로 인덱스라고 하면 테이블에 사용자가 생성해둔 B-Tree 인덱스를 의미함
  • 어뎁티브 해시 인덱스는 사용자가 수동응로 생성하는 인덱스가 아니라 InnoDB 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스
    • innodb_adaptive_hash_index 시스템 변수를 통해 어뎁티브 해시 인덱스 기능을 활성화 또는 비성활할 수 있음
  • 어뎁티브 해시 인덱스는 B-Tree 검색 시간을 줄여주기 위해 도입된 기능
    • 자주 읽히는 데이터 페이지의 키 값을 이용해서 해시 인덱스를 만들고, 필요할 때마다 어뎁티브 해시 인덱스를 검색
  • B-Tree 검색 시간을 줄여줄 수 있기 때문에 항상 활성화해야 된다는 생각을 할 수도 있지만 의도적으로 비활성화하는 경우도 많음
  • 중요한 것은 어뎁티브 해시 인덱스는 데이터 페이지를 메모리(버퍼 풀) 내에서 접근하는 것을 더욱 빠르게 하는 기능
    • 데이터 페이지를 많이 읽는 경우네는 도움이 되지 않음

어뎁티브 해시 인덱스가 성능 향상에 도움을 주는 경우

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

어뎁티브 해시 인덱스가 성능 향상에 도움을 주지 않는 경우

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

InnoDB vs MyISAM, Memory

  • 8.0 이전에는 MyISAM에서만 전문 검색, 공간 좌표 검색을 지원했지만, 8.0부터는 InnoDB에서도 지원
    • MyISAM의 기능을 InnoDB에서 지원하기 때문에 곧 deprecate 될 듯
  • 동시 처리 성능에 있어서 Memory 엔진은 InnoDB 엔진을 못 따라옴
    • 일시적인 테이블 생성의 경우에 TempTable엔진을 사용할 수 있기 때문에 Memory 엔진도 deprecate 될 듯