MySQL ) InnoDB 스토리지 엔진

InnoDB 스토리지 엔진

MySQL에서 가장 많이 사용되는 스토리지 엔진. 레코드 기반의 잠금을 제공, 높은 동시성 처리가 가능하며 안정적이고 성능이 뛰어나다.

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

InnoDB의 모든 테이블은 기본적으로 PK 를 기준으로 클러스터링되어 저장.

• PK 키 값의 순서대로 디스크에 저장되며 모든 세컨더리 인덱스는 PK 값을 논리적인 주소로 사용한다.
• PK 를 이용한 레인지 스캔이 상당히 빨리 처리된다.

외래 키 지원

DB 서버 운영의 불편함때문에 실제 서비스용 DB 에는 사용되지 않아도, 개발 환경의 DB에서는 좋은 가이드 역할을 한다.

MVCC (Multi Version Concurrency Control)

일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS 가 제공하는 기능. 잠금을 사용하지 않는 일관된 읽기를 언두 로그를 통해 제공한다. *멀티버전이란 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미.

• READ_COMMITED 이상의 격리 수준에서 변경되기 이전 데이터를 언두로그를 통해 제공한다.
• 트랜잭션이 길어지면 언두에서 관리되는 옛 데이터가 삭제되지 못하고 오랫동안 관리된다.
  • 언두 영역에 저장되는 시스템 테이블스페이스의 공간이 많이 늘어나는 상황 발생 😟
• 롤백하면, 언두 영역의 백업 데이터를 InnoDB 버퍼 풀로 다시 복구, 언두 영역 내용을 삭제한다.
• 언두 영역이 필요한 트랜잭션이 없을 때 삭제된다.

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

다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고 언두 로그에 있는 변경 전 데이터를 읽는다. INSERT 와 연결되지 않은 순수한 읽기 SELECT 작업은 항상 잠금을 대기하지 않고 바로 실행.

자동 데드락 감지

• 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사.
• 교착 상태에 빠진 트랜잭션을 찾음.
  • 언두 로그 양을 보고 언두 로그 레코드를 더 적게 가진 트랜잭션을 롤백.
• 그 중 하나를 강제 종료.

→ 특정 시스템 변수를 활성화하면 테이블 레벨의 잠금까지 감지할 수 있게 된다. 활성화하자.

데드락 작업이 부담될 때

1. 동시 처리 스레드가 매우 많아질 때.
2. 트랜잭션이 가진 잠금 갯수가 많아질 때

결과

• 데드락 감지 스레드가 느려진다.
• 더 많은 CPU 자원을 소모한다.

 

 

✅ 데드락 감지 스레드는 작동 ON, OFF 가 가능하다. 그러나, 데드락 감지를 OFF 한 상태에서 데드락이 벌어지면 일정 시간이 지나면 자동으로 요청이 실패하고 실패 에러 메시지를 반환한다.

자동화된 장애 복구

완료되지 못한 트랜잭션 및 디스크에 일부만 기록된 데이터 페이지 등을 자동으로 복구 작업한다.

• 기본적으로 MySQL 서버가 시작할 때 항상 자동복구를 수행한다.
• 자동 복구 할 수 없는 손상이 있다면 자동 복구를 멈추고 MySQL 가 종료된다.

자동 복구가 안 된다면

설정 파일에서 시스템 변수를 설정하고 데이터 파일이나 로그 파일 손상 여부 검사 과정을 선별해서 진행해야 한다.

1. 데이터를 가능한 만큼 백업.
2. 그 데이터로 MySQL 서버 DB 와 테이블을 다시 생성.

이래도 안 되면

1. 마지막 백업으로 DB 새로 구축 후
2. 바이너리 로그를 사용해 최대한 장애 시점까지의 데이터를 복구하자…!

InnoDB 버퍼 풀

InnoDB 스토리지 엔진의 가장 핵심!!! 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해두는 공간. 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이 한다.

• 변경된 데이터를 모아서 처리하면 랜덤한 디스크 작업의 횟수를 줄여준다.
• 서버 메모리가 허용하는 만큼 크게 설정하면 할수록 쿼리 성능이 빨라진다!

버퍼 풀 크기 설정

운영체제와 각 클라이언트 스레드가 사용할 메모리를 충분히 고려해서 설정해야 한다. → 커넥션이 많고 사용하는 테이블도 많다면 레코드 버퍼 용도로 사용되는 메모리 공간이 많이 필요할 수도…

레코드 버퍼

각 클라이언트 세션에서 테이블 레코드를 읽고 쓸 때 버퍼로 사용하는 공간. → 보통 전체 커넥션 개수와 각 커넥션에서 읽고 쓰는 테이블 개수에 따라서 결정된다.

버퍼 풀 인스턴스

내부 잠금 경합을 줄이기 위해 버퍼 풀을 여러 개로 쪼개어 관리할 수 있는데, 그 각 버퍼 풀을 버퍼 풀 인스턴스라고 부른다.

그래서 크기 설정 어떻게?

1. 버퍼 풀 크기를 적절히 작은 값으로 설정
2. 조금씩 상황 봐 가며 증가시키는 게 최선!

주의

버퍼 풀 크기 변경은 크리티컬한 변경, 가능하면 MySQL 서버가 한가한 시점을 골라 진행하자.

• 확장 작업은 시스템 영향도가 크지 않다.
• 축소는 서비스 영향도가 매우 크다!!
  • 되도록 버퍼 풀 크기를 줄이는 건 하지 않도록 주의하자.

버퍼 풀 구조

페이지 크기 조각으로 쪼개고 데이터를 필요로 할 때 해당 데이터 페이지를 읽어 각 조각에 저장한다. 버퍼 풀 페이지 크기 조각을 관리하기 위해 다음과 같은 자료 구조를 관리한다.

프리 리스트

실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들 목록. 사용자 쿼리가 새롭게 디스크 데이터 페이지를 읽어와야할 때 사용.

LRU 리스트

LRU 와 MRU 가 결합된 형태 디스크에서 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼 풀 메모리에 유지, 디스크 읽기를 최소화한다.

 

LRU	Old 서브 리스트
MRU	New 서브 리스트

• 페이지가 자주 사용되면 MRU 영역에서 계속 살아남는다.
• 거의 사용되지 않으면 점점 밀려 LRU 끝으로 밀려나고 결국 삭제된다.

플러시 리스트

디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(더티 페이지)의 변경 시점 기준의 페이지 목록 관리. 한 번 데이터 변경이 가해진 데이터 페이지는 플러시 리스트에 관리되고 특정 시점에 디스크에 기록된다.

• 체크 포인트를 발생시켜 디스크 리두 로그와 데이터 페이지 상태를 동기화한다.

주의

• 리두 로그가 디스크로 기록됐다고 데이터 페이지가 디스크로 기록됐다는 것을 항상 보장하진 않음.
  • 반대도 마찬가지.

버퍼 풀과 리두 로그

버퍼 풀은 서버 메모리가 허용하는 만큼 크게 설정하면 할수록 쿼리 성능이 빨라진다. 단, 버퍼 풀의 메모리 공간만 단순히 늘리는 것은 데이터 캐시 기능만 향상시키는 것. 쓰기 버퍼링까지 향상시키려면 버퍼 풀과 리두 로그의 관계를 이해하자!

 

클린 페이지	읽고 나서 전혀 변경되지 않은 데이터들.
더티 페이지	읽은 후 INSERT, UPDATE, DELETE 로 변경된 데이터들.

→ 더티 페이지는 디스크와 메모리(버퍼 풀) 데이터 상태가 달라 언젠가 디스크로 기록되어야 한다.

리두 로그

리두 로그는 1개 이상의 고정 크기 파일을 연결해 순환 고리처럼 사용.

• 데이터 변경이 계속 발생하면 리두 로그 파일에 기록됐던 로그 엔트리는?
• 어느 순간 다시 새로운 로그 엔트리로 덮어쓰인다.
• 그래서 재사용 가능한 곳과 당장 재사용 불가능 한 공간을 구분해서 관리해야 한다.

활성 리두 로그

재사용 불가능한 공간

LSN (Log Sequence Number)

리두 로그 파일 공간은 계속 순환되어 재사용되지만, 매번 기록될 때마다 로그 포지션은 계속 증가된다. 그 값.

• InnoDB 스토리지 엔진은 주기적으로 체크포인트 이벤트를 발생시켜 리두 로그와 버퍼 풀의 더티 페이지를 디스크로 동기화 시킨다.
• 발생한 체크포인트 중 가장 최근 체크포인트 지점의 LSN 이 활성 리두 로그 공간의 시작점.

체크 포인트 에이지

가장 최근 체크 포인트의 LSN 과 마지막 리두 로그 엔트리의 LSN의 차이. 즉, 활성 리두 공간의 크기.

 

 

✅ 버퍼 풀의 크기가 100GB 이하의 MySQL 서버에서는 리두 로그 파일의 전체 크기를 대략 5~10GB 수준으로 선택하고 필요할 때마다 조금씩 늘려가며 최저값을 선택하는 것이 좋다.

버퍼 풀 플러시

디스크 쓰기 동기화. 특별히 성능 문제가 발생하지 않는다면 굳이 관련 시스템 변수들을 조정할 필요는 없다.

플러시 리스트 플러시

플러시 리스트에서 오래전에 변경된 데이터 페이지를 순서대로 디스크에 동기화하는 작업을 수행하는 함수. → 언제부터 얼마나 많은 더티 페이지를 한번에 디스크로 기록하느냐에 따라 사용자 쿼리가 악영향을 받지 않고 부드럽게 처리된다.

어댑티드 플러시

리두 로그 증가 속도를 분석해 적절한 수준의 더티 페이지가 버퍼 풀에 유지될 수 있도록 디스크 쓰기를 실행.

LRU 리스트 플러시

LRU 리스트에서 사용 빈도가 낮은 데이터 페이지를 제거하고 새로운 공간을 만드는데 쓰이는 함수.

버퍼 풀 상태 백업 및 복구

InnoDB 서버의 버퍼 풀은 쿼리 성능에 매우 밀접하게 연결되어 있다.

백업

서버를 셧다운하기 전에 시스템 변수를 이용해 버퍼 풀 상태를 백업할 수 있음.

복구

시스템 변수를 통해 백업된 버퍼 풀의 상태를 다시 복구할 수 있음. 버퍼 풀 다시 복구하는 과정이 어느 정도 진행됐는지 확인할 수 있는 상태값도 제공. → 버퍼 풀 복구 도중에 급히 서비스를 시작해야 한다면 버퍼 풀 복구를 멈출 수도 있다.

버퍼 풀 적재 내용 확인

특정 테이블을 이용해 인덱스별로 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재돼 있는지 확인할 수 있다.

---

 

 

Double Write Buffer

페이지가 일부만 기록되는 현상을 막기 위해 쓰이는 버퍼

1. 실제 데이터 파일 변경 내용을 기록하기 전에 더티 페이지를 우선 묶는다.
2. 한 번의 디스크 쓰기로 테이블 스페이스에 Douber Writer Buffer 에 기록한다.
3. 각 더티 페이지를 적당한 위치에 하나씩 랜덤으로 쓴다.

→ 데이터 무결성이 매우 중요하다면 DoubleWrite 활성화를 고려하자.

언두 로그

트랜잭션과 격리 수준을 보장하기 위해 *DML 로 변경되기 이전 버전을 별도로 백업한 데이터를 보관하는 곳. *INSERT, UPDATE, DELETE

언두로그 사용 의의

• 트랜잭션 보장
  • 트랜잭션이 롤백되면 언두 로그에 백업해둔 이전 버전의 데이터를 이용해 복구한다.
• 격리 수준 보장 (높은 동시성)
  • 다른 커넥션에서 데이터를 조회하면 변경중인 데이터를 읽지 않고 언두 로그에 백업해둔 데이터를 읽어서 반환한다.

 

 

✅ 서버 별로 안정적인 시점의 언두 로그 건수를 확인해 이를 기준으로 언두 로그의 급증 여부를 모니터링하자!

언두 테이블 스페이스 관리

언두 로그가 저장되는 공간.

• 하나의 언두 테이블은 1개 이상 128개 이하의 롤백 세그먼트를 가진다.
• 롤백 세그먼트는 1개 이상의 언두 슬롯을 가진다.
  • InnoDB 페이지 크기를 16바이트로 나눈 값의 개수.

→ 가능하면 기본 값을 유지하자.

언두 테이블 스페이스 불필요한 공간 잘라내기

• 자동 모드.
  • 퍼지 스레드가 주기적으로 언두 로그 파일에서 사용되지 않는 공간을 잘라내 운영체제에 반납.
• 수동모드
  • 잘라내기가 자동으로 실행되지 않거나
  • 자동 모드로 언두 테이블 스페이스 공간 반납이 부진한 경우 사용

체인지 버퍼

변경해야할 인덱스 페이지가 버퍼 풀에 존재하지 않고 디스크로부터 읽어와야한다면 이를 즉시 실행하지 않고 임시 공간에 저장해두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시킴. 이 때 사용하는 임시 메모리 공간.

 

 

 

✅ 사용자에게 결과를 전달하기 전에 반드시 중복 여부를 체크해야하는 유니크 인덱스는 체인지 버퍼를 사용할 수 없다.

리두 로그

서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전 장치.

• 쓰기 비용이 낮은 자료구조를 가지고 있다.
• 비정상 종료가 발생하면 리두 로그 내용을 이용해 데이터 파일을 복구한다.

 

데이터 상태	방법
커밋됐지만 데이터 파일에 기록되지 않음	리두 로그 저장 데이터를 다시 복사
롤백됐지만 데이터 파일에 기록되지 않음	언두 로그 데이터 복사

✅ 리두 로그는 트랜잭션이 커밋되면 즉시 디스크로 기록되도록 설정하자!

• 사용량(변경 작업)이 많은 서버에서 리두 로그 기록 작업이 문제가 될 경우
• 이 부분을 보완하기 위해 최대한 ACID 속성을 보장하는 수준에서 버퍼링한다.

로그 버퍼

리두 로그 버퍼링에 사용되는 공간.

리두 로그 아카이빙

리두 로그를 아카이빙하는 기능. 아카이빙된 리두 로그를 정상적으로 사용하려면 커넥션을 그대로 유지하고, 작업이 완료되면 반드시 시스템 변수를 호출해 아카이빙을 정상적으로 종료해야 한다.

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

트랜잭션이 커밋돼도 데이터 파일은 즉시 디스크로 동기화되지 않지만, 리두 로그는 항상 디스크로 기록된다. 데이터가 중요하지 않더라도 서비스 도중에는 리두 로그를 활성화해 서버가 비정상적으로 종료돼도 특정 시점의 일관된 데이터를 가질 수 있게 하자.

어댑티드 해시 인덱스

사용자가 자주 요청하는 데이터에 자동으로 생성하는 인덱스. B-Tree 검색 시간을 줄여주기 위해 도입된 기능.

키	데이터 페이지	주소
해시 인덱스	B-Tree 인덱스의 고유 번호(id)	B-Tree 인덱스의 실제 키 값

• 어댑티브 해시 인덱스는 하나만 존재한다.
• 그래서 B-Tree 인덱스의 “고유 번호”를 키로 저장한다.
  • 특정 값이 어느 인덱스에 속한 것인지 구분해야 한다.
• 데이터 페이지 주소는 버퍼 풀에 로딩된 페이지 주소

도움이 되는 경우

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

→ 데이터 페이지 → 메모리(버퍼 풀) 접근은 빠르다.

도움이 안 되는 경우

• 디스크 읽기가 많음.
• 특정 패턴의 쿼리가 많음(조인 및 LIKE)
• 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽음.

→ 데이터 페이지 → 디스크 접근은 도움 안 된다.

어댑티드 해시 인덱스는 테이블 삭제 또는 변경 작업은 더 치명적이다.

판단

MySQL 서버의 상태 값을 살펴보며 판단하자. CPU 사용량은 높지 않은데 히트율이 높지 않다면 비활성화하는 게 낫다.

MySQL 로그 파일

MySQL 서버에 문제가 생기면 해당 로그 파일들을 자세히 확인하는 습관을 들이자.

에러 로그 파일

에러나 경고 메시지가 출력되는 로그 파일

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

설정 파일을 변경하거나 데이터베이스가 비정상적으로 종료되어 다시 시작하는 경우 반드시 에러 로그 파일을 통해 설정된 변수 이름이나 값이 명확하게 설정되고 의도한대로 적용됐는지 확인하자. → 인식하지 못한다면 에러 메시지를 출력해준다.

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

다시 시작되면서 완료되지 못한 트랜잭션을 정리하고 디스크에 기록되지 못한 데이터가 있다면 다시 기록하는 재처리 작업을 할 때 해당 과정에 대한 간단한 메시지가 출력됨. 문제가 있다면 에러 메시지를 출력하고 다시 종료됨.

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

사전 예방이 어려우니 자주 에러 로그 파일을 검토하고 데이터베이스의 숨겨진 문제점을 확인하자.

비정상적으로 종료된 커넥션 메시지

클라이언트 애플리케이션에서 정상적으로 접속 종료를 하지 못하고 프로그램이 종료된 경우. 네트워크에 문제가 있어 의도하지 않게 접속이 끊어지는 경우. 에러 로그 파일에 내용이 기록됨. → 해당 메시지가 너무 많이 기록되면 애플리케이션의 커넥션 종료 로직을 한 번 검토해보자.

InnoDB 모니터링 또는 상태 조회 명령의 결과 메시지

테이블 모니터링, 락 모니터링, InnoDB 엔진 상태를 조회하는 명령을 하면 큰 메시지가 로그 파일에 기록. → 모니터링 사용 후에는 다시 비활성화해 에러 로그 파일이 커지지 않게 만들자.

MySQL 종료 메시지

MySQL이 아무도 모르게 종료되거나 재시작되는 경우, 에러 로그 파일에서 마지막 메시지를 보자.

제너럴 쿼리 로그 파일

MySQL 서버에서 실행되는 쿼리로 어떤 것들이 있는지 전체 목록을 뽑아서 검토하려면 쿼리 로그를 활성화해 로그 파일로 기록한 후 검토하자.

슬로우 쿼리 로그

서비스 운영 중에 MySQL 서버의 전체적인 성능 저하를 검사하거나 정기적인 점검을 위한 튜닝을 할 때 사용.

• 시스템 변수에 설정한 시간 이상의 시간이 소요된 쿼리가 모두 기록된다.
• 반드시 쿼리가 정상적으로 실행이 완료되어야 슬로우 쿼리 로그에 기록된다.

 

✅ Percona Toolkit 의 스크립트를 이용하면 쉽게 빈도나 처리 성능별로 쿼리를 정렬해서 볼 수 있다.

로그 파일 결과

슬로우 쿼리 통계

모든 쿼리를 대상으로 슬로우 쿼리 로그의 실행 시간, 잠금 대기 시간 등의 평균 및 최소/최대 값을 표시

실행 빈도 및 누적 실행

각 쿼리별 응답 시간과 실행 횟수. 같은 모양의 쿼리면 동일한 Query ID 를 가진다.

쿼리별 실행 횟수 및 누적 실행 시간 상세 정보

쿼리가 얼마나 실행됐는지, 쿼리 응답 시간에 대한 히스토그램 같은 상세 내용.