Real MySQL 8.0 2주차

TAEYONG KIM·2023년 4월 17일
0

MySQL

목록 보기
2/4

2주차 - 김태용

전반적인 내용은 Real MySQL 8.0 (백은빈 이성욱 지음) 책의 내용을 기반으로 정리한 내용입니다.
모든 저작권은 해당 저자분들에게 있습니다.

  • Architecture

    먼저 서버의 엔진을 구분해야 한다. 사람의 머리 역할을 하는 MySQL Engine과 손발 역할을 담당하는 Storage Engine으로 구분할 수 있다. Storage Engine은 핸들러 API를 만족하면 누구든지 스토리지 엔진을 구현해서 MySQL 서버에 추가해서 사용 할 수 있다.

여기까지만 읽어보면 전혀 무슨 내용을 할지 와닿지 않는다.
서버에서 기본으로 제공하는 InnoDB Storage Engine, MyISAM Storage Engine을 구분해서 보자

개인적으로 DBMS 중 하나인 MySQL을 사용하면서 MySQL Engine과 Storage에 대해 깊게 공부 해볼 시도조차도 해본적이 없다. 이번 기회를 통해서 지식을 채워 넣는 시간으로 활용하고 싶다.
그냥저냥 들어본 용어들

  • Connection Handler
  • SQL Interface
  • SQL Parser
  • SQL Optimizer
  • Cache & Buffer
  • InnoDB, MYISAM, Memory
  • Data File, Log File

MYSQL Server를 구분해서 보자

간단히 말해서, MySQL Engine과 Storage Engine을 구분하자.

  • MySQL Engine : 클라이언트로부터 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL Parser 및 전처리기, 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룸. 표준 SQL을 지원하기 때문에 타 DBMS와 호환되어 실행될 수 있음

  • Storage Engine: 간단히, 요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS의 두뇌에 해당하는 처리를 수행하고, 실제 데이터를 디스크 Storage에 저장하거나 Disk Storage로부터 데이터를 읽어오는 부분은 스토리지 엔진이 전담함.

MYSQL 서버에서 MYSQL Engine은 하나지만 Storage Engine은 여러 개를 동시에 사용할 수 있다.

mysql> create table test_table (fd1 INT, fd2 INT) ENGINE = INNODB;

test_table이 INNO DB를 사용하도록 설정 → SQL 수행 시, INNODB Storage Engine이 처리를 담당하고, 성능 향상을 위항 키 캐시, Buffer Pool과 같은 기능을 내장함.

Handler API

MySQL Engine의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때 → 각 Storage Engine에 쓰기 또는 읽기를 요청함.(Read & Write)

스토리지에게 요청하는 것이 Handler 요청

여기서 사용되는 API를 핸들러 API라고 함. 따라서 InnoDB 즉 스토리지 엔진이 핸들러 API를 통해 데이터를 주고 받기 때문에 얼마나 많은 데이터 작업이 있었는지 기록도 확인할 수 있음.

MySQL Threading 구조

MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동, 크게 Foreground 스레드와 Background 스레드로 구분할 수 있다. MySQL 서버에서 실행 중인 스레드와 목록은 다음과 같다.

실행 중인 목록 확인하기

select thread_id,name, type, processlist_user, processlist_host
FROM performance_schema.threads
ORDER BY type, thread_id;

퍼포먼스 스키마데이터베이스의 threads테이블을 통해 확인 가능
초기에 → 전체 44개의 스레드가 실행 중이며 그중에서 41개의 스레드가 백그라운드 스레드이고 나머지 3개만 포그라운드 스레드로 표시돼 있음.

‘thread/sql/one_connection’ 실제 사용자의 요청을 처리하는 포그라운드 스레드이다.

백그라운드 스레드의 개수는 MYSQL 서버의 설정 내용에 따라 가변적일 수 있으며, 동일한 이름의 스레드가 2개 이상씩 보이는 것은 MySQL 서버의 설정 내용에 의해 여러 스레드가 동일 작업을 병렬로 처리하는 케이스임.

  • Process vs Thread
    MySQL는 프로세스 기반이 아닌 Thread 기반으로 작동한다.
    Process 와 Thread의 차이를 다들 알고 있겠지만 복습하지 않으면 정확하게 답변하기 힘들 수 도 있어서
    기록해 두려고 한다.

    • 먼저 Process란?
      • Linux에서는 Task라고 부릅니다. 수행 중인 프로그램, 프로그램의 수행 환경, 스케줄링 단위 등등..
      • 명백한 것은 프로세스는 CPU time, Memory, File, I/O 장치를 포함한 특정 자원을 필요로 합니다.
      • Process마다 unique한 Process Id를 가지고 있습니다.
      • Text Section, Data Section, Stack Section, Heap Section으로 구조가 구성되어 있습니다.
        Text Section -> 프로그램 코드 부분, 프로그램 순서에 따라 메모리 상위 번지로 자랍니다.
        Data Section -> 전역 변수, 초기화 된 부분과 초기화 되지 않은 부분을 별도로 관리합니다. data & bss
        Stack Section -> 지역 변수, 매개 변수, 복귀 주소 함수 호출 순서에 따라 동적으로 메모리 하위 번지로 자란다.
        Heap Section -> malloc() calloc() 등의 인터페이스로 동적 할당되는 부분입니다.
        Kernel이 관리하는 Process의 자원과 제어 흐름의 집합을 3부분으로 구성
    • System Context
    • Memory Context
    • Hardware Context
      Process는 Process마다 Context를 가지고 있는데 이것을 초기화 하는데 있어서 많은 시간을 소요하게 됩니다. 즉, Overhead가 크다라고 표현할 수 있다.
      Process Control Block(PCB) : Process의 구조에서도 PCB에서는 System Context File, Hardware Context, Memory Context를 모두 관리한다고 생각하면 좋다.
      그러면 Thread라는 개념이 왜 나왔을까?
      앞서 설명 했듯, 이것을 초기화하는데 있어서 많은 시간을 소요한다고 했다. 따라서 Process 내 Text Section, Data Section, Heap Section은 공유하면서 Stack과 Hardware Context만을 별도로 생성한다고 생각하면 좋습니다.

Hardware Context

MultiTasking, 즉 병행 처리에서 Hardware Context는 상당히 중요한 부분입니다. 각 Process마다 Cpu Register 값을 가지고 있기 때문에 중요함. 위에서 말했듯, Hardware Context를 별도로 생성한다는 것은 Thread에서도 고유의 Cpu Register 값을 갖는다는 의미가 된다.

  • Foreground Thread(Client Thread)
    포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리함.
    클라이언트 사용자가 작업을 마치고 커넥션을 종료했다면?
    해당 커넥션을 담당했던 스레드는 스레드 캐시(Thread Cache)로 되돌아간다.
    But, 이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있다면 스레드 캐시로 되돌아가지고 않고 종료시키고 일정 개수의 스레드만 스레드 캐시에 존재하게 한다!
    이렇게 최대 스레드 개수 설정에 대해서는 thread_cache_size라는 시스템 변수로 설정함.
    포그라운드 스레드가 하는일? 쿼리 문장에 대해 처리한다
    디테일 하게, Data를 Data Buffer나 Cache에서 가져오며, 버퍼나 캐시에 없는 경우에는 직접 Disk의 데이터나 Index 파일로부터 데이터를 읽어와서 작업을 처리해야함.
    여기서 Storage Engine에 따라 범위가 달라진다고 표현하고 싶은데,
    MYISAM 테이블은 Disk Write까지 포그라운드 스레드가 처리하지만 InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 Disk Write작업은 백그라운드 스레드가 처리한다.
    사용자 스레드 = 포그라운드 스레드는 똑같은 의미
    Client가 MySQL 서버에 접속하게 되면 MySQL Server는 클라이언트 요청을 처리해 줄 스레드를 생성, 클라이언트에게 할당. 이스레드는 DBMS의 앞단에서 사용자와 통신하기에 포그라운드 스레드라고 하며, 사용자가 요청한 작업을 처리하기 때문에 사용자 스레드라고도 함.

  • Background Thread
    MYISAM 경우, Disk Write 작업까지 포그라운드 스레드가 처리한다고 했지만 InnoDB 같은 경우는 아니라고 했음. 따라서 대표적으로 여러 가지 작업이 백그라운드로 처리됨.

    • Insert Buffer를 병합하는 스레드

    • 로그를 디스크로 기록하는 스레드

    • InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드

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

    • 잠금이나 데드락을 모니터링하는 스레드
      모두 중요한 역할을 하지만 그 중에서도 가장 중요한 것은 Log Thread와 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리하는 Write Thread일 것이다.
      MySQL 5.5 Version부터 데이터 쓰기 스레드와 읽기 스레드의 개수를 2개 이상 지정할 수 있게 됐으며
      innodb_write_io_threads 와 innodb_read_io_threads 시스템 변수로 스레드의 개수를 설정함
      데이터 읽는 작업은 주로 사용자 스레드에서 처리 → 많이 설정할 필요 X
      쓰는 작업은 아주 많은 작업을 백그라운드로 처리, 일반적인 내장 디스크를 사용할때는 2~4, DAS나 SAN과 같은 스토리지를 사용할 때는 디스크를 최적으로 사용할 수 있을만큼 충분히 설정하는 것이 좋음

    • DAS란? (Direct Attached Storage) : PC나 Server에 다이렉트로 꽂아서 사용하는 스토리지.

      서버와 하드웨어를 1:1로 연결, 각 서버는 자신이 직접 파일 시스템을 관리함. 서버에 직접 외장저장장치를 연결하므로 속도는 빠르고 확장은 쉽지만 연결 수에 한계가 있음

    • NAS란? (Network Attached Storage): 쉽게 말해서 DAS에 네트워크 기능을 탑재한 것

      사용자의 요청을 처리하는 데이터의 쓰기 작업 → 지연(버퍼링)되어 처리될 수 있지만 데이터의 읽기 작업은 절대 지연될 수 없다. 따라서, 일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재되어 있으며 InnoDB 또한 이러한 방식으로 처리함.

그렇다면, InnoDB는 쿼리로 인해 데이터가 변경되는 경우 데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 된다는 뜻이다. But, MYISAM에서의 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없음

  • Global Memory & Local Memory
    MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다.
    Global Memory 영역의 모든 메모리 공간은 MySQL 서버가 시작되면서 운영체제로부터 할당됨
    단순하게 MySQL의 시스템 변수로 설정해 둔 만큼 운영체제로부터 메모리를 할당받는다고 생각해도 됨
    • Local Memory 영역을 보기 전, MySQL 서버 내에 존재하는 많은 스레드가 공유해서 사용하는 공간인지 여부에 Global Memory와 구분되고 다음과 같은 특성이 존재한다.

Global Memory 특성

Global Memory 영역은 일반적으로 클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당됨
이 말은 즉, 모든 스레드에 의해 공유된다고 바라볼 수 있음.
대표적인 글로벌 메모리 영역

  • 테이블 캐시
  • InnoDB Buffer Pool
  • InnoDB Adaptive Hash Index
  • InnoDB REDO Log Buffer

Local Memory 특성

Session Memorty 영역이라고도 표현하며, MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역이다. 대표적으로 커넥션 버퍼와 정렬 버퍼 등이 있음. 클라이언트가 MySQL 서버에 접속하면 클라이언트 커넥션으로부터의 요청을 처리하기 위해 스레드를 하나씩 할당하게 되는데, 클라이언트 스레드가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역이라고도 부름

중요한 특징은 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우에는 MySQL이 메모리 공간을 할당조차도 하지 않을수도 있음. 또한 로컬 메모리 공간은 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간도 있고 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간도 있다.

대표적인 로컬 메모리 영역

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

MySQL의 독특한 구조

전문 검색 엔진, 사용자 인증 등등 이 세상의 수많은 사용자의 요구 조건을 만족시키기 위해 기본적으로 제공되는 스토리지 엔진 이외에 부가적인 기능을 더 제공하는 스토리지 엔진이 필요할 수 있다.
이러한 요건을 기초로 개발 회사 또는 사용자가 직접 스토리지 엔진을 개발하는 것이 가능하다
사실 MySQL에서 쿼리가 실행되는 과정의 대부분은 MySQL 엔진에서 처리되고, 마지막 ‘데이터 읽기/쓰기’ 작업만 스토리지 엔진에 의해 처리된다. 따라서, 사용자가 직접 스토리지 엔진을 개발하더라도 DBMS의 전체가 아닌 일부분의 기능만 수행하는 엔진을 작성한다는 것이다.

최소한 MySQL Engine이 각 Storage Engine에게 데이터를 읽어오거나 저장하도록 명령하려면 반드시 Handler를 통해야 한다는 점만 기억하자!

중요한 점

하나의 쿼리 작업은 여러 하위 작업으로 나뉘는데, 각 하위 작업이 MySQL 엔진 영역에서 처리되는지 or 스토리지 엔진 영역에서 처리되는지 구분할 줄 알아야 한다는 점
MySQL 서버에 포함되지 않은 스토리지 엔진을 사용하려면 MySQL 서버를 다시 빌드해야 한다. 하지만 MySQL 서버가 적절히 준비만 돼 있다면 Plugin 형태로 빌드된 스토리지 엔진 라이브러리를 다운로드해서 끼워 넣기만 하면 사용할 수 있다.

MySQL 서버에서는 스토리지 엔진 뿐 아니라 다양한 기능을 플러그인 형태로 지원한다. 인증이나 전문 검색 파서 또는 쿼리 재작성과 같은 플러그인이 있고, MySQL 서버의 기능을 커스텀하게 확장할 수 있게 플러그인 API가 매뉴얼에 공개돼 있음.

MySQL 8.0부터 기존의 플러그인 아키텍처를 대체하기 위해 컴포넌트 아키텍처를 지원함.

대체한다는 점은 단점을 가지고 있다는 점이다.

  • 플러그인은 오직 MySQL 서버와 인터페이스 할 수 있고 플러그인끼리는 통신할 수 없음
  • 플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않음
  • 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화 어려움

Query Execute Structure

쿼리를 실행하는 관점에서 역할을 바라보자

쿼리 파서 : 사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만들어 내는 작업을 의미 : 문장의 기본 문법 오류는 이 과정에서 발견되고 사용자에게 오류 메시지를 전달함

전처리기 : 파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인함 각 토큰을 테이블 이름이나 칼럼 이름, 내장 함수와 같은 개체를 매핑해 객체의 존재 여부의 객체의 접근 권한 등을 확인하는 과정을 수행함.

옵티마이저: 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할을 담당, DBMS의 두뇌에 해당.

실행 엔진: 옵티마이저가 두뇌라면 실행 엔진과 핸들러는 손과 발에 비유 실행 엔진은 만들어진 계획대로 핸들러에게 요청해서 받은 결과를 또 다른핸들러의 요청의 입력으로 연결하는 역할을 수행

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

Query Cache
MySQL 서버에서 Query Cache는 빠른 응답을 필요로 하는 웹 기반의 응용 프로그램에서 매우 중요한 역할을 담당. 즉, SQL 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환하는 우리가 알고 있는 캐시의 기본적인 역할을 한다.
But, 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 테이블과 관련된 것들은 캐시에서 모두 삭제되어야 함. 이는 심각한 동시 처리 성능 저하를 유발한다.
또한 MySQL 서버가 발전하면서 성능 개선되는 과정에서 쿼리 캐시는 계속된 동시 처리 성능 저하와 많은 버그의 원인이 되기도 했음

MySQL 8.0

8.0 버전이 되면서 쿼리 캐시는 MySQL 서버의 기능에서 완전히 제거되고, 관련 시스템 변수도 모두 제거됨

  • Thread Pool
    스레드 풀은 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많다 하더라도 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이 목적임. 스레드 풀을 사용한다고 성능이 두배 좋아지는 것은 아니다.
    제한된 수의 스레드만으로 CPU가 처리하도록 적절히 유도한다면 CPU의 프로세서 친화도 높이고 운영체제 입장에서는 불필요한 Context Switching 줄여서 Overhead를 낮출 수 있다.
    Percona Server의 Thread Pool은 플러그인 형태로 작동하게 구현돼 있다는 차이점이 있음
    스레드 그룹의 모든 스레드가 일을 처리하고 있으면 기존 작업 스레드가 처리를 완료할 때까지 기다릴지 여부를 판단하거나, 스레드 풀의 타이머 스레드는 그룹의 상태를 체크해서 thread_pool_stall_limit 시스템 변수에 정의된 밀리초만큼 작업 스레드가 지금 처리 중인 작업을 끝내지 못하면 새로운 스레드를 생성해서 스레드 그룹에 추가한다. 하지만 max_threads는 넘길 수 없음
    모든 thread가 수행중인 작업이 있을 때, 응답 시간에 아주 민감한 서비스라면 thread_pool_stall_limi 변수를 적절히 낮춰서 설정해야 한다. 하지만 0에 가까운 값으로 설정해야 한다면 thread_pool을 사용하지 않는 편이 나을 것이다.
  • Trasaction Meta Data

    데이터베이스 서버에서 테이블의 구조 정보와 스토어드 프로그램 등의 정보를 데이터 딕셔너리 또는 메타 데이터라고 부른다. MySQL 서버는 5.7 버전까지 테이블의 구조를 파일로 저장했는데, 이러한 파일 기반의 메타 데이터는 생성 및 변경 작업이 트랜잭션을 지원하지 않기 때문에 문제가 발생함.

따라서, MySQL 8.0 버전부터는 이러한 문제점을 해결하기 위해 테이블의 구조 정보나 스토어드 프로그램의 코드 관련 정보를 모두 InnoDB 테이블에 저장하도록 개선하였다.

InnoDB 테이블이야 테이블 기반의 딕셔너리에 저장되지만 MyISAM이나 CSV등과 같은 스토리지 엔진의 메타 정보는 여전히 저장할 공간이 필요함. 따라서 SDI 파일을 사용함. 이름 그대로 직렬화를 위한 포맷이므로 InnoDB테이블들의 구조도 SDI 파일로 변환할 수 있다.

  • InnoDB Storage Engine Architecture

우리가 지금까지 이해한 InnoDB

MySQL의 스토리지 엔진으로서 백그라운드 스레드가 담당하는 엔진으로서 디스크 Read와 Write의 작업을 수행하는 역할을 한다고 이해했다. 조금 더 자세하게 InnoDB는 MySQL에서 사용할 수 있는 엔진 중 거의 유일하게 레코드 기반의 잠금을 제공하며, 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다.

InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링 되어 저장됨
여기서 프라이머리 키는 레코드를 기준으로 의미한 것이 아니라 테이블 단위라고 생각해야 한다.
프라이머리 키 값의 순서대로 디스크에 저장된다는 뜻이며, 모든 Secondary Index는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용함

  • 프라이머리 키는 클러스터링 인덱스이기 때문에 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정됨
    그렇다면 MyISAM 스토리지 엔진은?
    결론적으로 말하면 클러스터링 키를 지원하지 않는다. 따라서, 프라이머리 키와 세컨더리 인덱스는 구조적으로 아무런 차이가 없음. 프라이머리 키는 Unique 제약을 받은 세컨더리 인덱스일 뿐이다. 그리고 MyISAM 테이블의 프라이머리 키를 포함한 모든 인덱스는 물리적인 레코드의 주소 값(ROWID)을 가짐
profile
TAEYONG KIM
백엔드 개발자의 성장기
이전 포스트

Real MySQL 8.0 1주차

다음 포스트

Real MySQL 8.0 3주차

0개의 댓글