[Real MySQL 8.0] 4장 - 아키텍처: MySQL 엔진과 스토리지 엔진(InnoDB, MyISAM)

MySQL 엔진 아키텍처

· MySQL은 C, JDBC, ODBC, .NET 등의 표준 드라이버를 제공하여 대부분의 프로그래밍 언어로 접근 가능하다.

· MySQL 서버는 역할에 따라 다음과 같이 구분 가능하다.

1. MySQL 엔진: 사람의 머리 역할

2. 스토리지 엔진: 사람의 손발 역할

- 스토리지 엔진은 핸들러 API를 만족하면 누구든 스토리지 엔진을 구현해 MySQL 서버에 추가해서 사용할 수 있다.

MySQL 엔진

· 요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS의 두뇌에 해당하는 처리를 수행한다.

· 중심 구성 요소

- 커넥션 핸들러, SQL 파서, 전처리기: 클라이언트로부터 접속 및 쿼리 요청

- 옵티마이저: 쿼리를 최적화하여 실행

· MySQL은 표준 SQL(ANSI SQL) 문법을 지원하여, 표준 문법에 따라 작성된 쿼리는 타 DBMS와 호환되어 실행될 수 있다.

스토리지 엔진

· 실제 데이터를 디스크 스토리지에 저장하거나, 읽어오는 처리를 담당한다.

· 스토리지 엔진은 여러 개를 동시에 사용할 수 있다. (각 테이블 마다 다른 스토리지 엔진 사용 가능)

· 성능 향상을 위해 키 캐시(MyISAM), 버퍼 풀(InnoDB) 등의 기능을 내장한다.

핸들러 API

· MySQl 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때, 각 스토리지 엔진에 쓰기 또는 읽기를 요청한다.

이러한 요청을 핸들러 요청이라 하고, 여기서 사용되는 API를 핸들러 API라고 한다.

· SHOW GLOBAL STATUS LIKE 'Handler%'; 명령으로 핸들러 API 통해 얼마나 많은 데이터(레코드) 작업이 있었는지 확인할 수 있다.

ex) commit, delete, update, write, read_key, rollback

MySQL 스레딩 구조

· MySQL 서버는 (프로세스 기반이 아닌) 스레드 기반이다.

· MySQL 서버의 스레드는 크게 포그라운드 스레드와 백그라운드 스레드로 구분할 수 있으며,

실행 중인 스레드 목록은 performance_schema 데이터베이스의 threads 테이블을 통해 확인할 수 있다.

- 조회한 데이터에서 동일한 이름의 스레드가 2개 이상씩 보이는 것은 동일 작업을 병렬로 처리하는 경우다.

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

· 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다.

· 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면, 담당 스레드는 스레드 캐시로 되돌아간다.

- 이미 스레드 캐시에 일정 수 이상의 대기 중인 스레드가 있다면, 스레드 캐시로 돌아가지 않고 종료된다.

즉, 스레드 캐시에는 일정한 개수의 스레드만 존재한다.

- 스레드 캐시에 유지할 수 있는 최대 스레드 개수는 thread_cache_size 시스템 변수로 설정한다.

· 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져온다. 버퍼나 캐시에 없는 경우 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리한다.

· MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리하고, InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다.

백그라운드 스레드

· InnoDB에서 백그라운드로 처리되는 작업:

- 인서트 버퍼를 병합하는 스레드

- 로그를 디스크로 기록하는 스레드 (중요)

- InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드 (중요)

- 데이터를 버퍼로 읽어 오는 스레드

- 잠금이나 데드락을 모니터링하는 스레드

· MySQL은 데이터 쓰기 스레드와 데이터 읽기 스레드 개수를 2개 이상 지정할 수 있다. (5.5 버전 이후)

- innodb_write_io_threads와 innodb_read_io_threads 시스템 변수로 스레드의 개수를 설정한다.

· 데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리되므로 읽기 스레드는 많이 설정할 필요가 없다.

· 쓰기 스레드는 아주 많은 작업을 백그라운드로 처리하기 때문에 일반적인 내장 디스크를 사용할 때는 2~4 정보,

DAS나 SAN과 같은 스토리지를 사용할 때는 디스크를 최적으로 사용할 수 있을 만큼 충분히 설정하자.

TODO: DAS, SAN 스토리지란?

· MyISAM은 사용자 스레드가 쓰기 작업까지 함께 처리하므로 쓰기 버퍼링 기능을 사용할 수 없다.

반면에 InnoDB는 INSERT, UPDATE, DELETE 쿼리로 데이터가 변경되는 경우 쓰기 버퍼링을 사용하여, 데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 된다.

- 사용자의 요청을 처리하는 도중 데이터 쓰기 작업은 지연(버퍼링)되어 처리될 수 있지만, 데이터의 읽기 작업은 지연될 수 없다.

메모리 할당 및 사용 구조

· MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬(세션 or 커넥션) 메모리영역으로 구분된다.

· 두 영역을 구분하는 기준은 MySQL 서버 내에 존재하는 많은 스레드가 공유해서 사용하는 공간인지 여부다.

글로벌 메모리 영역

· 일반적으로 클라이언트 스레드 수와 무관하게 하나의 메모리 공간만 할당되는 영역이다.

- 필요에 따라 2개 이상 할당받을 수 있고, N개의 글로벌 영역 또한 모든 스레드에 의해 공유된다.

주요 특징:

- 모든 MySQL 스레드에서 공유한다.

- MySQL 서버가 시작되면서 운영체제로부터 할당받는다.

· 대표적인 글로벌 메모리 영역:

- 테이블 캐시

- InnoDB 버퍼 풀

- InnoDB 어댑티브 해시 인덱스

- InnoDB 리두 로그 버퍼

로컬(세션 or 커넥션) 메모리 영역

· MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 영역이다.

- 클라이언트 커넥션으로 부터 요청을 처리하기 위해 스레드를 하나씩 할당한다.

- 클라이언트와 MySQL 서버의 커넥션을 세션이라고 하기 때문에 세션 메모리 영역이라고도 표현한다.

· 주요 특징:

- 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다.

- 적절한 메모리 공간을 설정하지 않으면, 최악의 경우 MySQL 서버가 메모리 부족으로 멈출 수 있으니 주의하자.

- 각 쿼리의 용도별로 필요할 때만 공간이 할당되고, 필요하지 않은 경우 MySQL이 메모리에 할당조차 하지 않을 수 있다.

- 1. 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간(커넥션 버퍼, 결과 버퍼),

2. 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간(소트 버퍼, 조인 버퍼)로 나뉜다.

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

· MySQL의 대표적인 독특한 구조 중 하나다.

· 대표적인 플러그인

- 전문 검색 엔진을 위한 검색어 파서(인덱싱할 키워드 분리)

- 사용자 인증을 위한 Native Authentication과 Caching SHA-2 Authentication

- 여러 스토리지 엔진

https://velog.io/@fortice/MySQL-%EC%95%84%ED%82%A4%ED%85%8D%EC%B2%98

· 플러그인 구조의 장점: 새로운 용도를 위해 개발할 때, 전체 데이터베이스가 아닌 필요한 부분의 플러그인만 개발해서 교체하면 된다.

예) MySQL 쿼리 실행을 크게 'SQL 파서 -> SQL 옵티마이저 -> SQL 실행기 -> 데이터 읽기/쓰기(스토리지 엔진 처리)'로 나눌 수 있다.

이때, 데이터 읽기/쓰기의 처리 방법을 바꾸고 싶다면 전체 DBMS가 아닌 스토리지 엔진 처리 부분만 변경하면된다.

· 설치된 MySQL 서버에 지원되는 스토리지 엔진을 확인하는 명령어: SHOW ENGINES;

· 설치된 MySQL 서버에 지원되는 플러그인을 확인하는 명령어: SHOW PLUGINS;

컴포넌트

· MySQL 8.0 버전에 기존 플러그인 아키텍처를 대체하기 위한 컴포넌트 아키텍처

- 플러그인의 단점을 보완해서 구현되었다.

· MySQL 서버 플러그인의 단점

- 오직 MySQL 서버와 인터페이스할 수 있고, 플러그인끼리 통신할 수 없다.

- MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않다. (캡슐화 x)

- 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어렵다.

쿼리 실행 구조

· 쿼리 파서

- 사용자 요청으로 들어온 쿼리 문장을 토큰으로 분해해 트리 형태의 구조로 만드는 작업을 한다.

- 토큰: MySQL이 인식할 수 있는 최소 단위의 어휘나 기호

- 쿼리 문장의 기본 문법 오류가 걸러져 사용자에게 오류 메시지를 전달한는 단계다.

· 전처리기

- 파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제가 있는지 확인한다.

- 각 토큰을 테이블 이름, 칼럼 이름, 내장 함수 같은 개체에 매핑해 해당 객체의 존재 여부와 객체 접근 권한 등을 확인한다.

- 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰이 걸러지는 단계다.

· 옵티마이저

- 사용자 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지 결정하는 역할을 담당한다.

- DBMS의 두뇌에 해당한다.

· 실행 엔진

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

자세한 실행 내용: Real MySQL p92

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

- MySQL 서버 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어 오는 역할을 담당한다.

- 핸들러는 결국 스토리지 엔진을 의미한다.

쿼리 캐시

· SQL 실행 결과를 메모리에 캐시하고, 동일한 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환하는 기능이다.

· 데이터 변경은 거의 없고 읽기만 하는 서비스에서는 매우 훌룡한 기능이다. (아주 드문 케이스)

· 문제점: 빠른 응답을 필요로 하는 웹 기반 응용 프로그램에서 매우 중요한 역할을 담당했지만, 테이블 데이터가 변경되면 캐시에 저장된 결과 중 관련된 것은 모두 삭제하는 문제가 있었다. 이는 심각한 동시 처리 성능 저하를 유발했다.

· MySQL 8.0 부터 완전히 제거되었다.

스레드 풀

· 스레드 풀은 작업처리에 사용되는 스레드를 제한된 개수만큼 정해놓고 작업큐에 들어오는 작업들을 하나씩 스레드가 맡아 처리한다.

· MySQL 서버 엔터프라이즈 에디션은 스레드 풀 기능을 제공하고, 커뮤니티 버전은 제공하지 않는다. 하지만 커뮤니티 버전도 플러그인 형태로 Percona Server의 스레드 풀을 사용할 수 있다.

- 엔터프라이즈 버전 스레드 풀은 MySQL 서버 프로그램에 내장되어 있고, Percona Server는 플러그인 형태로 제공한다.

· 목적: 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많아도,

MySQL 서버의 CPU가 제한된 개수의 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이다.

· 주의: 스케줄링 과정에서 CPU 시간을 제대로 확보하지 못하는 경우, 쿼리 처리가 더 느려질 수도 있다.

Percona Server 스레드 풀

· thread_pool_size 시스템 변수로 스레드 그룹의 개수를 조정할 수 있다. 기본 값: CPU 코어 개수

· 일반적으로 CPU 코어의 개수와 맞추는 것이 CPU 프로세서 친화도를 높이는 데 좋다.

TODO: thread_pool_oversubscribe

· 스레드 그룹의 모든 스레드가 일을 처리하고 있다면,

1. 스레드 풀은 해당 스레드 그룹에 새로운 작업 스레드를 추가하거나,

2.기존 작업 스레드가 처리를 완료할 때까지 기다릴지 여부를 판단해야한다.

- thread_pool_stall_limit 시스템 변수에 정의된 밀리초만큼 작업 스레드가 지금 처리 중인 작업을 끝내지 못하면 새로운 스레드를 생성해서 스레드 그룹에 추가한다. (스레드 풀의 타이머 스레드가 주기적으로 스레드 그룹의 상태를 체크)

- 응답 시간에 민감한 서비스는 해당 시스템 변수를 적절히 낮춰서 설정해야 한다.

- 이때, 전체 스레드 풀의 스레드 개수는 thread_pool_max_thread 시스템 변수에 설정된 개수를 넘을 수 없다.

· 선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있다.

- 먼저 시작된 트랜잭션 내에 속한 SQL을 빨리 처리해주면 해당 트랜잭션이 가지고 있는 잠금이 빨리 해제되고 잠금 경합을 낮춰 전체적인 처리 성능을 향상시킬 수 있다.

트랜잭션 지원 메타데이터

· 데이터 딕셔너리 or 메타데이터: 테이블 구조 정보와 스토어드 프로그램 등의 정보

· MySQL 5.7 이전: 테이블 구조를 FRM 파일에 저장하고 일부 스토어드 프로그램 또한 파일(*.TRN, *.TGF, *.PAR 등) 기반으로 관리

- 문제점: 파일 기반의 메타데이터는 생성 및 변경 작업이 트랙잰션을 지원하지 않는다. 때문에 테이블의 생성 또는 병경 도중 MySQL 서버가 비정상적으로 종료되면 일관되지 않은 상태로 남는 문제가 있었다.

· MySQL 8.0 이후: 테이블 구조 정보 또는 스토어드 프로그램 코드 관련 정보를 모두 InnoDB 테이블에 저장

- 시스템 테이블과 데이터 딕셔너리 정보를 모두 mysql DB에 저장한다.

- 시스템 테이블: MySQL 서버가 작동하는 데 기본적으로 필요한 테이블 ex) 사용자 인증과 권한에 관련된 테이블

- mysql DB는 통째로 mysql.idb라는 이름의 테이블스페이스에 저장된다.

- 스키마 변경 작업 중 MySQL 서버가 비정상 종료되어도, 스키마 변경이 완전한 성공 또는 완전한 실패로 정리된다.

- InnoDB 스토리지 엔진 이외의 스토리지 엔진을 사용하는 테이블은 SDI(Serialized Dictionary Information) 파일에 저장된다. 이 파일은 기존의 FRM 파일과 동일한 역할을 한다.

- 데이터 딕셔너리 테이블 조회 방법: Real MySQL p96

InnoDB 스토리지 엔진 아키텍처

· 레코드 기반 잠금을 제공하여, 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다.

https://velog.io/@sa46lll/Real-MySQL-04-2.-InnoDB-%EC%8A%A4%ED%86%A0%EB%A6%AC%EC%A7%80-%EC%97%94%EC%A7%84-%EC%95%84%ED%82%A4%ED%85%8D%EC%B2%98

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

· InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링되어 저장된다.

- 즉, 테이블의 레코드가 프라이머리 키 값으로 정렬되어 저장되고, 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용한다.

- 클러스터링 인덱스를 통해 레인지 스캔이 상당히 빨리 처리된다.

- 오라클 DBMS의 IOT(Index organized table)와 동일한 구조가 InnoDB에서는 일반적인 테이블 구조가 된다.

· 쿼리의 실행 계획에서 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높다.

· MyISAM은 클러스터링 키를 지원하지 않는다.

- MyISAM은 프라이머리 키와 세컨더리 인덱스는 구조적으로 아무런 차이가 없다.

- MyISAM에서 프라이머리 키는 유니크 제약을 가진 세컨더리 인덱스일 뿐이다.

- MyISAM 테이블의 프라이머리 키를 포함한 모든 인덱스는 물리적인 레코드의 주소 값(ROWID)을 갖는다.

외래 키 지원

· 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고, 변경 시에 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하다. 때문에 잠근이 여러 테이블로 전파되고, 그로 인해 데드락이 발생할 때가 많아 주의해야한다.

· 외래키는 InnoDB 스코리지 엔진 레벨에서 지원하는 기능으로 MyISAM과 MEMORY 테이블에서 사용할 수 없다.

· foreign_key_checks 시스템 변수를 OFF로 설정하면, 외래 키 관계에 대한 체크 작업을 일시적으로 멈출 수 있다.

- 이때, 부모 테이블에 대한 작업(ON DELETE CASCADE와 ON UPDATE CASCADE)도 무시하게 된다.

TODO: ON DELETE CASCADE와 ON UPDATE CASCADE

- GLOBAL과 SESSION 모두로 설정 가능하므로, 반드시 현재 작업을 실행하는 세션에서만 외래 키 기능을 멈추게 해야 한다.

mysql> SET foreign_key_checks=OFF;

mysql> SET SESSION foreign_key_checks=OFF;

MVCC(Multi Version Concurrency Control)

· 멀티 버전은 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미다.

· InnoDB는 언두 로그를 통해 MVCC를 구현한다.

· 목적: 잠금을 사용하지 않는 일관된 읽기를 제공한다.

· 일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이다.

· 작동 원리:

아래와 같이 InnoDB에 member 테이블이 저장되어 있다고 가정하자.

해당 테이블에 UPDATE 문장을 실행하자.

mysql> UPDATE member SET area='경기' WHERE id = 12;

그러면 InnoDB 내부는 다음과 같이 변경된다. InnoDB 버퍼 풀은 새로운 값인 '경기'로 업데이트 된다.

이때 COMMIT이나 ROLLBACK이 되지 않은 상태에서 다른 사용자가 다음과 같은 쿼리로 작업 중인 레코드를 조회하면 어디에 있는 데이터를 조회할까?

mysql> SELECT * FROM WHERE id =12;

MySQL 서버의 transactional_isolation 시스템 변수에 설정된 격리 수준에 따라 다르다.

격리 수준이 READ_UNCOMMITTED인 경우 InnoDB 버퍼 풀이나 데이터 파일로부터 변경되지 않은 데이터를 읽어 반환한다.

이외에 다른 격리 수준에서는 변경 이전의 내용을 보관하는 언두 영역의 데이터를 반환한다.

이때, 트랜잭션이 길어지면 언두 영역에서 관리하는 예전 버전의 데이터를 무한히 많아질 수 있다.

이 상태에서 COMMIT 명령어를 실행하면 지금 상태를 영구적인 데이터로 만들고, 언두 영역을 필요로 하는 트랜잭션이 더 없을 때 언두 영역을 삭제한다. 하지만 롤백을 실행하면 언두 영역에 있는 백업된 데이터를 InnoDB 버퍼 풀로 다시 복구하고, 언두 내용을 삭제한다.

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

· InnoDB는 MVCC 기술을 이용해 SERIALIZABLE을 제외한 다른 격리 수준에서 INSERT와 연결되지 않은 순수한 읽기 작업은 다른 트랜잭션의 변경과 관계 없이 항상 잠금을 대기하지 않고 바로 실행된다.

-즉, 특정 사용자가 레코드를 변경하고, 아직 커밋을 수행하지 않았다 하다라도 이 변경 트랜잭션이 다른 사용자의 SELECT 작업을 방해하지 않는다.

자동 데드락 감지

· InnoDB는 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프(Wait-for List) 형태로 관리한다.

· InnoDB는 데드락 감지 스레드를 가지고 있어서 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아 강제 종료한다. 일반적으로 언두 로그 레코드를 더 적게 가진 트랜잭션이 롤백 대상이된다.

· innodb_table_locks 시스템 변수를 활성화하면 InnoDB 스토리지 엔진 내부의 레코드 잠금 뿐만 아니라 테이블 레벤 잠금까지 감지할 수 있다. 특별한 이유가 없다면 활성화하자.

· 동시성 처리 스레드가 매우 많아지거나, 트랜잭션이 가진 잠금이 많아지면 데드락 감지 스레드가 느려져 서비스에 악영할을 미칠 수 있다. 이때, innodb_dealock_detect 시스템 변수를 OFF로 설정하면 데드락 감지 스레드는 작동하지 않는다.

하지만 데드락 상황에서 무한정 대기하는 문제가 발생할 수 있다. 이때, innodb_lock_wait_timeout 시스템 변수를 활성화하면 데드락 상황에서 일정 시간이 지나면 자동으로 요청이 실패하고 에러 메시지를 반환하게 된다. (따라서 앞선 두 시스템 변수는 꼭 함께 사용하자)

TODO: 자동화된 장애 복구

TODO: InnoDB 버퍼 풀

· 디스크 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다.

· 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이 한다.

- 데이터를 변경하는 INSERT, UPDATE, DELETE 쿼리 발생시 발생하는 랜덤한 디스크 작업 횟수를 줄일 수 있다.

· InnoDB 스토리지 엔진의 가장 핵심적인 부분이다.

TODO: Double Write Buffer

· 파셜 페이지(Partial-page) or 톤 페이지(Ton-page): 페이지가 일부만 기록되는 현상이다. 하드웨어 오작동, 시스템 비정상 종료 등으로 발생할 수 있다.

· InnoDB의 리두 로그는 리두 로그 공간의 낭비를 막기 위해 페이지의 변경된 내용만을 기록한다. 이로 인해 InnoDB에서 더티 페이지를 디스크 파일로 플러시할 때 일부만 기록되는 문제가 발생하면 해당 페이지의 내용은 복구할 수 없을 수도 있다.

이를 막기 위해 InnoDBSMS Double-Write 기법을 이용한다.

언두 로그

· InnoDB에서 트랜잭션과 격리 수준을 보장하기 위해 DML(INSERT, UPDATE, DELETE)로 변경되기 이전 버전의 데이터를 별도로 백업한다. 이 백업 데이터를 언두 로그(Undo Log)라고 한다.

· 용도: 트랜잭션 보장, 격리 수준 보장

- 트랜잭션이 롤백되면 트랜잭션 도중 변경된 데이터를 변경 전 데이터로 복구해야 하는데, 이때 언두 로그에 백업해둔 이전 버전의 데이터를 이용한다.

- 격리 수준 보장은 앞서 설명되었다.

TODO: 언두 로그 레코드 모니터링, 언두 테이블스페이스 관리

TODO: 체인지 버퍼

· 인덱스 변경에 따른 자원 소모를 줄여주는 기술

탄생 배경

· 문제: RDBMS에서 레코드가 INSERT 또는 UPDATE 될 때 데이터 파일을 변경하는 작업과 해당 테이블에 포함된 인덱스를 업데이트하는 작업이 필요하다. 인덱스를 업데이트하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱스가 많다면 상당히 많은 자원을 소모한다.

· 해결: InnoDB는 변경해야할 인덱스 페이지가 디스크로부터 읽어와 업데이트해야 한다면, 즉시 실행하지 않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시킨다. 이때 사용하는 임시 메모리 공간을 ‘체인지 버퍼’라고 한다.

특징

· 사용자에게 결과를 전달하기 전에 반드시 중복 여부를 체크해야 하는 유니크 인덱스는 체인지 버퍼를 사용할 수 없다.

· 체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해 병합된다.

이를 ‘체인지 버퍼 머지 스레드’라고 한다.

TODO: 리두 로그 및 로그 버퍼

· 하드웨어나 소프트웨어 등 여러 문제로 인해 MySQl 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치다.

· 탄생 배경

· 문제: 거의 모든 DBMS에서 데이터 파일은 쓰기보다 읽기 성능을 고려한 자료구조를 가지고 있어 데이터 파일 쓰기는 디스크 랜덤 액세스가 필요하다. 그래서 변경된 데이터를 데이터 파일에 기록하려면 큰 비용이 필요하다.

· 해결: 쓰기 비용이 낮은 자료 구조를 가진 리두 로그를 제공하여, 비정상 종료가 발생하면 리두 로그의 내용을 이용해 데이터 파일을 다시 서버가 종료되기 직전 상태로 복구한다. 이러한 이유로 대부분 데이터베이스 서버는 데이터 변경 내용을 로그로 먼저 기록한다.

데이터베이스 서버는 ACID도 중요하지만, 성능도 중요하기 때문에 데이터 파일뿐만 아니라 리두 로그를 버퍼링할 수 있는 InnoDB 버퍼 풀이나 리두 로그를 버퍼링할 수 있는 로그 버퍼 같은 자료 구조도 가지고 있다.

특징

· 트랜잭션 4가지 요소인 ACID 중 D(Durable)에 해당하는 영속성과 밀접하게 연관된다.

TODO: 어댑티브 해시 인덱스

· InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스다.

· innodb_adaptive_hash_index 시스템 변수를 이용해서 활성화 및 비활성화할 수 있다.

· 도입 목적: B-Tree의 검색 시간을 줄여주기 위해 도입되었다.

· 작동 방식: 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 어댑티브 해시 인덱스를 검색해서 레코드가 저장된 데이터 페이지를 즉시 찾아간다.

- 이렇게하면, B-Tree를 루트 노드부터 리프 노드까지 찾아가는 비용이 없어지고, CPU는 적을 일을 하지만 쿼리 성능은 빨라진다. 따라서 더 많은 쿼리를 동시에 처리할 수 있다.

· 특징

- 해시 인덱스를 ‘인덱스 키 값’과 해당 인덱스 키 값이 저장된 ‘데이터 페이지 주소’ 쌍으로 관리된다.

- 인덱스 키 값은 ‘B-Tree 인덱스 고유번호(ID)와 B-Tree 인덱스 실제 키 값’ 조합으로 생성된다.

- 인덱스 키 값에 ‘B-Tree 인덱스의 고유번호’가 포함되는 이유는 InnoDB 스토리지 엔진에서 어댑티브 해시 인덱스는 하나만 존재하기 때문이다. 즉, 특정 키 값이 어느 인덱스에 속한 것인지 구분하기 위해 필요하다.

InnoDB vs MyISAM vs MEMORY 스토리지 엔진

· MySQL 8.0 버전 이전에는 MyISAM이 활용되는 경우 많았다.

- MySQL 서버의 시스템 테이블(사용자 인증 관련 정보와 복제 관련 정보 저장)로 사용

- InnoDB에서 제공하지 않았던 전문 검색, 공간 좌표 검색 기능 지원

· MySQL 8.0 버전 이후에는 MyISAM는 거의 활용되는 않는다.

- MySQL 서버의 시스템 테이블이 InnoDB 스토리지 엔진으로 교체되었다.

- InnoDB에 전문 검색, 공간 좌표 검색 기능 지원을 시작했다.

- InnoDB의 기능이 개선되는 만큼 MyISAM 스토리지 엔진의 기능은 도태되고 있고,

이후 버전에서 MyISAM 스토리지 엔진은 없어질 것으로 예상되고 있다.

· MEMORY 스토리지 엔진 또한 InnoDB에 비해 우세한 점이 거의 없다.

- 테이블락을 사용하기 때문에 InnoDB보다 동시성 저리에서 떨어진다.

- 하나의 스레드에서만 데이터를 읽고 쓴다면 InnoDB보다 빠를 수 있다.

- 이전 버전과 호환성 유지 차원으로 유지될 뿐 향 후 버전에서 제거될 것으로 예상된다.

MyISAM 스토리지 엔진 아키텍처

키 캐시

· 인덱스만 대상으로 하며, 인덱스의 디스크 쓰기 작업에 대해서 부분적으로 버퍼링 역할을 한다.

· InnoDB 버퍼 풀과 비슷한 역할을 한다.

운영체제의 캐시 및 버퍼

· MyISAM 테이블 데이터는 디스크로부터 I/O를 해결해 줄 만한 어떠한 캐시나 버퍼링 기능도 제공하지 않는다. 따라서 MyISAM 테이블의 데이터 읽기나 쓰기 작업은 항상 운영체제의 디스크 읽기 또는 쓰기 작업으로 요청될 수밖에 없다.

· 대부분의 운영체제는 디스크로 읽기 쓰기에 대한 캐시나 버퍼링 메커니즘을 탑재하므로,

MySQL 서버가 요청하는 디스크 읽기 작업을 위해 매번 디스크 파일을 읽지는 않는다.

· 운영체제 캐시 기능은 InnoDB처럼 데이터 특성을 알고 전문적으로 캐시나 버퍼링을 하지는 못한다.

· 문제: 운영체제의 캐시 공간은 남는 메모리를 사용하는 것이 기본원칙이다. 하지만 MySQL이나 다른 애플리케이션에서 메모리를 모두 사용해 버리면 운영체제가 캐시 용도로 사용할 메모리 공간이 없어진다. 이런 경우 MyISAM 테이블 데이터를 캐시하지 못하여 쿼리 처리가 느려진다.

· 해결: 데이터베이스에서 MyISAM 테이블을 주로 사용한다면 운영체제가 사용할 수 있는 캐시 공간을 위해 충분한 메모리를 비워둬야한다.

- MyISAM이 주로 사용된다면, 일반적으로 키 캐시는 최대 물리 메모리의 40% 이상을 넘지 않고, 나머지 메모리 공간은 운영체제가 자체적인 파일 시스템을 위한 캐시 공간을 마련할 수 있도록 한다.

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

· InnoDB와 다르게 MyISAM 테이블은 프라이머리 키에 의한 클러스터링 없이 데이터 파일이 힙 공간처럼 활용된다.

- 즉, 레코드가 프라이머리 키와 무관하게 INSERT되는 순서대로 데이터 파일에 저장된다.

· MyISAM 테이블의 레코드는 모두 ROWID라는 물리적인 주솟값을 갖고, 프라이머리 키와 세컨더리 인덱스는 모두 데이터 파일에 저장된 레코드의 ROWID 값을 포인터로 갖는다.

· ROWID는 가변 긴이와 고정 길이 두 가지 방법으로 저장된다.

- 고정 길이: 가질 수 있는 레코드 개수가 한정되며, ROWID 값으로 4바이트 정수를 사용한다. 이때, 레코드가 INSERT된 순번이 ROWID로 사용된다.

- 가변 길이: ROWID는 최대 myisam_data_pointer_size 시스템 변수에 설정된 바이트 수만큼의 공간을 사용한다. 첫 번째 바이트는 ROWID 길이를 저장하는 용도로 사용하고 나머지 공간은 실제 ROWID를 저장하는데 사용된다. 이때, 데이터 파일에서 레코드 위치가 ROWID로 사용된다.

TODO: MySQL 로그 파일

에러 로그 파일

제너럴 쿼리 로그 파일

슬로우 쿼리 로그

출처

Real MySQL 8.0 1권

'데이터베이스 > Real MySQL' 카테고리의 다른 글

[Real MySQL 8.0] 5장 - 트랜잭션과 잠금 (동시성에 영향을 미치는 요소) (0)	2022.01.11
[Real MySQL 8.0] 6장 - 데이터 압축(페이지 압축과 테이블 압축) (0)	2022.01.04