[MySQL] MySQL 아키텍처

https://12bme.tistory.com/72 사이트 참고 후 작성하였습니다.

[MySQL] MySQL 아키텍처

MySQL 아키텍처

  # MySQL의 전체 구조

     - MySQL서버는 MySQL 엔진과 스토리지 엔진으로 구분됨. (MySQL의 쿼리 파서나 옵티마이저 등과 같은 기능을 스토리지 엔진과 구분하기 위해)

     - MySQL 엔진

          + 커넥션 핸들러 (Connection Pool)

          + SQL 파서 및 전처리기 

          + 옵티마이저 (Optimizer)

          + 성능 향상을 위한 캐시 및 버퍼

               > MyISAM의 키 캐시

               > InnoDB의 버퍼 풀과 같은 보조 저장소

     - 스토리지 엔진

          + 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분을 담당.

     - 핸들러 API

          + MySQL 엔진이 데이터를 쓰거나 읽어야 할 때, 각 스토리지 엔진에게 쓰기 또는 읽기 요청이 일어나는데 이러한 요청을 핸들러 요청이라고 함.

             여기서 사용되는 API를 핸들러 API라고 하고 InnoDB 스토리지 엔진 또한 이 핸들러 API를 이용해 MySQL 엔진과 데이터를 주고 받음.

     

  # MySQL 스토리지 엔진 비교

 

스토리지 엔진버전트랜잭션LOCK 세분성강점약점

스토리지 엔진	버전	트랜잭션	LOCK 세분성	강점	약점
MyISAM	모든버전	지원 안함	동시 삽입 가능 테이블 락	SELECT, INSERT, 대량적재	읽기/쓰기 혼합 작업
MyISAM Merge	모든 버전	지원 안함	동시 삽입 가능 테이블 락	분할 보관, 데이터웨어하우징	전역적 테이블 검색
Memory(HEAP)	모든 버전	지원 안함	테이블 락	중간 계산, 통계 참조 데이터	대량 데이터셋, 지속적인 스토리지
InnoDB	모든 버전	지원	MVCC의 ROW락	트랜잭션 처리
Falcon	6.0 이상	지원	MVCC의 ROW 락	트랜잭션 처리
Archive	4.1 이상	지원 안함	ROW 락	로깅, 집계 분석	임의 접근(인덱싱), 갱신, 삭제
CSV	4.1 이상	지원 안함	테이블 락	로깅, 외부 데이터 대량 적재	임의 접근(인덱싱)
Blackhole	4.1 이상	지원 안함	락 지원 안함	로깅, 복제 아카이브	특수 목적(로깅) 이외의 사용
Federated	5.0 이상	지원 안함	락 지원 안함	배포된 데이터 소스 관리	특수 목적(원격 데이터 조회) 이외의 사용
NDB 클러스터	5.0 이상	지원	ROW 락	고도의 가용성	일반적인 사용 어려움
PBXT	5.0 이상	지원	MVCC의 ROW 락	트랜잭션 처리, 로깅	클러스터 인덱스 지원 안함
SolidDB	5.0 이상	지원	MVCC의 ROW 락	트랜잭션 처리
Maria	6.X 이상	지원	MVCC의 ROW 락	MyISAM 엔진 대체

     ※ MySQL 스토리지 엔진들 중 오라클과 유사한 엔진은 InnoDB.

 

  # MySQL 스레딩 구조

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

          + 최소 MySQL 서버에 접속된 클라이언트의 수만큼 존재.

          + 주로 클라이언트 사용자가 요청하는 쿼리 문장을 처리하는 것이 임무.

          + 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면, 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시(Thread Pool)로 되돌아감.

          + thread_cache_size = 스레드 갯수 설정 파라미터

          + MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리.

          + InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리함.

     - 백그라운드 스레드

          ※ MyISAM의 경우 해당 사항 없음. InnoDB는 여러가지 작업이 백그라운드로 처리

          + InnoDB의 백그라운드 스레드

               > 인서트 버퍼(Insert Buffer) 를 병합하는 스레드

               > 로그를 디스크로 기록하는 스레드 (Log Thread)

               > InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드 (Write Thread)

               > 데이터를 버퍼로 읽어들이는 스레드 (Read Thread)

               > 기타 여러가지 락(LOCK)을 모니터링 하는 스레드

               > 위 스레드를 총괄하는 메인 스레드

               ※ 로그 스레드(Log Thread)와 쓰기 스레드(Write Thread)가 중요!!

                   쓰기 스레드는 윈도우계열 MySQL 5.0 버전, 리눅스/유닉스 계열 MySQL 5.1 버전부터 갯수가 1개이상으로 지정 할 수 있게 됨.

                                                                                                    (쓰기 스레드 개수 파라미터 = innodb_write_io_threads, 읽기 스레드 개수 파라미터 = innodb_read_io_threads)

                   쓰기 스레드는 보통 많은 작업을 처리하기 때문에 일반적인 내장 디스크 사용 시 2~4로 설정. DAS나 SAN 같은 스토리지 사용 시 4개 이상으로 설정하는 것을 권장.

               ※ 일반적은 상용 DBMS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재되어있으며 InnoDB 또한 이러한 방식으로 처리.

                   MyISAM은 그렇지 않고 사용자 스레드가 쓰기 작업까지 함께 처리하도록 설계되어있음. 

                   이러한 이유로 InnoDB에서는 DML로 데이터가 변경되는 경우, 데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 됨.

                   MyISAM에서는 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없음.

 

  # MySQL 메모리 할당 및 사용 구조

     - 글로벌 메모리 영역

          + 클라이언트 스레드 수와 무관하게 1개의 메모리 공간만 할당됨. (경우에 따라 2개이상으로 할당하기도 함)

          + 모든 스레드에 의해 공유됨.

     - 로컬 메모리 영역 (=세션 메모리 영역)

          + 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역.

          + 커넥션 버퍼, 정렬 버퍼등이 존재.

          + 클라이언트 스레드 별로 독립적으로 할당. 공유되지 않음.

          + 필요하지 않은 경우, 공간 할당 조차 하지 않는 경우도 있다고 함.

          ※ 메모리 영역에 할당되는 버퍼들

               > 커넥션 버퍼, 결과 버퍼 : 계속 할당된 상태로 남아있는 공간

               > 소트 버퍼, 조인 버퍼 : 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간