3.1.1 Mysql 전체적인 구조

Mysql 은 크게 Mysql엔진과 스토리지 엔진으로 구분해 볼 수 있다.

Mysql 엔진

Mysql 엔진은 클라이언트로 부터 접속 및 쿼리 요청을 처리하는 커넥션 핸들러 와 SQL 파서 및 전처리기, 그리고 쿼리의 최적화된 실행을 위한 옵티마이저 가 중심을 이룬다. 그리고 성능 향상을 위해 MyISAM의 키 캐시나 InnoDB의 버퍼 풀과 같은 보조 저장소 기능이 포함되 있다.

스토리지 엔진

스토리지 엔진은 실질적으로 데이터를 디스크 스토리지에 저장하거나 디스크로 부터 데이터를 읽어오는 부분을 전담한다. Mysql 서버에서 Mysql엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다.

핸들러 API

Mysql 엔진의 쿼리 실행기에서 데이터를 읽기/쓰기 작업을 할때 스토리지에 읽기/쓰기 작업을 요청하는데, 이런 요청작업을 핸들러(Handler) 요청이라 하고, 여기에 사용되는 API를 핸들러API라고 한다.

3.1.2 Mysql 스레딩 구조

Mysql은 프로세스 기반이 아니라 스레드 기반으로 작동하며 크게 포그라운드(Foreground)와 백그라운드(Background)로 구분할 수 있다.

포그라운드 스레드

포그라운드 스레드는 최소한 Mysql 서버에 접속된 클라이언트의 수만큼 존재하며, 주로 클라이언트 사용자가 요청하는 쿼리 문장을 처리하는 것이 임무다.

포그라운드 스레드는 데이터를 Mysql의 데이터 버퍼나 캐시로부터 가져오며, 버퍼나 캐시에 없는 경우에는 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리한다. MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리하지만, InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리한다.

백그라운드 스레드

MyISAM과는 다르게 InnoDB에서는 여러가지 작업이 백그라운드로 처리된다. 그중 가장 중요한것이 로그 스레드(Log thread) 와 버퍼의 데이터를 디스크로 내려 쓰는 작업을 처리하는 쓰기 스레드(Write thread) 이다.

3.1.3 메모리 할당 구조

Mysql에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다.
두 영역의 차이는 Mysql 서버 내에 존재하는 많은 스레드가 공유해서 사용하는 공간인지 아닌지로 구분할 수 있다.

글로벌 메모리 영역

일반적으로 클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당된다.

로컬 메모리 영역

세션 메모리 영역이라고도 표현하며, 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역이다. 로컬메모리는 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다는 특징이 있다.

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

Mysql의 독특한 구조 중 대표적인 것이 바로 플러그인 모델이다. 쿼리 실행과정에서 데이터 읽기/쓰기 작업이 스토리지 엔진에 의해 처리된다. 복잡한 처리, 예를 들면 group by 나 order by 는 Mysql 의 쿼리 실행기에서 처리가 된다. 여기서 중요한점은 ‘하나의 쿼리 작업’ 은 ‘여러가지 하위 작업’ 으로 나뉘는데 각각의 하위 작업이 어디에서 처리되는지 구분할 줄 알아야 한다.
스토리지 엔진은 대표적으로 MyISAM과 InnoDB가 있다.

1
2
3
mysql> show engines; -- 이 명령어를 통해 지원되는 엔진 확인가능
...
mysql> show plugins; -- 플러그인 확인 명령어

3.1.5 쿼리 실행구조

파서

파서는 쿼리문장을 토큰으로 분리해 트리 형태의 구조로 만들어 내는 작업을 한다.

전처리기

파서에서 만들어진 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다.

옵티마이저

쿼리문장을 저렴한 비용으로 가장 빠르게 처리할 지 결정하는 역할을 담당한다.

실행 엔진

만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행한다.

핸들러(스토리지 엔진)

Mysql 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할을 담당한다.

3.1.6 복제(Replication)

데이터가 갈수록 대용량화돼 가는 것을 생각하면 확장성(Scalability)은 DBMS에서 아주 중요한 요소인데, Mysql에서 확장성을 위한 일반적인 기술로는 복제(Replication)를 들어볼 수 있다.

복제는 2대 이상의 Mysql 서버가 동일한 데이터를 담도록 실시간 동기화하는 기술이다. 서버의 역할에 따라서 master 와 slave 로 나눌 수 있다.

복제 주의사항

  • 슬레이브는 하나의 마스터만 설정 가능
  • 마스터와 슬레이브의 데이터 동기화를 위해 슬레이브는 일기 전용으로 설정
  • 슬레이브 서버용 장비는 마스터와 동일한 사양이 적합
  • 복제가 불필요한 경우에는 바이너리 로그 중지
  • 바이너리 로그와 트랜잭션 격리 수준(Isolation level)

3.1.7 쿼리 캐시

쿼리 캐시는 타 DBMS에는 없는 Mysql의 독특한 기능이다.

여러가지 복잡한 처리 절차와 꽤 큰 비용을 들여 실행된 결과를 쿼리 캐시에 담아 두고, 동일한 쿼리 요청이 왔을 때 간단하게 쿼리 캐시에서 찾아 바로 결과를 준다.

쿼리 캐시는 SQL 문장의 결과는 메모리에 캐시해 두는 기능이다. 쿼리 캐시의 구조는 간단한 키와 값의 쌍으로 관리되는 맵(Map)과 같은 데이터 구조로 구현되 있다. 여기서 키는 쿼리문장 , 값은 쿼리 실행결과 형태로 저장되어 있다.

쿼리 캐시를 사용할 경우 캐시에 있는 쿼리 결과를 넘기기 전에 다음과 같은 확인절차가 필요하다.

  1. 요청된 쿼리 문장이 쿼리 캐시에 존재하는가?
  2. 해당 사용자가 그 결과를 볼 수 있는 권한을 가지는가?
  3. 트랜젝션 내에서 실행된 쿼리인 경우, 그 결과가 가시범위 내의 트랜젝션에서 만들어진 결과인가?
  4. 쿼리에 사용된 기능이 캐시되도 동일한 결과를 보장할 수 있는가?
    • Current_date, sysdate, rand 등과 같이 호출 시점에 따라 결과가 달라지는 요소가 있는가?
    • 프리페어 스테이트먼트의 경우 변수가 결과에 영향을 미치지 않는가?
  5. 캐시가 만들어지고 난 이후 해당 데이터가 다른 사용자에 의해 변경되지 않았는가?
  6. 쿼리에 의해 만들어진 결과가 캐시하기에 너무 크지 않은가?
  7. 그 밖에 쿼리 캐시를 사용하지 못하게 만드는 요소가 사용됐는가?

이런 제약사항들에도 불구하고 쿼리캐시는 훌륭한 기능이다. 다음을 통해 쿼리캐시 관련 데이터들을 볼 수 있다.

1
2
3
mysql> show global status like 'Qcache%'; -- 쿼리캐시 관련 데이터
....
mysql> show global status like 'Com_select';

Qcache_hits는 쿼리캐시로 처리된 select 쿼리의 수를 의미하며, Com_select 쿼리캐시에서 결과를 찾지 못해서 직접 실행한 횟수이다. 즉 Com_select 와 Qcache_hits를 더하면 Mysql 서버로 요청된 모든 select 문장의 총 합이 되는 것이다.

쿼리 캐시 히트율(%) = Qcache_hits/ (Qcache_hits + Com_select) * 100

보통 쿼리캐시 히트율이 20% 이상이면 쿼리캐시를 사용하는 것이 좋다고 한다. 하지만 히트율이 1% 라도 아주 큰 자원을 소모하는 쿼리의 경우 쿼리캐시를 사용하는 것이 좋다.