1. MySQL 8.0의 계정 관리 정책 변화의 핵심은?
가. 사용자 / 인증 관리의 변화
기본 인증 설정 변경에 따른 보안성 향상, 이중 비밀번호 도입에 따른 사용성 향상
1) 계정 생성과 권한 부여의 분리
•
MySQL 8.0에서는 ‘CREATE USER’ 명령으로 계정을 생성하고 ‘GRANT’ 명령으로 권한을 부여한다. 참고로 MySQL 5.7에서는 ‘GRANT’ 명령으로 계정을 생성하고 동시에 권한을 부여할 수 있었다.
•
계정 생성과 권한 부여를 분리함에 따라 뒤에서 나오는 역할이 계정과 별개로 생성할 수 있게 되었다.
2) 계정 생성 시 요구되는 기본 인증 설정 변경
•
MySQL 8.0에서는 ‘Caching SHA-2 Authentication’을 기본 인증 방식으로 채택했다. 5.7 버전에서는 ‘Native Authentication’을 기본 인증 방식으로 채택한다.
•
Native Authentication: 평문 비밀번호를 SHA-1(64Bit) 기반으로 해싱하여 DB 서버에 저장하고, 클라이언트가 보낸 해싱된 비밀번호가 DB에 저장된 것과의 일치여부 확인
•
Caching SHA-2 Authentication: Native Authentication과 가장 큰 차이는 해시 알고리즘과 Caching이다. 해시 알고리즘의 경우 SHA-1과 달리 Salt를 활용하고 수천 번의 해싱과정이 추가되기 때문에 동일한 평문의 비밀번호가 입력되어도 다른 해시 값이 생성된다. Caching의 경우, 위에서 언급한 SHA-2 기반 알고리즘으로 생성하는 과정에 CPU 자원이 많이 소모되므로 이전에 연결된 클라이언트와의 재인증과정에서는 메모리에 캐싱해둔 기존 해시 값을 재사용한다.
3) 이중 비밀번호 도입에 따른 사용성 개선
•
MySQL 8.0부터는 ‘이중 비밀번호’ 도입에 따라 DB 서버가 동작 중인 상태에서 비밀번호를 변경할 수 있다. 5.7 버전에서 DB 서버의 비밀번호를 변경하기 위해서는 해당 DB를 사용하는 서비스를 멈춰야 했다.
•
‘이중 비밀번호’ 적용하기 위해서는 기존 비밀번호가 설정된 상태에서 새로운 비밀번호 생성 시 RETAIN CURRENT PASSWORD 옵션을 추가하면 된다. 기존 비밀번호가 Secondary가 되고 새로 생성한 비밀번호가 Primary가 된다. WAS에서는 비밀번호를 Primary로 변경 후 재배포하면 전체적인 서비스가 동작한 상태에서 DB 서버의 비밀번호를 변경할 수 있다.
나. 권한 / 역할 관리의 변화
권한의 분리와 역할의 등장에 따라 직무별 권한에 대한 제한이 가능해짐
1) 동적 권한의 등장과 SUPER 권한의 분산
•
SUPER 권한으로 묶여 있던 권한을 여러 동적 권한으로 세분화하여 DB 관리 직무에 따라 배분할 수 있게 되었다. 리소스 관리, 백업 & 복제 관리, 서버 관리, 이중 비밀번호 관리 등의 동적 권한을 관리자의 직무에 따라 적절하게 나눌 수 있다.
2) 계정의 제한된 버전인 역할의 탄생
•
계정(account)과 역할(role)의 차이점은 로그인 가능 여부일 뿐이다. 다시 말해, mysql.user 테이블 기준으로 계정과 역할의은 ‘account_locked’ 필드의 값만 다르다. 계정은 해당 값이 Y이므로 로그인이 가능하지만 역할은 N이므로 로그인이 불가능하다.
•
‘계정 생성 권한’을 가진 관리자와 ‘역할 생성 권한’을 가진 관리자를 분리할 수 있다. 관리 직무를 분리하면 책임관계가 보다 명확해지고 Human Error의 가능성도 낮아진다.
다. 기타 질문
1) ‘Caching SHA-2 Authentication’으로 생성된 해시값은 같은 ‘평문 비밀번호’를 입력해도 다른 값을 생성한다. 그렇다면 server와 client는 어떤 원리로 비밀번호의 일치여부를 확인할 수 있는가?
•
server와 client 사이의 ‘공통의 인증 변수’를 공유한다는 사실을 확인함으로써 비밀번호의 일치여부를 확인할 수 있다. ‘공통의 인증 변수’는 Salt 값과 Hashing 반복 횟수(or Stretching) 등의 조합으로 구성된다. 보다 구체적인 원리는 다음의 링크를 참고하여 확인할 수 있다. (링크) SCRAM의 동작 원리
2. MySQL 아키텍처의 주요 특징은?
가. MySQL 엔진 아키텍처 주요 특징
키워드: 포그라운드 쓰레드, 쓰레드 풀, 쿼리 파싱, 전 처리, 옵티마이저, 쿼리 실행
1) SQL 요청 → 포그라운드 쓰레드 → 백그라운드 쓰레드
•
InnoDB 기준으로 포그라운드 쓰레드가 클라이언트의 SQL 요청을 받아서 캐시나 버퍼로 데이터를 가져간다. 백그라운드 쓰레드가 캐시나 버퍼에서 가져다가 디스크에 데이터를 기록한다. 데이터가 디스크에 기록되는 과정 중간에 버퍼가 있기 때문에 클라이언트는 데이터 갱신 작업 시 디스크에 기록될 때까지 기다리지 않아도 된다.
2) 쓰레드 풀 활용하여 CPU 자원 최적화 상태에서 DB 커넥션 유지
•
CPU 자원을 최적으로 활용하기 위해 쓰레드풀을 활용하여 쓰레드의 수를 적절한 수준으로 유지한다. 쓰레드 풀의 사이즈는 CPU 코어의 개수와 일치시키는 것을 권장한다. 권장사항에 따라 쿼드 코어 환경에서 쓰레드 풀의 사이즈(default 3)는 4로 설정할 수 있다. 참고로 쓰레드 풀의 사이즈는 쓰레드의 개수가 아니라 쓰레드 그룹의 개수를 뜻한다. 더불어 쓰레드 풀 방식에서 하나의 쓰레드는 여러 커넥션 요청을 전담한다.
3) SQL 요청 → 쿼리 파서 → 전처리기 → 옵티마이저 → 쿼리 실행기 → 이하 스토리지 엔진
•
포그라운드 쓰레드는 쿼리 실행기의 역할까지 담당하고 백그라운드 쓰레드에서 이하 스토리지 엔진의 역할을 담당한다.
나. InnoDB 스토리지 엔진 아키텍처 주요 특징
키워드: 클러스터링 인덱스, MVCC, 버퍼풀, 언두 로그, 리두 로그
1) 클러스터링 인덱스 중심의 쿼리 실행 계획 생성
•
클러스터링 인덱스의 핵심은 Primary Key를 중심으로 실 데이터(Document)가 Disk에 묶음으로 저장된다는 것이다. 더불어 세컨더리 인덱스는 Primary Key의 논리적 주소를 참조한다. 다른 엔진과 비교하면 이 차이를 확인할 수 있다. 클러스터링 인덱스가 적용되지 않은 경우, 프라이머리 키를 포함한 인덱스는 Disk의 물리적인 주소값을 갖는다. 이러한 특징으로 인해 클러스터링 인덱스로 저장된 레코드에 대해 레인지 스캔을 할 경우, 높은 성능을 보여준다.
2) MVCC에 의한 잠금 없는 읽기 지원
•
MVCC는 잠금을 건너뛰고 Undo Log 상에서 변경된 레코드의 값을 읽을 수 있도록 지원하는 기술이다. 트랜잭션에 의해 레코드의 변경이 발생하면 해당 레코드의 Undo Log에 기존 레코드 값과 함께 트랜잭션 아이디가 작성된다. Undo Log에는 트랜잭션을 기준으로 특정 레코드의 변경이 기록된다. 다시 말해, 트랜잭션 아이디를 기준으로 특정 레코드의 값을 버전별로 확인할 수 있는 것이다.
3) Buffer Pool 활용에 따른 디스크 작업의 지연 시간 감소
•
Buffer Pool은 디스크 내 파일과 인덱스 정보를 캐시의 형태로 메모리에 저장하는 공간이다. Buffer Pool 활용에 따라 물리적으로 떨어진 디스크 내 데이터에 대한 작업을 모았다가 동시에 진행할 수 있으므로 작업 지연시간을 줄일 수 있다.
4) Undo Log 활용하여 롤백 대비 및 동시성 지원
•
트랜잭션 내에서 레코드에 변경사항이 발생하면 변경된 값을 레코드에 즉시 반영하고 이전의 레코드 값을 Undo Log에 저장한다. 일종의 과거의 값이 Undo Log에서 관리되기 때문에 이를 바탕으로 데이터 복구와 락이 없는 동시성 제어를 지원할 수 있다. 트랜잭션 커밋에 실패하여 롤백하면 Undo Log에 이력으로 남은 데이터를 기반으로 트랜잭션 내에서 변경된 데이터를 복구한다. Undo Log 내 트랜잭션 아이디와 레코드의 변경이력을 바탕으로 격리수준에 따른 동시성을 지원한다.
5) Redo Log 활용에 따라 ACID의 Durable 지원
•
레코드에 변경사항이 발생하면 변경 내용을 Disk에 반영하기 전에 Redo Log에 기록한다. 변경 사항이 Disk에 반영되기 전에 시스템이 다운되더라도 Redo Log를 참고하여 추후 변경 사항이 Disk에 반영될 수 있다. 참고로 Redo Log는 메모리에 있는 Log Buffer에 쌓여있다가 주기적으로 또는 특정 이벤트에 따라서 Disk에 있는 Redo Log File에 저장된다.
•
시스템에 문제가 생긴 후 다시 시스템이 올라올 때, Redo Log File에 쌓여 있는 트랜잭션의 히스토리를 보면서 DB에 발생한 변경사항을 확인한다.
다. 기타 질문
1) InnoDB가 아닌 다른 엔진에서는 프라이머리 키의 순서대로 레코드를 디스크에 저장하지 않는다. 어떻게 다르게 저장되나?
•
MyISAM 엔진의 경우, 레코드가 Insert되는 순서대로 디스크에 저장된다. 물론 중간에 삭제된 레코드가 있으면 빈 물리적 공간에 저장되기도 한다. 참고로 모든 인덱스는 디스크의 물리 주소를 포인터 값으로 갖는다.
2) Update나 Delete 쿼리에 의해 데이터 변경이 발생하면 언두로그에 해당 데이터의 변경이력이 남아서 MVCC에 사용된다. 하지만 Insert 발생에 따라 생성된 언두로그는 MVCC에 사용되지 않는다. 그 이유는 무엇인가?
•
특정 트랜잭션이 시작되기 전에 Insert 쿼리가 존재하지 않았다면 트랜잭션이 끝날 때에도 없는 값으로 처리하면 MVCC의 목적(잠금 없는 동시성 제어)을 달성할 수 있다. 따라서 Insert에 의해 생성된 언두로그는 MVCC에 사용될 필요가 없다. 참고로, Update나 Delete의 경우 특정 트랜잭션이 시작되기 전부터 존재하던 레코드에 대한 변경 작업이기 때문에 값의 변경사항을 이력으로 남겨두어야 격리수준에 따라 MVCC의 목적을 달성하는데 활용될 수 있다.
3. Transaction을 효과적으로 사용하는 방법은?
가. Transaction 범위 설정
데이터의 부정합을 야기할 만한 로직에만 Transaction을 적용하는 것이 서비스 성능면에서 바람직하다
1) Transaction이 꼭 필요한 로직에만 적용
•
데이터의 부정합을 일으킬 여지가 없는 로직에는 Transaction을 적용할 필요가 없다. 예를 들어, 실시간으로 정확한 데이터가 요구되지 않는 단순 조회, 사용자의 입력 로직, 로그인 로직 등이 대표적이다. 이외에도 하나의 긴 트랜잭션으로 관리될 필요가 없는 로직의 경우에도 작은 트랜잭션으로 분할하는 것이 성능면에서 낫다.
2) 네트워크 통신 구간을 Transaction에서 제외할 것
•
네트워크 통신 구간은 특히 지연이 많이 발생하므로 트랜잭션이 적용되었을 때 오랜시간 잠금이 풀리지 않아 성능 이슈를 야기할 수 있다. 가급적이면 네트워트 통신 구간은 Transaction에서 제외하는 것이 바람직하다.
나. 인덱스와 잠금의 연관관계 파악
레코드의 인덱스를 기준으로 레코드 락을 걸기 때문에 인덱스의 ‘값의 다양성’이 낮다면 하나의 레코드 락에 여러 레코드가 불필요하게 잠길 수 있다
1) 인덱스 종류별로 적용되는 락
•
프라이머리 키와 유니크 인덱스에 의한 변경 작업에는 레코드 자체에만 락을 건다. 반면 보조 인덱스에 의한 변경 작업에는 넥스트 키 락 또는 갭 락이 걸린다. 참고로 넥스트 키 락은 레코드 락과 갭 락이 동시에 적용되는 락으로 이해할 수 있다.
•
갭 락이란 레코드 사이의 갭에 락을 걸어서 데이터의 추가를 방지하는 것이다.
2) 레코드 락의 기준은 인덱스
•
MySQL의 레코드락은 인덱스를 기준으로 걸린다. 다시 말해, 만약 동일한 인덱스를 가진 레코드가 10개가 있는 상황에서 10개의 레코드 중 하나의 레코드에 대해 레코드락이 걸렸다면, 결과적으로 10개의 레코드가 동시에 잠긴다. 레코드락의 기준은 레코드 값이 아니라 해당 레코드의 인덱스이기 때문이다. 레코드락에 따른 성능저하를 피하기 위해서라도 값의 다양성(Cardinality)을 고려하여 인덱스를 설정해야 한다.
다. 격리수준별 내부원리 이해와 활용
READ COMMITED: 트랜잭션 중 오직 Commit된 트랜잭션이 변경한 데이터만 볼 수 있다
REPEATABLE READ: Commit된 트랜잭션 중 오직 늦게 생성된 트랜잭션이 변경한 데이터만 볼 수 있다
1) DIRTY READ 방지 원리
•
긴 트랜잭션 실행 중 짧은 트랜잭션이 특정 레코드를 변경했다면, 해당 레코드의 변경 전 값이 UNDO Log에 남는다. 긴 트랜잭션은 변경된 레코드에서 데이터를 읽지 않고, UNDO Log에서 데이터를 읽는다.
2) NON REPEATABLE READ 방지 원리
•
긴 트랜잭션을 실행하면서 조회한 레코드가 ‘중간에 실행된 트랜잭션’이 Commit됨에 따라 변경된다면, 해당 레코드의 언두 로그에는 ‘중간에 실행된 트랜잭션’의 아이디가 남는다. 긴 트랜잭션의 종료 전에 변경된 레코드의 값을 다시 한 번 조회한다고 할 때, ‘중간에 실행된 트랜잭션’이 변경한 레코드의 값을 읽지 않고 언두 로그에 저장된 값을 읽는다. 이 때, 언두 로그의 값 중에 트랜잭션 아이디가 긴 트랜잭션의 아이디 보다 작은 언두 로그의 값을 읽는다. 참고로 트랜잭션 아이디는 생성시점에 따라 순차적으로 증가한다
3) PHANTOM READ 방지 원리
•
next-key lock은 변경을 위한 검색(=select … for update 등)의 대상이 되는 레코드에 걸린다. next-key lock은 갭락과 레코드락을 합친 형태이기 때문에 해당 잠금이 발생하면 레코드에 대한 변경이 불가하고, 레코드와 레코드 사이에 새로운 레코드가 삽입될 수 없다. 긴 트랜잭션에서 select for update가 처음 발생한 시점에서 다른 트랜잭션이 레코드를 삽입하려고 해도 잠금이 걸려서 진행될 수 없다. 따라서 긴 트랜잭션의 두번째 select for update가 발생해도 첫번째 select for update와 같은 값을 조회할 수 있다. 참고로 innodb_locks_unsafe_for_binlog의 기본 설정 상 비활성화(value=0)되어 있다. 같은 의미로, next-key lock은 기본적 설정 상 활성화되어 있다.
참고) Transaciton 격리 수준 정의
트랜잭션의 격리 수준이란 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션에서 변경하거나 조회하는 데이터를 다른 트랜잭션이 볼 수 있게 허용할지 말지를 결정하는 것이다
- 본문 중
•
READ COMMITED: 트랜잭션 중 오직 Commit된 트랜잭션에서 변경한 데이터만 다른 트랜잭션이 볼 수 있도록 허용
→ Undo Log 활용 수준: Commit되지 않은 트랜잭션에 의해 레코드의 값이 변경되어도, 변경 이전 값을 언두로그에 남겨 둔다. 다른 트랜잭션에서는 레코드의 값을 그대로 읽지 않고 언두로그에서 해당 레코드의 이전 값을 찾아서 참조한다
•
REPEATABLE READ: Commit된 트랜잭션 중 본 트랜잭션 이전에 생성된 트랜잭션에서 변경한 데이터만 다른 트랜잭션이 볼 수 있도록 허용
→ Undo Log 활용 수준: Undo Log내 트랜잭션 아이디를 참조하여 Commit된 트랜잭션에 의해 변경된 레코드의 이전 값 중에 본 트랜잭션 이전(본 트랜잭션의 아이디 보다 작은)의 Undo Log의 값만을 참조한다. 단, Commit된 트랜잭션에 의해 변경된 레코드의 이전 값을 언두로그에서 참조하기 위해서는 순수한 select 쿼리가 아닌 트랜잭션 내의 select 쿼리여야 한다. 왜냐하면 Commit된 다른 트랜잭션에 의해 변경된 레코드의 이전 값을 읽기 위해서는 본 트랜잭션의 아이디를 바탕으로 Undo Log에서 변경된 값을 읽어와야 하기 때문이다.
•
SERIALIZABLE: 공유락 기반으로 동작하기 때문에 다른 트랜잭션이 락을 획득해야 데이터를 볼 수 있도록 허용
→ Undo Log 활용 수준: 활용하지 않음
•
READ UNCOMMITED: Commit or Rollback 여부와 관계 없이 트랜잭션에 의해 변경된 데이터를 다른 트랜잭션이 볼 수 있도록 허용
→ Undo Log 활용 수준: 활용하지 않음
→ RDBMS에서 사용되지 않는 격리 수준임
라. 기타 질문
1) MySQL에서 ‘READ COMMITED’와 ‘REPEATABLE READ’의 공통점과 차이점은?
•
공통점: Commit된 트랜잭션에서 변경된 데이터만을 다른 트랜잭션에서 볼 수 있다.
•
차이점: READ COMMITED는 트랜잭션 중 오직 Commit된 트랜잭션에서 변경한 데이터만 다른 트랜잭션이 볼 수 있도록 허용한다. REPEATABLE READ는 Commit된 트랜잭션 중 본 트랜잭션 이전에 생성된 트랜잭션에서 변경한 데이터만 다른 트랜잭션이 볼 수 있도록 허용한다. 이 차이점은 언두 로그의 활용 수준에 따라 생긴다.
2) REPEATABLE READ 격리 수준에서 NON REPEATABLE READ 부정합을 방지하기 위해 단순 조회 쿼리마저도 트랜잭션으로 실행해야 하는 이유는?
•
NON REPEATABLE READ 부정합을 방지하기 위해서는 Commit된 트랜잭션 중에서 오직 늦게 생성된 트랜잭션에 의해 변경된 데이터만 읽어야 한다. 이를 위해서는 본 트랜잭션 아이디를 바탕으로 Undo log에서 본 트랜잭션 아이디 보다 낮은 트랜잭션 아이디의 값을 읽어야 한다. 이러한 이유로 단순 조회 쿼리마저도 트랜잭션으로 감싸야한다. 참고로 NON REPEATABLE READ 부정합을 신경쓰지 않아도 되는 단순 조회 기능의 경우, 트랜잭션으로 실행하지 않아도 된다.
3) MySQL 8.0에서 innodb_locks_unsafe_for_binlog 시스템 변수가 제거되었다. next-key lock을 활성화 또는 비활성화하기 위해서는 어떻게 해야 하는가?
•
MySQL 8.0에서 next-key lock는 기본 설정 상 활성화 되어 있다. 공식문서에 따르면 next-key lock을 비활성하기 위해서는 격리 수준 자체를 READ COMMITED로 낮추면 된다.
4. 인덱스의 최적 활용법은?
가. 클러스터링 인덱스의 최적 활용
1) 클러스터링 인덱스를 조건으로 조회 권장
•
클러스터링 인덱스가 설정된 테이블을 대상으로 데이터 조회 시 아래와 같은 방식으로 인덱스를 타서 데이터를 조회한다. 참고로 InnoDB 엔진은 특정 컬럼을 Primary Key로 설정하면 이를 기준으로 클러스터링 인덱스를 구축한다.
•
루트 페이지(클러스터링 인덱스) → 브랜치 페이지(클러스터링 인덱스) → 리프 페이지(==데이터 페이지)
2) 보조 인덱스를 조건으로 조회 권장 X
•
클러스터링 인덱스가 설정된 테이블을 대상으로 보조 인덱스만으로 인덱스가 아닌 컬럼을 조회한다면 다음과 같은 경로 조회하므로 성능이 느려진다.
•
루트 페이지(보조 인덱스) → 브랜치 페이지(보조 인덱스) → 리프 페이지(보조 인덱스) → 루트 페이지(클러스터링 인덱스) → 브랜치 페이지(클러스터링 인덱스) → 리프 페이지(==데이터 페이지)
3) Primary Key는 필수 설정 대상
•
InnoDB에서 Primary Key는 필수 설정 대상이다. 사용자가 설정하지 않을 경우, InnoDB가 내부적으로 자동 생성한다. 자동 생성된 키는 사용자가 활용할 수 없으므로 Primary Key는 auto-increment 방식으로라도 사용자가 필수 설정하여 활용하는 것이 바람직하다.
나. 커버링 인덱스의 최적 활용
1) 추후 작업
다. Slow Query에 대한 효율적인 분석 방법은?
1) Slow Query란
•
정상적으로 실행된 Query 중 long_query_time 시스템 변수에 할당된 값 보다 긴 시간 실행된 Query를 말한다.
2) pt-query-digest 스크립트 활용
•
Percona의 해당 스크립트 활용하여 슬로우 쿼리에 대한 분석정보를 확인할 수 있다. 스크립트 실행 명령은 아래와 같다.
•
실행명령: pt-query-digest --type=’slowlog’ mysql-slow.log > parsed_mysql-slog.log
라. InnoDB 엔진은 조회 명령 시 Disk에서 데이터를 어떻게 읽는가?
1) Random I/O vs Sequential I/O
•
조회 명령에 대해 옵티마이저가 어떤 실행계획을 수립하느냐에 따라 Disk에서 데이터를 읽어오는 방법이 달라진다. 실행계획 상 인덱스를 활용하여 Range Scan으로 조회해야 한다면 Disk에서 Random I/O 방식으로 데이터를 읽는다. 인덱스를 활용하지 않는 Full Scan으로 조회해야 한다면 Disk에서 Sequential I/O 방식으로 데이터를 읽는다.
•
DB 관점에서 성능을 높인다는 것은 Random I/O의 빈도를 줄이는 것이 핵심이다. 인덱스가 잘 설계되어 있다는 가정 하에 대부분의 조회 기능은 인덱스를 활용하므로 Sequential I/O 보다는 Random I/O 방식으로 Disk에서 데이터를 읽는다. 참고로 Sequential I/O는 한 번의 시스템 콜로 복수의 페이지를 동시 접근하지만, Random I/O는 각각의 페이지마다 별도의 시스템 콜이 필요하다. 참고로 인덱스가 설정되어 있다라도 너무 빈번한 Random I/O가 발생한다면 옵티마이저가 Full Table Scan 방식으로 Disk를 조회하도록 실행계획을 세운다.
2) Buffer Full
•
조회 대상 레코드의 데이터 페이지가 Buffer Full에 있다면 Disk를 거치지 않고 Buffer full에서 관련 페이지를 읽는다.
Reference
•
백은빈, 이성욱, Real MySQL 8.0
•
Caching SHA-2 Authentication Plugin
•
SCRAM or Caching SHA-2 Authentication
•
Next Key Lock 설정
◦
innodb_locks_unsafe_for_binlog의 deprecated, https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog
◦
MySQL 8.0에서 innodb_locks_unsafe_for_binlog 변수 제거의 의미, https://hoing.io/archives/4713