[Real MySQL 4장] 아키텍처

MySQL 아키텍처

프로그래밍 API를 통해 MySQL에 요청을 보낸다.

MySQL은 요청 받은 쿼리를 분석해서 최적의 실행계획을 수립 및 스토리지 엔진에게 명령한다.

받은 명령대로 스토리지 엔진이 디스크에 접근하여 데이터를 입출력한다.

쿼리 실행 구조

1. 쿼리 파서

   요청으로 들어온 쿼리를 MySQL이 인식할 수 있는 최소 단위인 토큰으로 분리해 트리 형태의 구조로 만드는 역할을 한다.

   쿼리 문장의 기본 문법 오류는 여기서 발견된다.

2. 전처리기

   각 토큰을 테이블, 컬럼 등과 매핑해 해당 객체의 존재 여부와 접근 권한을 확인한다.

3. 옵티마이저

   요청이 들어온 쿼리를 가장 효율적으로 실행할 수 있는 실행 계획을 수립한다.

4. 실행 엔진

   옵티마이저가 수립한 실행 계획대로 핸들러에게 명령을 내린다.

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

   실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 읽어오는 역할을 담당한다.

쿼리 캐시

쿼리 캐시는 성능 자체는 빨랐으나, 테이블의 데이터가 변경되면 변경된 테이블에 관련된 캐시들을 삭제해야 했기 때문에 성능 저하를 유발하여 MySQL 8.0 이후로는 제거됨

스레딩 구조

MySQL 서버는 스레드 기반으로 작동하며, 포그라운드 스레드와 백그라운드 스레드로 구분할 수 있다.

• 포그라운드 스레드(클라이언트 스레드) MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 주로 각 클라이언트의 사용자가 요청하는 쿼리 문장을 처리한다. 포그라운드 스레드는 데이터를 버퍼나 캐시에서 읽어와서 작업을 처리한다.
  
• 백그라운드 스레드 여러 작업들이 백그라운드 스레드로 처리된다. 아래는 그 중 몇 가지 작업들이다.
  • 로그를 디스크로 기록하는 스레드
  • InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
  • 잠금이나 데드락을 모니터링하는 스레드

스레드 풀

스레드가 너무 많아지면 성능이 감소한다. 성능이 떨어지는 데는 아래와 같은 원인들이 있다.

• 스레드가 많아질수록 더 많은 컨텍스트 스위칭이 발생하게 되어 성능이 감소한다.
• 여러 스레드가 동시에 같은 자원에 접근하려고 할 때 리소스 경합이 발생하며 대기 시간이 증가하고 성능이 감소한다.

→ 스레드 풀은 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이 목적이다.

MySQL 커뮤니티 에디션에서 스레드 풀 기능을 쓰려면 스레드 풀 플러그인(Percona Server)을 추가로 설치해야한다.

스레드 풀을 도입한다고 무조건 성능이 향상되는 것이 아니라, 적절한 세팅이 중요하다.

스레드 풀 동작 과정

• 서버는 요청이 들어올 때마다 새 스레드를 생성하는 대신, 스레드 풀의 스레드에 작업을 할당한다.
• 작업들은 큐에 저장되며, 스레드 풀에 놀고있는 스레드가 있으면 큐에서 작업을 빼서 실행한다.
• 예를 들어 스레드 풀의 스레드 개수를 10개로 정했고, 100개의 요청이 한 번에 들어왔다고 하면, 처음 10개의 요청에 대해서는 스레드를 할당하고, 나머지는 큐에다 넣어서 대기시킨다.

InnoDB

InnoDB는 MySQL에서 사용 가능한 스토리지 엔진 중 레코드 기반 잠금을 제공한다.

→ 높은 동시성 처리가 가능하고, 안정적이며, 성능이 우수하다.

InnoDB 구조

InnoDB의 특징

1. PK에 의한 클러스터링

   InnoDB의 모든 테이블은 PK값의 순서대로 디스크에 저장된다.

   → PK가 클러스터링 인덱스이므로 PK를 이용한 검색은 빠르게 처리된다.

2. 외래 키 지원

   왜래 키는 부모 & 자식 테이블 모두 인덱스 생성이 필요하고, 변경 시 부모 & 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하다.

   → 위 과정에서 잠금이 여러 테이블로 전파되어 데드락 발생할 수 있다.

   foreign_key_checks 시스템 변수를 OFF로 설정해서 왜래 키 체크를 중지할 수 있다.

3. MVCC(Multi Version Concurrency Control)

   InnoDB는 MVCC 기술을 이용하여 잠금을 사용하지 않는 일관된 읽기를 제공하는데, 이를 위해 언두 로그를 이용한다.

   • 데이터 변경 처리 과정

     1. 업데이트 쿼리 실행

     2. 버퍼 풀의 데이터를 즉시 새 데이터로 변경

     3. 기존 데이터는 언두 로그에 복사

     4. COMMIT 명령 실행 시 지금의 상태를 영구적으로 적용

     5. ROLLBACK 명령 실행 시 언두 영역의 데이터를 버퍼 풀로 복구하고 언두 영역의 데이터 삭제

        디스크의 데이터 파일에는 시점에 따라 업데이트 여부가 다르다.

        만약 COMMIT, ROLLBACK이 안된 상태에서 작업중인 레코드를 조회하면 격리 수준에 따라 결과가 다르다.

     • ex1) READ_UNCOMMITTED: InnoDB 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환한다.

     • ex2) READ_COMMITTED or 그 이상의 격리 수준: 언두 영역의 데이터를 반환한다.

       → 이러한 과정을 MVCC라고 표현한다.

   참고로 언두 영역의 데이터는 언두 영역의 데이터를 필요로 하는 트랜잭션이 없을 때 삭제된다.

4. 잠금 없는 일관된 읽기(Non-Locking Consistent Read)

   InnoDB는 MVCC 기술을 통해 잠금을 걸지 않고 읽기 작업을 수행하기 때문에 다른 트랜잭션의 잠금을 기다리지 않고 읽기 작업이 가능하다.

   일관된 읽기를 위해 언두 로그를 삭제하지 못하고 계속 유지해야 하기 때문에 오랜 시간 동안 활성 상태인 트랜잭션으로 인해 문제가 발생할 수 있다.

   → 트랜잭션이 시작됐다면 빠르게 롤백이나 커밋을 하는 것이 좋음

5. 자동 데드락 감지

   InnoDB는 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프 형태로 관리한다.

   데드락 감지 스레드가 주기적으로 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그 중 언두 레코드를 적게 가진 트랜잭션을 강제 종료시킨다.

   • 언두 레코드를 적게 가졌다는 것은 롤백을 해도 언두 처리를 해야 할 내용이 적다는 뜻이다. → 그러므로 언두 레코드를 적게 가진 트랜잭션은 강제 종료 시 부하를 덜 유발한다.
     

   데드락 감지 스레드는 일반적으로는 크게 부담되는 작업은 아니지만, 동시 처리 스레드가 매우 많아지거나 각 트랜잭션이 가진 잠금의 개수가 많아지면 느려진다.

   데드락 감지 스레드는 잠금 목록을 검사하기 위해 잠금 상태가 변경되지 않도록 새로운 잠금을 건다.

   → 데드락 감지 스레드가 느려지면 서비스 쿼리를 처리 중인 스레드도 느려진다.

   innodb_deadlock_detect 시스템 변수를 OFF로 설정하면 데드락 감지 스레드가 작동하지 않는다.

   하지만, 그러면 데드락 발생 시 교착상태 발생하게 된다.

   innodb_lock_wait_timeout 시스템 변수를 설정하면 일정 시간이 지났을 때 요청을 실패하게 할 수 있다.

   → innodb_deadlock_detect 시스템 변수를 OFF로 설정하면 innodb_lock_wait_timeout 시스템 변수는 기본값인 50초 보다 훨씬 낮게 설정하는 것이 좋다.

   

6. 자동화된 장애 복구

   InnoDB에는 손실이나 장애로부터 데이터를 보호하기 위한 여러 가지 메커니즘이 탑재되어 있다.

   자동으로 복구가 안되는 경우는 innodb_force_recovery 시스템 변수를 설정하여 복구할 수 있다.

7. InnoDB 버퍼 풀

   버퍼 풀은 디스크의 데이터 파일이나 인덱스 정보를 캐시해 두는 메모리 공간을 말한다.

   또한, 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이 한다.

   일반적으로 데이터를 변경하는 쿼리는 데이터 파일의 이곳저곳에 위치한 레코드를 변경하기 때문에 랜덤한 디스크 작업을 발생 시킨다.

   → 버퍼 풀이 이러한 변경된 데이터를 모아서 처리하면 랜덤한 디스크 작업의 횟수를 줄일 수 있다.

   • 버퍼 풀의 크기 설정 InnoDB 버퍼 풀의 크기를 적절히 작은 값으로 설정해서 조금씩 상황을 봐 가며 증가시키는 방법이 좋다. 버퍼 풀의 크기 변경은 서버가 한가한 시점을 골라 진행하는 것이 좋다. 버퍼 풀의 크기를 늘리는 것은 영향이 적지만, 줄이는 것은 영향이 매우 크므로 지양하자.
     
   • 버퍼 풀의 구조 InnoDB는 버퍼 풀을 페이지 단위로 쪼개어 필요한 데이터를 가져와서 각 페이지에 저장한다. 페이지 조각을 관리하기 위해 Free 리스트, LRU 리스트, Flush 리스트라는 3가지 자료 구조를 관리한다.
     • Free 리스트 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록을 저장하는 자료구조이다.
       
     • LRU 리스트 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 버퍼풀에 유지해서 디스크 읽기를 최소화 하기 위해 필요한 자료구조다. 처음 들어온 데이터는 old 리스트(LRU)에 들어가고, 한번 더 읽으면 new 리스트(MRU)에 들어간다. → 이렇게 New와 Old를 나누는 이유는 한번에 일회성 데이터가 많이 들어와서 기존에 자주 사용하던 데이터가 버퍼 풀에서 지워지는 것을 막기 위해서이다.
       
     • 플러시 리스트 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(더티 페이지)의 변경 시점 기준의 페이지 목록을 관리하는 자료구조다. 디스크에서 읽은 상태 그대로 변경이 전혀 없다면 리스트에서 관리되지 않지만, 변경이 가해진 데이터 페이지는 플러시 리스트에 관리되고 특정 시점이 되면 디스크로 기록된다.
       
   • 버퍼 풀과 리두 로그

     • 리두 로그(Redo Log)
       - DB에서 일어난 모든 변화를 저장하는 메모리 공간을 말한다.
       - 사용자의 INSERT, DELETE, UPDATE 작업으로 인한 데이터의 변화가 아직 디스크에는 적용되지 않은 상태에서 에러가 발생하면 리두 로그를 사용하여 작업 내용을 디스크에 반영한다.

       

       버퍼 풀은 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링이라는 두 가지 용도가 있다.

       버퍼 풀의 더티 페이지는 특정 리두 로그와의 관계를 가진다.

       리두 로그의 크기 만큼 더티 페이지를 가질 수 있고, 일정 수준이 되면 디스크 쓰기 작업을 진행한다.

   • 버퍼 풀 플러시 InnoDB는 더티 페이지를 성능상의 악영향 없이 디스크에 동기화 하기위해 2개의 플러시 기능을 백그라운드로 실행한다.
     • 플러시 리스트 플러시 리두 로그 공간의 재활용을 위해 주기적으로 오래된 리두 로그 공간을 비워야한다. 이때 오래된 리두 로그 공간을 지우려면 해당 리두 로그와 연결된 더티 페이지가 디스크에 동기화 되어야하는데, 이를 위해 플러시 리스트를 이용하여 디스크에 더티 페이지 데이터를 동기화 한다.
       
     • LRU 리스트 플러시 LRU 리스트에서 사용 빈도가 낮은 페이지들을 제거해서 공간을 만드는 작업을 말한다.
       
   • 버퍼 풀 상태 백업 및 복구 DB 서버를 껐다가 키면 성능이 1/10도 안되는 경우가 많다. 왜냐하면 버퍼 풀에 쿼리들이 사용할 데이터가 이미 준비되어 있는 상태(워밍업 상태)라면 디스크에서 데이터를 읽지 않아도 돼서 성능이 좋지만, 재시작 한 뒤에는 버퍼풀이 비어있기 때문이다. 서버를 재시작 하기 전에 버퍼 풀을 백업하고, 다시 시작하면 버퍼 풀을 복구할 수 있다. 백업은 메타 데이터만 해서 빠르지만, 복구는 많은 디스크 읽기를 요구하므로 버퍼 풀 복구가 실행중일 때 서비스를 시작하는 것은 좋지 않다. 시스템 변수로 이를 자동으로 하도록 할 수 있다.
     
   • 버퍼 풀의 적재 내용 확인 information_schema 데이터베이스의 innodb_buffer_page 테이블을 이용하여 버퍼 풀의 적재 내용을 확인할 수 있다. 하지만 버퍼 풀이 크면 테이블 조회가 큰 부하를 일으키며 서비스 쿼리가 많이 느려지는 문제가 있다. 위 사진 처럼 innodb_cached_indexes 테이블을 이용하여 테이블의 인덱스별로 데이터 페이지가 얼마나 적재되어 있는지 확인할 수 있다.
     

   1. Double Write Buffer

      Double Write Buffer는 ****더티페이지를 디스크로 플러시 할 때, 중간에 일부만 기록되는 문제(파셜 페이지, 톤 페이지)를 방지하기 위해 사용하는 버퍼를 말한다.

      

      위 사진과 같이 실제 데이터 파일에 변경 내용을 기록하기 전에 더티 페이지를 묶어서 Double Write 버퍼에 기록하고, 각 데이터 페이지를 실제 데이터 파일에 기록한다.

      만약 실제 데이터 파일에 기록 중에 시스템이 비정상적으로 종료될 시 버퍼의 내용을 실제 데이터와 비교하여 동기화 작업을 완료한다.

   2. 언두 로그

      InnoDB이 트랜잭션 격리 수준, 롤백을 보장하기 위해 INSERT, UPDATE, DELETE로 변경되기 이전의 데이터를 백업해두는 공간을 언두 로그라고 한다.

      • 격리 수준 데이터 변경 중에 데이터 조회 시 격리 수준에 따라 언두 로그에 백업해둔 데이터를 읽을 수 있다.
        
      • 롤백 트랜잭션 롤백 시 이전 데이터로 복구해야 하는데, 이때 언두 로그에 백업해둔 데이터를 사용한다.
        

      활성 상태의 트랜잭션이 장시간 유지되면 해당 트랜잭션이 시작된 시점부터 생성된 언두 로그를 계속 보존해야 하고, 변경된 레코드를 조회하게 되면 언두 로그의 이력을 조회해야 하므로 쿼리의 성능이 떨어진다.

      → 언두 로그의 용량을 모니터링 하는 것이 좋다.

   3. 체인지 버퍼

      RDBMS에서 레코드가 변경될 때는 데이터 파일 변경 작업과 테이블에 포함된 인덱스를 업데이트 해야한다.

      But 인덱스를 업데이트 하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로 많은 자원을 소모한다.

      → InnoDB는 인덱스 업데이트 시 디스크에서 읽어와야 한다면 즉시 실행하지 않고 메모리에서 임시로 처리하고 바로 결과를 반환하여 성능을 향상시키는데, 이때 사용하는 메모리 공간을 체인지 버퍼라고 한다.

      참고로 유니크 인덱스는 중복 여부를 체크해야 하므로 체인지 버퍼 사용이 불가능하다.

      체인지 버퍼에 임시로 저장된 인덱스는 체인지 버퍼 머지 스레드에 의해 병합된다.

   4. 리두 로그, 로그 버퍼

      리두 로그는 서버가 비정상적으로 종료되어도 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해준다.

      대부분의 DBMS는 파일 쓰기 시 디스크 랜덤 엑세스가 필요 → 큰 비용 필요 → 쓰기 비용이 낮은 리두 로그에 먼저 기록하고, 주기적으로 로그의 내용을 디스크에 동기화한다.

      리두 로그는 아래와 같은 상황에서 사용한다.

      1. 커밋됐지만 데이터 파일에 기록되지 않은 데이터 처리 시
      2. 롤백됐지만 데이터 파일에 기록된 데이터 처리 시

      여기서 2번의 경우는 롤백 시에는 언두 로그가 사용되지만, 변경 사항이 커밋되었는지, 롤백되었는지를 판단하기 위해서 리두 로그도 사용된다.

      • 참고로 변경된 데이터는 우선 리두 로그에 쓰여지고, 리두 로그의 내용은 주기적으로 디스크에 동기화 된다. (이 동기화 주기는 innodb_flush_log_at_trx_commit 시스템 변수로 결정)
      • 커밋은 리두 로그에 변경 사항이 전부 쓰여진 뒤에 진행되기 때문에 커밋 되었어도 디스크에 일부 변경 사항이 반영되지 않았을 수도 있고, 커밋 이전에도 디스크에 변경 사항 일부가 반영되었을 수도 있다.

      리두 로그 파일의 크기가 적절해야 변경된 내용을 버퍼 풀에 모았다가 한 번에 디스크에 기록할 수 있다.

      But 변경 작업이 많은 서버의 경우는 리두 로그 기록 작업이 문제가 되므로 버퍼링을 해야하는데, 이때 사용되는 공간을 로그 버퍼라고 한다.

   5. 어댑티브 해시 인덱스

      자주 조회되는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들어 B-Tree 인덱스를 타지 않고 바로 데이터에 접근할 수 있는 기능을 말한다.

      B-Tree 인덱스의 경우 시간 복잡도는 O(log n) 이지만, 해시 인덱스의 경우는 O(1)이므로 성능이 향상된다.

      → CPU 사용률은 줄고, 초당 쿼리 처리 수는 증가한다.

      어댑티브 해시 인덱스는 사용자가 직접 생성하는 것이 아닌 InnoDB가 자주 조회되는 데이터에 대해 자동으로 생성한다.

      • 어댑티브 해시 인덱스가 도움이 되는 경우
        • 디스크 읽기가 많지 않은 경우
        • 동등 비교와 IN 연산자가 많은 경우
        • 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우
      • 어댑티브 해시 인덱스가 도움이 되지 않는 경우
        • 디스크 읽기가 많은 경우
        • Join이나 Like 검색이 많은 경우
        • 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

      도움이 안되는 경우에는 innodb_adaptive_hash_index 시스템 변수를 이용하여 비활성화할 수 있다.

      

      어댑티브 인덱스가 도움이 되는지는 위 사진과 같이 MySQL 서버의 상태 값들을 통해 판단할 수 있다.

      위 사진 같은 경우는 1.03번이 해시 인덱스를 사용, 2.64번이 해시인덱스를 사용하지 않았음을 의미한다.

MySQL 로그 파일

1. 에러 로그 파일

   MySQL이 실행 중에 발생하는 경고, 에러 메시지가 출력되는 로그 파일을 말한다.

2. 제너럴 쿼리 로그 파일

   서버에서 실행되는 쿼리들을 전부 기록하는 로그 파일을 말한다.

3. 슬로우 쿼리 로그

   특정 시간보다 오래 걸린 쿼리를 기록하는 로그 파일을 말한다.

   서비스 운영 중에 어떤 쿼리가 문제인지 파악할 때 용이하다.

   쿼리의 내용이 상당히 많고 복잡하므로 pt-query-digest 스크립트를 이용하여 분석할 수 있다.