4장. 아키텍처

[jjy0918]

4.1 MySQL 엔진 아키텍처

4.1.1 MySQL의 전체 구조

• MySQL은 C API, JDBC, ODBC, .NET 등의 표준 드라이버를 제공하여 C/C++, JAVA 등의 모든 언어에서 사용할 수 있도록 해준다.
• MySQL 서버는 MySQL 엔진과 스토리지 엔진으로 구분할 수 있다.

4.1.1.1 MySQL 엔진

• MySQL 엔진은 클라이언트로부터 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리기, 쿼리 옵티마이저가 중심을 이룬다.
• MySQL은 ANSI SQL을 지원하기 때문에 타 DBMS와 호환되어 실행될 수 있다.

4.1.1.2 스토리지 엔진

• 스토리지 엔진은 실제 데이터를 디스크 스토리지에 저장하거나 읽어오는 부분을 담당한다.
• MySQL 엔진은 서버 당 하나지만, 스토리지 엔진은 여러 개 동시에 사용할 수 있다.
• 특정 테이블이 사용할 스토리지 엔진을 지정하여 처리할 수 있다.
  • CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;
• 각 스토리지 엔진은 성능 향상을 위해 캐시 키(MyISAM 스토리지 엔진)나 InnoDB 버퍼 풀과 같은 기능을 내장하고 있다.

4.1.1.3 핸들러 API

• MySQL 엔진의 쿼리 실행기에서 데이터 읽거나 쓸 때 스토리지 엔진에 요청하는데, 이러한 요청을 핸들러(Handler) 요청이라고 한다.
• 즉, MySQL 엔진과 스토리지 엔진 사이에 데이터를 주고 받을 때 핸들러 API를 사용한다.
• 핸들러 API를 통해 얼마나 많은 데이터(레코드) 작업이 있었는지 확인할 수 있다.
  • SHOW GLOBAL STATUS LIKE 'Handler%';

4.1.2 MySQL 스레딩 구조

• MySQL은 스레드 기반으로 동작하며, 프어그라운드(Foreground) 스레드와 백그라운드(Background) 스레드로 구분된다.
• MySQL 서버에서 실행 중인 스레드 목록은 performance_schema 데이터베이스의 thread 테이블을 통해 확인할 수 있다.
  • SELECT thread_id, name, type, processlist_user, processlist_host FROM performance_schema.threads;
• 백그라운드 스레드의 개수는 MySQL 서버의 설정 내용에 따라 가변적일 수 있다.
• 동일한 이름의 스레드가 2개 이상 보이는 것은 서버 설정에 의해 여러 스레드가 동일 작업을 병렬로 처리하는 경우다.
• MySQL 커뮤니티 에디션에서는 전통적인 스레드 모델이다.
• MySQL 엔터프라이즈 에디션과 Percona에서는 스레드 풀 모델도 사용할 수 있다.

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

• 포어그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트 수만큼 존재한다.
• 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다.
• 클라이언트 커넥션이 종료되면, 해당 커넥션을 담당하는 스레드는 다시 스레드 캐시(Thread Cache)로 되돌아간다.
  • 스레드 캐시에 일정 개수 이상이 대기중이라면, 그냥 종료시킨다.
  • 스레드 캐시에 유지할 수 있는 최대 개수는 thread_cache_size 시스템 변수에서 설정한다.
• 포어그라운드 스레드는 데이터를 데이터 버퍼나 캐시로부터 가져온다.
  • 데이터 버퍼나 캐시에 없는 경우 직접 디스크의 데이터나 인덱스 파일로부터 읽어온다.
• MyISAM 테이블은 쓰기 작업 까지 포어그라운드 스레드가 처리한다.
• InnoDB 테이블은 데이터 버퍼나 캐시까지만 포어그라운드 스레드가 처리하고, 버퍼에서 디스크까지 기록하는 것은 백그라운드 스레드가 처리한다.

4.1.2.2 백그라운드 스레드

• InnoDB는 여러 작업을 백그라운드에서 처리한다.
  • 인서트 버퍼(Insert Buffer)를 병합하는 스레드
  • 로그를 디스크로 기록하는 스레드
  • InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
  • 데이터를 버퍼로 읽어 오는 스레드
  • 잠금이나 데드락을 모니터링하는 스레드
• MySQL 5.5 이상에서는 쓰기 스레드와 데이터 읽기 쓰레드의 개수를 2개 이상 지정할 수 있다.
  • innodb_write_io_threads, innodb_read_io_threads 시스템 변수로 설정한다.
• 읽기 작업의 경우 클라이언트 스레드에서 처리되지만, 쓰기 작업은 백그라운드로 처리하기 때문에 디스크에 따라서 최적으로 설정해주어야 한다.
  • 내장 디스크의 경우 2~4
  • DAS, SAN과 같은 스토리지에서는 디스크를 최적으로 사용할 수 있게 충분히
• InnoDB에서는 일반적으로 읽기는 즉시 실행되지만, 쓰기는 버퍼링해서 일괄 처리한다.

4.1.3 메모리 할당 및 사용 구조

• MySQL 메모리는 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다.
• 글로벌 메모리 영역은 MySQL 서버가 시작되면 운영체제로부터 할당된다.
• 글로벌 메모리 영역과 로컬 메모리 영역은 MySQL 서버 내에 존재하는 많은 스레드가 공유해서 사용하는 공간인지 여부에 따라 구분된다.

4.1.3.1 글로벌 메모리 영역

• 클라이언트 수와 무관하게 하나의 메모리 공간만 할당된다.
  • 필요에 따라 2개 이상의 메모리 공간을 할당받을 수는 있다.
• 대표적인 글로벌 메모리 영역
  • 테이블 캐시
  • InnoDB 버퍼 풀
  • InnoDB 어댑티브 해시 인덱스
  • InnoDB 리두 로그 버퍼

4.1.3.2 로컬 메모리 영역

• 로컬 메모리 영역은 세션 메모리 영역이라고도 표현한다.
• 로컬 메모리 영역은 MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 영역이다.
• 로컬 메모리 영역은 클라이언트 스레드별로 독립적으로 할당되며, 절대 공유되지 않는다.
• 로컬 메모리 영역은 글로벌 메모리 영역과 달리 크기를 주의해서 설정하지 않기 때문에 최악의 경우 메모리 부족으로 멈출 수 있기 때문에 적절한 메모리 공간을 설정해야 한다.
• 필요할 때가 아니면, 메모리 강간을 할당조차 하지 않을 수 있다.
  • 소트 버퍼, 조인 버퍼 등
• 커넥션 버퍼나 결과 버퍼는 커넥션이 열려 있는 동안 할당된 상태로 남아 있다.
• 로컬 메모리의 대표 영역
  • 정렬 버퍼(Sort buffer)
  • 조인 버퍼
  • 바이너리 로그 캐시
  • 네트워크 버퍼

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

• MySQL에는 여러 가지 엔진을 플러그인 형태로 개발하여 사용할 수 있다.

• SHOW ENGINES; 명령어를 통해 MySQL 서버(mysqld)에서 지원되는 스토리지 엔진을 확인할 수 있다.

  

• Support 컬럼의 값을 통해 스토리지 엔진 여부를 확인할 수 있다.

  • YES: MySQL 서버에 해당 스토리지 엔진이 포함되어 있고, 사용 가능으로 활성화된 상태
  • DEFAULT: YES와 동일한 상태이지만, 필수 스토리지 엔진임을 의미한다.
  • NO: 현재 MySQL 서버에 포함되지 않았음을 의미한다.
  • DISABLED: 현재 MySQL 서버에는 포함되었지만, 파라미터에 의해 비활성화된 상태

• MySQL 서버에 포함되지 않은 스토리지 엔진을 사용하려면 MySQL 서버를 다시 빌드(컴파일)해야 한다.

• 플러그인 형태로 빌드된 스토리지 엔진 라이브리러를 다운로드 받아 넣으면, 사용할 수 있다.

• SHOW PLUGINS 명령을 통해 스토리지 엔진뿐 아니라 인증 및 전문 검색용 파서와 같은 플러그인도 확인할 수 있다.

• 스토리지 엔진 뿐 아니라 다양한 기능을 플러그인 형태로 지원한다.

4.1.5 컴포넌트

• MySQL 8.0부터는 기존의 플러그인 아키텍처를 대체하기 위해 컴포넌트 아키텍처가 지원된다.
• 컴포넌트 아키텍처는 플러그인의 단점을 보완해준다.
  • 플러그인은 오직 MySQL 서버와 인터페이스할 수 있고, 플러그인끼리는 통신할 수 없다.
  • 플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않다.
  • 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어렵다.

4.1.6 쿼리 실행 구조

• SQL 요청 -> (쿼리 파서 -> 전처리기 -> 옵티마이저 -> 쿼리 실행기) -> (스토리지 엔진)

4.1.6.1 쿼리 파서

• 쿼리 파서는 사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위)으로 분리해 트리 구조 만드는 작업
• 이 과정에서 쿼리 기본 문법 오류가 발견되어 사용자에게 오류 메시지를 전달한다.

4.1.6.2 전처리기

• 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다.
• 각 토큰을 테이블 이블이나 컬럼 이름, 내장 함수 등 개체를 매핑해 해당 객체의 존재 여부와 접근 권한을 확인한다.
• 실제 존재하지 않거나 권한이 없어서 사용할 수 없는 경우 이 단계에서 걸러진다.

4.1.6.3 옵티마이저

• 옵티마이저는 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지 결정하는 역할을 한다.
• 옵티마지어의 역할은 중요하고 영향 범위 또한 아주 넓다

4.1.6.4 실행 엔진

• 실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 한다.

4.1.6.5 핸들러(스토리지 엔진)

• 핸들러는 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하거나 읽어온다.
• MyISAM 테이블을 조적하는 경우 MyISAM 스토리지 엔진이 핸들러가 된다.
• InnoDB 테이블을 조작하는 경우 InnoDB 스토리지 엔진이 핸들러가 된다.

4.1.7 복제

4.1.8 쿼리 캐시(Query Cache)

• 쿼리 캐시는 SQL의 실행 결과를 메모리에 캐시하고, 동일 SQL이 실행되면 메모리에서 즉사 반환하여 빠른 성능을 보일 수 있다.
• 테이블 데이터 변경 시 쿼리 캐시가 모두 삭제되는 과정에서 성능 저하와 버그가 많이 발생하여 MySQL 8.0 부터는 제거되었다.

4.1.9 스레드 풀

• 스레드 풀은 MySQL 서버 엔터프라이드 에디션과 Peronana Sever에서 제공된다.
• 스레드 풀은 사용자 요청을 처리하는 스레드 수를 제한하여 서버 자원 소모를 줄이는 것을 목적으로 한다.
  • 하지만, 실제 눈에 띄는 성능 향상을 보여준 경우는 드물다.
• 스레드 풀 내 선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공한다.

4.1.10 트랜잭션 지원 메타데이터

• 데이터베이스 서버에서 테이블의 구조 정보, 스토어드 프로그램 등의 정보를 데이터 딕셔너리 또는 메타데이터라고 한다.
• MySQL 5.7까지는 테이블 구조를 FRM파일에 저장하고, 일부 스토어드 프로그램 파일(TRN, TRG, PAR 등) 기반으로 관리했다.
  • 파일 기반 메타데이터는 생성 및 변경 작업 트랜잭션을 지원하지 않은 상태로 남는 문제가 발생한다.
• MySQL 8.0 부터는 테이블의 구조 정보나 스토어드 프로그램 코드 관련 정보를 모두 InnoDB 테이블에 저장한다.
• 시스템 테이블과 데이터 딕셔너리 정보를 모아서 mysql DB에 저장하고 mysql.ibd라는 이름의 테이블스페이스에 저장된다.
• InnoDB 외 스토리지 엔진들을 위해 SDI(Serialized Dictionary Information) 파일을 사용한다.
  • *.sdi 파일 형태로 저장되며, 기존의 *.FRM 파일과 동일한 역할을 한다.

[KKambi]

플러그인 vs 컴포넌트

플러그인은 단점

1. MySQL 서버와만 인터페이스 가능, 플러그인끼리 통신 불가능
2. 캡슐화X (MySQL 서버의 변수, 함수를 직접 호출)
3. 상호 의존 관계 설정 불가 -> 초기화 어려움

MySQL 8.0부터 컴포넌트 도입!

스토어드 프로그램

프로시저, 함수, 테이블 트리거, 이벤트를 포함하는 용어

• 여러 SQL을 보내는 대신에, 스토어드 프로그램명과 파라미터만 보내서 네트워크 트래픽 양 감소
• 보안 강화 (권한 부여)
• 절차지향 프로그래밍 가능 (IF, WHILE 등)
• 단점: 낮은 처리 성능 (MySQL 엔진에 의해 해석되고 수행되는데, MySQL 서버는 절차적 코드 처리에 부족한 성능)

프로시저 - 여러 쿼리를 묶어놓은 집합 (여러 동작 일괄 처리) 함수 - Return 값을 반환하므로, 다른 SQL문장에 넣어 사용할 수 있음 테이블 트리거 - 테이블의 레코드가 저장되거나 변경될 때, 미리 정의된 작업을 자동으로 실행 이벤트 - 유저가 정의한 스케줄에 따라 실행되는 작업

글로벌 메모리 영역의 테이블 캐시?

MySQL 서버에서 사용되는 각 테이블의 메타 정보를 담고 있는 캐시

• 테이블을 읽고 쓰기 위해선 테이블을 open해야 하고, 사용하고 나서 close해야 함
• open된 테이블의 정보를 캐싱해서 각 커넥션에서 공유

DAN? SAN?

DAS, SAN, NAS, ...

• 스토리지의 종류
• 하드디스크를 여러개 장착가능한 데이터 스토리지
• 연결방식의 차이 존재

뭐가 다른데

• DAS = 유선, 외장하드
• NAS = 무선, TCP/IP 기반의 LAN(Local Area Network) 사용
• SAN = Storage Area Network, 광채널 스위치로 네트워킹

DEFINER

권한 관련 용어, MySQL에서 Stored Procedure의 수행 권한과 관련있다.

• SQL SECURITY DEFINER : 프로시저를 생성한 user의 권한으로 SP를 실행
• SQL SECURITY INVOKER : 프로시저를 호출한 user의 권한으로 SP를 실행

VIEW

데이터베이스에 존재하는 일종의 가상 테이블

• 실제 데이터를 저장하고 있진 않지만, 테이블처럼 행과 열을 가진다.
• 이름처럼, 다른 table에 저장된 데이터나, 다른 view에서 보여주는 데이터를 대신 보여주는 역할을 수행
• 특정 데이터의 원하는 부분을 투영한다고 이해하니 편했다.

장점

1. 특정 사용자에게 테이블 전체가 아닌, 필요한 필드만 보여줄 수 있음
2. 복잡한 쿼리를 단순화시킬 수 있음
3. 쿼리를 재사용할 수 있음 (이건 어떨 때인지?)

단점

1. 한 번 정의된 뷰는 변경할 수 없음 (새로 생성해야 한다는 의미인듯)
2. 삽입, 삭제, 갱신 작업에 제한이 크다.
3. 뷰만을 위한 인덱스를 가질 수 없다.

[jjy0918]

4.2 InnoDB 스토리지 엔진 아키텍처

• InnoDB 스토리지 엔진은 MySQL 스토리지 엔진 중 거의 유일한 레코드 기반 잠금을 제공하여 동시성 처리가 가능하며, 안전성이 뛰어나다.

4.2.1 PK에 의한 클러스터링

• InnoDB의 모든 테이블은 기본적으로 PK를 기준으로 클러스터링되어 저장된다.
  • PK 기준으로 순서대로 디스크에 저장.
  • 모든 세컨더리 인덱스는 레코드 주소 대신 PK의 논리적인 주소를 사용한다.
• PK가 인덱스 키로 사용되기 때문에 PK를 이용하면 스캔이 빨리 처리될 수 있다.
• 쿼리 실행 계획에서 PK는 다른 인덱스 보다 비중이 높게 설정된다.
• 오라클의 IOT(Index Organized Table)와 동일한 구조
• MyISAM 스토리지 엔진에서는 클러스터링 키를 지원하지 않는다.
  • PK와 세컨더리 인덱스 사이에 속도 차이가 없다.

4.2.2 외래 키 지원

• InnoDB에서 지원하는 외래 키 기능은 MyISAM이나 MEMORY 테이블에서는 사용할 수 없다.
• InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 컬럼에 인덱스를 생성과 변경 시 잠금이 전파되기 떄문에 주의가 필요하다.
• foreign_key_checks 시스템 변수를 OFF로 설정하면, 외래 키 관련된 작업을 일시적으로 멈출 수 있다.
  • 외래 키 체크를 일시적으로 해제하여도 부모와 자식 간 관계를 맞춘 후 활성화해야 한다.
  • OFF 시 ON DELETE CASCADE, ON UPDATE CASADE와 같은 옵션도 동작하지 않는다.
  • 해당 옵션은 GLOBAL과 SESSION 모두 설정이 가능하기 때문에 주의가 필요하다.

4.2.3 MVCC(Multi Version ConCurrency Control)

• MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기 제공이다.
  • 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다.
  • Isonloation Level에 따라 조회 시 다양한 버전에 대한 응답을 제공한다.
• InnoDB는 Undo log를 이용하여 MVCC 기능을 제공한다.
• UPDATE 쿼리를 날린 후 커밋을 하지 않았다면 변경 전 데이터는 Undo log에 저장되어 있고, 변경 결과는 InnoDB 버퍼 풀에 저장된다.
  • 이때 격리 레벨에 따라 조회 결과가 달라진다.

4.2.4 잠금 없는 일관된 읽기(Non-Locking Consistent Read)

• InnoDB 스토리지 엔진은 MVCC 기술을 이용하여 lock을 걸지 않고 읽기 작업을 수행한다.
  • lock을 걸지 않기 때문에 다른 트랜잭션이 가지고 있는 lock을 기다리지 않고 읽을 수 있다.
• isolation level이 SERIALIZABLE이 아니라면(READ_COMMITED, READ_UNCOMMITED, REPEATABLE_READ) INSERT와 연결되지 않은 순수한 읽기는 바로 실행된다.
  • 이것을 잠금 없는 일관된 읽기 라고 한다.
• InnoDB는 변경 전의 데이터를 모두 undo log에 저장하기 때문에 가능하다.
• 오랜시간 동안 활성된 트랜잭션 때문에 undo log를 삭제하지 못하는 경우 문제가 발생할 수 있기 때문에 빠른 커밋이나 롤백이 필요하다.

4.2.5 자동 데드락 감지

• InnoDB는 내부적으로 lock이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프 형태로 관리한다.
  • innodb_table_locks를 활성화하면 MySQL엔진에서 관리하는 테이블 잠금까지 감지할 수 있다.
• 데드락 감지 스레드가 따로 존재하고 주기적으로 검사하여 강제 종료한다.
  • 동시 처리 스레드가 매우 많아지거나 트랜잭션의 잠금 개수가 많아지면 매우 느려질 수 있다.
  • innodb_deadlock_detect 시스템 변수를 OFF로 설정하면, 데드락 감지 스레드는 동작하지 않는다.
  • innodb_lock_wait_timeout 설정 시 데드락이 발생 상황에서 일정 시간 지난 후 자동으로 요청이 실패하도록 할 수 있다.
• 강제 종료하는 트랜잭션은 undo log 양이 적은 것을 선택하고, 해당 트랜잭션을 롤백한 후 종료한다.

4.2.6 자동화된 장애 복구

• InnoDB는 기본적으로 MySQL이 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된 데이터에 대한 복구가 자동으로 진행된다.
  • 자동 복구될 수 없는 손상이 있다면 자동 복구를 멈추고 MySQL는 종료된다.
• innodb_force_recovery 시스템 변수를 설정하면, 자동 복구나 파일 손상 여부 검사를 선별적으로 설정할 수 있다.
  • 해당 시스템 변수는 1~6 까지 설정할 수 있다.
  • 복구에 실패했다면, 백업을 이용하여 다시 구축하는 방법밖에 없다.

4.2.7 InnoDB 버퍼 풀

• InnoDB 스토리지 엔진에 가장 핵심적인 부분으로, 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두고 쓰는 공간이다.
• 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 한다.
• 버퍼 풀을 이용하면 변경된 데이터를 모아 처리하기 때문에 랜덤한 디스크 작업 횟수를 줄일 수 있다.

4.2.7.1 버퍼 풀의 크기 설정

• MySQL 5.7 부터는 InnoDB 버퍼 풀의 크기를 동적으로 조절할 수 있도록 개선되었다.
• 메모리가 8GB 미만이란 50% 정도, 그 이상이라면 조금씩 올려가며 최적점을 찾는 것이 좋다.
• innodb_buffer_pool_size 시스템 변수를 통해 설정할 수 있다.
• 버퍼 크기를 늘리는 것은 괜찮지만, 줄이는 것은 서비스 영향도가 매우 크기 때문에 하지 않는 것이 좋다.
  • 버퍼 풀은 내부적으로 128MB의 청크 단위로 쪼개 관리되기 때문에 줄이거나 늘릴 때 128MB 단위로 처리된다.
• InnoDB 버퍼 풀은 버퍼 풀을 여러 개로 쪼개어 관리할 수 있도록 개선되어 버퍼 풀 관리하는 잠금(세마포어) 자체도 분산되어 동작한다.
  • innodb_buffer_pool_instances 시스템 변수를 통해 버퍼 풀을 여러 개로 분리할 수 있다.
  • 기본적으로 8개로 초기화된다

4.2.7.2 버퍼 풀의 구조

• InnoDB 버퍼 풀은 메모리 공간은 페이지 크기(innodb_page_size)의 조각으로 쪼개어 필요할 때 각 조각에 저장한다.
• 페이지 관리를 위해 LRU, Flush, Free 리스트라는 자료 구조를 사용한다.
  • Free는 비어 있는 페이지 목록이며, 사용자의 쿼리가 데이터를 디스크에서 가져올 때 사용된다.
  • LRU는 엄밀히 말해 LRU + MRU의 형태라고 보면 된다.
  • 자주 사용되는 데이터는 MRU 영역에서 계속 살아 있는다.
  • 거의 사용되지 않는 데이터는 LRU 끝으로 밀려나 제거된다.
  • Flush는 디스크로 동기화되지 않는 데이터가 존재하는 곳이다. 일정 시점이 지나면 디스크로 기록된다.

4.2.7.3 버퍼 풀과 리두 로그

• InnoDB에서 버퍼 풀과 리두 로그는 매우 밀접한 관계를 맺고 있다.
• InnoDB 버퍼 풀은 클린 페이지와 더티 페이지로 나뉜다.
  • 클린 페이지는 읽은 상태 그대로 전혀 변경되지 않은 데이터들이다.
  • 더티 페이지는 읽은 상태에서 변경된 데이터들이다.
• 리두 로그는 1개 이상의 고정 크기 파일을 연결하여 순환 고리처럼 사용한다.
  • 데이터 변경이 계속 발생하면 데이터가 있는 리두 로그 파일이 덮여 쓰일 수 있다.
  • 전체 로그 파일에서 재사용 가능한 공간과 재사용 불가능한 공간을 구분하여 관리해야 한다.
  • 재사용 불가능한 공간을 활성 리두 공간(Active Redo Log)이라고 한다.
• 리두 로그 파일은 기록될 때마다 로그 포지션을 계속 증가시키고, 그 값을 LSN(Log Sequence Number)이라고 한다.
• InnoDB는 주기적으로 체크포인트 이벤트를 발생시켜 리두 로그와 더티 페이지를 동기화한다.
  • 가장 최근 체크포인트의 LSN이 활성 리두 공간의 시작점이 된다.
  • 가장 최근 체크포인의 LSN과 마지막 리두 로그 엔트리의 LSN 차이점을 체크포인트 에이지(Checkpoin Age)라고 한다.
  • 체크포인트 에이지(Checkpoin Age)는 활성 리두 로그 공간의 크기가 된다.
• 체크포인트 발생 시 체크포인트 LSN 이하의 리두 로그 엔트리와 관련된 더티 페이지는 디스크로 동기화된다.
• 리두 로그는 변경분만 가지고 버퍼 풀은 페이지 통쨰로 가지기 때문에 데이터 변경이 발생해도 리두 로그는 훨씬 작은 공간만 있으면 된다.
  • 버퍼 풀 크기가 100GB 이하라면, 리두 로그 파일의 전체 크기는 5~10GB 부터 조금씩 늘리는 것이 좋다.

4.2.7.4 버퍼 풀 플러시(Buffer Pool Flush)

• MySQL 5.7 부터 대부분의 서비스에서 더티 페이지를 동기화하는 부분(더티 페이지 플러시)에서 디스크 쓰기 폭증 현상이 줄어들어 별도의 설정은 필요하지는 않다.
• InnoDB에서는 더티 페이지를 악영향 없이 디스크에 동기화하기 위해 2개의 플러시 기능을 백그라운드로 실행한다.
  • 플러시 리스트(FLush list) 플러시
  • LRU 리스트(LRU list) 플러시
• 플러시 리스트 플러시
  • InnoDB는 주기적으로 플러시 리스트 라는 플러스 함수를 호출하여 오래전에 변경된 데이터 페이지를 순서대로 디스크에 동기화한다.
  • innodb_page_cleaners, innodb_max_dirty_pages_pct_lwm, innodb_io_capacity 등 다양한 옵션을 통해 관련 설정이 가능하다.
  • 어댑티브 플럿히 기능을 활성화하면, 더티 페이지 비율이나 설정값에 상관 없이 새로운 알고리즘을 사용한다.
• LRU 리스트 플러시
  • LRU 리스트에서 사용 빈도가 낮은 데이터 페이지를 제거한다.
  • 더티 페이지는 동기화하고, 클린 페이지는 Free 리스트로 옮긴다.

4.2.7.5 버퍼 풀 상태 백업 및 복구

• 버퍼 풀은 쿼리의 성능에 매우 밀접하게 연결되어 있다.
  • 데이터가 버퍼 풀에 적재되어 있는 워밍업(Warming Up)의 경우 성능이 좋다.
• MySQL 5.5에서는 재시작할 때 강제 워밍업해주었다.
• MySQL 5.6부터는 버퍼 풀 덤프 및 적재 기능이 도입되어 버퍼 풀 상태를 백업한 후 다시 복구할 수 있다.
  • 버퍼 풀의 LRU 리스트에서 적재된 데이터 페이지의 메타 정보만 가져오기 때문에 매우 빠르게 완료된다.
  • 다시 버퍼 풀로 복구하는 작업은 디스트에서 가져와야 하기 때문에 느릴 수 있다.

4.2.7.6 버퍼 풀의 적재 내용 확인

• MySQl 5.6 부터는 information_schema 데이터베이스의 innodb_buffer_page 테이블을 이용하여 버퍼 풀에 적재된 페이지를 확인할 수 있다.
  • 버퍼 풀이 큰 경우 데이터 조회 시 큰 부하를 일으킬 수 있다.
• MySQL 8.0 부터는 information_schema 데이터베이스의 innodb_cached_indexes 테이블이 추가되어 인덱스 별로 데이터 페이지가 얼마나 적재돼 있는 확인할 수 있다.

4.2.8 Double Write Buffer

• 리두 로그는 공간 낭비를 막기 위해 페이지의 변경된 내용만 기록한다.
  • 더티 페이지 저장 시 일부만 기록되는 문제가 발생할 수 있고, 이런 현상 때문에 오동작이나 비정상 종료가 발생한다.
  • Double Write Buffer는 이러한 오동작을 막는 기능이다.
• Double Write Buffer는 디스크로 플러시할 때 저장 내용을 DoubleWrite 버퍼에 한꺼번에 저장하고, 개별로 데이터 파일로 기록한다.
  • DoubleWrite Buffer의 데이터는 정상 기록 시 필요는 없고, 실패할 때만 원래 목적으로 사용된다.
  • inndb_doublewrite 시스템 변수로 사용 여부를 결정할 수 있다.
• InnoDB 재시작 시 DoubleWrite버퍼의 내용과 데이터 파일의 데이터를 비교하여 변경이 있을 경우 복구한다.
• DoubleWrite버퍼는 안정성을 위해 자주 사용된다.
  • HDD 처럼 자기 원판이 회전하는 시스템에서는 부담이 없지만, SSD 처럼 랜덤 IO나 순차 IO가 비슷한 경우에는 부담스러울 수 있다.
• 리두 로그 동기화 설정을 1이 아닌 값으로 설정했다면, DoubleWrite버퍼도 비활성화 하는 것이 좋다.

[KKambi]

인덱스

메인메모리 I/O 속도 >>> 디스크 I/O 속도

• 인덱스를 통해 레코드를 묶어놓는다.
• 탐색 범위를 줄여, Disk I/O를 줄이자!

클러스터링 인덱스

InnoDB 스토리지 엔진은 테이블을 Primary key에 따라 클러스터링한다.

• 테이블당 하나만 가질 수 있다.
• INSERT 시 데이터가 정렬되고, 인덱스는 데이터 블록의 첫 번째 레코드 주소값을 갖게 된다.
• 데이터가 정렬되어 저장된다.

세컨더리 인덱스

• PK 이외의 필요한 정렬 기준이 있을 경우
• 테이블당 여러 개 가질 수 있다.
• 데이터가 해당 기준에 따라 정렬되어 있지 않다 = 인덱스마다 주소값을 갖고 있다 = 상대적으로 성능이 떨어진다.

Q. 어댑티브 해시 인덱스 = B-Tree 인덱스를 위한 보조 인덱스라고 보면 되는거지?

Online DDL

테이블 구조를 변경(=스키마 변경)을 실행하는 도중에도, INSERT나 UPDATE와 같은 DML을 실행할 수 있게 하는 기능 InnoDB는 3가지 알고리즘 지원

• copy : 변경된 스키마가 적용된 임시 테이블을 생성한 후, 기존 테이블의 데이터를 복사하여 테이블 이름 변경 (Concurrent DML 지원 X)
• inplace : 원본 테이블에 직접 변경작업을 적용 / 변경 중 들어오는 DML을 Online Alter Log Buffer에 쌓고, 변경 완료되면 버퍼를 반영
• instant : 메타 정보만 수정하여 변경 사항 반영 / 데이터 딕셔너리의 메타데이터만 변경 = 테이블에 메타데이터 락 X

테이블스페이스

간단하게, 데이터가 저장되는 공간

• 공간 분리를 해놓았다고 생각하면 됨
• DB > Tablespace > Table ?

InnoDB 테이블스페이스의 종류

1. System : Shared Tablespace / 데이터 딕셔너리, 이중 쓰기 버퍼, 변경 버퍼 등의 저장 / 여기서 테이블 생성도 가능
2. File-per-table : 하나의 InnoDB 테이블에 대한 Data 및 인덱스를 개별 파일로 관리하는 공간
3. General : Shared Tablespace / 테이블스페이스 추가를 위한 용도
4. Undo : 5.7 이후 시스템 테이블스페이스에서 분리
5. Session Temporary : 세션 사용자가 생성한 임시 테이블 & 옵티마이저가 내부적으로 생성하는 임시 테이블
6. Global Temporary : 사용자가 생성한 임시 테이블의 변경사항에 대한 롤백 세그먼트 저장

[jjy0918]

4.2.9.2 언두 테이블스페이스 관리

• 언두 로그가 저장되는 공간을 언두 테이블스페이스(Undo Tablespace)라고 한다.
• MySQL 8.0 부터는 언두 로그는 항상 시스템 테이블스페이스 외부의 별도 로그 파일에 기록된다.
• 언두 테이블스페이스는 1개 이상 128개 이하의 롤백 세그먼트를 가지며, 롤백 세그먼트는 1개 이상의 언두 슬롯을 가진다.
• 하나의 롤백 세그먼트는 InnoDB의 페이지 크기를 16바이트로 나눈 값의 개수만큼의 언두 슬록을 가진다.
  • InnoDB의 페이지 크기가 16KB라면, 하나의 롤백 세그먼트는 1024개의 언두 슬롯을 가지게 된다.
• 하나의 트랜잭션이 필요로 하는 언두 슬롯의 개수는 트랜잭션이 실행하는 INSERT, UPDATE, DELETE 문장의 특성에 따라 최대 4개의 언두 슬롯을 가질 수 있다.
• 즉, 최대 트랜잭션 수 = (InnoDB 페이지 크기) / 16 (롤백 세그먼트 개수) (언두 테이블스페이스 개수) 이다.
• 언두 로그 공간이 남는 것은 괜찮지만, 부족한 경우 트랜잭션을 시작할 수 없기 때문에 문제가 발생할 수 있다.
• MySQL 8.0 부터는 CREATE UNDO TABLESPACE나 DROP TABLESPACE로 새로운 언두 테이블스페이스를 동적으로 생성, 삭제할 수 있다.
• MySQL 8.0 부터는 언두 테이블스페이스 중 필요하지 않은 공간을 반납하는 Undo tablespace truncate 기능을 제공한다.

4.2.10 체인지 버퍼

• INSERT, UPDATE 시 인덱스도 변경될 수 있는데, 이때 인덱스를 업데이트 하는 과정은 랜덤하게 디스크를 읽기 때문에 자원을 많이 소모할 수 있다.
• InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만, 그렇지 않다면 임시 공간에 저장해두고 결과를 사용자에게 제공한다.
  • 이때 사용하는 임시 메모리 공간이 체인지 버퍼(Change Buffer)다.
• 체인지 버퍼에 저장된 데이터는 체인지 버퍼 머지 스레드(Merge Thread)라는 백그라운드 스레이드에 의해 병합된다.
  • MySQL 5.5에서는 INSERT만 가능했지만, MySQL 8.0 부터는 ISNERT, DELETE, UPDATE에 대해서도 가능해졌다.
• 유니크 인덱스의 경우 체인지 버퍼에 저장할 수 없다.
• 체인지 버퍼는 기본적으로 메모리 공간의 25%까지 사용할 수 있지만, 필요하다면 50% 까지 사용할 수 있다.

4.2.11 리두 로그 및 로그 버퍼

• 리두 로그는 트랜잭션의 4요소인 ACID 중 D(Durable, 지속성)과 가장 연관이 높다.
• 리두 로그를 통해 서버가 비정상적 종료 시 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해준다.
• 대부분의 데이터베이스는 데이터 변경 시 실제 디스크에 저장하려면 많은 자원이 필요하기 때문에 내용을 로그에 먼저 기록한다.
• 비정상 종료가 되면, 리두 로그를 바탕으로 다시 복구한다.
• MySQL이 비정상 종료가 되는 경우 InnoDB 스토리지 엔진의 데이터 파일은 두 가지 종류의 일관되지 않은 데이터를 가질 수 있다.
  1. 커밋되었지만 데이터 파일에 기록되지 않은 데이터
     • 리두 로그에 저장된 데이터를 다시 복사하면 된다.
  2. 롤백되었지만 데이터 파일에 이미 기록된 데이터
     • 그 변경이 커밋되었는지, 롤백 되었는지, 트랜잭션 중간 상태였는지 판단하기 위해 리두 로그 사용한다.
• 데이터베이스 서버에서 리두 로그는 트랜잭션 커밋 시 즉시 디스크로 기록되도록 설정하는 것을 권장한다.
• 리두 로그 파일들의 전체 크기는 InnoDB 스토리지 엔진이 가지고 있는 버퍼 풀의 효율성을 결정하기 때문에 신중히 결정해야 한다.
• 리두 버퍼의 크기는 기본 값인 16MB 수준에서 설정하는 것이 좋지만, BLOB이나 TEXT의 경우 더 크게 설정하는 것이 좋다.

4.2.11.1 리두 로그 아카이빙

• MySQL 8.0 부터는 InnoDB 스토리지 엔진의 리두 로그를 아카이빙할 수 있는 기능을 제공한다.
• 리두 로그 아카이빙 기능은 데이터 변경이 많아서 리두 로그가 덮어쓰인다고 하더라도 백업이 실패하지 않게 해준다.
• 리두 아카이빙 세션이 끊어지면 InnoDB 스토리지 엔진은 리두 로그 아카이빙을 멈추고 아카이빙 된 파일도 자동으로 삭제한다.
• 아카이빙된 리두 로그를 정상적으로 사용하려면 커넥션을 그대로 유지해야 하며, 정상 종료해야 한다.

4.2.11.2 리두 로그 활성화 및 비활성화

• MySQL 8.0 부터는 리두 로그를 수동으로 비활성화 할 수 있다.
• 리두 로그를 비활성화하여 데이터 복구나 대용량 데이터를 한번에 적재하는 경우 시간을 단축시킬 수 있다.
• 리두 로그가 비활성화 된 상태에서 서버가 비정상적으로 심각한 종료되면 문제가 발생할 수 있기 때문에 반드시 다시 활성화 시켜야 한다.

4.2.12 어댑티브 해시 인덱스

• 어댑티브 해시 인덱스(Adaptive Hash Index)는 사용자가 수동으로 생성하는 인덱스가 아니라 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스다.
• 일반적인 인덱스의 경우 B-Tree로 구성되어 있는데, 결국 데이터를 찾기 위해서는 리프 노드까지 찾아야 한다.
• 어댑티브 해시 인덱스는 B-Tree 검색 시간을 줄여주기 위해 도입되었다.
• InnoDB 스토리지 엔진은 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들어 레코드가 저장된 데이터 페이지를 직시 찾아갈 수 있게 도와준다.
• 해시 인덱스는 인덱스 키 값과 해당 인덱스 키 값이 저장된 데이터 페이지 주소의 쌍으로 관리된다.
• 인덱스 키 값은 B-Tree 인덱스의 고유 번호(Id)와 B-Tree 인덱스의 실제 키 값 조합으로 이루어진다.
• 키 값이 B-Tree의 고유 번호를 포함했기 때문에 모든 B-Tree 인덱스는 해시 인덱스에 하나만 저장된다.
• 데이터 페이지 주소는 실제 키 값이 저장된 InnoDB 버퍼 풀에 로딩된 페이지 주소를 가리킨다.
  • 데이터 페이지가 버퍼 풀에서 사라지면 어댑티브 해시 인덱스에서도 사라진다.
• MySQL 8.0 부터는 내부 잠금(세마포어)를 줄이기 위해 어댑티브 해시 인덱스의 파티션 기능을 제공한다.
• 어댑티브 해시 인덱스에도 성능상 단점이 존재한다.
  • 디스크 읽기가 많은 경우
  • 특정 패턴의 쿼리가 많은 경우(조인이나 LIKE 패턴 검색)
  • 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
• 성능상 이점도 당연히 존재한다.
  • 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)
  • 동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우
  • 쿼리가 데이터 중에서 일부 데이터만 집중되는 경우
• 어댑티브 해시 인덱스는 데이터 페이지를 메모리에서 접근하는 기능이기 때문에 빈번하게 디스크에서 읽어오는 경우 도움이 되지 않을 가능성이 크다.
• 어댑티브 해시 인덱스 또한 메모리를 사용하기 때문에 큰 메모리 공간을 요구할 수 있다.
• 또한, 인덱스 키가 해시 인덱스로 만들어져야 하고 불필요한 경우 제거돼야 하며, 해시 인덱스 활성화 시 그 키 값이 해시에 있는지 항상 검색해봐야한다.
  • 즉, 해시 인덱스의 효율이 없는 경우에도 계속 해시 인덱스를 사용한다.
• 인덱스가 삭제되거나 변경되는 경우 InnoDB 스토리지 엔진은 이 테이블이 가진 모든 데이터의 내용을 어댑티브 해시 인덱스에서 제거해야 한다.
  • 즉, 어댑티브 해시 인덱스에 의해 테이블 삭제, 변경 작업은 오랜 시간이 소요될 수 있다.
• 어댑티브 해시 인덱스가 사용 중인 메모리 공간과 서버 CPU 사용량을 종합하여 효율적인지 판단해야 한다.

4.2.13 InnoDB와 MyISAM, MEMORY 스토리지 엔진 비교

• MySQL 5.5 부터는 InnoDB 스토리지 엔진이 기본 스토리지 엔진이지만, MySQL 서버의 시스템 테이블은 여전히 MyISAM 테이블을 사용한다.
• 전문 검색이나 공간 좌표 검색 기능은 MyISAM 에서만 제공된다.
• MySQL 8.0 부터는 MySQL 모든 시스템 테이블이 InnoDB 스토리지 엔진으로 교체되었고, 전문 검색이나 공간 좌표 검색 기능도 제공한다.

4.3 MyISAM 스토리지 엔진 아키텍처

4.3.1 키 캐시

• InnoDB 버퍼 풀과 비슷한 역할을 하는 것이 MyISAM의 키 캐시(Key Cache)다.
• MyISAM 키 캐시는 인덱스 대상으로만 동작하며, 인덱스 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할을 한다.
• 캐시 키 히트율(Hit rate) = 100 - (Key_reads / Key_read_requests * 100)

4.3.2 운영체제의 캐시 및 버퍼

• MyISAM 테이블의 데이터에 대해서는 디스크로부터 I/O를 해결해 줄 만한 어떠한 캐시나 버퍼링 기능도 가지고 있지 않다.
  • 대부분의 운영체제에는 디스크로부터 읽는 파일에 대해 캐시나 버퍼링 매커니즘이 있기 때문에 매번 읽지는 않는다.

4.2.3 데이터 파일과 프라이머리 키(인덱스) 구조

• InnoDB는 PK에 의해서 클러스터링 되지만 MyISAM은 PK에 의해 클러스터링 없이 데이터 파일이 힙 공간처럼 활용된다.
  • 레코드 PK 값과 무관하게 INSERT 되는 순서대로 데이터 파일에 저장된다.
• 테이블에 저장되는 레코드는 모두 ROWID 라는 물리적인 주솟값을 가지며, PK와 세컨더리 인덱스 모두 파일에 저장된 레코드의 ROWID 값을 포인터로 가진다.
• ROWID 값은 고정 길이와 가변 길이 형태가 존재한다.

4.4 MySQL 로그 파일

4.4.1 에러 로그 파일

• MySQL이 실행되는 도중에 발생하는 에러나 경고 메시지가 출력되는 로그 파일이다.
• 로그 파일의 위치는 MySQL 설정 파일(my.cnf)에서 log_error 라는 이름의 파라미터로 정의된 경로에 생성된다.
  • 별도 정의하지 않은 경우 데이터 디렉터리(datadir)에 .err 라는 확장자가 붙은 파일로 생성된다.

4.4.1.1 MySQL이 시작하는 과정과 관련된 정보성 및 에러 메시지

• 설정 파일을 변경하거나 데이터베이스가 비정상적으로 종료된 이후 다시 시작하는 경우 반드시 MySQL 에러 로그 파일을 통해 정상적으로 적용되었는지 확인해야 한다.
• 특정 변수가 무시된 경우에는 MySQL 서버가 에러 메시지를 출력하고 시작하지 못했다는 메시지가 나온다.

4.4.1.2 마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB의 트랜잭션 복구 메시지

• 비정상 종료 후 다시 시작될 때 재처리 과정에서 메시지가 출력되는데 간혹 문제가 있어 복구가 안되면 에러 메시지가 로그에 기록된다..

4.4.1.3 쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지

• 쿼리 발생하는 도중 문제가 발생한 경우 로그에 기록된다.

4.4.1.4 비정상적으로 종료된 커넥션 메시지(Aborted connection)

• 애플리케이션에서 정상적으로 접속 종료를 하지 못하고 종료되는 경우 에러 로그 파일에 기록된다.

4.4.1.5 InnoDB의 모니터링 또는 상태 조회 명령의 결과 메시지

• 테이블 모니터링이나 락 모니터링, 엔진 상태 조회 명령은 상대적으로 큰 메시지를 에러 로그 파일에 기록한다.
• 모니터링을 사용한 이후에는 다시 비활성화하는 것이 좋다.

4.4.1.6 MySQL의 종료 메시지

• MySQL이 비정상적으로 종료되면 스택 트레이스를 출력하는데, 이때 내용을 살펴봐서 보는 것이 좋다.

4.4.2 제너럴 쿼리 로그 파일(Genenral log)

• MySQL에서 실행되는 쿼리를 검토하기 위해 쿼리 로그를 활성화하여 검토할 수 있다.
• 쿼리 로그 파ㅓ일에는 시간 단위로 실행되었던 쿼리의 내용이 모두 기록된다.
• 슬로우 쿼리 로그와는 조금 다르게 제너럴 쿼리 로그는 실행되기 전에 바로 기록된다.
• 쿼리 로그 파일의 경로는 general_log_file이라는 이름의 파라미터에 설정되어 있다.
• 파일이 아니라 테이블에 저장할 수 있다.

4.4.3 슬로우 쿼리 로그

• 슬로우 쿼리 로그 파일에는 long_query_time 시스템 변수에 설정한 시간 이상이 소요된 쿼리는 모두 기록된다.
• 쿼리 실행이 정상적인 경우에만 기록된다.
• log_output 옵션을 이용해 슬로우 쿼리 로그를 파일로 기록할지 테이블로 기록할지 선택할 수 있다.
• Percona에서 개발한 Percona Toolkit을 이용하면 스크립트를 통해 쉽게 빈도나 처리 성능별로 쿼리를 정렬하여 살펴볼 수 있다.
• 모든 쿼리를 대상으로 슬로우 쿼리 로그의 실행 시간, 잠금 대기 시간 등에 대해 최소/최대 값을 표시할 수 있다.
• 각 쿼리 별 응답 시간과 실행 횟수를 보여줄 수 있다.
• 쿼리별 실행 횟수 및 누적 실행 시간 상세 정보를 보여줄 수 있다.