MySQL 서버는 다른 DBMS에 비해 구조가 상당히 독특하다. 이를 모르고 사용하는 사용자들 입장에선 차이가 거의 느껴지지 않지만 이러한 독특한 구조 덕분에 다른 DBMS는 가질 수 없는 엄청난 혜택을 누릴 수 있으나 이 때문에 다른 DBMS에서는 문제가 되지 않을 것이 문제가 되기도 한다.
다음은 MySQL의 전체 구조이다.
MySQL 엔진
MySQL 엔진은 커넥션 핸들러와 SQL 파서 및 전처리기, 옵티마이저가 중심을 이룬다. 이에 대한 설명은 다음과 같다.
또한 MySQL은 표준 SQL(ANSI SQL) 문법을 지원하기 때문에 표준 문법에 따라 작성된 쿼리는 타 DBMS와 호환되서 실행될 수 있다.
스토리지 엔진
실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분은 스토리지 엔진이 담당한다.
스토리지 엔진이라는 이름에서 알 수 있듯 어떤 저장공간에서 읽기 및 쓰기를 담당한다고 생각하면 된다.
MySQL 서버에서 MySQL 엔진은 하나이지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다. 다음 예제와 같이 테이블이 사용할 스토리지 엔진을 저장하면 이후 해당 테이블의 모든 읽기 및 쓰기 작업은 정의된 스토리지 엔진에서 처리한다.
mysql> CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;test_table은 InnoDB 스토리지 엔진을 사용하도록 정의했다.- 이제
test_table에대한INSERT,UPDATE,DELETE,SELECT등의 작업이 발생하면 InnoDB 스토리지 엔진이 이를 처리하게 된다.
각 스토리지 엔진은 성능 향상을 위해 키 캐시(MyISAM 스토리지 엔진)나 InnoDB 버퍼 풀(InnoDB 스토리지 엔진)과 같은 기능을 내장하고 있다.
핸들러 API
MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에 쓰기 또는 읽기를 요청하는데, 이러한 요청을 핸들러(Handler) 요청이라 하고, 여기서 사용되는 API를 핸들러 API라고 한다. InnoDB 스토리지 엔진 또한 이 핸들러 API를 이용해 MySQL 엔진과 데이터를 주고받는다.
MySQL 스레딩 구조
MySQL 서버는 프로세스 기반이 아닌 스레드 기반으로 작동하며, 이러한 스레드들은 크게 포그라운드(Foreground) 스레드와 백그라운드(Background) 스레드로 구분할 수 있다.
포그라운드 스레드(클라이언트 스레드)
포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수 만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다. 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시(Thread Cache)로 되돌아간다.
이 때, 이미 스레드 캐시에 일정 개수 이상의 대기중인 스레드가 있으면 스레드 캐시에 넣지 않고 스레드를 종료시켜 일정 개수의 스레드만이 스레드 캐시에 존재할 수 있도록 한다.
스레드 캐시에 유지할 수 있는 최대 스레드 개수는 thread_cache_size 시스템 변수로 설정한다.
백그래운드 스레드
MyISAM의 경우에는 별로 해당 사항이 없는 부분이지만 InnoDB는 다음과 같이 여러 가지 작업이 백그라운드로 처리된다. InnoDB의 경우만 이런 여러가지 작업들이 백그라운드 스레드로 처리된다는 것을 다시 한 번 명심하자. InnoDB의 백그라운드 스레드는 크게 5가지가 있다.
- 인서트 버퍼(Insert Butter) 병합 스레드
- 로그 스레드(Log Thread)
- 쓰기 스레드(Write Thread)
- 읽기 스레드(Read Thread)
- 잠금, 데드락 모니터링 스레드
모두 중요한 역할이지만 가장 중요한 것은 로그 스레드와 쓰기 스레드이다. InnoDB에서도 데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리되기 때문에 읽기 스레드는 많이 설정할 필요는 없지만, 쓰기 스레드는 아주 많은 작업이 백그라운드 스레드로 처리되기 때문에 디스크를 최적으로 사용할 수 있을 만큼 충분히 설정하는 것이 좋다.
읽기 및 쓰기 스레드의 개수는 각각 innodb_read_io_threads, innodb_write_io_threads 시스템 변수로 설정할 수 있다.
사용자의 요청을 처리하는 도중 데이터의 쓰기 작업은 지연(버퍼링)될 수 있지만 데이터의 읽기 작업은 절대 지연될 수 없다.(SELECT Query를 보냈는데 10분 뒤에 결과가 나온다는 것은 말이 안된다.) 그래서 일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재돼 있으며, InnoDB 또한 이러한 방식으로 처리한다.
하지만, MyISAM은 그렇게 처리하지 않고 사용자 스레드가 쓰기 작업까지 함께 처리하도록 설계돼있다.
이러한 이유로 InnoDB에서는 INSERT, UPDATE, DELETE 쿼리로 데이터가 변경되는 경우 버퍼링 덕분에 데이터가 디스크의 데이터 파일로 완전히 저장될 때 까지 기다리지 않아도 된다. 버퍼링은 버퍼에 저장할 데이터를 모아놨다가 한 번에 디스크에 저장하는 방식이므로 매번 디스크에 접근하지 않기 때문이다.
반대로, MyISAM에서 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없다.
메모리 할당 및 사용 구조
MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다. 이 두 영역은 MySQL 서버 내에 존재하는 많은 스레드가 공유하는 공간인지 여부에 따라 구분되며 이들에 대한 특징을 자세히 알아보자.
글로벌 메모리 영억
글로리 메모리 영역은 모든 메모리 공간은 MySQL 서버가 시작되면서 OS로부터 할당을 받는다. MySQL의 시스템 변수로 설정해 둔 만큼 OS로 부터 할당 받는다고 알아두면 된다.
일반적으로 클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당된다. 단, 필요에 따라 2개 이상의 메모리 공간을 할당받을 수 있지만 클라이언트의 스레드 수와는 무관하며, 생성된 글로벌 영역이 N개라 하더라도 모든 스레드에 의해 공유된다.
대표적인 글로벌 메모리 영역은 다음과 같다.
- 테이블 캐시
- InnoDB 버퍼 풀
- InnoDB 어댑티브 해시 인덱스
- InnoDB 리두 로그 버퍼
로컬메모리 영역(세션 메모리 영역)
클라이언트가 MySQL 서버에 접속하면 MySQL 서버에서는 클라이언트 커넥션으로부터 요청을 처리하기 위해 스레드를 하나씩 할당하게 되는데, 클라이언트 스레드가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역이라고도 한다.
또한 클라이언트와 MySQL 서버 사이 커넥션을 세션에서 사용하는 메모리 영역이므로 세션 메모리 영역이라고도 표현한다.
로컬 메모리는 각 클라이언트 스레드마다 독립적으로 존재하며 절대 공유되지 않는다는 특징이 있다. 일반적으로 글로벌 메모리 영역의 크기는 주의해서 설정하지만 정렬 버퍼(Sort Buffer)와 같은 로컬 메모리 영역은 크게 신경쓰지 않고 설정하는데, 최악의 경우에는 MySQL 서버가 메모리 부족으로 멈춰버릴 수 있으므로 적절한 메모리 공간을 설정하는 것이 중요하다.
또한, 쿼리의 용도에 따라 공간이 필요하지 않다고 판단되는 경우 MySQL 서버는 메모리 공간을 할당조차도 하지 않을 수 있다. 대표적으로 정렬 버퍼(Sort Buffer)나 조인 버퍼(Join Buffer)와 같은 공간이 있다.
그리고, 로컬 메모리 공간은 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간도 있고(커넥션 버퍼, 결과 버퍼), 그렇지 않고 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간(정렬 버퍼나 조인 버퍼)도 있다.
대표적인 로컬 메모리 영역은 다음과 같다.
- 정렬 버퍼(Sort Buffer)
- 조인 버퍼
- 바이너리 로그 캐시
- 네트워크 버퍼
플러그인 스토리지 엔진
MySQL의 독특한 구조들 중 대표적인 것이 바로 플러그인 모델이다. 플러그인해서 사용할 수 있는 것이 스토리지 엔진만 있는 것은 아니다.
전문 검색 엔진을 위한 파서(인덱싱할 키워드를 분리해내는 작업)도 플러그인 형태로 개발해서 사용하고, 사용자의 인증을 위한 Native Authentication과 Caching SHA-2 Authentication 등도 모두 플로그인으로 구현되어 제공된다.
MySQL은 기본적으로 많은 스토리지 엔진을 가지고 있으나, 이외에 부가적인 기능을 제공하는 추가 스토리지 엔진이 필요한 경우 사용자가 직접 스토리지 엔진을 개발하는 것도 가능은 하다.
MySQL에서 쿼리가 실행되는 과정을 크게 밑의 그림 4.5와 같이 나눈다면 거의 대부분의 작업이 MySQL 엔진에서 처리되고, 마지막 데이터 읽기/쓰기 작업만 스토리지 엔진에 의해 처리된다. (만약, 사용자가 새로운 용도의 스토리지 엔진을 만든다 하더라도 DBMS의 전체 기능이 아닌 일부분의 기능만 수행하는 엔진이 된다는 의미이다.)
각 처리 영역에서 데이터 읽기/쓰기 작업은 대부분 1건의 레코드 단위(예를 들어, 특정 인덱스의 레코드 1건 읽기 또는 마지막으로 읽은 레코드의 다음 또는 이전 레코드 읽기와 같이)로 처리된다. 그리고 MySQL을 사용하다 보면 핸들러(Handler)라는 단어를 자주 접하게 될 것이다.
핸들러라는 단어는 자동차에 비유해보면 쉽게 이해할 수 있다. 사람이 핸들을 이용해 자동차를 운전하듯이, 프로그래밍 언어에서는 어떤 기능을 호출하기 위해 사용하는 운전대와 같은 역할을 하는 객체를 핸들러(또는 핸들러 객체)라 한다.
MySQL 서버에서 MySQL 엔진은 사람 역할을 하고, 각 스토리지 엔진은 자동차 역할을 한다. 이 때, MySQL 엔진이 스토리지 엔진을 조정하기 위해 핸들러라는 것을 사용한다. 잘 이해가 안간다면 최소한 MySQL 엔진이 각 스토리지 엔진에게 데이터를 읽어오거나 저장하도록 명령하려면 반드시 핸들러를 통해야 하나는 점만 기억하자.
MySQL에서 MyISAM이나 InnoDB 같은 다른 스토리지 엔진을 사용하는 테이블에 대해 쿼리를 실행하더라도 MySQL의 처리 내용은 대부분 동일하며, 단순히 (그림 4.5의 마지막 단계) 데이터 읽기/쓰기 영역의 처리만 차이가 있을 뿐이다. 실질적인 GROUP BY나 ORDER BY 등 복잡한 처리는 스토리지 엔진 영역이 아닌 MySQL 엔진의 처리 영역인 쿼리 실행기에서 처리된다.
그렇다면 MyISAM이나 InnoDB 스토리지 엔진 가운데 뭘 사용하든 별 차이가 없다고 생각이 들지만 그렇진 않다. 여기서 설명한 내용은 아주 간략하게 언급한 것이고 실제로 데이터 읽기/쓰기 작업 처리 방식은 매우 다양하게 달라질 수 있다. 여기서 중요한 내용은 하나의 쿼리 작업은 여러 하위 작업으로 나뉘는데, 각 하위 작업이 MySQL 엔진 영역에서 처리되는지 아니면 스토리지 엔진 영역에서 처리되는지 구분할 줄 알아야 한다는 것이다. 여기서는 각 단위의 작업을 누가 처리하고 MySQL 엔진 영역과 스토리지 엔진 영역의 차이를 설명하는데 목적이 있다.
컴포넌트
MySQL 8.0 버전부터는 기존의 플러그인 아키텍처를 대체하기 위해 컴포넌트 아키텍처가 지원된다. MySQL 서버의 플러그인에는 다음 단점들이 있기 때문이다.
- 플러그인은 오직 MySQL 서버와 인터페이스할 수 있고, 플러그인끼리는 통신할 수 없다.
- 플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않다.(캡슐화 불가)
- 플러그인은 상호 의존관계를 설정할 수 없어 초기화가 어렵다.
MySQL 서버에서 기본으로 제공되는 컴포넌트에 대한 자세한 설명과 컴포넌트 개발과 관련된 자세한 사항은 MySQL 메뉴얼을 참조하자.
쿼리 실행 구조
쿼리 파서
쿼리 파서는 사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)으로 분리해 트리 형태의 구조를 만들어 내는 작업을 의미한다. 이렇게 만들어진 트리를 파서 트리라 하며, 쿼리 문장의 기본 문법오류는 이 과정에서 발견된다.
참고
파서 : 구성성분 단위로 문장(구문)을 분해 후 분석하는 것
전처리기
리턴받은 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지를 확인한다. 각 토큰을 테이블 이름이나 컬럼 이름 또는 내장 함수와 같은 개체를 매핑하여 해당 객체가 실제로 존재하는 지와 접근 권한 등을 확인한다. 실제 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰은 이 단계에서 걸러진다.
옵티마이저
단어 그대로 최적화기 이다. 사용자 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할을 담당한다. DBMS의 두뇌 역할을 한다고 볼 수 있다.
실행 엔진
옵티마이저가 두뇌 역할을 한다면 핸들러는 손과 발에 비유할 수 있다. 더 자세히 설명하자면 옵티마이저는 회사의 경영진, 실행 엔진은 중간 관리자, 핸들러는 각 업무의 실무자로 비유할 수 있다. (경영진이 결정하여 중간 관리자한테 요청하면 중간 관리자는 실제 일을할 실무자에게 업무를 주는 방식)
실행 엔진이 하는 일을 더 쉽게 이해할 수 있게 간단하게 예를 들어 살펴보자. 옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정했다고 해보자.
- 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
- 다시 실행 엔진은
WHERE절에 일치하는 레코드를 읽어오라고 핸들러에게 요청 - 읽어온 레코드들은 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청
- 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청
- 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김
즉, 실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행한다.
핸들러(스토리지 엔진)
앞에서 언급한 것 처럼 핸들러는 MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할을 담당한다.
즉, 핸들러는 결국 스토리지 엔진을 의미하며, MyISAM 테이블을 조작하는 경우에는 핸들러가 MyISAM 스토리지 엔진이 되고, InnoDB 테이블을 조작하는 경우에는 핸들러가 InnoDB 스토리지 엔진이 된다.
쿼리 캐시
MySQL 서버에서 쿼리 캐시(Query Cache)는 빠른 응답을 필요로 하는 웹 기반 응용 프로그램에서 매우 중요한 역할을 담당했다. 대부분의 웹 기반 응용 프로그램 서버에서 성능을 향상시키기 위해 캐시를 사용하는 것 처럼 MySQL 서버에서도 캐시를 사용한다고 생각하면 될 것 같다
쿼리 캐시는 SQL의 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 리턴하기 때문에 매우 빠른 성능을 보였다. 하지만 쿼리 캐시는 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들을 모두 삭제(Invalidate)해야 했다. 이는 심각한 동시 처리 성능 저하를 유발한다. 또한 MySQL 서버가 발전하면서 성능이 개선되는 과정에서 쿼리 캐시는 계속된 동시 처리 성능 저하와 많은 버그의 원인으로 지목됬다.
따라서, MySQL 8.0으로 올라오면서 쿼리캐시는 MySQL 서버의 기능에서 완전히 제거되었다.
스레드 풀
스레드 풀은 내부적으로 사용자의 요청을 처리하는 스레드의 개수를 줄여서 동시 처리되는 요청이 많다 하더라도 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해줘 서버의 자원 소모를 줄이는 것이 목적이다.
그렇다고 MySQL 서버에 스레드 풀을 설치하면 드라마틱한 성능 향상이 있는 것은 아니다. 스레드의 수를 적절히 제한해야 하며 그렇지 않으면 오히려 사용하기 전보다 성능이 저하될 수 있다.
물론 적절한 수를 사용하면 CPU의 프로세스 친화도(Processor affinity)도 높이고 OS 입장에서 불필요한 컨텍스트 스위치(Context swtich)를 줄여서 오버헤드도 낮출 수 있다.
참고
컨텍스트 스위치(Context Switch) : 여러 개의 프로세스가 실행되고 있을 때, 기존에 실행되던 프로세스를 중단하고 다른 프로세스를 실행하는 것
Percona Server의 스레드 풀은 기본적으로 CPU 코어의 개수만큼 스레드 그룹을 생성하는데, 스레드 그룹의 개수는 thread_pool_size 시스템 변수를 통해 조정할 수 있다. 하지만 일반적으로는 CPU 코어의 개수와 맞추는 것이 CPU 프로세서 친화도를 높이는데 좋다.
Percona Server의 스레드 풀 플러그인은 우선순위 큐(Priority Queue)를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공한다. 사용자로부터 요청이 유입된 작업 순서를 우선 순위 큐를 사용하여 적절하게 재배치한다. 이를 통해 전체적인 처리 성능을 향상시킬 수 있다.
트랜잭션 지원 메타데이터(데이터 딕셔너리)
데이터베이스 서버에서 테이블의 구조 정보와 스토어드 프로그램 등의 정보를 데이터 딕셔너리 또는 메타 데이터라 한다.
MySQL 8.0 버전부터는 테이블의 구조 정보나 스토어드 프로그램의 코드 관련 정보를 모두 InnoDB 테이블에 저장하도록 개선됬다. MySQL 서버가 작동하는데 기본적으로 필요한 테이블들을 묶어서 시스템 테이블이라 하는데, 대표적으로 사용자의 인증과 권한에 관련된 테이블들이 있다.
시스템 테이블과 데이터 딕셔너리 정보를 모두 모아서 mysql DB에 저장하는데, 통째로 mysql.ibd라는 이름의 테이블 공간에 저장된다. 그래서 MySQL 서버의 데이터 디렉터리에 존재하는 mysql.ibd 파일은 다른 *.ibd 파일과 함께 특별히 주의해야 한다.
참고
실제mysqlDB에서 테이블을 조회하면 실제 테이블의 구조가 저장된 테이블은 보이지 않는다. 데이터 딕셔너리 테이블의 데이터를 사용자가 임의로 사용하지 못하게 화면에 보여주지만 않을 뿐 실제로 테이블은 존재한다.
