책너두 (Real MySQL 8.0 1권) 10일차 (~129p)

요약

• Double Write Buffer 사용 목적과 그 방법을 이해하게 됨.
• 언두 로그의 사용 이유와 모니터링 방법, 테이블 스페이스에 대한 이력과 관리 방법을 이해하게 됨.
• 체인지 버퍼의 사용 목적과 관리 방법을 이해하게됨.

발췌

• 대용량의 데이터를 처리하는 트랜잭션뿐만 아니라 트랜잭션이 오랜 시간 동안 실행될 때도 언두 로그의 양은 급격히 증가할 수 있다.

메모

Double Write Buffer

• InnoDB 스토리지 엔진의 리두 로그는 공간 낭비를 방지하기 위해 페이지의 변경된 내용만 기록함.
  • 더티 페이지를 디스크 파일로 플러시할 때 일부만 기록되는 문제가 발생하면 그 페이지 내용을 복구하지 못할 수 있음.
    • 페이지가 일부만 기록되는 현상을 파셜 페이지(Partial page) 또는 톤 페이지(Torn page) 라고 함.
    • 이 현상은 하드웨어 오작동이나 시스템 비정상 종료등으로 발생할 수 있음.
  • InnoDB 스토리지 엔진에서 이 문제를 막기 위해 Double-Write 기법을 사용함.
   

• A~E 더티 페이지를 디스크로 플러시하는 상황임.
• 스토리지 엔진에서 실데 데이터 파일에 변경 내용을 기록하기 전이 A~E 까지의 더티페이지를 묶어서 한 번의 디스크 쓰기로 시스템 테이블스페이스의 DoubleWrite 버퍼에 기록한다.
• InnoDB 스토리지 엔진에서 각 더티 페이지를 파일의 적당한 위치에 하나씩 랜덤 쓰기를 실행함.
• 실제 데이터 파일에 A~E 더티 페이지가 정상 기록되면 더이상 DoubleWrite 버퍼 공간에 기록된 내용은 필요 없어짐.
• 만약 A~B 까지 정상 기록됐지만, C 기록되는 도중, 운영체제 비정상 종료된 상황이라고 하자.
  • InnoDB 스토리지 엔진이 재시작될 때 항상 DoubleWrite 버퍼의 내용과 데이터 파일의 페이지들을 모두 비교해서 다른 내용을 담고 있는 페이지가 있는지 확인한다.
  • 다른 내용이 있다면 DoubleWrite 버퍼의 내용을 데이터 파일의 페이지로 복사한다.
  • innodb_doublewrite 시스템 변수를 켜서 DoubleWrite 기능을 사용할 수 있다.

• DoubleWrite 버퍼는 데이터 안정성을 위해 자주 사용됨.
  • HDD 처럼 자기 원판(platter)이 회전하는 저장 시스템에서는 한 번의 순차 디스크 쓰기 하는 것은 별로 부담되지 않음.
  • SSD 처럼 랜덤 IO 나 순차 IO 비용이 비슷한 저장 시스템에서는 상당히 부담스러움.
    • 그래도 데이터 무결성이 매우 중요한 서비스에서는 DoubleWrite 활성화를 고려하는게 좋음.
    • 만약 데이터베이스 성능을 위해 InnoDB 리두 로그 동기화 설정 (innodb_flush_log_at_trx_commmit)을 1이 아닌 값을로 설정했다면 DoubleWrite 도 비활성화는게 좋음.

일반적으로 MySQL 서버는 복제를 이용해 동일 데이터의 사본을 여러개 유지함. 그래서 MySQL 서버 비정상 종료되면 백업과 바이너리 로그를 이용해 다시 동기화 하는 경우가 많음. 즉, DoubleWrite 뿐만 아니라 리두 로그와 복제를 위한 바이너리 로그 등 트랜잭션 COMMIT 시점에 동기화 할것들이 많다. 즉, 리두 로그는 동기화 하지않으면서 DoubleWrite 만 활성화하는 것은 잘못된 선택임.

언두 로그

• InnoDb 스토리지 엔진은 트랜잭션과 격리 수준 보장을 위해 DML 로 변경되기 이전 버전의 데이터를 별도로 백업함.
  • 백업된 데이터를 언두 로그(Undo Log) 라고 함.
• 언두 로그 목적
  • 트랜잭션 보장
    • 트랜잭션 롤백 시 변경된 데이터를 변경 전 데이터로 복구할 때, 언두 로그에 백업해둔 이전 버전의 데이터를 이용해 복구함
  • 격리 수준 보장
    • 특정 커넥션에서 데이터 변경 중, 다른 커넥션이 데이터를 조회하면 트랜잭션 격리수준에 맞게 변경 중인 레코드를 읽지 않고 언두 로그에 백업해둔 데이터를 읽어서 반환하기도 함.
• 언두 로그는 InnoDB 스토리지 엔진에서 배우 중요한 역할을 담당함.
  • 그만큼 관리 비용도 많이 필요함.

언두 로그 레코드 모니터링

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

mysql> UPDATE member SET name='홍길동' WHERE member_id=1;

• 위 문장이 실행되면 트랜잭션 커밋하지 않아도 실제 데이터 파일 (데이터/인덱스 버퍼) 내용은 ‘홍길동’ 으로 변경됨.
  • 그리고 이전 name 이 ‘벽계수’ 였다면 언두 영역에는 ‘벽계수’ 라는 값이 백업되게 됨.
  • 롤백되면, 언두 영역의 백업된 데이터를 다시 데이터 파일로 복구함.
• 언두 로그의 데이터는 크게 2가지 용도로 사용됨.
  • 트랜잭션의 롤백 대비용
  • 트랜잭션 격리 수준을 유지하면서 높은 동시성 제공
• 5.5 이전 버전에서는 MySLQ 서버에서 한 번 증가한 언두 로그 공간을 다시 줄여주지 않았음.
  • ex) 1억 건의 레코드가 저장된 100GB 크기의 테이블을 DELETE 로 삭제하는 상황
    • MySQL 서버는 이 테이블에서 레코드 한 건 삭제하고 언두 로그에서 삭제되기 전 값을 저장함
    • 이렇게 1억건의 레코드가 테이블에서 삭제되고 언두 로그로 복사됨.
    • 즉, 테이블 크기만큼 언두 로그의 공간 사용량이 늘어남 (100GB)
  • 대용량 데이터 처리하는 트랜잭션 뿐만 아니라 트랜잭션이 오래 실행될 때도 언두로그 양이 급격히 증가할 수 있음.
    • 먼저 시작된 하나의 트랜잭션이 커밋되지 않았다면 다른 트랜잭션이 뒤늦게 시작했고 더빨리 커밋되도 해당 언두로그는 삭제되지 않음. (이미 시작된 하나의 트랜잭션이 아직 활성상태이기 때문)
    • 응용 프로그램에서 트랜잭션 관리를 잘못해서 이런 현상이 생길 수도 있지만 사용자의 실수로 인해 더 자주 문제가 되기도 함.
      • 서비스용 MySQL 서버에서 사용자가 트랜잭션을 시작하고 완료하지 않은채 하루 정도 방치했다면, 트랜잭션이 시작된 시점부터 생성된 언두 로그가 계속 쌓이게 됨.
      • 결국 InnoDB 스토리지 엔진은 하루치 데이터 변경을 모두 저장하고, 디스크의 언두 로그 저장공간은 계속 증가함.
      • 언두 로그의 증가로 인해 변경된 레코드를 조회하는 쿼리가 실행되면 InnoDB 스토리지 엔진은 언두 로그의 이력을 필요한 만큼 스캔해야만 레코드를 찾을 수 있다.
        • 언두 로그가 많아졌기 떄문에 그만큼 찾는 시간도 길어지고, 쿼리 성능이 전반적으로 떨어짐.
    • 위와 같은 이유로 5.5 버전까지는 언두로그 사용 공간이 한 번늘어나면 MySQL 서버를 새로 구축하지 않는 한 줄일 수 없었음.
    • 또, 언두 로그 늘어나면서 디스크 사용량 뿐만 아니라 매번 백업할 떄도 그만큼 더 복사해야 하는 문제도 발생함.
• 5.7과 8.0 부터는 언두 로그 공간의 문제를 완전히 해결함
  • 8.0 에서는 언두 로그를 돌아가면서 순차적으로 사용해서 디스크 공간을 줄이는것이 가능함.
  • MySQL 서버가 필요한 시점에 사용 공간을 자동으로 줄여 주기도함.
  • 그래도, MySQL 서버에서 활성 상태인 트랜잭션이 장시간 유지 되는 것은 성능상 좋지 않음.
  • 그래서 MySQL 서버의 언두 로그 레코드가 얼마나 있는지 항상 모니터링 하는 것이 좋음.

  // MySQL 서버의 모든 버전에서 사용 가능한 명령
  SHOW ENGINE INNODB STATUS \G
  
  // MySQL 8.0 버전에서 사용 가능한 명령
  mysql> SELECT count
               FROM information_schema.innodb_metrics
         WHERE SUBSYSTEM='transaction' AND NAME='trx_rseg_history_len';

  • 위 명령으로 MySQL 서버의 언두 레코드 건수를 확인할 수 있음.
  • MySQL 서버에서 실행되는 INSERT, UPDATE, DELETE 문장이 얼마나 많은 데이터를 변경하냐에 따라 언두 로그에 존재하는 레코드 건수가 다를 수 있음.
    • 따라서 MySQL 서버별로 이 값의 차이가 있을 수 있음.
    • 서버 별로 안정된 시점의 언두로그 레코드 건수를 확인해서 이 값을 기준으로 언두 로그의 급증 여부를 모니터링하는게 좋음.

언두 테이블스페이스 관리

• 언두 로그가 저장되는 공간을 언두 테이블스페이스(Undo Tablespace) 라고 함.
  • 버전별로 많은 변화가 있었음.
  • 5.6 이전버전에는 언두 로그가 모두 시스템 테이블스페이스(ibdata.ibd)에 저장됨.
    • 시스템 테이블 스페이스의 언두로그는 MySQL 서버가 초기화될 때 생성되기 때문에 확장의 한계가 있음.
  • 5.6 버전에서는 innodb_undo_tablespaces 시스템 변수가 도입됨.
    • 해당 값이 2보다 큰 값을 설정하면 InnoDB 스토리지 엔진은 더이상 시스템 테이블스페이스에 저장하지 않고 별도의 언두 로그 파일을 사용함.
    • 하지만 이 값이 0으로 설정됐다면 여전히 시스템 테이블 스페이스에 저장됨.
  • 8.0 부터 innodb_undo_tablespaces 시스템 변수는 효력이 없어짐. (deprecated)
    • 항상 시스템 테이블 스페이스 외부의 별도 로그파일에 기록되도록 개선됨.

• 언두 테이블 스페이스의 구성이다.
• 하나의 언두 테이블스페이스는 1~128개의 롤백 세그먼트를 가짐.
  • 롤백 세그먼트는 1개 이상의 언두 슬롯(Undo Slot) 을 가짐.
  • 하나의 롤백 세그먼트는 InnoDB 페이지 크기를 16 바이트로 나눈 값의 개수만큼의 언두 슬롯을 가짐.
    • ex) InnoDB 페이지 크기가 16KB 라면 하나의 롤백 세그먼트는 1024개의 언두 슬롯을 갖게됨.
• 하나의 트랜잭션이 필요로 하는 언두 슬롯의 개수는 트랜잭션이 실행하는 INSERT, UPDATE, DELETE 문장의 특성에 따라 최대 4개까지의 언두 슬롯을 사용함.
  • 일반적으로 트랜잭션이 임시 테이블을 사용하지 않으므로 하나의 트랜잭션은 대략 2개 정도의 언두 슬롯을 필요로 한다고 가정하면 최대 동시 처리 가능한 트랜잭션 개수는 다음 수식으로 예측 해볼 수 있음.

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

  • 일반적인 설정인 16KB InnoDB 에서 기본 설정 (innodb_undo_tablespaces=2, ‘innodb_rollback_segments=128) 이라면, 대략 131072 (= 16 * 1204 / 16 * 128 * 2 / 2) 정도의 트랜잭션을 동시 처리할 수 있음.
    • 일반적인 서비스에서 이정도까지 동시 트랜잭션은 필요하지 않겠지만 가능하면 이 기본 값을 유지하자.
  • 언두 로그 슬롯이 부족한 경우 트랜잭션을 시작할 수 없는 심각한 문제가 발생함.
    • 언두 로그 관련 시스템 변수를 변경해야 한다면 적절히 필요한 동시 트랜잭션 개수에 맞게 언두 테이블 스페이스와 롤백 세그먼트 개수를 설정해야 함.
  • 8.0 전까지 한 번 생성된 언두 로그는 변경이 허용되지 않고 정적으로 사용됐음.
  • 8.0 부터는 CREATE UNDO TABLESPACE 나 DROP TABLESPACE 와 같은 명령으로 새로운 언두 테이블스페이스를 동적으로 추가, 삭제할 수 있게 개선됨.
• 언두 테이블 스페이스 공간을 필요한 만큼만 남기고 불필요하거나 과도하게 할당된 공간을 운영체제로 반납할 수 있음.
  • ‘Undo tablespace truncate’ 라고 함.
  • 언두 테이블 스페이스의 불필요한 공간을 잘라내는(Truncate) 방법은 자동과 수동, 2가지 방법이있음.
  • 두 방법 모두 8.0 부터 지원됨.
    • 자동 모드 : InnoDB 스토리지 엔진의 퍼지 스레드(Purge Thread) 는 주기적으로 꺠어나서 언두 로그 공간에서 불필요해진 언두 로그 삭제하는 작업을 실행함. (언두 로그가 기록되고, 트랜잭션이 커밋되면 더이상 이전 값을 가지고 있는 언두 로그는 불필요 해짐. 이를 제거하는 작업)
      • 이 작업을 언두 퍼지(Undo Purge) 라고함
      • innodb_undo_log_truncate 시스템 변수가 ON 되면 퍼지 스레드는 주기적으로 언두 로그 파일에서 사용되지 않는 공간을 잘라내고 운영체제로 반납함.
      • 언두 로그 파일을 잘라내는 작업을 더 빈번하게 또는 덜 빈번하게 실행되게 하려면 innodb_purge_rseg_truncate_frequency 시스템 변수 값을 조정하면 됨.
    • 수동 모드 : innodb_undo_log_truncate 시스템 변수가 OFF 로 설정되어 언두 로그 파일의 잘라내기가 자동으로 실행되지 않거나 예상보다 자동 모드로 언두 테이블스페이스의 공간 반납이 제대로 이루어 지지 않는 경우 언두 테이블스페이스를 비활성화 해서 언두 테이블 스페이스가 더이상 사용되지 않도록 설정한다.
      • 이때, 퍼지 스레드는 비활성 상태의 언두 테이블 스페이스를 찾아서 불필요한 공간을 잘라내고 운영체제로 해당 공간을 반납한다.
      • 반납이 완료되면 언두 테이블 스페이스를 다시 활성화 한다.
      • 수동 모드는 언두 테이블 스페이스가 최소 3개 이상 돼야 작동함.

    // 언두 테이블스페이스 비활성화
    mysql> ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;
    
    // 퍼지 스레드에 의해 언두 테이블스페이스 공간이 반납되면 다시 활성화
    mysql> ALTER UNDO TABLESPACE tablespace_name SET ACTIVE;

체인지 버퍼

• RDBMS 에서 레코드가 INSERT 되거나 UPDATE 될 때는 데이터 파일을 변경하는 작업뿐만 아니라 해당 테이블에 포함된 인덱스를 업데이트 하는 작업도 필요함.
• 그런데 인덱스 업데이트 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱스가 많으면 이 작업은 당한 자원을 소모함.
  • InnoDB 는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행함.
  • 그렇지 않고 디스크로부터 읽어와서 업데이트 해야 하면 이를 즉시 실행하지 않고 임시 공간에 저장해두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상 시킴.
  • 이때 사용하는 임시 메모리 공간을 체인지 버퍼(Change Buffer) 라고 함.
• 사용자에게 결과를 전달하기 전에 반드시 중복 여부를 체크해야하는 유니크 인덱스는 체인지 버퍼를 사용할 수 없음.
• 체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 나중에 백그라운드 스레드에 의해 병합됨.
  • 이 스레드를 체인지 버퍼 머지 스레드(Merge thread) 라고 함.
• 5.5 버전까지는 INSERT 작업에 대해서만 이러한 버퍼링이 가능함. (5.5 이전까지는 이 버퍼를 인서트 버퍼라고 불렀음)
• 5.5 부터 조금씩 개선되면서 8.0 부터는 INSERT, DELETE, UPDATE 로 킥를 추가하거나 삭제하는 작업에 대해서도 버퍼링이 되도록 개선됨.
• 5.5 이전 버전에는 별도의 시스템 변수 설정 없이 기본적으로 기능이 활성화 됐음.
• 5.5부터 innodb_change_buffering 시스템 변수가 도입됨.
  • 작업의 종류별로 체인지 버퍼를 활성화 할 수 있었음.
  • 체인지 버퍼가 비효율 적일 떄는 체인지 버퍼를 사용하지 않게 설정할 수 있게 개선함.
  • 해당 시스템 변수에 다음 값을 설정할 수 있음.
    • all : 모든 인덱스 관련 작업 (inserts, deletes, purges)을 버퍼링한다.
    • none : 버퍼링 하지 않는다.
    • inserts : 인덱스에 새로운 아이템을 추가하는 작업만 버퍼링
    • deletes : 인덱스에 기존 아이템을 삭제하는 작업(삭제됐다는 마킹 작업)만 버퍼링
    • changes : 인덱스에 추가하고 삭제하는 작업(inserts, deletes)만 버퍼링
    • purges : 인덱스 아이템을 영구적으로 삭제하는 작업만 버퍼링 (백그라운드 작업)
• 체인지 버퍼는 기본적으로 InnoDB 버퍼 풀로 설정된 메모리 공간의 25% 까지 사용할 수 있게 설정됨.
  • 필요에 따라 InnoDB 버퍼 풀의 50% 까지 사용하게 설정할 수 있음.
  • 체인지 버퍼를 버퍼 풀에 적게 사용하게 하던가, INSERT, UPDATE 가 빈번히 실행되어 체인지 버퍼가 더 많은 버퍼 풀을 사용하게 하려면 innodb_change_buffer_max_size 시스템 변수 비율을 설정하면 됨.
• 체인지 버퍼가 버퍼 풀의 메모리를 얼마나 사용 중인지, 얼마나 많은 변경 사항을 버퍼링 하고 있는지 다음과 같이 확인 가능

// 체인지 버퍼가 사용 중인 메모리 공간 크기
mysql> SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED
       FROM performance_schema.memory_summary_global_by_event_name
       WHERE EVENT_NAME='memory/innodb/ibuf0ibuf';

// 체인지 버퍼 관련 오퍼레이션 처리 횟수
mysql> SHOW ENGINE INNODB STATUS \G