프로그래밍 API를 통해 MySQL에 요청을 보낸다.
MySQL은 요청 받은 쿼리를 분석해서 최적의 실행계획을 수립 및 스토리지 엔진에게 명령한다.
받은 명령대로 스토리지 엔진이 디스크에 접근하여 데이터를 입출력한다.
쿼리 파서
요청으로 들어온 쿼리를 MySQL이 인식할 수 있는 최소 단위인 토큰으로 분리해 트리 형태의 구조로 만드는 역할을 한다.
쿼리 문장의 기본 문법 오류는 여기서 발견된다.
전처리기
각 토큰을 테이블, 컬럼 등과 매핑해 해당 객체의 존재 여부와 접근 권한을 확인한다.
옵티마이저
요청이 들어온 쿼리를 가장 효율적으로 실행할 수 있는 실행 계획을 수립한다.
실행 엔진
옵티마이저가 수립한 실행 계획대로 핸들러에게 명령을 내린다.
핸들러(스토리지 엔진)
실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 읽어오는 역할을 담당한다.
쿼리 캐시는 성능 자체는 빨랐으나, 테이블의 데이터가 변경되면 변경된 테이블에 관련된 캐시들을 삭제해야 했기 때문에 성능 저하를 유발하여 MySQL 8.0 이후로는 제거됨
MySQL 서버는 스레드 기반으로 작동하며, 포그라운드 스레드와 백그라운드 스레드로 구분할 수 있다.
스레드가 너무 많아지면 성능이 감소한다. 성능이 떨어지는 데는 아래와 같은 원인들이 있다.
→ 스레드 풀은 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이 목적이다.
MySQL 커뮤니티 에디션에서 스레드 풀 기능을 쓰려면 스레드 풀 플러그인(Percona Server)을 추가로 설치해야한다.
스레드 풀을 도입한다고 무조건 성능이 향상되는 것이 아니라, 적절한 세팅이 중요하다.
InnoDB는 MySQL에서 사용 가능한 스토리지 엔진 중 레코드 기반 잠금을 제공한다.
→ 높은 동시성 처리가 가능하고, 안정적이며, 성능이 우수하다.
PK에 의한 클러스터링
InnoDB의 모든 테이블은 PK값의 순서대로 디스크에 저장된다.
→ PK가 클러스터링 인덱스이므로 PK를 이용한 검색은 빠르게 처리된다.
외래 키 지원
왜래 키는 부모 & 자식 테이블 모두 인덱스 생성이 필요하고, 변경 시 부모 & 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하다.
→ 위 과정에서 잠금이 여러 테이블로 전파되어 데드락 발생할 수 있다.
foreign_key_checks
시스템 변수를 OFF로 설정해서 왜래 키 체크를 중지할 수 있다.
MVCC(Multi Version Concurrency Control)
InnoDB는 MVCC 기술을 이용하여 잠금을 사용하지 않는 일관된 읽기를 제공하는데, 이를 위해 언두 로그를 이용한다.
업데이트 쿼리 실행
버퍼 풀의 데이터를 즉시 새 데이터로 변경
기존 데이터는 언두 로그에 복사
COMMIT
명령 실행 시 지금의 상태를 영구적으로 적용
ROLLBACK
명령 실행 시 언두 영역의 데이터를 버퍼 풀로 복구하고 언두 영역의 데이터 삭제
디스크의 데이터 파일에는 시점에 따라 업데이트 여부가 다르다.
만약 COMMIT
, ROLLBACK
이 안된 상태에서 작업중인 레코드를 조회하면 격리 수준에 따라 결과가 다르다.
ex1) READ_UNCOMMITTED
: InnoDB 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환한다.
ex2) READ_COMMITTED
or 그 이상의 격리 수준: 언두 영역의 데이터를 반환한다.
→ 이러한 과정을 MVCC라고 표현한다.
참고로 언두 영역의 데이터는 언두 영역의 데이터를 필요로 하는 트랜잭션이 없을 때 삭제된다.
잠금 없는 일관된 읽기(Non-Locking Consistent Read)
InnoDB는 MVCC 기술을 통해 잠금을 걸지 않고 읽기 작업을 수행하기 때문에 다른 트랜잭션의 잠금을 기다리지 않고 읽기 작업이 가능하다.
일관된 읽기를 위해 언두 로그를 삭제하지 못하고 계속 유지해야 하기 때문에 오랜 시간 동안 활성 상태인 트랜잭션으로 인해 문제가 발생할 수 있다.
→ 트랜잭션이 시작됐다면 빠르게 롤백이나 커밋을 하는 것이 좋음
자동 데드락 감지
InnoDB는 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프 형태로 관리한다.
데드락 감지 스레드가 주기적으로 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그 중 언두 레코드를 적게 가진 트랜잭션을 강제 종료시킨다.
데드락 감지 스레드는 일반적으로는 크게 부담되는 작업은 아니지만, 동시 처리 스레드가 매우 많아지거나 각 트랜잭션이 가진 잠금의 개수가 많아지면 느려진다.
데드락 감지 스레드는 잠금 목록을 검사하기 위해 잠금 상태가 변경되지 않도록 새로운 잠금을 건다.
→ 데드락 감지 스레드가 느려지면 서비스 쿼리를 처리 중인 스레드도 느려진다.
innodb_deadlock_detect
시스템 변수를 OFF로 설정하면 데드락 감지 스레드가 작동하지 않는다.
하지만, 그러면 데드락 발생 시 교착상태 발생하게 된다.
innodb_lock_wait_timeout
시스템 변수를 설정하면 일정 시간이 지났을 때 요청을 실패하게 할 수 있다.
→ innodb_deadlock_detect
시스템 변수를 OFF로 설정하면 innodb_lock_wait_timeout
시스템 변수는 기본값인 50초 보다 훨씬 낮게 설정하는 것이 좋다.
자동화된 장애 복구
InnoDB에는 손실이나 장애로부터 데이터를 보호하기 위한 여러 가지 메커니즘이 탑재되어 있다.
자동으로 복구가 안되는 경우는 innodb_force_recovery
시스템 변수를 설정하여 복구할 수 있다.
InnoDB 버퍼 풀
버퍼 풀은 디스크의 데이터 파일이나 인덱스 정보를 캐시해 두는 메모리 공간을 말한다.
또한, 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이 한다.
일반적으로 데이터를 변경하는 쿼리는 데이터 파일의 이곳저곳에 위치한 레코드를 변경하기 때문에 랜덤한 디스크 작업을 발생 시킨다.
→ 버퍼 풀이 이러한 변경된 데이터를 모아서 처리하면 랜덤한 디스크 작업의 횟수를 줄일 수 있다.
리두 로그(Redo Log)
- DB에서 일어난 모든 변화를 저장하는 메모리 공간을 말한다.
- 사용자의 INSERT
, DELETE
, UPDATE
작업으로 인한 데이터의 변화가 아직 디스크에는 적용되지 않은 상태에서 에러가 발생하면 리두 로그를 사용하여 작업 내용을 디스크에 반영한다.
버퍼 풀은 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링이라는 두 가지 용도가 있다.
버퍼 풀의 더티 페이지는 특정 리두 로그와의 관계를 가진다.
리두 로그의 크기 만큼 더티 페이지를 가질 수 있고, 일정 수준이 되면 디스크 쓰기 작업을 진행한다.
information_schema
데이터베이스의 innodb_buffer_page
테이블을 이용하여 버퍼 풀의 적재 내용을 확인할 수 있다. 하지만 버퍼 풀이 크면 테이블 조회가 큰 부하를 일으키며 서비스 쿼리가 많이 느려지는 문제가 있다. 위 사진 처럼 innodb_cached_indexes
테이블을 이용하여 테이블의 인덱스별로 데이터 페이지가 얼마나 적재되어 있는지 확인할 수 있다.Double Write Buffer
Double Write Buffer는 ****더티페이지를 디스크로 플러시 할 때, 중간에 일부만 기록되는 문제(파셜 페이지, 톤 페이지)를 방지하기 위해 사용하는 버퍼를 말한다.
위 사진과 같이 실제 데이터 파일에 변경 내용을 기록하기 전에 더티 페이지를 묶어서 Double Write 버퍼에 기록하고, 각 데이터 페이지를 실제 데이터 파일에 기록한다.
만약 실제 데이터 파일에 기록 중에 시스템이 비정상적으로 종료될 시 버퍼의 내용을 실제 데이터와 비교하여 동기화 작업을 완료한다.
언두 로그
InnoDB이 트랜잭션 격리 수준, 롤백을 보장하기 위해 INSERT
, UPDATE
, DELETE
로 변경되기 이전의 데이터를 백업해두는 공간을 언두 로그라고 한다.
활성 상태의 트랜잭션이 장시간 유지되면 해당 트랜잭션이 시작된 시점부터 생성된 언두 로그를 계속 보존해야 하고, 변경된 레코드를 조회하게 되면 언두 로그의 이력을 조회해야 하므로 쿼리의 성능이 떨어진다.
→ 언두 로그의 용량을 모니터링 하는 것이 좋다.
체인지 버퍼
RDBMS에서 레코드가 변경될 때는 데이터 파일 변경 작업과 테이블에 포함된 인덱스를 업데이트 해야한다.
But 인덱스를 업데이트 하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로 많은 자원을 소모한다.
→ InnoDB는 인덱스 업데이트 시 디스크에서 읽어와야 한다면 즉시 실행하지 않고 메모리에서 임시로 처리하고 바로 결과를 반환하여 성능을 향상시키는데, 이때 사용하는 메모리 공간을 체인지 버퍼라고 한다.
참고로 유니크 인덱스는 중복 여부를 체크해야 하므로 체인지 버퍼 사용이 불가능하다.
체인지 버퍼에 임시로 저장된 인덱스는 체인지 버퍼 머지 스레드에 의해 병합된다.
리두 로그, 로그 버퍼
리두 로그는 서버가 비정상적으로 종료되어도 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해준다.
대부분의 DBMS는 파일 쓰기 시 디스크 랜덤 엑세스가 필요 → 큰 비용 필요 → 쓰기 비용이 낮은 리두 로그에 먼저 기록하고, 주기적으로 로그의 내용을 디스크에 동기화한다.
리두 로그는 아래와 같은 상황에서 사용한다.
여기서 2번의 경우는 롤백 시에는 언두 로그가 사용되지만, 변경 사항이 커밋되었는지, 롤백되었는지를 판단하기 위해서 리두 로그도 사용된다.
innodb_flush_log_at_trx_commit
시스템 변수로 결정)리두 로그 파일의 크기가 적절해야 변경된 내용을 버퍼 풀에 모았다가 한 번에 디스크에 기록할 수 있다.
But 변경 작업이 많은 서버의 경우는 리두 로그 기록 작업이 문제가 되므로 버퍼링을 해야하는데, 이때 사용되는 공간을 로그 버퍼라고 한다.
어댑티브 해시 인덱스
자주 조회되는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들어 B-Tree 인덱스를 타지 않고 바로 데이터에 접근할 수 있는 기능을 말한다.
B-Tree 인덱스의 경우 시간 복잡도는 O(log n) 이지만, 해시 인덱스의 경우는 O(1)이므로 성능이 향상된다.
→ CPU 사용률은 줄고, 초당 쿼리 처리 수는 증가한다.
어댑티브 해시 인덱스는 사용자가 직접 생성하는 것이 아닌 InnoDB가 자주 조회되는 데이터에 대해 자동으로 생성한다.
도움이 안되는 경우에는 innodb_adaptive_hash_index
시스템 변수를 이용하여 비활성화할 수 있다.
어댑티브 인덱스가 도움이 되는지는 위 사진과 같이 MySQL 서버의 상태 값들을 통해 판단할 수 있다.
위 사진 같은 경우는 1.03번이 해시 인덱스를 사용, 2.64번이 해시인덱스를 사용하지 않았음을 의미한다.
에러 로그 파일
MySQL이 실행 중에 발생하는 경고, 에러 메시지가 출력되는 로그 파일을 말한다.
제너럴 쿼리 로그 파일
서버에서 실행되는 쿼리들을 전부 기록하는 로그 파일을 말한다.
슬로우 쿼리 로그
특정 시간보다 오래 걸린 쿼리를 기록하는 로그 파일을 말한다.
서비스 운영 중에 어떤 쿼리가 문제인지 파악할 때 용이하다.
쿼리의 내용이 상당히 많고 복잡하므로 pt-query-digest 스크립트를 이용하여 분석할 수 있다.