1. 아키텍처

Sixhustle·2021년 8월 12일

Database

RealMySQL

목록 보기

1/5

3.1 아키텍처

3.1.1 MySQL의 전체 구조

MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 구분됩니다.

MySQL 엔진

커넥션 핸들러 : 클라이언트로부터의 접속 및 쿼리 요청을 처리
SQL 파서 및 전처리기
옵티마이저 : 쿼리의 최적화된 실행을 지원
성능 향상을 위한 버퍼 풀과 같은 보조 저장소 기능이 포함(InnoDB)

스토리지 엔진

디스크 스토리지에 데이터를 저장하거나 읽어오는 부분을 담당
MySQL 엔진과 다르게 스토리지 엔진은 여러 개를 동시에 사용

핸들러 API

MySQL 엔진의 쿼리 실행기에서 데이터를 저장하거나 읽어야 할 때, 스토리지 엔진에게 요청하는 것을 말함

3.1.2 MySQL 스레딩 구조

MySQL 서버는 스레드 기반으로 작동하며, 포그라운드(Foregroud) 스레드와 백그라운드(Background) 스레드로 구분합니다.

포그라운드 스레드(클라이언트 스레드)

MySQL 서버에 접속된 클라이언트의 수만큼 존재하고, 사용자가 요청하는 쿼리 문장을 처리하는 것이 임무
데이터를 데이터 퍼버 또는 캐시 > 디스크의 데이터나 인덱스 파일순으로 읽어와 작업을 처리
InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리, 나머지는 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리

백그라운드 스레드 (InnoDB의 경우)

인서트 버퍼를 병합하는 스레드
로그를 디스크로 기록하는 스레드
버퍼 풀의 데이터를 디스크에 기록하는 스레드
데이터를 버퍼로 읽어들이는 스레드
여러 잠금과 데드락을 모니터링 하는 스레드
총괄하는 스레드

SQL 처리 도중 데이터의 쓰기 작업은 지연되어 처리될 수 있지만, 읽기 작업은 절대 지연될 수 없습니다.
따라서, 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재돼 있으며 InnoDB도 동일한 방식으로 처리합니다.

3.1.3 메모리 할당 및 사용 구조

모든 스레드가 공유하는 영역을 글로벌 메모리, 아닌 곳을 로컬(세션) 메모리 영역이라고 합니다.

글로벌 메모리 영역

MySQL 서버가 시작되면서 무조건 운영체제로부터 공간 할당
하나의 메모리 공간만 할당
생성된 글로벌 영역이 N개라 하더라고 모든 스레드에 의해 공유

로컬 메모리 영역

클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역
클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않음

3.1.5 쿼리 실행 구조

파서

쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위)으로 분리해 트리 형태의 구조로 만드는 작업
기본 문법 오류를 발견

전처리기

객체의 존재 여부와 접근 권한 등을 확인

옵티마이저

쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지 결정하는 역할

실행엔진

계획대로 각 핸드러를 연결하는 역할

핸들러

실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 읽어오는 역할
InnoDB 테이블을 조작하는 경우에는 핸들러가 InnoDB 스토리지 엔진

3.1.6 복제

일반적으로 데이터를 변경하는 Primary서버와 데이터를 읽기만 하는 Secondary서버로 나뉜다.

Primary(master)

바이너리 로그가 활성화되면 어떤 MySQL 서버든 Primary(master)가 될 수 있다.
데이터를 조작하는 DML과 스키마를 변경하는 DDL 쿼리를 바이너리 로그에 기록한다.
Secondary에서 변경 내역을 요청하면 바이너리 로그를 읽어 넘긴다.

Secondary(slave)

데이터(바이너리 로그)를 받아 올 primary 장비의 정보(IP와 포트 정보 밑 접속 계정)를 가지고 있는 경우
변경 내역을 릴레이 로그에 기록하고, 스레드가 릴레이 로그의 변경 내역을 재실행함으로써 마스터와 동일한 상태 유지

바이너리 로그 방식

1. Statement 포맷 방식

실행되는 쿼리 문장을 기록하는 방식
Repeatable-read 이상의 트랜잭션 격리 수준을 사용해야 함
네트워크 트래픽을 많이 유발하지 않음
InnoDB 테이블에서는 레코드 간의 간격을 잠그는 갭락이나 넥스트 키 락이 필요

2. Row 포맷 방식

변경된 레코드 값을 기록하는 방식
Read-committed 트랜잭션 격리 수준에서도 작동
InnoDB 테이블에서 잠금의 경합은 줄어듬

복제 주의 사항

Secondary는 하나의 Primary만 설정 가능
Primary/Secondary의 데이터 동기화를 위해 Secondary는 읽기 전용으로 설정
Secondary장비는 Primary와 동일한 사양이 적합
복제가 불필요한 경우에는 바이너리 로그 중지

3.1.7 쿼리 캐시

Key-Map의 구조로 쿼리의 결과를 메모리에 캐시해 두는 기능

쿼리 캐시의 결과를 내려 보내쥑 전에 반드시 아래의 확인 절차를 거쳐야 한다.

1. 요청된 쿼리 문장이 쿼리 캐시에 존재하는가?

요청된 쿼리 문장 자체가 동일한지 여부를 비교
공백, 탭, 대소문자까지 완전히 동일해야 같은 쿼리로 인식

2. 해당 사용자가 그 결과를 볼 수 있는 권한을 가지고 있는가?

3. 트랜잭션 내에서 실행된 쿼리인 경우 가시 범위 내에 있는 결과인가?

InnoDB는 순차적으로 증가하는 6바이트 숫자값의 트랜잭션 ID를 가짐
자신의 ID보다 큰 트랜잭션에서 변경한 작업 내역이나 쿼리 결과는 참조할 수 없다. (가시 범위)

4. DATE 등과 같이 호출 시점에 따라 결과가 달라지는 요소가 있는가?

5. PreparedStatement 변수가 결과에 영향을 미치지 않는가?

Bind변수가 사용된 쿼리의 경우 쿼리 문장 자체에 “?”가 사용되기 때문에 쿼리 캐시를 찾을 수 없음
실제 MySQL 서버에서는 PreparedStatement 형태로 실행되지 않는다.

6. 캐시가 만들어지고 난 이후 해당 데이터가 다른 사용자에 의해 변경되지 않았는가?

쿼리 캐시에 저장된 이후 데이터가 변경되면 데이터 제거(무효화)해야 한다.
무효화는 레코드 단위가 아닌 테이블 단위로 이뤄진다.
쿼리 캐시는 절대 여러 스레드에서 동시에 변경할 수 없기 때문에 무효화 작업을 고려하여 캐시의 크기를 32MB ~ 64MB 잡는 것이 좋다.

7. 쿼리에 의해 만들어진 결과가 캐시하기에 너무 크지 않은가?

만들어진 결과의 크기가 작을수록 쿼리 캐시를 더 효율적으로 사용할 수 있음
group by, distinct, count와 같은 집합 함수의 결과가 쿼리 캐시를 사용하기에 적합

8. 그 밖에 쿼리 캐시를 사용하지 못하게 만드는 요소가 있는가?

임시 테이블
사용자 변수의 사용
칼럼 기반의 권한 설정 등등

쿼리 Hit ratio

“show global status” 명령어를 이용해 쿼리 캐시의 사용률을 알 수 있다.

QCache_hist : 쿼리 캐시로 처리된 Select 쿼리의 수
Com_select : 쿼리 캐시에서 결과를 찾지 못해 MySQL 서버가 쿼리를 실행한 횟수

쿼리 캐시 히트율(%) = Qcache_hits / (Qcache_hits + Com_select) * 100

쿼리 캐시의 히트율이 20% 이상이면 일반적으로 쿼리 캐시를 사용하는 것이 좋다.
쿼리 캐시를 사용하지 않는다면, 아래처럼 파라미터를 설정하여 쿼리 캐시에 사용되는 메모리 낭비, 오버헤드를 줄이는 것이 좋다.

query_cache_size = 0
query_cache_type = 0

3.2 InnoDB 스토리지 엔진 아키텍쳐

InnoDB는 거의 유일한 레코드 기반의 잠금을 제공하고, 높은 동시성 처리가 가능하며 안정적이고 성능이 뛰어나다.

1. Primary Key에 의한 클러스터링

InnoDB의 모든 테이블은 기본적으로 Primary Key를 기준으로 클러스터링 되어 저장된다.
Primary Key값의 순서대로 디스크에 저장된다.
Range scan은 상당히 빨리 처리된다.

2. 잠금이 필요 없는 일관된 읽기 (Non-locking consistent read)

InnoDB는 MVCC(Multi Version Concurrency Control) 기술을 이용해 락을 걸지 않고 읽기를 수행
다른 트랜잭션이 가지고 있는 락을 기다리지 않아도 읽기 가능(Serializable 격리 수준 제외)

3. 외래 키 지원

외래 키는 InnoDB에서 지원하는 기능으로 MyISAM이나 MEMORY 테이블에서는 사용 불가

4. 자동 데드락 감지

그래프 기반의 데드락 체크 방식으로 데드락 발생시 바로 감지
트랜잭션 중 rollback이 가장 용이한 트랜잭션을 자동적으로 강제 종료

5. 자동화된 장애 복구

MySQL 서버가 시작될 때, 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된 데이터 페이지 등에 대한 복구 작업이 자동으로 진행

6. 오라클의 아키텍처 적용

3.2.2 InnoDB 버퍼 풀

디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간.
쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할

InnoDB 버퍼 풀은 아직 디스크에 기록되지 않은 변경된 데이터를 가지고 있음(Dirty Page)
Dirty Page는 InnoDB에서 주기적으로 Write 스레드가 필요한 만큼의 Dirty Page만 디스크에 기록함

3.2.3 Undo 로그

Update/Delete와 같은 데이터를 변경했을 때 변경되기 전의 데이터를 보관하는 곳
트랜잭션 롤백 대비용과 격리 수준을 유지하기 위한 용도로 사용

Mysql> UPDATE member SET name=‘홍길동’ WHERE mebmer_id = ‘1’;

위의 sql이 실행되면, 아래의 순서대로 적용도ㅣㄴ다.

트랜잭션을 commit하지 않아도 실제 데이터 파일(데이터/인덱스 버퍼) 내용은 “홍길동”으로 변경
Undo 영역에는 변경되기 전의 값이 저장
Commit되면 현재 상태가 그대로 유지, Rollback하면 Undo 영역의 데이터를 다시 데이터 파일(데이터/인덱스 버퍼)로 복구

3.2.4 인서트 버퍼(Insert Buffer)

InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행
디스크로부터 읽어와서 업데이트해야 한다면, 즉시 실행하지 않고 임시공간(Insert Buffer)에 저장해두고 사용자에게 결과를 반환하는 형태로 성능을 향상 시킴

레코드가 insert/update 될 때는 데이터 파일을 변경할 뿐 아니라 인덱스를 업데이트 하는 작업도 필요
인덱스를 업데이트하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱스가 많다면 상당히 많은 자원을 소모
—> 인덱스를 업데이트하는 작업은 왜 랜덤하게 디스크를 읽는 작업이 필요?
중복 여부를 체크해야 하는 유니크 인덱스는 Insert Buffer를 사용할 수 없음

3.2.5 리두(Redo) 로그 및 로그 버퍼

변경된 내용을 순차적으로 디스크에 기록하는 로그 파일

commit하면 ACID를 보장하기 위해 즉시 변경된 데이터의 내용을 데이터 파일로 기록 필요
위의 작업은 많은 자원이 필요하기 때문에 변경된 데이터를 InnoDB 버퍼 풀에 버퍼링 해둔다.
리두 로그 덕분에 DBMS 데이터는 버퍼링을 통해 한꺼번에 디스크에 변경된 내용을 순차적으로 처리할 수 있고, 성능 향상을 기대할 수 있음
로그 버퍼의 크기는 일반적으로 1~8MB 수준에서 설정하는 것이 적합

ACID

Atomic : 트랜잭션은 원자성 작업이어야 함을 의미한다.
Consistent : 일관성
Isolated : 격리성
Durable : 한 번 저장된 데이터는 지속적으로 유지돼야 한다.
일관성과 격리성은 서로 다른 두 개의 트랜잭션에서 동일 데이터를 조회하고 변경하는 경우에도 상호 간섭이 없어야 한다는 것을 의미

MVCC(Multi Version Concurrency Control)

MVCC의 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 데 있다.
하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다 는 의미이다.

아래는 READ_COMMITED 테이블의 데이터 변경 처리 과정을 나타낸다.

UPDATE 문장이 실행되면 커밋 여부와 관계 없이, InnoDB 버퍼 풀은 새로운 값 “경기”로 업데이트
Commit/Rollback이 되지 않은 상태에서 다른 사용자가 작업 중인 레코드를 조회한다면? MySQL 설정된 겨ㄱ리 수준에 따라 다르다.
2-1. READ_UNCOMMITTED : InnoDB 버퍼 풀이나 데이터 파일로부터 변경되지 않은 데이터를 읽어서 반환
2-2. READ_COMMITED 이상 : 아직 커밋되지 않았기 때문에 언두 영역의 데이터를 반환

3.2.7 잠금 없는 일관된 읽기

Serializable 격리 수준이 아니라면 insert와 연결되지 않은 순수한 읽기(select) 작업은 다른 트랜잭션의 변경 작업과 관계없이 바로 실행된다.
변경 트랜잭션이 다른 사용자의 select을 방해하지 않으며 이를 위해 InnoDB에서는 언두 로그를 사용한다.

References

개발자와 DBA를 위한 Real MySQL

Sixhustle

다음 포스트