[MySQL] MySQL 벼락치기(1) - 아키텍처

오랜만에 DB 포스팅이다. 

이번 포스팅은 사내에서 MySQL 관련 내용 발표를 위해 Real MySQL(http://wikibook.co.kr/real-mysql/) 서적을 기반으로 학습하고 이해한 내용을 정리하는 포스팅이다. 포스팅에서는 주로 InnoDB 스토리지 엔진을 기준으로 설명할 예정이다. 

MySQL 역시 내용이 많기 때문에 시리즈로 나눠서 정리할 예정이다. 

MySQL 의 전체 구조

MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 구분해서 볼 수 있다. 

MySQL 엔진

MySQL 엔진은 클라이언트로부터 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리기 그리고 쿼리의 최적화를 위한 옵티마이저가 중심을 이루며  DBMS의 두뇌에 해당하는 처리를 수행한다.

스토리지 엔진

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

아래의 예제와 같이 스토리지 엔진을 지정하면 이후 테이블의 모든 작업은 해당 스토리지 엔진이 처리한다. 

mysql> CREATE TABLE test_table( column1 INT, column2 INT) ENGINE=InnoDB

test_table 은 InnoDB 스토리지 엔진을 사용하도록 정의했다. 이제 test_table 에 INSERT, UPDATE, DELETE, SELECT 등의 작업이 발생하면 InnoDB 스토리지 엔진이 처리를 담당한다. 

MySQL 서버에서 스토리지 엔진은 아래와 같은 명령어로 확인할 수 있고 Support 에 YES나 DEFAULT 인경우에는 사용 할 수 있다.  

mysql> SHOW ENGINES;

핸들러API

MySQL 엔진이 스토리지 엔진에게 쓰기 또는 읽기를 요청할때 핸들러API를 사용한다. 

핸들러 API를 통해 얼마나 많은 데이터 작업이 있었는지는 아래와 같은 명령어로 확인이 가능하다. 

mysql> SHOW GLOBAL STATUS LIKE 'Handler%';

MySQL 스레딩 구조

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

포그라운드 스레드(클라이언트 스레드)

포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며 클라이언트 사용자가 요청하는 쿼리 문장을 처리하는것이 임무이다. 사용자가 커넥션을 종료하면 해당 커넥션은 스레드 풀로 되돌아간다. 이때 스레드 풀에 일정 개수이상의 대기중인 스레드가 있으면 스레드 풀에 넣지 않고 스레드를 종료시켜서 일정 개수의 스레드만 스레드 풀에 존재하게 된다. 

이렇게 스레드의 개수를 일정하게 유지하게 만들어주는 파라미터가 thread_cache_size 이고 아래와 같이 확인할 수 있다.

mysql> SHOW VARIABLES LIKE 'thread_cache_size';

백그라운드 스레드

백그라운드 스레드는 인서트 버퍼를 병합하는 스레드, 로그를 디스크로 기록하는 스레드 InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드, 데이터를 버퍼로 읽어들이는 스레드, 잠금이나 데드락을 모니터링 하는 스레드 등이 있고 마지막으로 모든 스레드를 총괄하는 메인 스레드가 있다. 

그중에서도 가장 중요한것은 로그 스레드와 버퍼의 데이터를 디스크로 내려쓰는 작업을 하는 쓰기 스레드(Write thread)다. 

읽기/쓰기 스레드의 개수를 지정하는 파라미터는 innodb_read_io_threads / innodb_write_io_threads 이다.  InnoDB 에서는 데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리되기 때문에 읽기 스레드는 많이 설정할 필요가 없지만 쓰기 스레드는 아주 많은 작업을 백그라운드로 처리하기때문에 일반적인 내장 디스크를 사용할때는 2~4개 정도, DAS나 SAN 과 같은 스토리지를 사용할때는 4개 이상으로 충분히 설정해야 한다. 

mysql> SHOW VARIABLES LIKE 'innodb_%_io_threads';

InnoDB 에서는 읽기 작업은 절대로 지연될 수 없지만 쓰기 작업은 쓰기 스레드에게 작업을 맡김으로써 지연되어 처리될 수 있기 때문에 INSERT 와 UPDATE 그리고 DELETE 쿼리로 데이터가 변경되는 경우 데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도된다. 

InnoDB 스토리지 엔진 아키텍처

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

InnoDB 스토리지 엔진의 특성

1. 프라이머리 키에 의한 클러스터링
• InnoDB의 모든 테이블은 기본적으로 PK 기준으로 클러스터링 되어 저장된다. 즉, PK 값의 순서대로 디스크에 저장되고 이로 인해 PK에 의한 레인지스캔은 상당히 빨리 처리될 수 있다. 
2. 잠금이 필요없는 일관된 읽기
• InnoDB 스토리지 엔진은 MVCC 라는 기술을 이용해 락을 걸지 않고 읽기 작업을 수행한다. 락을 걸지 않기 때문에 다른 트랜잭션이 가지고 있는 락을 기다리지 않는다. 
3. 외래키 지원
4. 자동데드락 감지
5. 자동화된 장애 복구
6. 오라클의 아키텍처 적용
• InnoDB 스토리지 엔진은 MVCC 기능이 제공된다는 것과 언두 데이터가 시스템 테이블 스페이스에 관리된다는것등 오라클 아키텍처와 유사한 부분이 많다. 

InnoDB 버퍼 풀

백그라운드 작업의 기반이 되는 메모리 공간이며 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다. 

InnoDB 버퍼 풀은 아직 디스크에 기록되지 않은 변경된 데이터를 가지고 있는데 이를 더티페이지라고 한다. 이러한 더티페이지는 InnoDB 에서 주기적으로 또는 어떤 조건이 되면 체크포인트 이벤트가 발생하는데 이때 Write 스레드가 필요한 만큼의 더티페이지만 디스크로 기록한다. 체크포인트가 발생한다고 해서 버퍼 풀의 모든 더티페이지를 디스크로 기록하는것은 아니다.  

InnoDB의 버퍼 풀 크기는 innodb_buffer_pool_size 로 설정하는데 일반적으로 장착된 물리 메모리의 50~80% 수준에서 설정한다.

언두(Undo) 로그

언두 영역은 UPDATE 문장이나 DELETE 와 같은 문장으로 데이터를 변경했을때 변경되기 전의 데이터를 보관하는 곳이다. 

mysql> UPDATE member SET name='홍길동' WHERE member_id='1';

위 문장이 실행되면 트랜잭션을 커밋하지 않아도 실제 데이터 파일 내용은 '홍길동'으로 변경된다. 그리고 변경되기 전의 값이 김철수 였다면 언두 영역에는 김철수 라는 값이 백업되는 것이다. 이 상태에서 만약 사용자가 커밋하게 되면 현재 상태가 그대로 유지되고 롤백하게 되면 언두 영역의 백업된 데이터를 다시 데이터 파일로 복구한다. 

인서트(Insert) 버퍼

INSERT 되거나 UPDATE 될 때는 데이터 파일을 변경하는 작업뿐 아니라 해당 테이블에 포함된 인덱스를 업데이트 하는 작업도 필요하다. 그런데 인덱스를 업데이트 하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱스가 많다면 이 작업은 상당히 많은 자원을 소모하게 된다. 

InnoDB 는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만 그렇지 않고 디스크로부터 읽어와서 업데이트 해야한다면 이를 즉시 실행하지 않고 임시공간에 저장하고 사용자에게 결과를 반환하는 형태로 성능을 향상시킨다. 이때 사용하는 임시 메모리 공간이 인서트 버퍼이다. 

리두(Redo)로그 및 로그 버퍼

쿼리문장으로 데이터를 변경하고 커밋하면 DBMS는 변경된 데이터의 내용을 데이터 파일로 기록한다. 이러한 데이터 변경을 순차적으로 디스크에 기록하게 하기위한 로그파일을 가지고 있으며 이를 리두 로그라고 한다. 그리고 리두로그의 버퍼링에 사용되는 공간이 로그버퍼 이다. 

MVCC(Multi Version Concurrency Control)

InnoDB 는 잠금을 사용하지 않는 일관된 읽기를 제공하기위해 MVCC를 사용하고 MVCC 는 앞에서 설명한 언두 로그를 이용해 이 기능을 구현한다. 

언두 로그에 데이터가 있는상태에서 다른 사용자가 쿼리를 통해 데이터를 조회하는 어떤 데이터를 반환해야 할까?

이는 격리수준 설정에 따라 다르다. 격리 수준이 READ_UNCOMMITTED 인경우 커밋이 됐든 아니든 변경된 상태의 데이터를 반환한다. 그렇지 않고 READ_COMMITTED 나 그 이상의 격리수준(REPEATABLE_READ, SERIALIZABLE) 인 경우에는 아직 커밋이 되지 않았기 때문에 언두 영역의 데이터를 반환한다. 이러한 과정을 MVCC 라고 표현한다. 

즉 하나의 레코드에 대해서 2개의 버전이 유지되고 필요에 따라 어느 데이터가 보여지는지 여러 가지 상황에 따라 달라지는 구조이다. 

MySQL 로그파일

MySQL은 다른 상용 DBMS와 비교하면 개발자를 위한 진단 도구가 상당히 부족한 편이기 때문에 로그파일을 이용해서 부하를 일으키는 원인을 찾아야 할때가 있다. 우리는 이중에서 슬로우 쿼리 로그파일을 읽는 방법을 알아보자.

슬로우 쿼리 로그 파일

슬로우 쿼리 로그파일에는 long_query_time(초단위) 에 설정된 이상의 시간이 소요된 쿼리가 모두 기록된다. 

슬로우쿼리 로그파일에 기록되는 쿼리는 일단 정상적으로 실행이 완료됐고 실행하는데 걸린 시간이 long_query_time 에 정의된 초 보다 많이 걸린 쿼리이다. 

mysql> SHOW VARIABLES LIKE 'long_query_time';

실제 슬로우 쿼리 로그 파일에는 다음과 같은 형태로 내용이 출력된다. 

# Time: 110202 12:13:14
# User@Host: root[root] @ localhost[]
# Query_time: 15.407663 Lock_time: 0.000197 Row_sent: 0, Rows:examined: 5 
update_tab set fd=1000 where fd=10

• Time : 쿼리가 종료된 시점을 의미한다 그래서 쿼리가 언제 시작됐는지 확인하려면 Time 항목에 나온 시간에서 Query_time 만큼 빼야한다.
• User@Host : 쿼리를 실행한 사용자의 계정이다.
• Query_time : 쿼리가 실행되는데 걸린 전체 시간을 의미한다.  Lock_time 에 표기된 시간은 실제 쿼리가 실행되는데 필요한 잠금체크와 같은 코드 실행부분의 시간까지 모두 포함되기 때문에 너무 작은 값이면 무시해도 무방하다. 
• Rows_examined : 이 쿼리가 처리되기 위해 몇 건의 레코드에 접근했는지를 의미하며 Rows_sent 는 실제 몇 건의 처리 결과를 클라이언트로 보냈는지를 의미한다. 일반적으로 Rows_examined 레코드 건수는 높지만 Rows_sent 에 표시된 레코드 건수가 상당히 적다면 쿼리를 튜닝해 볼 가치가 있다는 것이다. ( GROUP BY나 COUNT(), MIN(), MAX(), AVG() 등과 같은 집합 함수가 아닌 쿼리인 경우만 해당 )

오늘은 여기까지~

누군가에게 도움이 되었길 바라면서 오늘의 포스팅 끝~