MySQL 내부 구조

쿼리만 할 줄 아는 건 MySQL을 아는 게 아니었습니다.

---

MySQL 내부 구조에 대해서 공부해야할까요..?

MySQL은 개발을 하다보면 가장 쉽게 접할 수 있는 데이터베이스 중 하나입니다. 정말 많은 회사에서 특별한 경우가 아니면 MySQL을 사용하는 것을 볼 수 있는데, 이는 그만큼 오랫동안 유지보수가 되었고 오픈소스이기 때문에 생태계가 상당히 풍부하기 때문입니다. 무엇보다도 초보자가 설치해서 직접 쿼리를 날리기가 가장 쉬운 데이터베이스입니다(개인적인 의견입니다).

출처 : Google Image

저는 개발을 시작하면서부터 MySQL 데이터베이스를 만들어서 사용해보곤 했습니다. 그리고 실제로 회사에서 일을 하면서도 MySQL을 사용했습니다. 쿼리를 만들고 수정하는 과정을 거치면서 MySQL에 대해서 꽤 알게 되었다고 생각했었는데, MySQL 데이터베이스를 운영하는 관점에서 보니 제 생각은 완전히 틀렸었습니다. 

MySQL이 실제 쿼리를 수행하는 원리는 상당히 복잡합니다. 그런데 데이터베이스의 성능과 관련된 작업을 하기 위해서는 기본적으로 MySQL의 내부구조를 알고 있어야 합니다. 이렇게까지 해야하나 싶으면서도 궁금하기도 했기에 본 글에서는 "MySQL의 내부구조"에 대해서 공부한 내용을 정리해보았습니다.

---

MySQL이 쿼리를 수행하는 과정

MySQL이 클라이언트로부터 쿼리 요청을 받아서 실제 결과를 반환해주기까지 어떤 방식을 사용하기 있는지 알아보겠습니다. 아래는 쿼리 수행을 위해서 MySQL이 사용하는 Module 리스트입니다.

| MySQL을 구성하는 요소

1. Server Initialization Module

2. Connection Manager

3. Thread Manager

4. Connection Thread

5. User Authentication Module

6. Access Control Module

7. Parser

8. Command Dispatcher

9. Query Cache Module

10. Optimizer

11. Table Manager

12. Table Modification Modules

13. Table Maintenance Module

14. Sstatus Reporting Module

15. Abstracted Storage Engine Interface (Table Handler)

16. Storage Engine Implementations (MyISAM, InnoDB, MEMORY, Berrkeley DB)

17. Logging Module

18. Replication Master Module

19. Replication Slave Module

20. Client/Sserver Protocol API

21. Low-Level Network I/O API

22. Core API

상당히 많습니다.. 이렇게 복잡하게 구성되어 있는 줄 몰랐네요..

그럼 각각의 Module이 언제 어떻게 작업을 하는지 알아보겠습니다.

먼저, mysql 서버가 실행될 때, Initialization Module이 제일 먼저 작업을 시작합니다.  Initilzation Module은 커맨드라인을 통해 실행할 때 전달된 인자들을 포함하여 설정파일을 읽고 이를 파싱합니다. 또한 global memory buffer를 할당하고, 전역 변수와 구조를 설정하며, 접근 제어 테이블을 메모리에 올리는 등 이외에도 많은 초기화 작업을 진행합니다.

Initialization Module이 자신의 역할을  마치면 Connection Manager에게 바통을 넘겨줍니다. Connection Manager는 클라이언트로부터 연결을 받는 역할을 합니다. 클라이언트가 데이터베이스 서버에 연결을 시도할 때, Connection Manager는 low-level의 수많은 네트워크 프로토콜 작업들을 수행하고 바통을 Thread Manager에게 줍니다. Thread Manager는 클라이언트와의 연결을 위한 thread를 제공합니다. 이를 Connection Thread라고 합니다. Connection Thread는 새로 만들어질 수도 있고, 기존에 cache해두었던 thread가 사용될 수도 있습니다. Connection Thread가 할당되면 해당 thread는 사용자 권한 체크를 위해서 Authentication Module을 호출합니다. Authentication Module은 클라이언트가 제공했던 credential이 유효한지 체크합니다. Credential이 유효하다고 판별되면 그 때부터 클라이언트는 요청을 보낼 수 있습니다. 여기서 클라이언트의 요청은 Query(쿼리)와 Command(명령) 으로 구분됩니다. 둘의 차이점은 Query는 별도의 Parser를 거쳐야 하지만 Command는 Parser 없이 실행될 수 있습니다. (이해의 편의를 위해서 Query를 기준으로 설명을 이어가겠습니다.)

클라이언트가 쿼리를 Connection Thread에게 보내면, Connection Thread는 해당 요청을 Command Dispatcher에게 전달합니다. 만약 로깅 설정이 되어 있으면, Command Dispatcher는 Logging Module에 해당 쿼리를 평문 로그로 남기도록 요청합니다. 이 때 full logging  설정이 되어 있는 경우에는 에러가 발생되는 쿼리도 로그에 남게 됩니다. 한편, Command Dispatcher는 쿼리를 Query Cache Module을 통해 Parser에게 전달합니다. Query Cache Module은 cache가 가능한 쿼리인지 확인하고, 이전에 이미 실행된 결과가 있는 경우에는 바로 클라이언트에게 결과를 반환합니다. 이후에는 다시 Connection Thread이 바통을 받아서 이후의 쿼리 요청을 수행합니다. 만약 쿼리가 cache되어 있지 않은 경우에는 parser에 요청을 전달합니다.

Parser는 쿼리의 타입에 따라서 적합한 모듈에게 쿼리를 전달합니다. SELECT 쿼리는 Optimizer, UPDATE/INSERT/DELETE과 테이블 생성 및 스키마 변경 쿼리는 Table Modification Module, Key 통계를 체크, 수정하거나 테이블을 분할하는 쿼리는 Table Maintenance Module, 복제와 관련된 쿼리는 Replication Module, 그리고 상태 관련 쿼리는 Status Reporting Module로 보내집니다. 각 모듈은 전달받은 쿼리를 실행하기 전에 Access Control Module에게 쿼리에 필요한 테이블 리스트를 보냅니다. 해당 테이블들에 접근이 허가된 후에 Table Manager는 테이블을 열고 필요에 따라 다른 쿼리에 의해서 변경되지 않도록 lock을 걸어놓습니다.이제 Table Operation Module은 특정 작업을 수행할 준비가 되고, 여러 요청을 Abstracted Storage Engine Module로 전달합니다. Abstracted Storage Engine Module은 요청 쿼리를 해석해서 특정 Storage Engine Module에서 사용하는 형식에 맞게 변환하여 작업을 수행합니다. 요청을 끝나면 결과값을 클라이언트에게 반환합니다. 작업이 끝나면 Connection Thread는 다음 요청을 기다립니다. 만약 클라이언트가 Quit 명령어를 입력할 시에는 관련 session은 종료됩니다.

쿼리가 수행되는 과정에서 Low-level module들은 데이터에 수정 작업을 진행합니다. 이 때 binary update logging을 활성화시키면, 해당 module들은 Logging Module에 수정된 이벤트에 대한 binary update log를 남길 것을 요청합니다. 이를 binlog라고 합니다.

쿼리는 보통 클라이언트로부터 오지만, 다른 곳에서 쿼리를 보내는 경우도 있습니다. 그 중 하나가 바로 slave 노드에서 오는 요청입니다. 데이터베이스는 중요한 정보를 담고 있기 때문에 이중화와 읽기 부하 분산을 위해서 Master/Slave로 구성되는 것이 보통입니다. Slave로 지정된 서버는 Initialization Module이 실행될 때 Replication Slave Module을 호출합니다. Replication Slave Module은 SQL Thread와 I/O Thread 두 개의 thread를 생성합니다. 이 두 thread가 Master로부터 오는 업데이트 정보를 처리합니다. Master 노드에서는 Replication Master Module이 Master로 오는 업데이트 로그(binlog)를 지속적으로 Slave로 넘겨주는 역할을 합니다.

클라이언트와 서버간의 네트워크 통신은 Client/Server Protocol Module을 통해 이루어집니다. 이 Module은 데이터를 적합한 형식으로 패키징해주고, 설정에 따라서 압축도 해줍니다. Client/Server Protocol Module은 소켓레벨에서 데이터를 주고 받기 위해 Low-Level Network I/O Module을 사용합니다. 추가로 OpenSSL library를 사용하여 데이터를 암호화하기도 합니다.

---

MySQL Configurations

MySQL서버를 운영하다보면 여러가지 성능 문제에 부딪힐 수 있는데, 이럴 때 가장 쉽게 시도해볼 수 있는 것이 configuration 정보를 변경해보는 것입니다. MySQL Configuration의 종류는 어마어마(?)하게 많기 때문에 본 포스트에서는 몇가지만 뽑아서 알아보도록 하겠습니다.

1. innodb_buffer_pool_size

이 설정은 InnoDB의 테이블 데이터와 인덱스를 캐시할 때 얼마나 많은 메모리를 할당할 것인지에 관한 설정값입니다.

2. innodb_flush_log_at_trx_commit

Transaction Commit을 얼마나 자주 flush할 것인지에 대한 설정입니다. InnoDB는 장애에 대비하기 위해서 transaction log를 저장하고 관리합니다. Crash가 발생하면 pending transaction 들이 로그에 남게 되고 이후에 다시 실행됩니다. 데이터 유실을 방지하기 좋은 방법은 사실 commit이 발생하자마다 flush를 시켜서 disk에 쓰는 것입니다.  그러면 crash가 발생했을 때 그 이전에 발생한 모든 transaction을 복원할 수 있다는 장점이 있습니다. 하지만 transaction log를 flush하는 것은 disk에 접근을 최소한 한 번 이상 하기 때문에 부하가 발생합니다. 따라서 짧은 transaction들이 자주 발생하는 경우에는 매번 flush하는 것이 좋은 결과를 가져오지는 못할 수도 있습니다. 이런 경우에는 1초에 한 번 flush를 하는 방식을 사용할 수 있는데, 이와 같은 설정이 바로 innodb_flush_log_at_trx_commit입니다.

innodb_flush_log_at_trx_commit 값이 0이면 log buffer가 1초에 한 번 logfile에 쓰여지고 디스크에 쓰기 작업이 실행됩니다. transaction commit이 발생하는 것과는 관련이 없습니다. 값이 1이면, transaction commit이 발생할 때마다 logfile에는 쓰여지고 바로 disk에 쓰기 작업이 발생합니다. 값이 2인 경우에는 transaction commit시에 logfile에는 쓰여지지만, 실제로 logfile이 디스크에 쓰여지는 건 1초에 한 번입니다.

3. innodb_file_per_table

테이블을 저장할 때 tablespace별로 파일을 분리해서 저장하기 위한 설정값입니다.  만약 이 설정이 off로 되어 있으면, InnoDB는 데이터를 system tablespace에 저장합니다. OS는 이 영역을 innodb를 위한 데이터 영역으로 생각하기 때문에, 테이블을 지우더라도 Disk Space를 os에 반납하지 않습니다. 즉, innodb가 차지하는 디스크 영역은 줄어들지 않습니다.

그런데, innodb_file_per_table 설정을 키면, 테이블과 인덱스 정보가 table별로 따로 저장됩니다. 각 테이블의 tablespace를 사용하기 때문에, 만약 테이블을 drop하거나 truncate하는 경우에는 os에 디스크를 반납하게 됩니다. 또한 다른 데이터베이스에 테이블을 옮기는 경우에도 테이블별로 데이터가 나뉘어져 있기에 훨씬 편합니다. MySQL 5.6 이상 버전부터 이 옵션은 default로 켜져있습니다.

4. innodb_lock_wait_timeout

InnoDB에서는 테이블 뿐만 아니라 레코드에 대해서도 lock을 걸 수 있습니다. 한 thread가 record에 대해서 lock을 걸면 다른 thread는 해당 lock이 풀지기 전까지는 해당 record에 작업을 할 수가 없습니다. 이러한 특성은 성능 향상에 큰 기여를 했지만, 때로는 deadlock에 걸릴 수 있다는 단점이 있습니다. 그래서 innodb는 innodb_lock_wait_timeout 값을 기준으로 요청이 이 값보다 길게 wait상태에 있는 경우 deadlock으로 간주하고 에러를 발생시킵니다. 설령 그게 실제로 deadlock이 아니라도 할지라도 상관 없습니다.

5. init-file

이 옵션은 MySQL 서버가 실행될 때 돌려야하는 SQL 명령문을 지정하는 옵션입니다. 작업을 시작하기 전에 특정 테이블이 존재하는지 체크하거나, 글로벌 변수값을 지정해야 하는 경우 등에 유용하게 활용됩니다.

6. key_buffer_size

MyISAM은 InnoDB와는 다르게 key값만 캐시할 수 있습니다. 이 옵션은 key cache 사이즈 값입니다.

7. log_slow_queries

슬로우 쿼리를 logging할지에 관한 옵션입니다. 슬로우 쿼리는 MySQL 성능 향상을 위해 가장 먼저 해결해야하는 것 중 하나이므로 이 옵션은 켜두시고 모니터링 하는 것이 좋습니다. 추가로 슬로우 쿼리를 몇 초로 규정할지에 관한 옵션은 long_query_time입니다.

8. max_allowed_packet

MySQL은 모든 쿼리가 충분히 짧아 MySQL이 정한 하나의 packet 크기보다 작게 전달되기를 희망합니다. 왜냐하면 이 packet을 저장하기 위해 MySQL은 메모리를 할당하는데, 이보다 더 큰 값이 들어와서 Out Of Memory가 발생하기를 원치 않기 때문입니다. 이 옵션은 MySQL이 하나의 packet 사이즈가 최대로 가질 수 있는 값에 관한 옵션입니다.

9. max_connections

MySQL 서버가 수용할 수 있는 최대의 Connection 개수에 대한 설정값입니다. Connection은 서버의 메모리를 차지하기 때문에 메모리 용량을 고려해서 max_connections 값을 정하는 것이 좋습니다.

10. max_heap_table_size

MySQL에는 메모리에 저장할 수 있는 테이블이 있는데 이를 heap table이라고 합니다. heap table은 데이터를 메모리에 저장하기 때문에 속도가 상당히 빠르지만, 서버가 다운되거나 재시작되면 테이블 전체가 날라간다는 단점이 있습니다. 이 옵션은 heap table에 사용할 메모리의 사이즈를 위한 옵션입니다.

11. max_join_size

이 옵션은 record combination에 대한 설정 값입니다. Optimizer는 쿼리가 이 옵션 사이즈보다 큰 record combination작업이 진행된다고 판단되면 쿼리 작업을 중단합니다.

12. query_cache_type

이 옵션은 쿼리 결과값을 cache하기 위한 옵션입니다. 0은 cache하지 않음, 1은 SQL_NO_CACHE 플래그가 붙은 것을 제외한 모든 쿼리를 cache, 2는 SQL_CACHE 플래그가 붙은 쿼리만 cache합니다.

13. table_open_cache

이 옵션은 모든 Thread가 열 수 있는 table 수입니다. 테이블이 하나라고 해도 여러 thread에서 같은 테이블을 조회하면 각각 thread에 table descriptor가 할당됩니다. 이 값을 높이면 mysql 데몬이 필요한 file descriptor의 개수가 증가하여 여러 thread가 빠르게 테이블에 접근하여 데이터를 조회할 수 있습니다. 이 값은 위에서 언급했던 max_connections 값과 긴밀하게 연관되어 있습니다. 자세한 사항은 mysql의 홈페이지를 참조하세요~

https://dev.mysql.com/doc/refman/5.7/en/table-cache.html

MySQL :: MySQL 5.7 Reference Manual :: 8.4.3.1 How MySQL Opens and Closes Tables

이외에도 어마무시하게 많은 옵션들이 있지만 처음 공부하는 입장에서는 이 정도로 마무리하겠습니다. MySQL을 Deep하게 사용하지 않는 경우에는 기본 옵션으로 사용해도 (개인적으로)무방하다고 봅니다. 그런데 트래픽이 많아지거나 순간적으로 DB에 몰리는 트래픽 패턴을 가지고 있다면 분명 MySQL설정값 변경을 통해서 성능최적화를 해야합니다.

* 다음에 추가적으로 알게되는 중요한 옵션들이 있으면 그 때 다시 업데이트 하도록 하겠습니다.

**본 글은 understanding MySQL Internals(2007), mysql official manual을 참조하였습니다~

---

## 잘못된 내용은 피드백주시면 더 좋은 글로 보답하겠습니다.