Real MySQL 04. 아키텍처

솔트커피·2022년 10월 12일
Real MySQLmysqlrdb
0
post-thumbnail

4.1 MySQL 엔진 아키텍처

4.1.1 MySQL의 전체 구조

4.1.1.1 MySQL 엔진

클라이언트로부터의 접속 및 쿼리를 요청하는 커넥션 핸들러와 SQL 파서 및 전처리기, 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룬다.

요청된 SQL 문장을 분석하거나 최적화 하는 등 DBMS의 두뇌에 해당하는 처리를 수행한다.

4.1.1.2 스토리지 엔진

실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 작업 수행
MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다.

각 스토리지 엔진은 성능 향상을 위해 키 캐시(MyISAM 스토리지 엔진)나 InnoDB 버퍼 풀(InnoDB 스토리지 엔진)과 같은 기능을 내장하고 있다.

4.1.1.3 핸들러 API

MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에 쓰기 또는 읽기를 요청하는데 이러한 요청을 핸들러(Handler) 요청이라 하고, 여기서 사용되는 API를 핸들러 API라고 한다.

4.1.6 쿼리 실행 구조

4.1.6.1 쿼리 파서

사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)로 분리해 트리 형태의 구조로 만들어 내는 작업을 의미한다.
쿼리 문장의 기본 문법 오류는 이 과정에서 발견되고 사용자에게 오류 메시지를 전달하게 된다.

4.1.6.2 전처리기

파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다.
각 토큰을 테이블 이름이나 칼럼 이름, 또는 내장 함수와 같은 개체를 매핑에 해당 객체의 존재 여부와 접근 권한 등을 확인하는 과정을 이 단계에서 수행한다.

4.1.6.3 옵티마이저

사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할을 담당하며, DBMS의 두뇌에 해당한다고 볼 수 있다.

4.1.6.4 실행 엔진

옵티마이저가 두뇌라면 실행 엔진과 핸들러는 손과 발에 비유할 수 있다.
실행 엔진이 하는 일을 더 쉽게 이해할 수 있게 간단하게 예를 들어 살펴보자.
옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정했다고 해보자.

  1. 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
  2. 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
  3. 읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청
  4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 다시 핸들러에게 요청
  5. 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김

4.1.6.5 핸들러(스토리지 엔진)

앞에서 잠깐 언급한 것처럼 핸들러는 MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어 오는 역할을 담당한다.
핸들러는 결국 스토리지 엔진을 의미하며, MyISAM 테이블을 조작하는 경우에는 핸들러가 MyISAM 스토리지 엔진이 되고, InnoDB 테이블을 조작하는 경우에는 핸들러가 InnoDB 스토리지 엔진이 된다.

4.1.9 스레드 풀

스레드 풀은 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많다 하더라도 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이 목적이다.

많은 사람들이 MySQL 서버에서 스레드 풀만 설피하면 성능이 그냥 두 배쯤 올라갈 거라고 기대하는데, 실세 서비스에서 눈에 띄는 성능 향상을 보여준 경우는 드물었다.

앞서 소개한 것처럼 동시에 실행 중인 스레드들을 CPU가 최대한 잘 처리해낼 수 있는 수준으로 줄여서 빨리 처리하게 하는 기능이기 때문에 스케줄링 과정에서 CPU 시간을 제대로 확보하지 못하는 경우에는 쿼리 처리가 더 느려지는 사례도 발생할 수 있다는 점에 주의하자.

물론 제한된 수의 스레드 만으로 CPU가 처리하도록 적절히 유도한다면 프로세서 친화도(Processor affinity)도 높이고 운영체제 입장에서는 불필요한 컨텍스트 스위치(Context Switch)를 줄여서 오버헤드를 낮출 수 있다.

Percona Server의 스레드 풀 플러그인은 선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공한다.
이렇게 먼저 시작된 트랜잭션 내에 속한 SQL을 빨리 처리해주면 해당 트랜잭션이 가지고 있던 잠금이 빨리 해제되고 잠금 경합을 낮춰서 전체적인 처리 성능을 향상 시킬 수 있다.

4.2 InnoDB 스토리지 엔진 아키텍처

InnoDB는 MySQL에서 사용할 수 있는 스토리지 엔진 중 거의 유일하게 리코드 기반의 잠금을 제공하며, 그 때문에 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다.

4.2.1 프라이머리 키에 의한 클러스터링

InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링 되어 저장된다.
즉, 프라이머리 키 값의 순서대로 디스크에 저장된다는 뜻이며, 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용한다.

프라이머리 키가 클러스터링 인덱스이기 때문에 프라이머리 키를 이용한 레인지 스캔은 상당히 빨리 처리될 수 있다.
결과적으로 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정 된다.

MyISAM 스토리지 엔진에서는 클러스터링 키를 지원하지 않는다. 그래서 MyISAM 테이블에서는 프라이머리 키와 세컨더리 인덱스는 구조적으로 아무런 차이가 없다.
프라이머리 키는 유니크 제약을 가진 세컨더리 인덱스일 뿐이다. 그리고 MyISAM 테이블의 프라이머리 키를 포함한 모든 인덱스는 물리적인 레코드의 주소 값(ROWID)을 가진다.

4.2.2 외래 키 지원

외래 키에 대한 지원은 InnoDB 스토리지 엔진 레벨에서 지원하는 기능으로 MyISAM이나 MEMORY 테이블에서는 사용할 수 없다.
외래 키는 데이터베이스 서버 운영의 불편함 떄문에 서비스용 데이터베이스에서는 생성하지 않는 경우도 자주 있는데, 그렇다 하더라도 개발 환경의 데이터베이스에서는 좋은 가이드 역할을 할 수 있다.

  • InnoDB에서 외래키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성 필요
  • 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업 필요
    • 따라서 잠금(lock)이 여러 테이블로 전파 됨
    • 그로 인해 데드락이 발생할 때가 많음
  • 수동으로 데이터를 적재하거나 스키마 변경 등의 관리작업이 실패 할 수 있음
    • 부모 테이블과 자식 테이블의 관계를 명확히 파악해서 순서대로 작업한다면 문제없이 실행할 수 있지만 외래 키가 복잡하게 얽힌 경우에는 그렇게 간단하지 않음
    • 서비스에 문제가 있어서 긴급하게 뭔가 조치를 해야하는데 이런 문제가 발생하면 더 조급해질 수도 있음

4.2.3 MVCC(Multi Version Concurrency Control)

일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며, MVCC의 가장 큰 목적은 잠금을 사용하지 않는 읽관된 읽기를 제공하는 데 있다. InnoDB는 언두 로그(Undo log)를 이용해 이 기능을 구현한다.

멀티 버전 : 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미

예시

격리 수준이 READ_COMMITED인 MySQL 서버에서 InnoDB 스토리지 엔진을 사용하는 사용하는 테이블의 데이터 변경을 처리하는 절차

mysql> CREATE TABLE member (
	m_id INT NOT NULL,
	m_name VARCHAR(20) NOT NULL,
	m_area VARCHAR(100) NOT NULL,
	PRIMART KEY (m_id),
	INDEX ix_area (m_area)
);

mysql> INSERT INTO member (m_id, m_name, m_area) VALUES (12, '홍길동', '서울');
mysql> COMMIT;

INSERT 문이 실행되면 데이터베이스의 상태는 그림 4.10과 같은 상태로 바뀔 것이다.

그림 4.11은 MEMBER 테이블에 UPDATE 문장이 실행될 때의 처리 절차를 그림으로 보여준다.

mysql> UPDATE member SET m_area='경기' WHERE m_id=12;

UPDATE 문장이 실행되면 커밋 실행 여부와 관계 없이 InnoDB의 버퍼 풀은 새로운 값인 경기로 업데이트된다.
그리고 디스크의 데이터 파일에는 체크포인트나 InnoDB의 Write 스레드에 의해 새로운 값으로 업데이트돼 있을 수도 있고 아닐 수도 있다. (InnoDB가 ACID를 보장하기 때문에 일반적으로는 InnoDB의 버퍼 풀과 데이터 파일은 동일한 상태라고 가정해도 무방하다.)

  • 아직 COMMIT이나 ROLEBACK 되지 않은 상태에서 다른 사용자가 다음 같은 쿼리로 작업중인 레코드를 조회하면 어디에 있는 데이터를 조회할까?
SELECT * FROM member WHERE m_id=12;

-> 이 질문의 답은 MySQL 서버의 시스템 변수(transaction_isolation)에 설정된 격리 수준(Isolation level)에 따라 다르다는 것이다.

격리 수준이 READ_UNCOMMITTED인 경우에는 InnoDB 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환한다. 즉, 데이터가 커밋됐든 아니든 변경된 상태의 데이터를 반환한다.

그렇지 않고 READ_COMMITED나 그 이상의 격리수준 인 경우에는 아직 커밋되지 않았기 때문에 InnoDB 버퍼 풀이나 데이터 파일에 있는 내용 대신 변경되기 이전의 내용을 보관하고 있는 언두 영역의 데이터를 반환한다.

이러한 과정을 DBMS에서는 MVCC라고 표현한다.
즉, 하나의 레코드(회원 번호가 12인 레코드)에 대해 2개의 버전이 유지되고, 필요에 따라 어느 데이터가 보여지는지 여러 가지 상황에 따라 달라지는 구조다.

여기서는 한 개의 데이터만 가지고 설명했지만 관리해야 하는 예전 버전의 데이터는 무한히 많아질 수 있다.

이 상태에서 COMMIT 명령을 실행하면 InnoDB는 더 이상의 변경 작업 없이 지금으 ㅣ상태를 영구적인 데이터로 만들어 버린다. 하지만 롤백을 실행하면 InnoDB는 언두 영역에 있는 백업된 데이터를 InnoDB 버퍼 풀로 다시 복구하고, 언두 여역의 내용을 삭제해 버린다.

커밋이 된다고 언두 여역의 백업 데이터가 항상 바로 삭제되는 것은 아니며 필요로 하는 트랜잭션이 더는 없을 때 비로소 삭제된다.

4.2.4 잠금 없는 일관된 읽기(Non-Locking Consistent Read)

InnoDB 스토리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다.
때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고 읽기 작업이 가능하다.

격리 수준이 SERIALIZABLE 보다 낮다면 INSERT와 연결되지 않은 순수한 SELECT 작업은 다른 트랜잭션의 변경 작업과 관계 2없이 항상 잠금을 대기하지 않고 바로 실행된다.

따라서 특정 사용자가 레코드를 변경하고 아직 커밋을 수행하지 않았더라도 이 변경 트랜잭션이 다른 사용자의 SELECT 작업을 방해하지 않는다.

이를 잠금 없는 일관된 읽기라고 표현하며, InnoDB에서는 변경되기 전의 데이터를 읽기 위해 언두 로그를 사용한다.

4.2.13 InnoDB와 MyISAM, MEMORY 스토리지 엔진 비교

MyISAM 엔진

  • MySQL 5.5부터 InnoDB 스토리지 엔진이 기본 스토리지 엔진으로 채택됨
  • MySQL 서버의 시스템 테이블(사용자 인증 관련된 정보와 복제 관련된 정보가 저장된 mysql의 DB 테이블)은 여전히 MyISAM 테이블을 사용
  • 또한 전문 검색이나 공간 좌표 검색 기능은 MyISAM 테이블에서만 지원됨
  • 그러나 8.0으로 업그레이드 되면서 MySQL 서버의 모든 기능을 InnoDB 스토리지 엔진만으로 구현할 수 있게 됨

MEMORY 엔진

  • 모든 처리를 메모리에서만 수행하니 하나의 스레드에서만 데이터를 읽고 쓴다면 InnoDB보다 빠를 수 있다.
  • 하지만 OLTP에서는 동시 처리 성능이 매우 중요한데, 몇십 또는 몇백 개의 클라이언트에서 쿼리 요청이 실행되는 경우라면 테이블 수준의 잠금으로 인해 제대로 된 성능을 내지 못할 것이다.

4.3 MyISAM 스토리지 엔진 아키텍처

4.3.1 키 캐시

InnoDB의 버퍼 풀과 비슷한 역할을 하는 것이 MyISAM의 키 캐시(Key cache, 키 버퍼라고도 불림)다. 하지만 이름 그대로 MyISAM 키 캐시는 인덱스만을 대상으로 작동하며, 또한 인덱스의 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할을 한다.

키 캐시가 얼마나 효율적으로 작동하는지는 다음 수식으로 간단히 확인할 수 있다.
키 캐시 히트율(Hit rate) = 100 - (key_reads / Key_read_request * 100)

  • Key_reads : 인덱스를 디스크에서 읽어 들인 횟수를 저장하는 상태 변수
  • Key_read_requests : 키 캐시로부터 인덱스를 읽은 횟수를 저장하는 상태 변수

매뉴얼에서는 일반적으로 키 캐시를 이용한 쿼리의 비율(히트율, Hit rate)을 99% 이상으로 유지하라고 권장한다. 히트율이 99% 미만이라면 키 캐시를 조금 더 크게 설정하는 것이 좋다.

4.3.2 운영 체제의 캐시 및 버퍼

MyISAM 스토리지 엔진은 테이블의 인덱스를 키 캐시를 이용해 디스크를 검색하지 않고도 빠르게 검색할수 있지만, MyISAM 테이블의 데이터에 대해서는 디스크로부터의 I/O를 해결해 줄 만한 어떠한 캐시나 버퍼링 기능은 가지고 있지 않다.
그래서 MyISAM 테이블의 데이터 읽기나 쓰기 작업은 항상 OS의 디스크 읽기 또는 쓰기 작업으로 요청될 수밖에 없다.

물론 대부분의 OS에는 디스크로부터 읽고 쓰는 파일에 대한 캐시나 버퍼링 메커니즘을 탑재하고 있기 때문에 MySQL 서버가 요청하는 디스크 읽기 작업을 위해 매번 디스크의 파일을 읽지는 않는다.

다른 애플리케이션에서 메모리를 모두 사용해 버린다면 운영체제가 캐시 용도로 사용할 수 있는 메모리 공간이 없어진다.
따라서 MyISAM이 주로 사용되는 MySQL에서 일반적으로 키 캐시는 최대 물리 메모리의 40% 이상을 넘지 않게 설정하고, 나머지 메모리 공간은 운영체제가 자체적은 파일 시스템을 위한 캐시 공간을 마련할 수 있게 해주는 것이 좋다.

4.3.3 데이터 파일과 프라이머리 키(인덱스) 구조

MyISAM 테이블은 프라이머리 키에 의한 클러스터링 없이 데이터 파일이 힙(Heap) 공간처럼 활용된다.
즉, MyISAM 테이블에 레코드는 프라이머리 키 값과 무관하게 INSERT 되는 순서대로 데이터 파일에 저장된다.

그리고 MyISAM 테이블에 저장되는 레코드는 모두 ROWID라는 물리적인 주솟값을 가지는데, 프라이머리 키와 세컨더리 인덱스는 모두 데이터 파일에 저장된 레코드의 ROWID 값을 포인터로 가진다.

고정 길이 ROWID

  • MyISAM 테이블을 생헝할 때 MAX_ROWS 옵션을 명시하면 MySQL 서버는 최대로 가질 수 있는 레코드가 한정된 테이블을 생성한다.
  • 이렇게 테이블이 가질 수 있는 레코드의 개수가 한정되면 ROWID 값으로 4바이트 정수를 사용한다.
  • 이때 레코드가 INSERT된 순번이 ROWID로 사용된다.

가변 길이 ROWID

  • MyISAM 테이블을 생헝할 때 MAX_ROWS 옵션을 명시하지 않으면 ROWID는 최대 myiasm_data_pointer_size 시스템 변수에 설정된 바이트 수만큼의 공간을 사용할 수 있다.
  • myiasm_data_pointer_size의 기본 값은 7이므로 ROWID는 2 ~ 7바이트까지 가변적인 ROWID를 갖게 되는데, 첫 번째 바이트는 ROWID의 길이를 저장하는 용도로 사용하고 나머지 공간은 실제 ROWID를 저장하는데 사용한다.
  • MyISAM 테이블이 가변적인 ROWID를 가지면 데이터 파일에서 레코드의 위치(offset)가 ROWID로 사용된다.
profile
솔트커피
이전 포스트

외래 키(Foriegn Key)

다음 포스트

Real MySQL 05. 트랜잭션과 잠금

0개의 댓글