[MySQL] MySQL 아키텍처

MySQL의 전체 구조

MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 구분해서 볼 수 있습니다. 위 그림에서는 MySQL의 쿼리 파서나 옵티마이저 등과 같은 기능을 스토리지 엔진과 구분하고자 "MySQL 엔진"과 "스토리지 엔진"으로 구분했습니다. 그리고 "MySQL 엔진"과 "스토리지 엔진"을 모두 합쳐서 MySQL 또는 MySQL 서버라고 표현합니다.

위는 오라클 전체 구조 입니다. MySQL, Oracle 구조에는 상당한 차이가 있습니다. 실제 기능상에는 큰 차이가 없음에도 이런 차이가 발생하는 이유는 MySQL 구조내 "Pluggable Storage Engine" 때문입니다. MySQL은 내부적으로 성격이 다른 아키텍처 구조를 여러개 포함하고 있습니다. 오라클의 경우 테이블의 데이터가 처리가 단일 엔진으로 동일하게 처리되는 것에 비해 MySQL은 테이블별로 처리할 엔진을 선택할 수 있습니다. MySQL의 Pluggable Storage Engine 들은 각각 저마다의 데이터를 처리하는 장단점을 지니고 있으며, 사용자가 이를 테이블 별로 선택할 수 있습니다.

위 내용을 담은 블로그 저자는 오픈소스에 대한 다양한 개발자들의 시도를 수용하는 정책들 와중에 스토리지 엔진들이 서로 경쟁하며 진화하는 과정에서 이렇게 되었다고 추측하고 있습니다. 실질적으로 지금도 MySQL 기존 엔진들은 계속 진화하고 있으며, 배포만 안되었을뿐 개발자들이 가지고 있는 우리가 모르는 엔진도 다수 존재하고 있을지도 모릅니다.

MySQL 엔진

MySQL 엔진은 클라이언트로부터 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리기, 그리고 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룹니다. 그리고 성능 향상을 위해 MyISAM의 키 캐시나 InnoDB의 버퍼 풀과 같은 보조 저장소 기능이 포함돼 있습니다. 또한, MySQL은 표준 SQL(ANSI SQL-92) 문법을 지원하기 때문에 표준 문법에 따라 작성된 쿼리는 타 DBMS와 호환되어 실행될 수 있습니다. 

스토리지 엔진

MySQL 엔진은 요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS의 두뇌에 해당하는 처리를 수행하고, 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분은 스토리지 엔진이 전담합니다.

MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있습니다. 테이블이 사용할 스토리지 엔진을 지정하면 이후 해당 테이블의 모든 읽기 작업이나 변경 작업은 정의된 스토리지 엔진이 처리합니다.

테이블에서 사용할 스토리지 엔진 지정방법입니다.

mysql> CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;

테이블별 엔진을 확인하는 방법입니다.

mysql> SHOW TABLE STATUS;

핸들러 API

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

핸들러 API 내 데이터(레코드) 작업이 얼마나 수행되었는지 확인하는 방법입니다.

mysql> SHOW GLOBAL STATUS LIKE 'Handler%';

MySQL 스토리지 엔진 비교

내용을 훑어보면 대부분의 엔진들이 오라클 엔진의 기능을 100으로 봤을때 60,70 혹은 100으로 기능을 줄이거나 유사하게 구현하고 있습니다. 기능을 줄인 엔진은 그만큼 락 경합이나 데이터 처리시 부하를 줄이면서 대량 인서트나 메모리내의 조회로 성능을 높이고 있는 경향을 보이고 있습니다.

스토리지 엔진	버전	트랜잭션	LOCK 세분성	강점	약점
MyISAM	모든버전	지원 안함	동시 삽입 가능 테이블 락	SELECT, INSERT, 대량적재	읽기/쓰기 혼합 작업
MyISAM Merge	모든 버전	지원 안함	동시 삽입 가능 테이블 락	분할 보관, 데이터웨어하우징	전역적 테이블 검색
Memory(HEAP)	모든 버전	지원 안함	테이블 락	중간 계산, 통계 참조 데이터	대량 데이터셋, 지속적인 스토리지
InnoDB	모든 버전	지원	MVCC의 ROW락	트랜잭션 처리
Falcon	6.0 이상	지원	MVCC의 ROW 락	트랜잭션 처리
Archive	4.1 이상	지원 안함	ROW 락	로깅, 집계 분석	임의 접근(인덱싱), 갱신, 삭제
CSV	4.1 이상	지원 안함	테이블 락	로깅, 외부 데이터 대량 적재	임의 접근(인덱싱)
Bloackhole	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 시스템 구축시, 테이블의 성격에 따라 전략적인 엔진선택을 성능향상을 이끌어낼 수 있음을 의미합니다.

하지만, 스토리지 엔진을 잘못 사용할경우 트랜잭션이 의도하지 않는 방향으로 처리된다거나 부작용이 있을 수 있음은 염두 하여야 합니다. MySQL 엔진들 중 가장 보편적으로 쓰이면서 오라클과 유사한 엔진은 InnoDB 엔진입니다. 스토리지 엔진에 대한 상세한 설명은 추후 포스팅하도록 하겠습니다.

오라클 엔진과 비교 내용참고(http://www.dator.co.kr/dewpoint/236276)

MySQL 스레딩 구조

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

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

포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리하는 것이 임무입니다. 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면, 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시(Thread pool)로 되돌아갑니다. 이때 이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면 스레드 캐시에 넣지 않고 스레드를 종료시켜 일정 개수의 스레드만 스레드 캐시에 존재하게 합니다. 이렇게 스레드의 개수를 일정하게 유지하게 만들어주는 파라미터가 thread_cache_size입니다.

포그라운드 스레드는 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오며, 버퍼나 캐시에 없는 경우에는 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리합니다. MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리하지만(MyISAM도 지연된 쓰기가 있지만 방식은 아님), InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리합니다.

백그라운드 스레드

MyISAM의 경우에는 별로 해당 사항이 없는 부분이지만 InnoDB는 여러 가지 작업이 백그라운드로 처리됩니다. 대표적으로 1. 인서트 버퍼(Insert Buffer)를 병합하는 스레드, 2. 로그를 디스크로 기록하는 스레드, 3. InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드, 4. 데이터를 버퍼로 읽어들이는 스레드, 그리고 기타 여러가지 잠금이나 데드락을 5. 모니터링하는 스레드가 있습니다. 또, 이러한 모든 스레드를 총괄하는 메인 스레드도 존재합니다.

위 스레드들은 모두 중요한 역할을 하지만 그중에서도 가장 중요한 것은 로그 스레드(Log thread)와 버퍼의 데이터를 디스크로 내려 쓰는 작업을 처리하는 쓰기 스레드(Write thread)입니다. 쓰기 스레드는 윈도우계열 MySQL 5.0부터 1개 이상을 설정할 수 있었지만 리눅스/유닉스 계열 MySQL에서는 5.1 버전부터 쓰기 스레드의 개수를 1개 이상으로 지정할 수 있게 되었습니다. 이 쓰기 스레드의 개수를 지정하는 파라미터는 innodb_write_io_threads이며, 읽기 스레드(Read thread)의 개수를 지정하는 파라미터는 innodb_read_io_threads입니다. InnoDB에서도 데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리되기 때문에 읽기 스레드를 많이 설정할 필요는 없습니다.

쓰기 스레드는 아주 많은 작업을 백그라운드로 처리하기 때문에 일반적인 내장 디스크를 사용할 때는 2~4 정도, DAS나 SAN과 같은 스토리지를 사용할 때는 4개 이상으로 충분히 설정해 해당 스토리지 장비가 충분히 활용될 수 있게 하는 것이 좋습니다.

SQL 처리 도중 데이터의 쓰기 작업은 지연(버퍼링)되어 처리될 수 있지만 데이터의 읽기 작업은 절대 지연될 수 없습니다. 일반적은 상용 DBMS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재돼 있으며 InnoDB 또한 이러한 방식으로 처리하게 됩니다. 하지만 MyISAM은 그렇지 않고 사용자 스레드가 쓰기 작업까지 함께 처리하도록 설계돼 있습니다. 이러한 이유로 InnoDB에서는 INSERT와 UPDATE 그리고 DELETE 쿼리로 데이터가 변경되는 경우, 데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 됩니다. 하지만 MyISAM에서 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없습니다.

MySQL에서 사용자 스레드와 포그라운드 스레드는 똑같은 의미로 사용됩니다. 
클라이언트가 MySQL 서버에 접속하게 되면 MySQL 서버는 그 클라이언트의 요청을 처리해 줄 스레드를 생성해 그 클라이언트에게 할당해 줍니다. 이 스레드는 DBMS의 앞단에서 사용자(클라이언트)와 통신하기 때문에 포그라운드 스레드라고 하며, 또한 사용자가 요청한 작업을 처리하기 때문에 사용자 스레드라고도 부릅니다.

MySQL 메모리 할당 및 사용 구조

MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분됩니다. 글로벌 메모리 영역의 모든 메모리 공간은 MySQL 서버가 시작되면서 무조건 운영체제로부터 할당됩니다. 글로벌 메모리 영역과 로컬 메모리 영역의 차이는 MySQL 서버 내에 존재하는 많은 스레드가 공유해서 사용하는 공간인지 아닌지에 따라 구분됩니다.

1) 글로벌 메모리 영역

일반적으로 클라이언트 스레드의 수와 무관하게 일반적으로 하나의 메모리 공간만 할당 됩니다. 필요에 따라 2개 이상의 메모리 공간을 할당하기도 하지만, 클라이언트 스레드 수와는 무관하며, 생성된 글로벌 영역이 N개라 하더라도 모든 스레드에 의해 공유됩니다.

2) 로컬 메모리 영역

세션 메모리 영역이라고도 표현하며, MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역입니다.대표적으로 커넥션 버퍼, 정렬 버퍼등이 존재합니다. 클라이언트와 MySQL 서버와의 커넥션을 세션이라고 하기 때문에 로컬 메모리 영역을 세션 메모리 영역이라고도 표현합니다.

로컬 메모리는 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않습니다. 일반적으로 글로벌 메모리 영역의 크기는 주의해서 설정하지만 소트 버퍼와 같은 로컬 메모리 영역은 크게 신경 쓰지 않고 설정하곤 합니다. 로컬 메모리 공간은 또한 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우에는 MySQL이 메모리 공간을 할당조차도 하지 않을 수도 있습니다.

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

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

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

MySQL은 플러그인이라는 독특한 구조를 가지고 있습니다. 스토리지 엔진 뿐만 아니라 MySQL5.1부터는 전문 검색 엔진을 위한 검색어 파서(인덱싱할 키워드를 분리해내는 작업)도 플러그인 형태로 개발해서 사용할 수 있습니다. MySQL은 이미 기본적으로 많은 스토리지 엔진을 가지고 있습니다. 개발 전문회사 또는 개발자가 직접 스토리지 엔진을 제작하는 것도 가능합니다.

MySQL에서 쿼리가 실행될때, 거의 대부분의 작업이 MySQL 엔진에서 처리되고, 마지막 " 데이터 읽기/쓰기" 작업만 스토리지 엔진에 처리됩니다. 이말은 즉슨, 새로운 스토리지 엔진을 만들더라도 DBMS의 전체 기능이 아닌 일부분의 기능만 수행하는 엔진을 작성하게 된다는 말입니다.

MySQL 엔진이 스토리지 엔진을 조정하기 위해 핸들러라는 것을 사용하게됩니다. MySQL에서 최소한 MySQL 엔진이 각 스토리지 엔진에게 데이터를 읽어오거나 저장하도록 명령하려면 핸들러를 꼭 통해야 한다는 사실을 알아둘 필요가 있습니다. MySQL 서버 상태 변수 중 "Handler_"로 시작하는 상태변수들은 "MySQL 엔진이 각 스토리지 엔진에게 보낸 명령의 횟수를 의미하는 변수"라고 이해하면 됩니다. MySQL에서 MyISAM이나 InnoDB와 같이 다른 스토리지 엔진을 사용하는 테이블에 대해 쿼리를 실행하더라도 MySQL의 처리 내용은 대부분 동일하며, 단순히 "데이터 읽기/쓰기" 영역의 처리만 차이가 나게됩니다. 실질적인 GROUP BY나 ORDERY BY 등 많은 복잡한 처리는 스토리지 엔진 영역이 아닌 MySQL 엔진의 처리 영역인 "쿼리 실행기"에서 처리됩니다.

하나의 쿼리 작업은 여러 하위 작업으로 나누어 지는데, 각 하위 작업이 MySQL 엔진 영역에서 처리되는지 아니면 스토리지 엔진 영역에서 처리되는지 구분할 줄 알아야 합니다. 

아래는 MySQL 서버(mysqld)에서 지원되는 스토리지 엔진을 확인하는 방법입니다.

mysql> SHOW ENGINES;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |

| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |

| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |

| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |

| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |

| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |

| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |

| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |

| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

9 rows in set (0.00 sec)

다음은 플러그인을 확인하는 방법입니다.

mysql> show plugins;

+----------------------------+----------+--------------------+---------+---------+

| Name                       | Status   | Type               | Library | License |

+----------------------------+----------+--------------------+---------+---------+

| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |

| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |

| mysql_old_password         | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |

| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |

| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |

| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |

| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |

| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |

| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |

| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL    | GPL     |

| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |

| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |

| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |

| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |

| partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |

+----------------------------+----------+--------------------+---------+---------+

42 rows in set (0.01 sec)

출처: https://12bme.tistory.com/72?category=682920 [길은 가면, 뒤에 있다.]