[Real MySQL8.0 스터디 week1] 4.2장

Coen·2023년 1월 28일
1

Real MySQL Study

목록 보기
2/5
post-thumbnail

이 글은 Real MySQL 스터디에서 지원님이 발표한 발표자료입니다.






InnoDB는 MySQL에서 사용할 수 있는 스토리지 엔진 중 거의 유일하게 레코드 기반 잠금을 제공하며, 그로 인해 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다. 주요 기능들을 알아보자. ![](https://velog.velcdn.com/images/coen/post/6ea0d395-f3a4-4dd4-8961-c3adad49454d/image.png) # 1. 프라이머리 키에 의한 클러스터링

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

  • 즉, 프라이머리 키 값의 순서대로 디스크에 저장된다.
  • 모든 세컨더리 인덱스는 물리적인 레코드의 주소(ROWID) 대신 프라이머리 키의 값을 논리적인 주소로 사용한다.
  • 결과적으로 쿼리 실행 계획에서 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정된다. (우선 순위에 있다는 의미인 듯)

2. 외래 키 지원

외래 키 지원은 InnoDB 엔진에서만 지원하는 기능이다

  • 외래 키는 부모, 자식 테이블의 해당 칼럼에 인덱스 생성이 필요하고, 변경 시에는 데이터를 체크해야 하므로 잠금이 여러 테이블로 전파되고, 데드락이 발생할 수 있어 조심해야 한다.
  • 제약 조건을 걸게 되면 수동으로 데이터를 적재하거나 스키마 변경 등의 관리 작업이 실패할 수 있다. 부모와 자식 테이블의 관계를 명확히 파악하여 순서대로 작업한다면 문제없이 실행될 수 있지만, 외래키가 복잡하게 얽혀있는 경우에는 파악하기가 쉽지 않다.
    • 이 경우에는 foreign_key_check 시스템 변수를 off로 설정하여, 외래 키 관계 체크 작업을 일시적으로 멈출 수 있고, 부가적인 외래키 조건 체크를 하지 않아서 더 빠르게 작업을 처리할 수 있다.
    • 참고로 이 옵션을 해제한 경우, ON DELETE CASCADE, ON UPDATE CASCADE 옵션도 무시된다.
    • 이 변수는 글로벌과 세션 영역 모두 적용할 수 있어서 현재 실행 중인 세션에만 적용하고, 작업이 완료되면 세션을 종료하거나 다시 활성화해야 한다.
      SET SESSION foreign_key_checks=OFF; // SESSION 키워드가 없으면 글로벌로 동작
      
      // 작업 실행
      
      SET SESSION foreign_key_checks=ON;

3. MVCC(Multi Version Concurrency Control)

레코드 레벨의 트랜잭션을 지원하는 DBMS가 지원하는 기능이며, 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 데 있다.

InnoDB에서는 언두 로그를 이용하여 이 기능을 구현한다.

  1. Insert
INSERT INTO member (m_id, m_name, m_area) VALUES (12, '홍길동', '서울');

  1. Update
UPDATE member SET m_area='경기' WHERE m_id=12;

UPDATE 문장이 실행되면 커밋 실행 여부와 관계 없이 InnoDB의 버퍼 풀은 새로운 값인 경기로 업데이트된다.그리고 디스크의 데이터 파일에는 체크포인트나 InnoDB의 Write 스레드에 의해 새로운 값으로 업데이트돼 있을 수도 있고 아닐 수도 있다. (InnoDB가 ACID를 보장하기 때문에 일반적으로는 InnoDB의 버퍼 풀과 데이터 파일은 동일한 상태라고 가정해도 무방하다.)

  1. Commit 되지 않은 데이터 Read
SELECT * FROM member WHERE m_id=12;

MySQL 서버의 시스템 변수(transaction_isolation)에 설정된 격리 수준(Isolation level)에 따라 결과가 달라진다.

  • READ_UNCOMMITTED인 경우 ⇒ InnoDB 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환한다. 즉, 데이터가 커밋됐든 아니든 변경된 상태의 데이터를 반환한다.
  • READ_COMMITED나 그 이상의 격리수준 ⇒ 아직 커밋되지 않았기 때문에 변경되기 이전의 내용을 보관하고 있는 언두 영역의 데이터를 반환한다.

이러한 과정을 DBMS에서는 MVCC라고 표현한다.

이 상태에서 COMMIT 명령을 실행하면 InnoDB는 더 이상의 변경 작업 없이 지금의 상태를 영구적인 데이터로 만들고, 언두 영역의 데이터를 더 이상 필요로 하는 트랜잭션이 없으면 삭제한다. 하지만 롤백을 실행하면 InnoDB는 언두 영역에 있는 백업된 데이터를 InnoDB 버퍼 풀로 다시 복구하고, 언두 영역의 내용을 삭제해 버린다.

4. 잠금 없는 일관된 읽기

InnoDB 스토리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다.때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고 읽기 작업이 가능하다.

격리 수준이 SERIALIZABLE 보다 낮다면 INSERT와 연결되지 않은 순수한 SELECT 작업은 다른 트랜잭션의 변경 작업과 관계 없이 항상 잠금을 대기하지 않고 바로 실행된다.

따라서 특정 사용자가 레코드를 변경하고 아직 커밋을 수행하지 않았더라도 이 변경 트랜잭션이 다른 사용자의 SELECT 작업을 방해하지 않는다.

이를 잠금 없는 일관된 읽기라고 표현하며, InnoDB에서는 변경되기 전의 데이터를 읽기 위해 언두 로그를 사용한다. 단, 트랜잭션이 계속 활성 상태이면 언두 로그를 삭제하지 못하고 계속 유지하기 때문에, 트랜잭션이 시작됐다면 가능한 빨리 롤백이나 커밋을 하는 게 좋다.

5. 자동 데드락 감지

  • 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프(Wait-for List)형태로 관리한다.
  • 데드락 감지 스레드가 주기적으로 그래프를 검사해 교착상태의 트랜잭션을 종료하는데, 데드락이 발생하면 언두 로그의 양이 적은 트랜잭션을 롤백한다. 왜냐하면 언두 로그 양이 적을 수록 처리할 롤백 작업이 적은 것이므로 부하가 적기 때문이다.
  • 테이블 락은 MySQL 엔진에서 관리하므로 InnoDB 스토리지 엔진에서 데드락 감지가 불확실해질 수 있는데, innodb_table_locks시스템 변수를 활성화하면 테이블 레벨의 락도 감지 가능하므로 가능하면 활성화하자.
  • 동시 처리 스레드가 많거나, 트랜잭션들의 잠금이 많아지면 데드락 감지 스레드가 느려진다.
    • 데드락 감지 스레드는 잠금을 검사하는 동안 잠금 상태가 변경도지 않도록 잠금 목록이 저장된 리스트에 새로운 잠금을 걸어 상태 변경을 막는데, 이 때문에 데드락 감지 스레드가 느려지면 서비스 쿼리를 처리 중인 스레드가 작업을 불가해서 성능에 좋지 않다.
    • innodb_deadlock_detect 변수를 통해 적절히 비활성화하면, 데드락이 발생했을 때 무한정 대기한다. 이때, innodb_lock_wait_timeout 변수를 설정하여 데드락 상황에 일정 시간(초)이 지나면 요청 실패 후 에러 메시지를 반환하게 할 수 있다.

6. 자동화된 장애 복구

  • InnoDB 데이터 파일은 기본적으로 MySQL서버가 시작될 때 항상 자동 복구 수행한다.
    • 이 때 복구될 수 없는 손상이 있으면 서버 종료된다.
    • innodb_force_recovery 변수를 1~6(값이 클수록 손상 정도가 커서 복구 가능성이 적어짐)으로 설정하면 데이터 파일이나 로그 파일의 손상 여부 검사 과정을 선별적으로 진행할 수 있다.
  • 서버 기동 후 InnoDB 테이블이 인식되면 mysqldump를 이용해 데이터를 가능한 만큼 백업하고 DB와 테이블을 다시 생성하는 것이 좋다.

7. InnoDB 버퍼 풀

  • InnoDB 스토리지 엔진에서 가장 핵심적인 부분
  • 기능
    1. 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간

    2. 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할

      : 데이터 변경 시 랜덤 I/O가 많이 발생할 수 있는데, 이를 모아서 처리하여 랜덤 I/O를 줄임

버퍼 풀 크기 설정

  • 운영체제와 각 클라이언트 스레드가 사용할 메모리를 충분히 고려해서 설정하자.
  • 5.7버전부터는 InnoDB 버퍼 풀 크기를 innodb_buffer_pool_size동적으로 조절 가능하다. 운영체제 전체 메모리 공간에 따라서 얼마나 할당하는 게 좋을지 달라진다. (자세한 가이드는 책을 참고) 기본적으로 적게 설정해서 상황에 따라 조금씩 증가시켜보는 방식이다. 단, 버퍼 풀 크기를 조절하는 작업은 서비스에 크리티컬하므로 공식 문서를 반드시 숙지하자.
  • InnoDB 버퍼 풀 전체를 관리하는 락(세마포어)로 인해 내부 락 경합이 많이 발생하는 것을 막기 위해 버퍼 풀을 여러 개 쪼개어 관리할 수 있다. innodb_buffer_pool_instance 로 버퍼 풀을 여러 개로 쪼개어 관리하면, 락(세마포어) 자체도 경합이 분산되는 효과가 생긴다.

버퍼 풀 구조

  • 버퍼 풀이라는 거대한 메모리 공간을 innodb_page_size변수 크기의 페이지 조각으로 쪼개어 관리한다.
  • 페이지를 관리하기 위한 자료 구조
    • 프리 리스트
      • 비어 있는 페이지 목록, 새롭게 데이터 페이지를 읽어와야 할 때 사용
    • LRU 리스트
      • LRU와 MRU를 결합한 형태로, 가장 오래 전/최근에 사용한 페이지의 리스트
      • 디스크로부터 한번 읽어온 페이지를 최대한 오래 버퍼 풀 메모리에 유지해서 디스크 I/O 최소화
    • 플러시 리스트
      • 디스크와 동기화되지 않은 데이터를 가진 데이터 페이지(=더티 페이지)의 변경 시점 기준의 페이지 목록 관리
      • 데이터가 변경되면, InnoDB는 변경 내용을 (1) 리두 로그에 기록하고, (2) 버퍼 풀 데이터 페이지에도 변경 내용을 반영하기 때문에 필요함

버퍼 풀과 리두 로그

  • 버퍼 풀의 기능 중 하나인 쓰기 버퍼링의 성능을 높이려면 버퍼 풀과 리두 로그와의 관계를 파악해야 한다.
  • 리두 로그는 하나의 고정 크기의 파일을 순환 형태로 연결해서 사용
    • 어느 순간 사용중인 공간을 덮어 쓸 수 있기 때문에 구분이 필요
    • 재사용 불가능한 공간을 활성 리두 로그라고 부름
  • 하나의 파일을 순환하지만 로그 포지션은 계속 증가
    • LSN(LogSequence Number)
    • 데이터를 디스크에 기록 시 가장 최근 체크포인터 시점의 LSN이 활성 리두 로그 공간의 시작점
    • 마지막 로그 엔트리의 LSN 과의 차이가 체크포인트 에이지
      • 활성 리두 공간의 크기
  • 체크포인트의 LSN보다 작은 리두 로그 엔트리와 관련된 더티 페이지들을 디스크에 동기화

버퍼 풀 플러시

  • 버퍼 풀에 더티 페이지가 쌓이다가 디스크에 동기화될 때 디스크 I/O가 폭증하는 것(= Disk IO burst 현상)을 방지하기 위해서 2가지 플러시 기능을 백그라운드로 실행한다.
  1. 플러시 리스트 플러시
    • 리두 로그 공간의 재활용을 위해 공간을 비우려면 관련 더티 페이지를 먼저 동기화 시켜야 한다.
    • 더티 페이지를 디스크와 동기화하는 클리너 스레드가 있다. innodb_page_cleaners 로 클리너 스레드를 조절할 수 있다. 이 값을innodb_buffer_pool_instances와 동일한 값으로 하여, 하나의 클리너 스레드가 하나의 버퍼 풀 인스턴스를 처리하도록 설정하는 것을 권장한다.
    • 전체 버퍼 풀 내에서 더티 페이지(↔ 클린 페이지)의 비율을 정할 수 있다. innodb_max_dirty_pages_pct 로 설정할 수 있는데, 기본값을 권장한다. 단, 많은 더티 페이지를 가지면 버퍼링이 효율은 좋아지나 한번에 디스크 쓰기를 하면 Disk IO burst 현상이 발생할 수 있다. 이를 방지하기 위해 일정 수준 이상의 더티 페이지가 발생하면 조금씩 더티 페이지를 디스크로 기록할 수 있다. innodb_max_dirty_pages_lwm 를 설정하면 된다.
    • innodb_io_capacity , innodb_io_capacity_maxDB서버가 어느 정도로 디스크 읽기/쓰기가 가능한지를 설정하는 값이다.
      • 어댑티브 플러시innodb_io_capacity , innodb_io_capacity_max 에 의존하지 않고, 리두 로그의 증가 속도를 분석해서 적절한 수준의 더티 페이가 버퍼 풀에 유지되도록 디스크 I/O를 실행하는 새로운 알고리즘 방식이다. innodb_adaptive_flushing 를 활성화하면 사용할 수 있다.
  2. LRU 리스트 플러시
    • LRU 리스트에의 끝부분 부터 확인하여 사용 빈도가 낮은 페이지들 제거한다. 더티 페이지는 디스크에 즉시 동기화하며, 클린 페이지는 즉시 프리 리스트로 페이지를 옮긴다.
    • 버퍼 풀 인스턴스별로 최대 innodb_lru_scan_depth 만큼 스캔한다.

8. Double Write Buffer

  • 리두 로그는 (공간의 낭비를 막기 위해) 변경된 내용만 기록하므로, 디스크에 플러시될 때 하드웨어 에러 등으로 일부만 기록되어 복구할 수 없는 경우가 발생할 수 있다. (= Partial page, Torn page)
  • Double Write 기법으로 디스크 데이터 파일에 변경 내용을 기록하기 전에 더티 페이지를 우선 묶어서 한번의 Disk I/O로 Double Write버퍼에 기록한다. 그 다음 데이터 파일에 쓰기를 하는데 중간에 실패하면, Double Write 버퍼를 이용해 복구 가능하다. 결국 Disk I/O가 발생하기 때문에 SSD처럼 랜덤 I/O와 시퀀셜 I/O 비용이 비슷한 경우에는 쓰기 부담스럽지만 무결성이 중요하다면 사용이 권장된다.

💡 새롭게 알게된 점 😀 HDD는 시퀀셜 I/O < 랜덤 I/O 지만, SSD는 둘의 차이가 거의 없다.

9. 언두 로그

언두 로그는 트랜잭션과 격리 수준을 보장하기 위해 DML 작업 시 이전 버전을 백업하는 용도로 사용된다.

  • 트랜잭션 보장 : 트랜잭션이 롤백되면 트랜잭션 도중 변경된 데이터를 변경 전 데이터로 복구해야 하는데, 이때 언두 로그의 백업해둔 데이터로 복구
  • 격리수준 보장 : 특정 커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 데이터를 조회하면 트랜잭션 격리 수준에 맞게 변경중인 레코드를 읽지 않고, 언두 로그에 백업해둔 데이터를 읽어와 반환

5.5 이전 버전 문제점

  • 대용량 처리
    • 100억건의 삭제 작업이 있으면, 삭제되기 전 값을 모두 언두 로그에 저장해야 하므로 용량이 매우 커진다.
  • 트랜잭션 지연 시
    • A, B, C 트랜잭션이 연속 실행될 때, B, C는 끝났지만 A가 활성상태로 유지중이라면 B, C의 언두 로그도 저장해야 한다.
    • 이렇게 하나의 트랜잭션이 지연되면 언두 로그가 누적되어 디스크 사용량이 늘어날 뿐만 아니라, 변경된 레코드를 조회하는 쿼리는 언두 로그를 필요한 만큼 스캔해야 해서 쿼리 성능이 떨어짐

8.0 버전부터는 완전히 해결됨

  • MySQL 서버가 필요한 시점에 언두 로그 공간을 삭제함

언두 테이블스페이스

언두 로그가 저장되는 공간을 언두 테이블스페이스라고 한다.

  • 이전 버전들에서는 언두 로그를 시스템 테이블스페이스에 저장했지만, 8.0 버전부터는 외부의 독립적인 파일로 저장한다.
  • 하나의 테이블스페이스에 1~128개의 롤백 세그먼트를 가지고, 각 롤백 세그먼트는 여러개의 언두 슬롯을 가진다.
    • 언두 슬롯의 최대 개수: 페이지의 크기 / 16Byte
  • 최대 동시 트랜잭션 수 = InnoDB 페이지 크기 / 16 콜백 세그먼트 수 테이블 스페이스 수
  • 테이블스페이스 공간이 과도하다면 Truncate 작업을 통해 공간을 반환할 수 있다.

10. 체인지 버퍼

  • 레코드를 변경하는 작업이 발생하면 인덱스도 업데이트해야 하는데, 이때 디스크 랜덤 I/O가 발생한다. 단, 버퍼 풀에 해당 인덱스 페이지가 있으면 바로 업데이트하면 되지만, 없는 경우에는 임시 메모리 공간(= 체인지 버퍼)에 저장해두고 일단 사용자에게 결과를 반환한다.
  • 유니크 인덱스에 대한 수정은 중복 체크를 위해 체인지 버퍼를 사용할 수 없다.
  • 체인지 버퍼에 임시 저장된 인덱스 레도들은 이후 머지 스레드(백그라운드 스레드)에 의해 병합된다.
  • 버전 8.0부터는 INSERT뿐만 아니라 DELETE, UPDATE 작업으로 인해 인덱스가 추가, 삭제되는 작업에 대해서도 버퍼링할 수 있게 개선됐다.

11. 리두 로그(Write-Ahead-Log) 및 로그 버퍼

리두 로그는 트랜잭션의 4가지 요소인 ACID 중에서 Durable에 해당하는 영속성에 가장 밀접하게 연관되어 있다. MySQL 서버가 비정상 종료 시 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치다.

데이터베이스 서버는 데이터 변경 내용을 로그(리두 로그)로 먼저 기록한다. 데이터 파일 쓰기는 디스크의 랜덤 액세스가 필요하며 읽기에 비해 큰 비용이 발생한다. 이에 비용이 낮은 리두 로그를 이용해 저장하고, 문제 발생 시 리두 로그를 이용해 복구한다.

일관성이 지켜지지 않는 경우

  • 커밋됐지만, 데이터 파일에 기록되지 않는 경우
    • 리두 로그를 데이터 파일에 복사
  • 롤백됐지만, 데이터 파일에 이미 기록된 경우
    • 리두 로그로 상태 파악 후, 언두 로그를 데이터 파일에 복사

리두 로그의 디스크 동기화

리두 로그는 트랜잭션이 커밋되면 즉시 디스크로 기록되도록 설정하는 것을 권장하지만, 이런 경우 디스크 I/O가 많이 발생할 수 있으므로 리두 로그를 어느 주기로 디스크에 동기화할지 innodb_flush_log_at_trx_commit 변수를 설정할 수도 있다.

로그 버퍼

변경량이 많은 경우에는 리두 로그의 기록 작업이 문제가 될 수 있다. 이를 보완하기 위해서 ACID 속성을 보장하는 수준에서 버퍼링할 수 있도록 로그 버퍼 같은 자료구조를 사용한다.

12. 어댑티브 해시 인덱스

어댑티브 해시 인덱스는 사용자가 수동으로 생성하는 인덱스가 아니라 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이다.

B-Tree는 일반적으로 검색이 매우 빠르다 하지만, 스레드가 매우 많다면 이 역시 오래 걸린다. 이를 위해 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고 필요할 때 빠르게 레코드가 저장된 페이지를 찾을 수 있다.

  • 인덱스는 인덱스 키 값, 데이터 페이지 주소 쌍으로 되어있음
  • 인덱스 키 값은 B-Tree 인덱스의 고유 번호(Id), B-Tree 인덱스의 실제 키 값의 조합
  • 페이지 주소는 버퍼 풀에 로딩된 페이지 주소이다.

어댑티브 해시 인덱스가 성능 향상에 도움이 크게 되는 경우

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

어댑티브 해시 인덱스가 성능 향상에 크게 도움 되지 않는 경우

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

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

한 줄 요약 : InnoDB 써라

profile
백엔드 프로그래머

0개의 댓글