트랜잭션 , 데이터 압축 , 암호화

Vorhandenheit ·2022년 7월 14일

목록 보기

21/28

트랜잭션

트랜잭션은 논리적인 작업 셋 자체가 100% 적용이 되거나, 아무것도 적용되지않아야함을 보장해 주는 것입니다. 트랜잭션과 함께 나오는게 잠금인데 잠금은 동시성을 제어하기 위한 기능이고 트랜잭션은 데이터의 정합성을 보장하기 위한 기능입니다.

1. MySQL 엔진의 잠금

잠금은 스토리지 엔진과 MySQL엔진 레벨로 나눌 수 있습니다.

(1) 글로벌 락

MySQL에서 제공하는 잠금 가운데 가장 범위가 큽니다. 서버의 모든 변경 작업을 멈춥니다.

(2) 테이블 락

테이블 락은 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블 락을 획득 할 수 있습니다.
LOCK TABLES table_name[ READ | WRITE '로 특정 테이블을 설정할 수 있습니다.

(3) 네임드 락

네임드 락은 GET_LOCK() 함수를 이용해서 임의의 문자열에 대해서 잠금을 설정할 수 있습니다.

(4) 메타데이터 락

데이터베이스 객체의 이름이나 구조를 변경하는 경우에 획득하는 잠금입니다. 예를 들면 RENAME TABLE과 같이 테이블의 이름을 변경하는 경우, 자동으로 획득하는 잠금입니다.

2. InnoDB 엔진의 잠금

InnoDB 스토리지 엔진 내부에서 레고드 기반의 잠금 방식을 가지고 있습니다.

information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCK_WAITS, INNODB_LOCKS라는 테이블을 조인해서 죠회하면 현재 어떤 트랜잭션이 잠금을 대기하고 있고, 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인할 수 있습니다.

(1) InnoDB 스토리지 엔진의 잠금

레코드 락

레코드 자체만을 잠그는 것을 레코드 락이라고 합니다. InnoDB의 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠급니다.

갭락

갭 락은 레코드 자체가 아니라 레코드와 레코드 사이의 간격을 잠그는 걸 의미합니다. 이 갭 락의 역할은 새로운 레코드가 사이에 생성되는걸 제어하는 것입니다.

넥스트 키락

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

자동 증가 락

AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금입니다.

(2) 인덱스와 잠금

인덱스의 레코드를 잠그기 떄문에, 변경해야할 레코드를 찾기 위해서 인덱스의 레코드 모두에 락을 겁니다.

(3) 레코드 수준의 잠금 확인 및 해제

performance_schema 테이블을 이용해서 잠금과 잠금 대기 순서를 확인할 수 있습니다.

(4) MySQL의 격리 수준

여러 트랜잭션이 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지 결정하는 것입니다.

A. READ UNCOMMITTED

어떤 트랜잭션에서 처리한 작업이 완료되지않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 '더티 리더'라고 합니다.
이 '더티 리더'가 허용되는 격리 수준이 READ UNCOMMITED입니다.

B. READ COMMITTED

오라클에서 기본으로 사용되는 격리 수준입니다. 여기에서는 더티리드 같은 현상 발견되지않습니다. 어떤 트랜잭션에서 데이터를 변경했다고 하더라도 commit이 완료되어야만 다른 트랜잭션에서 조회할 수 있기 때문입니다.

여기에서는 'NON-REPEATABLE READ' 라는 부정합 문제가 있습니다. 어떤 트랜잭션에서 작업시 다른 트랜잭션이 끼어들어 반환하는 값이 다를 때 이 부정합 문제가 발생합니다.

C. REPEATABLE READ

InnoDB에서 기본적으로 사용되는 격리 수준입니다.여기에서는 부정합 문제가 발생하지않습니다. 여기에서는 'PHANTOM TREA' 다른 트랜잭션에서 수행한 변경 작업에 레코드가 보였다 안보였다하는 현상을 볼 수 있습니다.

D. SERIALIZABLE

가장 엄격한 격리 수준입니다. InnoDB는 select작업에는 레코드 잠금도 설정되지않고 실행됩니다. 이 단계에서는 설정이됩니다. 그렇기 떄문에, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없습니다.

데이터 압축

mysql 서버에서 사용 가능한 압축 방식은 테이블 압축과 페이지 압축 두 가지 종류로 구분할 수 있습니다.

1. 페이지 압축

'Transparent Page Compresssion', MySQL 서버가 디스크에 저장할 때 데이터 페이지가 압축되어 저장되고, 반대로 MySQL서버가 디스크에서 데이터 페이지를 읽어올 때는 압축이 해제가됩니다.

이 페이지 압축은 한 가지 문제가 있는데, 압축했을시 용량이 얼마나 될지 예측이 불가능합니다. 디스크에 이 압축된 페이지를 저장하고 남는 공간을 펀치 홀이라고 합니다. 하지만 이 펀치홀 기능을 지원하지않으면 오히려 자원을 잡아먹는 결과가 생길 수 있습니다.

2. 테이블 압축

(1) 압축 테이블 생성

테이블 압축을 사용하기 위해서는 전제조건으로 압축을 사용하려는 테이블이 별도의 테이블 스페이스를 사용해야합니다.
inno_db_file_per_table 시스템 변수가 ON으로 설정된 상태에서 테이블이 생성돼야합니다.
그리고 테이블 압축을 사용할려면 테이블 생성할 때 ROW_FORMAT=COMPRESSSED 옵션을 명시해야합니다.

(2) KEY_BLOCK_SIZE를 결정

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

(3) 압축된 페이지의 버퍼 풀 적재 및 사용

InnoDB 스토리지 엔진은 압축된 테이블의 데이터 페이지를 버퍼 풀에 적재하면 압축된 상태와 압축이 해제된 상태 2개 버전을 관리합니다.
그래서 InnoDB 스토리지 엔진은 디스크에서 읽은 상태 그대로의 데이터 페이지 목록을 관리하는 LRU리스트와 압축된 페이지들의 압축 해제 버전인 Unzip_LRU 리스트를 별도로 관리하게 됩니다

InnoDB 버퍼 풀의 공간이 필요한 경우에는 LRU 리스트에서 원본 데이터 페이지는 유지하고, Unzip_LRU 리스트에서 압축 해제된 버전은 제거해서 버퍼 풀의 공간을 확보합니다
압축된 데이터 페이지가 자주 사용되는 경우에는 Unzip_LRU 리스트에 압축 해제된 페이지를 계속 유지하면서 압축 및 압축 해제 작업을 최소화합니다.
압축된 데이터 페이지가 사용되지않아서 LRU 리스트에서 제거되는 경우에는 Unzip_LRU 리스트에서도 함께 제거됩니다

(4) 테이블 압축 관련 설정

압축 실패율을 낮추기 위한 필요한 튜닝 포인트 제공

'innodb_cmp_per_index_enabled' : 테이블 압축이 사용된 테이블의 모든 인덱스별로 압축 성공 및 압축 실행 횟수를 수집하도록 설정합니다.
테이블 단위로 수집된 정보는 'information_schema.INNO_CMP 테이블에 기록되며, 인덱스 단위로 수집된 정보는 'information_schema.INNODB_CMP_PER_INDEX' 테이블에 기록됩니다
'innodb_compression_level' : InnoDB의 테이블 압축은 zlib알고리즘만 지원합니다. 이때 시스템 변수를 이용해 압축률을 설정할 수 있습니다.
'innodb_compresssion_failure_threshold_pct' & 'innodb_compression_pad_pct_max' : 테이블 단위로 압축 실패율이 시스템 설정값보다 커지면 압축을 실행하기 전 원본 데이터 페이지의 끝에 의도적으로 일정 크기의 빈 공간을 추가합니다. 여기서 빈공간을 패딩이라고 합니다.
'inndb_log_compressed_pages' : MySQL 서버가 비정상적으로 종료됐다가 다시 시작하는 경우 압축알고리즘의 버전 차이가 있더라도 복구 과정이 실패하지않도록 innoDB 스토리지 엔진은 압축된 데이터 페이지를 그대로 리구 로그에 기록합니다. 하지만 이는 리두 로그 증가량에 상당한 영향을 미칩니다 시스템 변수를 OFF로 설정한 후 모니터링해보는 것이 좋습니다.

7. 데이터 암호화

(1) 7.1 MySQL 서버의 데이터 암호화

데이터베이스 서버와 디스크 사이의 데이터 읽고 쓰는 지점에서 암호화 또는 복호화를 수행합니다.
즉 InnoDB I/O 레이어에서만 데이터 암호화 및 복호화 과정이 실행됩니다
이러한 암호화 방식을 TDE(Transparent Data Encrption)이라고 합니다.

A. 2단계 키 관리

키 관리 아키텍처

이 TDE에서 암호화 키는 키링 플러그인에 의해 관리됩니다.
MySQL 서버의 데이터 암호화는 마스터 키와 테이블스페이스 키라는 두 가지 종류의 키를 가지고 있습니다. 여기서 테이블스페이스 키는 '프라이빗 키'라고 부르기도 합니다.

MySQL서버는 외부 키 솔루션 또는 플러그인에서 마스터 키를 가저오고, 암호화된 테이블이 생성될 때마다 해당 테이블을 위한 임의의 테이블스페이스 키를 발급합니다.
그리고 MySQL 서버는 마스터 키를 이용해서 테이블 스페이스키를 암호화해서 각 테이블의 데이터 파일 헤더에 저장합니다.

B.암호화 성능

TDE 방식은 데이터 페이지가 한번 메모리에 적재되면 암호화되지않은 테이블과 동일한 성능을 보이지만, 쿼리가 InnoDB 버퍼 풀에 존재하지않는 데이터 페이지를 읽어야 하는 경우에 복호화 과정을 거치기 떄문에 복호화 시간동안 쿼리 처리가 지연될 것입니다. 또 암호화된 테이블이 변경되면 다시 디스크로 동기화될 때 암호화해야되기 때문에 디스크에 저장할 때도 추가로 시간이 걸립니다.

C. 암호화와 복제

Mysql 서버에서 소스서버와 레플리카 서버는 서로 각자의 마스터 키와 테이블 스페이스 키를 관리하기 떄문에 암호화된 데이터가 저장된 데이터 파일의 내용은 달라집니다.
그래서 마스터 키 로테이션을 실행하면 소스 서버와 레플리카 서버가 각각 서로 다른 마스터 키를 새로 발급받습니다.

(2) keyring_file 플러그인 설치

keyring_file 플러그인은 테이블스페이스 키를 암호화하기 위한 마스터 키를 디스크의 파일로 관리합니다. 이 때 마스터 키는 평문으로 디스크에 저장됩니다.

MySQL 서버 설정파일(my.cnf)에서 'early-plugin-load' 시스템 변수에 'keyring_file'플러그 인을 위한 라이브러리를 명시하면 됩니다
그리고 keyring_file 플러그인이 마스터 키를 저장할 키링 파일의 경로를 keyring_file_data 설정에 명시하면 됩니다.

(3) 테이블 암호화

A. 테이블 생성

테이블 생성 구문과 동일하며, 마지막에 'ENCRYPTION=Y'옵션만 추가로 넣으면됩니다. 이 테이블의 데이터가 디스크에 기록될 때는 데이터가 자동으로 암호화되어 저정되고 다시 디스크에서 메모리로 읽어올 때 복호화됩니다.

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

응용 프로그램 암호화와 MySQL 서버의 암호화 기능 중 선택해야하는 상황이라면 고민할 필요없이 MySQL서버의 암호화 기능 선택할 것을 권장

C. 테이블 스페이스 이동

TDE로 암호화된 테이블에 대해 'FLUSH TABLES source_left FOR EXPORT'명령을 실행하면 MySQL 서버는 임시로 사용할 마스터 키를 발급해서 source_table.cfp라는 파일로 기록합니다. 테이블스페이스 이동기능을 사용할 떄는 반드시 데이터 파일과 임시 마스터 키가 저장된 *.cnf 파일을 함께 복사해야합니다.

D. 언두 로그 및 리두 로그 암호화

'innodb_undo_log_encrypt' 시스템 변수와 'innodb_redo_log_encrypt' 시스템 변수를 이용해 InnoDB 엔진의 리두 로그와 언두 로그를 암호화된 상태로 저장할 수 있습니다.
리두 로그와 언두 로그를 위한 각각의 프라이빗 키가 발급되고, 해당 프라이빗 키는 마스터 키로 암호화되어서 리두 로그파일과 언두 로그 파일의 헤어데 저장됩니다

(4) 바이너리 로그 암호화

바이너리 로그는 의도적으로 긴 시간 동안 보관하는 서비스도 있고 중분 백업을 위해 바이너리 로그를 보관하기도 합니다.

A. 바이너리 로그 암호화 키 관리

바이너리 로그와 릴레이 로그 파일의 데이터는 파일 키로 암호화해서 디스크로 저장하고, 파일 키는 '바이너리 로그 암호화 키'로 암호화해서 각 바이너리 로그와 릴레이 로그 파일의 헤더에 저장됩니다
즉 '바이너리 로그 암호화 키'는 테이블 암호화의 마스터 키와 동일한 역할을 하며, 파일 키는 바이너리 로그와 릴레이 로그 파일 단위로 자동 생성되어 해당 로그 파일의 데이터 암호화에만 사용됩니다.

B. 바이너리 로그 암호화 키 변경

바이너리 로그 암호화 키는 다음과 같이 변경할 수 있습니다,
'ALTER INSTANCE ROTATE BINLOG MASTER KEY'
1.증가된 시퀸스 번호와 함께 새로운 바이너리 로그 암호화 키 발급 후 키링 파일에 저장
2. 바이너리 로그 파일과 릴레이 로그 파일 스위치
3. 새로 생성되는 바이너리 로그와 릴레이 로그 파일의 암호화를 위해 파일 키를 생성하고, 파일 키는 바이너리 로그파일 키로 암호화해서 각 로그 파일에 저장
4. 기조 ㄴ바이너리 로그와 릴레이 로그파일의 파일 키를 읽어서 새로운 바이너리 로그 파일 키로 암호화해서 다시 저장
5. 모든 바이너리 로그와 릴레이 로그 파일이 새로운 바이너리 로그 암호화키로 다시 암호화됐다면 기조 ㄴ바이너리 로그 암호화 키를 키링 파일에서 제거

'SHOW BINARY LOGS'로 확인할 수 있습니다.

C. mysqlbinlog 도구 활용

바이너리 로그 암호화 키는 그 바이너리 로그나 릴레이 로그 파일을 생성한 MySQL 서버가 가지고 있기 떄문에 MySQL 서버와 관계없이 mysqlbinlog도구만으로는 복호화할 방법이 없습니다.
바이너리 로그 파일의 내용을 볼 수 잇는 방법은 MySQL 서버를 통해 가져오는 방법이 유일합니다.

Vorhandenheit

읽고 기록하고 고민하고 사용하고 개발하자!

이전 포스트

Mysql 스토리지 엔진

다음 포스트