내맘대로 정리하는 Real MySQL #3장) 아키텍처 - MySQL 아키텍처 편

<내맘대로 정리하는> 시리즈는, 책을 읽으며 몰랐던 내용을 위주로 정리한 내용 그대로 포스팅하는 시리즈입니다 ^^

원문의 문맥이 궁금하면 (좋은 책이니) 이 참에 하나 장만하는 것은 어떤가요??

MySQL 전체 구조

• MySQL 엔진
  • 커넥션 핸들러 : 클라이언트로부터의 접속 및 쿼리 요청 처리
  • SQL 파서 및 전처리기
  • 옵티마이저 : 쿼리의 최적화된 실행에 도움
  • 성능 향상을 위한 MyISAM의 키 캐시나 InnoDB같은 보조 저장소 기능
• 스토리지 엔진 : 실제 데이터를 디스크에 저장하거나 디스크로부터 데이터를 읽어오는 부분
• 핸들러 요청 : MySQL 엔진 → 스토리지 엔진에 보내는 요청
• 핸들러 API : 핸들러 요청에서 사용되는 API, SHOW GLOBAL STATUS LIKE 'Handler%'로 확인 가능하다.

스레드 구조

• MySQL 서버는 스레드 기반으로 작동한다
• Foreground Thread
  • 캐시된 Foreground Thread는 Thread Pool에 존재하는 Thread이다.
  • MySQL 서버에 접속된 클라이언트 수만큼 존재한다. 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리하는 것이 임무이다. 작업이 종료되면 Thread Pool로 돌아가며 thread_cache_size 옵션에 의해 수가 조절된다.
  • foreground thread는 데이터는 MySQL의 데이터 버퍼나 캐시로부터 가져오며, 데이터가 없으면 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어온다. MyISAM 테이블의 경우는 쓰기 작업도 foreground thread가 처리하지만, InnoDB 테이블은 데이터 버퍼나 캐시까지만 foreground thread가 처리하고 나머지 버퍼로부터 디스크까지 기록하는 작업은 background thread가 처리한다.
• Background Thread : InnoDB는 많은 background thread가 존재한다.
  • Insert Buffer를 병합하는 Thread, 로그를 디스크로 기록하는 Thread, InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 Thread, 데이터를 버퍼로 읽어들이는 Thread, 여러 가지 잠금이나 데드락을 모니터링하는 Thread 등이 있다. 이러한 모든 Thread를 총괄하는 메인 Thread도 존재한다.
  • 가장 중요한 것은 log thread와 write thread이다.
• 일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재되 있으며 InnoDB 또한 이러한 방식으로 처리한다.

메모리 할당 및 사용 구조

• 글로벌 메모리 영역
  • MySQL 서버가 시작되면서 무조건 운영체제로부터 할당된다 (한번에 or 점차적으로)
  • 스레드 수와 무관하게 일반적으로 하나의 메모리 공간이 할당되며 모든 스레드에 의해 공유된다
• 로컬 메모리 영역
  • 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역
  • 스레드별로 독립적으로 할당되며 절대 공유되어 사용하지 않는다
  • 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우에는MySQL이 메모리 공간을 할당조차도 하지 않을 수 있다
  • 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간도 있고(커넥션 버퍼, 결과 버퍼) 그렇지 않고 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간(소트 버퍼, 조인 버퍼)도 있다.

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

SHOW ENGINES; 명령어로 확인할 수 있다.

쿼리 실행 구조

• 파서
  • 사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조를 만들어낸다. 문법적 오류는 이때 발견된다.
• 전처리기
  • 파서 트리를 바탕으로 쿼리 문장에 구조적인 문제점을 확인한다. 테이블 이름, 칼럼 이름 등의 객체 존재여부나 접근권한을 체크한다.
• 옵티마이저
  • 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리하는 결정을 내린다.
  • 이책의 내용은 대부분 옵티마이저가 선택하는 내용을 설명하는 것이며, 어떻게 하면 옵티마이저가 더 나은 선택을 할 수 있게 유도하는가를 알려주는 것이라 생각해도 될 정도로 옵티마이저의 역할은 중요하고 영향 범위 또한 아주 넓다.
• 실행엔진 (쿼리 실행기)
  • 옵티마이저에서 내려온 계획대로 각 핸들러(스토리지 엔진)에게 요청해서 받은 결과를 또 다른 핸들러 요청으로 연결하는 역할을 수행한다.
• 스토리지 엔진
  • 실행엔진 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어 오는 역할을 담당한다

복제

복제 : 2대 이상의 MySQL 서버가 동일한 데이터를 담도록 실시간으로 동기화하는 기술

MySQL에서는 쓰기와 읽기의 역할로 구분해, 전자를 마스터, 후자를 슬레이브라 한다.

• 마스터
  • 마스터 서버에서 실행되는 DML, DDL 가운데 데이터의 구조나 내용을 변경하는 모든 쿼리 문장은 바이너리 로그에 기록한다.
  • 슬레이브 서버에서 변경 내역을 요청하면 마스터 장비는 그 바이너리 로그를 읽어 슬레이브로 넘긴다.
  • Binlog dump 라는 스레드가 이 일을 전담하는 스레드이다.
• 슬레이브
  • 마스터 서버가 바이너리 로그를 가지고 있다면 슬레이브 서버는 릴레이 로그를 가지고 있다.
  • 슬레이브 서버의 I/O 스레드는 마스터 서버에 접속해 변경 내역을 요청하고, 받아 온 변경 내역을 릴레이 로그에 기록한다. 슬레이브 서버의 SQL 스레드가 릴레이 로그에 기록된 변경 내역을 재실행함으로써 슬레이브의 데이터를 마스터와 동일한 상태로 유지한다

몇 가지 팁

• 슬레이브는 읽기 전용으로 설정하는 것이 일반적이다.
• 슬레이브 서버는 마스터 서버가 다운된 경우 그에 대한 복구 대안으로 사용될 때도 많기 때문에 사양을 동일하게 맞추는 경우가 대부분이다.
• 바이너리 로그를 기록하는 작업은 AutoCommit이 활성화된 MySQL 서버에서 더 심각한 부하로 나타날 때가 많다. 특히 트랜잭션을 지원하지 않는 MyISAM 테이블은 항상 AutoCommit 모드로 작동하기 때문에 InnoDB 테이블보다 바이너리 로그를 기록하는 데 더많은 자원을 사용하게 된다.
• 바이너리 로그는 어떤 내용이 기록되느냐에 따라 두 가지 방식이 있다.
  • STATEMENT 포맷 방식 : 마스터에서 실행되는 쿼리 방식을 저장하는 방식
    • 네트워크 트래픽을 많이 유발하지 않는다.
    • 정상적으로 작동하려면 REPEATABLE-READ 이상의 트랜잭션 격리 수준을 사용해야 하며, 그로 인해 InnoDB 테이블에서는 레코드 간의 간격을 잠그는 갭락이나 넥스트 키 락이 필요해진다.
  • ROW 포맷 방식 : 마스터에서 실행된 쿼리에 의해 변경된 레코드 값을 기록하는 방식
    • 네트워크 트래픽을 많이 발생시킬 수 있지만 READ-COMMITTED 트랜잭션 격리 수준에서도 작동할 수 있으며 InnoDB 테이블에서 잠금의 경합은 줄어든다.

쿼리 캐시

쿼리 캐시는 단어의 의미와는 달리 SQL 문장을 캐시하는 것이 아니라 쿼리의 결과를 메모리에 캐시해 두는 기능이다. 쿼리 캐시는 key(쿼리)-value(결과)로 이루어져 있다.

쿼리 캐시의 결과를 내려 보내기 전에 확인해야 하는 내용들

• 요청된 쿼리 문장이 쿼리 캐시에 존재하는가?
  • 애플리케이션 전체 쿼리 가운데 동일하거나 비슷한 작업을 하는 쿼리는 하나의 쿼리로 통일해 문자열로 관리하는 것이 좋다 (옛날 방식인듯..?) 그렇다고 무리하게 쿼리를 통합하라는 것이 아니며, 적절히 추가 비용이 없이 변경 가능한 것들을 통합하라는 것이다.
• 해당 사용자가 그 결과를 볼 수 있는 권한을 가지고 있는가?
• 트랜잭션 내에서 실행된 쿼리인 경우 가시 범위 내에 있는 결과인가?
  • 가시 범위 : 각 트랜잭션은 자신의 ID보다 ID값이 큰 트랜잭션에서 변경한 작업 내역이나 쿼리 결과는 참조할 수 없다.
  • InnoDB의 모든 트랜잭션은 각 트랜잭션 ID가 있으며, 트랜잭션 ID는 트랜잭션이 시작된 시점을 기준으로 순차적으로 증가하는 6byte 숫자값이다
• 호출 시점에 따라 달라지는 요소가 있는가? (ex. CURRENT_DATE())
• Prepare Statement의 경우 변수가 결과에 영향을 미치지 않는가?
  • Application에서 Prepare Statement를 사용하더라도 MySQL에서는 그 형태로 실행되지 않는다.
  • 진정한 Server side prepare statement를 위해서는 DB connection을 생성할때 특별한 옵션을 써야 한다.
• 캐시가 만들어나고 난 이후 해당 데이터가 다른 사용자에 의해 변경되지 않았는가?
• 쿼리에 의해 만들어진 결과가 캐시하기에 너무 크지 않은가?
  • query_cache_limit 옵션으로 설정할 수 있다
• 그 밖에 쿼리 캐시를 사용하지 못하게 하는 요소
  • 임시 테이블에 대한 쿼리
  • 사용자 변수의 사용
  • 칼럼 기반의 권한 설정
  • LOCK IN SHARE MODE 힌트
  • FOR UPDATE 힌트
  • UDF의 사용
  • 독립적인 SELECT 문장이 아닌 일부분의 서브 쿼리
  • 스토어드 루틴에서 사용된 쿼리
  • SQL_NO_CACHE 힌트

MySQL 서버에서 실행되는 작업은 대부분 MySQL 서버의 상태 변수에 누적되어 기록되기 때문에 아래와 같이 "SHOW GLOBAL STATUS" 명령어를 이용해 쿼리 캐시가 얼마나 사용됐고 MySQL 서버에서 SELECT 쿼리가 얼마나 실행됐는지 등에 대한 정보를 확인해볼 수 있다.

• Qcache_hits : 쿼리 캐시로 처리된 SELECT 쿼리수
• Com_select : 쿼리 캐시에서 처리하지못해 MySQL서버가 찾은 SELECT 쿼리수