[MySQL] MySQL 엔진 아키텍처

Real MySQL 8.0 1권

Real MySQL 8.0 읽으면서 배웠던 부분들을 정리한 글입니다.

 

MySQL 엔진 아키텍처

---

https://dev.mysql.com/doc/refman/8.0/en/pluggable-storage-overview.html

 

MySQL 엔진

요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS의 두뇌에 해당하는 처리를 수행한다. MySQL엔진은 커넥션 핸들러, SQL 파서, 전처리기, 옵티마이저로 구성되어 있다. 또한 MySQL은 표준 SQL 문법을 지원하기 때문에 표준 문법에 따라 작성된 쿼리는 타 DBMS와 호환되어 실행될 수 있다.

 

스토리지 엔진

디스크 스토리지에 실제 데이터를 저장하고 읽어오는 부분을 전담한다. 하나의 MySQL 서버에서 MySQL 엔진은 하나이지만, 스토리지 엔진은 여러개를 동시에 사용할 수 있다. 각 스토리지 엔진은 성능 향상을 위해 키 캐시나 InnoDB 버퍼 풀 같은 기능을 내장하고 있다.

 

핸들러 API

MySQL엔진의 쿼리 실행기에서 스토리지 엔진으로의 요청을 핸들러 요청이라고 하고, 이때 사용되는 API를 핸들러 API라고 한다. SHOW GLOBAL STATUS LIKE 'Handler%' 명령어를 통해 데이터 작업의 양을 알 수 있다.

 

 

MySQL 스레딩 구조

MySQL 서버는 스레드 기반으로 동작하며, 포그라운드와 백그라운드로 구분한다. MySQL 서버에서 실행중인 스레드 목록은 performance_schema.threads 테이블을 통해 확인할 수 있다.

 

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

포그라운드 스레드는 최소한 MySQL 접속된 클라이언트 수만큼 존재하며, 요청된 쿼리 문장을 처리한다. 커넥션이 종료된 스레드는 스레드 캐시로 돌아간다. 스레드 캐시에 유지할 수 있는 최대 스레드 개수는 thread_cache_size 시스템 변수로 설정한다.

데이터를 MySQL의 버퍼나 캐시로부터 가져온다. 버퍼나 캐시에 없는 경우 디스크 혹은 인덱스 파일에서 읽어온다. MyISAM은 디스크 쓰기 작업까지 포그라운드 스레드가 처리하지만, InnoDB는 백그라운드 스레드가 처리한다.

 

백그라운드 스레드

InnoDB의 백그라운드 작업 목록

• Insert Buffer 병합
• 로그를 디스크로 기록
• InnoDB 버퍼 풀의 데이터를 디스크로 기록
• 데이터를 버퍼로 읽기
• 잠금, 데드락 모니터링

innodb_write_io_threads, innodb_read_io_threads를 통해 시스템 변수로 스레드 개수를 설정할 수 있다. 데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리되기 때문에 읽기 스레드를 많이 설정할 필요는 없다. 이에 비해 쓰기 스레드는 일반 내장 디스크에선 2~4 정도, DAS나 SAN과 같은 스토리지에선 충분히 설정하는 것이 좋다.

일반적인 상용 DBMS(InnoDB 포함)에선 읽기 작업과 달리 쓰기 작업은 버퍼링하여 일괄 처리하지만 MyISAM의 경우 일반적인 쿼리는 버퍼링 기능을 사용할 수 없다.

 

 

메모리 할당 및 사용 구조

글로벌 메모리 영역

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

글로벌 메모리 영역은 MySQL 서버가 시작되면서 운영체제로부터 할당된다. 운영체제의 종류에 따라 MySQL 서버의 메모리 할당 방식이 상당히 복잡하기 때문에, MySQL 서버가 사용하는 정확한 메모리의 양을 측정하는 것은 쉽지 않다.

클라이언트 스레드 수와 무관하게 메모리 공간이 할당되며, 모든 스레드에 의해 공유 된다.

 

로컬 메모리 영역

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

세션 메모리 영역이라고도 하며, MySQL 서버 상에 존재하는 클라이언트 스레드가 쿼리를 처리하는데에 사용하는 메모리 영역이다. 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되지 않는다. 쿼리의 용도별로 필요할 때만 공간이 할당되기 때문에 MySQL이 메모리 공간을 할당조차 하지 않을 수도 있다.

 

 

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

플러그인 모델은 MySQL의 독특한 구조 중 하나이다. 스토리지 엔진 뿐만 아니라 검색어 파서, 사용자 인증 등도 모두 플러그인으로 구현되어 제공된다.

 

 

컴포넌트

기존의 플러그인 아키텍처를 대체하기 위해 컴포넌트 아키텍처가 지원된다. SELECT * FROM mysql.component 명령어를 통해 설치된 컴포넌트를 확인할 수 있다.

 

플러그인 아키텍처 단점

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

 

 

쿼리 실행 구조

https://distributedsystemsauthority.com/mysql-performance-tuning-part-1/

쿼리 파서

사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리하여 트리 형태의 구조로 만들어 내는 작업을 의미한다. 쿼리 문장의 문법 오류는 이 과정에서 발견되어 사용자에게 전달된다.

 

전처리기

파서 과정에서 만들어진 파서 트리를 기반으로 쿼리에 구조적 문제점이 있는지 확인한다. 쿼리의 테이블, 컬럼, 내장 함수 들을 객체로 매핑하여 각 객체의 존재 여부와 접근 권한을 확인한다.

 

옵티마이저

쿼리 문장을 가장 빠르고 적은 비용으로 처리하는 방법을 결정하는 역할을 담당한다.

 

실행 엔진

옵티마이저에서 만들어진 계획대로 각 핸들러들을 연결하는 역할을 수행한다.

 

핸들러(스토리지 엔진)

실행 엔진의 요청에 따라 디스크에 데이터를 저장하거나 읽어오는 역할을 담당한다.

 

 

쿼리 캐시

쿼리 캐시는 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환하기 때문에 매우 빠른 성능을 보이지만, 데이터의 변경이 빈번히 일어날수록 동시 처리 성능 저하를 유발하게 되었다. 따라서 MySQL은 8.0 버전부터 쿼리 캐시를 삭제했다.

 

 

스레드 풀

스레드 풀은 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있도록 서버의 자원 소모를 줄이는 것이 목적이다. 스레드 풀을 사용함으로써 CPU 프로세서의 친화도도 높이고 불필요한 컨텍스트 스위치를 줄여서 오버헤드를 낮출 수 있다. 하지만 CPU 시간을 제대로 확보하지 못하는 경우에는 쿼리 처리가 오히려 더 느려지는 사례도 발생할 수 있으므로 주의하자.

 

MySQL의 스레드 풀은 엔터프라이즈 에디션에서만 지원한다. 이를 대신하여 플러그인 중 하나인 Percona Server의 스레드 풀을 사용할 수 있다.

 

Percona Server의 스레드 풀

Percona Server의 스레드 풀은 플러그인 형태로 작동하도록 구현되어 있다. Percona Server의 스레드 풀은 기본적으로 CPU 코어의 개수만큼 스레드 그룹을 생성한다. 이는 thread_pool_size를 통해 설정할 수 있다. 또한 선순위 큐와 후순위 큐를 이용하여 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공한다.

스레드 그룹의 모든 스레드가 일을 처리하고 있다면 새로운 작업 스레드를 그룹에 추가할지, 기존 스레드의 처리가 완료될 때까지 기다릴지 여부를 판단해야 한다. 타이머 스레드는 thread_pool_stall_limie에 정의된 밀리초 이상으로 작업을 처리중이라면 새로운 스레드를 생성해서 스레드 그룹에 추가한다. 하지만 이 스레드의 개수는 thread_pool_max_threads 에 설정된 개수를 넘을 수 없다.

 

 

트랜잭션 지원 메타데이터

데이터베이스 서버의 테이블 구조 정보와 스토어드 프로그램 등의 정보를 데이터 딕셔너리 또는 메타데이터라고 한다.

MySQL 5.7 버전까지는 테이블의 구조를 FRM 파일에 저장하고 일부 스토어드 프로그램 또한 파일 기반으로 관리했다. 하지만 이러한 파일 기반의 메타데이터는 트랜잭션을 지원하지 않기 때문에 생성 또는 변경 도중 서버가 종료되면 일관성이 깨진다.

하지만 MySQL 8.0 버전부터는 이를 해결하기 위해 메타데이터 정보를 모두 InnoDB 테이블에 저장한다. 이는 mysql DB에 저장하고 mysql DB는 mysql.idb 라는 이름의 테이블스페이스에 저장된다. 실제로 information_schema의 테이블 구조와 관련된 테이블은 모두 mysql 테이블의 뷰로 만들어져있는 것을 알 수 있다.

 

 

 

InnoDB 스토리지 엔진 아키텍처

---

InnoDB는 MySQL에서 사용할 수 있는 스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금을 제공하며, 그 때문에 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다.

 

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

InnoDB의 모든 테이블은 프라이머리 키를 기준으로 클러스터링되어 저장된다. 따라서 프라이머리 키 값의 순서대로 디스크에 저장되며, 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용한다. 이 때문에 프라이머리 키는 다른 인덱스에 비해 실행 계획에서 비중이 높게 설정된다.

 

 

외래 키 지원

외래 키에 대한 지원은 InnoDB 스토리지 엔진 레벨에서 지원하는 기능으로 MyISAM 이나 MEMORY 테이블에서는 사용할 수 없다. 외래 키는 개발 환경에서 좋은 가이드 역할을 할 수 있지만, 쿼리 실행에서 잠금이 여러 테이블로 전파되기 때문에 데드락이 발생할 때가 많으므로 주의하는 것이 좋다. 또한 외래 키 관계 체크로 인해 수동으로 데이터를 적재하거나 스키마 변경시 작업이 실패할 수 있다. 이는 foreign_key_checks 옵션으로 일시적으로 비활성화할 수 있지만 데이터를 적재한 후엔 다시 활성화해주어야 한다.

 

 

MVCC(Multi Version Concurrency Control)

레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능으로 버전 관리를 통해 잠금을 사용하지 않는 일관된 읽기가 가능하다. InnoDB는 이를 Undo log를 이용하여 구현하고 있다.

 

 

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

InnoDB 스토리지 엔진은 MVCC 기술을 이용하여 잠금을 걸지 않고 읽기 작업을 수행한다. (격리 수준 SERIALIZABLE 제외). 레코드에 대해 변경 작업이 이루어지더라도 다른 트랜잭션의 SELECT 작업을 방해하지 않는다. 이를 잠금 없는 일관된 읽기라고 한다. 격리 수준이 READ_UNCOMMITED라면 InnoDB의 버퍼 풀 데이터를 읽고 READ_COMMITED, REPEATABLE_READ라면 언두 로그 데이터를 읽게 된다.

 

 

자동 데드락 감지

InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 목록을 그래프(Wait-for List) 형태로 관리한다. 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사하여 교착 상태에 빠진 트랜잭션을 강제 종료한다. 트랜잭션 중 언두 로그의 양이 가장 작은 트랜잭션이 롤백을 실행해도 서버의 부하를 덜 일으키기 때문에 언두 로그 양으로 종료 대상을 선정한다.

InnoDB 스토리지 엔진은 상위 레이어인 MySQL 엔진의 테이블 잠금을 볼 수 없어 데드락 감지가 불확실할 수도 있다. innodb_tagle_locks 시스템 변수를 활성화하면, InnoDB 스토리지 엔진 내부의 레코드 잠금뿐만 아니라 테이블 레벨의 잠금까지 감지할 수 있다.

동시 처리 스레드가 많은 경우 데드락 감지 스레드가 잠금 목록에 잠금을 걸면서 많은 CPU 자원을 소모할 수 있다. 이를 innodb_deadlock_detect으로 데드락 감지를 비활성화한 후 innodb_lock_wait_timeout으로 잠금 타임아웃 시간을 작게 설정하는 방법으로 해결할 수 있다. 높은 동시성 처리를 요구하는 서비스라면 이 방법을 고려해보자.

 

 

자동화된 장애 복구

MySQL이 장애로 인해 예기치 않게 종료되었을 경우, innodb_force_recovery 시스템 변수를 1~6으로 설정하여 MySQL 서버를 다시 시작할 수 있다. MySQL 서버를 재시작한 후에는 mysqldump 프로그램이나 SELECT INTO OUTFILE… 명령어를 이용하여 덤프 후 데이터베이스를 다시 구축하는 것이 좋다.

 

innodb_force_recovery 시스템 변수

• 1(SRV_FORCE_IGNORE_CORRUPT)
  • InnoDB의 테이블 스페이스의 데이터나 인덱스 페이지에서 손상된 부분 무시
• 2(SRV_FORCE_NO_BACKGROUND)
  • 백그라운드 스레드 가운데 메인 스레드를 시작하지 않고 MySQL 서버를 시작한다.
• 3(SRV_FORCE_NO_TRX_UNDO)
  • 언두 영역의 데이터, 리두 로그 영역의 데이터를 사용하여 장애 시점의 데이터 상태를 만들어낸 후, 정상적인 경우 커밋되지 않은 트랜잭션은 롤백을 수행하지만 해당 설정 모드는 롤백하지 않고 재시작하게 된다.
• 4(SRV_FORCE_NO_IBUF_MERGE)
  • 인덱스 변경에 사용되는 인서트 버퍼의 내용을 무시하고 서버를 시작한다.
  • 인서트 버퍼는 실제 데이터가 아닌 인덱스에 관련된 부분이므로 덤프를 사용하면 데이터의 손실없이 복구할 수 있다.
• 5(SRV_FORCE_NO_UNDO_LOG_SCAN)
  • 언두 로그를 모두 무시하고 서버를 시작한다.
  • 커밋되지 않았던 작업도 커밋된 것처럼 처리되므로 잘못된 데이터가 데이터베이스에 남을 수 있다.
• 6(SRV_FORCE_NO_LOG_REDO)
  • 리두 로그를 모두 무시하고 서버를 시작한다.

 

InnoDB 버퍼 풀

InnoDB 스토리지 엔진에서 가장 핵심적인 부분으로, 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다. 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 한다.

 

버퍼 풀의 크기 설정

InnoDB 버퍼 풀은 innodb_buffer_pool_size 시스템 변수로 설정할 수 있으며, 동적으로 크기를 변경할 수 있다. 운영체제의 전체 메모리 공간의 50%정도만 버퍼풀로 설정하고 조금씩 올려가면서 최적점을 찾는 것이 좋다. innodb_buffer_pool_instances 변수를 통해 버퍼 풀의 개수를 설정할 수 있으며 디폴트 값은 8이다. 버퍼 풀 인스턴스당 5GB 메모리를 사용할 수 있도록 설정하는 것이 좋다.

 

버퍼 풀의 구조

버퍼 풀의 페이지 크기 조각을 관리하기 위해 LRU, Flush, Free 리스트 자료구조를 사용한다.

 

LRU 리스트

디스크로부터 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼풀에 메모리에 유지하여 디스크 읽기를 최소화하기 위해 사용. 거의 사용되지 않는 데이터 페이지는 LRU의 끝으로 밀려나 버퍼 풀에서 제거된다.

 

InnoDB 스토리지 엔진에서 데이터를 찾는 과정

1. 어댑티브 해시 인덱스와 B-Tree 인덱스를 이용하여 데이터 페이지가 버퍼 풀에 있는지 검사
   1. 없다면 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재, 해당 페이지에 대한 포인터를 LRU 헤더에 추가. 데이터 페이지가 실제로 읽혔을 경우 MRU 방향으로 승급
   2. 있다면 해당 페이지 포인터를 MRU 방향으로 승급
2. 필요한 데이터가 자주 접근되었다면 어댑티브 해시 인덱스에 추가

 

Flush 리스트

디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(더티 페이지) 목록 관리

 

Free 리스트

InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록

 

버퍼 풀과 리두 로그

버퍼 풀은 데이터베이스 서버의 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링의 기능을 제공하는데, 버퍼 풀의 메모리 공간을 늘리는 것은 이 데이터 캐시 기능을 향상시키는 것을 의미한다. 쓰기 버퍼링의 성능 향상을 위해서 버퍼 풀의 메모리 공간과 함께 리두 파일의 크기도 고려해야 한다.

버퍼 풀은 디스크에서 읽은 상태에서 전혀 변경되지 않은 클린 페이지와, 변경된 더티 페이지를 가지고 있다. 더티 페이지가 버퍼 풀에 무한정 머무를 수 없기 때문에 InnoDB 스토리지 엔진은 주기적으로 체크포인트 이벤트를 발생시켜 변경된 데이터를 디스크로 동기화 시킨다. 더티 페이지는 특정 리두 로그 엔트리와 관계를 가지고 순환 고리처럼 사용한다. 전체 리두 로그 파일에서 아직 디스크로 동기화되지 않은 공간은 재사용이 불가능하며, 이를 활성 리두 로그 공간이라고 한다.

가장 최근 체크포인트의 LSN이 활성 리두 로그 공간의 시작점이 된다. 가장 최근 체크 포인트의 LSN과 마지막 리두 로그 엔트리의 LSN 차이를 Checkpoint Age라고 하며, 활성 리두 로그 공간의 크기를 일컫는다.

 

버퍼 풀 플러시

플러시 리스트 플러시

리두 로그 공간의 재활용을 위해 오래된 리두 로그 엔트리의 사용 공간을 비우는 작업. 더티 페이지가 많다면 디스크 쓰기 폭발 현상이 발생할 수 있는데, 이를 위해 InnoDB 엔진은 어댑티브 플러시라는 기능을 제공한다. 이는 리두 로그의 증가 속도를 분석하여 적절한 수준의 더티 페이지가 버퍼 풀에 유지될 수 있도록 디스크 쓰기를 실행해 준다.

 

LRU 리스트 플러시

LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거하는 작업

 

버퍼 풀 상태 백업 및 복구

디스크의 데이터가 버퍼 풀에 적재되어 있는 상태를 워밍업이라고 표현하는데, 이는 몇십 배의 쿼리 처리 속도 차이를 보인다. 이를 위해 innodb_buffer_pool_dump_now를 사용하여 버퍼 풀의 상태를 백업하고 복구할 수 있다.

 

버퍼 풀의 적재 내용 확인

information_schema의 innodb_cached_indexes 테이블을 통해 인덱스별로 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재되어 있는지 확인할 수 있다.

 

 

Double Write Buffer

리두 로그는 공간 낭비를 막기 위해 페이지의 변경된 내용만 기록한다. 이로 인해 플러시에 문제가 발생하여 일부만 기록되면 해당 페이지의 내용은 복구할 수 없을 수 있다. 이를 파셜 페이지(Partial-page) 또는 톤 페이지(Torn-page)라고 한다.

이를 해결하기 위한 방법이 Double-Write 기법으로, 더티페이지를 데이터 파일에 기록하기 전에 더티페이지를 묶어 시스템 테이블스페이스의 DoubleWrite 버퍼에 기록한다. 해당 기능은 innodb_doublewrite 시스템 변수로 사용 여부를 결정할 수 있다.

 

 

언두 로그

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

 

언두 로그의 기능

• 트랜잭션 보장
  • 트랜잭션의 롤백시, 데이터 복구 과정에서 언두 로그에 백업한 데이터를 이용한다.
• 격리 수준 보장
  • 트랜잭션 격리 수준이 READ_COMMITED이거나 REPEATABLE_READ일 경우 언두 로그의 데이터를 읽어서 반환한다.

 

언두 로그 레코드 모니터링

대용량의 데이터를 처리하거나 오랜 시간 실행되는 트랜잭션으로 인해 언두 로그의 양이 증가할 수 있다. InnoDB 스토리지 엔진은 언두 로그의 이력을 필요한 만큼 스캔해야 레코드를 찾을 수 있기 때문에 쿼리 성능이 떨어질 수 있다. MySQL 서버의 언두 로그 레코드를 모니터링하여 급증 여부를 확인하자.

 

언두 테이블스페이스 관리

언두 로그가 저장되는 공간을 언두 테이블스페이스라고 한다. MySQL 5.6 이전 버전에서는 언두 로그를 모두 시스템 테이블스페이스에 저장했지만, 이는 MySQL 서버가 초기화될 떄 생성되기 때문에 확장에 한계가 있었다. 이는 MySQL 8.0 으로 업그레이드되면서 언두 로그는 항상 시스템 테이블스페이스 외부의 별도 로그 파일에 기록되도록 개선되었다.

 

구성

하나의 언두 테이블스페이스는 1개 이상 128개 이하의 롤백 세그먼트를 가진다. 롤백 세그먼트는 InnoDB의 페이지 크기를 16바이트로 나눈 값의 개수만큼 언두 슬롯을 가진다.

일반적으로 하나의 트랜잭션은 2개 정도의 언두 슬롯을 필요로 하며, 최대 동시 처리 가능한 트랜잭션의 개수는 InnoDB 페이지 크기 / (16 * 롤백 세그먼트 개수 * 언두 테이블스페이스 개수) 로 계산할 수 있다.

 

Truncate 방법

• 자동 모드
  • InnoDB 스토리지 엔진의 퍼지 스레드가 주기적으로 불필요해진 언두 로그를 삭제하고 운영체제로 반납하는 작업을 한다.
  • innodb_undo_log_truncate로 활성 여부를 설정할 수 있으며 innodb_purge_rseg_truncate_frequency 를 통해 실행되는 빈도 수를 조절할 수 있다.
• 수동 모드
  • 언두 테이블스페이스를 비활성화로 설정하면 퍼지 스레드는 언두 테이블스페이스를 찾아서 운영체제로 반납한다. 이후 다시 활성화하면 된다.
  • 수동 모드는 언두 테이블스페이스가 최소 3개 이상은 되어야 한다.

 

 

체인지 버퍼

RDBMS에서 레코드가 INSERT되거나 UPDATE될 때는 데이터 작업 뿐만 아니라 인덱스 작업도 필요하다. 하지만 인덱스 작업은 디스크를 읽는 작업이 필요하기 때문에 많은 자원을 소모하게 된다. 따라서 InnoDB는 변경해야 할 인덱스 페이지를 버퍼 풀이 아닌 디스크를 통해 읽어와야 할 경우 즉시 실행하지 않고 임시 공간을 사용하게 되는데, 이를 체인지 버퍼라고 한다. 이는 이후 체인지 버퍼 머지 스레드라고 부르는 백그라운드 스레드에 의해 병합된다. 하지만 중복 여부를 체크해야 하는 유니크 인덱스는 체인지 버퍼를 사용할 수 없으니 유의하자.

 

 

리두 로그 및 로그 버퍼

리두 로그는 MySQL 서버가 비정상적으로 종료되었을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치의 역할을 한다. 비정상적인 종료로 인한 데이터는 두 가지 종류고 각 데이터의 복구 방법은 다음과 같다.

• 커밋되었지만 데이터 파일에 기록되지 않은 데이터
  • 리두 로그를 사용하여 데이터 복구
• 롤백되었지만 데이터 파일에 이미 기록된 데이터
  • 리두 로그와 언두 로그 모두 사용하여 데이터 복구

트랜잭션이 커밋될 때마다 리두 로그를 디스크에 기록하는 작업은 많은 부하를 유발하기 때문에 리두 로그를 어느 주기로 디스크에 동기화할지는 innodb_flush_log_at_trx_commit 시스템 변수를 통해 설정할 수 있다. 또한 전체 리두 로그의 파일 크기는 innodb_log_file_size와 innodb_log_files_in_group으로 결정한다.

 

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=parkjy76&logNo=220580358147

• innodb_flush_log_at_trx_commit
  • 0: 트랜잭션이 커밋될 때마다 Log Buffer로 기록, 1초에 한번씩 디스크로 동기화
  • 1: 트랜잭션이 커밋될 때마다 디스크로 기록 및 동기화
  • 2: 트랜잭션이 커밋될 때마다 OS buffer cache로 기록, 1초에 한번씩 디스크로 동기화

 

 

리두 로그 아카이빙

MySQL 8.0 버전부터 InnoDB 스토리지 엔진의 리두 로그를 아카이빙할 수 있는 기능이 추가되었다.

 

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

MySQL 서버가 비정상적으로 종료되어도 데이터 파일에 기록되지 못한 트랜잭션을 복구하기 위해 리두로그는 항상 활성화되어 있다. 트랜잭션이 커밋되어도 데이터 파일은 즉시 디스크로 동기화되지 않는 반면, 리두 로그는 항상 디스크로 기록된다.

MySQL 8.0 버전부터는 데이터를 복구하거나 대용량 데이터를 한번에 적재하는 경우 리두 로그를 비활성화 하는 방법을 통해 데이터의 적재 시간을 단축시킬 수 있다.

 

 

어댑티브 해시 인덱스

어댑티브 해시 인덱스는 사용자가 생성하는 것이 아닌 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이다. 자주 읽히는 데이터 페이지의 키 값을 이용하여 인덱스를 만들어 저장된 데이터 페이지를 즉시 찾아갈 수 있도록 도와준다. 어댑티브 해시 인덱스는 innodb_adaptive_hash_index 변수를 이용하여 활성화 여부를 결정할 수 있다.

키 값은 B-Tree 인덱스의 고유번호(Id)와 B-Tree 인덱스의 실제 키 값의 조합이며, 이 키 값은 버퍼 풀에 로딩된 데이터 페이지의 주소를 저장한다.

 

어댑티브 해시가 불필요한 경우

• 디스크 읽기가 많음
• 특정 패턴의 쿼리가 많음(조인, LIKE 패턴 검색)
• 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽음

 

어댑티브 해시가 필요한 경우

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

어댑티브 해시 인덱스도 다른 인덱스들과 마찬가지로 메모리를 차지하고 데이터를 수정하는 작업에 오버헤드를 유발하기 때문에 항상 성능에 좋지는 않다. 이는 SHOW ENGINE INNODB STATUS를 통해 캐시 히트율을 확인하여 어댑티브 해시의 필요성을 판단할 수 있다.

 

 

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

이전엔 MyISAM이 기본 스토리지 엔진으로 사용되는 경우가 많았지만, MySQL 5.5 버전부터 InnoDB가 기본 스토리지 엔진으로 채택되면서 점차 InnoDB 에서 모든 기능을 구현할 수 있도록 변경되었다. MySQL 8.0에서는 모든 기능이 InnoDB 스토리지 엔진 기반으로 재편되었고, MyISAM 만의 장점이 사라지게 되었다.

 

 

 

MyISAM 스토리지 엔진 아키텍처

---

https://velog.io/@jsj3282/5.-MyISAM-%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의 버퍼 풀과 비슷한 역할을 하지만 인덱스만을 대상으로 한다. 또한 인덱스의 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할을 한다.

 

운영체제의 캐시 및 버퍼

MyISAM은 디스크로의 I/O를 해결할 캐시나 버퍼링 기능을 가지고 있지 않기 때문에 빈번한 디스크 I/O 호출을 발생시킬 수 있다. 따라서 MySQL 의 최대 물리 메모리를 40% 이상 넘지 않도록 설정하고, 나머지 메모리 공간을 운영체제가 사용하여 운영체제의 캐시 공간을 마련하는 것이 좋다.

 

 

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

MyISAM 테이블은 프라이머리 키에 의한 클러스터링 없이, INSERT되는 순서대로 데이터 파일에 저장되어 힙 공간처럼 활용된다. 따라서 저장되는 레코드는 모두 ROWID라는 물리적 주솟값을 가지며 모든 인덱스들은 이 값을 포인터로 가진다.

 

 

 

MySQL 로그 파일

---

에러 로그 파일

MySQL이 실행되는 도중에 발생하는 에러나 경고 메세지가 출력되는 로그 파일이다.

 

보편적인 로그 메시지

• MySQL이 시작하는 과정과 관련된 정보성 및 에러 메시지
• 비정상적으로 종료된 경우 나타나는 InnoDB의 트랜잭션 복구 메시지
• 쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지
• 비정상적으로 종료된 커넥션 메시지(Aborted connection)
• InnoDB의 모니터링 또는 상태 조회 명령의 결과 메시지
• MySQL의 종료 메시지

 

제너럴 쿼리 로그 파일

MySQL 서버에서 실행되는 전체 쿼리를 조회하기 위해 사용한다.

 

슬로우 쿼리 로그

슬로우 쿼리 로그 파일엔 long_query_time 시스템 변수에 설정한 시간 이상이 소요된 쿼리가 기록된다. 실제 소요된 시간을 기준으로 기록 여부를 판단하기 때문에 정상적으로 실행된 쿼리만 기록된다. log_output 옵션을 이용하여 로그를 파일로 기록할지 테이블로 기록할지 선택할 수 있다. 하지만 테이블로 설정하더라도 slow_log 테이블과 general_log 테이블은 CSV 스토리지 엔진을 사용하기 때문에 파일 저장과 동일하게 동작한다. 로그 분석을 좀 더 쉽게 하기 위해 Percona Toolkit의 pt-query-digest 스크립트를 사용할 수 있다.

 

슬로우 쿼리 로그 구성

• Time: 쿼리가 종료된 시점
• User@Host: 쿼리를 실행한 사용자 계정
• Query_time: 쿼리가 실행되는 데 걸린 시간
• Lock_time: InnoDB의 경우 MySQL 엔진 레벨의 잠금과 스토리지 엔진 자체의 잠금을 가지고 있는데, Lock_time은 MySQL 엔진 레벨에서의 테이블 잠금에 대한 대기 시간을 의미한다. 이 값은 쿼리 실행 전의 잠금 체크와 같은 코드 실행 시간까지 포함되기 때문에 매우 작은 값이라면 무시해도 무방하다.
• Rows_examined: 쿼리가 처리되기 위해 접근한 레코드
• Rows_sent: 실제 클라이언트로 보내진 레코드