MySQL 서버는 사람의 머리 역할을 담당하는 MySQL 엔진과 손발 역할을 담당하는 스토리지 엔진으로 구분할 수 있다.
그리고 손과 발의 역할을 담당하는 스토리지 엔진은 핸들러 API를 만족하면 누구든지 스토리지 엔진을 구현해서
MySQL 서버에 추가해서 사용할수 있다. 이번 장에서는 MySQL 엔진과 MySQL 서버에서 기본으로 제공되는
InnoDB 스토리지 엔진, 그리고 MyISAM 스토리지 엔진을 구분해서 살펴보겠다
먼저 MySQL의 쿼리를 작성하고 튜닝할 떼 필요한 기본적인 MySQL 엔진의 구조를 훑어 보겠다
- MySQL 서버는 다른 DBMS에 비해 구조가 상당히 독특하다
- 사용자 입장에서 보면 거의 차이가 느껴지지 않지만 이러한 독특한 구조 때문에 다른 DBMS에서는 가질 수 없는 엄청난 혜택을 누린다
- 반대로 다른 DBMS에서는 문제 되지 않을 것들이 가끔 문제가 된다
4.1.1 MySQL의 전체 구조
- MySQL은 일반 상용 RDBMS와 같이 대부분의 프로그래밍 언어로부터 접근 방법을 모두 지원한다
- MySQL 고유의 C API부터 시작해 JDBC나 ODBC, 그리고. NET의 표준 드라이버를 제공하며,
- 이러한 드라이버를 이용해 C/C++, PHP, 자바, 펄, 파이썬, 루비나. NET 및 코볼까지
- 모든 언어로 MySQL 서버에서 쿼리를 사용할 수 있게 지원한다
- MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 구분한다
4.1.1.1 MySQL 엔진
- MySQL 엔진은 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리기,
- 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룬다.
- 또한 MySQL은 표준 SQL(ANSI SQL) 문법을 지원하기 때문에 표준 문법에 따라 작성된 쿼리는 타 DBMS와 호환되어 실행
4.1.1.2 스토리지 엔진
- MySQL 엔진은 요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS의 두뇌에 해당하는 처리를 수행하고,
- 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분은 스토리지 엔진이 전담한다
- MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다
- 다음 예제와 같이 테이블이 사용할 스토리지 엔진을 지정하면 이후 해당 테이블의 모든 읽기 작업이나
- 변경 작업은 정의된 스토리지 엔진이 처리한다
## 다음 예제와 같이 테이블이 사용할 스토리지 엔진을 지정하면 이후 해당 테이블의 모든 읽기작업이나 변경작업은 정의된 스토리지 엔진이 처리한다
CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE = INNODB;- 위 예제에서는 test_table은 InnoDB 스토리지 엔진을 사용하도록 정의했다.
- 이제 test_table에 대해 INSERT, UPDATE, DELETE, SELECT,... 등의 작업이 발생하면
- InnoDB 스토리지 엔진이 그러한 처리를 담당한다.
- 그리고 각 스토리지 엔진은 성능 향상을 위해 키 캐시(MyISAM 스토리지 엔진)나
- InnoDB 버퍼 풀(InnoDB 스토리지 엔진)과 같은 기능을 내장한다
4.1.1.3 핸들러 API
- MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에 쓰기 또는 읽기를 요청하는데,
- 이러한 요청을 핸들러 요청이라 하고, 여기서 사용되는 API를 핸들러 API라고 한다
- InnoDB 스토리지 엔진 또한 이 핸들러 API를 이용해 MySQL 엔진과 데이터를 주고받는다.
- 이 핸들러 API를 통해 얼마나 많은 데이터 작업이 있었는지 SHOW, GLOBAL, STATUS, LIKE, 'Handler%'; 명령으로 확인
## 이 핸들러 API를 통해 얼마나 많은 데이터 작업이 있었는지 SHOW GLOBAL STATUS LIKE 'Handler%'; 명령으로 확인
SHOW GLOBAL STATUS LIKE 'Handler%';
4.1.2 MySQL 스레딩 구조
- MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동하며, 크게 포그라운드 스레드와 백그라운드 스레드로 구분
- MySQL 서버에서 실행 중인 스레드의 목록은 다음과 같이 performance_schema 데이터 베이스의 threads 테이블 통해 확인
## MySQL 서버에서 실행 중인 스레드의 목록은 다음과 같이 performance_schema 데이터 베이스의 threads 테이블 통해 확인
SELECT thread_id, name, type, processlist_user, processlist_host
FROM performance_schema.threads ORDER BY type, thread_id;
- 전체 44개의 스레드가 실행 중이며, 그중에서 41개의 스레드가 백그라운드이고 나머지 3개만 포그라운드 스레드로 표시돼있다.
- 이 중에서 마지막 'thread/sql/one_connection' 스레드만 실제 사용자의 요청을 처리하는 포그라운드 스레드다.
- 백그라운드 스레드의 개수는 MySQL 서버의 설정 내용에 따라 가변적일 수 있다.
- 동일한 이름의 스레드가 2개 이상씩 보이는 것은 MySQL 서버의 설정 내용에 의해 여러 스레드가 동일 작업을 병렬로 처리하는 경우다
여기서 소개하는 스레드 모델은 MySQL 서버가 전통적으로 가지고 있던 스레드 모델이며, MySQL 커뮤니티 에디션에서 사용되는 모델이다. MySQL 엔터프라이즈 에디션과 Percona MySQL 서버에서는 전 톡 정인 스레드 모델뿐 아니라 스레드 풀 모델을 사용할 수 있다. 스레드 풀과 전통적인 스레드 모델의 가장 큰 차이점은 포그라운드 스레드와 커넥션의 관계다.
전통적인 스레드 모델에서는 커넥션 별로 포그라운드 스레드가 하나씩 생성되고 할당된다.
하지만 스레드 풀에서는 커넥션과 포그라운드 스레드는 1:1 관계가 아니라 하나의 스레드가 여려 개의 커넥션 요청을 전담한다.
4.1.2.1 포그라운드 스레드(클라이언트 스레드)
- 포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며,
- 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다
- 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시로 돌아간다.
- 이때 이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면 스레드 캐시에 넣지 않고
- 스레드를 종료시켜 일정 개수의 스레드만 스레드 캐시에 존재하게 한다.
- 이때 스레드 캐시에 유지할 수 있는 최대 스레드 개수는 thread_cache_size 시스템 변수로 설정한다.
- 포그라운드 스레드는 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오며, 버퍼나 캐시에 없는 경우에는
- 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리한다.
- MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리하지만 (MyISAM 도 지연된 쓰기가 있지만 일반적인 방식 x)
- InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고,
- 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다
MySQL에서 사용자 스레드와 포그라운드 스레드는 똑같은 의미로 사용된다. 클라이언트가 MySQL 서버에 접속하게 되면 MySQL 서버는 그 클라이언트의 요청을 처리해 줄 스레드를 생성해 그 클라이언트에게 할당한다. 이 스레드는 DBMS의 앞단에서 사용자와 통신하기 때문에 포그라운드 스레드라고 하며, 사용자가 요청한 작업을 처리하기 때문에 사용자 스레드라고도 한다
4.1.2.2 백그라운드 스레드
- MyISAM의 경우에는 별로 해당 사항이 없는 부분이지만 InnoDB는 다음과 같이 여러 가지 작업이 백그라운드로 처리된다
* 인서트 버퍼를 병합하는 스레드
* 로그를 디스크로 기록하는 스레드
* InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
* 데이터를 버퍼로 읽어오는 스레드
* 잠금이나 데드락을 모니터링하는 스레드
- 가장 중요한 것은 로그 스레드와 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리하는 쓰기 스레드 일 것이다
- MySQL 5.5 버전부터 데이터 쓰기 스레드와 데이터 읽기 스레드의 개수를 2개 이상 지정할 수 있게 됐으며,
- innodb_write_io_threads와 innodb_read_io_threads 시스템 변수로 스레드의 개수를 설정한다.
- InnoDB에서도 데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리되기 때문에
- 읽기 스레드는 많이 설정할 필요가 없지만 쓰기 스레드는 아주 많은 작업을 백그라운드로 처리하기 때문에
- 일반적인 내장 디스크를 사용할 때는 2~4 정도, DAS나 SAN과 같은 스토리지를 사용할 때는
- 디스크를 최적으로 사용할 수 있을 만큼 충분히 설정하는 것이 좋다
- 사용자의 요청을 처리하는 도중 데이터의 쓰기 작업은 지연되어 처리될 수 있지만 데이터의 읽기 작업은 절대 지연될 수 없다
- 그래서 일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링 해서 일괄 처리하는 기능이 탑재돼 있으며,
- InnoDB 또한 이러한 방식으로 처리한다.
- 하지만 ISAM은 그렇지 않고 사용자 스레드가 쓰기 작업까지 함께 처리하도록 설계돼 있다.
- 이러한 이유로 InnoDB에서는 INSERT, UPDATE, DELETE 쿼리로 데이터가 변경되는 경우
- 데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 된다.
- 하지만 MyISAM에서 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없다
4.1.3 메모리 할당 및 사용 구조
- MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로켈 메모리 영역으로 구분할 수 있다.
- 글로벌 메모리 영역의 모든 메모리 공간은 MySQL 서버가 시작되면서 운영 체제로부터 할당된다
- 운영체제의 종류에 따라 다르겠지만 요청된 메모리 공간은 100% 할당해줄 수도 있고, 그 공간만큼 예약해두고
- 필요할 때 조금씩 할당해주는 경우도 있다
- 각 운영체제의 메모리 할당 방식은 상당히 복잡하며, MySQL 서버가 사용하는 정확한 메모리의 양을 측정하는 것 또한 쉽지 않다
- 그냥 단순하게 MySQL의 시스템 변수로 설정해 둔 만큼 운영 체제로부터 메모리를 할당받는다고 생각해도 된다
- 글로벌 메모리 영역과 로컬 메모리 영역은 MySQL 서버 내의 존재하는 많은 스레드가 공유해서 아용하는 공간인지 여부에 따라 구분된다
4.1.3.1 글로벌 메모리 영역
- 일반적으로 클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당된다.
- 단, 필요에 따라 2개 이상의 메모리 공간을 할당받을 수도 있지만 클라이언트의 스레드 수와는 무관하며,
- 생성된 글로벌 영역이 N 개라 하더라도 모든 스레드에 의해 공유된다
* 테이블 캐시
* InnoDB 버퍼 풀
* InnoDB 어댑티브 해시 인덱스
* InnoDB 리두 로그 버퍼
4.1.3.2 로컬 메모리 영역
- 세션 메모리 영역이라고도 표현하며, MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역이다.
- 대표적으로 그림 4.3의 커넥션 버퍼와 정렬 버퍼 등이 있다.
- 그림 4.2에서 볼 수 있듯이 클라이언트가 MySQL 서버에 접속하면 MySQL 서버에서는 클라이언트 커넥션으로부터의
- 요청을 처리하기 위해 스레드를 하나씩 할당하게 되는데, 클라이언트 스레드가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역
- 클라이언트와 MySQL 서버와의 커넥션을 세션이라고 하기 때문에 로컬 메모리 영역을 세션 메모리 영역이라고도 표현한다.
- 로컬 메모리는 각 클라이언트 스레드 별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다는 특징이 있다
- 일반적으로 글로벌 메모리 영역의 크기는 주의해서 설정하지만 소트 버퍼와 같은 로컬 메모리 영역은 크게 신경 쓰지 않고 설정하는데,
- 최악의 경우에는 MySQL 서버가 메모리 부족으로 멈춰버릴 수도 있으므로 적절한 메모리 공간을 설정하는 것이 중요하다
- 로컬 메모리 공간의 또 한 가지 중요한 특징은 각 쿼리의 용도별로 필요할 때만 공간이 할당되고
- 필요하지 않은 경우에는 MySQL이 메모리 공간을 할당조차도 하지 않을 수도 있다는 점이다.
- 대표적으로 소트 버퍼나 조인 버퍼와 같은 공간이 그러하다
- 로컬 메모리 공간은 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간도 있고
- 그렇지 않고 않고 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간도 있다
대표적인 로컬 메모리 영역
* 정렬 버퍼
* 조인 버퍼
* 바이너리 로그 캐시
* 네트워크 버퍼
'Books > RealMySQL 8.0' 카테고리의 다른 글
| 3.5 역할(Role) (0) | 2021.10.04 |
|---|---|
| 3.4 권한 (0) | 2021.10.04 |
| 3.3 비밀번호 관리 (0) | 2021.10.03 |
| 3.2 사용자 계정관리 (0) | 2021.10.02 |
