InnoDB 스토리지 엔진 아키텍처

InnoDB 버퍼풀

언두 로그

InnoDB 스토리지 엔진은 트랜잭션과 격리 수준을 보장하기 위해 DML(INSERT, UPDATE, DELETE)로 변경되기 이전 버전의 데이터를 별도로 백업한다.

이렇게 백업된 데이터를 언두 로그라고 한다.

언두 로그는 다음과 같이 사용된다.

트랜잭션 보장

트랜잭션이 롤백되면 트랜잭션 도중 변경된 데이터를 변경 전 데이터로 복구해야 하는데, 이때 언두 로그에 백업해둔 이전 버전의 데이터를 이용해 복구한다.

격리 수준 보장

특정 커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 데이터를 조회하면 트랜잭션 격리 수준에 맞게 변경중인 레코드를 읽지 않고 언두 로그에 백업해둔 데이터를 읽어서 반환하기도 한다.

언두 로그는 InnoDB 스토리지 엔진에서 매우 중요한 역할을 담당하지만 관리 비용도 많이 필요하다.

언두 로그 모니터링

언두 영역은 INSERT, UPDATE, DELETE와 같은 문장으로 데이터를 변경했을 때 변경되지 전의 데이터를 보관하는 곳이다.

UPDATE member SET name='홍길동' where member_id=1;

위 문장이 실행되면 트랜잭션을 커밋하지 않아도 실제 데이터 파일 내용은 '홍길동'으로 변경된다.

그리고 언두 영역에는 변경되기 전 값이 백업된다.

이 상태에서 커밋이 이뤄지면 상태가 그대로 유지되고, 롤백이 되면 백업된 데이터가 다시 복구된다.

언두 로그의 데이터는 두 가지 용도로 사용되는데, 첫 번째 용도가 위에 예시의 트랜잭션의 롤백 대비용이다.

두 번째 용도는 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공하는 데 있다.

해당 섹션 초반부에 나온 MVCC가 이것을 말한다.

MySQL 5.5 이전에는 한 번 증가한 언두 로그 공간은 다시 줄어들지 않았다.

1억 건의 레코드가 저장된 100GB 크기의 테이블의 모든 레코드를 DELETE로 삭제하거나 UPDATE로 변경하면, 언두 로그 공간이 100GB가 된다.

책에서는 레코드를 한 건 삭제한다고 나와있는데, 잘못 설명한 것 같다.

또한 트랜잭션이 오래 실행될 때도 언두 로그의 양은 급격히 증가할 수 있다.

한 트랜잭션에서 실수 또는 버그로 인해 트랜잭션이 닫히지 않았다면, 언두 로그 또한 삭제되지 않고 계속 누적되어 유지된다.

MVCC 파트에서 설명했었다.

MySQL 5.5 버전까지는 이렇게 언두 로그의 사용 공간이 한 번 늘어나면 MySQL 서버를 새로 구축하기 전까지 줄일 수 없었다.

하지만 MySQL 5.7과 8.0으로 업그레이드 되면서 언두 로그 공간의 문제점은 해결됐다.

MySQL 8.0에서는 언두 로그를 돌아가면서 순처적으로 사용해 디스크 공간을 줄이는 것도 가능하며, 때로는 필요한 시점에 사용 공간을 자동으로 줄여주기도 한다.

show engine innodb status \g 명령으로 언두 로그 건수를 확인할 수 있다.

MySQL에서 INSERT 문장으로 인한 언두 로그와 UPDATE, DELETE 문장으로 인한 언두 로그는 별도로 관리된다.
차이는 UPDATE, DELETE는 MVCC와 롤백에 모두 사용되지만, INSERT는 롤백에서만 사용되기 때문이다.
위의 개수 또한 UPDATE, DELETE 문장으로 인한 언두 로그 개수만 표시된다.

언두 테이블스페이스 관리

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

MySQL 5.6 이전에서는 언두 로그가 모두 시스템 테이블스페이스(ibdata.ibd)에 저장됐다.

이후 MySQL 5.6 부터는 innodb_undo_tablespaces 시스템 변수가 도입됐고, 해당 변수를 2보다 큰 값을 설정하면 InnoDB 스토리지 엔진은 더이상 언두 로그를 시스템 테이블스페이스에 저장하지 않고 별도의 언두 로그 파일을 사용한다.

하지만 이것도 MySQL 8.0.14 이전의 이야기이고, 이후 innodb_undo_tablespaces 변수는 deprecated 처리되었다.

언두 로그는 MySQL 인스턴스가 초기화될 때, 두 개의 기본 언두 테이블스페이스가 생성되며, 최대 127개의 언두 테이블스페이스를 지원한다.

하나의 언두 테이블스페이스에는 1개 이상 128개 이하의 롤백 세그먼트를 가지며, 롤백 세그먼트는 1개 이상의 언두 슬롯을 가진다.

롤백 세그먼트의 기본 값은 128이다.

롤백 세그먼트의 언두 슬롯 개수는 InnoDB 페이지 크기에 따라 달라지는데, InnoDB 페이지 크기/16을 하면 된다.

그리고 언두 슬롯에는 4가지 종류가 있다.

1. 사용자 정의 테이블 INSERT
2. 사용자 정의 테이블 UPDATE, DELETE
3. 임시 테이블 INSERT
4. 임시 테이블 UPDATE, DELETE

일반적으로 트랜잭션이 임시 테이블을 사용하지 않으므로 하나의 트랜잭션은 2개 정도의 언두 슬롯을 필요하다 가정하면, 최대 동시 처리 가능한 트랜잭션의 개수는 다음과 같이 측정할 수 있다.

16 * 1024(InnoDB 페이지 크기) / 16 * 128(롤백 세그먼트 개수) * 2(언두 테이블스페이스 개수) / 2(언두 슬롯) => 131,072개

일반적인 서비스에서 이 정도까지 동시 트랜잭션이 필요하지 않으므로 기본값을 유지해도 문제가 없을 것이다.

만약 언두 로그 슬롯이 부족한 경우에는 트랜잭션을 시작할 수 없는 심각한 문제가 발생하므로, 언두 테이블스페이스 개수를 늘려야한다.

MySQL 8.0 이전까지는 한 번 생성된 언두 로그는 변경이 허용되지 않고 정적으로 사용됐지만, 8.0.14 부터 CREATE UNDO TABLESPACE, DROP UNDO TABLESPACE 명령으로 새로운 언두 테이블스페이스를 동적으로 추가하고 삭제할 수 있다.

언두 테이블스페이스 공간을 필요한 만큼만 남기고 불필요하거나 과도하게 할당된 공간을 운영체제로 반납하는 것을 Undo tablespace truncate라고 하는데, 언두 테이블스페이스의 불필요한 공간을 잘라내는 방법은 자동과 수동으로 하는 두 가지 방법이 있다.

기본으로 설정되고 활성화된 방법은 자동 모드이고, innodb_undo_log_truncate 변수를 통해 비활성화 할 수 있다.

체인지 버퍼

RDBMS에서 레코드가 INSERT되거나 UPDATE될 때는 데이터 파일을 변경하는 작업뿐 아니라 해당 테이블에 포함된 인덱스를 업데이트하는 작업도 필요하다.

그런데 인덱스를 업데이트하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱스가 많다면 이 작업은 상당히 많은 자원을 소모하게 된다.

InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만, 그렇지 않고 디스크에서 읽어와 업데이트해야 한다면 임시 공간에 저장해 두고 사용자에게 결과를 반환하는 형태로 성능을 향상시켰다.

이때 사용하는 임시 메모리 공간을 체인지 버퍼라고 한다.

책에서는 유니크 인덱스는 체인지 버퍼를 사용할 수 없다고 나와있는데, 인덱스에 내림차순 설정이 된 경우에도 체인지 버퍼를 사용할 수 없다.

MySQL 5.5 이전에는 INSERT 작업에 대해서만 버퍼링이 가능했지만, 5.5 이후 조금씩 개선되며 8.0에서는 INSERT, UPDATE, DELETE 작업에서도 버퍼링이 될 수 있게 개선됐다.

또한 innodb_change_buffering 변수를 통해 체인지 버퍼가 비효율적일 때는 체인지 버퍼를 사용하지 않게 설정할 수 있다.

체인지 버퍼는 버퍼 풀의 메모리 공간을 사용하므로 버퍼 풀이 최대 캐시할 수 있는 데이터가 줄어들 수 있다.

따라서 체인지 버퍼를 모니터링하고, 적절한 체인지 버퍼 용량을 설정하려 한다면 innodb_change_buffer_max_size 변수를 통해 체인지 버퍼의 용량을 백분율로 조절할 수 있다.

리두 로그 및 로그 버퍼

리두 로그는 트랜잭션의 4가지 요소인 ACID에서 D(Durable)와 가장 연관돼 있다.

리두 로그는 하드웨어나 소프트웨어 등 여러 문제점으로 인해 MySQL이 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치다.

MySQL을 포함한 대부분 데이터베이스는 데이터 변경 내용을 로그로 먼저 기록한다.

대부분 DBMS에서는 읽기 성능을 고려한 자료 구조를 가지고 있기에, 쓰기는 디스크의 랜덤 액세스가 필요한데, 따라서 변경된 데이터를 파일에 기록하는 것은 큰 비용이 필요하다.

따라서 데이터베이스는 쓰기 비용이 낮은 자료 구조를 가진 리두 로그를 가지고 있으며, 비정상 종료가 발생하면 리두 로그의 내용을 이용해 데이터 파일을 서버가 종료되기 이전으로 복구한다.

데이터베이스는 ACID도 중요하지만, 성능도 중요하기에 리두 로그를 버퍼링할 수 있는 로그 버퍼와 같은 자료 구조도 가지고 있다.

MySQL이 비정상 종료되는 경우 InnoDB 스토리지 엔진의 데이터 파일은 두 가지 종류의 일관되지 않은 데이터를 가질 수 있다.

1. 커밋됐지만 데이터 파일에 기록되지 않은 데이터
2. 롤백됐지만 데이터 파일에 이미 기록된 데이터

1번의 경우 리두 로그에 저장된 데이터를 데이터 파일에 다시 복사하면 되지만, 2번의 경우 리두 로그로는 해결할 수 없다.

이때는 변경되기 전 데이터를 가진 언두 로그의 내용을 가져와 데이터 파일에 복사하면 된다. 하지만 리두 로그가 전혀 필요하지 않은 것은 아니고, 최소한 그 변경이 커밋됐는지, 롤백됐는지, 아니면 트랜잭션의 실행 중간 상태였는지를 판단하기 위해서라도 리두 로그가 필요하다.

데이터베이스 서버에서 리두 로그는 트랜잭션이 커밋되면 즉시 디스크로 기록되도록 시스템 변수를 설정하는 것을 권장한다.

하지만 이렇게 트랜잭션이 커밋될 때마다 리두 로그를 디스크에 기록하는 작업은 많은 부하를 유발하기에 innodb_flush_log_at_trx_commit 변수를 통해 리두 로그가 디스크로 언제 동기화할 지 결정할 수 있다.

또한 위에서 언급했지만, 리두 로그의 크기는 버퍼 풀의 효율성을 결정하기 때문에 신중하게 결정해야 한다. (리두 로그가 꽉 차면 디스크 IO 발생)

책에서는 innodb_log_file_size, innodb_log_files_in_group 시스템 변수를 통해 리두 로그 크기를 설정한다고 했지만 마찬가지 위에서 언급했듯이 해당 값은 deprecated 되었기에 사용하지 안 된다!

또한 BLOB, TEXT 같이 큰 데이터를 변경하는 경우에는 로그 버퍼 크기를 늘리는 것 또한 디스크 IO를 줄이는 것에 도움이 될 수 있다.

리두 로그 아카이빙

DBA와 관련된 내용이고, Enterprise 에디션 또는 다른 툴을 사용해야 하므로 생략
자세한 내용은 공식 문서 참고하는게 더 정확할 듯

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

리두 로그를 비활성화가 된 상태에서 비정상적인 서버 종료가 발생하면 데이터 손실이나 인스턴스 손상이 발생할 수 있다.

따라서 비활성화 하는 것 보다는 innodb_flush_log_at_trx_commit 설정을 통해 사용할 것을 권장한다.

애초에 정합성을 위한 트랜잭션 때문에 RDB를 사용하는 것인데, 해당 설정을 수정하려면 RDB를 사용할 필요가 없지 않을까?

MySQL 8.0 이전에는 수동으로 리두 로그를 비활성화할 수 없었는데, 이후에는 비활성화 할 수 있게 기능이 추가되었다.

이유는 새로운 MySQL 인스턴스를 실행하며 데이터를 로드할 때, 리두 로그 쓰기와 이중 쓰기 버퍼링을 해제하여 시간을 단축하기 위함이다.

공식 문서에서도 이 기능은 데이터를 로드하는 용도로만 사용하라고 나와있다.

어댑티브 해시 인덱스

인덱스라고 하면 일반적으로 B-Tree 인덱스를 의미한다.

하지만 굳이 B-Tree 인덱스가 아니더라도, 사용자가 직접 테이블에 생성해둔 인덱스가 일반적으로 알고 있는 인덱스일 것 이다.

여기서 나오는 어댑티브 해시 인덱스는 사용자가 수동으로 생성하는 인덱스가 아닌, InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 의해 자동으로 생성하는 인덱스이다.

B-Tree 인덱스에서 특정 값을 찾는 과정은 매우 빠르게 처리되지만, 이것은 상대적인 것이며 B-Tree의 구조가 깊을 수록 시간은 오래 걸린다.

어댑티브 해시 인덱스는 이러한 B-Tree의 검색 시간을 주여주기 위해 도입된 기능이며, 이를 통해 버퍼 풀에 충분한 메모리가 있는 시스템에서 인메모리 데이터베이스처럼 작동할 수 있다.

어댑티브 해시 인덱스는 자주 읽히는 데이터페이지의 키 값을 기반으로 해시 인덱스를 만들고, 값으로 버퍼 풀의 데이터 페이지 주소를 가진다.

JIT 컴파일러처럼 동작한다고 생각하면 될 것 같다.

MySQL 8.0 이전에는 하나의 메모리 객체이기 때문에 경합이 심했는데, 8.0 이후에는 파티션으로 분할할 수 있는 기능이 제공되어 경합도 그만큼 줄게 되었다.

innodb_adaptive_hash_index_parts 변수로 설정할 수 있다. (기본값 8)

하지만 어댑티브 해시 인덱스가 성능 향상에 크게 도움이 되지 않는 경우도 많은데, 다음과 같은 경우이다

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

다음과 같은 경우에 성능 향상에 많은 도움이 된다.

• 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우 (데이터가 버퍼 풀에 모두 적재될 수 있을 경우)
• 동등 조건 검색(=, IN 연산)이 많은 경우
• 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우

하지만 단순히 어댑티브 해시 인덱스가 도움이 될지 안될지를 판단하기는 쉽지 않은데, 확실한 것은 어댑티브 해시 인덱스는 데이터 페이지를 버퍼 풀 내에서 접근하는 것을 빠르게 만드는 기능이다.

그렇기 때문에 데이터 페이지를 디스크에서 읽어오는 경우가 빈번한 데이터베이스 서버에서는 아무런 도움이 되지 않는다.

또한 어댑티브 해시 인덱스를 사용하기 위해 메모리를 사용하며, InnoDB 스토리지 엔진은 어댑티브 해시 인덱스에 값이 있든 없든 조회를 해봐야한다.

그리고 테이블을 DROP, ALTER로 변경한다면 해당 테이블이 가진 모든 데이터 페이지 내용은 새롭게 갱신되어야 한다.

따라서 어댑티브 해시 인덱스에 있는 값들 또한 제거되어야 하므로 많은 CPU 자원을 소비하게 된다.

하지만 DROP, ALTER가 빈번하게 발생하는 작업은 아니므로 큰 단점은 아니라고 생각한다.

이러한 이유로 인해 어댑티브 해시 인덱스의 사용 유무를 결정하는 것을 고려해야 하는데, 가장 확실한 방법은 어댑티브 해시 인덱스가 기본 활성화 되어 있으므로 모니터링을 통해 제대로 활용되고 있는지를 확인하면 된다.

어댑티브 해시 인덱스 히트율 확인

show engine innodb status\g

(hash searches/s) / ((hash searches/s) + (non-hash searches/s)) * 100을 하면 히트율을 대락 가늠할 수 있다.

어댑티브 해시 인덱스 메모리 사용량 확인

select event_name, current_number_of_bytes_used from performance_schema.memory_summary_global_by_event_name where event_name = 'memory/innodb/adaptive hash index';

InnoDB와 MyISAM, MEMORY 스토리지 엔진 비교

예전에는 MyISAM이 기본 스토리지 엔진으로 사용되었지만, 최근에는 InnoDB가 기본으로 사용되고 공간 좌표 검색이나 전문 검색 등 모두 InnoDB를 지원하도록 개선됐다.

따라서 MyISAM 스토리지 엔진을 사용할 이유가 없다.

가끔 MyISAM 또는 MEMORY 스토리지 엔진이 그래도 InnoDB 스토리지 엔진보다 더 나은 성능이 있을 것이라고 기대하는 경우가 있는데, MySQL 5.1과 5.5 버전이라면 의미가 있겠지만, 8.0 이후에는 더 이상 무의미한 비교가 될 것으로 보인다.

MEMORY 스토리지 엔진이 MEMORY 라는 이름 때문에 과대평가를 받을 수 있는데, 정말 단순한 경우에만 그렇고, 트랜잭션이 적용되는 순간 MVCC와 같은 동시성 처리 기능을 지원받을 수 없기에 성능은 매우 떨어지게 된다.

또한 사용자의 쿼리를 처리하기 위해 임시 테이블을 사용할 필요가 있는데, 5.7 이전까지는 MEMORY 스토리지 엔진이 내부 임시 테이블 용도로 사용되었으나, 가변 길이 타입 컬럼(VARCHAR, VARBINARY)을 지원하지 않는 문제점 때문에 TempTable 스토리지 엔진이 사용된다.

따라서 MyISAM, MEMORY 스토리지 엔진을 선택해서 얻을 수 있는 장점은 없으며, 이전 버전과 하위 호환을 위해 사용되는 것으로 생각할 수 있다.

MyISAM 스토리지 엔진 아키텍쳐

InnoDB의 핵심이 버퍼 풀이라면, MyISAM은 키 캐시가 그 역할을 한다.

키 캐시

이름 그래도 키 캐시는 인덱스를 대상으로 작동하며, 인덱스의 디스크 쓰기 작업에 대해서만부분적으로 버퍼링 역할을 한다.

키 캐시가 얼마나 효율적으로 작동하는지는 다음 수식으로 간단히 확인할 수 있다.

100 - (Key_reads / Key_read_requests * 100)

MyISAM은 사용할 일이 없기에 자세한 내용은 공식 문서를 참고하자

MySQL 로그 파일

MySQL의 상태를 진단하는 많은 도구들이 지원되지만, 이러한 기능들을 사용하기 위해 도구의 사용법, MySQL의 내부 지식을 필요로 하는 경우가 많다.

하지만 로그 파일을 이용하면 MySQL의 내부 지식이 없더라도 상태나 부하를 일으키는 원인을 쉽게 찾아서 해결할 수 있다.

많은 사용자가 이를 무시하고 다른 방법으로 해결책을 찾으려고 노력하곤 하는데, MySQL에 문제가 생기면 로그 파일을 자세히 확인하는 습관을 들일 필요가 있다.

에러가 발생했을 때, 로그를 분석하는 것은 MySQL뿐 아니라 다른 곳에서도 통용되는 것이 아닐까

에러 로그 파일

MySQL이 실행되는 도중 발생하는 에러나 경고 메시지가 출력되는 로그 파일이다.

에러 로그 파일의 위치는 MySQL 설정 파일(my.cnf)에서 log_error라는 이름의 파라미터로 정의된 경로에 생성된다.

기본 설정은 /etc/mysql/mysql.conf.d/mysqld.cnf에서 /var/log/mysql/error.log로 적혀있다.

그 외 에러 로그에 대한 자세한 내용은 책을 참고하거나, 공식 문서를 참고하자.

제너럴 쿼리 로그 파일

MySQL 서버에서 실행되는 쿼리로 어떤 것들이 있는지 전체 목록을 뽑아서 검토해 볼 때가 있는데, 이때는 쿼리 로그를 활성화하여 쿼리를 로그 파일로 기록하게 한 다음, 해당 파일을 검토하면 된다.

에러 로그와 마찬가지로 MySQL 설정 파일에서 설정할 수 있지만, general_log 변수를 통해 활성화 할 수 있다.

제너럴 쿼리 로그는 쿼리가 실행되기 전에 기록되므로, 쿼리 실행 중 에러가 발생해도 기록된다.

제너럴 로그를 활성화하는 것은 성능에 매우 매우 않은 영향을 준다고 한다. 😂

슬로우 쿼리 로그

MySQL 쿼리 튜닝은 서비스에 적용하기 전에 전체적으로 튜닝하는 경우와 서비스 운영중에 전체적인 성능 점검을 위한 튜닝으로 나눌 수 있다.

전자의 경우에는 어떤 쿼리를 호출했는지 눈에 바로 보이기 때문에 문제가 없지만, 운영중에는 어떤 쿼리가 문제의 쿼리인지 판단하기가 어렵다.

따라서 이때는 슬로우 쿼리 로그가 많은 도움이 된다.

슬로우 쿼리 로그는 slow_query_log 변수로 활성화 할 수 있고, long_query_time 변수에 설정한 시간(초 단위지만, 소수점을 포함하면 마이크로 초 단위) 이상의 시간이 소요된 쿼리가 모두 기록된다.

슬로우 쿼리 로그는 쿼리가 실행된 후, 실제 소요된 시간을 기준으로 슬로우 쿼리 로그에 기록할 지 판단하기 때문에, 반드시 쿼리가 정상적으로 실행이 완료돼야 기록된다.

또한 기본적으로 인덱스를 사용하지 않는 쿼리도 기록되지 않기 때문에 log_queries_not_using_indexes 변수 설정을 통해 필요하다면 해당 값을 변경해야 한다.

InnoDB 스토리지 엔진의 경우 잠금이 MySQL 엔진 레벨 뿐 아닌, 스토리지 엔진 레벨에도 잠금이 있기에 슬로우 쿼리에 출력되는 내용이 혼란스러울 수 있다.

이에 자세한 내용은 책을 참고하자.

그 외 슬로우 쿼리 또는 제너럴 로그 파일의 내용이 많아 쿼리의 내용을 검토하기 힘들 경우에는 Percona Toolkit의 pt-query-digest 스크립트를 이용하면 쉽게 빈도나 처리 성능별로 쿼리를 정렬해서 살펴볼 수 있다.

특정 툴의 학습에 관한 내용이므로 생략한다.