List
Search
1. MySQL 서버 구성
•
MySQL 서버 구성
◦
MySQL 엔진
▪
쿼리 파서, 옵티마이저 기능등을 스토리지 엔진과 구분하기 위해 용어 분리
◦
스토리지 엔진
1.1. MySQL 엔진
•
역할
◦
DBMS의 두뇌에 해당하는 처리를 수행
◦
요청된 SQL 문장 분석 및 최적화
•
구성
◦
쿼리 파서: 쿼리의 구문 분석
◦
전처리기: 객체 매핑 및 권한 확인
◦
옵티마이저: 쿼리 최적화 계획 수립
◦
실행 엔진: 계획 실행 및 핸들러와의 상호작용
1.2. 스토리지 엔진
•
실제 데이터를 디스크 스토리지에 저장
•
디스크 스토리지로부터 데이터 조회
•
MySQL에서 여러개의 스토리지 엔진을 동시에 사용 가능
◦
테이블 생성 시 스토리지 엔진 지정 가능
CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;
SQL
복사
1.3. 핸들러 API
•
핸들러(Handler) 요청
◦
MySQL 엔진 쿼리 실행기 → 스토리지 엔진으로 쓰기/읽기 요청
•
핸들러 API
◦
핸들러 요청에서 사용되는 API
◦
MySQL 엔진과 스토리지 엔진 간의 데이터 주고받는 역할
mysql> SHOW GLOBAL STATUS LIKE 'Handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 591 |
| Handler_delete | 8 |
| Handler_discover | 0 |
| Handler_external_lock | 6309 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 41 |
| Handler_read_key | 1728 |
| Handler_read_last | 0 |
| Handler_read_next | 4045 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 680 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 331 |
| Handler_write | 8 |
+----------------------------+-------+
18 rows in set (0.02 sec)
SQL
복사
2. 스레드
MySQL은 프로세스가 아닌 스레드 기반임
•
구성
◦
포어그라운드 스레드
◦
백그라운드 스레드
•
현재 실행중인 스레드 목록 조회 쿼리
◦
thread/sql/one_connection 라고 되어있는 스레드가 실제 사용자 요청 처리하는 스레드
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 | localhost |
+-----------+---------------------------------------------+------------+------------------+------------------+
39 rows in set (0.02 sec)
SQL
복사
2.1. 포어그라운드 스레드 (클라이언트 스레드)
•
역할
◦
최소한 MySQL 서버에 접속된 클라이언트 수 만큼 존재
◦
각 클라이언트 사용자가 요청하는 쿼리 문장 처리
▪
데이터를 MySQL 데이터 버퍼나 캐시로부터 조회
▪
데이터 버퍼나 캐시에 없는 경우 직접 디스크의 데이터/인덱스 파일로 부터 데이터 조회 후 작업 처리
•
스레드 캐시(Thread Cache)
◦
사용자 요청 → 작업 완료 → 커넥션 종료 → 스레드는 스레드 캐시(Thread Cache)로 되돌아 감
◦
스레드 캐시 일정 개수 유지
▪
캐시에 일정 개수 이상의 대기 중인 스레드가 존재 → 스레드 캐시에 넣지 않고 스레드 종료
▪
thread_cache_size 로 스레드 캐시 내 최대 스레드 개수 설정 가능
•
스토리지 엔진별 특징
◦
MyISAM
▪
디스크 쓰기작업까지 포어그라운드 스레드가 처리
◦
InnoDB
▪
데이터 버퍼 및 캐시까지만 포어그라운드 스레드가 처리
▪
버퍼 → 디스크 기록은 백그라운드 스레드가 처리
위에서 설명한 최소한 MySQL 서버에 접속된 클라이언트 수 만큼 존재 라는 말을 조금더 명확히 할 필요가 있다. DB 관점에서 클라이언트는 DB에 연결된 개별 연결을 의미한다. 대부분 상용 백엔드 시스템에서는 커넥션 풀을 이용하여 일정 수의 DB 연결을 생성해두고, API 요청이 들어올 때마다 하나를 재 사용한다.
MySQL은 기본적으로 내장된 커넥션 풀을 제공하진 않지만 스레드 풀이나 최대 허용 커넥션 수 등으로 커넥션 수를 조정할 수 있다.
•
스레드 풀 사용 시
SHOW VARIABLES LIKE 'thread_pool_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_pool_size | 4 |
+-------------------+-------+
SQL
복사
•
일반적인 MySQL 연결 상태 확인
// 현재 활성 연결 수 확인
SHOW STATUS LIKE 'Threads_connected';
// 최대 허용 커넥션 수 확인
SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
SQL
복사
2.2. 백그라운드 스레드
•
InnoDB의 백그라운드 스레드 종류
◦
Main Thread: 인서트 버퍼(Insert Buffer)를 병합하는 스레드
◦
Log thread: 로그를 디스크로 기록하는 스레드
◦
Write thread: InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
◦
Read thread: 데이터를 버퍼로 읽어 오는 스레드
◦
Monitor thread: 잠금이나 데드락을 모니터링하는 스레드
•
특징
◦
위 중, 로그 스레드와 쓰기 스레드가 가장 중요함
◦
쓰기/읽기 스레드는 시스템 변수로 갯수 설정 가능
mysql> SHOW VARIABLES LIKE '%_io_threads';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_read_io_threads | 4 |
| innodb_write_io_threads | 4 |
+-------------------------+-------+
SQL
복사
◦
읽기 작업은 주로 클라이언트 스레드에서 처리되므로 많이 설정 필요 x
◦
쓰기 작업은 백그라운드 스레드에서 처리 됨
▪
일반 내장 디스크 사용시 → 2~4개 설정
▪
DAS / SAN 등 스토리지 사용시 디스크 최적을 사용할 수 있을 만큼 설정 필요
3. 메모리
3.1. 글로벌 메모리 영역
•
특징
◦
클라이언트 스레드 수와 무관하게 하나의 메모리 공간만 할당
◦
모든 스레드에 공유
•
구성
◦
테이블 캐시
◦
InnoDB 버퍼 풀
◦
InnoDB 어댑티브 해시 인덱스
◦
InnoDB 리두 로그 버퍼
3.2. 로컬 메모리 영역
•
특징
◦
세션 메모리 영역
◦
MySQL 서버상 존재하는 클라이언트 스레드가 쿼리하는데 사용되는 메모리 영역
◦
스레드(커넥션) 별로 독립적으로 할당되며 절대 공유되지 않음
◦
쿼리의 용도별로 필요한 공간만 할당됨. 필요하지 않은 경우 메모리 공간 할당 자체도 안됨
•
구성
◦
정렬(소트) 버퍼
◦
조인 버퍼
◦
바이너리 로그 캐시
◦
네트워크 버퍼
4. 플러그인과 컴포넌트
4.1. 플러그인 스토리지 엔진
•
MySQL의 아키텍처는 모듈화되어 있어, 사용자가 MySQL 서버에 새로운 기능을 추가할 수 있음
•
MySQL 서버는 다양한 스토리지 엔진을 지원함
•
테이블을 생성할 때 ENGINE 옵션을 사용하여 지정할 수 있음
•
SHOW ENGINES 명령어 현재 MySQL 서버에서 사용 가능한 스토리지 엔진을 확인 가능
4.2. 컴포넌트 아키텍처
•
MySQL 8.0부터 도입
◦
플러그인의 단점을 보완한 구조
•
주요 특징
◦
플러그인 간 통신 불가능 문제 해결
▪
기존 플러그인은 MySQL 서버와만 통신 가능, 플러그인들 간의 통신이 불가능
◦
캡슐화 문제 해결
▪
플러그인은 MySQL 서버의 변수나 함수를 직접 호출했기 때문에 안전 X
◦
초기화 문제 해결
▪
플러그인은 상호 의존 관계를 설정할 수 없어 초기화가 어려움
5. 기타 주요 기능
5.1. 쿼리 캐시
•
MySQL 8.0에서 제거됨
•
성능 저하의 원인이 되기도 함
◦
테이블 데이터 변경 시 관련된 캐시 데이터 삭제
5.2. 스레드 풀
•
Percona Server의 스레드 풀 기능
•
제한된 스레드 수로 높은 동시 처리 성능 제공
5.3. 트랜잭션 지원 메타데이터
•
메타데이터(데이터 딕셔너리) 종류
◦
테이블 구조 정보
◦
스토어드 프로그램
•
5.7 버전 이하
◦
파일 기반으로 저장
◦
생성 & 변경 작업이 트랜잭션 지원을 하지 않아 원자성을 보장 X
•
8.0 버전 이후
◦
InnoDB 기반의 테이블에 메타데이터 저장
◦
*.ibd
6. 쿼리 실행 관점의 MySQL 구조
•
전체 구조
6.1. 쿼리 파서(Query Parser)
•
사용자가 입력한 쿼리 문장을 토큰으로 분리하고 파서 트리 구조로 변환
◦
토큰: MySQL이 인식할 수 있는 최소 단위의 어휘나 기호
•
기본 문법 오류를 감지 & 오류 메세지 전달
6.2. 전처리기 (Preprocessor)
•
파서 단계에서 생성된 트리를 기반으로 쿼리 문장의 구조적 문제를 확인
•
테이블 이름, 컬럼 이름, 함수 등의 존재 여부와 접근 권한을 검사
6.3. 옵티마이저 (Optimizer)
•
옵티마이저는 쿼리를 가장 효율적으로 실행할 방법을 결정하는 역할
•
DBMS의 성능을 좌우하는 중요한 요소
•
비용을 최소화하고 실행 계획을 수립
6.4. 실행 엔진 (Execution Engine)
•
옵티마이저가 수립한 실행 계획에 따라 실제로 쿼리를 실행
•
핸들러(스토리지 엔진)와 상호작용하여 데이터를 처리
6.5. 핸들러 (Handler: 스토리지 엔진)
•
MySQL 엔진의 가장 밑단에 위치
•
실행 엔진의 요청에 따라 데이터를 디스크에서 읽기/쓰기
•
핸들러 = 스토리지 엔진