책너두 (Real MySQL 8.0 1권) 7일차 (~97p)

요약

• 플러그인 스토리지 엔진 모델에 대해 알게 되었음.
• 스토리지 엔진과 플러그인을 제공된 핸들러 API & 플러그인 API 를 통해 커스텀하게 구현할 수 있다는 사실을 알게 됨.
• 쿼리 실행구조와 각 부분의 역할을 이해하게 됨.
• 쿼리 캐시의 탄생과 삭제 역사를 이해하게 됨.
• mysql 에서의 스레드풀 동작 원리를 이해하게 됨.
• 트랜잭션 지원 메타데이터의 관리 방법과 이전 버전의 히스토리를 이해하게 됨.

발췌

• MyISAM 이나 InnoDB 스토리지 엔진 가운데 뭘 사용하든 별 차이가 없는 것 아닌가, 라고 생각할 수 있지만 그렇진 않다.
  • 여기서 설명한 내용은 아주 간략하게 언급한 것일 뿐이고, 단순히 보이는 ‘데이터 읽기/쓰기’ 작업 처리 방식이 얼마나 달라질 수 있는가를 이 책의 나머지 부분을 통해 깨닫게 될 것이다.

메모

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

• MySQL 의 독특한 구조 중 대표적인 것이 플러그인 모델이다.
• 플러그인으로 스토리지 엔진만 있는 것이 아님.
  • 전문 검색 엔진을 위한 검색어 파서(인덱싱할 키워드를 분리해내는 작업) → 플러그인 형태로 개발해서 사용 가능
  • 사용자 인증을 위한 Native Authentication 과 Caching SHA-2 Authentication 들도 플러그인으로 구현되어 제공됨.
• MySQL 은 이미 기본적으로 많은 스토리지 엔진을 가지고 있음.

• 쿼리가 실행 되는 과정을 위로 나눌 수 있는데, 거의 대부분의 작업이 MySQL 엔진에서 처리됨.
• 마지막 ‘데이터 읽기/쓰기’ 작업만 스토리지 엔진에 의해 처리 된다.
  • ‘데이터 읽기/쓰기’ 작업은 대부분 1건의 레코드 단위로 처리된다.
• MySQL 엔진이 각 스토리지 엔진에게 데이터를 읽어오거나 저장하도록 명령하려면 반드시 핸들러를 통해야 함.
  • MySQL 서버의 상태 변수 중, ‘Handler_’ 로 시작하는 상태변수는 MySQL 엔진이 각 스토리지 엔진에게 보낸 명령의 횟수를 의미하는 변수이다.
• MySQL 에서 MyISAM 이나 InnoDB 와 같이 다른 스토리지 엔진을 사용하는 테이블에 대해 쿼리를 실행하더라도 MySQL 의 처리 내용은 대부분 동일하다.
  • 단순히 ‘데이터 읽기/쓰기’ 영역의 처리만 차이가 있다.
  • 여기서 단순하다고 말하지만, 사실 이 영역의 처리가 큰 차이라는 것을 책을 읽으면서 알게 된다.
• 하나의 쿼리 작업은 여러 하위 작업으로 나뉘는데, 각 하위 작업이 MySQL 엔진 영역에서 처리 되는지 아니면 스토리지 엔진 영역에서 처리되는지 구분할 줄 알아야 함.

mysql> SHOW ENGINES;

• mysql 서버 (mysqld) 에서 지원되는 스토리지 엔진이 어떤 것이 있는지 위 명령을 통해 확인 가능함.
• 위 명령을 통해 나온 Support 컬럼에 표시될 수 있는 값은 다음 4가지 이다.
  • YES : MySQL 서버 (mysqld)에 해당 스토리지 엔진이 포함됨. 사용 가능으로 활성화된 상태
  • DEFAULT : ‘YES’ 와 동일한 상태이고, 필수 스토리지 엔진임을 의미. (이 스토리지 엔진이 없으면 MySQL 이 시작되지 않을 수 있음을 의미)
  • NO : 현재 MySQL 서버 (mysqld) 에 포함되지 않음
  • DISABLED : 현재 MySQL 서버 (mysqld) 에 포함됐지만 파라미터에 의해 비활성화된 상태
• MySQL 서버 (mysqld) 에 포함되지 않은 스토리지 엔진 (= Support 칼럼이 NO) 을 사용하려면 MySQL 서버를 다시 빌드(컴파일) 해야 한다.
  • 플러그인 형태로 빌드된 스토리지 엔진 라이브러리를 다운로드해서 끼워 넣기만 하면 사용할 수 있다.
  • 플러그인 형태의 스토리지 엔진은 손쉽게 업그레이드 할 수 있다.

mysql> SHOW PLUGINS;

• 스토리지 엔진 뿐만 아니라 모든 플러그인의 내용도 위 명령을 통해 확인 가능함.
• MySQL 서버에서는 스토리지 엔진뿐만 아니라 다양한 기능을 플러그인 형태로 지원한다.
  • 인증이나 전문 검색 파서, 또는 쿼리 재작성과 같은 플러그인 제공
  • 비밀번호 검증과 커넥션 제어 등에 관련된 다양한 플러그인 제공
  • MySQL 서버의 기능을 커스텀하게 확장할 수 있게 플러그인 API 가 매뉴얼에 공개되어 있음.
    • 완전히 새로운 기능들을 플러그인을 이용해 구현 가능

컴포넌트

• MySQL 8.0 부터 기존 플러그인 아키텍처를 대체하기 위해 컴포넌트 아키텍처가 지원됨.
• 기존 MySQL 서버 플러그인은 다음과 같은 몇가지 단점이 있었음.
  • 플러그인은 오직 MySQL 서버와 인터페이스할 수 있고, 플러그인끼리 통신할 수 없음.
  • 플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않음. (캡슐화 안됨)
  • 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어려움.
• 위 단점을 보완하는 컴포넌트가 구현됨.
• 5.7 버전까지 비밀번호 검증 기능이 플러그인 형태로 제공됐다면, 8.0 부터는 비밀번호 검증 기능이 컴포넌트로 개선됨.
• 플러그인과 마찬가지로 컴포넌트도 설치하면서 새로운 시스템 변수를 설정해야 될 수 있음.
  • 컴포넌트 사용하기 전 관련 매뉴얼 살펴봐야 함.

쿼리 실행 구조

• 쿼리 실행 관점에서 MySQL 구조를 위와 같이 표현할 수 있음.
• 기능별로 나눠서 살펴보자

쿼리 파서

• 사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만들어 내는 작업
  • 토큰은 MySQL 이 인식할 수 있는 최소 단위의 어휘나 기호를 뜻함.
• 쿼리 문장의 기본 문법 오류는 이 과정에서 발견되고 사용자에게 오류 메시지를 전달함.

전처리기

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

옵티마이저

• 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지 결정하는 역할을 담당.
• DBMS 의 두뇌에 해당
• 이 책에서는 대부분 옵티마이저가 선택하는 내용을 설명함.
• 어떻게 하면 옵티마이저가 더 나은 선택을 할 수 있게 유도하는가를 알게 될 것임.
  • 그만큼 옵티마이저의 역할은 중요하고 영향 범위 또한 넓다.

실행 엔진

• 옵티마이저가 두뇌라면 실행 엔진과 핸들러는 손과 발에 비유할 수 있음.
• 옵티마이저가 GROUP BY 를 처리하기 위해 임시 테이블을 사용하기로 결정한 상황이라 가정
  • 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청함.
  • 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에 요청함.
  • 읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청
  • 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청
  • 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김
    • 즉, 실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행함.

핸들러 (스토리지 엔진)

• 핸들러는 MySQL 서버의 가장 밑단의 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크부터 읽어 오는 역할을 담당한다.
  • 결국 핸들러는 스토리지 엔진을 의미한다.
  • MyISAM 테이블을 조작하는 경우, 핸들러가 MyISAM 스토리지 엔진이 되고, InnoDB 테이블을 조작하는 경우, 핸들러가 InnoDB 스토리지 엔진이 된다.

복제

• MySQL 서버에서 복제(Replication)는 매우 중요한 역할을 담당한다.
  • 지금까지 MySQL 서버에서 복제는 많은 발전을 거듭해옴.
• 추후 책의 별도의 장에서 다룬다.

쿼리 캐시

• SQL 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환하기 때문에 매우 빠른 성능을 보였음.
• 하지만, 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에 변경된 테이블과 관련된 것들을 모두 삭제(Invalidate) 해야 했음.
  • 이는 심각한 동시 처리 성능 저하를 유발함.
  • 쿼리 캐시는 MySQL 서버가 발전하며 성능이 개선되는 과정에서도 지속적인 동시 처리 성능 저하 이슈와 많은 버그의 원인이 되었음.
• 데이터의 변경이 거의 없고 읽기만 하는 서비스에는 도움이 되겠지만 이런 서비스는 흔치 않음.
• 결국 8.0 부터 쿼리 캐시는 MySQL 서버의 기능에서 완전히 제거됨.
  • 관련 시스템 변수도 모두 제거됨.

스레드 풀

• MySQL 서버 엔터프라이즈 에디션은 스레드 풀(Thread Pool) 기능을 제공함.
• MySQL 커뮤니티 에디션은 스레드 풀 기능을 지원하지 않음.
• 지금 설명하는 스레드 풀은 MySQL 엔터프라이즈 에디션에 포함된 스레드 풀 대신, Percona Server 에서 제공하는 스레드 풀에 대한 기능이다.
• MySQL 엔터프라이즈 스레드 풀 기능은 MySQL 서버 프로그램에 내장되어 있음.
• Percona Server 의 스레드 풀은 플러그인 형태로 작동하도록 구현되어 있음.
  • Percona Server 에서 스레드 풀 플러그인 라이브러리 (thread_pool.so 파일) 을 MySQL 커뮤니티 에디션 서버에 설치 (INSTALL PLUGIN 명령)해서 사용하면 됨.
• 스레드 풀은 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄인다.
  • 동시 처리되는 요청이 많더라도 MySQL 서버의 CPU 가 제한된 개수의 스레드 처리에만 집중할 수 있게 함. → 서버 자원 소모를 줄이는 것이 목적
• 스레드 풀이 실제 서비스에서 눈에 띄는 성능 향상을 보여주는 경우는 드뭄.
• 스레드 풀은 실행 중인 스레드들을 CPU 가 최대한 잘 처리해낼 수 있는 수준으로 줄여서 빨리 처리하게 하는 기능임.
  • 스케줄링 과정에서 CPU 시간을 제대로 확보하지 못하는 경우, 쿼리 처리가 더 느려지는 사례도 발생할 수 있으므로 주의해야 함.
  • 제한된 스레드수만으로 CPU 가 처리하도록 적절히 유도한다면, CPU 프로세서 친화도 (processor affinity) 도 높이고, 운영체제 입장에서 불필요한 컨텍스트 스위치 (Context switch) 를 줄여서 오버헤드를 낮출 수 있음.
• Percona Server 의 스레드 풀은 기본적으로 CPU 코어의 개수만큼 스레드 그룹을 생성함. → 코어 개수와 맞추는 것이 CPU 프로세서 친화도를 높이는 데 좋음.
  • thread_pool_size 시스템 변수를 변경해서 조정할 수 있음.
• 이미 스레드 풀이 처리 중인 작업이 있는 경우 thread_pool_oversubscribe 시스템 변수 (기본값 = 3)에 설정된 개수만큼 추가로 더 받아들여서 처리함.
  • 이 값이 너무 크면 스케줄링해야할 스레드가 많아져서 스레드 풀이 비효율적으로 작동할 수 있음.
• 스레드 그룹의 모든 스레드가 일을 처리하고 있다면 스레드 풀은 해당 스레드 그룹에 새로운 작업 스레드(Worker thread)를 추가할지, 기존 작업 스레드가 처리를 완료할 떄까지 기다릴지 여부를 판단해야 함.
  • 스레드 풀의 타이머 스레드는 주기적으로 스레드 그룹의 상태를 체크해서 thread_pool_stall_limit 시스템 변수에 정의된 밀리초만큼 작업 스레드가 지금 처리 중인 스레드를 처리하지 못하면 새로운 스레드를 생성하여 스레드 그룹에 추가함.
  • 단, thread_pool_max_threads 시스템 변수에 설정된 개수를 넘어설 수 없음.
    • 즉, 새로운 쿼리 요청이 들어오더라도 스레드 풀은 thread_pool_stall_limit 시간동안 기다려야만 새로 들어온 요청을 처리할 수 있음.
    • 민감한 서비스 라면 thread_pool_stall_limit 시스템 변수를 적절힌 낮춰서 설정해야 함.
      • 그렇지만 0에 가까운 값으로 설정하는건 권장하지 않음.
      • 0에 가까운 값을 설정해야 한다면, 스레드 풀을 사용하지 않는게 낫다.
• Percona Server 의 스레드 풀 플러그인은 선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공함.
  • 먼저 시작된 트랜잭션 내에 속한 SQL 을 빨리 처리해서 해당 트랜잭션이 갖고 있던 잠금이 빨리해제되고 잠금 경합을 낮춰서 전체 처리 성능을 향상시킬 수 있다.

트랜잭션 지원 메타데이터

• 테이블 구조 정보와 스토어드 프로그램 등의 정보를 데이터 딕셔너리, 또는 메타데이터 라고 함.
• 5.7버전 까지는 테이블의 구조를 FRM 파일에 저장하고 일부 스토어드 프로그램 을 TRN, TRG, PAR 기반으로 관리함.
  • 이러한 파일 기반의 메타데이터 생성 및 변경 작업은 트랜잭션을 지원하지 않음.
  • 테이블의 생성 또는 변경 도중, MySQL 서버가 비정상적으로 종료되면 일관되지 않은 상태로 남는 문제가 발생함. → 데이터베이스나 케이블이 깨졌다고 표현함
• 8.0 부터 위 문제를 해결하기 위해 테이블의 구조 정보나 스토어드 프로그램의 코드 관련 정보를 모두 InnoDB 의 테이블에 저장하도록 개선됨.
• MySQL 서버가 작동하는데 필요한 테이블을 묶어 시스템 테이블 이라고 함.
  • 대표적으로 사용자 인증, 권한에 관련된 테이블임.
• 8.0 부터 이런 시스템 테이블을 모두 InnoDB 스토리지 엔진을 사용하도록 개선함.
• 시스템 테이블과 데이터 딕셔너리(메타 데이터) 정보를 모두 모아 mysql DB 에 저장하고 있음.
• mysql DB 는 통쨰로 mysql.ibd 라는 이름의 테이블스페이스에 저장됨.
  • mysql 서버의 데이터 디렉터리에 존재하는 mysql.ibd 라는 파일은 다른 *.ibd 파일과 함께 특별히 주의해야 함.

mysql DB 에서 테이블 목록을 살펴보면 실제 테이블의 구조가 저장된 테이블은 보이지 않음.
(SHOW TABLES 로 확인 못함)
사용자가 임의로 수정하지 못하게 사용자의 화면에 보여주지 않을 뿐이지 실제로 테이블은 존재함.

• 8.0 부터 데이터 딕셔너리와 시스템 테이블이 모두 트랜잭션 기반의 InnoDB 스토리지 엔진에 저장되도록 개선됨.
  • 스키마 변경 작업 중간에 MySQL 서버가 비정상적으로 종료된다고 하더라도 스키마 변경이 완전한 성공 또는 완전한 실패로 정리된다.
  • 파일로 메타데이터 관리했을때 발생한 문제를 개선하게 됨.
• MySQL 서버에서 InnoDB 스토리지 엔진을 사용하는 테이블은 메타 정보가 InnoDB 테이블 기반의 딕셔너리에 저장됨.
• MyISAM 이나 CSV 와 같은 스토리지 엔진의 메타 정보는 저장할 공간이 필요함.
  • SDI(Serialized Dictionary Information) 파일을 사용한다.
  • InnoDB 이외의 테이블들에 대해서는 SDI 포맷의 *.sdi 파일이 존재한다. → 기존의 *.FRM 파일과 동일한 역할을 한다
  • SDI 는 직렬화를 위한 포맷이므로 InnoDB 테이블들의 구조도 SDI 파일로 변환할 수 있다.
    • ibd2sdi 유틸리티를 이용하면 InnoDB 테이블스페이스에서 스키마 정보를 추출할 수 있다.
    • mysql DB 에 포함된 테이블의 스키마를 JSON 으로 덤프하여 테이블 구조를 볼 수 있다. (데이터 딕셔너리 형태로 구조 확인 가능)