: MySQL에서 사용할 수 있는 스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금을 제공하여 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어남
1. 프라이머리 키에 의한 클러스터링
: InnoDB의 모든 테이블은 프라이머리 키를 기준으로 클러스터링되어 저장 ⇒ 프라이머리 키 값대로 순서대로 저장
: 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용, 프라이머리 키는 클러스터링 인덱스이므로 레인지 스캔이 상당히 빨리 처리될 수 있음
: 이로 인해 결과적으로 프라이머리 키는 다른 보조 인덱스에 비해 비중이 높게 설정됨
MyISAM은 클러스터링 키를 지원하지 않음, 따라서 프라이머리 키와 세컨더리 인덱스는 구조적으로 아무런 차이가 없음. 단순히 유니크 제약을 가진 세컨더리 인덱스일뿐
프라이머리 키를 포함한 모든 인덱스는 물리적인 레코드의 주소 값을 가짐
2. 외래 키 지원
난.. 외래 키 안좋아해..
: 데이터베이스 서버 운영의 불편함 때문에 서비스용 데이터베이스에선 생성하지 않는 경우도 자주 있는데, 개발 환경의 데이터베이스에선 좋은 가이드 역할을 할 수 있음
: InnoDB에서의 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지
체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고 그로 인해 데드락이 발생할 때가 많으므로 개발할 때도 주의하는 것이 좋다.
: 세션 단위에서 외래 키 검증을 해제하는 방법도 있으니 가능하면 잘 사용해보자.. SET SESSION foreign_key_checks=OFF;
3. MVCC(Multi Version Concurrency Control)
: 일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능으로, 잠금을 사용하지 않는 일관된 읽기를 제공하는 것이 목적
보통의 경우, 사용자가 데이터를 읽거나 쓰는 동안 해당 데이터에는 잠금이 걸리게 되며, 다른 사용자는 그 작업이 끝날 때까지 기다려야 함
이런 방식은 동시에 많은 사용자가 데이터베이스에 액세스하게 될 경우, 서로를 기다리는 시간이 길어져 전체적인 성능이 저하될 수 있음
반면에 MVCC는 각 트랜잭션에 대해 특정 시점의 데이터 스냅샷을 제공함으로써 이런 문제를 해결
즉, 한 사용자가 데이터를 변경하는 동안에도 다른 사용자는 해당 데이터의 이전 버전을 읽을 수 있게 해주어 전체적인 데이터베이스의 성능이 향상됨
: InnoDB는 Undo Log를 이용해 해당 기능을 지원하고 있다.
1.
특정값 생성 → 버퍼 풀과 데이터 파일에 저장
2.
특정값 변경 → 변경값 버퍼 풀에 저장, 기존 값 언두 로그에 저장, 데이터 파일엔 반영되었는지 안 되었는지 모르는 상태
3.
COMMIT이나 ROLLBACK이 되지 않은 상태에서 다른 사용자가 접근 → 어떤 영역의 값을 조회?
: 시스템 변수에 설정된 격리 수준에 따라 다름
•
READ_UNCOMMITTED인 경우 버퍼 풀
•
그 이상의 수준인 경우, 버퍼 풀이나 데이터 파일에 있는 내용 대신 보관하고 있는 언두 로그의 값을 반환
: COMMIT하면 변경 작업없이 지금의 상태를 영구적인 데이터로 만들어버림
: 그러나 언두 로그의 값이 바로 사라지진 않음, ROLLBACK의 가능성이 있기 때문, 언두 영역을 필요로하는 트랜잭션이 모두 사라지면 그때 비로소 사라짐
4. 잠금 없는 일관된 읽기(Non_Locking Consistent Read)
: MVCC를 통해 잠금을 걸지 않고 읽기 작업을 수행함
: 오랜 시간 동안 활성 상태인 트랜잭션이 있으면 MySQL 서버가 느려지거나 이슈가 발생할 때가 가끔 있으므로 가능한 롤백이나 커밋을 빨리 해주자
5. 자동 데드락 감지
: 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프 형태로 관리, 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 강제 종료
: 이때 우선순위는 트랜잭션의 언두 로그 양, 언두 로그 레코드를 더 적게 가진 트랜잭션이 롤백의 대상이 됨
6. 자동화된 장애 복구
: InnoDB 데이터 파일은 기본적으로 MySQL 서버가 시작될 때 항상 자동 복구를 수행, 자동으로 복구될 수 없는 손상이 있다면 즉시 종료
: 이때는 MySQL 서버의 설정 파일에 innodb_force_recovery 시스템 변수를 설정해서 MySQL 서버를 시작해야 함
•
InnoDB의 로그 파일이 손상됐다면 6으로 설정하고 MySQL 서버를 기동
•
InnoDB 테이블의 데이터 파일이 손상됐다면 1로 설정하고 MySQL 서버를 기동
•
어떤 부분이 문제인지 알 수 없다면 1~6까지 변경하면서 재시작.. 숫자가 커질 수록 심각한 상황..
◦
1(SRV_FORCE_IGNORE_CORRUPT) : 손상된 데이터가 발견되도 무시하고 MySQL 서버를 시작
◦
2(SRV_FORCE_NO_BACKGROUND) : 백그라운드 메인 스레드를 시작하지 않고 MySQL 서버를 시작
◦
3(SRV_FORCE_NO_TRX_UNDO) : MySQL 서버가 시작할때 이전에 완료되지 않은 트랜잭션을 복구하는 작업을 동작 X
◦
4(SRV_FORCE_NO_IBUF_MERGE) : Insert Buffer 내용을 무시하고 MySQL 서버를 시작
◦
5(SRV_FORCE_NO_UNDO_LOG_SCAN) : Undo log를 무시하고 MySQL 서버를 시작
◦
6(SRV_FORCE_NO_LOG_REDO) : Redo log를 무시하고 MySQL 서버를 시작
•
위의 방법을 사용해도 재시작되지 않는다면 백업 파일을 가지고 구축을 다시 해야하며 바이너리 로그를 사용해 최대한 장애 시점까지의 데이터를 복구할 수도 있음
7. InnoDB 버퍼 풀
: 가장 핵심적인 부분으로 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해두는 공간
: 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 수행, 랜덤한 디스크 작업을 방지하기 위해 변경된 데이터를 모아서 처리
1.
버퍼 풀의 크기 설정
: 가능하면 작은 값으로 설정해서 조금씩 상황을 봐가며 동적으로 증가시키는 방법이 최적
: MySQL 서버가 배포되어있는 운영체제의 전체 메모리 공간이 8GB 미만이면 50%, 그 이상이라면 50%에서 점차 증가
: InnoDB 버퍼 풀은 innodb_buffer_pool_size 시스템 변수로 크기를 설정할 수 있음, 크리티컬한 변경이므로 한가한 시점을 골라서 진행하는 것이 좋음
: 버퍼 풀 전체를 관리하는 잠금으로 인해 내부 잠금 경합이 유발되었는데, 이를 방지하기 위해 여러 개로 쪼개어 관리할 수 있도록 개선
: 기본적으로 버퍼 풀 인스턴스는 8개로 초기화되지만 전체 버퍼 풀의 메모리 크기가 1GB 미만이라면 1개만 생성됨, 메모리가 크다면 인스턴스 당 5GB 정도가 적당
2.
버퍼 풀의 구조
: InnoDB 스토리지 엔진은 버퍼 풀이라는 메모리 공간을 페이지 크기 조각으로 쪼개어 필요할 때 해당 데이터 페이지를 디스크로부터 읽어와서 각 조각에 저장
1.
버퍼 풀 인스턴스
2.
페이지 디스크립터: 버퍼 풀의 각 페이지에 대한 메타데이터를 관리, 이는 페이지의 상태(예: 고정, 해제, 변경 등)와 같은 정보를 포함
3.
해시 인덱스: 페이지 디스크립터를 통해 빠르게 페이지를 찾을 수 있게 하는 인덱스
4.
LRU 리스트: 버퍼 풀의 페이지들이 LRU(Least Recently Used) 알고리즘에 따라 정렬, 즉, 가장 오랫동안 사용되지 않은 페이지가 리스트의 앞에 위치
5.
Free 리스트: 아직 할당되지 않은 페이지들의 리스트
6.
Flush 리스트: 디스크에 기록되어야 하는 변경된 페이지들의 리스트
3.
버퍼 풀과 리두 로그
: InnoDB의 버퍼 풀과 리두 로그는 매우 밀접한 관계, 버퍼 풀은 메모리이므로 장애가 발생하여 비정상 종료시 데이터를 잃어버림, 리두 로그는 이런 트랜잭션을 기록
: InnoDB는 재시작 시, 해당 내용을 바탕으로 복구를 진행함
: 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정하면 쿼리의 성능이 빨라짐
: 디스크의 모든 데이터 파일이 버퍼 풀에 적재될 정도의 버퍼 풀 공간이라면 유의미한 변화는 없다.
: InnoDB의 버퍼 풀은 서버의 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링이라는 두 가지 용도가 있는데 버퍼 풀의 메모리 공간을 늘리는 것은 데이터 캐시 기능만 향상 시키는 것
: 쓰기 버퍼링 기능까지 향상시키려면 InnoDB 버퍼 풀과 리두 로그와의 관계를 먼저 이해해야 함
: InnoDB의 버퍼 풀은 클린 페이지(어떤 변경도 일어나지 않은)와 더티 페이지(변경이 일어난)을 가지고 있다. 더티 페이지는 디스크와 메모리의 값이 다르므로 언젠간 병합
: 리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용, 데이터 변경이 계속 일어나면 리두 로그 파일에 기록된 로그 엔트리는 새로은 로그 엔트리로 덮어짐
: 그래서 InnoDB 스토리지 엔진은 전체 리두 로그 파일에서 재사용 가능한 공간, 불가능한 공간을 구분해서 관리해야 하는데, 재사용 불가능한 공간이 활성 리두 로그
: 리두 로그 파일의 공간은 계속 재사용되지만 매번 기록될 때마다 로그 포지션은 계속 증가하며, 이를 LSN
: 가장 최근 체크포인트의 LSN과 마지막 리두 로그 엔트리의 LSN 차이를 체크포인트 에이지라고 하며, 체크포인트 에이지는 활성 리두 로그 공간의 크기
4.
버퍼 풀 플러시
•
플러시 리스트 플러시
: 리두 로그 공간의 재활용을 위해 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워야 함
: 리두 로그 공간이 지워지려면 InnoDB 버퍼 풀의 더티 페이지가 디스크로 동기화되어야 한다.
: 이를 위해 주기적으로 플러시 리스트 플러시 함수를 호출
: 언제부터 얼마나 많은 더티 페이지를 한 번에 디스크로 기록하느냐에 따라 쿼리 처리 성능에 영향을 미치고, 이를 다양한 시스템 변수로 조정할 수 있다.
•
LRU 리스트 플러시
: LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 공간을 만들어야 하고, 이때 LRU 리스트 플러시 함수가 사용
5.
버퍼 풀 상태 백업 및 복구
: 버퍼 풀 덤프 및 적재 기능 도입으로 셧다운 전 현재 버퍼 풀의 상태를 백업하여 해당 정보를 토대로 복구
: 버퍼 풀의 LRU 리스트에서 적재된 데이터 페이지의 메타 정보만 가져와서 저장하기 때문에 백업 파일의 크기는 작고, 백업은 빠르게 완료
: 하지만 백업된 버퍼 풀의 내용을 다시 버퍼 풀로 복구하는 과정은 해당 내용을 디스크에서 읽어와야 하므로 시간이 걸림
6.
버퍼 풀의 적재 내용 확인
: information_schema 데이터 베이스에 innodb_cached_indexed 테이블을 이용하면, 테이블의 인덱스별로 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재돼 있는지 확인 가능
8. Double Write Buffer
: InnoDB의 리두 로그는 리두 로그 공간의 낭비를 막기 위해 페이지의 변경된 내용만 기록, 즉 더티 페이지, 더티 페이지를 디스크 파일로 플러시할 때 일부만 병합되면..? 그 페이지의 내용은 복구할 수가 없어짐,, 이를 방지하기 위한 기법
: 실제 데이터 파일에 변경 내용을 기록하기 전, 더티 페이지를 묶어서 한 번의 디스크 쓰기로 시스템 테이블 스페이스의 Double Wirte Buffer에 기록한다.
9. 언두 로그
: 트랜잭션과 격리 수준을 보장하기 위해 변경되기 전의 데이터를 별도로 백업하는 것
: 트랜잭션 보장, 격리 수준 보장을 위해 사용
•
트랜잭션 롤백 시, 언두 로그에 백업해 둔 이전 버전의 데이터를 이용해 복구
•
트랜잭션 격리 수준에 맞게 언두 로그에 백업해둔 데이터를 읽기도 함
1.
언두 로그 레코드 모니터링
•
MySQL 서버의 언두 로그 레코드 양을 항상 모니터링 하는 것이 좋고, 다음 명령으로 언두 로그 레코드 건수를 확인할 수 있음, SHOW ENGINE INNODB STATUS
2.
언두 테이블스페이스 관리
•
언두 로그가 저장되는 공간, 언두 로그는 항상 시스템 테이블스페이스 외부의 별도 로그 파일에 기록
•
언두 로그 공간이 남는 것은 크게 문제되지 않지만, 언두 로그 슬롯이 부족한 경우에는 트랜잭션을 시작할 수 없는 심각한 문제가 발생
•
언두 테이블스페이스 공간을 필요한 만큼만 남기고, 불필요하거나 과도하게 할당된 공간을 운영체제로 반납하는 것을 Undo tablespace truncate
◦
자동 모드
: 트랜잭션이 커밋되면 더이상 언두 로그에 복사된 이전 값은 불필요, InnoDB 스토리지 엔진의 퍼지 스레드가 주기적으로 불필요해진 언두 로그를 삭제 이를 언두 퍼지
◦
수동 모드
: 자동 모드의 성능이 별로일 때, 언두 테이블스페이스를 비활성화해서 언두 테이블스페이스가 더이상 사용되지 않도록 설정
: 퍼지 스레드는 비활성 상태 언두 테이블스페이스의 불필요한 공간을 잘라내고 운영체제로 공간을 반납, 수동 모드는 언두 테이블스페이스가 최소 3개 이상은 돼야 작동
10. 체인지 버퍼
: 레코드가 변경되면, 인덱스도 업데이트해야 한다. 인덱스 업데이트는 디스크를 랜덤하게 읽어야해서 많은 자원을 소모하므로 체인지 버퍼를 사용
•
체인지 버퍼
: 변경할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하고, 그렇지 않고 디스크에서 읽어와야 한다면 즉시 실행하지 않고 임시 공간에 저장해두고 바로 사용자에게
결과를 반환하는 형태로 성능을 향상시키는데, 이때 사용하는 임시 메모리공간이 체인지 버퍼
: 유니크 인덱스는 사용자에게 결과를 전달하기 전에 중복 여부를 체크해야하므로 체인지 버퍼를 사용할 수 없음
•
체인지 버퍼 머지 스레드
: 체인지 버퍼에 임시로 저장된 인덱스 레코드 조각을 백그라운드에서 병합하는 스레드
11. 리두 로그 및 로그 버퍼
: 리두 로그는 트랜잭션의 ACID에서 D에 해당하는 영속성과 밀접, 하드웨어나 소프트웨어 등 여러 가지 문제점으로 MySQL 서버가 비정상 종료시 데이터 파일에 미반영된 파일들을
소실되지 않게 해주는 안전장치
: 데이터 파일 쓰기는 디스크의 랜덤 액세스가 필요하므로 큰 비용이 필요, 이로 인한 성능 저하를 막기 위해 데이터베이스 서버는 쓰기 비용이 낮은 자료 구조를 가진 리두 로그를
사용, 비정상 종료가 발생하면 리두 로그의 내용을 이용해 데이터 파일을 서버가 종료되기 직전의 상태로 복구
•
서버가 비정상 종료되는 경우, 2개의 경우의 수 발생
◦
커밋됐지만 데이터 파일에 기록되지 않은 데이터 ⇒ 데이터 파일에 다시 복사
◦
롤백됐지만 데이터 파일에 이미 기록된 데이터 ⇒ 리두 로그로는 해결할 수 없음, 언두 로그의 내용을 가져와서 데이터 파일에 복사
데이터베이스 서버에서 리두 로그는 트랜잭션이 커밋되면 즉시 디스크로 기록되도록 시스템 변수를 설정하는 것을 권장
1.
리두 로그 아카이빙
: 아직 복사하지 못한 리두 로그가 덮어쓰이면 백업 툴이 리두 로그 엔트리를 복사할 수 없어 백업은 실패
: 리두 로그 아카이빙 기능은 데이터 변경이 많아서 리두 로그가 덮어쓰인다고 해도 백업이 실패해줌
2.
리두 로그 활성화 및 비활성화
: MySQL 서버에서 트랜잭션이 커밋돼도 데이터 파일은 즉시 디스크로 동기화되지 않지만, 리두 로그는 항상 디스크로 기록
: 데이터를 복구하거나 대용량 데이터를 한번에 적재하는 경우, 리두 로그를 비활성화해서 데이터 적재 시간을 단축
12. 어댑티브 해시 인덱스
: 어댑티브 해시 인덱스는 사용자가 수동으로 생성하는 인덱스가 아닌 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스
: B-Tree 인덱스에서 특정 값을 찾는 과정은 동시에 몇천 개의 스레드로 실행하면 CPU는 엄청난 프로세스 스케줄링을 하게 되고 쿼리 성능이 저하 어댑티브 해시 인덱스는 이러한
B-Tree 검색 시간을 줄여주기 위해 도입된 기능, 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 어뎁티브 해시 인덱스를 검색해서 레코드가
저장된 데이터 페이지를 즉시 찾아갈 수 있어지는데, 그만큼 CPU는 적은 일을 하고 쿼리 성능은 빨라짐
: 해시 인덱스의 키 값은 ‘B-Tree 인덱스의 고유번호와 B-Tree 인덱스의 실제 키 값’ 조합으로 생성 모든 B-Tree 인덱스에 대한 어댑티브 해시 인덱스가 하나의 해시 인덱스에
저장되며, 특정 키 값이 어느 인덱스에 속한 것인지 구분해야 하므로, 해시 인덱스의 키 값에 B-Tree 인덱스의 고유번호가 포함
: 어댑티브 해시 인덱스는 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리되고, 버퍼 풀에서 해당 데이터 페이지가 없어지면 어댑티브 해시 인덱스에서도 해당 페이지의 정보는 삭제
: 내부 잠금(세마포어) 경합을 줄이기 위해 어댑티브 해시 인텍스의 파티션 기능을 제공
: 어댑티브 해시 인덱스는 버퍼 풀 내에서 접근하는 것을 더 빠르게 만드는 기능이므로, 데이터 페이지를 디스크에서 읽어오는 경우가 빈번한 서버에서는 도움이 되지 않음
•
어댑티브 해시 인덱스가 성능 향상에 도움되지 않는 경우
◦
디스크 읽기 많은 경우
◦
특정 패턴의 쿼리가 많은 경우
◦
매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
•
어댑티브 해시 인덱스가 성능 향상에 도움이 되는 경우
◦
디스크 읽기가 많지 않은 경우 (디스크 데이터가 버퍼 풀 크기와 비슷한 경우)
◦
동등 조건 검색이 많은 경우
◦
쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우
•
어댑티브 해시 인덱스도 저장 공간인 메모리를 사용하며 테이블의 삭제, 변경 작업에 많은 영향을 주므로 사용하려는 서비스 패턴에 맞게 도움이 되는지 판단하고 사용
13. InnoDB와 MyISAM, MEMORY 스토리지 엔진 비교
: 8.0부터는 모든 시스템 테이블이 InnoDB 스토리지 엔진으로 교체
: MEMORY나 MyISAM 모두 추후 버전에선 삭제될 것이라고 저자는 생각함..
