책너두 (Real MySQL 8.0 1권) 11일차 (~141p)

요약

• 리두 로그 의 사용 목적을 이해하게됨.
• 로그 버퍼를 이용해 리두 로그 버퍼링을 하는 방법을 이해하게 됨.
• 리두 로그 아카이빙을 통해 리두 로그들을 관리하는 방법들을 이해하게 됨.
• 리두로그의 활성, 비활성화 상태와 서버의 비정상 종료와의 관계와 어떤 식으로 데이터 파일을 관리할지를 이해하게 됨.
• 어댑티브 해시 인덱스의 의미와 장단점을 이해하게됨.
  • 책에 설 말하는 28% 정도의 어댑티브 해시 인덱스 이용했다는 근거를 잘 모르겠음..
    • CPU 사용률이 100% 에 근접하면 어댑티브 해시 인덱스가 효율적이라고 하는데 효율적이지 않은 것 아닌가..? 라고 생각함..

메모

리두 로그 및 로그 버퍼

• 리두 로그(Redo Log) 는 트랜잭션의 4가지 요소인 ACID 중 D(Durable) 에 해당하는 영속성과 가장 밀접하게 연관돼있음.
• 리두 로그는 하드웨어나 소프트웨어 등 여러 문제점으로 인해 MySQL 서버가 비정상 종료됐을 때, 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치임.
• MySQL 서버를 포함한 대부분의 데이터베이스 서버는 데이터 변경 내용을 로그로 먼저 기록한다.
  • 일부 DBMS 에서는 리두 로그를 WAL 로그 라고도 함. (Write Ahead Log : 데이터를 디스크에 기록하기 전에 먼저 기록되는 로그)
• 거의 모든 DBMS 는 데이터 파일 쓰기 보다 읽기 성능을 고려한 자료구조를 가지고 있음.
  • 데이터 파일 쓰기할 때는 디스크의 랜덤 액세스가 필요함. → 따라서 변경된 데이터를 데이터 파일에 기록하려면 상대적으로 큰 비용이 필요함.
    • 이러한 성능 저하를 막기 위해 데이터베이스 서버에서 쓰기 비용이 낮은 자료 구조를 가진 리두 로그를 가지고 있음.
    • 비정상 종료가 발생하면 리두 로그의 내용을 가지고 데이터 파일을 다시 서버가 종료되기 직전의 상태로 복구함.
• 데이터베이스 서버는 ACID 도 중요하지만 성능도 중요하므로 데이터 파일 뿐만 아니라 리두 로그를 버퍼링할 수 있는 InnoDB 버퍼 풀이나 로그 버퍼와 같은 자료구조도 가지고 있음.
• MySQL 서버가 비정상 종료될 때, InnoDB 스토리지 엔진의 데이터 파일은 다음 2가지 종류의 일관되지 않은 데이터를 가질 수 있게 됨.
  • 커밋됐지만 데이터 파일에 기록되지 않은 데이터
    • 이 경우는 리두 로그에 저장된 데이터를 데이터 파일에 다시 복사하기만 하면 됨.
  • 롤백됐지만 데이터파일에 이미 기록된 데이터
    • 이 경우는 리두 로그로 해결할 수 없음.
      • 이때, 언두 로그의 내용을 가져와 데이터 파일에 복사하면 됨.
        • 그렇다고 리두 로그가 아예 필요 없는건 아님.
          • 최소한 그 변경이 커밋됐는지, 롤백됐는지, 아니면 트랜잭션이 실행 중간 상태였는지 확인하기 위해 리두 로그가 필요함.
• 데이터베이스 서버에서 리두 로그는 트랜잭션이 커밋되면 즉시 디스크로 기록되도록 시스템 변수를 설정하는 것을 권장함.
  • 그래야만 서버가 비정상 종료됐을 때, 비정상 종료 직전까지의 트랜잭션 커밋 내용이 리두 로그에 기록될 수 있고, 이 리두 로그를 이용해 장애 직전 시점까지의 복구가 가능해짐.
  • 대신, 이처럼 트랜잭션 커밋될 때마다 리두 로그를 디스크에 기록하는 작업은 맣은 부하를 유발함.
    • InnoDB 스토리지 엔진에서 리두 로그를 어느 주기로 디스크에 동기화할지 결정하는 innodb_flush_log_at_trx_commit 시스템 변수를 제공함.
    • 이 시스템 변수는 다음과 같은 값을 가질 수 있다.
      • 0 : 1초에 한 번씩 리두 로그를 디스크로 기록하고 동기화를 실행함.
        • 서버가 비정상 종료되면 최대 1초 동안의 트랜잭션의 경우에는 커밋됐다고 해도 해당 트랜잭션에서 변경한 데이터가 사라질 수 있음.
      • 1 : 매번 트랜잭션이 커밋될떄마다 디스크로 기록하고 동기화를 수행함. 그래서 트랜잭션이 커밋되면 바로 기록 & 동기화 되므로 해당 트랜잭션의 변경 데이터는 바로 사라진다. (나름 해석해보았는데 맞는지 모르겠음..)
      • 2 : 매번 트랜잭션이 커밋될 때마다 디스크로 기록(write) 되지만 실질적인 동기화(sync)는 1초에 한번씩 실행됨.
        • 일단 트랜잭션이 커밋되면 변경 내용이 운영체제의 메모리 버퍼로 기록되는 것이 보장됨.
        • 그래서 MySQL 서버가 비정상 종료됐어도 운영체제가 정상동작한다면 해당 트랜잭션의 데이터는 사라지지 않음.
        • 만약 운영체제도 비정상 종료된다면 최근 1초 동안의 트랜잭션 데이터는 사라질 수 있음.
    • innodb_flush_log_at_trx_commit 값이 0 혹은 2 로 설정한 경우, 디스크 동기화 작업이 항상 1초 간격으로 실행 되는 건 아님.
      • 스키마 변경을 위한 DDL 이 실행되면 리두 로그가 디스크로 동기화 되기 때문에 InnoDB 스토리지 엔진이 스키마 변경을 위한 DDL 을 실행한다면 1초 보다 간격이 짧을 수도 있음.
        • 하지만 스키마 변경 작업은 자주 실행되는 작업이 아니므로 리두 로그는 최대 1초 정도 손실이 발생할 수 있다는 점을 기억하자.
    • innodb_flush_log_at_trx_commit 값이 0 혹은 2 로 설정한 경우, 디스크 동기화 시간 간격을 innodb_flush_log_at_timeout 변수를 통해 변경할 수 있음.
      • 기본 값은 1초 이고, 일반적인 서비스에서 이 간격을 변경할 만한 특별한 이유는 딱히 없을 것으로 보임
• InnoDB 스토리지 엔진의 리두 로그 파일들의 전체 크기는 InnoDB 스토리지 엔진이 가지고 있는 버퍼 풀의 효율성을 결정하기 떄문에 신중히 결정해야 함.
  • 리두 로그 파일의 크기는 innodb_log_file_size 시스템 변수로 결정함.
  • innodb_log_files_in_group 시스템 변수는 리두 로그 파일의 개수를 결정함.
    • 전체 리두 로그 파일의 크기는 두 시스템 변수의 곱으로 결정됨.
  • 리두 로그 파일의 전체 크기가 InnoDB 버퍼 풀의 크기에 맞게 적절히 선택돼야 InnoDB 스토리지 엔진이 적절히 변경된 내용을 버퍼 풀에 모았다가 한 번에 모아서 디스크에 기록할 수 있음.
  • 사용량 (특히 변경 작업)이 매우 많은 DBMS 의 경우, 리두 로그 기록 작업이 큰 문제가 됨.
    • 이 부분을 보완하기 위해 최대한 ACID 속성을 보장하는 수준에서 버퍼링 한다.
    • 이러한 리두 로그 버퍼링에 사용되는 공간이 로그 버퍼임.
      • 로그 버퍼 크기는 기본 값인 16MB 수준에서 설정하는게 적합함.
      • BLOB, TEXT 와 같은 큰 데이터를 자주 변경하는 경우에는 더 크게 설정하는게 좋음.

참고 : ACID 는 데이터베이스에서 트랜잭션의 무결성을 보장하기 위해 꼭 필요한 4가지 요소(기능)을 의미함.

- 'A' : Atomic = 트랜잭션은 원자성 작업이어야 함
- 'C' : Consistent = 일관성
- 'I' : Isolated = 격리성
- 'D' : Durable = 한 번 저장된 데이터는 지속적으로 유지돼야 함.

일관성과 격리성은 쉽게 정의하기 힘듦. 
이 2가지 속성이 의미하는 바는 
서로 다른 2 개의 트랜잭션에서 동일 데이터를 조회 하고 변경하는 경우에도 
상호 간섭이 없어야 한다는 것이다.

리두 로그 아카이빙

• 8.0 버전부터 InnoDB 스토리지 엔진은 리두 로그를 아카이빙할 수 있는 기능이 추가됨.
  • MySQL 엔터프라이즈 백업이나 Xtrabackup 툴은 데이터 파일을 복사하는 동안 InnoDB 스토리지 엔진의 리두 로그에 쌓인 내용을 계속 추적하면서새로 추가된 리두 로그 엔트리를 복사함.
    • 데이터 파일을 복사하는 동안 추가된 리두 로그 엔트리가 같이 백업되지 않으면 복사된 데이터 백업 파일은 일관된 상태를 유지하지 못함.
    • MySQL 서버에 유입되는 데이터 변경이 너무 많으면 리두 로그가 매우 빠르게 증가하고, 엔터프라이즈 백업 혹은 XtraBackup 툴이 새로 추가되는 리두 로그 내용을 복사하기도 전에 덮어 쓰일 수 있음.
      • 이렇게 아직 복사하지 못한 리두 로그가 덮어쓰이면 백업 툴이 리두 로그 엔트리를 복사할 수 없어서 백업이 실패함.
    • 8.0 의 리두 로그 아카이빙 기능은 데이터 변경이 많아 리두 로그가 덮어 쓰인다 하더라도 백업이 실패하지 않게 해줌.
• 백업 툴이 리두 로그 아카이빙을 사용하려면 MySQL 서버에서 아카이빙된 리두 로그가 저장될 디렉터리를 innodb_redo_log_archive_dirs 시스템 변수에 설정해야 함.
  • 이 디렉터리는 운영체제의 MySQL 서버를 실행하는 유저(일반적인 mysql 유저)만 접근이 가능함.

linux> mkdir /var/log/mysql_redo_archive
linux> cd /var/log/mysql_redo_archive
linux> mkdir 20200722
linux> chmod 700 20200722

mysql> SET GLOBAL innodb_redo_log_archive_dirs='backup:/var/log/mysql_redo_archive';

• 위와 같이 디렉터리를 준비하면 다음과 같이 리두 로그 아카이빙을 시작하도록 innodb_redo_log_archive_start UDF (사용자 정의 함수 : User Defined Function) 을 실행하면 된다.

mysql> DO innodb_redo_log_archive_start('backup','20200722');

• UDF 는 1개 ~ 2개의 파라미터를 입력할 수 있음.
  • 첫 번째 파라미터는 리두 로그를 아카이빙할 디렉터리에 대한 레이블임
  • 두 번째 파라미터는 서브디렉터리 이름임.
    • 얘는 입력하지 않아도 됨.
      • 이때는 innodb_redo_log_archive_dirs 시스템 변수의 레이블에 해당하는 디렉터리에 별도 서브디렉터리 없이 리두 로그를 복사하게 됨.
• 이제 특정 테이블에 데이터 변경 명령을 실행하고 위에서 설정한 로그의 디렉터리에 파일이 생겼는지 확인할 수 있다.
  • InnoDB 리두 로그 아카이빙은 로그 파일이 로테이션될 때 복사되는게 아님.
    • 리두 로그 파일에 로그 엔트리가 추가될 때 함께 기록되는 방식을 사용하고 있어서 데이터 변경이 발생하면 즉시 아카이빙된 로그 파일의 크기가 조금씩 늘어나는걸 확인할 수 있음.

mysql> DO inodb_redo_log_archive_stop();

• 리두 로그 아카이빙을 종료할 때는 innodb_redo_log_archive_stop UDF 를 실행하면 됨.
  • InnoDB 스토리지 엔진은 리두 로그 아카이빙을 멈추고 아카이빙 파일도 종료한다.
  • 아카이빙 파일은 삭제하지 않기 때문에 사용이 완료되면 사용자가 수동으로 삭제해해야 함.
• innodb_redo_log_archive_start UDF 를 실행한 세션의 연결이 끊어지지 않아야 리두 로그 아카이빙이 계속 실행된다.
  • 만약 리두 로그 아카이빙을 시작한 세션이 innodb_redo_log_archive_start UDF 실행 하기 전에 연결이 끊어지면 InnoDB 스토리지 엔진은 리두 로그 아카이빙을 멈추고 아카이빙 파일도 자동으로 삭제해 버린다.
    • 아카이빙을 시작한 세션이 비정상적으로 종료되면 아카이빙된 리두 로그도 쓸모 없기 때문
  • 아카이빙된 리두 로그를 정상적으로 사용하려면 커넥션을 그대로 유지해야함.
  • 작업이 완료되면 반드시 innodb_redo_log_archive_stop UDF 를 호출해서 아카이빙을 정상 종료해야 함.

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

• 하드웨어 혹은 소프트웨어 등의 여러 문제로 MySQL 서버가 비정상 종료될 때 데이터 파일에 기록되지 못한 트랜잭션을 복구하기 위해 InnoDB 엔진의 리두 로그는 항상 활성화 되어 있음.
• MySQL 서버 트랜잭션이 커밋돼도 데이터 파일은 즉시 디스크로 동기화 되지 않음.
• 리두 로그(트랜잭션 로그)는 항상 디크스로 기록됨.
  • 물론 innodb_flush_log_at_trx_commit 시스템 설정에 따라 디스크로 기록되는 타이밍이 다름.
• 8.0 이전 버전까지는 수동으로 리두 로그 비활성화 할 수 없었음.
• 8.0 부터 수동으로 리두 로그 활성화 & 비활성화 가능해짐
• 8.0 부터는 데이터를 복구하거나 대용량 데이터를 한번에 적재할 경우 다음과 같이 리두 로그를 비활성화 해서 데이터의 적재 시간을 단축시킬 수 있음.

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

// 리두 로그 비활성화 한 후 대량 데이터 적재 실행
mysql> LOAD DATA ...

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

• Innodb_redo_log_enabled 시스템 변수로 리두 로그가 활성화 됐는지 확인 가능

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';

• 리두 로그를 비활성화 하고 데이터 적재 작업을 실행했다면 데이터 적재 완료 후 리두 로그를 다시 활성화 하는 것을 잊지말자
  • 리두 로그가 비활성화 상태에서 MySQL 서버가 비정상 종료되면 MySQL 서버의 마지막 체크 포인트 이후 시점의 데이터는 모두 복구할 수 없게 됨.
• 만약 MySQL 서버가 비정상 종료되어 데이터가 일부 손실돼도 괜찮다면 리두 로그를 비활성화 하는 것 보단 innodb_flush_log_at_trx_commit 변수를 0 또는 2로 설정해서 사용할 것을 권장함.

MySQL 서버는 항상 새롭게 시작될 때 자신이 가진 리두 로그에서 데이터 파일에 기록되지 못한 데이터가 있는지 검사한다. 만약, 리두 로그가 비활성화된 상태에서 MySQL 서버가 비정상 종료되면 리두 로그를 이용한 복구가 불가능하므로 MySQL 서버가 정상적으로 실행되지 못할 수 있음. 이때 innodb_force_recovery 시스템 변수를 6으로 설정 후 다시 시작해야 함.

어댑티브 해시 인덱스

• 일반적으로 ‘인덱스’ 라고 하면 테이블에 사용자가 생성해둔 B-Tree 인덱스를 의미함.
  • 인덱스가 사용하는 알고리즘이 B-Tree 가 아니더라도 사용자가 직접 테이블에 생성해둔 인덱스가 우리가 일반적으로 아는 인덱스임.
• 어댑티브 해시 인덱스는 사용자가 수동으로 생성하는 인덱스가 아니라 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스임.
• innodb_adaptive_hash_index 시스템 변수로 어댑티브 해시 인덱스 기능을 활성화 하거나 비활성화 할 수 있다.
• B-Tree 인덱스에서 특정 값을 찾는 과정이 매우 빠르게 처리된다고 알고는 있지만, 빠르냐 느리냐의 기준은 결국 상대적임
  • 데이터베이스 서버가 얼마나 많은 일을 하느냐에 따라 B-Tree 인덱스에서 값을 찾는 과정이 느릴 수도 있고 빠를 수도 있음.
  • B-Tree 인덱스에서 특정 값을 찾기 위해서 루트 노드 → 브랜치 노드 → 리프 노드까지 찾아가야 원하는 레코드를 읽을 수 있음.
    • 적당한 사양의 컴퓨터에서 이런 작업을 동시에 몇개 실행해도 성능 저하가 보이지 않음.
    • 이 작업이 동시에 몇천 개의 스레드로 실행하면 컴퓨터의 CPU 는 엄청난 프로세스 스케줄링을 하고 자연히 쿼리 성능은 떨어짐.
    • 어댑티브 해시 인덱스는 이러한 B-Tree 검색 시간을 줄여주기 위해 도입된 기능임.
      • InnoDB 스토리지 엔진은 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 어댑티브 해시 인덱스를 검색해서 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있음.
      • B-Tree 를 루트 노드부터 리프 노드 까지 찾아가는 비용이 없어지고 그만큼 CPU는 적은 일을 하면서 쿼리 성능은 빨라짐
      • 그리고 더 많은 쿼리를 동시 처리할 수 있게 됨.
      • 해시 인덱스는 ‘인덱스 키 값’ 과 해당 인덱스 키 값이 저장된 ‘데이터 페이지 주소’ 의 쌍으로 관리됨.
        • 인덱스 키 값은 ‘B-Tree 인덱스의 고유번호(Id) 와 B-Tree 인덱스의 실제 키 값’ 조합으로 생성됨.
          • 어댑티브 해시 인덱스 키 값에 B-Tree 인덱스의 고유 번호가 포함된 이유는 InnoDB 스토리지 엔진에서 어댑티브 해시 인덱스는 하나만 존재(물론 파티션되는 기능도 있음)하기 때문임.
          • 모든 B-Tree 인덱스에 대한 어댑티브 해시 인덱스가 하나의 해시 인덱스에 저장되며, 특정 키 값이 어느 인덱스에 속한 것인지도 구분해야 하기 때문임.
        • ‘데이터 페이지 주소’ 는 실제 키 값이 저장된 데이터 페이지의 메모리 주소를 가짐.
          • 이는 InnoDB 버퍼 풀에 로딩된 페이지의 주소를 의미함.
          • 그래서 어댑티브 해시 인덱스는 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리함.
            • 버퍼 풀에서 해당 데이터 페이지가 없어지면 어댑티브 해시 인덱스에서도 해당 페이지의 정보는 사라짐.
• 단순한 쿼리를 MySQL 서버가 최대한 처리할 수 있는 수준까지 실행하는 상태에서 어댑티브 해시 인덱스를 활성화 했을때 CPU 사용률과 초당 쿼리 처리 수 변화를 살펴보자.
  • ex) 초당 20000개 정도 쿼리를 처리할 때 CPU 사용률이 100% 였음.
    • 어댑티브 해시 인덱스를 활성화 한 후 쿼리 처리량은 2배 가까이 늘어남.
    • 그리고 CPU 사용률도 떨어짐.
    • B-Tree 루트 노드 부터 검색이이 줄면서 InnoDB 내부 잠금(세마포어) 의 횟수도 획기적으로 줄어 듬.
• 이전 버전까지 어댑티브 해시 인덱스는 하나의 메모리 객체였음.
  • 그래서 어댑티브 해시 인덱스의 경합(Contention)이 상당히 심했음.
  • 8.0 부터 내부 잠금(세마포어) 경합을 줄이기 위해 어댑티브 해시 인덱스의 파티션 기능을 제공한다.
    • innodb_adaptive_hash_index_parts 시스템 변수를 이용하여 파티션 개수를 변경할 수 있음.
      • 기본 값은 8 임. 만약 어댑티브 해시 인덱스가 성능에 많은 도움이 되면 파티션 개수를 더 많이 설정할 수록 인덱스 내부 잠금 경합을 줄이는데 많은 도움을 줌.
• 지금까지 어댑티브 해시 인덱스의 장점만 있는 것처럼 보이지만, 실제로 어댑티브 해시 인덱스를 의도적으로 비활성화 하는 경우도 많음.
• 어댑티브 해시 인덱스가 성능 향상에 크게 도움이 되지 않는 경우는 다음과 같다.
  • 디스크 읽기가 많은 경우
  • 특정 패턴의 쿼리가 많은 경우(조인 or LIKE 패턴 검색)
  • 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
• 다음과 같은 경우 성능 향상에 많은 도움이 됨.
  • 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷 한 경우 (디스크 읽기가 많지 않은 경우)
  • 동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우
  • 쿼리가 데이터 중에 일부 데이터에만 집중되는 경우
• 어댑티브 해시 인덱스는 데이터 페이지를 메모리 (버퍼 풀) 내에서 접근하는 것을 더 빠르게 만드는 기능임.
  • 때문에 데이터 페이지를 디스크에서 읽어오는 경우가 빈번한 데이터 베이스 서버에서는 아무런 도움이 되지 않음.
• 어댑티브 해시 인덱스는 저장 공간인 메모리를 사용하기에 떄로는 상당히 큰 메모리 공간을 사용할 수 도 있음.
• 어댑티브 해시 인덱스 또한 데이터 페이지의 인덱스 키가 해시 인덱스로 만들어져야 하고 불필요한 경우 제거돼야 함.
• 어댑티브 해시 인덱스가 활성화 되면 InnoDB 스토리지 엔진은 그 키 값이 해시 인덱스에 있든 없든 검색해봐야 함.
  • 즉, 해시 인덱스의 효율이 없는 경우에도 InnoDB는 계쏙 해시 인덱스를 사용할 것임.
• 어댑티브 해시 인덱스는 테이블 삭제 작업에도 많은 영향을 미침
  • 어떤 테이블의 인덱스가 어댑티브 해시 인덱스에 적재돼 있다고 가정하자.
    • 이때, 이 테이블을 삭제(DROP) 하거나 변경(ALTER) 하려고 하면 InnoDB 스토리지 엔진은 이 테이블이 가진 모든 데이터 페이지의 내용을 어댑티브 해시 인덱스에서 제거해야 함.
      • 이로 인해 테이블이 삭제되거나 스키마가 변경되는 동안 상당히 많은 CPU 자원을 사용하고 그만큼 데이터베이스 서버의 처리 성능이 느려짐.
      • 어댑티브 해시 인덱스의 도움을 많이 받을 수록 데이블 삭제 또는 변경 작업(Online DDL 포함)은 치명적인 작업이 되게 됨.
• 어댑티브 해시 인덱스가 우리 서비스 패턴에 맞게 도움이 되는지, 아니면 불필요한 오버헤드만 만들고 있는지 판단해야 함.
  • 정확한 판단할 수 있는 가장 쉬운 방법은 MySQL 서버 상태 값들을 살펴보는 것임.
    • MySQL 서버에서 어댑티브 해시 인덱스는 기본적으로 활성화 돼있음.
      • 특별히 서버 설정을 변경하지 않았다면 이미 어댑티브 해시 인덱스를 사용 중인 상태임.
      • MySQL 상태 값 중 ‘hash searches/s’ 값으로 어댑티브 해시 인덱스의 사용률을 확인해볼 수 있음.
      • 어댑티브 해시 인덱스의 메모리 사용량은 performance_schema 를 시용해서 확인할 수 있다.

      mysql> 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';