Real MySQL 4장 아키텍처 정리

MySQL 엔진 아키텍처

---

MySQL 전체 구조 및 간략 설명

MySQL의 구조를 그림으로 표현하면 다음과 같다.

여러 프로그래밍 언어에 드라이버와 API를 제공하며, MySQL서버는 크게 MySQL엔진과 스토리지 엔진으로 구분할 수 있음.

MySQL 엔진

MySQL엔진엔 커넥션 핸들러, SQL 파서 및 전처리기, 옵티마이저 등이 있음.

스토리지 엔진

MySQL엔진은 SQL을 분석하거나 최적화하는 등의 DBMS의 두뇌와 같은 역할이라면 스토리지 엔진은 실제 데이터를 디스크에 저장하고 읽어오는 손발과 같은 역할.

MySQL 서버에서 MySQL엔진은 하나지만, 스토리지 엔진은 여러 개가 있을 수 있음. create table문에서 끝에 ENGINE=INNODB; 구문을 붙이면 해당 테이블에 대한 읽기 쓰기를 정의된 스토리지 엔진이 처리함.

대표적인 스토리지 엔진은 InnoDB와 MyISAM이 있는데 다음과 같은 성능 향상을 위한 특징이 있다. (

근데 사실 5.7이후 버전부터 MyISAM은 쓸 이유가 없다. 뒤에 스토리지 엔진 부분에서 후술

)

• InnoDB - 버퍼풀
• MyISAM - 키 캐시

핸들러 API

MySQL 엔진의 쿼리 실행기에서 데이터를 읽거나 쓸때 스토리지 엔진에 읽기 쓰기를 요청한다. 이 요청하는 API를 핸들러 API라고 함.

---

MySQL 스레딩 구조

MySQL 서버의 스레드는 크게 포그라운드 스레드와 백그라운드 스레드로 나눌 수 있다.

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

MySQL 서버에 접속된 클라이언트 수만큼 존재한다. 각 클라이언트의 SQL 요청을 처리함. 스레드 캐시가 있으며, 시스템 변수를 설정하여 스레드 캐시 사이즈를 변경할 수 있다.

InnoDB의 경우 포그라운드 스레드가 데이터를 직접 디스크 작업까지 수행하지 않고 버퍼나 캐시까지만 수행하며 디스크에 데이터를 옮기는 작업은 백그라운드 스레드가 처리한다.

백그라운드 스레드

InnoDB는 다음과 같은 여러 작업이 백그라운드로 처리된다.

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

5.5 버전부터 시스템 변수를 설정하여 데이터 쓰기 스레드와 읽기 스레드의 갯수를 튜닝할 수 있다. 주로 읽기 작업은 클라이언트 스레드가 처리하기 때문에 읽기 스레드는 많을 필요가 없고, 쓰기 스레드의 경우 일반적인 내장 디스크를 사용한다면 2~4개를 잡는다.

---

메모리 할당 및 사용 구조

MySQL의 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다. 글로벌 메모리 영역은 MySQL서버가 시작되면서 운영체제로부터 할당된다. 글로벌 메모리 영역은 MySQL서버 내의 여러 스레드가 공유해서 사용하는 영역이다.

글로벌 메모리 영역

클라이언트 스레드 수와 무관하게 하나의 메모리 영역만 할당되며(필요에 따라 n개로 늘어날 수 있지만 클라이언트 스레드 수와는 무관) 다음과 같은 용도로 사용한다.

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

로컬 메모리 영역(세션 메모리 영역, 클라이언트 메모리 영역)

클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역이다. 스레드별로 독립적으로 사용되며, 다음과 같은 용도로 사용된다.

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

---

쿼리 실행 구조

MySQL의 쿼리 실행 순서를 그림으로 표현하면 다음과 같다.

순서대로 파트마다 어떤 일을 하는지 알아보자

쿼리 파서

클라이언트가 SQL로 요청하면 제일 먼저 쿼리 파서가 받는다. 쿼리 파서는 쿼리를 토큰으로 분리해서 트리 형태의 구조로 만드는 역할을 한다. SQL문법 오류는 이 과정에서 발견하여 사용자에게 반환된다.

전처리기

파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는 지 확인하는 역할을 한다. 테이블 이름이나 칼럼 이름 등을 검사해 해당 객체의 존재 여부나 접근 권한 등을 확인한다.

옵티마이저

사용자의 요청으로 들어온 쿼리 문장의 성능을 가장 빠르게 처리하는 법을 결정한다.

실행 엔진(쿼리 실행기)

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

핸들러(스토리지 엔진)

실행 엔진의 요청에 따라 데이터를 디스크에 저장하고 읽어오는 역할을 한다. 스토리지 엔진별로 내부 동작은 다름.

---

쿼리 캐시

SQL의 실행 결과를 메모리에 캐시해두고 동일한 SQL 쿼리가 실행되면 캐시에서 읽어서 반환하는 역할을 했었다. 5.7 버전 까지는…

하지만, 많은 요청이 들어올 수록 쓰기 작업시 캐시로 관리하는 쿼리 결과값에도 쓰기 내용을 반영시켜야 해서 캐시 관리 비용이 커지고 동시 처리 성능이 오히려 악화되었다. 결국 8.0 버전부터는 사라졌다.

---

스레드 풀

커뮤니티 에디션은 스레드풀을 지원하지 않는다. 엔터프라이즈 에디션부터 지원한다. 근데 Percona Server의 스레드 풀을 이용하면 커뮤니티 에디션에서도 스레드 풀을 사용할 수 있다.

Percona Server의 스레드풀은 플러그인 형태로 구현되어 있고, 기본적으로 cpu 코어 갯수만큼의 스레드 그룹을 생성한다. 다음과 같은 시스템 변수로 스레드 풀을 컨트롤할 수 있다.

• thread_pool_size: 스레드 그룹의 갯수, 기본값은 cpu 코어의 갯수만큼 잡힌다.
• thread_pool_oversubscribe: 스레드 풀의 스레드가 모두 처리중일 때 추가로 받아들일 작업의 수, 기본값은 3, 이 값이 너무 크면 스케줄링 할 스레드가 많아져 오히려 성능이 저하될 수 있다.
• thread_pool_stall_limit: 정의된 밀리초만큼 스레드가 처리 중인 작업을 끝내지 못하면 새로운 스레드를 생성해서 스레드 그룹에 추가한다. 응답 시간에 민감하다면 낮게 설정하면 된다. 그렇다고 0에 가깝게 설정하는건 별로다. 그럴거면 그냥 스레드풀 없이 하는게 낫다.
• thread_pool_max_threads: 스레드 풀의 전체 스레드 갯수 최대치.

---

트랜잭션 지원 메타데이터

MySQL 5.7 버전까지는 테이블 구조 정보, 스토어드 프로그램 등의 정보를 나타내는 메타데이터를 파일 기반으로 관리했다. 하지만, 파일 기반으로 관리하니 트랜잭션이 지원되지 않아서 MySQL 서버가 비정상적으로 종료되면 수정된 내용 중 일부만 반영되는 일관되지 않는 상태가 되어 데이터베이스 깨짐 현상이 발생했다.

8.0버전 부터는 이런 문제점을 해결하기 위해 메타데이터를 InnoDB의 테이블에 저장하도록 개선됐다. 모든 시스템 테이블이 InnoDB 스토리지 엔진을 사용하도록 개선했고 mysql DB에 저장하고 있다. 스키마 변경 작업 중간에 MySQL 서버가 비정상적으로 종료될 시에 이제 스키마 변경 작업이 완전 성공 또는 완전 실패로 정리된다.

---

InnoDB 스토리지 엔진 아키텍처

---

InnoDB는 MySQL의 스토리지 엔진중 가장 많이 사용된다. 거의 유일하게 레코드 기반의 잠금을 제공하기 때문에 높은 동시성 처리가 가능하고 안정적이고 성능이 뛰어남. InnoDB의 아키텍처를 그림으로 나타내면 다음과 같다.

InnoDB는 다음과 같은 특징을 가진다.

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

InnoDB의 테이블은 데이터를 프라이머리 키로 클러스터링하여 저장한다. 키 순서대로 디스크에 저장하며, 모든 세컨더리 인덱스(논 클러스터링 인덱스)는 레코드 주소 대신 PK의 값을 논리적인 주소로 사용한다. PK가 클러스터링 인덱스라서 PK에 의한 조회는 매우 빠름. 쿼리 실행 계획에서도 최우선으로 선택된다.

MyISAM은 전부 논 클러스터링 인덱스!

---

외래 키 지원

외래키는 InnoDB에만 있다. MyISAM에서는 지원 X

InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 대해 인덱스 생성이 필요하다. 변경 시에는 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요해서 잠금이 여러 테이블로 전파된다. 그로 인해 데드락이 발생하기도 하니 주의가 필요함.

외래 키 제약조건 때문에 수동으로 데이터를 변경하거나 스키마를 변경할 때 작업이 실패할 수 있는데 이때는 foreign_key_checks를 꺼두면 외래 키에 대한 체크 작업을 일시적으로 멈출 수 있다. 이 옵션을 끄면 부가적인 체크 작업이 필요 없기 때문에 데이터 적재나 삭제, 스키마 변경 작업도 훨씬 빠르게 처리될 수 있다.

그렇다고 부모와 자식 테이블의 관계가 깨진 상태로 유지해도 된다는 것은 아니고, 최종적으론 일관성을 맞춘 후에 다시 옵션을 활성화해야 한다. 참고로 옵션이 비활성화 되면 ON DELETE CASCADE, ON UPDATE CASCADE 옵션도 무시된다.

foreign_key_checks옵션은 글로벌변수와 세션변수 모두 설정 가능하다.

mysql> SET foreign_key_check=OFF;
mysql> SET SESSION foreign_key_check=OFF;

---

MVCC(Multi Version Concurrency Control) (feat. 언두 로그)

레코드에 대해서 동시에 여러 버전으로 관리한다는 의미로 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 데에 있다. InnoDB는 언두 로그를 이용해 이 기능을 구현한다.

예를 들면, 트랜잭션의 격리 수준이 READ COMMITTED면서 트랜잭션 시작 이후에 다른 트랜잭션이 데이터를 변경한 레코드를 조회할 경우에는 버퍼 풀이나 디스크에서 해당 레코드를 조회하고 READ UNCOMMITTED 이상인 경우엔 언두 로그에서 해당 데이터를 조회한다.

트랜잭션이 길어지면 언두에서 관리하는 데이터가 삭제되지 못하고 오랫동안 관리돼야 하며, 언두 영역의 공간이 많이 늘어나는 상황이 발생할 수도 있다.

잠금 없는 일관된 읽기

InnoDB는 MVCC기술을 이용해 잠금없이 읽기 작업을 수행한다. 격리 수준이 SERIALIZABLE이 아니라면 INSERT와 연결되지 않은 순수한 읽기 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 바로 실행된다. 오랜 시간 트랜잭션이 활성화되면 MySQL 서버가 느려자거나 문제가 발생할 때가 있는데 이런 일관된 읽기를 위해 언두로그를 삭제하지 못하고 계속 유지해야 해서 그렇다. 따라서 트랜잭션은 빠르게 커밋이나 롤백되어야 한다.(타임아웃 설정 필수)

---

자동 데드락 감지

InnoDB는 잠금이 데드락 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프 형태로 관리한다. InnoDB는 데드락 감지 스레드가 있어서 주기적으로 이 그래프를 검사해서 데드락에 걸린 스레드를 찾아 그중 하나를 강제종료하는데 이때의 기준은 언두로그의 양이다. 언두 데이터를 더 적게 가진 쪽이 롤백의 대상이 된다. 이유는 롤백시 언두 처리를 해야할 데이터가 적기 때문. 트랜잭션 강제 롤백으로 인한 MySQL 서버의 부하를 덜 발생하게 한다.

InnoDB는 기본적으로는 상위 레이어인 MySQL 엔진에서 관리하는 테이블 잠금은 볼 수가 없어서 데드락 감지가 불확실할 수 있는데, innodb_table_locks시스템 변수를 활성화하면 InnoDB 내부의 레코드 잠금 뿐 아니라 테이블 레벨의 잠금도 관리할 수 있다.

주의

동시 처리 스레드가 매우 많을 경우 데드락 감지 스레드도 늘어나는데, 데드락 감지시에 데드락 감지 스레드가 잠금 목록 리스트에 잠금을 걸게 되는데 이것 때문에 서비스 처리 스레드가 느려질 수 있다. 동시 처리가 매우 많은 경우 데드락 감지 스레드가 오히려 많은 CPU 자원을 소모하게 할 수도 있는 샘.

이런 문제가 발생할 여지가 있을 땐 데드락 감지 스레드를 비활성화 하고 트랜잭션에 타임아웃을 설정하는 방식으로 해결한다.

---

자동화된 장애 복구

InnoDB 데이터 파일은 기본적으로 MySQL 서버가 시작될 때 항상 자동 복구를 수행한다. 만약 디스크에서 MySQL 관련 파일을 삭제해버려서 자동으로 복구를 못하는 경우가 발생할 수도 있는데… 이땐 복구가 어렵다. 자동 복구가 불가능한 상황에선 MySQL 서버가 바로 종료돼버리는데 이때는 innodb_force_recovery시스템 변수를 설정해서 MySQL 서버를 시작해야 한다. 이 옵션은 InnoDB 스토리지 엔진이 데이터 파일이나 로그 파일의 손상 여부 검사 과정을 선별적으로 진행하게 한다.

---

InnoDB 버퍼 풀

디스크의 데이터 파일이나, 인덱스 정보를 메모리에 캐시하는 공간이다. 이름이 버퍼 풀인 이유는 쓰기 작업을 지연시키고 한번에 일괄처리하는 버퍼의 역할도 같이 하기 때문.

버퍼 풀의 크기 설정

일단 크면 클수록 성능이 좋겠지만… 무작정 크게 설정하는게 능사가 아니다. 운영체제와 클라이언트 스레드가 사용할 메모리를 고려해서 설정해야한다. 5.7 버전부터 InnoDB 버퍼 풀의 크기를 동적으로 조절할 수 있으며, 작은 값으로 설정해서 조금씩 상황을 봐 가면서 증가시켜야 한다.

innodb_buffer_pool_size시스템 변수로 크기를 설정할 수 있으며, 동적으로 버퍼 풀의 크기를 확장할 수도 있다. 하지만, 버퍼 풀의 크기 변경은 서비스 영향도가 큰 작업이므로 가능하면 MySQL 서버가 한가한 시점에 골라서 해야한다.

버퍼 풀의 구조

InnoDB는 버퍼 풀이라는 메모리 공간을 innodb_page_size 시스템 변수에 지정된 값의 크기의 조각으로 쪼개서 데이터를 조각에 저장한다. 버퍼 풀의 페이지 크기 조각을 관리하기 위해 LRU 리스트와 플러시 리스트, 프리 리스트라는 3개의 자료 구조를 관리한다.

• LRU 리스트: 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 버퍼 풀에 유지해서 디스크 읽기를 최소화하는 목적으로 관리한다. 사실 MRU(Most Recently Used) + LRU(Least Recently Used) 두 서브 리스트가 합쳐진 구조이다. Old 서브 리스트 영역은 LRU, New 서브 리스트 영역은 MRU로 이해하면 된다. 동작은 다음과 같다. 처음 디스크에서 가져온 데이터는 LRU헤더에 꽂히고 LRU에서 재 조회된 데이터는 MRU 헤더에 꽂힌다. 리스트에 새 데이터가 꽂히면 꼬리 부분부터 하나씩 밀리며, LRU에 꼬리에서 밀리면 버퍼 풀에서 삭제되는 샘임.
• 플러시 리스트: 디스크에 데이터를 일괄 쓰기시에 쓰이며, 디스크로 동기화되지 않은 데이터를 가진 페이지(더티 페이지)의 변경 시점 기준의 페이지 목록을 관리한다. 디스크 읽은 상태 그대로면 여기다 저장 안하지만 변경되면 저장됨.(하이버네이트 영속성 컨텍스트의 더티 체킹과 비슷한 원리인듯)
• 프리 리스트: InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어있는 페이지들의 목록. 사용자의 쿼리가 디스크 페이지를 읽어와야 하는 경우 사용된다.

버퍼 풀과 리두 로그

버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정할 수록 성능이 좋아진다. 이는 버퍼 풀이 가져다 주는 성능상 이점인 캐시와 버퍼링 중 캐시에 도움이 되기 때문이다. 버퍼링을 향상시키려면 어떻게 해야할까?

버퍼 풀은 디스크에서 읽은 상태 그대로인 클린 페이지와 변경된 데이터를 가진 더티 페이지가 있다. 더티 페이지에 데이터가 보관될 때 리두 로그에도 그 내용을 남기게 된다. 이 리두 로그라는 공간은 복구 목적이기 때문에 복구 내용이 영속반영이 되면 공간을 비우고 재사용하게 된다. 그래서 리두 로그가 일정 수치로 차면 영속시키고 공간을 비우기 위해 강제로 버퍼풀의 내용을 디스크에 쓰는 작업이 발생하는데, 리두 로그의 사이즈가 너무 작으면 이 버퍼링이 너무 자주 발생하고, 또 너무 크면 한번에 쓰는 내용이 너무 많아져서 문제가 된다. 그래서 적절한 리두 로그의 사이즈를 찾는 것이 중요하다.

성능 튜닝 관련해서 정리

• 버퍼 풀 사이즈 조절: 버퍼 풀의 캐시 성능 튜닝
• 리두 로그 사이즈 조절: 버퍼 풀의 버퍼링 성능 튜닝

버퍼 풀 플러시

InnoDB는 버퍼 풀에서 더티 페이지들을 성능상의 악영향 없이 디스크에 동기화하기 위해 2개의 플러시 기능을 백그라운드로 실행한다.

• 플러시 리스트 플러시: 오래전에 변경된 데이터 페이지 순서대로 디스크에 동기화하는 작업을 수행한다. 이때 얼만큼의 더티 페이지를 디스크에 동기화하냐에 따라 성능상의 악영향을 줄일 수 있다. 시스템 변수로 이 작업에 관련된 값들을 조절할 수 있다.
• LRU 리스트 플러시: LRU 리스트에서 사용 빈도가 낮은 페이지들을 제거해서 새로운 페이지를 읽어올 공간을 만들어야 하는데, 이때 사용된다. 제거할 페이지에서 더티 페이지는 디스크에 동기화하고, 클린 페이지의 경우 프리 리스트로 페이지를 옮긴다. 오래된 리스트를 지울 때 스캔할 리스트의 갯수는 시스템 변수로 조절할 수 있다.

버퍼 풀 상태 백업 및 복구

버퍼 풀은 캐시 역할을 해서 쿼리 성능을 향상 시키는 역할을 한다. 버퍼 풀에 디스크 데이터를 적재하는 과정을 워밍업이라고 표현하는데, 워밍업이 되지 않은 상태에서는 쿼리 성능이 많이많이 느리다. 그래서 5.5 버전에서는 서버를 셧다운했다가 다시 시작하는 경우 강제 워밍업을 위해 주요 테이블과 인덱스에 대해 풀 스캔을 한번 실행하고 서비스를 오픈하곤 했다.

5.6 버전부터는 버퍼 풀 덤프 및 적재 기능이 도입되어 서버를 재시작 하더라도 버퍼 풀의 상태를 백업 복구할 수 있게 되었다. innodb_buffer_pool_now시스템 변수를 활성화해서 버퍼 풀의 상태를 복구할 수 있다.

---

Double Write Buffer

InnoDB의 리두 로그는 공간 낭비를 막기 위해 페이지 변경된 내용에 대해서만 기록한다. 그래서 더티 페이지를 디스크 파일로 플러시할 때 일부만 기록되고 하드웨어 오작동이나 시스템 비정상 종료로 인해 작업이 중단되면 그 페이지 내용은 복구할 수 없을 수도 있다. 이런 현상을 파셜 페이지 또는 톤 페이지라고 하는데 InnoDB에서는 이같은 현상을 막기 위해 Double-Write 기법을 이용한다.

버퍼 풀에서 디스크에 변경된 내용을 기록하기 전에 변경된 내용들을 묶어서 먼저 한번의 디스크 쓰기로 시스템 테이블스페이스의 DoubleWrite 버퍼에 기록한다. 그 후에 각 더티 페이지를 파일의 적당한 위치에 하나씩 랜덤으로 쓴다.

DoubleWrite 버퍼에 기록된 변경 내용은 실제 데이터 파일에 정상적으로 반영되면 더이상 필요가 없다. 버퍼 풀의 플러시를 복구하는 용도로만 사용되며, 이 기능을 사용할 지에 대한 여부는 innodb_doublewrite시스템 변수로 제어할 수 있다.

이 기능은 HDD 처럼 순차 디스크 쓰기를 할 경우엔 별로 부담되지 않지만, SSD처럼 랜덤 IO나 순차 IO의 비용이 비슷한 저장 시스템인 경우는 부담스럽다. 하지만 데이터 무결성이 더 중요하다면 사용을 고려할만 함.

---

언두 로그

InnoDB는 트랜잭션과 격리 수준을 보장하기 위해 데이터 변경 이전 버전의 데이터를 언두 로그에 백업한다. 만약 한 트랜잭션이 너무 오래 활성화 되어있으면 언두로그가 너무 많이 쌓여서 문제가 될 수 있다. InnoDB는 8.0 버전부터 언두 로그를 시스템 테이블스페이스가 아니라 별도의 로그 파일에 저장한다.

---

체인지 버퍼

데이터가 변경되면 데이터 뿐만 아니라 테이블에 포함된 인덱스도 처리해야 한다. 근데 인덱스를 업데이트 하려면 랜덤하게 디스크를 읽어야 해서 자원이 많이 소모된다. 변경해야 할 인덱스 페이지가 버퍼풀에 있으면 바로 업데이트를 수행하지만, 디스크에서 읽어서 변경해야 하는 상황이라면 즉시 변경하지 않고 임시 공간에 저장하고 결과를 반환하는데 이 임시 공간을 체인지 버퍼라고 한다.

유니크 인덱스는 체인지 버퍼를 사용할 수 없다. 사용자에게 결과를 전달하기 전에 중복 여부를 체크해야 해서 어차피 디스크를 다 읽어서 확인해야 하기 때문.

체인지 버퍼에 임시로 저장된 레코드 조각은 이후 백그라운드 스레드에 의해 병합된다. 이 스레드를 체인지 버퍼 머지 스레드라고 한다. innodb_change_buffering이라는 시스템 변수로 데이터 변경 작업(INSERT, UPDATE, DELETE)별로 버퍼링을 활성화할 수 있다.

---

리두 로그 및 로그 버퍼

리두 로그는 MySQL 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치다. 데이터의 변경 내용을 반영하기 전에 로그로 먼저 기록해두고 이를 복구할 때 사용한다. 이 리두 로그를 버퍼링할 수 있는 로그 버퍼와 같은 자료구조도 가지고 있다. MySQL 서버가 비정상 종료될 때 다음과 같은 일관성 문제가 생길 수 있다.

1. 커밋됐지만 데이터 파일에 기록되지 않음
2. 롤백됐지만 데이터 파일에 이미 기록됨

1번은 리두 로그에 있는 데이터를 데이터 파일에 반영해주면 된다. 하지만 2번엔 리두 로그로 해결할 수 없고 이때는 변경되기 전 언두 로그의 내용을 가져와서 데이터 파일에 반영해줘야 한다.

리두 로그 비활성화

MySQL 8.0부터 리두 로그를 비활성화할 수 있는 옵션이 추가됐다. 비활성화 할 경우엔 리두 로그를 남기지 않기 때문에 데이터 변경 속도가 매우 빨라질 수 있다. 데이터를 복구하거나 최초 적재시에 리두 로그를 비활성화해서 적재 시간을 단축할 수 있다.

mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG;

-- // 데이터 적재 실행

mysql> ALTER INSTANCE ENABLE INNODB REDO_LOG;

---

어댑티브 해시 인덱스

어댑티브 해시 인덱스는 우리가 아는 인덱스가 아니라 InnoDB가 사용자가 자주 조회하는 데이터에 대해 자동으로 거는 인덱스를 말한다. innodb_adaptive_hash_index시스템 변수를 이용해서 활성화하거나 비활성화할 수 있다. 언제나 성능 향상에 도움이 되는 것이 아니라 상황에 맞게 활성화하고 비활성화 해야한다.

활성화하고 비활성화 하는 기준은 다음과 같이 정리할 수 있다.

비활성화하는 경우

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

활성화하는 경우

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

---

MyISAM 스토리지 엔진 아키텍처

---

사실 이 스토리지 엔진은 쓰는 일이 없다고 한다. 키 캐시라는 기능이 있어서 읽기가 빠르다고는 하지만 다음과 같은 단점에 의해 InnoDB에 밀린다.

• 레코드 단위 락 없음. 락 필요하면 테이블 단위 락. → 동시성 처리 성능 구림
• 트랜잭션 없음.
• 외래키 없음.

엔터프라이즈급 애플리케이션이라면 사용이 불가능할 듯 하다. 읽기 전용으로 사용하기엔 관계형 데이터베이스의 특징을 전혀 살리지 못하는 것 같다.
그럴거면 성능상 이점을 더 확실하게 볼 수 있는 NoSQL이 많다.(ex 몽고, Redis 등..)

---

MySQL 로그 파일

---

에러 로그 파일

MySQL이 실행되는 도중에 발생하는 에러나 경고가 적히는 로그 파일이다. 다음과 같은 메시지들을 출력한다.

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

---

제너럴 쿼리 로그 파일(제너럴 로그 파일)

어떤 쿼리가 실행됐는지 시간 단위로 실행된 쿼리를 남긴다. 쿼리 요청을 받으면 바로 기록하기 때문에 쿼리 실행중에 에러가 발생해도 일단 로그 파일에는 기록됨.

---

슬로우 쿼리 로그

쿼리 성능 튜닝을 목적으로 쿼리 결과가 느린 쿼리들만 기록된다 long_query_time 시스템 변수에 설정한 시간 이상이 소요된 쿼리를 기록한다.

Ref

이미지 출처

Real MySQL 8.0 1권 - 위키북스