MySQL 엔진과 스토리지 엔진
mysql 엔진: 머리
스토리지 엔진: 손, 발
mysql 엔진은 요청된 sql 문장을 분석하거나 최적화 하는 등의 처리를 수행합니다.
스토리지 엔진은 데이터를 저장하거나 읽어오는 작업을 수행합니다.
myql 엔진은 하나지만, 스토리지 엔진은 여러 개를 동시에 사용할 수 있습니다,
CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;test_table 에 대해 INSERT, UPDATE 등의 DML 작업이 발생하면 InnoDB 스토리지 엔진이 처리합니다.
각 스토리지 엔진은 성능 향상을 위한 기능을 가지고 있습니다.
- MyISAM 스토리지 엔진: 키 캐시
- InnoDB 스토리지 엔진: 버퍼 풀
핸들러 API
mysql 엔진의 쿼리 실행기에서 각 스토리지 엔진에 쓰기, 읽기 등의 작업을 요청하는데 이 요청을 핸들러 요청이라고 합니다.
이때 사용되는 API를 핸들러 API 라고 합니다. (mysql 엔진 <-> 스토리지 엔진)
아래 명령어로 핸들러 API 를 통해 얼마나 많은 데이터 작업이 있었는지 확인할 수 있습니다.
(mysql 엔진이 각 스토리지 엔진에게 보낸 명령의 횟수를 의미하는 변수)
SHOW GLOBAL status LIKE 'Handler%';
mysql 스레딩 구조
mysql 서버는 프로세스 기반이 아니라 스레드 기반으로 작동하며 포그라운드와 백그라운드 스레드로 구분할 수 있습니다.
mysql 서버에서 실행 중인 스레드 목록은 아래 쿼리문으로 확인할 수 있습니다.
mysql> SELECT thread_id, name, TYPE, processlist_user, processlist_host
-> FROM PERFORMANCE_SCHEMA.THREADS ORDER BY TYPE, THREAD_ID;
+-----------+---------------------------------------------+------------+------------------+------------------+
| thread_id | name | TYPE | processlist_user | processlist_host |
+-----------+---------------------------------------------+------------+------------------+------------------+
| 1 | thread/sql/main | BACKGROUND | NULL | NULL |
| 3 | thread/innodb/io_ibuf_thread | BACKGROUND | NULL | NULL |
| 4 | thread/innodb/io_log_thread | BACKGROUND | NULL | NULL |
| 5 | thread/innodb/io_read_thread | BACKGROUND | NULL | NULL |
| 6 | thread/innodb/io_read_thread | BACKGROUND | NULL | NULL |
| 7 | thread/innodb/io_read_thread | BACKGROUND | NULL | NULL |
| 8 | thread/innodb/io_read_thread | BACKGROUND | NULL | NULL |
| 9 | thread/innodb/io_write_thread | BACKGROUND | NULL | NULL |
| 10 | thread/innodb/io_write_thread | BACKGROUND | NULL | NULL |
| 11 | thread/innodb/io_write_thread | BACKGROUND | NULL | NULL |
| 12 | thread/innodb/io_write_thread | BACKGROUND | NULL | NULL |
| 13 | thread/innodb/page_flush_coordinator_thread | BACKGROUND | NULL | NULL |
| 14 | thread/innodb/log_checkpointer_thread | BACKGROUND | NULL | NULL |
| 15 | thread/innodb/log_flush_notifier_thread | BACKGROUND | NULL | NULL |
| 16 | thread/innodb/log_flusher_thread | BACKGROUND | NULL | NULL |
| 17 | thread/innodb/log_write_notifier_thread | BACKGROUND | NULL | NULL |
| 18 | thread/innodb/log_writer_thread | BACKGROUND | NULL | NULL |
| 19 | thread/innodb/log_files_governor_thread | BACKGROUND | NULL | NULL |
| 24 | thread/innodb/srv_lock_timeout_thread | BACKGROUND | NULL | NULL |
| 25 | thread/innodb/srv_error_monitor_thread | BACKGROUND | NULL | NULL |
| 26 | thread/innodb/srv_monitor_thread | BACKGROUND | NULL | NULL |
| 27 | thread/innodb/buf_resize_thread | BACKGROUND | NULL | NULL |
| 28 | thread/innodb/srv_master_thread | BACKGROUND | NULL | NULL |
| 29 | thread/innodb/dict_stats_thread | BACKGROUND | NULL | NULL |
| 30 | thread/innodb/fts_optimize_thread | BACKGROUND | NULL | NULL |
| 31 | thread/mysqlx/worker | BACKGROUND | NULL | NULL |
| 32 | thread/mysqlx/worker | BACKGROUND | NULL | NULL |
| 33 | thread/mysqlx/acceptor_network | BACKGROUND | NULL | NULL |
| 37 | thread/innodb/buf_dump_thread | BACKGROUND | NULL | NULL |
| 38 | thread/innodb/clone_gtid_thread | BACKGROUND | NULL | NULL |
| 39 | thread/innodb/srv_purge_thread | BACKGROUND | NULL | NULL |
| 40 | thread/innodb/srv_worker_thread | BACKGROUND | NULL | NULL |
| 41 | thread/innodb/srv_worker_thread | BACKGROUND | NULL | NULL |
| 42 | thread/innodb/srv_worker_thread | BACKGROUND | NULL | NULL |
| 44 | thread/sql/signal_handler | BACKGROUND | NULL | NULL |
| 45 | thread/mysqlx/acceptor_network | BACKGROUND | NULL | NULL |
| 43 | thread/sql/event_scheduler | FOREGROUND | event_scheduler | localhost |
| 47 | thread/sql/compress_gtid_table | FOREGROUND | NULL | NULL |
| 49 | thread/sql/one_connection | FOREGROUND | root | 192.168.112.1 |
| 50 | thread/sql/one_connection | FOREGROUND | root | 192.168.112.1 |
| 51 | thread/sql/one_connection | FOREGROUND | root | 192.168.112.1 |
| 53 | thread/sql/one_connection | FOREGROUND | root | localhost |
+-----------+---------------------------------------------+------------+------------------+------------------+
42 rows in set (0.01 sec)책에는 44개의 스레드가 실행중이라고 되어있지만 제가 실행한 mysql은 42개의 스레드가 실행중이며 6개는 포그라운드, 나머지는 백그라운드 스레드입니다.
책에는 thread/sql/one_connection 스레드만 실제 사용자의 요청을 처리하는 포그라운드 스레드라고 나와있는데, 저는 이 스레드가 4개나 떠있어서 일단 기억해두고 나중에 이유를 찾아봐야겠습니다. 동일한 이름의 스레드가 2개 이상인건 mysql 서버 설정에 의해 여러 스레드가 동일 작업을 병렬로 처리하는 경우라고 하는데, 따로 병렬 설정을 하지는 않았습니다.
포그라운드 스레드 (클라이언트 스레드)
포그라운드 스레드는 최소 mysql 서버에 접속한 클라이언트 수만크 존재하고 각 클라이언트 사용자가 요청하는 쿼리문을 처리합니다.
사용자가 커넥션을 종료하면 해당 커넥션을 담당하던 스레드는 스레드 캐시로 되돌아갑니다,
이 때, 이미 스레드 캐시에 일정 개수 이상의 대기중인 스레드가 있으면 캐시에 안넣고 바로 종료시킵니다.
스레드 캐시에 유지할 수 있는 최대 스레드 개수는 thread_cache_size 시스템 변수로 설정합니다.
SHOW variables LIKE 'thread_cache_size';
포그라운드 스레드는 데이터를 버퍼나 캐시로부터 가져오며 이게 없으면 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어옵니다.
- MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리(지연된 쓰기가 있지만 일반적인 방식이 아님)
- InnoDB 테이블은 데이터 버퍼, 캐시까지만 포그라운드 캐시가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리
백그라운드 스레드
InnoDB에서 백그라운드 스레드 처리
- 인서트 버퍼를 병합하는 스레드
- 로그를 디스크로 기록하는 스레드
- InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
- 데이터를 버퍼로 읽어오는 스레드
- 잠금이나 데드락을 모니터링하는 스레드
가장 중요한 스레드
- 로그 스레드
- 쓰기 스레드
-- 쓰기 스레드
SHOW variables LIKE 'innodb_write_io_threads';
-- 읽기 쓰레드
SHOW variables LIKE 'innodb_read_io_threads';읽기 작업은 보통 클라이언트 스레드에서 처리되기때문에 많이 설정할 필요가 없습니다.
쓰기 스레드는 내장 디스크일 경우 2~4, DAS, SAN 과 같은 스토리지 일 경우 충분하게 설정해야합니다.
일반적인 DBMS와 InnoDB 는 쓰기 작업을 할 때 지연(버퍼링) 처리로 일괄 처리를 하기때문에 완전히 저장될때까지 기다리지 않아도 됩니다.
하지만 MyISAM은 포그라운드 스레드가 쓰기 작업까지 처리하기 때문에 기다려야합니다.
메모리
글로벌 메모리 영역의 모든 메모리 공간은 mysql 서버가 시작되면서 os 로부터 할당됩니다.
글로벌 메모리 영역
클라이언트 스레드 수와 무관하게 메모리 공간을 할당받으며 모든 스레드에 공유됩니다.
- 테이블 캐시
- InnoDB 버퍼 풀
- InnoDB 어댑티브 해시 인덱스
- InnoDB 리두 로그 버퍼
로컬 메모리 영역 (세션 메모리 영역, 클라이언트 메모리 영역)
클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역입니다.
클라이언트와 MySQL 서버와의 커넥션을 세션이라고 하기 때문에 세션 메모리 영역이라고도 표현합니다.
각 클라이언트 스레드별로 독립적으로 할당되어 절대 공유되지 않습니다.
- 소트 버퍼, 조인 버퍼
필요할때만 공간이 할당되고 필요없을때는 할당 안함 (쿼리 실행하는 순간에만 할당했다가 끝나면 할당 해제)
- 커넥션 버퍼, 결과 버퍼
커넥션이 열려있는 동안 계속 할당된 상태
- 소트 버퍼
- 조인 버퍼
- 바이너리 로그 캐시
- 네트워크 버퍼
플러그인 스토리지 엔진 모델
mysql에서 쿼리가 실행될때 대부분의 작업은 mysql 엔진이 처리하고, 데이터 읽기/쓰기 작업만 스토리지 엔진이 처리합니다.
스토리지 엔진의 종류에 따라서 데이터 읽기/쓰기 작업 처리 방식이 많이 달라집니다.
중요한 것은 다음과 같습니다.
하나의 쿼리 작업은 여러 하위 작업으로 나뉘는데, 각 하위 작업이 mysql 엔진 영역에서 처리되는지, 스토리지 엔진 영역에서 처리되는지 구분할 줄 알아야 한다.
ORDER BY, GROUP BY 등의 처리는 mysql 엔진의 쿼리 실행기에서 처리됩니다.
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster | NO | Clustered, fault-tolerant tables | NULL | NULL | NULL |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| 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 |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| ndbinfo | NO | MySQL Cluster system information storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)YES: mysql 서버 (mysqld)에 해당 스토리지 엔진이 포함되어 있고, 사용 가능으로 활성화된 상태
DEFAULT: YES와 동일한 상태이고 필수 스토리지 엔진. 이 스토리지 엔진이 없으면 mysql이 시작되지 않을수도 있음
NO: 현재 mysql 서버(mysqld)에 포함되지 않음
DISABLED: 현재 mysql 서버 (mysqld)에는 포함됐지만 파라미터에 의해 비활성화된 상태
NO인 스토리지 엔진은 다시 빌드하거나, 플러그인 형태로 빌드된 스토리지 엔진 라이브러리를 다운로드해서 끼워넣으면 사용할 수 있습니다.
mysql> SHOW PLUGINS;
+----------------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+----------------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| daemon_keyring_proxy_plugin | ACTIVE | DAEMON | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | 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_TEMP_TABLE_INFO | 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_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CACHED_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SESSION_TEMP_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| TempTable | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| ndbcluster | DISABLED | STORAGE ENGINE | NULL | GPL |
| ndbinfo | DISABLED | STORAGE ENGINE | NULL | GPL |
| ndb_transid_mysql_connection_map | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| mysqlx | ACTIVE | DAEMON | NULL | GPL |
+----------------------------------+----------+--------------------+---------+---------+
48 rows in set (0.15 sec)
컴포넌트
mysql 8.0부터 기존의 플러그인 아키텍처를 대체하기 위해 컴포넌트 아키텍처가 지원됩니다.
기존 플러그인 단점
- 플러그인은 오직 mysql 서버와 인터페이스 할 수 있고, 플러그인끼리는 통신할 수 없음
- 플러그인은 mysql 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않음 (캡슐화 안됨)
- 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어려움
쿼리 실행 구조
쿼리파서
쿼리파서는 쿼리문을 토큰으로 분리해 트리 형태의 구조로 만드는 작업을 의미합니다.
토큰: mysql이 인식할 수 있는 최소 단위의 어휘나 기호
쿼리문의 문법 오류는 쿼리파서에서 발견되고 사용자에게 오류 메세지를 전달합니다.
전처리기
파서 트리를 기반으로 쿼리문에 구조적인 문제가 있는지 확인합니다.
실제 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰은 여기서 걸러집니다.
옵티마이저
쿼리문을 저렴한 비용으로 가장 빠르게 처리하는 길을 찾는 과정입니다.
실행 엔진
옵티마이저: 회사의 경영진
실행엔진: 중간 관리자
핸들러: 실무자
톱이마이저가 GROUP 를 처리하기 위해 임시 테이블을 사용한다고 가정
1. 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
2. 실행 엔진이 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
3. 읽어온 레코드들을 1번에서 준비한 임시 테이블에 저장하라고 핸들러에게 요청
4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어오라고 핸들러에게 요청
5. 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김
핸들러 (스토리지 엔진)
실행 엔진의 요청에 따라 디스크에 데이터를 쓰거나 읽습니다.
쿼리 캐시 (삭제됨)
SQL 실행 결과를 메모리에 저장하고, 동일 쿼리가 실행되면 테이블을 읽지 않고 캐시 결과를 반환했었으나 8.0 버전부터 완전히 사라졌습니다.
데이터가 변경되면 변경된 테이블과 관련된 것들은 모두 삭제해야되서 동시 처리 성능이 저하됐고, 많은 버그가 있었기 때문입니다.
스레드 풀
mysql 커뮤니티 에디션은 스레드 풀 기능을 지원하지 않습니다.
책에서는 Percona Server 에서 제공하는 스레드 풀 기능으로 설명이 나옵니다. (플러그인 형태로 작동)
스레드풀의 목적은 요청 처리 스레드 개수를 줄여서 mysql 서버의 cpu가 제한된 개수의 스레드 처리에만 집중할 수 있게 하여 서버의 자원 소모를 줄이는 것입니다. -> 불필요한 컨텍스트 스위치를 줄여서 오버헤드를 낮춤.
트랜잭션 지원 메타데이터
db 서버에서 테이블의 구조 정보와 스토어드 프로그램 등의 정보를 데이터 딕셔너리(메타데이터) 라고 합니다.
mysql 8.0 이전까지 데이터 딕셔너리를 파일에 저장했는데, 이런 파일 기반 메타데이터는 트랜잭션을 지원하지 않는 문제가 있었습니다.
8.0 부터는 데이터 딕셔너리를 InnoDB 테이블에 저장합니다.
mysql 서버가 작동하는데 필요한 테이블을 통틀어서 시스템 테이블 이라고 합니다. 예) 사용자 인증, 권한 관련
이 시스템 테이블도 8.0부터 InnoDB 를 사용하며 시스템 테이블 + 데이터 딕셔너리를 모아 mysql DB 에 저장합니다.
mysql Db는 통째로 mysql.ibd 테이블스페이스에 저장됩니다.
사용자는 테이블 구조 정보 테이블을 확인할 수 없는데, 임의로 수정을 못하도록 막혀 것입니다.
데이터 딕셔너리 정보를 information_schema DB의 뷰로 확인할 수 있습니다.
SHOW CREATE TABLE INFORMATION_SCHEMA.TABLES;
-- root계정도 권한없음 출력
SELECT * FROM mysql.tables LIMIT 1;
InnoDB 외 스토리지 엔진을 사용하는 테이블은 SDI(Serialized Dictionary Information) 파일을 사용합니다.
InnoDB 테이블의 구조도 SDI 파일로 변경할 수 있으며 대표적으로 ibd2sdi 유틸리티가 있습니다.
'Database' 카테고리의 다른 글
| [Real MySQL 8.0] 사용자 및 권한 (0) | 2023.09.01 |
|---|---|
| [Real MySQL 8.0] mysql 설치와 설정 (0) | 2023.08.28 |
| [친절한 SQL 튜닝] NDV, 선택도 (Selectivity), 카디널리티 (Cardinality) (0) | 2023.08.16 |
| MongoDB에서 bulkWrite updateOne upsert를 할 때 filter: _id 주의점 (0) | 2023.03.28 |