InnoDB는 MySQL에서 사용할 수 있는 스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금을 제공하므로 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다. (트랜잭션, xa, savePoint)
- 개략적인 구조
프라이머리 키에 의한 클러스터링
모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링되어 저장된다.
프라이머리 키 값의 순서대로 디스크에 저장되고, 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용한다.
쿼리의 실행 계획에서 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정되어 있어 다른 보조 인덱스보다도 프라이머리 키가 선택될 확률이 높다.
MyISAM 스토리지 엔진에서는 클러스터링 키를 지원 x. 프라이머리 키와 세컨더리 인덱스도 차이 x
외래키 지원
외래 키에 대한 지원은 InnoDB 스토리지 엔진 레벨에서 지원하는 기능이다. (MyISAM, MEMORY에서는 없음)
InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 대해 인덱스 생성이 필요하고, 변경시에는 두 테이블 모두 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고 데드락이 발생할 때가 많다.
조급해서 긴급한 조치가 필요할 때 테이블의 순서대로 작업을 처리하는 것은 힘들다. → foreign_key_checks 시스템 변수를 off로 설정하면 외례키 관계에 대한 체크 작업을 일시적으로 멈출 수 있다.
MVCC (Multi Version Concurrency Control)
레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능.
가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 것이다.
InnoDB는 언두 로그를 이용해 이 기능을 구현하고 멀티 버전은 여러개의 버전이 동시에 관리된다는 의미
격리 수준이 READ_COMMITTED 일 때 InnoDB 스토리지 엔진을 사용하는 테이블의 데이터 변경을 어떻게 처리할까?
- 테이블을 생성하고 데이터 하나를 넣는다.
- update 문장을 실행한다.
update member set m_area='경기' where m_id = 12;
update 문장이 실행되면 커밋 실행 여부와 관계없이 버퍼 풀은 업데이트된다.
디스크의 데이터 파일에는 업데이트 되었을 수도 있고 안되었을 수도 있음( 보통 InnoDB는 ACID를 보장하므로 버퍼풀과 데이터 파일은 동일한 상태라고 가정해도 무방)
이때 커밋이나 롤백이 되지 않은 상태에서 위의 데이터를 조회하면 어떻게 될까?
결과는 시스템 변수에 설정된 격리 수준에 따라 다르다.
read_uncommited인 경우에는 버퍼 풀이 현재 가지고 있는 변경된 데이터를 반환한다.
read_commited의 이상인 경우에는 아직 커밋되지 않았으므로 변경되기 이전의 언두 영역의 데이터를 반환한다.
→ 위와 같은 과정을 DBMS 에서 MVCC라고 표현한다. 하나의 레코드에 대해 2개의 버전이 유지되고, 필요에 따라 어느 데이터가 보여지는지 달라지는 구조다.
커밋을 하면 InnoDb는 지금의 상태를 영구적인 데이터로 만들어버린다. 이때 언두 영역의 백업 데이터가 항상 바로 삭제되는 것은 아니다. 롤백을 실행하면 언두 영역에 있는 백업된 데이터를 InnoDb 버퍼 풀로 다시 복구하고 삭제해버린다.
잠금 없는 일관된 읽기(Non-Locking Consistent Read)
MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다.
잠금을 걸지 않으므로 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고, 읽기 작업이 가능하다.
격리수준의 SERIALIZABLE을 제외하고는 다른 트랜잭션의 변경 작업과 관계 없이 항상 잠금을 대기하지 않고 바로 실행된다. 이때 변경되기 전의 데이터를 읽기 위해 언두 로그를 사용한다.
오랫 시간 동안 활성 상태인 트랜잭션으로 인해 MySQL 서버가 느려지거나 문제가 발생할 때가 가끔 있는데, 이러한 일관된 읽기를 위해 언두 로그를 삭제하지 못하고 계속 유지해야 한다.
자동 데드락 감지
내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프 형태로 관리한다.
InnoDB 스토리지 엔진은 데드락 감지 스레드를 가지고 있어서 데드락 감지 스레드가 주기적으로 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 강제로 하나를 종료한다.
어떤 트랜잭션을 강제 종료할 것인지를 판단하는 기준은 트랜잭션의 언두 로그 양이며, 언두 로그 레코드를 더 적게 가진 트랜잭션이 일반적으로 롤백의 대상이 된다.
innodb_table_locks 시스템 변수를 활성화하면 엔진 내부의 레코드 잠금뿐만 아니라 테이블 레벨의 잠금까지 감지 가능해진다. (활성화 하는게 좋다.)
주의해야할 것이 동시 처리 스레드가 매우 많아지거나 트랜잭션의 잠금이 매우 많아지면 데드락 감지 스레드가 너무 느려져서 더 많은 CPU 자원을 소모할 수도 있다.
이때 innodb_deadlock_detect 시스템 변수를 off 로 설정하면 데드락 감지 스레드는 작동하지 않게 된다. 하지만 데드락 상황이 발생해도 무한정 대기하게 될 수 있으므로 innodb_lock_wait_timeout 시스템 변수를 낮게 변경해서 사용해야 한다.
InnoDB 버퍼 풀
디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이고 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할을 한다.
일반적인 어플리케이션에서는 데이터를 변경하는 쿼리는 데이터 파일의 이곳저곳에 위치한 레코드를 변경하기 때문에 랜덤한 디스크 작업을 발생시키는데 변경된 데이터를 모아서 처리하면 랜덤한 디스크 작업의 횟수를 줄일 수 있다.
버퍼 풀의 크기 설정
운영체제와 각 클라이언트 스레드가 사용할 메모리도 충분히 고려해서 설정해야 한다.
MySQL 서버 내에서 메모리를 필요로 하는 부분은 크게 없지만 아주 독특한 경우 레코드 버퍼가 상당한 메모리를 사용하기도 한다. 레코드 버퍼는 각 클라이언트 세션에서 테이블의 레코드를 읽고 쓸 때 버퍼로 사용하는 공간을 말하는데, 커넥션과 사용하는 테이블이 많아진다면 메모리 공간이 꽤 많이 필요해질 수 있다.
하지만 버퍼 공간은 동적으로 해제되기도 해서 정확히 필요한 메모리 공간의 크기를 계산할 수가 없다.
다행히 MySQL 5.7 부터는 InnoDB 버퍼 풀의 크기를 동적으로 조절할 수 있게 개선되었다. 그러므로 InnoDB 버퍼 풀의 크기를 적절히 작은 값으로 설정해서 조금씩 상황을 봐 가면서 증가시키는 것이 좋다.
InnoDB 버퍼 풀은 innodb_buffer_pool_size 시스템 변수로 크기를 설정할 수 있고 동적으로 버퍼풀의 크기를 확장할 수 있다.
InnoDB 버퍼 풀을 더 크게 변경하는 작업은 시스템 영향도가 크지 않은데, 줄이는 작업은 영향도가 매우 크므로 가능하면 버퍼 풀의 크기를 줄이는 작업은 하지 않은 것이 좋다.
버퍼 풀은 풀 전체를 관리하는 잠금으로 인해 내부 잠금 경합을 유발해왔는데, 이런 경합을 줄이기 위해 버퍼 풀을 여러 개로 쪼개어 관리할 수 있게 개선되었다. 여러개로 쪼개지면서 풀 전체를 관리하는 잠금 자체도 경합이 분산되는 효과를 가져갔다.
언두 로그
트랜잭션과 격리 수준을 보장하기 위해 DML 로 변경되기 이전 버전의 데이터를 별도로 백업한다.
백업된 데이터를 언두 로그라고 한다.
- 트랜잭션 보장
트랜잭션이 롤백되면 트랜잭션 도중 변경된 데이터를 변경 전 데이터로 복구해야 하는데, 이때 언두 로그에 백업해둔 이전 버전의 데이터를 이용해 복구한다.
- 격리 수준 보장
커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 데이터를 조회하면 트랜잭션 격리 수준에 맞게 변경중인 레코드를 읽지 않고 언두 로그에 백업해둔 데이터를 읽어서 반환하기도 한다.
언두 로그에 데이터가 정말 많이 쌓이면 디스크에 사용량이 증가하게 된다. 디스크에 정말 많이 쌓이게 되면 스토리지 엔진은 언두 로그의 이력을 필요한 만큼 스캔해야 필요한 레코드를 찾을 수 있기 때문에 쿼리 성능이 전반적으로 떨어지게 된다.
다행히 MySQL 8.0에서는 언두 로그를 돌아가면서 순차적으로 사용해 디스크 공간을 줄이는 것이 가능해졌고, 때로는 MySQL 서버가 필요한 시점에 사용 공간을 자동으로 줄여주기도 한다.
언두 로그가 저장되는 공간 : 언두 테이블 스페이스
체인지 버퍼
레코드가 insert되거나 update 될 때 데이터 파일을 변경하는 작업뿐 아니라 해당 테이블에 포함된 인덱스를 업데이트하는 작업도 필요하다.
인덱스를 업데이트하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱스가 많다면 이 작업은 상당히 많은 자원을 소모하게 된다.
그래서 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만 그렇지 않고 디스크로부터 읽어와서 업데이트해야 한다면 임시 공간에 저장해두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시킨다.
이 때 사용하는 임시 메모리 공간을 체인지 버퍼라고 한다.
리두 로그 및 로그 버퍼
트랜잭션 4가지 요소인 ACID 중에서 D에 가장 밀접
리두 로그는 하드웨어나 소프트웨어 등 여러가지 문제점으로 인해 MySQL 서버가 비정상적으로 종료됐을 대 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치다.
대부분의 데이터베이스 서버는 데이터 변경 내용을 로그로 먼저 기록한다.
- 커밋됐지만 데이터 파일에 기록되지 않은 데이터
- 롤백됐지만 데이터 파일에 이미 기록된 데이터
1번의 경우에는 리두 로그에 저장된 데이터를 데이터 파일에 다시 복사하기만 하면 된다.
2번의 경우에는 리두 로그로는 해결할 수 없고, 변경되기 전 데이터를 가진 언두 로그의 내용을 가져와 데이터 파일에 복사하면 된다.
리두 로그는 커밋됐는지, 롤백됐는지, 트랜잭션의 실행 중간 상태였는지 확인하기 위해서라도 필요하다.
어댑티브 해시 인덱스
사용자가 수동으로 생성하는 인덱스가 아니라 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성되는 인덱스다.
자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고, 필요할 대마다 어댑티브 해시 인덱스를 검색해서 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있다.
B-Tree를 루트 노드부터 리프 노드까지 찾아가는 비용은 없어지고 쿼리의 성능은 빨라진다.
참고
Real MySQL 8.0
