✅ MySQL 스토리지 엔진 아키텍처

MySQL에서는 기본적으로 InnoDB 스토리지 엔진을 사용한다.

MySQL의 다양한 스토리지 엔진 중 거의 유일하게 InnoDB 스토리지 엔진만 레코드 기반의 잠금 기능을 제공한다. 이러한 이유로 높은 동시성 처리가 가능하며, 안정적인 성능을 자랑한다.

📌 프라이머리 키(PK)에 의한 클러스터링

InnoDB는 기본적으로 PK를 기준으로 클러스터링 되어 저장된다.
즉, PK 값의 순서대로 디스크에 저장된다.

이러한 이유로 PK 기반 조회 속도가 매우 빠르다.
(PK는 클러스터링 인덱스이기 때문)

📌 MVCC(Multi Version Concurrency Control)

MVCC는 잠금을 사용하지 않는 일관된 읽기(SELECT) 기능을 제공하는 데 주목적을 둔 기능이다.

InnoDB에서는 위 기능을 언두 로그(Undo log)를 이용해 구현한다.

초기 신짱구의 나이는 5살로 저장되어 있었지만, UPDATE을 통해 10살로 변경했고 아직 COMMIT, ROLLBACK을 하지 않은 상태다.

이때 다른 사용자가 신짱구를 조회하면(COMMIT, ROLLBACK 이전) 짱구의 나이는 몇살로 나올까? 5살? 10살?

이 결과는 시스템 변수 transaction_isolation의 격리 수준 설정에 따라 다르다.

transaction_isolation은 아래처럼 격리 수준을 설정할 수 있다.

• REPEATABLE-READ(기본 값) : 변경되기 이전의 내용을 보관하는 언두 로그 영역의 데이터를 반환
  • 5살 반환
• READ-UNCOMMITTED : 커밋 여부를 떠나 버퍼 풀에 있는 데이터를 반환
  • 10살 반환
• READ-COMMITTED : 항상 최근 커밋된 데이터만을 조회한다. 즉, 디스크에 있는 데이터를 반환
  • 5살 반환
• SERIALIZABLE : 매우 엄격한 격리 수준으로 여러 트랜잭션이 동일한 레코드에 동시 접근할 수 없다.
  • 5살 반환

📌 자동 데드락 감지

InnoDB는 자동 데드락 감지 스레드가 있으며, 잠금 대기 목록을 그래프(Wait-for List) 형태로 관리한다.

감지 스레드가 대기 목록 그래프를 검사하고, 교착 상태에 빠진 트랜잭션을 발견하면 트랜잭션 하나를 강제 종료한다.
(트랜잭션 종료의 기준은 언두 로그이며, 작은 양의 언두 로그 트랜잭션을 종료/롤백한다)

※ InnoDB는 MySQL엔진에서 관리하는 테이블 장금을 확인할 수 없어 데드락 감지가 불확실하다.

이러한 이유로 innodb_table_locks 시스템 변수를 ON으로 설정하여 테이블 레벨의 잠금까지 감지할 수 있게 하는 것을 권장한다.

mysql> innodb_table_locks=ON;

동시 처리 스레드가 많아지거나, 트랜잭션이 가진 잠금의 개수가 많아지면 감지 스레드의 속도가 느려진다. 이렇게 되면 서비스 속도에도 악영향을 미치게 되고, 감지 스레드는 더 많은 CPU 자원을 소모한다.

이럴 때는 아예 감지 스레드의 사용을 비활성화하는 방법이 있다.

mysql> innodb_deadlock_detect=OFF;

하지만 감지 스레드를 비활성화 하면 교착 트랜젝션이 발생할 때 해당 트랜젝션이 무한정 대기 상태로 접어들게 된다.

이러한 문제를 방지하기 위해 데드락 상태에서 일정 시간이 지나면 자동으로 요청 실패 되도록 innodb_lock_wait_timeout 시스템 변수를 설정한다.

innodb_lock_wait_timeout 시스템 변수의 기본값은 50s이고 일반적으로 50초 보다 낮게 설정하여 사용한다.

mysql> innodb_deadlock_detect=OFF;
mysql> innodb_lock_wait_timeout=50;

📌 자동화된 장애 복구

InnoDB는 매우 견고하게 만들어져서 데이터 파일 손상, MySQL 서버가 시작되지 못하는 경우가 거의 없다. 하지만, 하드웨어 이슈로 InnoDB가 자동으로 복구하지 못하는 상황이 발생할 수 있다.

InnoDB 데이터 파일은 MySQL 서버가 시작할 때 항상 자동 복구를 수행한다. 하지만, 자동 복구할 수 없는 경우 자동 복구를 중지하고 MySQL 서버를 종료한다.
이때는 innodb_force_recovery 시스템 변수를 설정하여 MySQL 서버를 강제 기동한다.

⚠️ innodb_force_recovery 시스템 변수는 비상 상황에서만 사용해야 한다!

innodb_force_recovery 시스템 변수값은 0~6 이 있으며, 기본값은 0(정상 실행), 강제 복구 실행은 1~6의 값을 사용한다.

어떤 부분에서 발생한 문제인지 모를 때는 1~6까지 시스템 변수값을 변경하면서 시도한다.

※ 강제 복구 실행 시에는 SELECT만 사용할 수 있다.

※ 변수값은 innodb_force_recovery 관련 공식 문서를 참고

※ innodb_force_recovery값이 커질수록 심각한 상황을 의미하며, 복구 가능성이 적어진다.

위 방법을 통해 MySQL 서버 강제 실행에 성공하면 가장 먼저 mysqldump를 사용하여 가능한 만큼의 데이터를 백업한다. 이후 MySQL 서버의 DB와 테이블 등을 다시 생성하는 게 좋다.

만약 MySQL 서버 강제 실행이 실패하면, 지금까지의 백업 파일 및 바이너리 로그를 사용하여 최대한 DB를 복구하는 방법밖에 없다...

📌 버퍼 풀(Buffer Pool) ⭐️

InnoDB 버퍼 풀(Buffer Pool)은 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다.

또한, 쓰기 작업을 임의 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 한다.

▶︎ 버퍼 풀 크기 설정 ⭐️

버퍼 풀(Buffer Pool)의 사이즈를 결정은 운영체제, 클라이언트 스레드가 사용할 메모리 등 다양한 변수에 대해 고려해야 한다.

가장 이상적인 방법은 "InnoDB 버퍼 풀의 크기를 적절히 작은 값으로 설정하여 점차적으로 늘려가능 방법"이 가장 이상적인 방법이다.

이미 MySQL 서버가 세팅되어 있는 회사라면, 현재 설정된 메모리 크기를 기준으로 버버 풀 크기를 조정하면된다.

하지만, MySQL 서버를 처음 세팅하는 환경이라면 전체 메모리 크기의 50%를 버퍼 풀로 설정하여 조금씩 버퍼 풀의 크기를 늘려나가는게 좋다.

버퍼 풀의 크기는 innodb_buffer_pool_size 시스템 변수를 사용하여 조절한다.

⚠️ innodb_buffer_pool_size 시스템 변수의 크기 조절은 크리티컬한 작업임으로 한가한 시간(새벽)에 조절하는것을 권장한다.

버퍼 풀의 크기를 늘리는 작업은 서비스 운영에 큰 영향이 없지만, 크기를 줄이는 작업은 서비스 운영에 큰 영향이 발생할 수 있다.

마지막으로 버퍼 풀의 크기 조절은 128MB 단위로만 처리된다. 이러한 이유로 버퍼 풀의 크기를 조절할 때는 공식문서를 참고하여 수정을 권장한다.

▶︎ 버퍼 풀 인스턴스 설정 ⭐️

버퍼 풀 전체를 관리하는 잠금(세마포어)으로 인해 내부 잠금 경합이 자주 발생했다. 이러한 문제를 줄이기 위해 버퍼 풀을 여러 개로 쪼개어 관리할 수 있게 개선되었다.

innodb_buffer_pool_instances 시스템 변수를 사용하여 버퍼풀을 여러 개로 분리하여 관리 할 수 있다.

이렇게 분리된 버퍼 풀을 "버퍼 풀 인스턴스"라 부른다.

기본적으로 버퍼 풀 인스턴스는 자동 산정 되어 설정된다. (MySQL 8.4 이후)

하지만, 메모리의 크기가 1GB 미만인 경우 버퍼 풀 인스턴스는 1개만 생성된다.