4. 아키텍처

MySQL 구조

MySQL 엔진

Client로 부터 접속 및 쿼리 요청을 처리하는 Connection Handler, SQL 파서, 전처리, 옵티마이저

스토리지 엔진

MySQL로 요청된 SQL 문장을 분석, 최적화 및 디스크에 저장하거나 읽어오는 부분을 전담하고 있다.
MySQL엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다.

Handler API

MySQL 엔진에서 스토리지 엔진으로 읽기 or 쓰기를 요청을 핸들러 요청이라 한다.
SHOW GLOBAL STATUS LIKE 'Handler%'; 명령어로 작업 이력을 확인할 수 있다.

MySQL 스레딩 구조

MySQL은 프로세스 기반이 아니라 스레드 기반으로 작동하며, 크게 Foreground threads, Backround threads로 구분된다.

Foreground threads (= Client threads)

포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 주로 각 클라이언트 사용자가 요청하느 쿼리 문장을 처리한다. 클라이언트가 커넥션을 종료하면 해당 스레드는 스레드 캐시로 되돌아간다. 이때 스레드 캐시에 일정 개수의 스레드가 있드면 해당 스레드는 종료처리 된다.
└thread_cache_size 옵션을 통해 일정 개수의 스레드 캐시 유지

포그라운드 스레드는 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오며 없는 경우 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리한다.

Write	MyISAM	MySQL
버퍼	포그라운드	포그라운드
캐시	포그라운드	포그라운드
디스크	포그라운드	백그라운드

Background threads

MyISAM은 해당 사항이 없고, InnoDB는 여러 가지 작업이 처리된다.

• 인서트 버퍼를 병합하는 스레드
• 로그를 디스크로 기록하는 스레드
• InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
  innodb_write_io_threads
• 데이터를 버퍼로 읽어 오는 스레드
  innodb_read_io_threads
• 잠금이나 데드락을 모니터링하는 스레드

threads 개수 설정은 일반 내장 디스크 인 경우 2~4개, DAS나 SAN인 경우 최적으로 사용할 수 있을만큼 충분히 설정하는 것이 좋다

일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재 되어있다.
InnoDB에서는 INSERT, UPDATE, DELETE 쿼리로 데이터가 변경되는 경우 디스크에 저장될 때까지 기다리지 않아도 된다.

메모리 할당 및 사용 구조

글로벌 메모리는 MySQL이 시작되면서 OS로 부터 할당 된다.

글로벌 메모리

클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당된다. 2개 이상의 메모리 공간을 할당 받을 수 있지만 스레드의 수와 무관하다. 생성된 메모리 공간은 모든 스레드에 의해 공유된다.

• 테이블 캐시
• InnoDB 버퍼 풀
• InnoDB 어댑티브 해시 인덱스
• InnoDB 리두 로그 버퍼

로컬 메모리(=세션 메모리)

클라이언트 스레드가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역이라고 한다. 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역이다. 스레드별로 독립적으로 할당되며 공유되어 사용되지 않는다.
메모리 영역의 크기는 주의해서 설정해야지만, 최악의 경우 메모리 부족으로 서버가 멈출 수도 있다. 한 가지 중요한 특징은 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않는 경우 할당조차 하지 않는다

• 정렬 버퍼
• 조인 버퍼
• 바이너리 로그 캐시
• 네트워크 버퍼

플러그인 스토리지 엔진 모델

MySQL 독특한 구조 중 대표적인 것이 바로 플러그인 모델이다. 스토리지 엔진, 검색어 파서, 사용자 인증 등등
쿼리가 실행되는 대부분의 작업이 MySQL 엔진에서 처리되고, 데이터 읽기/쓰기만 스토리지 엔진에 의해 처리된다.

Group By, Order By 등 복잡한 처리는 MySQL 엔진-쿼리 실행기에서 처리된다.

MySQL 서버에 포함되지 않은 스토리지 엔진을 사용할려면 서버를 재 빌드해야 한다.
SHOW PLUGINS; 명령어로 스토리지 엔진뿐 아니라 다른 플로그인도 확인할 수 있다.

컴포넌트

플러그인의 단점을 보완하기 위해 컴포넌트 등장

• 플러그인은 오직 MySQL 서버와 인터페이스할 수 있고, 플러그인끼리는 통신할 수 없음
• 플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않음
• 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어려움

쿼리 실행 구조

• 쿼리 파서
  쿼리 문장을 토큰으로 분리해 트리 형태의 구조롤 만들어 내는 작업. 기본 문법 오류는 이 과정에서 발견
• 전처리기
  트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인. 테이블 명, 컬럼 명 내장 함수 등 존재 여부와 접근 권한 등을 확인
• 옵티마이저
  쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정
• 실행 엔진
  만들어진 계획대로 각 핸들어에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할

스레드 풀

엔터프라이즈 에디션은 기능을 제공하지만, 커뮤니티 에디션은 지원을 하지 않는다. 여기서는 Percona Server에서 제공하는 스레드 풀 기능을 살펴본다.
스레드 풀은 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많다 하더라도 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이 목적이다.

InnoDB 스토리지 엔젠 아키텍처

프라이머리 키에 의한 클러스터링

InnoDB의 테이블은 기본적으로 PK Key를 기준으로 클러스터링이 되어 저장된다. PK 순서대로 디스크에 저장된다는것 그로인해 Secondary key의 leaf node value 값은 PK 값을 가지고 있다.
└ PK값이 변경되면 Index 레코드 주소 값이 변경됨으로 순서대로 저장되다 보니 레인지 스캔이 빠르다.

외래 키 지원

MyISAM, MEMORY에서는 지원하지 않고 InnoDB에서는 지원하고 있다.

MVCC (Multi Version Concurrency Control)

레코드 레벨의 트랜잭션을 지원는 DBMS가 제공하는 기능이며 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 데 있다. 여기서 Multi Version은 하나의 레코드에 대해 여러 개의 버전이 동시에 관리되고 있다는 것
InnoDB는 Undo log를 이용해 이 기능을 구현한다.

레코드 값을 UPDATE 했을 때 기존 값을 가지고 있던 버퍼 풀의 데이터는 바로 값이 수정되며, 이전 값은 언두 로그로 이동되며 이전 값을 가지고 있다. (ACID를 보장하기에 일반적으로 버퍼 풀 데이터는 데이터 파일과 동일한 상태 이다)

여기서 DB의 isolation 설정에 따라, 각각의 Transaction에서 commit 후 데이터인지, 수정된 데이터를 바로 확인할 수 있는지가 버퍼 풀에서 값을 가저가냐, 언두 로그에서 값을 가져가는지 결정되는것
└ 자세한 내용은 이후 트랜잭션에 설명

이후, commit을 하면 영구적인 데이터로 만들고, Rollback을 하는 경우 언두 영역을 버퍼 풀로 복구하고 언두 영역 데이터를 삭제한다. 언두 영역을 필요로 하는 트랜잭션이 더는 없을 때 비로소 삭제된다.

자동 데드락 감지

InnoDB는 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프 형태로 관리한다. 주기적으로 검사해 교착 상태에 빠진 트랜잭션을 찾아서 강제 종료한다. 종료하는 기준은 언두 로그의 양이며 더 적게 가진 트랜잭션이 롤백 대상이 된다
innodb_table_locks를 활성화 하면 레코드 잠금뿐만 아니라 테이블 레벨의 잠금까지 감지할 수 있다.
innodb_deadlock_detect를 통해 OFF가 가능하며, innodb_lock_wait_timeout 을 통해 일정 시간 지나면 자동 종료도 가능하다

구글은 PK 조회 변경이 높은 서비스가 많았고 또한 많은 트랜잭션이 동시에 실행하기에 데드락 감지가 성능을 저하시킨다는 것을 알아냈다. 감지 스레드 활성화 비활성화가 기능을 오라클에 요청해서 추가되었다.

자동화된 장애 복구

MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된 데이터 페이지 등에 대한 이련의 복구 작업이 자동으로 진행된다.
innodb_force_recovery 설정값에 따라 InnoDB 스토리지 엔진이 데이터 파일이나 로그 파일의 손상 여부 검사 과정을 선별적으로 진행할 수 있다. 1~6까지 옵션값이 있으며 자세한 건 검색

InnoDB 버퍼 풀

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

버퍼 풀의 크기 설정

피크치가 큰 레코드 버퍼는 설정할 수 없으며, 커넥션 개수와 각 커넥션에서 읽고 쓰는 테이블의 개수에 따라서 결정된다. 하지만 이 버퍼 공간은 동적으로 해체되기도 하므로 정확히 필요한 메모리 공간의 크기를 계산할 수 없다.
MySQL 5.7버전 이후 부터 동적으로 조절할 수 있음으로 적절히 작은 값에서 증가시키는 것이 최적이다

Memory 8G 미만이면 50%에서 올려가며 최적점을 찾고
Memory 50G 이상이라면 15~30G 제외한 나머지를 할당하자

innodb_buffer_pool_size 로 설정할 수 있으며 값을 늘리는 것은 영향도가 크기 않지만, 반대로 줄이는 것은 가능하면 하지 않도록 하자
innodb_buffer_pool_instances를 이용해 버퍼 풀을 여러개로 분리해서 관리할 수 있다.
메모리가 크다면 인스턴스 당 5G 정도가 되게 인스턴스 개수를 설정하는 것이 좋다.

버퍼 풀의 구조

InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장한다. 버퍼 풀의 페이지 크기 조각을 관리하기 위해 InnoDB 스토리지 엔진은 크게 LRU(Least Recently Used) 리스트와 플러시 리스트, 프리 리스트라는 3가지 자료구조를 사용한다.

LRU 리스트에서 페이지가 자주 사용된다면 MRU(Most Recentlry Used) 영역에서 살아남게 되고, 반대로는 LRU의 끝으로 밀려나 버퍼 풀에서 제거될 것이다.

버퍼 풀과 리두 로그

InnoDB의 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정하면 성능이 빨라진다
버퍼 풀의 쓰기 버퍼링 기능까지 향상 시킬려면 버퍼 풀과 리두 로그와의 관계를 이해해야 한다.

버퍼 풀에서 INSERT, UPDATE, DELETE 명령으로 변경된 데이터를 가진 더티 페이지는 언젠가 디스크로 기록돼야 한다.
리두 로그 공간에는 디스크로 동기화 해야하는 값을 가지고 있다. 리두 로그는 순환 구조로 비어있는 공간에 값을 기록한다.
매번 기록 시 계속 증가된 값을 가지게 되는데 이를 LSN(Log Sequence Number)이라고 한다.
InnoDB 스토리지 엔진은 주기적으로 체크포인트 이벤트를 발생 시켜 체크포인트의 LSN보다 작은 값은 디스크로 동기화 시킨다.

그렇다보니 버퍼 풀의 사이즈와 리두 로그의 사이즈의 연관 관계를 생각해서 메모리 할당을 하는 게 좋다.

버퍼 풀 플러시

아직 디스크로 기록되지 않은 더티 페이지들을 성능상의 악영향 없이 디스크에 동기화 하기 위해 다음과 같이 2개의 플러시 기능을 백그라운드로 실행한다.

• 플러시 리스트 플러시
  - innodb_page_cleaners
  - innodb_max_dirty_pages_pct_lwm
  - innodb_max_drity_pages_pct
  - innodb_io_capacity
  - innodb_io_capacity_max
  - innodb_flush_neighbors
  - innodb_adaptive_flushing
  - innodb_adaptive_flushing_lwm
• LRU 리스트 플러시
  - innodb_buffer_pool_instances * innodb_lru_scan_depth 스캔

버퍼 풀 상태 백업 및 복구

버퍼 풀은 쿼리의 성능에 매우 밀접하게 되어있다. 서버를 재기동시 평상시보다 1/10도 안 되는 경우가 대부분이다. 디스크의 데이터가 버퍼 풀에 적재돼 있는 상태를 워밍업이라고 표현

-- 백업
SET GLOBAL innodb_buffer_pool_dump_now=ON;
-- 복구
SET GLOBAL innodb_buffer_pool_load_now_ON;

백업시 데이터 디렉토리에 ib_buffer_pool이라는 이름의 파일로 생성된다.
백업은 매우 빨리 완료되지만, 다시 로드하는 과정은 상당한 시간이 걸릴 수 있다. 각 테이블의 데이터 페이지를 다시 디스크에서 읽어와야 하기 때문이다.
innodb_buffer_pool_dump_at_shutdown innodb_buffer_pool_load_at_startup 설정을 통해 자동화가 가능하다.
매번 서버를 내리기 직전에 백업을 할 필요는 없다. 로드 시 실제 존재하지 않는 페이지인 경우 무시하고 로드되기 때문이다.

언두로그

InnoDB 스토리지 엔진은 트랜잭션과 격리 수준을 보장하기 위해 DML로 변경되기 이전 버전의 데이터를 별도로 백업한다. 이렇게 백업된 데이터를 언두 로그 라고 한다.

언두 로그 모니터링

많은 활성화 상태의 트랜잭션이 장시간 유지된다면, 그 만큼 언두 로그에 많은 데이터 량이 쌓이게 되고 그렇다면 다른 트랙잭션에서는 확인해야하는 데이터량이 많아지고 성능이 하락된다.
SHOW ENGINE INNODB STATUS /G
위 명령어를 통해 서버별로 안정적인 시점의 언두 로그를 확인해 이를 기준으로 급증 여부를 모니터링하는 것이 좋다.

언두 테이블스페이스 관리

MySQL 8.0.14 부터 언두 로그는 항상 시스템 테이블스페이스 외부의 별도 로그 파일에 기록하고 있다.

하나의 언두 테이블 스페이스는 1개 이상 128개 이하의 롤백 세그먼트를 가지며, 롤백 세그먼트는 1개 이상의 언두 슬롯을 가진다.
페이지 크기가 기본 값인 16KB인 경우 대략 131072개의 정도의 트랙잰션이 동시에 처리 가능하다.

CREATE UNDO TABLESPACE or DROP TABLESPACE 명령어로 동적으로 추가 및 삭제가 가능하다.
innodb_undo_log_truncate 설정을 통해 과도하게 할당된 공간을 처리할 수 있다.

체인지 버퍼

레코드가 INSERT or UPDATE 시 인덱스를 업데이트하는 작업이 필요하다. 해당 인덱스가 버퍼 풀에 존재하면 바로 수정을 하지만 그게 아니라면 체인지 버퍼에 저장을 한다.
└innodb_change_buffering 통해 동작을 설정할 수 있다.
└중복 여부를 체크해야 하는 유니크 인덱스는 사용할 수 없다.
체인지 버퍼는 이후 백그라운드 스레드에 의해 병합되는데, 이 스레드를 체인지 버퍼 머지 스레드라고 한다. 기본적으로 InnoDB 버퍼 풀로 설정된 메모리 공간의 25%까지 사용할 수 있게 설정되어있다.
index 수정이 많은 경우 innodb_change_buffer_max_size 비율을 수정하면 메모리 비율이 늘어난다.

리두 로그 및 로그 버퍼

리두 로그는 H/W or S/W 등 문제점으로 인해 MySQL 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안정장치다. 대부분 DB 서버는 데이터 변경 내용을 로그로 먼저 기록한다.
대부분 DB가 읽기 중심의 자료 구조임으로 파일 쓰기는 상대적 큰 비용을 요구한다. 이러한 성능 저하를 막기위해 리두 로그를 가지고 있다.

리두 로그는 트랜잭션이 커밋되면 즉시 디스크로 기록되도록 설정하는 것을 권장한다.
하지만 커밋될 때 마다 디스크에 기록하는 작업은 많은 부하를 유발한다. 그래서 innodbb_flush_log_at_trx_commit 이라는 설정을 통해 어느 주기로 동기화할지 선택이 가능하다.

로그 버퍼는 변경된 내용을 한번에 모았다가 디스크에 기록할 수 있는 크기를 말한다.
로그 버퍼는 기본값인 16MB 수준에서 설정하는 것이 적합하며, BLOB이나 TEXT와 같이 큰 데이터를 자주 변경한다면 더 크게 설정하는 것이 좋다.

어댑티브 해시 인덱스

사용자가 수동으로 생성하는 인덱스가 아니라, 자주 요청되는 데이터에 대해 자동으로 생성되는 인덱스이다.
innodb_adaptive_hash_index 로 통해 활성화하거나 비활성화 할 수 있다.

많은 스레드가 데이터를 찾고 CPU는 엄청난 프로세스 스케줄링을 하게 되면 쿼리의 성능이 떨어지게 되니, B-TREE의 검색 시간을 줄여주기 위해 도입되었다.

• 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우
• 동등 조건 검색이 많은 경우
• 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우

위 조건은 성능 향상에 도움이 되지만

• 디스크 읽기가 많은 경우
• 특정 패턴의 쿼리가 많은 경우 (join or like)
• 매우 큰 데이터를 가진 테이블의 레코드를 폭 넓게 읽는 경우

위 조건에서는 성능 향상에 크게 도움이 되지 않을 것 이다.
SHOW ENGINE INNODB STATUS /G 를 통해 해시 인덱스의 활용을 확인할 수 있고 해당 결과 값을 통해 득인지 실인지 판단하면 될 것 같다.

MySQL 로그 파일

Error 로그 파일

my.cnf 에서 log_error로 정의된 디렉토리 경로에 생성된다.
설정이 없는 경우는 데이터 디렉터리에 .err 확장자로 생성된다.

General 로그 파일

시간 단위로 실행됐던 쿼리의 내용이 모두 기록된다.
쿼리 로그를 파일이 아닌 테이블에 저장하도록 설정할 수 있음으로 SQL을 조회해서 확인해야한다.
SHOW GLOBAL VARIABLES LIKE 'general_log_file';

Slow 쿼리 로그

long_query_time 에서 설정한 시간보다 오래 걸리는 쿼리를 기록한다. 여기서 정상적으로 싱행이 완료된 쿼리만 기록이 된다.
log_output 설정을 통해 테이블 or 파일로 받을 수 있다.

Percona Tookit을 활용하여 추출된 파일에 대해 통계를 확인할 수 있다.