2025-08-18

오늘 배운 것

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

  • InnoDB는 MySQL에서 사용할 수 있는 스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금을 제공
    • 높은 동시성 처리가 가능
    • 안정적이며 성능이 뛰어남

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

  • InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링 되어 저장
    • 클러스터링 : 테이블의 저장순서를 인덱스 순서와 일치시키는 구조
    • 따라서 프라이머리 키를 이용한 범위 스캔은 상당히 빨리 처리 가능
    • 모든 세컨더리 인덱스(pk가 아닌 컬럼으로 만든 추가 B+트리)는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 활용

4.2.2 외래키 지원

  • InnoDB 스토리지 엔진 레벨에서만 외래키에 대한 지원함
  • InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요
    • 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크 필요
    • 따라서 잠금이 여러 테이블로 전파되고 그로인해 데드락 발생 다수
      • 개발할 때 외래 키의 존재에 주의

4.2.3 MVCC(Multi Version Concurrency Control)

  • 일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능(즉 InnoDB만 제공)
  • 잠금을 사용하지 않는 일관된 읽기 제공
  • 트랜잭션 격리 수준에 따른 읽는 데이터 영역 차이
    • READ_UNCOMMITED : 버퍼풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환
    • READ_COMMITED, REPEATABLE_READ, SERIALIZABLE : 변경되기 이전 내용을 보관하고 있는 언두 영역의 데이터 반환

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

  • InnoDB 스토리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행
  • 격리 수준이 SERIALIZABLE이 아닌 다른 수준들인 경우 INSERT와 연결되지 않은 순수한 읽기 작업은 다른 트랜잭션의 변경 작업과 관계 없이 항상 잠금을 대기하지 않고 바로 실행
  • 오랜시간동안 활성 상태인 트랜잭션으로 인해 MySQL 서버가 느려지거나 문제가 발생 원인
    • 이러한 일관된 읽기를 위해 언두로그를 삭제하지 못하고 계속 유지해야 하기 때문
    • 트랜잭션이 시작됐다면 가능한 한 빨리 롤백이나 커밋을 통해 트랜잭션을 완료하는 것이 좋음

4.2.5 자동 데드락 감지

  • InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프 형태로 관리
  • 데드락 감지 스레드 : 주기적으로 잠금 대기 그래프를 검사해 교착상태에 빠진 트랜잭션을 찾아서 그 중 하나를 강제 종료
    • 강제 종료 판단 기준 : 트랜잭션의 언두 로그양이 더 적을 수록 강제 종료의 대상이 됨
      • 이유
        • 롤백을 해도 언두 처리를 해야할 내용이 적음
        • 트랜잭션 강제 롤백으로 인한 MySQL 서버의 부하도 덜 유발
  • innodb_table_locks : 활성화 시키면 MySQL 엔진에서 관리되는 테이블 레벨의 잠금까지 InnoDB 스토리지 엔진이 감지할 수 있음
  • innodb_deadlock_detect : 비활성화 시키면 데드락 감지 스레드가 더는 작동하지 않게 됨
  • innodb_lock_wait_timeout : 활성화 시켜 초단위로 설정하면 잠금을 설정한 시간 동안 획득하지 못하면 쿼리는 실패하고 에러 반환
    • 데드락 감지 스레드가 부담되어 innodb_deadlock_detect 를 off로 사용중이라면 innodb_lock_wait_timeout 를 기본값 50초보다 훨씬 낮은 시간으로 변경해서 사용할 것을 권장

4.2.6 자동화된 장애 복구

  • InnoDB 데이터 파일은 기본적으로 MySQL 서버가 시작될 때 항상 자동 복구를 수행
  • innodb_force_recovery : 자동 복구될 수 없는 손상 발생시 설정하고 서버 시작해야함
    • 로그 파일 손상시 → 6
    • 테이블의 데이터 파일 손상 시 → 1
    • 문제를 알수 없을 시 1부터 하나씩 재시작 해보고 점차 6까지 올림
      • 1 → 6으로 갈수록 심각한 상황이어서 데이터 손실 가능성이 커지고 복구 가능성 적어짐
  • 해당 변수를 설정해서 복구모드로 서버를 기동하고 테이블 인식 되면 mysqldump를 이용해 데이터를 가능한 만큼 백업하고 그 데이터로 MySQL 서버의 DB와 테이블을 다시 생성하는 것이 좋음
    • 복구모드에서는 조회 이외의 쿼리는 불가능

4.2.7 InnoDB 버퍼 풀

  • 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간
  • 쓰기 작업을 지연시켜 일괄작업으로 처리할 수 있게 해주는 버퍼 역할도 함

4.2.7.1 버퍼 풀의 크기 설정

  • 운영체제와 각 클라이언트 스레드가 사용할 메모리도 충분히 고려해서 설정
  • 설정 방법 추천
    • 가능한 InnoDB 버퍼 풀의 크기를 적절히 작은 값으로 설정해서 조금씩 상황을 봐 가면서 증가시키기
    • 운영체제 공간 8GB 미만 → 50%
    • 운영체제 공간 50GB 이상 → 20~35GB
  • innodb_buffer_pool_size : 버퍼 풀 크기 설정
  • 동적으로 버퍼 풀의 크기를 확장할 수 있으나 크리티컬한 변경이기에 한가한 시점에서 진행
  • 크기를 줄이는 과정은 시스템 영향이 크기 때문에 가능한 해당 작업을 하지 않도록 주의
  • 버퍼풀 크기 증가/감소 단위 → 128 MB

4.2.7.2 버퍼 풀의 구조

  • InnoDB 스토리지 엔진은 버퍼풀이라는 거대한 메모리 공간을 페이지 크기의 조각으로 쪼개어 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장
  • LRU 리스트
    • LRU(Least Recently Used) + MRU(Most Recently Used)
    • 디스크로부터 새로 읽어온 페이지는 LRU 헤더에 추가
    • 버퍼 풀에 있는 페이지를 실제로 읽으면 MRU 헤더로 이동
    • LRU tail에 있는 페이지를 제거
  • 플러시 리스트
    • 디스크로 동기화 되지 않은 데이터를 가진 데이터 페이지(더티페이지)의 변경 시점 기준의 페이지 목록을 관리
    • 데이터가 변경되면 리두로그에 기록하고 버퍼풀의 데이터 페이지에도 변경 내용을 반영
      • 리두 로그의 각 엔트리는 특정 데이터 페이지와 연결
    • 체크포인트 : 디스크의 리투 로그와 데이터 페이지의 상태를 동기화
      • 리두 로그가 디스크로 기록됐다고 해서 데이터 페이지가 디스크로 기록됐다는 것을 항상 보장하지 않음
      • 그 반대도 마찬가지임 따라서 체크 포인트를 활용해 동기화를 주기적으로 시켜줌
      • 체크 포인트는 리두로그의 어느 부분부터 복구를 실행해야할지 판단하는 기준점을 만들어줌
  • 프리 리스트
    • 실제 사용자 데이터로 채워지지 않은 페이지들의 목록
    • 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용

4.2.7.3 버퍼 풀과 리두 로그

  • 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정하면 할수록 쿼리의 성능이 빨라짐
  • 버퍼풀의 역할
    • 데이터 캐시
      • 공간 늘리기로 성능향상 가능
    • 쓰기 버퍼링
      • 리두 로그 파일의 크기를 적절히 선택하고 최적값을 찾아나가는 과정으로 성능 향상 가능
  • 클린 페이지 : 디스크에서 읽은 상태로 변경되지않음
  • 더티 페이지 : 변경된 데이터
  • 리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용
    • 활성 리두 로그 : 재사용 불가능한 공간
  • LSN(Log Sequence Number) : 리두로그 파일일 기록될 때마다 증가한 로그 포지션 값
  • 체크포인트 에이지 : 활성 리두 공간의 크기
  • 버퍼 풀의 더티 페이지는 특정 리두 로그 엔트리와 관계를 가지고, 체크포인트가 발생하면 체크 포인트 LSN보다 작은 리두 로그 엔트리와 관련된 더티 페이지는 모두 디스크로 동기화 돼야 한다.

4.2.7.4 버퍼 풀 플러시

  • InnoDB 스토리지 엔진은 버퍼 풀에서 아직 디스크로 기록되지 않은 더티 페이지들을 성능상의 악영향 없이 디스크에 동기화하기 위해 2개의 플러시 기능을 백그라운드로 실행

4.2.7.4.1 플러시리스트 플러시

  • 리두 로그 공간의 재활용을 위해 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워야 함
  • 이 때 반드시 버퍼 풀의 더티 페이지가 먼저 디스크로 동기화 되어야 함
  • 이를 위해 InnoDB 스토리지 엔진은 주기적으로 플러시 리스트 플러시 함수를 호출해서 플러시 리스트에서 오래 전에 변경된 데이터 페이지에 순서대로 디스크에 동기화하는 작업을 수행
  • 클리너 스레드 : 스토리지 엔진에서 디티 페이지를 디스크로 동기화하는 스레드
  • 어댑티브 플러시 알고리즘 : 리두 로그의 증가속도를 분석해서 적절한 수주느이 더티 페이지가 버퍼 풀에 유지될 수 있도록 쓰기를 실행

4.2.7.4.2 LRU 리스트 플러시

  • 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 공간 생성
  • 더티 페이지는 디스크에 동기화, 클린 페이지는 즉시 프리리스트로 페이지 이동
  • 인스턴스 별로 최대 innodb_lru_scan_depth 개수 만큼만 스캔

4.2.7.5 버퍼 풀 상태 백업 및 복구

  • 워밍업 : 디스크의 데이터가 버퍼 풀에 적재돼 있는 상태
    • 워밍업 안되어 있는 상태보도 몇십배 쿼리 처리속도를 보임
    • MySQL 5.6번 부터 버퍼 풀 덤프 및 적재기능 도입되어 자동으로 워밍업 가능
  • 버퍼 풀의 백업은 데이터 페이지의 메타 정보만 가져옴
  • 따라서 다시 버퍼 풀로 복구하는 과정은 상당한 시간이 걸리 수 있음

4.2.7.6 버퍼 풀 적재 내용 확인

  • MySQL 5.6버전 부터 information_schema 데이터베이스의 Innodb_buffer_page 테이블을 이용해 버퍼 풀 메모리에 적재된 테이블의 페이지를 확인 할 수 있음
    • 하지만 버퍼 풀이 큰 경우에 테이블 조회가 상당히 큰 부하를 일으키면서 서비스 용 서버에서는 버퍼 풀 상태 확인 거의 불가능
  • MySQL 8.0 버전 부터 information_schema 데이터베이스에 innodb_cashed_indexes테이블이 새로 추가되어 테이블의 인덱스별로 데이터 페이지가 얼마나 버퍼풀에 적재돼 있는지 확인 가능

4.2.8 Double Write Buffer

  • 파셜 페이지 OR 톤 페이지 : 하드웨어의 오작동이나 시스템의 비정상 종료 등으로 인해 더티 페이지를 디스크 파일로 플러시할 때 일부만 기록되는 문제
  • 해결책 → Double-Write
    • 실제 데이터 파일에 변경 내용을 기록하기 전에 더티 페이지들을 우선 묶어서 한번의 디스크 쓰기로 시스템 테이블 스페이스의 double write 버퍼에 기록
    • 그후 스토리지 엔진이 각 더티 페이지를 적당한 위치에 랜더 스기 실행
    • 만약 중간에 실패하면 스토리지 엔진이 재시작 될 때 double write 버퍼의 내용과 데이터 파일의 페이지들을 모두 비교해서 다른 내용을 담고 있는 페이지가 있으면 더블 라이트 버퍼의 내용을 데이터 파일 페이지로 복사
  • SSD는 랜덤 IO,순차 IO의 비용이 비슷하여 더블 라이트가 부담스러울 수 있음
  • 데이터 무결성이 중요하다면 사용하는 것을 고려해봐야함
  • 만약 리두 로그 동기화 설정이 1이 아니면 더블라이트도 비활성화하는 것이 좋음

4.2.9 언두 로그

  • 스토리지 엔진이 트랜잭션과 격리 수준을 보장하기 위해 DML로 변경되기 이전 버전의 데이터를 별도로 백업
    • 트랜잭션 보장
    • 격리수준 보장

4.2.9.1 언두 로그 모니터링

  • 트랜잭션이 장시간 유지되었을 때, 만약 그동안 빈번하게 변경된 레코드를 조회하게 되면 언두 로그 이력을 필요한 만큼 스캔해야 되기 때문에 쿼리 성능이 전반적으로 떨어진다.
  • 따라서 언두 로그가 얼마나 증가했는지 항상 모니터링 하는 것이 좋다
  • 서버별로 안정적인 시점의 언두 로그 건수를 확인해 이를 기준으로 언두 로그의 급증 여부를 모니터링 하는 것이 좋음

4.2.9.2 언두 테이블스페이스 관리

  • 테이블 스페이스 : 언두로그가 저장되는 공간
  • MySQL 8.0부터 언두 로그는 항상 시스템 테이블 스페이스 외부의 별도 로그 파일에 기록되도록 개선 됨
  • 하나의 언두 테이블 스페이스에는 1 ~ 128개의 콜백 세그먼트를 가짐
  • 콜백 세그먼트는 1개 이상의 언두 슬롯을 가짐
  • Undo tablespace truncate : 언두 테이블 스페이스 공간을 필요한 만큼만 남기고 불필요하거나 과도하게 할당된 공간을 운영체제로 반납하기
    • 자동 : 퍼지 스레드가 주기적으로 깨어나서 언두 로그 공간에서 불필요해진 언두 로그를 삭제
    • 수동 : 언두 테이블 스페이스를 비활성화해서 언두 테이블 스페이스가 더이상 사용되지 않도록 설정하면 퍼지 스레드가 비활성 상태의 언두 테이블 스페이스를 찾아서 불필요한 공간을 잘라내고 운영체제로 해당 공간 반납. 그후 언두 테이블 스페으 다시 활성화. 수동 모드는 최소 2개의 언두 테이블 스페이스 필요

4.2.10 체인지 버퍼(=인서트 버퍼)

  • 인덱스를 업데이트 할 때 디스크로부터 읽어와서 업데이트 해야한다면 즉시 실행하지않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상 시킴
    • 이때의 임시공간 → 체인지 버퍼
  • 유니크 인덱스는 체인지 버퍼를 사용할 수 없음
  • 체인지 버퍼 머지 스레드 : 체이지 버퍼에 임시로 저장된 인덱스 레코드 조각을 병합시킴
  • 기본적으로 버퍼풀 메모리 공간의 25%까지 사용가능하고 필요하다면 50%까지도 가능

4.2.11 리두 로그 및 로그 버퍼

  • 리두 로그 : 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안정장치
  • 로그 버퍼 : 버퍼 풀리안 리두로그를 버퍼링함
  • 커밋됐지만 데이터 파일에 기록되지 않은 데이터
    • 리두 로그 다시 데이터 파일에 복사
  • 롤백됐지만 데이터 파일에 이미 기록된 데이터
    • 언두 로그의 내용을 데이터 파일 복사 + 리두 로그로 변경의 커밋, 롤백, 실행 중간 상태 인지 확인

4.2.11.1 리두로그아카이빙

  • 백업을 하는 동안 데이터 변경이 많아서 리두로그가 덮어쓰인다고 하더라도 실패하지 않게 해줌

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

  • 데이터를 복구하거나 대용량 데이터를 한번에 적재하는 리두로그를 비활성화해서 데이터의 적재시간을 단축 시킬 수 있음

4.2.12 어댑티브 해시 인덱스

  • InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스
  • B-Tree 검색 시간을 줄여주기 위해 도입된 기능
  • 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있음
  • 해시 인덱스
    • 인덱스 키값(B-Tree 인덱스 고유번호 + B-Tree 인덱스의 실제 키값)과 해당 인덱스 키값이 저장된 데이터 페이지 주소의 쌍으로 관리
    • 데이터 페이지 주소는 버퍼풀의 로딩된 페이지의 주소를 의미함 따라서 버퍼풀에서 사라진 페이지는 어댑티브 해시 인덱스에서도 사라짐
  • 성능 향상 좋을 때
    • 디스크 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)
    • 동등 조건 검색이 많은 경우
    • 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우
    • 즉, 어댑티브 해시 인덱스는 버퍼 풀 내에서 접근하는 것을 더 빠르게 만드는 기능이므로 디스크에서 읽어오는 경우가 빈번한 데이터 베이스 서버에서는 아무런 도움이 되지 않음

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

  • InnoDB가 두 스토리지 엔진보다 훨씬 성능이 좋아 이 둘은 사라질 것~

4.3 MyISAM 스토리지 엔진 아키텍처

4.3.1 키 캐시

  • InnoDB의 버퍼 풀과 비슷한 역할을 함
  • 차이점
    • 인덱스 대상으로만 작동
    • 인덱스 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링

4.3.2 운영체제의 캐시 및 버퍼

  • MyISAM에는 디스크로부터의 I/O를 해결해줄만한 어떠한 캐시나 버퍼링 기능을 가지고 있지 않음
  • 그래서 운영체제의 캐시나 버퍼링 메커니즘에 의존적
  • 따라서 키캐시가 최대 물리 메모리의 40%이상을 넘지 않게 하고 나머지 메모리 공간은 운영체제가 자체적인 파일 시스템을 위한 캐시 공간을 마련할 수 있게 해주는 것이 좋음

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

  • MyISAM은 데이터 파일이 힙 공간처럼 INSERT 되는 순서대로 데이터 파일에 저장
  • 저장되는 레코드들은 모두 ROWID라는 물리적인 주솟값을 가짐
  • 프라이머리키와 세컨더리 인덱스 모두 ROWID 값을 포인터로 가짐
  • ROWID 저장 방식
    • 고정 길이
      • 4바이트 정수
    • 가변길이
      • 기본값 7바이트로 2 ~ 7바이트까지의 가변적인 ROWID가지게 됨
      • 1번째 바이트는 ROWID 길이를 저장하는 용도 나머지 공간은 실제 ROWID 저장

results matching ""

    No results matching ""