01. MySQL 아키텍처

0. MySQL이란?

  MySQL은 세계에서 가장 많이 쓰이는 오픈 소스의 관계형 데이터베이스 관리 시스템(RDBMS)중 하나로, 다중 스레드 및 다중 사용자 형식의 데이터베이스 관리 시스템이자 오라클에서 배포한 무료 오픈소스이다. 따라서 많은 웹 애플리케이션이 MySQL을 기본 데이터베이스로 채택하고 있다.

 

1. MySQL의 전체 구조

MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 구분된다.

  1) MySQL 엔진

• DBMS의 두뇌 역할!!
• 요청된 SQL 문장을 분석하거나 최적화 수행
• 커넥션 핸들러(클라이언트로부터의 접속 및 쿼리 요청을 처리)와 SQL 파서, 전처리기, 그리고 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룬다.
• 성능 향상을 위한 보조 저장소 기능(MyISAM의 키 캐시나 InnoDB의 버퍼 풀 등)이 포함되어 있다.

  2) 스토리지 엔진

• 실제 데이터를 디스크 스토리지에 저장하거나, 디스크 스토리지로부터 데이터를 읽어오는 부분 담당
• MySQL 서버에서 여러 개를 동시에 사용할 수 있다.
• InnoDB, MyISAM 등
• 테이블이 사용할 스토리지 엔진을 지정하면 이후 해당 테이블의 모든 DML작업은 정의된 스토리지 엔진이 담당하여 처리한다.

mysql> CREATE TABLE tablename (fd1 INT, fd2 INT) ENGINE=INNODB;

  3) 핸들러 API

• 핸들러(Handler) 요청 : MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 일이 생겼을 때, 각 스토리지 엔진에게 하는 요청(쓰기/읽기 등)
• 핸들러 API = 핸들러 요청에서 사용되는 API
• MySQL 엔진이 스토리지 엔진을 조정하기 위해 사용하는 것
• 즉, 상태 변수 중 "Handler%"로 시작하면 MySQL 엔진이 각 스토리지 엔진에게 보낸 명령의 횟수를 의미하는 변수이다. 데이터(레코드) 작업이 얼마나 있었는지를 보려면 아래의 명령으로 확인할 수 있다.

mysql> SHOW GLOBAL STATUS LIKE 'Handler%';

 

2. 메모리 할당 및 사용 구조

 글로벌 메모리 영역과 로컬 메모리 영역의 차이

 => MySQL 서버 내에 존재하는 많은 스레드가 공유해서 사용하는 공간인지? 아닌지?

 

  1) 글로벌 메모리 영역

• 클라이언트 스레드 수와 무관하게 하나의 메모리 공간만 할당됨
• 생성된 글로벌 영역이 N개라 하더라도 모든 스레드에 의해 공유된다.

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

• MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역
• 각 클라이언트 스레드별로 독립적 할당(절대 공유되어 사용 X)
• 커넥션 버퍼, 정렬(Sort) 버퍼 등
• 각 쿼리의 용도별로 필요할 때만 공간이 할당되고, 필요하지 않은 경우에는 MySQL이 할당하지 않음

 

3. MySQL의 쿼리 실행 구조

 

  1) 클라이언트가 MySQL 서버에 쿼리를 요청한다.

  2) SQL 파서가 쿼리 문장을 토큰단위로 분리해 트리 형태의 구조로 만들어 낸다. 이 때 쿼리 문장의 기본 문법 오류가 발견된다.

  3) 파서 트리를 기반으로 전처리기에 넘어가 쿼리 문장에 구조적인 문제점이 있는지(해당 객체 존재 여부, 접근권한 확인 등) 확인한다.

  4) 옵티마이저는 쿼리 문장을 가장 최적화(저렴한 비용으로 가장 빠르게)하여 처리하도록 결정하는 역할을 한다. 쿼리 변환, 비용 최적화, 실행계획 계산 등의 일을 수행한다.

  5) 실행 엔진 : 옵티마이저가 경영진이라면 실행 엔진은 중간 관리자, 핸들러는 각 업무의 실무자로 비유할 수 있다. 실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행한다.

  6) 핸들러가 MySQL 실행 엔진의 요청에 따라 데이터를 디스크 스토리지에 저장하고 디스크로부터 읽어 오는 역할을 수행한다.

 

3-1. 복제(Replication)

  MySQL에서의 복제란 데이터베이스의 확장성을 위한 방법 중 하나로, 2대 이상의 MySQL 서버가 동일한 데이터를 담도록 실시간 동기화하는 기술이다. 쓰기와 읽기의 역할로 구분해서 INSERT나 UPDATE와 같은 쿼리를 이용해 데이터를 변경할 수 있는 마스터(Master)와, SELECT 쿼리로 데이터를 읽기만 할 수 있는 슬레이브(Slave)로 나뉜다. MySQL의 복제는 마스터에서 처리된 내용이 바이너리 로그에 기록되고, 그 내용이 슬레이브 MySQL 서버로 전달되어 재실행되는 방식으로 처리된다. 슬레이브 서버가 마스터 서버에 접속해 변경 내역을 요청하고, 슬레이브 서버의 SQL 스레드가 릴레이 로그에 기록된 변경 내역을 재실행함으로써 슬레이브의 데이터를 마스터와 동일한 상태로 유지하는 원리이다.

 

마스터(Master)

• INSERT, UPDATE 등의 쓰기 작업을 통해 데이터를 변경하는 경우
• 바이너리 로그가 활성화되면 어떤 MySQL 서버든 마스터가 될 수 있지만, MySQL 서버에서 반드시 1개만 존재
• 마스터 서버에서 실행되는 DML, DDL 중 데이터의 구조나 내용을 변경하는 모든 쿼리 문장은 바이너리 로그에 기록된다.

슬레이브(Slave)

• SELECT 읽기 작업 (읽기 전용으로 설정한다.)
• MySQL 서버에서 1개 이상으로 구성 가능
• 데이터(binLog)를 바아 올 마스터 장비의 정보(IP주소, 포트 정보 및 접속 계정)을 가지고 있는 경우
• 슬레이브 서버의 I/O 스레드가 마스터 서버에 접속해 변경 내역을 요청하고, 받아 온 변경 내역을 릴레이 로그에 기록한다.
• I/O스레드(Slave_IO_Therad)와 SQL스레드(Slave_SQL_Thread)는 마스터 MySQL에서는 기동되지 않으며, 복제가 설정된 슬레이브 MySQL 서버에서 자동적으로 기동하는 스레드이다.

MySQL에서 복제를 할 때는 아래 사항을 잊지 말고 주의하자.

• 하나의 슬레이브는 하나의 마스터만 가질 수 있다. (하나의 마스터에 N개의 슬레이브는 가능)
• 데이터는 마스터로 접속해서 변경해야 하므로, 슬레이브는 읽기 전용으로 설정한다.
• 슬레이브 서버용 장비라고 마스터 서버용 장비보다 한 단계 낮은 장비 X! 동일한 사양이 적합하다.
• 복제가 불필요한 경우에는 바이너리 로그를 중지한다. 성능에 어마어마한 영향을 끼친다..(갭 락 유지, 트랜잭션 커밋 시마다 기록 등)
• 마스터와 슬레이브의 데이터 일치를 위해 격리 수준은 REPEATABLE READ만 사용한다. 바이너리 로그 파일에서 STATEMENT 포맷 방식은 SQL 문장 하나만 슬레이브로 전달되므로 네트워크 트래픽을 많이 유발하지는 않는다. 하지만 SQL 기반 복제가 정상적으로 작동하기 위해서는 REPEATABLE READ 이상의 트랜잭션 격리 수준이 필요하며, 이로 인해 InnoDB 테이블에서는 갭 락과 넥스트 키 락이 필요해진다. 반면, 레코드 기반의 ROW 포맷 방식에서 복제는 마스터와 슬레이브 서버 간의 네트워크 트래픽은 많이 발생할 수 있지만, READ-COMMITTED 수준에서도 정상 작동하며 InnoDB 테이블에서의 잠금 경합은 줄어든다.

 

 

 

다음 글에서는 MySQL의 중요한 기능 중 하나인 쿼리 캐시에 대해 다뤄야겠당 !! 히히 😁