MySQL 아키텍처

진성대·2025년 3월 13일
mysql
0

SQL

목록 보기
6/10

RealMySQL 책을 읽고 노션에 정리한 글을 옮긴 것입니다.

1. MySQL 전체 구조

MySQL Server
│
├── MySQL Engine
│   ├── Connection Handler
│   ├── SQL Interface
│   ├── SQL Parser
│   ├── SQL Optimizer
│   └── Cache & Buffer
│
└── Storage Engine
    ├── Storage Engine API
    └── Engines
        ├── InnoDB
        ├── MyISAM
        └── Memory
  • MySQL 엔진
    • 커넥션 핸들러(Connection Handler) - 클라이언트로부터의 접속 및 쿼리 요청을 처리
    • SQL 문장을 분석하거나 최적화 하는등 DBMS의 두뇌 역할을 한다.
  • 스토리지 엔진

    • 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분을 담당

    • CRUD 담당

      CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;

    • 스토리지 엔진은 MYSQL 엔진과 다르게 여러 스토리지 엔진을 사용할 수 있다.

    • 위의 쿼리처럼 테이블을 어떤 스토리지 엔진을 사용할지 지정할 수 있다.

  • 핸들러 API
    • MySQL 엔진과 스토리지 엔진과의 데이터를 주고 받을때(읽기, 쓰기 등) 할때 API를 통해서 소통을 하는데 이것을 핸들러 API 라고 부른다.

MySQL 스레딩 구조

  • MySQL 서버는 프로세스 기반이 아닌 스레드 기반으로 작동한다. (포그라운드 스레드, 백그라운드 스레드)
  • 포그라운드 스레드 (클라이언트 스레드)
    • 클라이언트당 한개씩 스레드가 생성되며 다 사용후 스레드 캐시에 저장이 된다. 캐시가 다 찼으면 스레드를 삭제시킨다.
    • 읽기 작업을 할당 받았을때, 데이터 버퍼나 캐시에 접근하여 데이터를 읽는 작업을 한다.
    • 데이터 버퍼나 캐시에 데이터가 없는 경우 디스크에 직접 읽으러 접근을 한다.
    • MyISAM은 포그라운드가 쓰기 작업을 직접 처리하지만 InnoDB는 백그라운드 스레드가 쓰기 작업을 처리한다.
  • 백그라운드 스레드
    1. 인서트 버퍼(Insert Buffer)를 병합하는 스레드
      • 인서트 버퍼란 InnoDB에서 Secondary Index(비클러스터 인덱스)의 쓰기 작업을 최적화 하기 위해 사용되는 데이터 구조이며 비클러스터 인덱스는 일반적으로 랜덤읽기를 사용하는데 이 랜덤 읽기는 디스크의 물리적 구조상 좋지 않다.
      • 비클러스터 인덱스의 변경 작업 (INSERT, UPDATE, DELTE)를 즉시 디스크에 반영하지 않고 Insert Buffer라는 특정 공간에 저장한 후, 적절한 시점에 디스크에 I/O 한다.
    2. 로그를 디스크로 기록하는 스레드
    3. InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
    4. 데이터를 버퍼로 읽어 오는 스레드
    5. 잠금이나 데드락을 모니터링 하는 스레드
    • 위 의 과정 모두 중요하지만 백그라운드 스레드 의 작동중에 중요한 것이 로그 스레드와 버퍼 데이터를 디스크로 내려쓰는 작업이다.
    • 데이터의 쓰기 작업은 지연(버퍼링)될 수 있지만 데이터를 읽는 작업은 지연 되면 안된다. (read 시 바로 데이터 보여줌)
    • 쓰기 작업을 버퍼링 해서 일괄 처리하는 기능이 되어있다. 하지만 MyISAM은 사용자 스레드가 INSERT, UPDATE, DELTE 기능까지 사용한다. InnoDB는 디스크의 데이터가 완전히 저장 될때까지 기다리지 않아도 된다.

메모리 할당 및 사용 구조

  • 글로벌 메모리 영역은 운영체제한테 할당을 받는다.
  • 글로벌 메모리 영역
    • 클라이언트 스레드의 개수와 상관없이 하나의 메모리 공간만 할당한다.
  • 로컬 메모리 영역(세션 메모리 영역)
    • 클라이언트 스레드는 각 로컬 메모리 영역별로 독립적으로 존재를 한다.

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

  • Order, group by 같은 쿼리는 MySql 엔진의 쿼리 실행기에서 실행된다.

컴포넌트

  • 기존의 플러그인 들의 단점을 보완해서 나왔다.

쿼리 실행 구조

  1. SQL 요청
  2. MySQL 엔진
    1. 쿼리 파서
      • 쿼리 문장을 토큰으로 분리해서 트리 형태의 구조로 만든다.
    2. 전처리기
      • 파서 트리를 기반으로 쿼리 문장에 문제점을 찾는다. 접근 권한, 테이블 네이밍, 실제 존재하는 테이블 안에 있는가 등등
    3. 옵티마이저 (쿼리 변환, 비용 최적화, 실행 계획 수립)
      • 사용자가 사용한 쿼리를 어떻게 하면 최적화 해서 적은 비용으로 처리할지 결정하는 곳
    4. 쿼리 실행기 (실행 엔진)
      • 옵티마이저가 머리, 쿼리 실행기( 관리자), 스토리지 엔진(실무 담당자)
      • 쿼리 실행기가 옵티마이저가 계획한대로 스토리지 엔진에게 명령하고 결과를 받은 후에 사용자에게 반환한다.
  3. 스토리지 엔진 (핸들러)
    1. InnoDB, MyISAM, Memory 중에 하나 사용
    • 데이터를 읽어오거나 쓰거나 한다.
  4. 쿼리 실행기

쿼리 캐시

  • 쿼리의 결과를 캐시에 저장하고 또 같은 쿼리를 호출 할 시에 디스크에 접근해서 데이터를 가져오는게 아닌 캐시에 접근해서 데이터를 가져오도록 하는것이다.
  • 하지만 테이블의 데이터가 변경시 테이블 관련 캐시들도 삭제해야하는데 이는 심각한 동시성 성능저하를 불러왔다.
  • MySql 8.0 버전에서 삭제를 하였다.

스레드 풀

  • CPU가 잘 처리할 수 있도록 스레드 순서들을 잘 조합하도록 하는 것 → 이것만 잘 되면 프로세스 친화도를 높이고 불필요한 컨텍스트 스위치가 발생하지 않으며 오버헤드를 줄 일 수 있다.
  • 스레드 풀은 cpu 의 코어 개수 만큼 스레드 그룹을 생성한다.
  • 작업이 들어왔는데 스레드 풀의 스레드 그룹에 스레드가 가득 차 있으면 스레드를 추가 할 지 판단한다.
  • 스레드가 추가되는 기준은 지금 작업중인 스레드가 작업이 끝나는 남은 시간으로 판단해서 추가한다.
  • 선순위 큐, 후 순위 큐로 우선순위를 판단해서 트랜잭션 잠금을 빨리 해제시켜서 잠금 경합을 낮춰서 처리 성능을 향상 시킬 수 있다.

트랜잭션 지원 메타데이터

  • 기존에 테이블의 메타데이터, 데이터 딕셔너리 등 MySQL 과 관련된 데이터 정보들은 FRM 파일에 저장했다.
  • 파일로 관리 되기 때문에 트랜젝션을 지원하지 않아 테이블 생셩, 변경등 작업을 했을때 MySQL 서버가 오류로 인해 서버가 다운됐을때 정상적으로 복구하지 못하는 일이 발생헀다
  • MySQL 8.0 버전에서는 이런 메타데이터, 데이터 딕셔너리, 스토어드 프로그램 등을 InnoDB 엔진에 저장하도록 설정해 두었고 트랜잭션으로 관리 되기 때문에 예상치 못한 오류로 인해 서버가 죽었을때도 복구가 가능해졌다.
  • 또한 데이터 딕셔너리 정보들을 테이블로 검색해서 보려고 해도 보이지 않는데 이는 보안때문인거 같고 그 테이블을 보기 위해서는 View 를 통해서 볼 수 있다. 직접 그테이블을 보려고 하면 없다고 나오거나 권한이 없다고 나온다.
  • InnoDB를 사용하는 테이블은 관련 데이터들을 스토리지 엔진에 저장할 수 있지만 MyISAM이나 Memory는 메타데이터를 SDI 파일에 저장한다

2. InnoDB 스토리지 엔진 아키텍처

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

  • InnoDB 의 프라이머리 키는 클러스터링(비슷한 속성끼리 뭉치는것) 되어 저장된다. 세컨더리 키는 프라이머리 키의 논리적 주소를 가지며 세컨더리 키를 통해 검색을 하게 되면 프라이머리키를 거쳐서 검색을 하게 된다.
UserID | Name      | Age
------ | --------- | ---
   101 | Alice     | 25
   102 | Bob       | 30
   103 | Charlie   | 22
   
 Age | UserID (프라이머리 키)
--- | --------------------
 22 | 103
 25 | 101
 30 | 102
  • MyISAM은 InnoDB 와 다르게 프라이머리 키를 클러스터링 하지 않는다. 따라서 프라이머리 키나 세컨더리 키는 동일하다. 인덱스 들은 각자 고유의 물리 주소를 갖게 되며 이 물리 주소 값을 ROWID 라고 한다.

외래키 지원

MVCC(Multi Version Concurrency Control)

  • 잠금을 사용하지 않는 일반적인 읽기를 제공하는 것
  • 예를 들어 트랜잭션 격리 수준의 단계가 READ COMMIT 단계일때
  • 데이터를 Insert 한다고 가정을 해보자
INSERT INTO member (m_id, m_name, m_area) VALUES (12, '홍길동', '서울');
  • 그러면 InnoDB 버퍼 풀 안에
m_idm_namem_area
12홍길동서울

가 생성되고 언두 로그는 아직 비어있게 된다

그리고 데이터 파일(디스크)에도 같은 내용이 들어가져 있다.

  • 사용자가 UPDATE 를 다음과 같이 진행한다고 한다면
UPDATE member SET m_area='경기' WHERE m_id=12;
  • 언두 로그에
m_idm_area
12서울

가 생성이 되고 InnoDB 버퍼풀에

m_idm_namem_area
12홍길동경기

가 생성된다. 언두로그에는 UPDATE 하기 전 값을 복사해서 생성해 둔다.

아직 COMMIT 상태 이전이기 때문에 다른 클라이언트에서 READ 를 통해서 m_id = 12 를 검색하게 된다면 언두로그에 있는 값을 전달해준다.

  • 언두로그와 연관된 트랜잭션이 끝날때까지 언두로그에 있는 값을 제거하지 않는다.
  • COMMIT 을 하면 데이터 디스크에 값을 영구적으로 변경하게 되지만 ROLLBACK 을 하게 된다면 언두로그에 있는 값을 다시 버퍼 풀로 가져온다. 즉 COMMIT 을 했다고 언두로그에 있는 값을 즉시 삭제하지는 않는다.

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

  • 언두로그(트랜잭션 격리수준이 READ_COMMIT, REPEATABLE_READ)나 버퍼풀(READ_UNCOMMIT) 을 통해서 트랜잭션의 잠금을 기다리지 않고 READ 할 수 있다.
  • 트랜잭션의 점유율이 길어지면 쌓이는 언두로그의 양도 많아지기 때문에 SQL의 성능 저하 이슈가 발생할 수 있다.

자동 데드락 감지

  • 데드락 감지 스레드는 스레드의 잠금 대기 목록을 보고 종료할 스레드를 찾는데 그 기준이 언두 로그의 양이다. 언두 로그의 양이 적은 스레드를 우선적으로 종료시킨다.
  • InnoDB 스토리지는 상위 레이어의 테이블 잠금은 볼 수 없는데 innodb_table_lock 시스템 변수를 활성화 하면 MySQL엔진에서 관리하는 테이블 잠금까지 볼 수있게 된다.
  • 동시 처리 스레드가 많아지게 되면 데드락 성능도 저하 되게 된다. 잠금 대기 목록에 변화되는 것을 막기 위해 그 리스트도 잠금을 걸게 된다. 그러면 서비스 쿼리를 처리중인 스레드는 완전 정지하게 된다.
    이런 경우 실제 서비스 중인 DB 면 큰 피해를 입게 된다.
  • 이 문제를 해결 하기 위해서 innodb_deadlock_detect 라는 변수를 제공하며 OFF를 하면 데드락 감지를 하지 않게 된다. 하지만 데드락 감지를 종료하게 되면 데드락 발생시 문제가 심각해지며 교착상태에 나오지 못해서 서비스가 정지될 수있다. 그 경우 innodb_lock_wait_timeout 을 통해서 일정 시간 이후까지 교착상태에서 못빠져 나오면 발생시킨 스레드를 종료하도록 한다.

자동화된 장애 복구

  • InnoDB는 손상된 데이터를 복구하는 기술이 있다.
  • 그런데 이 기술도 복구 못하는게 있긴 하다.
  • innodb_force_recovery 시스템 변수는 손상된 데이터 파일이나 로그를 검사할 수 있게 선별해주는시스템 변수다.
  • innodb_force_recovery 값이 커질 수 록 더이상 복구가 힘든 단계이다.(1 ~ 6)

InnoDB 버퍼 풀

  • 디스크의 데이터 파일이나 인덱스 정보를 캐시해 두는 공간이다.
  • 쓰기 작업을 일괄 처리할 수 있도록 해주는 버퍼 역할도 한다. 랜덤 쓰기 작업을 줄여줄 수 있다.

1. 버퍼 풀의 크기 설정

  • 8GB 미만이면 메모리를 50% 정도로 설정하고 그 이상이면 조금씩 올려가면서 최적점을 찾아야한다.
  • 버퍼풀을 128 mb 단위로 늘리거나 줄여야한다.
  • 버퍼풀 안에 여러개로 쪼개서 버퍼풀을 관리하는데 메모리가 1GB 이하면 1개로 관리되고 40GB 이하면 8개로 관리된다. 더 크다면 5GB 에 하나당 추가시키는것이 좋다.

2. 버퍼 풀의 구조

  • 버퍼풀의 페이지 크기 조각을 관리하기위해, LRU, FLUSH, FREE 3가지 자료구조로 관리된다.
  • InnoDB 스토리지 엔진에서 데이터를 찾는과정
    1. 버퍼 풀 검사.
      1. AHI(어뎁티브 해시 인덱스) → 버퍼 풀(LRU 리스트) → 디스크(B-Tree 인덱스)
    2. 디스크 적재
      1. 데이터가 버퍼 풀에 없으면 디스크에서 적재하고 LRU 영역에 배치
    3. MRU 승급
      1. 적재된 데이터가 실제로 사용되면 MRU 영역으로 이동
    4. 데이터 페이지 관리
      1. 데이터는 쿼리 빈도에 따라 나이가 부여되고, 오래 사용되지 않으면 버퍼 풀에서 제거
    5. AHI에 추가
      1. 자주 사용되는 페이지 인덱스 키는 AHI 에 추가되어 빠른 검색 가능.
  • 플러시 리스트
  • 초기상태 :
    • 디스크에 데이터 페이지와 리두 로그가 존재
    • 버퍼 풀에는 데이터 페이지가 로드 되지 않은 상태
  • 데이터 변경 발생
    • 사용자 쿼리가 데이터를 변경
    • 변경된 내용이
      • Redo Log 에 기록
      • 버퍼 풀의 데이터 페이지에 반영
    • 변경된 데이터 페이지는 플러시 리스트에 추가
  • 플러시 리스트 관리
    • 변경된 데이터 페이지는 플러시 리스트를 통해 추적
    • 특정 시점(체크 포인트 등)에서 디스크로 기록
  • 체크포인트 발생
    • 리두 로그와 데이터 페이지를 동기화
    • 디스크에 기록된 리두 로그의 위치와 데이터 페이지를 동기화 하여 복구 지점을 설정

3. 버퍼 풀과 리두 로그

  • 버퍼풀 의 성능을 올리려면 캐시 용량을 늘리면 읽기 성능이 좋아지고 리두 로그 용량을 늘려야지 쓰기 성능이 좋아진다.
  • 클린 페이지
    • 디스크에 있는 데이터와 완전히 동일한 상태의 페이지
    • 디스크에서 읽어 온 후 변경되지 않았으므로 디스크와 동기화 상태
  • 더티 페이지
    • insert, update, delete 명령으로 변경된 데이터를 가진 페이지
    • 디스크와 버퍼 풀의 데이터가 불일치 상태이며, 언젠가 디스크로 기록(플러시)되어야 한다.
  • 리두 로그
    • 데이터 변경내용이 발생하면, 이를 Redo Log 파일에 기록한다.
    • 리두 로그는 Write-Ahead Logging(WAL)방식을 구현하며, 데이터가 디스크로 기록되지 않아도 복구를 가능하게 한다.
  • 활성 리두 로그 (Active Redo Log)
    • 리두 로그 파일에서 재사용할 수 없는 공간을 의미한다.
    • 이 공간은 아직 체크포인트로 처리되지 않았으며, 복구에 필요한 상태를 유지한다.
  • LSN(Log Sequence Number)
    • 로그 엔트리가 기록될 떄마다 증가하는 숫자로, 각 엔트리를 고유하게 식별한다.
    • LSN은 변경의 순서를 추적하며, 체크포인트와 활성 리두 로그의 범위를 정의한다.
  • 체크포인트
    • InnoDB가 버퍼 풀의 더티 페이지와 리두 로그를 디스크와 동기화 하는 시점
    • 복구시점을 정의(LSN 기반)
    • 리두 로그의 재사용 가능 공간 확보
  • 버퍼 풀, 리두 로그, 체크포인트의 상호작용
    1. 데이터 변경이 발생하면
      1. 리두 로그 기록
        • 데이터 변경 내용이 Redo Log 버퍼에 기록되고, 일정 시점에서 Redo Log 파일(디스크)에 플러시 된다.
        • LSN은 새롭게 추가된 로그 엔트리를 고유하게 식별한다.
      2. 버퍼 풀 변경
        • 변경된 데이터는 메모리의 버퍼 풀에 반영되며, 데이터 페이지는 더티 페이지로 상태가 봐뀐다.
        • 이 상태에서 디스크와 메모리의 데이터가 불일치 하게 된다.
    2. 리두 로그 순환
      • 리두 로그는 고정된 크기의 파일을 순환적으로 사용한다.
      • 새롭게 로그가 기록되면, 기존 리두 로그 파일의 일부를 덮어씌운다.
      • 활성 리두 로그 (Active Redo Log)
        • 체크포인트 이후에 생성된 로그 엔트리로, 복구에 필요한 공간이다.
        • 체크포인트가 발생하지 않으면, 활성 리두 로그의 크기는 점점 증가한다.
    3. 체크포인트 발생
      • 체크포인트는 버퍼 풀과 리두 로그를 디스크와 동기화하여 일관성을 유지한다.
      1. 체크포인트 LSN 업데이트
        • 체크포인트가 발생하면, 현재까지 디스크와 동기화된 마지막 로그 엔트리의 LSN을 기록한다.
        • 이 값을 체크포인트 LSN이라고 한다.
      2. 더티 페이지 플러시
        • 체크포인트 LSN 보다 작은 LSN 과 관련된 더티 페이지를 모드 디스크로 기록한다.
        • 이 과정을 통해 디스크의 데이터 상태와 버퍼 풀의 더티 페이지가 일치하게 된다.
      3. 리두 로그 정리
        • 체크포인트 LSN보다 작은 리두 로그 엔트리는 복구에 더이상 필요하지 않으므로 재사용 가능 상태로 전환된다.
    4. 체크포인트 에이지
      • 활성 리두 로그 크기를 나타낸다.
      • 계산 방법
        • 체크포인트 에이지 = (가장 최근 리두로그 엔트리의 LSN) - (체크포인트 LSN)
      • 의미
        • 활성 리두 로그가 클수록 복구 시 처리해야 할 로그 엔트리가 많아지고, 복구 시간이 길어진다.
        • 활성 리두 로그가 너무 커지면, 리두 로그 파일이 부족해져 데이터베이스 성능에 영향을 줄 수 있다.

4. 버퍼 풀 플러시(Buffer Pool Flush)

  • 더티 페이지 플러시를 하는 백그라운드로 적용하는 2가지 방법
  1. 플러시 리스트 플러시
    1. innodb_page_cleanrs는 더티 페이지를 디스크와 동기화 시켜주는 클리너 스레드의 시스템 변수인데 버퍼 풀 갯수와 동일하게 설정하는 것이 좋다.
    2. 버퍼 풀 인스턴스 갯수보다 많이 설정하더라도 버퍼 풀 갯수만큼 생성된다.
    3. innodb_max_dirty_pages_pct 는 버퍼 풀의 페이지 갯수에서 더티페이지의 비율을 조정할 수 있다.
    4. 디스크 폭발 현상은 디스크에 기록하고 있는 더티 페이지 비율 보다 버퍼 풀에 있는 더티 페이지비율이 90% 차지할때 발생한다.
    5. 더티 페이지가 많아지면 디스크 쓰기 폭발 현상이 발생할 수 있다.
    6. innodb_io_capacity: 디스크 쓰기 속도를 제한하여 조절
    7. innodb_max_dirty_pages_pct_lwm : 더티 페이지 비율이 일정 수준 (기본 10%)을 넘으면, 더티 페이지를 디스크로 조금씩 기록
    8. 조정 필요성
      1. 더티 페이지 비율이 너무 낮으면 디스크 쓰기 빈도 증가 → 비율을 높여 조정
      2. 더티 페이지 비율이 너무 높으면 디스크 쓰기 폭발 → 디스크 쓰기 속도 증가로 조정
    9. innodb_adaptive_flushing_lwm은 활성 리두 로그 공간의 비율에 따라 더티 페이지를 생성시킬 수 있게 비율을 조절 할 수 있게 하는 시스템 변수이다.
    10. innodb_flush_neighbors 는 더티 페이지를 기록할때 근처에 있는 더티 페이지를 묶어서 같이 저장하는 시스템 변수이다 HDD 시절 시 디스크 읽기 쓰기는 고비용이였기때문에 읽기, 쓰기를 최소화 하기 위함이다.
  2. LRU 리스트 플러시
    1. LRU 리스트의 끝부분 부터 데이터 페이지를 읽기 시작하는데 더티 페이지일 경우는 디스크 동기화 작업을 하고 클린 페이지 일 경우 프리 리스트로 페이지를 옮긴다.

5. 버퍼 풀 상태 백업 및 복구

  • MySQL 5.5 버전 전에는 SQL 서버를 셧다운 하고 다시 재시작 했을 시 에 버퍼 풀이 비어 있기 떄문에 쿼리의 성능 향상을 위해서 테이블, 인덱스 풀 스캔 을 했었다.
  • MySQL 5.6 버전 이후 부터는 innodb_buffer_pool_load_now 시스템 변수를 통해서 서버를 셧다운 하기전에 버퍼 풀에 있는 것을 dump 뜰 수 있다. 그래서 셧다운 하고 재시작시 워밍업을 할 필요가 없어졌다.
  • 버퍼 풀의 백업은 LRU 리스트에 적재되어 있는 데이터 페이지의 메타 정보만 가져와서 진행한다.
  • 백업은 메타데이터만 저장하기 때문에 속도가 빠르지만 백업한걸 버퍼 풀에 가져오려고 하면 디스크에 직접 읽기 작업이 들어가기 때문에 백업보다 시간이 걸린다.

6. 버퍼 풀의 적재 내용 확인

  • innodb_cached_indexes 를 통해서 sql 5.6 버전에서는 보여주지 못한 버퍼 풀에 있는 테이블을 볼 수 있게 되었다.

Double Write Buffer

  • 리두 로그는 데이터 페이지의 변경사항을 전부 기록하는게 아니라 변경 된 부분만 기록하기 때문에 더티페이지를 디스크에 기록을 할때 문제가 발생하면 페이지 내용을 복구 못할 수 있다.
  • 이런 상황을 해결하기 위해서 Double Write Buffer 를 이용하는데 더티페이지가 디스크에 플러시 하기 이전에 시스템 테이블 스페이스의 Double Write Buffer 에 기록한다.
  • 더티페이지에서 디스크로 정상적으로 데이터 플러시가 이뤄 졌다면 Double Write Buffer는 필요없겠지만 비정상적인 종료가 되었을때 더티 페이지에 복구를 하기 위해서 여기 있는 Double Write Buffer 에 있는 내용과 데이터 파일의 페이지들을 모두 비교해서 다른 내용을 담고 있으면 Double Write Buffer 에 있는 내용을 페이지로 복사한다.
  • 데이터의 무결성을 중요하게 여기면 Double Write Buffer 를 활성화 해놓는 것이 좋다. 리두로그는 비활성화 하고 Double Write Buffer만 활성화 하는것은 맞지 않다.

언두 로그

  • 트랜잭션 보장 (일관성)
    • 사용자가 트랜잭션을 롤백하면 이전 데이터를 가지게 되는것?
  • 격리 수준 보장 (격리성)
    • 사용자가 데이터를 조작하는 와중에 다른 사용자가 데이터를 조회하면 조작전 즉 커밋전 데이터를 볼 수 있는것?
  1. 언두 로그 모니터링

image.png

  • 시나리오별 동작

(1). 트랜잭션 A : BEGIN → SELECT

  • BEGIN:
    • 트랜잭션 A는 시작되며, 데이터의 스냅샷이 생성된다. (예 : REPEATABLE READ 격리 수준)
    • 스냅샷은 언두 로그를 참조하여 트랜잭션 A가 “변경되지 않은 데이터”를 볼 수 있도록 한다.
  • SELECT :
    • 트랜잭션 A 는 버퍼 풀에서 데이터를 읽는다.
    • 만약 해당 데이터가 다른 트랜잭션에 의해 변경되었다면, 언두 로그를 통해 변경 전 버전을 참조한다.

(2) 트랜잭션 B : BEGIN → UPDATE → COMMIT

  • BEGIN
    • 트랜잭션 B 가 시작된다.
  • UPDATE :
    • 트랜잭션 B 는 버퍼 풀에서 데이터를 가져와 수정한다.
    • 수정한 데이터는 더티 페이지로 버퍼풀에 저장된다.
    • 언두 로그에는 변경전 데이터가 기록된다.
  • COMMIT :
    • 트랜잭션 B 가 완료되고, 변경 사항이 리두 로그와 버퍼 풀에 남아 있으며, 체크포인트 시 디스크로 플러시된다.
    • 트랜잭션 A는 언두 로그를 통해 변경 전 데이터를 읽을 수 있다.

(3) 트랜잭션 C : BEGIN → DELETE → COMMIT

  • BEGIN
    • 트랜잭션 C 가 시작된다.
  • DELETE
    • 트랜잭션 C는 버퍼 풀에서 데이터를 삭제한다.
    • 삭제전 데이터는 언두 로그에 기록된다.
    • 다른 트랜잭션은 언두 로그를 통해 삭제 전 데이터를 참조할 수 있다.
  • COMMIT
    • 트랜잭션 C 가 완료되고, 삭제된 데이터가 버퍼 풀에서 제거 된다.
  • 트랜잭션이 계속 열려있으면 언두로그가 계속 쌓이기 때문에 서버에 문제가 발생할 수 있다. 또한 한번 늘어난 언두로그의 용량은 서버를 새로 구축하지 않는한 줄일 수 없다. 이러한 문제들은 SQL 8.0 버전에서 해결될 수 있었는데. 늘어난 언두로그를 돌아가면서 순차적으로 사용해서 디스크 공간을 줄여주던가 언두로그의 용량 자체를 순차적으로 줄여주기도 한다.
  • INSERT 와 UDPATE, DELETE 의 언두로그는 따로 관리된다. UPDATE, DELETE 문장으로 인한 언두 로그는 데이터 복구, MVCC 에 모두 사용되지만 INSERT 문장은 MVCC 에 사용되지 않고 데이터 복구에만 사용된다.
  1. 언두 테이블스페이스 관리
    • 언두 로그가 저장되는 공간을 언두 테이블 스페이스라고 한다. SQL 5.6 이전에는 언두 로그를 시스템 테이블 스페이스에 저장을 시켰다. 이건 서버 초기화 될때 시스템 테이블 스페이스가 생성되므로 언두로그를 확장시키는데 문제가 있었다. SQL 5.6 버전에서는 innodb_undo_tablespaces 시스템 변수가 도입 됐고 시스템 변수를 2보다 큰값으로 하면 시스템 테이블스페이스에 저장을 하는게 아니라 별도의 언두 로그 파일에 저장을 시켰다. SQL 8.0 버전에서는 언두 로그를 항상 시스템 테이블 스페이스 외부의 별도 로그 파일에 기록되도록 개선시켰다.
    • 하나의 언두 테이블스페이스는 1개이상 128개 이하의 롤백 세그먼트를 가지며, 롤백 세그먼트는 1개이상의 언두 슬롯을 가진다. 하나의 롤백 세그먼트는 InnoDB 의 페이지 크기의 16바이트로 나눈 값의 개수만큼 언두 슬롯을 가진다.
    • 최대 동시 트랜잭션 수 = (InnoDB 페이지 크기) / 16 (롤백 세그먼트 개수) ( 언두 테이블스페이스 개수)
    • MySQL 8.0 버전 이후 부터는 언두 테이블 스페이스의 공간을 추가하거나 삭제할 수 있게 되었다.
    • 과도하게 할당된 언두 테이블 스페이스를 다시 운영체제로 반납하는 것을 ‘Undo tablespace truncate”이라고 한다. 반납을 자동, 수동으로 할 수 있는데
    • 자동은 InnoDB 스토리지 엔진의 퍼지 스레드가 주기적으로 언두 로그 공간에 불필요한 언두 로그를 삭제한다. 이 작업을 언두 퍼지(Undo Purge) 라고 한다 MySQL 서버의 innodb_undo_log_truncate 시스템 변수가 ON 으로 설정되면 퍼지 스레드가 자동으로 언두로그 를 삭제한다.
    • 수동은 innodb_undo_log_truncate 시스템 변수가 OFF 됐을때 퍼지 스레드가 자동으로 언두로그를 삭제 하지 않을때 언두 테이블스페이스를 비활성화 시키면 퍼지스레드가 비활성화 된 언두 테이블스페이스를 찾아서 불필요한 공간을 찾아내서 운영체제로 공간을 반납한다.

체인지 버퍼

  • INSERT 하거나 UPDATE 할때 INDEX 도 같이 변경을 해주어야 한다. 인덱스 페이지가 버퍼 풀에 존재한다면 버퍼 풀에서 변경을 하면 되지만 버퍼 풀에 존재 하지 않는다면 디스크에 랜덤 읽기로 시간이 많이 소요가 된다. 그런 경우를 대비해서 체인지 버퍼 라는 곳에 저장을 하고 사용자에게 결과를 반환하는 형태로 성능을 향상시켰다.
  • 체인지 버퍼에 임시로 저장된 인덱스 레코드 조작은 머지 스레드를 통해서 병합된다.
  • 유니크 인덱스는 체인지 버퍼를 사용할 수 없는데 그 이유는 체인지 버퍼는 디스크를 읽지 않고 지연처리를 전제로 하는데 유니크 인덱스는 중복 확인을 위해 디스크 페이지를 읽어야 한다.
  • 유니크 인덱는 현재 상태를 즉시 확인해야 하기 때문에, 체인지 버퍼의 “지연 처리” 방식과 충돌한다. 그렇기 때문에 유니크 인덱스는 체인지 버퍼를 사용할 수 없고, 변경 전 작업 시 즉시 디스크 페이지를 읽어 반영해야 한다.

리두 로그 및 로그 버퍼

  • ACID 의 D (Durable) 은 리두 로그와 연관되어있다.
  • 리두 로그는 비정상적인 종료가 발생했을때 데이터 파일을 다시 서버가 종료되기 직전의 상태로 복구해준다.
    • innodbflush_log_at *trx*commit = 0 : 1초에 한 번씩 리두 로그를 디스크로 기록하고 동기화 실행
    • innodb_flush_log_at_trx_commit = 1 : 매번 트랜잭션이 커밋될 때마다 디스크로 기록되고 동기화까지 수행한다.
    • innodb_flush_log_at_trx_commit = 2 : 매번 트랜잭션이 커밋될 때마다 디스크로 기록은 되지만 실질적인 동기화는 1초에 한 번씩 실행된다. 일단 트랜잭션이 커밋되면 변경 내용이 운영체제의 메모리 버퍼로기록되는것이 보장되어서 SQL서버가 비정상적으로 종료 됐다고 하더라도 운영체제가 정상적으로 작동하면 해당 트랜잭션 데이터는 사라지지 않는다.
    • innodb_flush_log_at_timeout 시스템 변수를 통해서 디스크 동기화 시간을 변견할 수 있다. 기본 시간은 1초
  • 리두 로그 아카이빙
  • 리두 로그 활성화 및 비활성화

어댑티브 해시 인덱스

  • InnoDB 에서 사용자가 자주 사용하는 데이터를 기반으로 자동으로 인덱스 생성(B-TREE의 검색 시간을 줄여주기 위해서 존재)
  • InnoDB 버퍼풀에 있는 데이터기반으로 어댑티브 해시 인덱스가 존재하며 버퍼풀에 데이터 페이지가 제거되면 AHI도 삭제된다.
  • AHI KEY는 B-TREE 의 인덱스 고유번호와 B-TREE 인덱스 실제 키 값으로 구성되며 VALUE 는 버퍼 풀에 있는 데이터 페이지 주소다.
  • 어댑티브 해시 인덱스가 성능향상에 크게 도움 되지 않는 경우
    1. 디스크 읽기가 많은경우
    2. 특정 패턴의 쿼리가 많은 경우(조인이나 LIKE 패턴 검색)
    3. 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
  • 어댑티브 해시 인덱스가 성능 향상에 크게 도움 되는 경우
    1. 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)
    2. 동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우
    3. 쿼리가 데이터 중에서 일부 데이터만 집중되는 경우

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

  • InnoDB 가 MyISAM 보다 나은점
    1. 현재 MyISAM 의 모든 기능이 InnoDB 로 대체 가능하다.
  • InnoDB가 Memory 스토리지 엔진보다 나은점
    1. Memory 스토리지 엔진은 테이블 수준의 잠금을 사용하기 때문에 동시 처리 성능이 InnoDB 스토리지 엔진보다 부족하다.

3. MyISAM 스토리지 엔진 아키텍처

키 캐시

  • InnoDB 의 버퍼 풀과 비슷한 역할을 한다.
  • 인덱스를 대상으로만 작동하며, 인덱스의 디스크 쓰기 작업에 대해서 부분적으로 버퍼링 역할을 한다.

운영체제의 캐시 및 버퍼

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

  • 프라이머리 키에 의한 클러스터링이 없고 데이터 파일이 힙 공간처럼 활용 된다.
  • INSERT 되는 순서대로 차곡차곡 쌓인다는 뜻
  • MyISAM 테이블에 저장되는 레코드는 모두 ROWID라는 물리적 주솟값을 가지는데 프라이머리 키와 세컨더리 인덱스는 모두 데이터 파일에 저장된 레코드의 ROWID 값을 포인터로 가진다
  • 고정 길이 ROWID
  • 가변 길이 ROWID

4. 로그 파일

에러 로그 파일

  • my.cnf 에서 log_error라는 이름의 파라미터로 정의된 경로에 생성된다.

MySQL이 시작하는 과정과 관련된 정보성 및 에러 메시지

마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB의 트랜잭션 복구 메시지

쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지

비정상적으로 종료된 커넥션 메시지(Aborted connection)

InnoDB의 모니터링 또는 상태 조회 명령(SHOW ENGINE INNODB STATUS 같은)의 결과 메시지

MySQL의 종료 메시지

제네럴쿼리 로그 파일(제네럴 로그 파일, General log)

슬로우 쿼리 로그

  • 서비스 도중에 쿼리의 성능을 파악하기 위함
  1. 슬로우 쿼리 통계
  2. 실행 빈도 및 누적 실행 시간순 랭킹
  3. 쿼리별 실행 횟수 및 누적 실행 시간 상세정보
profile
진성대
주니어 개발자
다음 포스트

트랜잭션과 잠금

0개의 댓글