3.1 MySQL 아키텍처

3.1.1 MySQL의 전체 구조

• MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 구분해서 볼 수 있다.
  • MySQL 엔진 : 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러, SQL 파서 및 전처리기, 그리고 옵티마이저가 중심을 이룬다.
  • 스토리지 엔진 : 실제 데이터를 디스크 스토리지에 저장하거나, 디스크 스토리지로부터 데이터를 읽어오는 부분을 전담한다.
• MySQL 서버에서 MySQL 엔진은 하나이지만, 스토리지 엔진은 여러 개를 동시에 사용할 수 있다.

CREATE TABLE test_table(fd1 INT, fd2 INT) ENGINE=INNODB;

3.1.2 MySQL 스레딩 구조

• MySQL 서버는 프로세스 기반이 아닌 스레드 기반으로 작동하며, 크게 포그라운드(Foreground) 스레드와 백그라운드(Background) 스레드로 구분할 수 있다.
• 포그라운드 스레드(클라이언트 스레드)
  • 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 각 클라리언트 사용자가 요청한 쿼리 문장을 처리하는 것이 임무다.
  • 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면, 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시(Thread Pool)로 되돌아간다.
  • 하지만 일정 개수 이상 대기중인 스레드가 이다면 스레드를 종료시킨다.(thread_cache_size 기준)
  • 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오며, 버퍼 혹은 캐시가 없을 경우 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리한다.
  • MyISAM은 디스크 쓰기 작업까지 포그라운드 스레드가 처리하고, InnoDB는 데이터 버퍼, 캐시까지만 포그라운드 버퍼가 처리하고 나머지는 백그라운드 스레드가 처리한다.
    
• 백그라운드 스레드
  • MyISAM의 경우 별로 해당사항이 없지만, InnoDB의 경우 여러 작업이 백그라운드에서 처리된다.
  • 주로 인서트 버퍼를 병합하는 스레드, 로그를 디스크로 기록하는 스레드, InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드, 데이터를 버퍼로 읽어들이는 스레드, 데드락 모니터링 스레드, 모든 스레드를 총괄하는 메인 스레드가 있다.
  • 그 중 중요한 스레드는 로그 스레드(Log thread)와 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리하는 쓰기 스레드(Write thread)이다.

SQL 처리 도중, 데이터의 쓰기 작업은 지연(버퍼링)되어 처리 될 수 있지만, 데이터의 읽기 작업은 수시로 진행해야 한다. 그래서 일반적인 상용 DBMS에서는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재되어 있으며, InnoDB도 이와 같은 방식으로 동작한다.

• 참고 : MyISAM은 데이터가 디스크의 데이터 파일로 완전히 저장될 때 까지 기다려야한다.

3.1.3 메모리 할당 및 사용 구조

• MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다.

• 글로벌 메모리 영역은 스레드간 공유하는 데이터다.

• 글로벌 메모리 영역
  • 클라이언트 스레드 수와 무관하게 하나의 메모리 공간만 할당된다.
• 로컬 메모리 영역
  • 세션 메모리 영역이라고도 표현되며, 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역이다.
  • 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다는 특징이 있다.
  • 로컬 메모리 공간은 커넥션이 열려 있는 동안 계속 할당된 상태로 남아있는 공간도 있고(커넥션 버퍼나 결과 버퍼), 그렇지 않고 쿼리를 실행하는 순간에만 할당했다가 해제하는 공간(소트버퍼 혹은 조인버퍼)도 있다.

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

• MySQL 서버에서는 전문 검색 엔진을 위한 검색어 파서(인덱싱할 키워드를 분리하는 작업)을 플러그인 형태로 개발해서 사용할 수 있다.
• 스토리지 엔진 또한 플러그인 형식으로 적용할 수 있다.

3.1.5 쿼리 실행 구조

SQL 쿼리가 실행되는 관점에서 MySQL의 구조는 아래와 같이 나누어져있다고 볼 수 있다 :

• 파서 : 사용자의 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 분리하는 작업을 의미 한다. 쿼리 문장의 기본 문법 오류는 이 단계에서 발견된다.
• 전처리 : 파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적 문제점이 있는지 확인한다. 주로 객체 존재 여부와 객체의 접근 권한을 확인하는 과정을 거친다.
• 옵티마이저 : 옵치마이저란 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리하는 방법을 결정하는 역할을 담당한다.
• 실행 엔진 : 옵티마이저가 두뇌라면, 실행 엔진은 손과 발에 비유할 수 있다.
• 핸들러 : MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 디스크를 저장하고, 디스크로부터 읽어오는 역할을 수행한다. 결국 스토리지 엔진을 의미한다.

3.1.6 복제(Replication)

• MySQL의 복제는 레플리케이션(Replication)이라고도 하는데, 복제는 2대 이상의 MySQL 서버가 동일한 데이터를 담도록 실시간으로 동기화 하는 기술을 말한다.
• 일반적으로 MySQL의 복제는, INSERT/UPDAE등 데이터를 변경할 수 있는 MySQL 서버와 SELECT등으로 데이터를 조회만 할 수 있는 MySQL 서버로 나뉜다. 전자를 마스터(Master)라고 하고, 후자를 슬레이브(Slave)라고 한다.
• 일반적으로 MySQL 서버의 복제에서 마스터는 반드시 1개이며, 슬레이브는 1개 이상으로 구성될 수 있다.
• 보통 하나의 MySQL이 마스터 또는 슬레이브 가운데 하나의 역할만 수행하지만, 두가지 역할 동시에 수행할수도 있다.

마스터(Master)

• MySQL의 바이너리 로그가 활성화되면 어떤 MySQL 서버든 마스터가 될 수 있다.
• 애플리케이션 입장에서는 주로 데이터 생성, 변경 및 삭제되는 주체라고 볼 수 있다.
• 마스터 서버에 실행되는 DML, DDL 가운데 데이터 구조나 내용을 변경하는 모든 쿼리 문장은 바이너리 로그에 기록한다. 슬레이브 서버에서 변경 내역을 요청하면, 마스터 장비는 그 바이너리 로그를 읽어 슬레이브로 넘긴다.(Binlog dump라는 스레드에서 진행하며, 하나의 슬레이브 서버당 하나의 스레드가 존재)

슬레이브(Slave)

• 데이터(바이너리 로그)를 받아올 마스터 장비의 정보를 갖고 있는 경우, 슬레이브가 된다.
• 슬레이브 서버는 릴레이 로그를 갖고 있다.(마스터는 바이너리 로그)
• 슬레이브 서버의 I/O 스레드는 마스터 서버에 접속해 변경 내역을 요청하고, 받아 온 변경 내역을 릴레이 로그에 기록한다. 그리고 슬레이브 서버의 SQL 스레드가 릴레이 로그에 변경 내역을 재실행함으로써 슬레이브의 데이터를 마스터와 동일한 상태로 유지한다.
• I/O 스레드와 SQL스레드는 마스터 MySQL에서는 기동되지 않으며, 복제가 설정된 슬레이브 MySQL 서버에서 자동으로 기동한다.

복제시 간과하는 부분이 있을 수 있으며, 아래 사항에 대해 주의해야 한다.

• 슬레이브는 하나의 마스터만 설정 가능
• 마스터와 슬레이브의 데이터 동기화를 위해 슬레이브는 읽기 전용으로 설정
• 슬레이브 서버용 장비는 마스터와 동일한 사양이 적합
  • 마스터 서버에 수많은 동시 사용자가 실행한 데이터 쿼리 변경 쿼리 문장이 슬레이브 서버에서는 하나의 스레드로 처리되어야 하기 때문에, 변경이 잦은 서버일 수록 마스터 서버의 사양보다 슬레이브 서버의 사양이 더 좋아야 한다.
• 복제 불필요시 바이너리 로그 중지
  • 복제시 MySQL이 많은 자원을 소모하고 있기 때문에, 복제가 불필요할 때에는 바이너리 로그를 중지시키자.

3.1.7 쿼리 캐시

• 쿼리 캐시는 타 DBMS에는 없는 MySQL의 독특한 기능 중 하나이다.
• 적절히 설정해두면 상당한 성능 효과를 얻을 수 있다.
• 쿼리 캐시는 SQL 문장을 캐시하는 것이 아니라, 쿼리의 결과를 메모리에 캐시해두는 기능이다. 키와 값의 쌍으로 관리되는 맵과 같은 데이터 구조로 구현되어 있다. (키 : 쿼리, 값 : 결과 )
• 쿼리 캐시 결과를 내려보내기 전, 아래의 절차를 거친다.
  • 요청된 쿼리 문장이 쿼리 캐시에 존재하는가?
  • 해당 사용자가 그 결과를 볼 수 있는 권한을 갖고 있는가?
  • 트랜잭션 내 실행된 쿼리인 경우, 그 결과가 가시 범위 내 트랜잭션에서 만들어진 결과인가?
  • 쿼리에 사용된 기능이 캐시되어도 동일한 결과를 보장할 수 있는가?
  • 캐시가 만들어지고 난 후, 해당 데이터가 다른 사용자에 의해 변경되지 않았는가?
  • 쿼리에 의해 만들어진 결과가 캐시하기에 너무 크지 않은가?
  • 그 밖에 캐시를 사용하지 못하게 만드는 요소가 사용되었는가?

쿼리 캐시를 사용하지 못하게 되는 수많은 제약사항이 있어도, 쿼리 캐시는 그만큼 효과를 충분히 얻을 수 있는 기능이다.

• 쿼리 캐시는 MySQL GLOBAL STATUS를 통해 캐시 히트율을 계산할 수 있는데, Qcache_hits(쿼리 캐시로 처리된 SELECT 쿼리의 수), Com_select(쿼리 캐시에서 결과를 찾지 못해 MySQL 서버가 쿼리를 실행한 횟수)를 통해 계산 가능하다.
  • 쿼리 캐시 히트율(%) = Qcache_hits / (Qcache_hits + Com_select) * 100
  • 쿼리 캐시의 히트율이 20% 이상이면 일반적으로 쿼리 캐시를 사용하는 것이 좋다고 한다.(물론 절대적인 수치는 아니고, 해당 쿼리가 사용하는 자원이나 시간에 따라 추가적으로 고려한다)

query_cache_size = 0
query_cache_type = 0

• 쿼리 캐시 미사용시, 컨피규레이션 파일의 설정 파라미터를 위와 같이 변경하자.

참고자료 : Real MySQL - 3.1 MySQL 아키텍처