아키텍처

유석현(SeokHyun Yu)·2023년 12월 2일

SQL

목록 보기

43/45

1. MySQL 엔진 아케틱처

MySQL의 쿼리 작성 및 튜닝 과정을 이해하기 위해서는 먼저 MySQL 엔진의 독특한 구조에 대해 알아보는 것이 중요하다. MySQL 서버는 다른 DBMS와 비교했을 때 구조적으로 상당한 차이점을 가진다. 사용자 입장에서는 이러한 차이가 뚜렷하게 느껴지지 않을 수 있지만, MySQL만의 구조는 다양한 혜택과 함께 일부 도전 과제를 제공한다.

MySQL의 구조가 독특한 이유 중 하나는 MySQL이 다양한 스토리지 엔진을 지원한다는 점이다. 이는 사용자가 데이터의 특성에 맞춰 서로 다른 스토리지 엔진을 선택할 수 있게 해준다. 예를 들어, InnoDB는 트랜잭션 처리와 외래 키 제약 조건을 지원하는 반면, MyISAM은 빠른 읽기와 간단한 테이블 구조에 적합하다.

이러한 다양한 스토리지 엔진 지원은 MySQL에 유연성을 제공하며, 다양한 활용 사례와 성능 요구 사항에 적응할 수 있게 해준다. 그러나 이는 동시에 다른 DBMS에서 발견되지 않는 독특한 튜닝 요구 사항이나 성능 문제를 야기할 수도 있다. 예를 들어, 한 스토리지 엔진에서 효율적인 쿼리가 다른 엔진에서는 최적의 성능을 내지 못할 수 있다.

따라서 MySQL을 사용할 때는 이러한 스토리지 엔진의 특성을 이해하고, 적절한 엔진을 선택하는 것이 중요하다. 또한, 서버 설정, 쿼리 최적화 및 인덱싱 전략을 스토리지 엔진의 특성에 맞게 조정해야 한다. 이렇게 함으로써 MySQL의 장점을 최대한 활용하고, 특정 엔진 관련 문제를 최소화할 수 있다.

MySQL은 다양한 프로그래밍 언어로부터의 접근을 지원하는 범용 데이터베이스 관리 시스템(DBMS)이다. MySQL 서버는 크게 두 부분, 즉 MySQL 엔진과 스토리지 엔진으로 구분할 수 있다.

MySQL 엔진: 이 부분은 클라이언트로부터의 쿼리 요청을 처리하는 핵심 구성요소이다. MySQL 엔진에는 다음과 같은 중요한 요소들이 포함된다:

커넥션 핸들러: 클라이언트의 접속 및 쿼리 요청을 처리한다.
SQL 파서: 클라이언트로부터 받은 SQL 문장을 분석한다.
실행 계획 처리기: SQL 쿼리에 최적화된 실행 계획을 세운다.
옵티마이저: 쿼리의 성능을 최적화한다.

스토리지 엔진: 실제 데이터의 디스크 저장이나 디스크로부터의 데이터 읽기는 스토리지 엔진이 담당한다. MySQL 서버에서는 하나의 MySQL 엔진이 있지만, 여러 스토리지 엔진을 동시에 사용할 수 있다. 테이블을 생성할 때 해당 테이블이 사용할 스토리지 엔진을 지정하면, 해당 테이블의 모든 읽기 및 변경 작업은 지정된 스토리지 엔진이 처리한다. 그리고 각 스토리지 엔진은 성능 향상을 위한 기능을 내장하고 있으며, MyISAM 스토리지 엔진의 키캐시나 InnoDB 스토리지 엔진의 InnoDB 버퍼풀이 그 예이다.

MySQL 엔진이 데이터를 쓰거나 읽을 때는 스토리지 엔진에 쓰기 또는 읽기를 요청한다. 이러한 요청을 '핸들러 요청'이라고 하며, 이때 사용되는 API를 '핸들러 API'라고 한다. InnoDB 스토리지 엔진도 이 핸들러 API를 통해 MySQL 엔진과 데이터를 주고받는다.

이러한 MySQL의 구조는 그 유연성과 확장성을 제공하며, 다양한 요구 사항과 환경에 맞게 데이터베이스 시스템을 최적화할 수 있는 기회를 제공한다.

2. MySQL 스레딩 구조

MySQL 서버는 프로세스 기반이 아닌 스레드 기반으로 작동하며, 이를 통해 더 효율적인 자원 관리와 빠른 응답 시간을 제공한다. MySQL 서버 내의 스레드는 크게 '포그라운드 스레드'와 '백그라운드 스레드'로 구분할 수 있다.

포그라운드 스레드(클라이언트 스레드)
포그라운드 스레드는 MySQL 서버에 접속된 각 클라이언트마다 최소한 하나씩 존재한다. 이 스레드들은 주로 클라이언트 사용자가 요청하는 SQL 문장을 처리한다. 사용자가 작업을 마치고 연결을 종료하면, 해당 스레드는 스레드 캐시(Thread Cache)로 돌아간다. 스레드 캐시에는 thread_cache_size 시스템 변수로 설정된 개수만큼의 스레드가 유지된다. 포그라운드 스레드는 데이터를 InnoDB 버퍼나 캐시로부터 가져오며, 필요한 경우 디스크의 데이터나 핸들러 파일로부터 데이터를 읽어와 작업을 처리한다.

백그라운드 스레드
백그라운드 스레드는 주로 InnoDB와 같은 스토리지 엔진에서 다양한 작업을 처리한다. 이 작업에는 인서트 버퍼 병합, 로그 기록, 버퍼 풀 데이터 기록, 데이터 읽기, 잠금 및 데드락 모니터링 등이 포함된다. 가장 중요한 백그라운드 스레드는 로그 스레드와 디스크로 데이터를 기록하는 쓰기 스레드이다. MySQL 5.5 버전부터는 쓰기 및 읽기 스레드의 개수를 여러 개로 지정할 수 있으며, innodb_write_io_threads와 innodb_read_io_threads 시스템 변수로 이를 설정할 수 있다. InnoDB는 쓰기 작업을 버퍼링하여 일괄 처리하는 반면, MyISAM은 사용자 스레드가 쓰기 작업을 직접 처리한다.

이러한 스레드 기반의 구조는 MySQL 서버가 다양한 작업을 효율적으로 처리하도록 돕는다. 포그라운드 스레드는 클라이언트의 요청에 직접 응답하고, 백그라운드 스레드는 서버의 데이터 관리 및 유지 관리 작업을 담당한다. 이 구조는 MySQL의 성능과 확장성을 높이는 데 기여한다.

3. 메모리 할당 및 사용 구조

MySQL 서버의 메모리 구조는 크게 '글로벌 메모리 영역'과 '로컬(세션) 메모리 영역'으로 나누어진다. 이 두 영역은 서버 내의 스레드가 공유하는지 여부에 따라 구분된다.

글로벌 메모리 영역

글로벌 메모리 영역은 MySQL 서버 시작 시 운영체제로부터 할당된다. 이 영역의 메모리 할당은 운영체제의 종류에 따라 다르며, MySQL 서버가 사용하는 정확한 메모리 양을 측정하는 것은 쉽지 않다.
이 영역은 클라이언트 스레드의 수와 무관하게 고정된 메모리 공간을 가지며, 모든 스레드에 의해 공유된다.
글로벌 메모리 영역에는 테이블 캐시, InnoDB 버퍼 풀, InnoDB 어댑티브 해시 인덱스, InnoDB 리두 로그 버퍼 등이 포함된다.

로컬 메모리 영역

로컬 메모리 영역은 세션 메모리 영역이라고도 하며, MySQL 서버상의 클라이언트 스레드가 쿼리 처리에 사용하는 메모리 영역이다.
각 클라이언트 스레드는 독립적으로 할당된 로컬 메모리를 가지며, 이는 절대 공유되지 않는다.
로컬 메모리 영역에는 정렬 버퍼, 조인 버퍼, 바이너리 로그 캐시, 네트워크 버퍼 등이 포함되며, 쿼리의 용도에 따라 필요할 때만 메모리 공간이 할당된다.
특히 소트 버퍼나 조인 버퍼는 쿼리 실행 시점에만 할당되며, 사용 후에는 해제된다.

4. 쿼리 실행 구조

MySQL 서버의 쿼리 처리 과정은 여러 단계로 구성되어 있으며, 각 단계는 쿼리의 효율적인 처리를 위해 중요한 역할을 한다.

쿼리 파서

쿼리 파서의 역할은 사용자로부터 들어온 쿼리 문장을 토큰(어휘나 기호의 최소 단위)으로 분리하고 구조화하는 것이다. 쿼리 문장의 기본 문법 오류는 이 단계에서 발견되며, 오류가 발견될 경우 사용자에게 오류 메시지가 전달된다.

전처리기

파서에 의해 생성된 파서 트리를 기반으로 전처리기는 구조적인 문제점을 확인한다. 이 단계에서는 토큰을 테이블 이름, 칼럼 이름, 내장 함수 등의 객체에 매핑하고, 해당 객체의 존재 여부와 접근 권한을 검증한다.

옵티마이저

옵티마이저는 쿼리의 실행 계획을 세우고, 쿼리를 가장 효율적으로 처리하는 방법을 결정한다. 이 단계는 DBMS의 '두뇌'에 해당하며, 어떻게 하면 옵티마이저가 더 나은 선택을 할 수 있도록 유도할지에 대한 지식이 중요하다.

실행 엔진

실행 엔진은 쿼리의 실행 계획을 실제로 처리하는 역할을 한다. 예를 들어, GROUP BY 처리를 위해 임시 테이블을 사용하기로 결정한 경우, 실행 엔진은 임시 테이블 생성, WHERE 절에 맞는 레코드 읽기, 임시 테이블에 데이터 저장, 임시 테이블에서 데이터 읽기 등의 과정을 핸들러에게 요청한다.

핸들러(스토리지 엔진)

핸들러는 MySQL 서버의 가장 밑단에서 실행 엔진의 요청에 따라 데이터를 디스크에 저장하거나 디스크로부터 읽어오는 역할을 담당한다. MyISAM과 InnoDB 테이블을 조작하는 경우, 각각 MyISAM 스토리지 엔진과 InnoDB 스토리지 엔진이 핸들러의 역할을 수행한다.

5. 쿼리 캐시

MySQL 서버에서 쿼리 캐시(Query Cache)의 역할은 웹 기반 응용 프로그램에 빠른 응답을 제공하는 것이었다. 쿼리 캐시는 SQL 쿼리의 실행 결과를 메모리에 저장하여, 동일한 쿼리가 재실행될 때 테이블을 다시 읽지 않고 즉시 결과를 반환함으로써 성능을 향상시켰다. 이는 특히 데이터 변경이 거의 없고 주로 읽기 작업을 수행하는 서비스에서 유용했다.

그러나 쿼리 캐시의 단점도 있었다. 테이블의 데이터가 변경될 경우, 변경된 테이블과 관련된 캐시된 결과들은 모두 무효화되어야 했다. 이러한 무효화 과정은 심각한 동시 처리 성능 저하를 유발했다. 또한, MySQL 서버의 발전 과정에서 쿼리 캐시는 동시 처리 성능 저하 및 다양한 버그의 원인이 되었다.

이러한 이유로, MySQL 8.0 버전에서는 쿼리 캐시 기능이 완전히 제거되었으며, 관련된 시스템 변수들도 삭제되었다. 쿼리 캐시의 제거는 데이터 변경이 적고 읽기 작업이 많은 특정 환경에서만 효과적이었던 기능을 고려했을 때, MySQL 서버의 전반적인 성능 및 안정성 향상을 위한 올바른 결정이라고 평가된다. 이러한 변경을 통해 MySQL은 더욱 신뢰성 높고 성능이 개선된 데이터베이스 관리 시스템으로 발전하게 되었다.

6. 스레드 풀

MySQL 서버, 특히 엔터프라이즈 에디션에서 스레드 풀(Thread Pool) 기능은 성능 최적화를 위한 중요한 도구이다. MySQL 커뮤니티 에디션에서는 기본적으로 이 기능을 지원하지 않지만, Percona Server의 스레드 풀 플러그인을 MySQL 커뮤니티 에디션 서버에 설치하여 사용할 수 있다.

스레드 풀의 기본 목적은 사용자의 요청을 처리하는 스레드의 수를 제한하여, 요청이 많아도 MySQL 서버의 CPU가 제한된 수의 스레드 처리에만 집중할 수 있게 함으로써 서버의 자원 사용 효율성을 높이는 것이다. 이는 서버의 성능을 개선하는 데 도움이 될 수 있지만, 스레드 풀만으로 성능이 두 배로 향상된다고 기대하는 것은 현실적이지 않다. 스레드 풀의 효과는 서비스의 특성과 서버 환경에 따라 다를 수 있으며, 때로는 쿼리 처리가 느려지는 경우도 발생할 수 있다.

Percona Server의 스레드 풀은 일반적으로 CPU 코어의 개수에 맞춰 스레드 그룹을 생성한다. 스레드 그룹의 수는 thread_pool_size 시스템 변수를 통해 조정할 수 있으며, 일반적으로는 CPU 코어의 개수와 일치시키는 것이 좋다. 스레드 풀은 요청이 들어올 때 해당 요청을 처리하는 방식을 결정하며, 모든 스레드가 작업 중일 때는 새로운 작업 스레드를 추가하거나 기존 작업 스레드가 처리를 완료할 때까지 기다린다.

스레드 풀은 응답 시간이 중요한 서비스에서는 thread_pool_stall_limit 시스템 변수를 조절하여 최적의 성능을 낼 수 있도록 설정해야 한다. 이 변수는 스레드 풀이 새로운 요청을 처리하기 전에 기다리는 시간을 결정한다. 하지만 이 값을 너무 낮게 설정하면 스레드 풀의 효과를 충분히 활용하지 못할 수 있으므로 적절한 값으로 설정하는 것이 중요하다.

또한, Percona Server의 스레드 풀 플러그인은 우선순위 큐를 이용하여 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공한다. 이 기능은 특정 트랜잭션의 처리를 빠르게 완료함으로써 전체 시스템의 처리 성능을 향상시킬 수 있다.

이처럼 스레드 풀은 MySQL 서버의 성능을 최적화하는 데 유용한 도구이지만, 서비스의 특성과 서버 환경을 고려하여 적절하게 설정하고 사용하는 것이 중요하다.

7. InnoDB

MySQL에서 InnoDB 스토리지 엔진은 그 사용의 보편성과 더불어 데이터 관리의 효율성과 무결성을 제공하는 핵심 요소이다. InnoDB는 트랜잭션 지원, 로우 레벨 락킹, 외래 키 제약과 같은 기능을 제공하며, ACID(원자성, 일관성, 고립성, 지속성) 속성을 지원하여 안정적인 데이터 관리를 가능하게 한다.

InnoDB의 가장 중요한 특징 중 하나는 '버퍼 풀(Buffer Pool)'이다. 버퍼 풀은 InnoDB 테이블의 데이터와 인덱스를 메모리에 캐싱함으로써, 디스크 I/O를 줄이고 전반적인 데이터베이스 성능을 향상시키는 중요한 역할을 한다.

버퍼 풀의 주요 작동 방식은 다음과 같다:

데이터 읽기: 데이터를 읽을 때, InnoDB는 먼저 버퍼 풀에서 해당 데이터를 찾는다. 버퍼 풀에 데이터가 있으면(캐시 히트) 빠르게 접근할 수 있으며, 없다면(캐시 미스) 디스크에서 데이터를 읽어 버퍼 풀에 저장한다.
데이터 쓰기: 변경된 데이터(더티 페이지)는 먼저 버퍼 풀에 쓰여지고, 이후 정기적으로 디스크에 반영된다. 이 접근 방식은 성능 최적화와 데이터 무결성 보장을 동시에 수행한다.
LRU 알고리즘: 제한된 크기를 가진 버퍼 풀은 LRU 알고리즘을 사용해 메모리를 관리한다. 이는 오래된 데이터를 제거하고 새 데이터를 위한 공간을 마련한다.
동시성 관리: InnoDB는 다수의 스레드가 버퍼 풀에 동시에 접근할 수 있도록 동시성을 관리한다. 이는 락과 래치를 활용해 실현된다.

InnoDB의 버퍼 풀은 MySQL 데이터베이스의 성능과 안정성 향상에 결정적인 역할을 한다. 데이터베이스 관리자는 시스템의 요구 사항에 맞게 버퍼 풀의 크기와 동작을 조절함으로써 데이터베이스 시스템을 최적화할 수 있다.

8. MyISAM

MySQL에서 MyISAM 스토리지 엔진은 오래된 기술이지만, 특정 상황에서는 여전히 유용할 수 있다. MyISAM의 가장 두드러진 특징 중 하나는 키 캐시(Key Cache) 기능을 사용한다는 것이다. 이 기능은 MyISAM 테이블의 인덱스 정보를 메모리에 캐싱하여, 데이터베이스가 디스크 대신 메모리에서 인덱스에 빠르게 접근할 수 있게 해 준다. 이는 특히 읽기 작업이 빈번한 경우 쿼리 처리 속도를 획기적으로 향상시킨다.

키 캐시의 크기는 key_buffer_size 매개변수를 통해 조절할 수 있다. 적절한 크기 설정은 시스템 메모리와 작업 부하에 따라 달라지며, 인덱스 적중률과 전체 성능에 큰 영향을 미친다. 너무 작은 설정은 캐시 효율을 떨어뜨리고, 너무 큰 설정은 다른 메모리 리소스에 영향을 줄 수 있다.

그러나 MyISAM은 몇 가지 중요한 제한 사항을 가진다. 특히, 트랜잭션 지원 부재와 테이블 레벨 락은 동시에 많은 쓰기 작업이 발생하는 환경에서 성능 저하를 유발할 수 있다. 또한, 시스템 오류 시 데이터 복구가 어렵다는 점도 큰 단점이다.

이런 이유로 현대의 응용 프로그램 개발에서는 더 안정적이고 트랜잭션을 지원하는 InnoDB 스토리지 엔진이 선호된다. 그럼에도 불구하고, 읽기 중심의 작업이나 트랜잭션 처리가 중요하지 않은 특정 상황에서는 MyISAM이 여전히 유용하게 사용될 수 있다. MyISAM은 이러한 조건에 맞는 환경에서 뛰어난 읽기 성능을 제공할 수 있다.

9. MySQL 로그 파일

MySQL 서버에서는 다양한 로그 파일을 사용하여 데이터베이스 활동을 기록한다. 이 로그 파일들은 데이터베이스 관리와 성능 모니터링에 중요한 역할을 한다.

에러 로그 (Error Log)
- 에러 로그는 MySQL 서버가 시작하거나 종료될 때, 문제가 발생했을 때 기록된다.
- 이 로그에는 시스템 문제, 경고, 오류 메시지 등이 포함되어 있으며, 문제 해결과 시스템 모니터링에 유용하다.
- 위치는 hostname.err 파일로 저장되며, my.cnf 또는 my.ini에서 설정을 통해 변경할 수 있다.
제너럴 쿼리 로그 (General Query Log)
- 제너럴 쿼리 로그는 서버에 전송된 모든 클라이언트의 연결 및 쿼리 정보를 기록한다.
- 데이터베이스 활동을 추적하는 데 유용하지만, 모든 쿼리를 기록하므로 파일 크기가 커지고 성능에 영향을 줄 수 있다.
슬로우 쿼리 로그 (Slow Query Log)
- 슬로우 쿼리 로그는 설정된 임계값보다 오래 걸리는 쿼리를 기록한다.
- 비효율적인 쿼리를 찾아 데이터베이스 성능 최적화에 도움을 준다.
- 임계값은 long_query_time 변수를 통해 설정되며, 초 단위로 설정된다.