BackEnd/Real MySQL 8.0

[Real MySQL 8.0] InnoDB 스토리지 엔진 아키텍처 2/2

짱호 2022. 5. 29. 16:12
반응형

언두 로그

언두 로그란 트랜잭션과 격리 수준을 보장하기 위해 DML(INSERT, UPDATE, DELETE)로 변경되기 이전 버전의 데이터를 별도로 백업해둔 데이터를 말한다. 언두 로그의 데이터는 크게 트랜잭션의 롤백 대비용 데이터와 격리 수준을 유지하면서 높은 동시성을 제공하기 위한 용도로 사용된다.

  • 트랜잭션 보장
    • 트랜잭션이 롤백되면 언두 로그에 백업해둔 이전 버전의 데이터를 이용해 복구한다.
  • 격리 수준 보장
    • 데이터를 변경하는 도중 다른 커넥션에서 데이터를 조회하면 격리 수준에 맞게(READ_COMMITTED, REPEATABLE READ) 언두 로그에 백업해둔 데이터를 읽어서 반환한다.

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

 

언두 로그 레코드 모니터링

서비스 중인 MySQL 서버에서 활성 상태의 트랜잭션이 장시간 유지되는 것은 성능상 좋지 않다.

예를 들어, 트랜잭션을 시작한 상태에서 완료하지 않고 하루 정도 방치했다고 가정해보자.
이 경우 트랜잭션이 시작된 시점부터 생성된 언두 로그는 계속 InnoDB 스토리지 엔진에 보존될 것이며, 결국 하루치 데이터 변경을 모두 저장하고 디스크의 언두 로그 저장 공간은 계속 증가하게 된다.

빈번히 변경된 레코드를 조회하는 쿼리가 실행되면 InnoDB 스토리지 엔진은 언두 로그의 이력을 필요한 만큼 스캔해야만 레코드를 찾을 수 있다. 따라서 쿼리의 성능이 전반적으로 떨어지게 되는 결과를 얻게 된다.

MySQL 5.5 버전까지는 한 번 증가한 언두 로그 공간은 다시 줄어들지 않는 문제도 있었지만, 이 문제는 MySQL 5.7과 MySQL 8.0으로 업그레이드되면서 완전히 해결되었다.

하지만 장시간 트랜잭션이 유지되는 것은 성능상 좋지 않은 건 마찬가지이므로 MySQL 서버의 언두 로그 레코드가 얼마나 되는지는 항상 모니터링하는 것이 좋다.

- // MySQL 서버의 모든 버전에서 사용 가능한 명령
SHOW ENGINE INNODB STATUS \G

- // MySQL 8.0 버전에서 사용 가능한 명령
SELECT count
FROM information_schema.innodb_metrics
WHERE SUBSYSTEM='transaction' AND NAME='trx_rseg_history_len';

 

언두 테이블스페이스 관리

언두 로그가 저장되는 공간을 언두 테이블스페이스라고 하는데, 이 공간은 서버가 초기화될 때 생성되기 때문에 확장에 한계가 있었다. 따라서 언두 테이블스페이스는 MySQL 버전별로 많은 변화가 있었다.

  • MySQL 5.6 이전 버전
    • 언두 로그가 모두 테이블스페이스에 저장
  • MySQL 5.6 버전
    • innodb_undo_tablesapces 시스템 변수 도입
    • innodb_undo_tablesapces 시스템 변수 값을 0으로 설정한 경우 언두 로그를 시스템 테이블스페이스에 저장
    • innodb_undo_tablesapces 시스템 변수 값을 2보다 큰 값으로 설정한 경우 별도의 언두 로그 파일에 저장
  • MySQL 8.0
    • innodb_undo_tablesapces 시스템 변수 Deprecated
    • 언두 로그를 항상 외부의 별도 로그 파일에 기록

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

하나의 트랜잭션이 필요로 하는 언두 슬롯의 개수는 DML의 특성에 따라 최대 4개까지 사용하게 된다. 우리는 다음과 같은 수식으로 최대 동시 처리 가능 트랜잭션 개수를 예측해 볼 수 있다.

최대 동시 트랜잭션 수 = (InnoDB 페이지 크기) / 16 * (롤백 세그먼트 개수) ******* (언두 테이블스페이 스 개수)

언두 로그 공간이 남는 것은 문제가 되지 않지만, 언두 로그 슬롯이 부족한 경우 트랜잭션을 시작할 수 없는 심각한 문제가 발생한다. MySQL 8.0 이전에는 한 번 생성된 언두 로그는 변경이 허용되지 않고 정적으로 사용되었지만 MySQL 8.0 버전부터는 CREATE UNDO TABLESPACE나 DROP TABLESPACE 같은 명령으로 언두 테이블스페이스를 동적으로 추가하고 삭제할 수 있게 개선되었다.

 

체인지 버퍼

체인지 버퍼는 변경해야 할 인덱스 페이지를 디스크로부터 읽어와야 하는 경우 자원 소모를 줄이기 위해 사용되는 임시 메모리 공간을 뜻한다.

레코드가 INSERT 혹은 UPDATE 될 때 데이터 파일을 변경하는 작업뿐 아니라 해당 테이블에 포함된 인덱스도 업데이트하는 작업이 필요하다. 그런데 테이블에 인덱스가 많다면 이 작업은 디스크를 랜덤하게 읽는 작업이 필요하다. InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만, 그렇지 않은 경우 디스크로부터 읽어와야 하기 때문이다. 따라서 이러한 작업은 상당히 많은 자원을 소모하게 된다.

이러한 자원 소모를 막기 위해 InnoDB는 즉시 업데이트를 실행하지 않고 인덱스 레코드를 체인지 버퍼에 저장해두었다가 사용자에게 결과를 반환하는 형태로 성능을 향상시킨다. 이후 체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 버퍼 머지 스레드라는 백그라운드 스레드에 의해 병합된다.

MySQL 5.5 이전 버전까지는 INSERT 작업에 대해서만 이러한 버퍼링 기능(인서트 버퍼)을 사용할 수 있었지만, MySQL 5.5 버전부터 조금씩 개선되며 MySQL 8.0 버전부터는 INSERT, UPDATE, DELETE로 인해 키를 추가하거나 삭제하는 작업에 대해서도 버퍼링이 될 수 있게 개선되었다.

 

리두 로그 및 로그 버퍼

리두 로그

리두 로그는 트랜잭션의 4가지 요소인 ACID(원자성, 일관성, 격리성, 지속성) 중 D에 해당하는 영속성과 가장 밀접하게 연간 돼 있으며, 하드웨어나 소프트웨어 등 여러 가지 문제점으로 인해 MySQL 서버가 비정상적으로 종료되었을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치 역할을 한다.

모든 DBMS는 읽기 성능을 고려한 자료구조를 가지고 있어 쓰기는 디스크의 랜덤 액세스가 필요하다. 즉, 쓰기에 더 큰 비용이 필요하다. 이로 인한 성능 저하를 막기 위해 쓰기 비용이 낮은 자료 구조를 가진 리두 로그를 가지고 있다.

MySQL 서버가 비정상 종료되는 경우 다음과 같은 일관되지 않은 데이터를 가질 수 있다.

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

1번의 경우 리두 로그에 저장된 데이터를 다시 복사하기만 하면 되지만, 2번의 경우 리두 로그로 해결할 수 없어 언두 로그의 내용을 가져와 데이터 파일에 복사해야 한다. 트랜잭션의 상태(커밋, 롤백, 실행 중)를 확인하기 위해 2번의 경우에도 리두 로그는 필요하다.

 

로그 버퍼

변경 작업이 매우 많은 DBMS 서버의 경우 리두 로그의 기록 작업이 성능 저하로 이어질 수 있다. 이러한 부분을 보완하기 위해 ACID 속성을 보장하는 수준에서 리두 로그 버퍼링을 수행하는데, 이때 사용되는 공간이 로그 버퍼다.

 

리두 로그 아카이빙

MySQL 서버에 유입되는 데이터 변경이 너무 많으면 리두 로그가 빠르게 증가하고, 새로 추가되는 리두 로그 내용을 복사하기 전에 덮어 쓰일 수 있다. 이렇게 되면 데이터 백업 파일은 일관된 상태를 유지하지 못하고 데이터 백업에 실패하게 된다.

이러한 문제를 해결하기 위해 MySQL 8.0 버전부터는 리두 로그 아카이빙 기능을 지원하여 데이터 변경이 많아서 리두 로그가 덮어 쓰인다고 해도 백업이 실패하지 않도록 하는 기능이 추가되었다.

 

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

MySQL 8.0 버전부터는 리두 로그를 수동으로 활성화하거나 비활성화할 수 있게 됐다. 데이터를 복구하거나 대용량 데이터를 한 번에 적재하는 경우 리두 로그를 비활성화해 데이터 적재 시간을 줄일 수 있다.

// 리두 로그 비활성화
ALTER INSTANCE DISABLE INNODB REDO_LOG;

... 대량 데이터 적재 ...

// 리두 로그 활성화
ALTER INSTANCE ENABLE INNODB REDO_LOG;

 

어댑티브 해시 인덱스

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

B-Tree의 검색 시간을 줄이기 위해 도입된 기능으로, 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고 필요할 때마다 어댑티브 해시 인덱스를 검색해 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있게 해 준다.

해시 인덱스는 인덱스 키 값과 해당 인덱스의 키 값이 저장된 데이터 페이지 주소의 쌍으로 관리된다. 이때 인덱스 키 값은 B-Tree 인덱스의 고유 ID와 B-Tree 인덱스의 실제 키 값의 조합으로 생성된다.

데이터 페이지 주소는 실제 키 값이 저장된 데이터 페이지의 메모리 주소를 가지는데, 이는 InnoDB 버퍼 풀에 로딩된 페이지 주소를 의미한다.

 

어댑티브 해시 인덱스로 인한 성능 향상

어댑티브 해시 인덱스를 사용한다고 해서 무조건 성능이 향상되는 것은 아니다.

성능 향상에 크게 도움이 되지 않는 경우

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

성능 향상에 도움이 되는 경우

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

어댑티브 해시 인덱스 또한 데이터 페이지의 인덱스 키가 해시 인덱스로 만들어져야 하고 불필요한 경우 제거돼야 하며, 어댑티브 해시 인덱스가 활성화되면 InnoDB 스토리지 엔진은 그 키 값이 해시 인덱스에 있든 없든 검색해봐야 한다. 따라서 효율이 없는 해시 인덱스를 InnoDB는 계속 사용하게 된다.

우리는 서비스 패턴을 파악해 어댑티브 해시 인덱스가 도움이 되고 있는지 불필요한 오버헤드를 발생시키고 있는지 판단해 적절히 사용해야 한다.

 

MySQL 로그 파일

로그 파일을 이용하면 MySQL 서버에 대한 내부 지식이 없어도 MySQL의 상태나 부하의 원인을 쉽게 찾을 수 있고 해결할 수도 있다.

 

에러 로그 파일

에러 로그 파일은 MySQL 설정 파일(my.cnf)에 log_error 파라미터 경로에 생성되며, MySQL이 실행되는 도중 발생하는 에러나 경고 메시지가 출력되는 로그 파일이다. 여러 메시지가 출력되지만 우리가 자주 보게 될 메시지는 다음과 같다.

  • MySQL 시작 과정과 관련된 정보성 메시지
    • MySQL 서버 설정 파일 변경이나 비정상 종료 후 다시 시작하는 경우 출력되는 메시지
  • 비정상적으로 종료된 경우 나타나는 InnoDB 트랜잭션 복구 메시지
    • 비정상 종료 시 처리되지 못한 트랜잭션 데이터를 재처리 작업 시 출력되는 메시지
  • 쿼리 처리 도중 발생하는 문제에 대한 메시지
    • 쿼리 도중 발생한 문제를 출력하는 메시지
  • 비정상 종료된 커넥션 메시지
    • 정상적으로 접속 종료를 하지 못하고 프로그램이 종료된 경우 출력되는 메시지
  • MySQL 종료 메시지
    • MySQL이 마지막으로 왜 종료됐는지 확인할 수 있는 메시지

 

제네럴 쿼리 로그 파일

제네럴 쿼리 로그 파일은 MySQL 서버에서 실행되는 쿼리 전체 목록을 뽑아서 검토할 때 사용할 수 있다. 쿼리 요청을 받으면 바로 기록하기 때문에 쿼리 실행 중 에러가 발생해도 일단 로그 파일에 기록되는 특징을 가지고 있다.

// 제네럴 쿼리 로그 파일 경로 확인
SHOW GLOBAL VARIABLES LIKE 'general_log_file';

 

슬로우 쿼리 로그

long_query_time 시스템 변수에 설정한 시간 이상이 소요된 쿼리는 슬로우 쿼리 로그에 모두 기록되기 때문에 서비스에서 사용되는 쿼리 중 어떤 쿼리가 문제인지를 파악하는데 슬로우 쿼리 로그가 상당히 많은 도움이 된다.

InnoDB의 경우 MySQL 엔진 레벨의 잠금과 스트로지 엔진 자체 잠금을 가지고 있어 슬로우 쿼리 로그에 출력되는 내용이 조금 혼란스러울 수 있다. 슬로우 쿼리 로그 예시를 살펴보며 간단히 로그 보는 법을 알아보자.

# Time:2020-07-19T15:44:22.178484+09:00
# User@Host:root[root] @ localhost [] Id: 14
# Query_time:1.180245 Lock_time:0.002658 Rows_sent:1 Rows_examined:2844047 
use employees;
SET timestamp=1595141060;
select emp_no, max(salary) from salaries;
  • Time
    • 쿼리가 종료된 시점을 의미
    • 시작 시간을 확인하려면 Time 시간에서 Query_time 시간을 빼야 한다.
  • Query_time
    • 쿼리 실행에 걸린 전체 시간
  • Lock_time
    • MySQL 엔진 레벨에서 관장하는 테이블 잠금에 대한 대기 시간
    • 위 예제에서는 0.002658초간 테이블 락을 기다렸다는 의미
  • Rows_sent
    • 실제 몇 건의 처리 결과를 클라이언트로 보냈는지를 나타내는 수치
  • Rows_examined
    • 쿼리가 처리되기 위해 몇 건의 레코드에 접근했는지를 의미
반응형
저작자표시