[MySQL] 쿼리 실행 구조 및 쿼리 캐시

쿼리 실행 구조

쿼리 실행구조는 기능별로 다음과 같이 나눠질 수 있습니다.

1) 파서

파서는 사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)으로 분리해 트리 형태의 구조로 만들어 내는 작업을 의미합니다. 쿼리 문장의 문법 오류는 이 과정에서 발견되어 사용자에게 오류 메시지를 전달하게 됩니다.

2) 전처리기

파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인합니다. 각 토큰을 테이블 이름이나 컬럼 이름 또는 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체의 접근 권한 등을 확인하는 과정을 이 단계에서 수행합니다. 실제 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰은 이 단계에서 걸러집니다.

3) 옵티마이저

옵티마이저란 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지 결정하는 역할을 담당하는데, DBMS의 두뇌에 해당한다고 볼 수 있습니다. 어떻게 하면 옵티마이저가 더 나은 선택을 할 수 있게 유도하는 과정 자체가 바로 MySQL 성능 최적화와 관계있다고 볼 수 있습니다.

4) 실행 엔진

옵티마이저가 두뇌라면 실행 엔진과 핸들러는 손과 발에 비유될 수 있습니다.

- 실행 엔진은 핸들러에게 임시 테이블을 만들라고 요청합니다.

- 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청합니다.

- 읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에 요청합니다.

- 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어오라고 핸들러에게 다시 요청합니다.

- 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘깁니다.

즉, 실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행하게 됩니다.

5) 핸들러(스토리지 엔진)

위에서 잠깐 언급한 것처럼 핸들러는 MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할을 담당하게 됩니다. 핸들러는 결국 스토리지 엔진이 됩니다.

복제(Replication)

데이터베이스의 데이터가 갈수록 대용량화되어가는 만큼 확장성(Scalability)은 DBMS에서 아주 중요한 요소입니다. MySQL은 확정성을 위한 다양한 기술을 제공하는데 가장 일반적인 방법이 복제(Replication)입니다. 

MySQL의 복제는 Replication이라고도 하는데, 복제는 2대 이상의 MySQL 서버가 동일한 데이터를 담도록 실시간으로 동기화하는 기술입니다. 일반적으로 MySQL 서버의 복제에서는 마스터는 반드시 1개이며 슬레이브는 1개 이상으로 구성될 수 있습니다.

일반적으로 마스터 또는 슬레이브 가운데 하나의 역할만을 수행하지만 때로는 MySQL 서버 하나가 마스터이면서 슬레이브 역할까지 수행하도록 설정하는 것도 가능합니다. 또한 마스터용 MySQL 프로그램과 슬레이브용 MySQL 프로그램이 정해져 있는 것은 더더욱 아닙니다.

마스터(Master) : MySQL의 바이너리 로그가 활성화되면 어떤 MySQL 서버든 마스터가 될 수 있습니다. 애플리케이션의 입장에서 본다면 마스터 장비는 주로 데이터가 생성 및 변경, 삭제 되는 주체(시작점)이라고 볼 수 있습니다. 일반적으로 MySQL 복제를 구성하는 경우 복제에 참여하는 여러 서버 가운데 변경이 허용되는 서버는 마스터로 한정할 때가 많습니다. 그렇지 않은 경우 복제되는 데이터의 일관성을 보장하기 어려워집니다. 마스터 서버에서 실행되는 DML, DDL 가운데 데이터의 구조나 내용을 변경하는 모든 쿼리 문장은 바이너리 로그에 기록됩니다. 슬레이브 서버에서 변경 내역을 요청하면 마스터 장비는 그 바이너리 로그를 읽어 슬레이브로 넘기게 됩니다.

마스터 장비의 프로세스 가운데 "Binlog dump"라는 스레드가 이 일을 전담하는 스레드이며, 만약 하나의 마스터 서버에 10개의 슬레이브가 연결돼 있다면 "Binlog dump" 스레드는 10개가 표시될 것입니다.

슬레이브(Slave) : 데이터(바이너리 로그)를 받아 올 마스터 장비의 정보(IP주소와 포트 정보 및 접속 계정)를 가지고 있는 경우 슬레이브가 됩니다(마스터나 슬레이브라고 해서 별도의 빌드 옵션이 필요하거나 프로그램을 별도로 설치해야 하는 것은 아닙니다). 마스터 서버가 바이너리 로그를 가지고 있다면 슬레이브 서버는 릴레이 로그를 가지고 있습니다. 일반적으로 마스터와 슬레이브의 데이터를 동일한 상태로 유지하기 위해 슬레이브 서버는 읽기전용으로 설정할 때가 많습니다.

슬레이브 서버의 I/O 스레드는 마스터 서버에 접속해 변경 내역을 요청하고, 받아온 변경 내역을 릴레이 로그에 기록하게 됩니다. 그리고 슬레이브 서버의 SQL 스레드가 릴레이 로그에 기록된 변경 내역을 재실행(Replay)함으로써 슬레이브의 데이터를 마스터와 동일한 상태로 유지합니다. I/O 스레드와 SQL 스레드는 마스터 MySQL에서는 기동되지 않으며, 복제가 설정된 슬레이브 MySQL 서버에서 자동적으로 기동하는 스레드입니다. 

슬레이브 서버의 SQL 스레드가 릴레이 로그에 기록된 변경 내역을 재실행(Replay)함으로써 슬레이브의 데이터를 마스터와 동일한 상태로 유지합니다. I/O 스레드와 SQL 스레드는 마스터 MySQL에서는 기동되지 않으며, 복제가 설정된 슬레이브 MySQL 서버에서 자동적으로 기동하는 스레드입니다.

복제를 사용할 경우, 다음 사항에 대해 주의해야 합니다.

1) 슬레이브는 하나의 마스터만 설정 가능

하나의 마스터에 N개의 슬레이브가 일반적이 형태이며 그 밖에 링(Ring) 형태나 트리(Tree) 형태의 구성도 가능합니다. 그리고 많이 사용하지는 않지만 마스터-마스터 형태의 복제도 사용됩니다. 마스터-마스터 형태에는 사실 2개의 MySQL 서버 모두 마스터이면서 슬레이브가 되는 형태로 구성되는 것입니다.

2) 마스터 슬레이브의 데이터 동기화를 위해 슬레이브는 읽기 전용으로 설정

마스터와 슬레이브로 복제가 구성된 상태에서 데이터는 마스터로 접속해서 변경해야 합니다.

3) 슬레이브 서버용 장비는 마스터와 동일한 사양이 적합

변경이 매우 잦은 MySQL 서버일수록 마스터 서버의 사양보다 슬레이브 서버의 사양이 더 좋아야 마스터에서 동시에 여러 개의 스레드로 실행된 쿼리가 슬레이브에서 지연되지 않고 하나의 스레드로 처리될 수 있습니다. 또한, 슬레이브 서버는 마스터 서버가 다운된 경우 그에 대한 복구 대안으로 사용될 때도 많기 때문에 사양을 동일하게 맞추는 경우가 대부분입니다.

4) 복제가 불필요한 경우에는 바이너리 로그 중지

바이너리 로그를 작성하기 위해 MySQL이 얼마나 많은 자원을 소모하고 성능이 저하되는지 잘모르는 사용자가 많습니다. 바이너리 로그를 안정적으로 기록하기 위해 Gap lock을 유지하고, 매번 트랜잭션이 커밋될 때마다 데이터를 변경시킨 쿼리 문장을 바이너리 로그에 기록해야 합니다. 특정한 경우에 바이너리 로그에 정확히 기록되고 나서야 사용자가 요청한 쿼리 문장이 완료될 때도 있습니다. 바이너리 로그를 기록하는 작업은 특히 AutoCommit이 활성화된 MySQL 서버에서 더 심각한 부하로 나타날 때가 많습니다. 특히 트랜잭션을 지원하지 않는 MyISAM 테이블은 항상 AutoCommit 모드로 작동하기 때문에 InnoDB 테이블보다 바이너리 로그를 기록하는데 더 많은 자원을 사용하게 됩니다.

바이너리로그가 성능에 영향을 미치므로, 성능개선이 이부분도 참고하여야 합니다.

5) 바이너리 로그와 트랜잭션 격리 수준(Isolation level)

바이너리 로그 파일은 어떤 내용이 기록되느냐에 따라 STATEMENT 포맷 방식과 ROW 포맷 방식이 있습니다. STATEMENT 방식은 바이너리 로그 파일에 마스터에서 실행되는 쿼리 문장을 키록하는 방식이며, ROW 포맷은 마스터에서 실행된 쿼리에 의해 변경된 레코드 값을 기록하는 방식입니다.

SQL 기반의 복제는 아무리 데이터의 변경을 많이 유발하는 쿼리라 하더라도 SQL 문장 하나만 슬레이브로 전달되므로 네트워크 트래픽을 많이 유발하지는 않습니다. 하지만 SQL 기반의 복제가 정상적으로 작동하려면 REPEATABLE-READ 이상의 트랜잭션 격리 수준을 사용해야 하며, 그로 인해 InnoDB 테이블에서는 레코드 간의 간격을 잠그는 갭락이나 넥스트 키 락이 필요해집니다. 반면 레코드 기반의 복제는 마스터와 슬레이브 MySQL 서버 간의 네트워크 트래픽을 많이 발생시킬 수는 있지만 READ-COMMITTED 트랜잭션 격리수준에서도 작동할 수 있으며 InnoDB 테이블에서 잠금의 경합은 줄어듭니다.

쿼리 캐시

MySQL 쿼리 캐시에 대한 분석 블로그( https://www.percona.com/blog/2015/08/07/mysql-query-cache-worst-enemy-best-friend/ )

쿼리 캐시는 다른 DBMS에는 없는 MySQL의 독특한 기능 중 하나로서 적절히 설정만 해두면 상당한 성능 향상 효과를 얻을 수 있습니다. 쿼리 캐시는 SQL 문장을 캐시하는 것이 아니라 쿼리의 결과를 메모리에 캐시해 두는 기능입니다. 쿼리 캐시의 구조는 간단히 키와 값의 쌍으로 관리되는 맵(Map)과 같은 데이터 구조로 구현되어 있습니다. 여기서 키를 구성하는 요소 가운데 가장 중요한 것은 쿼리 문장 자체일 것이며, 값은 해당 쿼리의 실행 결과가 될 것입니다.

데이터베이스에서 쿼리를 처리할 때는 상당히 많은 부분의 처리 절차가 있습니다. 이를 전부 무시하고 동일한 쿼리 문장이 요청됐다고 그냥 캐시된 결과를 보내서는 안됩니다. 쿼리 캐시 결과를 내려 보내주기 전에 반드시 다음과 같은 확인 절차를 거쳐야 합니다.

1. 요청된 쿼리 문장이 쿼리 캐시에 존재하는가?

쿼리 문장 비교대상에는 공배기안 탭과 같은 문자도 모두 포함되며, 대소문자까지 완전히 동일해야 같은 쿼리로 인식합니다. 동일한 쿼리가 여러 곳에서 정의되어 사용되면 어느 순간에 각 쿼리가 달라지고(공백이나 개행 문자 하나라도) 그렇게 되면 쿼리 캐시를 공유하지 못하게 됩니다. 결론적으로 애플리케이션의 전체 쿼리 가운데 동일하거나 비슷한 작업을 하는 쿼리는 하나의 쿼리로 통일해 문자열로 관리하는 것이 좋습니다.

2. 해당 사용자가 그 결과를 볼 수 있는 권한을 가지고 있는가?

3. 트랜잭션 내에서 실행된 쿼리인 경우, 그 결과가 가시 범위 내의 트랜잭션에서 만들어진 결과인가? (InnoDB의 경우)

InnoDB에서는 트랜잭션 격리 수준을 준수하기 위해 각 트랜잭션은 자신의 ID보다 ID 값이 큰 트랜잭션에서 변경한 작업이나 쿼리 결과를 참조할 수 없습니다.

4. 쿼리에 의해 만들어진 결과가 캐시하기에 너무 크지 않은가?

    4.1. CURRENT_DATE, SYSDATE, RAND 등과 같이 호출 시점에 따라 결과가 달라지는 요소가 있는가?

    호출될때마다 결과 값이 달라지는 CURRENT_DATE()나 SYSDATE(), 그리고 RAND()와 같은 내장 함수뿐 아니라 NOT DETERMINISTIC으로 정의된 스토어드 함수 등은 사용하지 않는 편이 쿼리 캐시의 효율을 높이는데 도움이 됩니다.

    4.2. 프리페어 스테이트먼트의 경우 변수가 결과에 영향을 미치지 않는가?

    프리페어 스테이트먼트의 경우 쿼리 문장 자체에 변수가 사용되기 때문에 쿼리 문장 자체로 쿼리 캐시를 찾을 수가 없습니다. 프로그램 코드에서는 프리페어 스테이트먼트를 사용했다 하더라도 실제 MySQL 서버에서는 프리페어 스테이트먼트 형태로 실행되지 않습니다. 진정한 프리페어 스테이트먼트를 사용하려면 프로그램 소스코드에서 데이터베이스 커넥션을 생성할 때 특별한 옵션을 사용해야만 합니다. 이를 서버 사이드 프리페어 스테이트먼트라고 합니다.

5. 캐시가 만들어지고 난 이후 해당 데이터가 다른 사용자에 의해 변경되지 않았는가?

- 쿼리 캐시를 무효화하는 작업은 레코드 단위가 아닌 테이블 단위로 처리됩니다. 만약 쿼리 캐시를 1GB로 아주 크게 설정하고, 하나의 테이블로부터 조회된 데이터로 쿼리 캐시를 꽉 채웠다고 해봅시다. 그런데 해당 테이블에 새로운 레코드 한건 INSERT하면 MySQL 서버는 쿼리 캐시에 채워져 있는 1GB의 내용을 모두 제거해야 할 것입니다.

- 더욱이 쿼리 캐시는 절대 여러 스레드에서 동시에 변경할 수 없기 때문에 다른 스레드는 쿼리 캐시 삭제 작업이 완료될 때까지 기다려야 합니다. 많은 사용자가 쿼리 캐시를 위한 메모리 공간은 무조건 크게 설정하면 좋다고 생각하지만 이러한 이유로 적절한 크기 이상으로 설정할 경우 캐시 자체가 부하의 원인이 될 가능성도 있습니다.

여기서 적절한 크기는 일반적으로 32MB~64MB 정도입니다.

6. 쿼리에 의해 만들어진 결과가 캐시하기에 너무 크지 않은가?

- 쿼리 캐시의 전체 크기를 64MB로 설정했는데, 만약 어떤 쿼리 하나가 60MB 정도의 쿼리 결과를 만들어내면 하나의 쿼리 때문에 쿼리 캐시를 다 소모해 버릴 수 있습니다. 이러한 현상을 예방하고자 특정한 크기 미만의 쿼리 결과만 캐시하도록 설정하는 시스템 파라미터가 있습니다. query_cache_limit으로, 값은 1~2MB 미만으로 설정하는 것이 일반적입니다.

- 쿼리가 결과를 만들어내는 데 많은 시간과 자원이 필요하지만 만들어진 결과의 크기가 작을수록 쿼리 캐시를 더 효율적으로 사용할 수 있기 때문에 GROUP BY나 DISTINCT, COUNT() 와 같은 집합 함수의 결과가 쿼리 캐시를 사용하기에 아주 적합합니다.

7. 그 밖에 쿼리 캐시를 사용하지 못하게 만드는 요소가 사용됐는가?

위의 7가지 사항 이외에도 쿼리 캐시를 사용하지 못하게 하는 요소가 있습니다.

- 임시 테이블(Temporary table)에 대한 쿼리

- 사용자 변수의 사용: 쿼리에 사용자 변수를 사용하면 프리페어 스테이트먼트와 동일한 효과가 발생하게 되어 MySQL이 쿼리 캐시를 사용하지 못하게 합니다.

- 컬럼 기반의 권한 설정

- LOCK IN SHARE 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: 쿼리 캐시로 처리된 SELECT 쿼리의 수

Com_sleect: 쿼리 캐시에서 결과를 찾지 못해서 MySQL 서버가 쿼리를 실행한 횟수

쿼리 캐시 히트율(%) = Qcache_hits / (Qcache_hits + Com_select)*100

MySQL 서버에서 쿼리 캐시를 사용하지 않기로 했다면 설정 파일의 설정 파라미터를 다음과 같이 변경하면 됩니다. (이 두 파라미터를 동시에 설정해야 메모리 낭비도 없고, MySQL 서버가 쿼리 캐시를 검색하거나 제거하는 데 필요한 오버해드도 감소)

query_cache_size = 0

query_cache_type = 0

쿼리캐시 설정 관련 내용을 참고하기 좋은 블로그입니다. (http://blog.pages.kr/30)

출처: https://12bme.tistory.com/73?category=682920 [길은 가면, 뒤에 있다.]