[3주차] Real MySQL (ch05 ~ ch07)

05. 트랜잭션과 잠금

5.1 트랜잭션

5.1.1 MySQL에서의 트랜잭션

• MyISAM이나 MEMORY 같은 스토리지 엔진은 트랜잭션을 지원하지 않는다.

5.1.2 주의사항

• 트랜잭션의 범위는 최소화해야 한다.

• 트랜잭션 범위 동안 커넥션을 가지고 있고, 필요시 락까지 걸 수 있다.

• 트랜잭션 내에 DB 작업 외 네트워크 통신 같은 작업은 제거하는 것이 좋다. 통신이 지연된다면 DBMS 서버까지 영향을 받을 수 있다.

5.2 MySQL 엔진의 잠금

5.2.1 글로벌 락

FLUSH TABLES WITH READ LOCK

• MySQL 서버 전체에 영향을 미친다.

• 서버에 존재하는 모든 테이블을 닫고 잠금을 걸기 때문에 명령이 실행되기 전 먼저 실행된 트랜잭션이 완료될 때까지 기다려야 한다.

• mysqldump 같은 백업 프로그램은 내부적으로 이 명령을 실행할 때도 있기 때문에 옵션에 따라 어떤 잠금을 거는지 확인해봐야 한다.

• InnoDB를 기본 스토리지 엔진으로 채택하면서 트랜잭션을 지원하기 때문에 모든 데이터 변경 작업을 멈출 필요가 없어졌고 백업락이 도입되었다.
• 백업 작업 중에 데이터베이스의 구조적 변경을 방지하여 안전한 백업을 보장한다.
• 데이터베이스 구조 변경 방지
• DDL(Data Definition Language) 작업 차단
• AUTO_INCREMENT 관련 작업 제한
• 메타데이터 잠금 포함
• 일반적인 DML(Data Manipulation Language) 작업은 허용

LOCK INSTANCE FOR BACKUP

5.2.2 테이블 락

• 개별 테이블 단위로 설정되는 잠금이며 명시적 또는 묵시적으로 획득할 수 있으나 특별한 상황이 아니면 명시적인 테이블 락은 애플리케이션에서 사용할 필요가 거의 없다.

• 묵시적인 테이블락은 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하는 동안 자동으로 획득했다가 완료된 후 자동으로 해제된다.

• InnoDB의 경우, 대부분의 DML 쿼리에서는 무시되고 DDL 쿼리에만 영향을 미친다.

5.2.3 네임드 락

SELECT GET_LOCK('mylock', 2);
SELECT IS_FREE_LOCK('mylock');
SELECT RELEASE_LOCK('mylock');

• 사용자가 지정한 임의의 문자열에 대해 잠금을 설정, 해제한다.

• 데이터베이스를 이용한 네임드 락은 자주 사용되지는 않는다.
• 메모리가 아닌 테이블에 저장되며, GET_LOCK() 함수 호출 시 매번 DB 작업이 발생한다.
• 분산 락 시스템으로 대체된다.
• Redis의 SETNX 명령어
• ZooKeeper의 분산 락
• etcd의 락 메커니즘

• MySQL 8.0 버전부터 네임드 락을 중첩해서 사용하거나 현재 세션에서 획득한 네임드 락을 한 번에 모두 해제하는 기능이 추가되었다.

5.2.3 메타데이터 락

• 데이터베이스 객체(테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우 자동으로 획득하는 잠금이다.

RENAME TABLE rank TO rank_backup, rank_new TO rank;

• 위와 같이 하나의 명령문으로 묶으면 잠금을 획득하여, rank 테이블을 찾을 수 없다는 오류는 발생하지 않는다.

5.3 InnoDB 스토리지 엔진 잠금

5.3.1 InnoDB 스토리지 엔진의 잠금

• 레코드 기반의 잠금 기능을 제공한다.

• 레코드 자체가 아니라 인덱스의 레코드를 잠근다.

• 보조 인덱스를 이용한 변경 작업은 넥스트 키 락 또는 갭 락을 사용한다.

• 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업은 갭에 대해서는 잠그지 않고 레코드 자체에 대해서만 락을 건다.

• 레코드 자체가 아니라 레코드 사이의 간격만 잠그는 것을 의미한다.

• 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어한다.

• 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 넥스트 키 락이라고 한다.

• 인덱스 레코드와 그 레코드 앞의 간격을 잠근다.

• MySQL의 변경 이벤트를 기록하는 로그파일이다.

• 로그 포맷은 다음과 같다.
• ROW 포맷: 행 단위로 변경사항을 기록
• STATEMENT 포맷: SQL문 자체를 기록
• MIXED 포맷: ROW와 STATEMENT를 혼합해서 사용

• STATEMENT 포맷은 공간 효율적이나 실행 시점에 따라 결과가 달라질 수 있는 위험이 있다.

• 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서도 동일한 결과가 나오도록 하기 위해 갭 락이나 넥스트 키 락을 사용한다.
• 의외로 이로 인한 데드락이나 병목 현상이 발생할 수 있기 때문에 가능하면 ROW 형태로 바꾸는 것이 좋다.
• MySQL 8.0 버전 부터는 ROW 포맷의 안정성도 높아져서 기본 설정으로 변경 되었다.

• 레플리카 서버에 원본 데이터가 복제되는 로직은 다음과 같다.

소스 서버:
1. 트랜잭션 실행
2. 갭 락으로 데이터 일관성 보장
3. 바이너리 로그에 기록

레플리카 서버:
1. 바이너리 로그 수신
2. 동일한 순서로 트랜잭션 실행
3. 소스와 동일한 결과 생성

-- 소스 서버
BEGIN;
-- 이 시점에 갭 락 설정
SELECT * FROM users WHERE id BETWEEN 100 AND 200 FOR UPDATE;
INSERT INTO users VALUES (150, 'New User');
-- 커밋 시점에 락 해제
COMMIT;

-- 바이너리 로그에 기록되는 내용
BEGIN;
SELECT * FROM users WHERE id BETWEEN 100 AND 200 FOR UPDATE;
INSERT INTO users VALUES (150, 'New User');
COMMIT;

-- 레플리카 서버에서 독립적으로 실행
-- (소스 서버의 락과는 무관)

• AUTO_INCREMENT라는 컬럼 속성을 통해 자동 증가하는 숫자 값을 제공한다.

• 내부적으로 AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금을 사용한다.

• AUTO_INCREMENT 컬럼에 명시적으로 값을 설정하더라도 자동 증가 락을 걸게 된다.

• 잠금을 최소화하기 위해 쿼리가 실패했더라도 한 번 증가된 값은 다시 줄지 않고 그대로 남는다.

5.3.2 인덱스와 잠금

• InnoDB의 잠금은 인덱스를 잠그는 방식으로 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 건다.

• 따라서 인덱스 설계가 중요하다.

5.3.3 레코드 수준의 잠금 확인 및 해제

• MySQL 8.0 버전부터 performance_schema의 data_locks와 data_lock_waits 테이블로 대체되고 있다.

• 쿼리 실행 결과는 잠금 대기 큐의 내용을 보여준다.

• KILL 명령을 통해 잠금을 가진 스레드를 강제 종료 할 수 있다.

5.4 MySQL 격리 수준

5.4.1 READ UNCOMMITED

• 각 트랜잭션의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보인다.

• 이런 현상을 더티 리드라 한다.

5.4.2 READ COMMITTED

• 오라클 DBMS에서 기본으로 사용되는 격리 수준이다.

• InnoDB에서는 커밋 전에는 언두 영역에 백업된 데이터를 가져와서 보여준다.

• 트랜잭션 도중 다른 트랜잭션에서 데이터를 커밋하면 한 트랜잭션에서 시점에 따라 다른 값을 조회할 수 있다.

• 이를 NON-REPEATABLE READ 라고 한다.

5.4.3 REPEATABLE READ

• REPEATABLE READ 격리 수준에서는 기본적으로 SELECT 쿼리 문장도 트랜잭션 범위 내에서만 동작한다.

• 변경되기 전 레코드를 언두 공간에 백업해두고 동일 트랜잭션 내에서는 동일한 결과를 보여준다.

• READ COMMITTED도 언두 영역에 백업된 레코드를 값을 보여주는 것은 동일하지만 어느 버전까지 찾아 들어가느냐에 차이가 있다.

• 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션 번호가 포함되어 있으며, REPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수 없다.

• 트랜잭션 도중 SELECT … FOR UPDATE와 같이 락을 걸기 위해 언두 영역이 아닌 현재 레코드 값을 가져와야 하는 경우에는 다른 트랜잭션에 의해 INSERT된 값이 조회될 수 있다.

• 이를 PHANTOM READ라고 한다.
• InnoDB의 REPEATABLE READ 격리 수준에서 PHANTOM READ가 발생하는 특수한 경우이며, 일반적으로 PHANTOM READ는 SELECT … FOR UPDATE 뿐만 아니라 조회 시 새로운 행이 나타나거나 사라지는 것을 의미한다.

5.4.4 SERIALIZABLE

• 읽기 작업도 공유 잠금을 획득해야 하기에 조회 중에도 다른 트랜잭션이 레코드를 변경하지 못하게 된다.

NON-REPEATABLE READ:
- 동일한 행의 데이터가 변경되는 현상
- UPDATE, DELETE에 의해 발생
- 이미 존재하는 데이터의 내용이 바뀜

PHANTOM READ:
- 결과 집합의 행이 추가/삭제되는 현상
- INSERT, DELETE에 의해 발생
- 새로운 행이 나타나거나 사라짐

1. 레코드 락:
• 개별 인덱스 레코드에 대한 락
• 예: id=5인 레코드만 잠금

2. 갭 락:
• 인덱스 레코드 사이의 간격을 잠금
• 예: id 5와 10 사이의 간격(6,7,8,9)을 잠금

3. 넥스트 키 락:
• 레코드 락 + 갭 락의 조합
• 예: id=5인 레코드와 그 다음 값까지의 범위를 잠금

06. 데이터 압축

6.1 페이지 압축

• Transparent Page Compression이라고 불리는데 디스크 I/O 시점에 암복호화 되기 때문에 MySQL 서버 내부 코드에서는 압축 여부와 관계없이 동작하기 때문이다.

• 하나의 테이블은 동일한 크기의 페이지(블록)로 통일되어야 하는데 데이터 페이지를 압축한 결과가 용량이 얼마나 될지 예측이 불가능하다.

• 이를 위해 운영체제별로 특정 버전의 파일 시스템에서만 지원되는 펀치 홀이라는 기능을 사용한다.
• 압축으로 인해 절약된 공간은 운영체제로 반환한다.

• 펀치 홀 기능은 운영체제뿐만 아니라 하드웨어 자체에서도 해당 기능을 지원해야 하고, 파일 시스템 관련 명령어가 펀치 홀을 지원하지 못한다. 때문에 실제 페이지 압축은 많이 사용되지 않는다.

• 페이지 압축의 작동 방식
1. 기본 원리
• MySQL이 16KB 페이지를 압축
• 압축된 데이터는 실제로 더 작은 공간 차지
• 하지만 파일시스템에는 여전히 16KB로 할당
2. Punch hole 메커니즘
• 압축 후 실제 사용하지 않는 공간을 운영체제에 반환
• 이를 위해 'sparse file' 및 'hole punching' 기능 필요

• 하드웨어/OS 제약이 생기는 주요 이유
1. 파일시스템 요구사항
• Sparse file 지원 필요
• Hole punching 기능 지원 필요
• 예: ext4, xfs 등의 최신 버전 필요
2. 운영체제 요구사항
• Linux: FALLOC_FL_PUNCH_HOLE 지원 필요
• 커널 버전 3.0 이상 필요
3. 스토리지 요구사항
• Sparse file을 올바르게 처리할 수 있어야 함
• 일부 스토리지 시스템은 이를 지원하지 않음

6.2 테이블 압축

• 버퍼 풀 공간 활용률이 낮음
• 압축 페이지 관리 방식
• InnoDB는 압축된 페이지와 압축 해제된 페이지를 모두 버퍼 풀에 저장
• 동일 데이터가 두 가지 형태로 존재하므로 공간이 2배로 필요
• 메모리 사용 구조
• 디스크: 압축된 형태로 저장 (예: 8KB)
• 버퍼 풀:
• 압축된 형태 (8KB) + 압축 해제된 형태 (16KB)
• 실제 16KB 데이터를 위해 24KB의 버퍼 풀 공간 사용

• 쿼리 처리 성능이 낮음
• 추가적인 CPU 작업
• 데이터 읽을 때 : 압축 해제 필요
• 데이터 쓸 때 : 압축 작업 필요
• 이러한 압축/해제 작업이 CPU 자원 소비
• 메모리 접근 패턴
• 압축 페이지 접근 시 항상 압축 해제 필요
• 캐시 히트라도 압축 해제 과정 필요
• 추가적인 메모리 연산 발생
• 작업 과정 예시

SELECT * FROM compressed_table WHERE id = 1;

1. 디스크에서 압축된 페이지 읽기
2. 압축 해제
3. 버퍼 풀에 두 가지 형태로 저장
4. 실제 데이터 처리

• 빈번한 데이터 변경 시 압축률이 떨어짐
• 페이지 분할(Page Split) 현상:
• 압축된 페이지에 새 데이터 추가 시
• 압축 후 크기가 설정된 크기를 초과하면
• 페이지를 분할해야 함
• 단편화 발생:
• 잦은 갱신으로 페이지 분할이 자주 발생
• 페이지 공간이 비효율적으로 사용됨
• 결과적으로 전체적인 압축률 저하

1. 적합한 사용 케이스
• 읽기 위주의 작업이 많은 테이블
• 데이터 변경이 적은 테이블
• 저장 공간 절약이 매우 중요한 경우

2. 부적합한 사용 케이스
• OLTP 환경의 주요 트랜잭션 테이블
• 빈번한 데이터 변경이 있는 테이블
• 성능이 매우 중요한 테이블

6.2.1 압축 테이블 생성

1. system 테이블스페이스
• ibdata1 파일
• 시스템 테이블 정보 저장
• 데이터 딕셔너리 정보 저장

2. file-per-table 테이블스페이스
• 각 테이블별로 별도의 .ibd 파일
• 테이블과 인덱스를 개별 파일로 관리

3. general 테이블스페이스
• 여러 테이블을 하나의 테이블스페이스에 저장
• CREATE TABLESPACE로 생성

4. undo 테이블스페이스
• 언두 로그를 저장
• 트랜잭션의 롤백에 사용

5. temporary 테이블스페이스
• 임시 테이블 저장
• 세션 종료시 자동 삭제

1. file-per-table 장점
• 테이블 별 관리 용이
• 공간 반환 가능
• 테이블 삭제 시 공간 즉시 운영체제 반환

2. general 테이블스페이스 장점
• 여러 테이블 통합 관리
• 공간 효율성
• 관리 편의성

• KEY_BLOCK_SIZE는 반드시 FILE_BLOCK_SIZE보다 작아야 하며, 4KB 또는 8KB만 사용 가능하다.

• FILE_BLOCK_SIZE가 4KB이거나 8KB인 경우에는 제약 사항이 생긴다.

FILE_BLOCK_SIZE와 KEY_BLOCK_SIZE

1. 정의
• FILE_BLOCK_SIZE: 제너럴 테이블스페이스의 물리적 페이지 크기를 지정
• KEY_BLOCK_SIZE: 테이블 압축 시 사용되는 압축된 페이지의 크기

2. 적용 범위
• FILE_BLOCK_SIZE: 테이블스페이스 전체
• KEY_BLOCK_SIZE: 개별 테이블

3. 목적
• FILE_BLOCK_SIZE: 물리적 저장 구조 정의
• KEY_BLOCK_SIZE: 데이터 압축 크기 지정

6.2.2 KEY_BLOCK_SIZE 결정

• 테이블 압축에서 가장 중요한 부분은 압축된 결과가 어느 정도가 될지를 예측해서 KEY_BLOCK_SIZE를 결정하는 것이다.

• 압축 시, 압축 실패율이 높다고 해서 압축을 사용하지 말아야 하는 것은 아니다.
• INSERT만 되는 로그 테이블 같은 경우, 압축 실패율이 높더라도 전체적으로 데이터 파일의 크기가 줄어든다면 압축할 필요가 있다.
• 조회와 변경이 빈번한 경우에는 압축 실패율이 낮더라도 압축하지 않는 편이 낫다. 테이블 압축은 zlib를 이용하는데 압축 알고리즘은 많은 CPU 자원을 소모한다.

6.2.3 압축된 페이지의 버퍼 풀 적재 및 사용

• InnoDB 스토리지 엔진은 압축된 테이블의 데이터 페이지를 버퍼풀에 적재하면 압축된 상태와 압축이 해제된 상태 2개 버전을 관리한다.
• 디스크에서 읽은 그대로의 데이터 페이지 목록을 관리하는 LRU 리스트와 압축 해제 버전인 Unzip_LRU 리스트를 별도로 관리한다.
• 결국 버퍼 풀 공간을 이중으로 사용함으로써 메모리를 낭비하는 효과를 가진다. 또한 압축 해제 작업에서 CPU 자원을 소모한다.
• 이러한 단점을 보완하기 위해 Unzip_LRU 리스트를 별도로 관리하며 요청에 따라 대응한다.
• 버퍼 풀 공간이 필요한 경우, Unzip_LRU에서 압축 해제된 버전은 제거
• 압축된 데이터 페이지가 자주 사용되는 경우, Unzip_LRU의 페이지를 유지하여 압축, 해제 작업 최소화
• 압축된 데이터 페이지가 사용되지 않아서 LRU 리스트에서 제거되는 경우, Unzip_LRU 리스트에서도 제거
• CPU 사용량이 높은 서버에서는 Unzip_LRU 비율을 높여서 유지
• Disk I/O 사용량이 높은 서버에서는 Unzip_LRU 리스트 비율을 낮춰서 버퍼 풀 공간을 확보

07. 데이터 암호화

7.1 MySQL 서버의 데이터 암호화

• MySQL 서버의 I/O 레이어에서 암복호화를 수행한다. 데이터는 디스크에 저장된 단계에서만 암호화 되는 것이다.

• MySQL 서버에서 쿼리를 처리하는 부분에서는 암호화된 데이터를 처리하지 않으므로 이러한 방식을 TDE(Transparent Data Encryption)이라고 한다.

7.1.1 2단계 키 관리

• MySQL 서버의 TDE에서 암호화 키는 키링 플러그인에 의해 관리된다.

• 데이터 암호화는 마스터 키와 테이블 스페이스 키(또는 프라이빗 키)라는 두 종류의 키로 처리한다.

• 마스터 키는 하시코프 Vault 같은 외부 키 관리 솔루션(KMS) 또는 디스크의 파일(Keyring_file)에서 관리한다.

• 테이블스페이스 키로 테이블의 데이터를 암호화하고 마스터키로 테이블 스페이스 키를 암호화하여 테이블의 데이터 파일 헤더에 저장한다.

• 마스터 키는 외부에 노출되므로 주기적으로 변경해야 하는데, 각각 테이블 파일 헤더의 암호화된 테이블 스페이스 키만 새로 암호화하여 저장하면 되기 때문에 효율적이다.

• MySQL의 TDE에서 지원하는 암호화 알고리즘은 AES 256비트이다.

7.1.2 암호화와 성능

• 디스크로부터 읽어온 데이터 페이지는 복호화되어 InnoDB의 버퍼 풀에 적재된다.

• 새로운 데이터를 다시 저장할 때도 저장은 백그라운드 스레드가 수행하기 때문에 사용자의 쿼리가 지연되지는 않는다.

• AES 암호화 알고리즘 특성상 데이터 페이지가 암호화키보다 훨씬 큰 상황에서 암호화된 결과는 평문과 동일한 크기이다. 따라서 암호화한다고 하여 InnoDB 버퍼 풀의 효율이 달라지거나 메모리 사용 효율이 떨어지는 현상은 발생하지 않는다.

• 암호화와 압축을 동시에 실행해야 한다면 압축을 먼저 실행하고 암호화를 적용한다.
• 암호화된 결과문의 압축률이 떨어짐
• 압축된 데이터는 압축, 압축 해제 두 가지의 상태로 InnoDB 버퍼 풀에서 처리되기에 암호화가 먼저 진행된다면 버퍼 풀의 데이터에 대해서도 매번 복호화를 진행해야 함

• 암호화된 테이블의 경우 읽기는 3~5배 정도, 쓰기의 경우 5~6배 정도 느리다.

7.1.3 암호화와 복제

• MySQL 서버에서 기본적으로 모든 노드는 각자의 마스터 키를 할당해야 한다. 따라서 레플리카 서버의 마스터 키 또한 원본과 다르게 관리한다.

7.2 keyring_file 플러그인 설치

• keyring_file 플러그인은 마스터키를 디스크의 파일로 관리하는데, 이때 마스터 키는 평문으로 디스크에 저장한다.
• 때문에 keyring_file 플러그인을 사용한다면 다른 서버에서 키링 파일을 로컬에 다운로드해서 서버를 시작한 후 로컬의 파일을 지우는 방식으로 사용하는 것을 고려해볼 수 있다.

7.3 테이블 암호화

7.3.1 테이블 생성

ENCRYPTION='Y'

• 일반적인 테이블 생성 구문과 동일하며, 마지막에 ‘ENCRYPTION='Y'’ 옵션만 추가로 넣는다.

• 모든 테이블에 대해 암호화를 적용하고자 한다면 default_table_encryption 시스템 변수를 ON으로 설정한다.

7.3.2 응용 프로그램 암호화와의 비교

• 응용 프로그램에서 암호화하여 데이터베이스에 저장하는 경우(비밀번호, 개인정보 등)가 있는데, 이럴 경우 인덱스 관련된 작업에 문제가 생긴다.

• MySQL 서버의 암호화 기능은 인덱스 관련 작업을 모두 처리한 후 최종 디스크에 저장할 때만 암호화하기 때문에 위와 같은 제약이 없다.

• 응용 프로그램에서 암호화하여 저장할 경우, 인덱스 관련 작업을 고려하여 처리해야 한다.

7.3.3 테이블 스페이스 이동

• 테이블을 다른 서버로 복사해야 하는 경우 등에 테이블스페이스 이동 기능이 레코드를 덤프했다가 복구하는 방식보다 훨씬 빠르고 효율적이다.

• 암호화 되어있는 경우 FLUSH TABLES 명령을 통해 테이블 스페이스를 Export 할 수 있다.

7.4 언두 로그 및 리두 로그 암호화

7.5 바이너리 로그 암호화