5. 트랙잭션과 잠금

MySQL 엔진의 잠금

글로벌 락

FLUSH TABLES WITH READ LOCK 명령어로 획득할 수 있으며 MySQL 제공하는 잠금 가운데 가장 범위가 크다. 다른 세션에는 DDL, DML 실행이 불가 하며 LOCK 이 해제될 때까지 대기해야한다.

테이블 락

LOCK TABLES table_name READ | WRITE 명령으로 특정 테이블에 대해 LOCK을 획득할 수 있다.
InnoDB에서 DML 쿼리는 무시되고 DDL의 경우 영향을 미친다.

네임드 락

GET LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다. 단순히 사용자가 지정한 문자열(String)에 대해 획득하고 반납하는 잠금이다.

메타데이터 락

데이터베이스 객체의 이름이나 구조를 변경하는 경우에 획득하는 LOCK이다. 명시적으로 획득하거나 해제할 수 있는것이 아니고 RENAME TABLE a TO b 같이 이름을 변경하는 경우 자동으로 획득한다
RENAME TABLE a TO a_backup, new_a TO a 명령어를 사용하면 실제 Application에서는 Error를 발생하지 않고 테이블을 교체할 수 있다.

사이즈가 있는 테이블의 구조를 수정 해야한다면 DDL 보다는 신규 테이블을 만들고 데이터를 옮기고 RENAME 명령을 통해 이관하는게 좋다.

InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다.
INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 테이블을 조인해서 조회하면 현재 어떤 트랙재션이 잠금을 대기하고 잠금을 하고 있는지 확인할 수 있다.

LOCK 종류

• 레코드 락
  레코드 자체만을 잠그는 것을 레코드 락이라고 하며 다른 사용 DBMS의 레코드 락과 동일한 역할을 한다. 다른 점은 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 점이다. 인덱스가 없는 테이블이더라고 내부적으로 인덱스를 생성해 잠금을 설정한다.

• 갭 락
  GAP LOCK은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠근는 것을 의미한다. 역할은 Gap 사이의 새로운 레코드가 INSERT 되는 것을 제어하는 것

• 넥스트 키 락
  레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 넥스트 키 락이라고 한다.
  STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL은 서버에는 REPEATABLE READ 격리 수준을 사용해야 한다. 또한 innodb_locks_unsage_for_binlog 시스템 변수가 비활성화되면 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식을 잠금이 걸린다.

• 자동 증가 락
  AUTO INCREMENT를 통해 자동 증가하는 값을 사용할 때 사용하는 잠금이다. 해당 잠금은 명시적을 획득하고 해체하는 방법은 없다.

인덱스와 잠금

InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다. 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다.

--300개 (no index)
SELECT COUNT(*) FROM a_tb WHERE phone='010';

--1개 (index)
SELECT COUNT(*) FROM a_tb WHERE id='hhosng'

UPDATE a_tb SET name='hhsong' WHERE phone='010' AND id='hhsong';

위 UPDATE를 실행할 경우 잠금이 걸리는 레코드는 300개이다.
만약 인덱스가 없는 테이블에 대해 UPDATE를 한다면 테이블 풀 스캔을하면서 모든 레코드에 대해 LOCK 처리가 된다.

MySQL의 격리 수준

	DIRTY READ	NON_REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	발생	발생	발생
READ COMMITTED	없음	발생	발생
REPEATABLE READ	없음	없음	발생 (InnoDB는 없음)
SERIALIZABLE	없음	없음	없음

격리 수준이 높아질수록 MySQL 서버의 처리 성능이 많이 떨어질 것으로 생각하는 사용자가 많은데, 사실 SERIALIZABLE 격리 수준이 아니라면 크게 성능의 개선이나 저하는 발생하지 않는다.

READ UNCOMMITTED

한 트랙잭션이 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 더티 리드라고 하고, 허용되는 격리 수준이 READ UNCOMMITTED다.

READ COMMITTED

가장 많이 선택되는 격리 수준이며 더티 리드 같은 현상은 발생하지 않는다. 하지만 트랜잭션을 시작하고 종료전에 다른 트랜잭션에서 commit 한 정보를 조회할 수 있어, 항상 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성에 어긋난다.

REPEATABLE READ

InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다. 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되기 전 레코드를 언두 공간에 백업해두고 실제 레코드 값을 변경한다. 이러한 변경 방식을 MVCC라고 한다

모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차 증가)를 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있다. 해당 번호를 통해 데이터를 조회할 때 자기보다 작은 값을 조회하며 일정한 값을 조회할 수 있도록 유지한다.

언두 영역에서 트랜잭션에 연관된 번호들은 COMMIT, ROLLBACK이 완료되지 않는 이상 계속 데이터가 남게되며, 해당 백업 데이터가 많아지면 MySQL 서버의 처리 성능이 떨어질 수 있다.

하지만 다음과 같은 부정합이 발생할 수 있다.
SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE 인 경우는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져온다. 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 PHANTOM READ라고 한다.

SERIALIZABLE

가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준. 하나의 트랜잭션이 잠금을 획득한 순간 다른 트랜잭션은 앞선 트랜잭션이 종료 전까지 대기해야한다.

6. 데이터 압축

디스크에 저장된 데이터 파일의 크기는 일반적으로 쿼리의 처리 성능과도 직결되지만 백업 및 복구 시간과도 밀접하게 연결된다.

페이지 압축

MySQL 서버가 디스크에 저장하는 시점에 데이터 페이지가 압축되어 저장되고, 반대로 페이지를 읽어올 때 압축이 해체된다. 버퍼 풀에 데이터 페이지가 적재되면 압축이 해체된 상태로 관리된다.
페이지를 압축한 결과가 용량이 얼마나 될지 예측이 불가능한데 적어도 하나의 테이블은 동일한 크기의 페이지로 통일해야 한다. 그래서 펀치 홀 이라는 기능을 사용한다. 페이지 크기가 16KB이고 압축된 페이지가 10KB이다. 데이터를 읽을때 빈 공간인 6KB를 더해서 16KB로 읽을 수 있게 한다.
하지만 압축된 용량이 다르고 여러 페이지의 원본 데이터 용량을 알 수 없어 많이 사용되지 않는 상태이다.

테이블 압축

OS나 H/W에 대한 제약 없이 사용할 수 있기 때문에 활용도가 더 높은 편이다. 하지만 몇 가지 단점이 있다.

• 버퍼 풀 공간 활용률이 낮음
• 쿼리 처리 성능이 낮음
• 빈번한 데이터 변경 시 압축률이 떨어짐

7. 데이터 암호화

MySQL 서버의 데이터 암호화

DB 서버와 디스크 사이의 데이터를 읽고 쓰기 시점에서 암호화 or 복호화를 수행한다. InnoDB 스토리지 엔진의 I/O에서만 데이터의 암호화 및 복화화 과정이 실행된다. 암호화된 테이블도 그렇지 않은 테이블도 동일한 처리 과정을 거친다. 그렇다보니 사용자 입장에서는 아무런 차이가 없다. 이러한 암호화 방식을 TDE(Transport Data Encryption)이라고 한다.

2단계 키 관리

MySQL 서버의 TDE에서 암호화 키는 키링 플러그인에 의해 관리된다. 데이터 암호화는 마스터 키와 테이블스페이스 키라는 2가지 종류의 키를 가지고 있다.
마스터 키는 외부의 파일을 이용하기 때문에 노출될 가능성이 있다. 그래서 마스터 키는 주기적으로 변경해야 한다.
테이블스페이스 키는 각 테이블의 데이터 파일 헤더에 저장하여 외부로 누출되지 않기 때문에 보안상 취약점이 되지는 않는다.

암호화와 성능

암호화는 TDE 방식이기 때문에 디스크로부터 한 번 읽은 데이터 페이지는 복호화되어 InnoDB의 버퍼 풀에 적재된다. 그래서 한 번 메모리에 적재되면 암호화되지 않은 테이블과 동일한 성능을 보인다. 하지만 디스크로 부터 데이터를 읽는 경우 복호화 과정을 거치기 때문에 복호화 시간 동안 쿼리 처리가 지연될 것이다. 하지만 쓰기는 백그라운드 스레드가 수행하기 때문에 실제 사용자 쿼리가 지연되는 것은 아니다.
└ 100GB 보다 작은 테이블 크기에 대해 1ms 정도 차이난다.

AES(Advanced Encryption Standard) 암호화 알고리즘은 암호화하고자 하는 평문의 길이가 짧은 경우 암호화 키의 크기에 따라 암호화된 겨로가의 용량이 더 커질 수 도 있지만, 이미 페이지는 암호화 키보다 크기 때문에 동일한 크기의 암호문을 반환한다. 그래서 TDE를 적용한다고 해도 버퍼 풀의 효율이 달라지거나 메모리 사용 효율이 떨어지는 현상은 발생하지 않는다.

암호화와 복제

TDE를 이용한 암호화 사용 시 마스터 키콰 테이블스페이스 키는 레플리카로 복제되지 않는다. 서로 각자의 마스터 키와 테이블스페이스 키를 관리하기 때문에 복제 데이터 파일은 암호화 되기 전의 값이 동일하더라도 파일의 내용은 완전히 달라진다.

테이블 암호화

테이블 생성 시 ENCRYPTION='Y' 옵션만 추가로 넣으면 된다.

테이블스페이스 이동

TDE가 적용되어 암호화된 테이블의 경우 마스터 키가 다르기에 FLUSH TABLES table FOR EXPORT 명령으로 할 수 있다.
해당 명령어를 실행하면 다른 MySQL 서버에서 사용할 임시 마스터 키를 발급해서 table.cfg라는 파일로 기록한다. 해당 임시키로 다시 테이블을 암호화하여 헤더 부분에 저장한다.
└*.cfg 파일을 함께 복사해야만 복구가 가능하다.

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

데이터 파일 이외의 디스크 파일로 기록되는 경우에는 여전히 평문으로 저장됨으로 innodb_undo_log_encrypt and innodb_redo_log_encrypt를 이용해 암호화된 상태로 저장할 수 있게 된다.

적용시킨 시점부터 암호화가 됨으로 이전에 기록된 log 내용에 대해서는 평문으로 기록된다.
각각의 로그는 마스터 키로 인한 테이블스페이스 키로 암호화된다
암호화된 키는 각각 파일의 헤더에 저장된다.

바이너리 로그 암호화

바이너리 로그는 의도적으로 상당히 긴 시간 동안 보관하는 서비스도 있고 때로는 증분 백업을 위해 보관하기도 한다. 이런 이유로 바이너리 로그 파일의 암호화는 상화에 따라 중요도가 높아질 수도 있다.

로그의 데이터는 파일 키로 암호화해서 디스크로 저장하고 각각의 파일 헤더에 저장된다.