MySQL 아키텍처
1. MySQL의 전체 구조
MySQL은 일반 상용 RDBMS에서 제공하는 대부분의 접근법을 모두 지원한다. MySQL 고유의 C API부터 시작해 JDBC와 ODBC, 그리고 .NET의 표준 드라이버를 제공하며, 이러한 드라이버를 이용해 C/C++, PHP, 자바, 펄, 파이썬, 루비나 .NET 및 코볼까지 모든 언어를 이용해 MySQL 서버에서 쿼리를 사용할 수 있게 지원한다.
MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 구분해서 볼 수 있다. 여기서는 MySQL의 쿼리 파서나 옵티마이저 등과 같은 기능을 스토리지 엔진과 구분하고자 위의 그림에서는 "MySQL엔진"과 "스토리지 엔진"으로 구분했다. 그리고 이 둘을 모두 합쳐서 그냥 MySQL 또는 MySQL 서버라고 표현하겠다.
MySQL 엔진
MySQL 엔진은 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리기, 그리고 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룬다. 그리고 성능 향상을 위해 MyISAM의 키 캐시나 InnoDB의 버퍼 풀과 같은 보조 저장소 기능이 포함돼 있다. 또한, MySQL은 표준 SQL(ANSI SQL-92) 문법을 지원하기 때문에 표준 문법에 따라 작성된 쿼리는 타 DBMS와 호환되어 실행될 수 있다.
스토리지 엔진
MySQL 엔진은 요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS의 두뇌에 해당하는 처리를 수행하고, 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지부터 데이터를 읽어오는 부분은 스토리지 엔진이 전담한다. MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다. 다음 예제와 같이 테이블이 사용할 스토리지 엔진을 지정하면 이후 해당 테이블의 모든 읽기 작업이나 변경 작업은 정의된 스토리지 엔진이 처리한다.
mysql > CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;위 예제에서 test_table은 InnoDB 스토리지 엔진을 사용하도록 정의했다. 이제 test_table에 대해 INSERT, UPDATE, DELETE, SELECT, ... 등의 작업이 발생하면 InnoDB 스토리지 엔진이 그러한 처리를 담당하게 된다.
핸들러 API
MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에게 쓰기 또는 읽기를 요청하는데, 이러한 요청을 핸들러(Handler) 요청이라고 하고, 여기서 사용되는 API를 핸들러 API라고 한다. InnoDB 스토리지 엔진 또한 이 핸들러 API를 이용해 MySQL 엔진과 데이터를 주고받는다. 이 핸들러 API를 통해 얼마나 많은 데이터(레코드) 작업이 있었는지는 "SHOW GLOBAL STATUS LIKE 'Handler%'; 명령으로 확인할 수 있다.
2. MySQL 스레딩 구조
MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동하며, 크게 포그라운드(Foregroud) 스레드와 백그라운드(Background) 스레드로 구분할 수 있다.
포그라운드 스레드(클라이언트 스레드)
포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리하는 것이 임무다. 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면, 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시(Thread pool)로 되돌아간다. 이때 이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면 스레드 캐시에 넣지 않고 스레드를 종료시켜 일정 개수의 스레드만 스레드 캐시에 존재하게 한다. 이렇게 스레드의 개수를 일정하게 유지하게 만들어주는 파라미터가 thread_cache_size다.
포그라운드 스레드는 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오며, 버퍼나 캐시에 없는 경우에는 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리한다. MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리하지만(MyISAM도 지연된 쓰기가 있지만 일반적인 방식은 아님). InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다.
백그라운드 스레드
MyISAM의 경우에는 별로 해당 사항이 없는 부분이지만 InnoDB는 여러 가지 작업이 백그라운드로 처리된다. 대표적으로 인서트 버퍼(Insert Buffer)를 병합하는 스레드, 로그를 디스크로 기록하는 스레드, InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드, 데이터를 버퍼로 읽어들이는 스레드, 그리고 기타 여러 가지 잠금이나 데드락을 모니터링하는 스레드가 있다. 이러한 모든 스레드를 총괄하는 메인 스레드도 있다.
모두 중요한 역할을 하지만 그중에서도 가장 중요한 것은 로그 스레드(Log thread)와 버퍼의 데이터를 디스크로 내려 쓰는 작업을 처리하는 쓰기 쓰레드(Write thread)일 것이다. 쓰기 스레드는 윈도우용 MySQL 5.0에서부터 1개 이상을 설정할 수 있었지만 리눅스와 유닉스 계열 MySQL에서는 5.1 버전부터 쓰기 쓰레드의 개수를 1개 이상으로 지정할 수 있게 됐다. 이 쓰기 쓰레드의 개수를 지정하는 파라미터는 innodb_read_io_threads다. InnoDB에서도 데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리되기 때문에 읽기 쓰레드는 많이 설정할 필요가 없지만 아주 많은 작업을 백그라운드로 처리하기 때문에 일반적인 내장 디스크를 사용할 때는 2~4정도, DAS나 SAN과 같은 스토리지를 사용할 때는 4개 이상으로 충분히 설정해 해당 스토리지 장비가 충분히 활용될 수 있게 하는 것이 좋다.
SQL 처리 도중 데이터의 쓰기 작업은 지연(버퍼링)되어 처리될 수 있지만 데이터의 읽기 작업은 절대 지연될 수 없다(사용자가 SELECT 쿼리를 실행했는데, "요청된 SELECT는 10분 뒤에 결과를 돌려주겠다"라고 응답을 보내는 DBMS는 없다). 그래서 일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재돼 있으며 InnoDB 또한 이러한 방식으로 처리한다. 이러한 이유로 InnoDB에서는 INSERT와 UPDATE 그리고 DELETE 쿼리로 데이터가 변경되는 경우, 데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 된다. 하지만 MyISAM에서 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없다.
MySQL에서 사용자 스레드와 포그라운드 스레드는 똑같은 의미로 사용된다. 클라이언트가 MySQL 서버에 접속하게 되면 MySQL 서버는 그 클라이언트의 요청을 처리해 줄 스레드를 생성해 그 클라이언트에 할당해 준다. 이 스레드는 DBMS의 앞단에서 사용자(클라이언트)와 통신하기 때문에 포그라운드 스레드라고 하며, 또한 사용자가 요청한 작업을 처리하기 때문에 사용자 스레드라고도 한다.
3. 메모리 할당 및 사용 구조
MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다. 글로벌 메모리 영역의 모든 메모리 공간은 MySQL 서버가 시작되면서 무조건 운영체제로부터 할당된다. 운영체제의 종류에 따라 다르겠지만 요청된 메모리 공간을 100% 할당해줄 수도 있고, 그 공간만큼 예약해두고 필요할 때 조금씩 할당해주는 경우도 있다. 각 운영체제의 메모리 할당 방식은 상당히 복잡하며, MySQL 서버가 사용하고 있는 정확한 메모리 양을 측정하는 것 또한 쉽지 않다. 그냥 단순하게 MySQL의 파라미터로 설정해 둔 만큼 운영체제로부터 메모리를 할당받는다고 생각하는 것이 좋을 듯 하다.
글로벌 메모리 영역과 로컬 메모리 영역의 차이는 MySQL 서버 내에 존재하는 많은 스레드가 공유해서 사용하는 공간인지 아닌지에 따라 구분되며 각각 다음과 같은 특성이 있다.
글로벌 메모리 영역
일반적으로 클라이언트 스레드의 수와 무관하게 일반적으로는 하나의 메모리 공간만 할당된다. 단, 필요에 따라 2개 이상의 메모리 공간을 할당받을 수도 있지만 클라이언트의 스레드 수와는 무관하며, 생성된 글로벌 영역이 N개라 하더라도 모든 스레드에 의해 공유된다.
로컬 메모리 영역
세션 메모리 영역이라고도 표현하며, MySQl 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 메모리 영역이다. 대표적으로 커넥션 버퍼와 정렬(소드) 버퍼 등이 있다. 클라이언트가 MySQL 서버에 접속하면 MySQL 서버에서는 클라이언트 커넥션으로부터의 요청을 처리하기 위해 스레드를 하나씩 할당하게 되는데, 클라이언트 스레드가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역이라고도 한다. 클라이언트와 MySQL 서버와의 커넥션을 세션이라고도 하기때문에 로컬 메모리 영역을 세션 메모리 영역이라고도 표현한다.
로컬 메모리는 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다는 특징이 있다. 일반적으로 글로벌 메모리 영역의 크기는 주의해서 설정하지만 소트 버퍼와 같은 로컬 메모리 영역은 크게 신경 쓰지 않고 설정하는데, 최악의 경우(가능성은 희박하지만)에는 MySQL 서버가 메모리 부족으로 멈춰 버릴 수도 있으므로 적절한 메모리 공간을 설정하는 것이 중요하다. 로컬 메모리 공간의 또 한 가지 중요한 특징은 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우에는 MySQL이 메모리 공간을 할당조차도 하지 않을수도 있다는 점이다. 대표적으로 소트 버퍼나 조인 버퍼와 같은 공간이 그러하다. 그리고 로컬 메모리 공간은 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간도 있고(커넥션 버퍼나 결과 버퍼) 그렇지 않고 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간(소트 버퍼나 조인 버퍼)도 있다.
4. 플러그인 스토리지 엔진 모델
MySQL의 독특한 구조 중 대표적인 것이 바로 플러그인 모델이다. 플러그인해서 사용할 수 있는 것이 스토리지 엔진만 가능한 것은 아니다. MySQL 5.1부터는 전문 검색 엔진을 위한 검색어 파서(인덱싱할 키워드를 분리해내는 작업)도 플러그인 형태로 개발해서 사용할 수 있다. MySQL은 이미 기본적으로 많은 스토리지 엔진을 가지고 있다. 하지만 이 세상의 수많은 사용자의 요구조건을 만족시키기 위해 기본적으로 제공되는 스토리지 엔진 이외에 부가적인 기능을 더 제공하는 스토리지 엔진이 필요할 수도 있으며, 이러한 요건을 기초로 다룬 전문 개발 회사 또는 직접 스토리지 엔진을 제작하는 것도 가능하다.
MySQL에서 쿼리가 실행되는 과정을 아래 그림과 같이 나눈다면 거의 대부분의 작업이 MySQL 엔진에서 처리되고, 마지막 "데이터 읽기/쓰기"작업만 스토리지 엔진에 의해 처리된다(만약 직접 아주 새로운 용도의 스토리지 엔진을 만든다 하더라도 DBMS의 전체 기능이 아닌 일부분의 기능만 수행하는 엔진을 작성하게 된다는 의미다).
위의 각 처리 영역에서 "데이터 읽기/쓰기" 작업은 거의 대부분의 1건의 레코드 단위로 처리된다(예를 들어 특정 인덱스의 레코드 1건 읽기 또는 마지막 읽었던 레코드의 다음 또는 이전 레코드 읽기와 같이). 그리고 MySQL을 사용하다 보면 "핸들러(Handler)"라는 단어를 자주 접하게 될 것이다. 핸들러라는 단어는 MySQL 서버의 소스코드로부터 넘어온 표현인데, 이는 우리가 매일 타고 다니는 자동차로 비유해 보면 쉽게 이해할 수 있다. 사람이 핸들(운전대)을 이용해 자동차를 운전하듯이, 프로그래밍 언어에서는 어떤 기능을 호출하기 위해 사용하는 운전대와 같은 역할을 하는 객체를 핸들러(또는 핸들러 객체)라고 표현한다. MySQL 서버에서는 MySQL 엔진은 사람 역할을 하고, 각 스토리지 엔진은 자동차 역할을 하게 되는데, MySQL 엔진이 스토리지 엔진을 조정하기 위해 핸들러라는 것을 사용하게 된다.
MySQL에서 핸들러라는 것은 개념적인 내용이라서 완전히 이해하지 못하더라도 크게 문제되진 않자민 최소한 MySQL 엔진이 각 스토리지 엔진에게 데이터를 읽어오거나 저장하도록 명령하려면 핸들러를 꼭 통해야 한다는 점만 기억하자. MySQL 서버의 상태 변수라는 것이 있는데, 이러한 상태 변수 가운데 "Handler_"로 시작하는 것(대표적으로 Handler_read_rnd_next 같은)이 많다는 사실을 알게 될 것이다. 그러면 "Handler_"로 시작하는 상태 변수는 "MySQL 엔진이 각 스토리지 엔진에게 보낸 명령의 횟수를 의미하는 변수"라고 이해하면 된다. MySQl에서 MyISAM이나 InnoDB와같이 다른 스토리지 엔진을 사용하는 테이블에 대해 쿼리를 실행하더라도 MySQL의 처리 내용은 대부분 동일하며, 단순히 "데이터 읽기/쓰기" 영역의 처리만 차이가 있을 뿐이다. 실질적인 GROUP BY나 ORDER BY 등 많은 복잡한 처리는 스토리지 엔진 영역이 아니라 MySQL 엔진의 처리 영역인 "쿼리 실행기"에서 처리된다.
그렇다면 MyISAM이나 InnoDB 스토리지 엔진 가운데 뭘 사용하든 별 차이가 없는 것 아닌가, 라고 생각할 수 있지만 그렇진 않다. 스토리지 엔진 가운데 어떤 걸 사용하느냐에 따라 "데이터 읽기/쓰기" 작업 처리 방식이 많이 달라질 수 있다. 여기서 중요한 내용은 '하나의 쿼리 작업은 여러 하위 작업으로 나뉘는데, 각 하위 작업이 MySQL 엔진 영역에서 처리되는지 아니면 스토리지 엔진 영역에서 처리되는지 구분할 줄 알아야 한다'는 점이다.
이제 설치돼 있는 MySQL 서버(mysqld)에서 지원되는 스토리지 엔진이 어떤 것이 있는지 확인해보자.
mysql > SHOW ENGINES;
+-------------+---------+-------------------------+--------------------+-------------+
| ENGINE | Support | Comment | Transactions | XA | Savepoints
+------------+----------+-------------------------+--------------+-----+-------------+
| ndbcluster | DISABLES | Clustered, fault-to... | YES | NO | NO |
| MRG_MYISAM | YES | Collection of ident... | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage e... | NO | NO | NO |
| CSV | YES | CSV storage engine ... | NO | NO | NO |
| MEMORY | YES | Hash based, stored ... | NO | NO | NO |
| FEDERATED | YES | Federated MySQL sto... | YES | NO | NO |
| ARCHIVE | YES | Archive storage eng... | NO | NO | NO |
| InnoDB | YES | Supports transactio... | YES | YES | YES |
| POSTGRES | YES | Postgres storage en... | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as o... | NO | NO | NO |
+------------+----------+-------------------------+--------------+-----+-------------+Support 칼럼에 표시될 수 있는 값은 아래 4가지다.
YES
MySQl 서버(mysqld)에 해당 스토리지 엔진이 포함돼 있고, 사용 가능으로 활성화된 상태임
DEFAULT
"YES"와 동일한 상태이지만 필수 스토리지 엔진임을 의미함(즉 이 스토리지 엔진이 없으면 MySQL이 시작되지 않을 수도 있음을 의미한다)
NO
현재 MySQL 서버(mysqld)에 포함되지 않았음을 의미함
DISABLED
현재 MySQL 서버(mysqld)에 포함됐지만 파라미터에 의해 비활성화된 상태임
MySQl 서버(mysqld)에 포함되지 않은 스토리지 엔진(Support 칼럼이 NO로 표시되는)을 사용하려면 MySQL 서버를 다시 빌드(컴파일)해야 한다. 하지만 여러분의 MySQL 서버가 적절히 준비만 돼 있다면 플러그인 형태로 빌드된 스토리지 엔진 라이브러리를 내려받아 끼워 넣기만 하면 사용할 수 있다. 또한 플러그인 형태의 스토리지 엔진은 손쉽게 업그레이드 할 수 있다. 스토리지 엔진뿐 아니라 모든 플러그인의 내용은 다음과 같이 확인할 수 있다. 이 명령으로 스토리지 엔진뿐 아니라 전문 검색용 파서와 같은 플러그인도 (만약 설치돼 있다면) 확인할 수 있다.
mysql > SHOW PLUGINS;
+-------------+---------+----------------+----------+
| Name | Stauts | Type | Library |
+-------------+---------+----------------+----------+
| MEMORY | ACTIVE | STORAGE ENGINE | NULL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL |
| MRG_MyISAM | ACTIVE | STORAGE ENGINE | NULL |
+-------------+---------+----------------+----------+5. 쿼리 실행 구조
위의 그림은 쿼리를 실행하는 관점에서 MySQL의 구조를 간략하게 그림으로 표현한 것이며, 다음과 같이 기능별로 나눠볼 수 있다.
파서
파서는 사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)으로 분리해 트리 형태의 구조로 만들어 내는 작업을 의미한다. 쿼리 문장의 기본 문법 오류는 이 과정에서 발견되며 사용자에게 오류 메시지를 전달하게 된다.
전처리기
파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다. 각 토큰을 테이블 이름이나 칼럼 이름 또는 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체의 접근 여부와 같이 객체의 접근권한 등을 확인하는 과정을 이 단계에서 수행한다. 실제 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰은 이 단계에서 걸러진다.
옵티마이저
옵티마이저란 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지 결정하는 역할을 담당하는데, DBMS의 두뇌에 해당한다고 볼 수 있다.
실행 엔진
옵티마이저가 두뇌라면 실행 엔진과 핸들러는 손과 발에 비유할 수 있다. 실행 엔진이 하는 일을 더 쉽게 이해할 수 있게 간단하게 예를 들어서 살펴보자. 옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정했다고 해보자.
- 실행 엔진은 핸들러에게 임시 테이블을 만들라고 요청.
- 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청.
- 읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청.
- 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어오라고 핸들러에게 다시 요청.
- 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김
즉, 실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행한다.
핸들러(스토리지 엔진)
핸들러는 MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어 오는 역할을 담당한다. 핸들러는 결국 스토리지 엔진을 의미하며 MyISAM 테이블을 조작하는 경우에는 핸들러가 MyISAM 스토리지 엔진이 되고, InnoDB 테이블을 조작하는 경우에는 핸들러가 InnoDB 스토리지 엔진이 된다.
6. 복제(Replication)
데이터베이스의 데이터가 갈수록 대용량화돼 가는 것을 생각하면 확장성(Scalability)는 DBMS에서 아주 중요한 요소다. MySQL은 확장성을 위한 다양한 기술을 제공하는데 그중에서 가장 일반적인 방법이 복제(Replication)다. MySQL의 복제는 거의 2000년도부터 제공됐기 때문에 타 DBMS의 복제보다 훨씬 이전부터 제공된 기능이며 또한 그만큼 안정적이다.
MySQL의 복제는 레플리케이션(Replication)이라고도 하는데, 복제는 2대 이상의 MySQL 서버가 동일한 데이터를 담도록 실시간으로 동기화하는 기술이다. 일반적으로 MySQL의 복제에는 INSERT나 UPDATE와 같은 쿼리를 이용해 데이터를 변경할 수 있는 MySQL 서버와 SELECT 쿼리로 데이터를 읽기만 할 수 있는 MySQL 서버로 나뉜다. MySQL에서는 쓰기와 읽기의 역할로 구분해, 전자를 마스터(Master)라고 하고 후자를 슬레이브(Slave)라고 하는데(이러한 역할별 명칭은 DBMS 종류별로 조금씩 차이가 있다다), 일반적으로 MySQL 서버의 복제에서는 마스터는 반드시 1개이며 슬레이브는 1개 이상으로 구성될 수 있다.
하나의 MySQL이 일반적으로는 마스터 또는 슬레이브 가운데 하나의 역할만을 수행하지만 때로는 MySQL 서버 하나가 마스터이면서 슬레이브 역할까지 수행하도록 설정하는 것도 가능하다. 또한 마스터용 MySQL 프로그램과 슬레이브용 MySQL 프로그램이 정해져 있는 것은 더더욱 아니다. 마스터나 슬레이브라는 것은 단지 그 서버의 역할 모델을 지칭하는 것일 뿐이다.
마스터(Master)
기술적으로는 MySQL의 바이너리 로그가 활성화되면 어떤 MySQL 서버든 마스터가 될 수 있다. 애플리케이션의 입장에서 본다면 마스터 장비는 주로 데이터가 생성 및 변경, 삭제되는 주체(시작점)라고 볼 수 있다. 일반적으로 MySQL 복제를 구성하는 경우 복제에 참여하는 여러 서버 가운데 변경이 허용되는 서버는 마스터로 한정할 때가 많다. 그렇지 않은 경우 복제되는 데이터의 일관성을 보장하기 어렵기 때문이다. 마스터 서버에서 실행되는 DML(데이터를 조작하는 문장)과 DDL(스키마를 변경하는 문장) 가운데 데이터의 구조나 내용을 변경하는 모든 쿼리 문장은 바이너리 로그에 기록한다. 슬레이브 서버에서 변경 내역을 요청하면 마스터 장비는 그 바이너리 로그를 읽어 슬레이브로 넘긴다. 마스터 장비의 프로세스 가운데 "Binlog dump"라는 스레드가 이 일을 전담하는 스레드다. 만약 하나의 마스터 서버에 10개의 슬레이브가 연결돼 있다면 "Binlog dump" 스레드는 10개가 표시될 것이다.
슬레이브(Slave)
데이터(바이너리 로그)를 받아 올 마스터 장비의 정보(IP 주소와 포트 정보 및 접속 계정)를 가지고 있는 경우 슬레이브가 된다(마스터나 슬레이브라고 해서 별도의 빌드 옵션이 필요하거나 프로그램을 별도로 설치해야 하는 것은 아니다). 마스터 서버가 바이너리 로그를 가지고 있다면 슬레이브 서버는 릴레이 로그를 가지고 있다. 일반적으로 마스터와 슬레이브의 데이터를 동일한 상태로 유지하기 위해 슬레이브 서버는 읽기 전용으로 설정할 때가 많다. 슬레이브 서버의 I/O 스레드는 마스터 서버에 접속해 변경 내역을 요청하고, 받아온 변경 내역을 릴레이 로그에 기록한다. 그리고 슬레이브 서버의 SQL 스레드가 릴레이 로그에 기록된 변경 내역을 재실행(Replay)함으로써 슬레이브의 데이터를 마스터와 동일한 상태로 유지한다. I/O 스레드(Slave_IO_Thread)와 SQL 스레드(Slave_SQL_Thread)는 마스터 MySQL에서는 기동되지 않으며, 복제가 설정된 슬레이브 MySQL 서버에서 자동적으로 기동하는 스레드다.
복제를 사용할 경우 주로 잘못 생각하거나 간과하는 부분이 있는데 최소한 다음 사항에 대해서는 주의해야 한다.
슬레이브는 하나의 마스터만 설정 가능
MySQL의 복제에서 하나의 슬레이브는 하나의 마스터만 가질 수 있다. 이 제약사항만 피할 수 있다면 다양한 형태의 구성으로 데이터를 복제할 수 있다. 하나의 마스터에 N개의 슬레이브가 일반적인 형태이며 그 밖에 링(Ring) 형태나 트리(Tree) 형태의 구성도 가능하다. 그리고, 많이 사용하진 않지만 마스터-마스터 형태의 복제도 사용된다. 마스터-마스터 형태는 사실 2개의 MySQL 서버 모두 마스터이면서 슬레이브가 되는 형태로 구성되는 것이다.
마스터와 슬레이브의 데이터 동기화를 위해 슬레이브는 읽기 전용으로 설정
마스터와 슬레이브로 복제가 구성된 상태에서는 데이터는 마스터로 접속해서 변경해야 하는데, 사용자 실수나 애플리케이션 오류로 인해 슬레이브로 접속해서 실행하는 경우가 가끔 발생한다. 만약 운 나쁘게도 일부 변경 작업은 마스터에서 실행되고 일부는 슬레이브에서 실행되고 있었다면 데이터 동기화에 상당한 노력이 필요할지도 모른다. 이러한 사용자 실수를 막기 위해 슬레이브는 읽기 전용(read_only 설정 파라미터)으로 설정하는 것이 일반적이다.
슬레이브 서버용 장비는 마스터와 동일한 사양이 적합
많은 사용자가 착각하는 부분이기도 한데, 슬레이브 서버용 장비는 마스터 서버용 장비보다 한 단계 낮은 장비로 선택하려고 할 때가 있다. 하지만 마스터 서버에서 수많은 동시 사용자가 실행한 데이터 변경 쿼리 문장이 슬레이브 서버에서는 하나의 스레드로 모두 처리돼야 한다(이 부분은 지금의 구조상 피해 갈 방법이 없다). 그래서 변경이 매우 잦은 MySQL 서버일수록 마스터 서버의 사양보다 슬레이브 서버의 사양이 더 좋아야 마스터에서 동시에 여러 개의 스레드로 실행된 쿼리가 슬레이브에 지연되지 않고 하나의 스레드로 처리될 수 있다. 하지만 데이터 변경은 데이터 조회보다는 1/10 이하 수준으로 유지되는 것이 일반적이므로 마스터 서버와 슬레이브 서버를 같은 사양으로 유지할 때가 많다. 또한, 슬레이브 서버는 마스터 서버가 다운된 경우 그에 대한 복구 대안으로 사용될 때도 많기 때문에 사양을 동일하게 맞추는 경우가 대부분이다.
복제가 불필요한 경우에는 바이너리 로그 중지
바이너리 로그를 작성하기 위해 MySQL이 얼마나 많은 자원을 소모하고 성능이 저하되는지 잘 모르는 사용자가 많다. 바이너리 로그를 안정적으로 기록하기 위해 갭 락(Gap lock)을 유지하고, 매번 트랜잭션이 커밋될 때마다 데이터를 변경시킨 쿼리 문장을 바이너리 로그에 기록해야 한다(어떤 경우에는 바이너리 로그에 정확히 기록되고 나서야 사용자가 요청한 쿼리 문장이 완료될 때도 있다). 바이너리 로그를 기록하는 작업은 AutoCommit이 활성화된 MySQL 서버에서 더 심각한 부하로 나타낼 때가 많다. 특히 트랜잭션을 지원하지 않는 MyISAM 테이블은 항상 AutoCommit 모드로 작동하기 때문에 InnoDB 테이블보다 바이너리 로그를 기록하는데 더 많은 자원을 사용하게 된다.
바이너리 로그와 트랜잭션 격리 수준(Isolation level)
바이너리 로그 파일은 어떤 내용이 기록되느냐에 따라 STATEMENT 포맷 방식과 ROW 포맷 방식이 있다. STATEMENT 방식은 바이너리 로그 파일에 마스터에서 실행되는 쿼리 문장을 기록하는 방식이며, ROW 포맷은 마스터에서 실행된 쿼리에 의해 변경된 레코드 값을 기록하는 방식이다. MySQL 5.0 이하 버전까지는 STATEMENT 방식만 제공됐었는데, 이 방식에서는 마스터와 슬레이브의 데이터 일치를 위해 REPEATABLE READ 격리 수준만 사용 가능하다. "MySQL 5.0 + STATEMENT 포맷의 바이너리 로그 + REPEATABLE READ 격리 수준" 환경에서는 "INSERT INTO ... SELECT ... FROM ..." 형태의 쿼리 문장을 사용할 때 주의해야 한다.
MySQL의 복제는 마스터에서 처리된 내용이 바이너리 로그로 기록되고, 그 내용이 슬레이브 MySQL 서버로 전달되어 재실행되는 방식으로 처리된다. MySQL 5.0까지는 바이너리 로그에는 마스터 MySQL 서버에서 실행된 SQL 문장이 그대로 기록되고, 슬레이브 MySQL 서버에서 똑같은 SQL 문장이 재실행되는 방식만 지원됐다. MySQL 5.1부터는 MySQL 5.0과 같이 SQL 문장을 기록하는 방법과 마스터에서 변경된 데이터 레코드를 기록하는 두 가지 방법을 제공한다. 바이너리 로그 파일에 SQL 문장을 기록하는 방식을 문장 기반 복제(Statement based replication)라고 하며, 변경된 레코드를 바이너리 로그에 기록하는 방식을 레코드 기반의 복제(Row based replication)라고 한다.
SQL 기반의 복제는 아무리 데이터의 변경을 많이 유발하는 쿼리라 하더라도 SQL 문장 하나만 슬레이브에 전달되므로 네트워크 트래픽을 많이 유발하지는 않는다. 하지만 SQL 기반의 복제가 정상적으로 작동하려면 REPEATABLE-READ 이상의 트랜잭션 격리 수준을 사용해야 하며, 그로 인해 InnoDB 테이블에서는 레코드 간의 간격을 잠그는 갭 락이나 넥스트 키 락이 필요해진다. 반면 레코드 기반의 복제는 마스터와 슬레이브 MySQL 서버 간의 네트워크 트래픽을 많이 발생시킬 수 있지만 READ-COMMITTED 트랜잭션 격리 수준에서도 작동할 수 있으며 InnoDb 테이블에서 잠금의 경합은 줄어든다.
7. 쿼리 캐시
쿼리 캐시(Query Cache)는 타 DBMS에는 없는 MySQL의 독특한 기능 중 하나로서 적절한 설정만 해두면 상당한 성능 향상 효과를 얻을 수 있다. 여러 가지 복잡한 처리 절차와 꽤 큰 비용을 들여 실행된 결과를 쿼리 캐시에 담아 두고, 동일한 쿼리 요청이 왔을 때 간단하게 쿼리 캐시에서 찾아서 바로 결과를 내려 줄 수 있기 때문에 기대 이상의 효과를 거둘 수 있다. 하지만 항상 그렇듯이 이 쿼리 캐시에도 장단점이 있으므로 적절한 조율이 중요하다. 쿼리 캐시는 단어의 의미와는 달리 SQL 문장을 캐시하는 것이 아니라 쿼리의 결과를 메모리에 캐시해 두는 기능이다. 쿼리 캐시의 구조는 간단한 키와 값의 쌍으로 관리되는 맵(Map)과 같은 데이터 구조로 구현돼 있다. 여기서 키를 구성하는 요소 가운데 가장 중요한 것은 쿼리 문장 자체일 것이며, 값은 해당 쿼리의 실행 결과가 될 것이다. 위의 그림에서 쿼리 캐시 안의 작은 표와 같이 생각해볼 수 있겠다.
하지만 데이터베이스에서 쿼리를 처리할 때는 상당히 많은 부분의 쿼리 절차가 있다. 이를 전부 무시하고 동일한 쿼리 문장이 요청됐다고 그냥 캐시된 결과를 보내서는 안 된다. 쿼리 캐시의 결과를 내려보내주기 전에 반드시 다음과 같은 확인 절차를 거쳐야 한다.
- 요청된 쿼리 문장이 쿼리 캐시에 존재하는가?
- 해당 사용자가 그 결과를 볼 수 있는 권한을 가지고 있는가?
- 트랜잭션 내에서 실행된 쿼리인 경우, 그 결과가 가시 범위 내의 트랜잭션에서 만들어진 결과인가?(InnoDB의 경우)
- 쿼리에 사용된 기능(내장 함수나 저장 함수 등)이 캐시돼도 동일한 결과를 보장할 수 있는가?
4.1 CURRENT_DATE, SYSDATE, RAND 등과 같이 호출 시점에 따라 결과가 달라지는 요소가 있는가?
4.2 프리페어 스테이트먼트의 경우 변수가 결과에 영향을 미치지 않는가? - 캐시가 만들어지고 난 이후 해당 데이터가 다른 사용자에 의해 변경되지 않았는가?
- 쿼리에 의해 만들어진 결과가 캐시하기에 너무 크지 않은가?
- 그 밖에 쿼리 캐시를 사용하지 못하게 만드는 요소가 사용됐는가?
물론 이 밖에도 더 세세한 쿼리 캐시 비교 검색 과정이 있지만, 생략하고 우선 이 내용을 조금더 자세히 살펴보자.
요청된 쿼리 문장이 쿼리 캐시에 존재하는가?
위의 그림에서도 알 수 있듯이, 쿼리 캐시는 MySQL의 어떠한 처리보다 앞 단에 위치하며, 캐시된 결과를 찾기 위해 쿼리 문장을 분석해서 복잡한 비교 과정을 거치는 것이 아니기 때문에 아주 간단하고 빠르게 진행된다. 비교 방식은 그냥 요청된 쿼리 문장 자체가 동일한지 여부를 비교하는 것이다. 여기서 비교하는 대상으로는 공백과 탭과 같은 문자까지 모두 포함되며, 대소문자까지 완전히 동일해야 같은 쿼리로 인식한다. 결론적으로 애플리케이션의 전체 쿼리 가운데 동일하거나 비슷한 작업을 하는 쿼리는 하나의 쿼리로 통일해 문자열로 관리하는 것이 좋다. 그렇다고 무리하게 쿼리를 통합하라는 것은 아니며, 적절히 추가 비용이 없이 변경이 가능한 것들은 통합하라는 것이다. 동일한 쿼리가 여러곳에서 정의되어 사용되면 어느 순간에 각 쿼리가 달라지고(공백이나 개형 문자 하나라도) 그렇게 되면 쿼리 캐시를 공유하지 못하게 된다.
해당 사용자가 그 결과를 볼 수 있는 권한을 가지고 있는가?
어떤 사용자가 요청한 쿼리에 대해 동일한 쿼리 결과가 쿼리 캐시에 저장돼 있더라도 이 사용자가 해당 테이블의 읽기 권한이 없다면 쿼리 캐시의 결과를 보여줘서는 안 되기 때문에 이런 확인 작업은 당연한 것이다.
트랜잭션 내에서 실행된 쿼리인 경우 가시 범위 내에 있는 결과인가?
InnoDB의 모든 트랜잭션은 각 트랜잭션 ID를 갖게 된다. 트랜잭션 ID는 트랜잭션이 시작된 시점을 기주능로 순차적으로 증가하는 6바이트 숫자 값이어서 트랜잭션 ID 값을 비교해보면 어느 쪽이 먼저 시작된 트랜잭션인지 구분할 수 있다. InnoDB에서는 트랜잭션 격리 수준을 준수하기 위해 트랜잭션은 자신의 ID보다 ID 값이 큰 트랜잭션에 변경된 작업 내역이나 쿼리 결과는 참조할 수 없다. 이를 트랜잭션의 가시 범위라고 한다. 쿼리 캐시도 그 결과를 만들어낸 트랜잭션의 ID가 가시 범위 내에 있을 때만 사용할 수 있는 것이다.
CURRENT_DATE(), SYSDATE(), RAND() 등과 같이 호출 시점에 따라 결과가 달라지는 요소가 있는가?
SYSDATE()나 RAND() 같은 함수는 동일 사용자가 동일 쿼리를 실행하더라도 호출하는 시간에 따라 결과가 달라지다. 또한 이런 내장 함수뿐 아니라 NOT DETERMINISTIC 옵션으로 정의된 스토어드 루틴이 사용된 쿼리로 시점에 따라 결과가 달라질 가능성이 있다. 그래서 호출될 때마다 결과 값이 달라지는 CURRENT_DATE()나 SYSDATE(), 그리고 RAND()와 같은 내장 함수뿐 아니라 NOT DETERMINISTIC으로 정의된 스토어드 함수 등은 사용하지 않는 편이 쿼리 캐시의 효율을 높이는 데 도움된다.
프리페어 스테이트먼트의 경우 변수가 결과에 영향을 미치지 않는가?
프리페어 스테이트먼트(바인드 변수가 사용된 쿼리)의 경우에는 쿼리 문장 자체에 변수("?")가 사용되기 때문에 쿼리 문장 자체로 쿼리 캐시를 찾을 수가 없다. 여기서 한 가지 주의해야 할 사항은 프로그램 코드에서는 프리페어 스테이트먼트를 사용했다 하더라도 실제 MySQL 서버에서는 프리페어 스테이트먼트 형태로 실행되지 않는다는 점이다. 진정한 프리페어 스테이트먼트를 사용하려면 프로그램의 소스코드에서 데이터베이스 커넥션을 생성할 때 특별한 옵션을 사용해야만 한다. 이를 서버 사이드(Server side) 프리페어 스테이트먼트라고 하는데, MySQL 5.0까지는 프리페어 스테이트먼트로 실행된 쿼리는 쿼리 캐시를 사용할 수 없었지만 MySQl 5.1부터는 이러한 제약이 없어졌다.
캐시가 만들어지고 난 이후 해당 데이터가 다른 사용자에 의해 변경되지 않았는가?
쿼리 결과가 쿼리 캐시에 지정된 이후 데이터가 변경되면 어떻게 될까? 당연히 이미 변경된 데이터를 캐시하는 것은 의미가 없기 때문에 데이터를 제거(무효화, Invalidation)해야 한다. 위에서도 잠깐 언급한 것처럼 쿼리 캐시는 빠른 처리를 위해 아주 단순하게 작동하도록 설계돼 있다. 따라서 쿼리 캐시에 있는 데이터를 무효화하는 작업은 레코드 단위가 아닌 테이블 단위로 처리된다. 만약 쿼리 캐시를 1GB로 아주 크게 설정하고, 하나의 테이블로부터 조회된 데이터로 쿼리 캐시를 꽉 채웠다고 해보자. 그런데 해당 테이블에 새로운 레코드를 한 건 INSERT하면 MySQL 서버는 쿼리 캐시에 채워져 있는 1GB의 내용을 모두 제거해야 할 것이다. 이 작업은 아무리 메모리 작업이라 해도 상당한 시간이 소모될 것이다.
더욱이 쿼리 캐시는 절대 여러 스레드에서 동시에 변경할 수 없기 때문에 다른 스레드는 쿼리 캐시 작업이 완료될 때까지 기다려야 한다. 많은 사용자가 쿼리 캐시를 위한 메모리 공간은 무조건 크게 설정하면 좋다고 생각하지만 이러한 이유로 적절한 크기 이상으로 설정한 경우 캐시 자체가 부하의 원인이 될 가능성도 있다. 여기서 적절한 크기라 함은 일반적으로 32MB ~ 64MB 정도다.
그리고 이와 관련해서 한 가지 더 주의해야 할 사항이 있다. 사용자들이 조회한 횟수를 보여주는 칼럼을 가지고 있는 게시판 테이블이 있다고 해보자. 애플리케이션에서는 항상 이 테이블로부터 SELECT를 실행하기 전에 UPDATE로 조회 수를 증가시켜야 할 것이다. 그러면 어떤 현상이 발생할까? 이 테이블로부터 SELECT한 결과를 쿼리 캐시에 저장했는데, 그 뒤에 실행된 UPDATE 쿼리에 의해 바로 쿼리 캐시에서 삭제돼 버리는 현상이 발생하는 것이다.
즉, 이 테이블은 절대 쿼리 캐시를 사용할 수 없고, 쿼리 캐시에 저장하고 사제하는 오버헤드만 추가하는 꼴이 돼 버리는 것이다. UPDATE와 SELECT의 쿼리 순서를 변경해도 마찬가지다. 이런 경우에는 조회수 칼럼을 다른 테이블로 분리하거나 또는 조회수를 일정한 횟수만큼 누적한 후 한꺼번에 업데이트하는 편이 좋다(일반적으로 후자가 좋다). 테이블 3개를 조인해서 하나의 쿼리로 작성한 경우와, 각 테이블별로 쿼리를 하나씩 쪼개서 작성한 경우를 가정해 보자. 전자의 경우에는 3개의 테이블 가운데 하나만 변경돼도 쿼리 캐시를 사용할 수 없지만 후자의 경우에는 3개의 쿼리 가운데 여전희 2개의 쿼리는 쿼리 캐시를 사용할 수 있다. 그래서 가끔은 복잡하게 하나의 쿼리로 필요한 모든 데이터를 가져오도록 쿼리를 작성하는 것보다 잘게 쪼개는 것이 더 효율적일 수도 있다.
쿼리에 의해 만들어진 결과가 캐시하기에 너무 크기 않은가?
쿼리 캐시의 전체 크기를 64MB로 설정했는데, 만약 어떤 쿼리 하나가 60MB 정도의 쿼리 결과를 만들어내면 하나의 쿼리 때문에 쿼리 캐시를 다 소모해 버릴 수 있다. 이러한 현상을 예방하고자 특정한 크기 미만의 쿼리 결과만 캐시하도록 설정하는 시스템 파라미터가 있다. 이 설정 파라미터의 이름은 query_cache_limit이며, 값은 1~2M 미만으로 설정하는 것이 일반적이다. MySQL은 이 파라미터로 설정된 크기 미만의 쿼리 결과만 캐시한다. 결론적으로 쿼리 결과를 만들어내는 데 많은 시간과 자원이 필요하지만 만들어진 결과의 크기가 작을수록 쿼리 캐시를 더 효율적으로 사용할 수 있기 때문에 GROUP BY나 DISTINCT, 그리고 COUNT()와 같은 집합 함수의 결과가 쿼리 캐시를 사용하기에 아주 적합하다.
그 밖에 쿼리 캐시를 사용하지 못하게 만드는 요소가 사용됐는가?
위에서 언급한 사항 말고도 쿼리 캐시를 사용하지 못하게 하는 요소로는 다음과 같은 것이 있다.
- 임시 테이블(Temporary table)에 대한 쿼리
- 사용자 변수의 사용
쿼리에 사용자 변수를 사용하면 프리페어 스테이트먼트와 동일한 효과가 발생하므로 MySQL이 이 쿼리 캐시를 사용하지 못하게 한다. - 칼럼 기반의 권한 설정
- LOCK IN SHAPE MODE 힌트
SELECT 문장의 끝에 붙여서 조회하는 레코드에 공유 잠금(읽기 락)을 설정하는 쿼리 - FOR UPDATE 힌트
SELECT 문장의 끝에 붙여서 조회하는 레코드에 배타적 잠금(쓰기 락)을 설정하는 쿼리 - UDF(User Defined Function)의 사용
- 독립적인 SELECT 문장이 아닌 일부분의 서브 쿼리
- 스토어드 루틴(Procedure, Function, Trigger)에서 사용된 쿼리(독립적인 쿼리라 하더라도)
- SQL_NO_CACHE 힌트
SELECT 문장에서 SELECT 키워드 뒤에 붙이는 힌트로서, 이 힌트가 사용되면 쿼리 캐시를 사용하지 않는다. 애플리케이션에서 사용되는 쿼리에 의도적으로 이 힌트를 사용하는 경우는 거의 없으며, 대신 쿼리의 성능을 시험할 때 자주 사용한다.
이렇게 쿼리 캐시를 사용하지 못하게 하는 수많은 제약사항이 있음에도 여전히 쿼리 캐시는 그만큼의 효과를 충분히 얻을 수 있는 훌륭한 기능이다. MySQL 서버에서 실행되는 작업은 대부분 MySQL 서버의 상태 변수에 누적되어 기록되기 때문에 아래와 같이 "SHOW GLOBAL STATUS" 명령을 이용해 쿼리 캐시가 얼마나 사용됐고 MySQL 서버에서 SELECT 쿼리가 얼마나 실행됐는지 등에 대한 정보를 확인해 볼 수 있다. 이 상태 값 중에서 Qcache_hits와 Com_select 상태 값을 이용해 쿼리 캐시가 얼마나 효율적으로 사용되고 있는지 조사해 볼수 있다. Qcache_hits는 쿼리 캐시로 처리된 SELECT 쿼리의 수를 의미하며, Com_select는 쿼리 캐시에서 결과를 찾지 못해서 MySQL 서버가 쿼리를 실행한 횟수를 의미한다. 즉, Com_Select 값과 Qcache_hits 값을 더하면 MySQL 서버로 요청된 모든 SELECT 문장의 총 합이 되는 것이다.
mysql > SHOW GLOBAL STATUS LIKE 'Qcache%';
+---------------------------+----------+
| Variable_name | Value |
+---------------------------+----------+
| Qcache_free_blocks | 838 |
| Qcache_free_memory | 64759552 |
| Qcache_hits | 21474569 |
| Qcache_inserts | 68300488 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 48608444 |
| Qcache_queries_in_cache | 1532 |
| Qcache_total_blocks | 3934 |
+---------------------------+----------+
mysql > SHOW GLOBAL STATUS LIKE 'Com_select';
+----------------+------------+
| Variable_name | value |
+----------------+------------+
| Com_select | 123205141 |
+----------------+------------+쿼리 캐시의 히트율이 20% 이상이면 일반적으로 쿼리 캐시를 사용하는 것이 좋다고 이야기하기도 하는 데, 이보다 낮은 수치가 나온 경우도 사용하는 것이 좋을 때가 있다. 쿼리 캐시 히트율은 쿼리 캐시가 얼마나 실행 시간을 줄이고 컴퓨팅 자원을 절약해줬는지를 나타내는 수치가 아니기 때문에 1%의 쿼리 캐시 히트율이라 하더라도 해당 쿼리가 사용하는 자원이나 시간이 아주 크다면 쿼리 캐시는 그만큼 가치 있는 일을 했다고 볼 수 있기 때문이다. MySQL 서버에서 쿼리 캐시를 사용하지 않기로 했다면 컨피규레이션 파일의 설정 파라미터를 다음과 같이 변경하면 된다(이 파라미터를 동시에 설정해야 메모리 낭비도 없고, MySQL 서버가 쿼리 캐시를 검색하거나 제거하는 데 필요한 오버헤드도 줄일 수 있다).
query_cache_size = 0
query_cache_type = 0참고
- Real MySQL
