Day 1. MySQL 아키텍처

본 글은 위키북스에서 나온 Real MySQL을 공부하고 정리한 내용입니다

1. MySQL의 전체 구조

MySQL은 C API, JDBC, ODBC, .NET의 표준 드라이버를 제공
이런 드라이버를 이용해 C/C++, Java, Python, Ruby 등 모든 언어를 이용해 MySQL 서버에 쿼리를 사용할 수 있게 지원

1-1. MySQL 엔진

요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS의 두뇌 역할
하나만 사용
구성
- 커넥션 핸들러
  - 클라이언트로부터의 접속 및 쿼리 요청을 처리
- SQL 파서
- 전처리기
- 옵티마이저
  - 쿼리의 최적화된 실행을 제공
- 보조 저장소 기능
  - 성능 향상을 위해 MyISAM의 키 캐시, InnoDB의 버퍼 풀 등

1-2. 스토리지 엔진

실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로 부터 데이터를 읽어오는 역할
여러개를 동시에 사용 가능

1-3. 핸들러 API

데이터를 쓰거나 읽어야 할때 각 스토리지 엔진에게 보내는 API 요청
SHOW GLOBAL STATUS LIKE 'Handler%';

2. MySQL 스레드 구조

MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동
포그라운드 스레드, 백그라운드 스레드로 구분된다
- 포그라운드 스레드(사용자 스레드)
  - 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재
  - 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리하는 것이 임무
  - 커넥션 종료시 Thread Pool로 되돌아가지만, thread_cache_size 이상의 쓰레드는 종료된다
- 백그라운드 스레드
  - 여러 중요한 역할을 처리, 특히나 로그 쓰레드와 쓰기 쓰레드가 주요 기능이다. ( InnoDB )
  - 쓰기 쓰레드는 쓰기 작업(INSERT, UPDATE, DELETE)를 버퍼링하여 일괄 처리하는 기능을 수행 ( InnoDB )
  - 이로써 데이터가 스토리지에 완전히 저장될 때까지 기다리지 않아도 된다

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

글로벌 메모리 영역과 로컬 메모리 영역으로 구분된다
글로벌 메모리 영역
- MySQL 서버에서 하나의 메모리 공간만 할당된다
- 필요에 따라 2개 이상의 메모리 공간을 할당 받을 순 있지만, 쓰레드 수와 무관하며 모든 쓰레드에 의해 공유된다
로컬 메모리 영역
- MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역
- 각 스레드별로 독립적으로 할당되며, 절대 공유하지 않는다

4. 쿼리 실행 구조

쿼리 파서
- 사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위)으로 분리해 트리 형태의 구조로 만드는 작업
- 쿼리 문장의 기본 문법 오류(Syntax Error)이 여기서 발견된다
전처리기
- 파서 과정에서 만들어진 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인
- 각 토큰을 테이블 이름이나 칼럼 이름 또는 내장 함수와 같은 개체를 매핑해 존재 여부와 접근 권한등을 확인
옵티마이저
- 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지 결정하는 역할을 담당
쿼리 실행기(실행 엔진)
- 옵티마이저가 만들어낸 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할
- 옵티마이저는 회사 경영진(계획을 짬), 실행 엔진은 중간 관리자(각 실무자들을 관리), 핸들러는 실무자(담당 업무 처리)
스토리지 엔진(핸들러)
- MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할

5. 복제(Replication)

MySQL 확장성을 위한 가장 일반적인 방법
2대 이상의 MySQL 서버가 동일한 데이터를 담도록 실시간으로 동기화하는 기술
쓰기와 읽기 역할을 구분해 전자를 Master, 후자를 Slave라고 한다
일반적으로 MySQL 서버의 복제에서는 마스터는 반드시 1개이며 슬레이브는 1개 이상으로 구성

5-1. Master

기술적으로는 MySQL의 바이너리 로그가 활성화되면 어떤 MySQL 서버든 마스터가 될 수 있다
실행되는 DML(데이터 조작어), DDL(스키마 조작어) 가운데 데이터의 구조나 내용을 변경하는 모든 쿼리 문장은 바이너리 로그에 기록한다
슬레이브 서버에서 변경 내역을 요청하면 마스터 장비는 바이너리 로그를 읽어 슬레이브로 넘김
각 슬레이브 마다 담당 스레드가 정해지고 이를 "Binlog dump" 스레드라고 부른다 ( 슬레이브 10개, Binlog dump 스레드 10개 )

5-2. Slave

데이터(바이너리 로그)를 받아 올 마스터 장비의 정보(ip, port, 접속 계정등)를 가지고 있는 경우 슬레이브가 된다
일반적으로 읽기 전용으로 설정할 때가 많다 ( 아니라면 데이터 일관성을 보장하기 어려움 )
슬레이브 서버의 I/O 스레드는 마스터 서버에 접속해 변경 내역을 요청, 받아 온 변경 내역을 릴레이 로그에 기록
슬레이스 서버의 SQL 스레드가 릴레이 로그에 기록된 변경 내역을 재실행함으로써 데이터를 마스터와 동일하게 맞춘다

5-3. 주의사항

슬레이브는 하나의 마스터만 설정 가능
마스터와 슬레이브의 데이터 동기화를 위해 슬레이브는 읽기 전용으로 설정
- 일부 변경 작업이 슬레이브에서 실행된다면 데이터 동기화에 많은 비용을 지불해야한다
슬레이브 서버용 장비는 마스터와 동일한 사양이 적합
- 마스터 서버에서 수많은 동시 사용자가 실행한 데이터 변경 쿼리 문장이 슬레이브에서는 하나의 스레드로 처리된다
- 변경이 잦은 MySQL 서버일수록 슬레이스 서버 사양이 더 좋아야 지연되지 않고 처리될 수 있다
복제가 불필요한 경우에는 바이너리 로그 중지
- 바이너리 로그를 안정적으로 기록하기 위해 갭 락(Gap lock)을 유지하고, 매번 트랜잭션이 커밋될 때마다 데이터를 변경시킨 문장을 바이너리 로그에 기록해야 한다
바이너리 로그와 트랜잭션 격리 수준
- 바이너리 로그 파일은 어떤 내용이 기록되느냐에 따라 STATEMENT 포맷 방식, ROW 포맷 방식으로 나뉜다
- STATEMENT 포맷 방식 ( MySQL 5.0이하 버전까지는 유일한 방식 )
  - 마스터에서 실행되는 쿼리 문장을 기록하는 방식
  - REPEATABLE READ 격리 수준만 사용 가능 ( 레코드 간의 간격을 잠그는 갭락, 넥스트 키 락 필요 )
  - 아무리 많은 데이터 변경을 유발하는 쿼리라도 문장 하나만 전달하기 때문에 네트워크 트래픽이 적다
- ROW 포맷 방식( MySQL 5.1부터 지원 )
  - 마스터에서 실행된 쿼리에 의해 변경된 레코드 값을 기록하는 방식
  - 변경된 모든 레코드 값을 전달하기 때문에 네트워크 트래픽이 많음
  - READ-COMMITTED 트랜잭션 격리 수준에서도 작동 가능하며, 잠금의 경합은 줄어든다

6. 쿼리 캐시

타 DBMS에는 없는 MySQL의 독특한 기능중 하나
여러가지 복잡한 처리 절차와 꽤 큰 비용을 들여 실행된 결과를 쿼리 캐시에 담아 두고, 동일한 쿼리 요청이 왔을 때 간단하게 쿼리 캐시에서 찾아서 바로 결과를 내려 줄 수 있다
쿼리 결과를 메모리에 캐시해 두는 기능
간단한 키와 값의 쌍으로 관리되는 Map과 같은 데이터 구조로 구현돼 있다
동일한 쿼리 문장이라도 캐시된 결과를 바로 내려보내면 안된다. 즉 여러가지 확인 절차를 거치게 된다
1. 요청된 쿼리 문장이 쿼리 캐시에 존재하는가?
  - 쿼리 문장 자체가 동일한지 여부를 비교 ( 공백, 탭 같은 모든 문장을 다 포함 )
  - 결론적으로 어플리케이션의 전체 쿼리 가운데 동일하거나 비슷한 작업을 하는 쿼리는 하나의 쿼리로 통일해 문자열로 관리하는 것이 좋다
2. 해당 사용자가 그 결과를 볼 수 있는 권한을 가지고 있는가?
3. 트랜잭션 내에서 실행된 쿼리인 경우, 그 결과가 가시 범위 내의 트랜잭션에서 만들어진 결과인가?
  - 각 트랜잭션은 자신의 ID보다 ID 값이 큰 트랜잭션에서 변경한 작업 내역이나 쿼리 결과는 참조 불가 ( 순차 증가하기 때문 )
4. 쿼리에 사용된 기능(내장 함수, 저장 함수 등)이 캐시돼도 동일한 결과를 보장할 수 있는가?
  - CURRENT_DATE, SYSDATE, RAND 등과 같이 호출 시점에 따라 결과가 달라지는 요소가 있는가?
  - PrepareStatement의 경우 변수가 결과에 영향을 미치지 않는가?
    - PrepareStatement 경우에는 쿼리 문장 자체에 변수("?")가 바인딩되어 사용되기 때문에 쿼리 문장 자체로 쿼리 캐시를 찾을 수 없다
5. 캐시가 만들어지고 난 이후 해당 데이터가 다른 사용자에 의해 변경되지 않았는가?
  - 테이블 단위로 동작하기 때문에 쿼리 캐시 메모리 공간을 크게 설정하면 불필요한 작업시간이 소모된다
  - 적절한 크기는 32M ~ 64M을 추천하고 있다
6. 쿼리에 의해 만들어진 결과가 캐시하기에 너무 크지 않는가?
7. 그 밖에 쿼리 캐시를 사용하지 못하게 만드는 요소가 사용됐는가?
쿼리 캐시 사용률 확인하기
```
// show global status

SHOW GLOBAL STATUS LIKE 'Qcache%';

SHOW GLOBAL STATUS LIKE 'Com_select';
```
- 쿼리 캐시가 얼마나 사용됐고 MySQL 서버에서 SELECT 쿼리가 얼마나 실행됐는지 등에 대한 정보를 확인할 수 있다
- Qcache_hit: 쿼리 캐시로 처리된 SELECT 쿼리의 수
- Com_select: 쿼리 캐시에서 결과를 찾지 못해 MySQL 서버가 쿼리를 실행한 횟수
- 쿼리 캐시 히트율(%) = Qcache_hits / (Qcache_hits + Com_select) * 100

7. InnoDB 스토리지 엔진

InnoDB는 MySQL에서 사용할 수 있는 스토리지 엔진 중에서 거의 유일하게 레코드 기반의 잠금을 제공
때문에 높은 동시성 처리가 가능하고 또한 안정적이며 성능이 뛰어나다

7-1. InnoDB 스토리지 엔진의 특성

프라이머리 키(PK)에 의한 클러스터링
- InnoDB의 모든 테이블은 기본적으로 PK를 기준으로 클러스터링되어 저장된다
- 즉 PK값의 순서대로 디스크에 저장된다는 뜻 ( PK에 의한 Range Scan은 상당히 빨리 처리될 수 있다 )
- 쿼리의 실행 계획에서 PK는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정 ( PK가 선택될 확률이 높음 )
잠금이 필요 없는 일관된 읽기 ( Non-locking consistent read )
- InnoDB 스토리지 엔진은 MVCC(Multi Version Concurrency Control)라는 기술을 이용해 락을 걸지 않고 읽기 작업을 수행한다
- 락을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 락을 기다리지도 않는다
외래 키 지원
- 외래 키는 여러 가지 제약사항 탓으로 인해 실무에서는 잘 사용하지 않기 때문에 그렇게 필수 적이지는 않지만 개발 환경의 데이터베이스에서는 좋은 가이드 역할을 할 수 있다
- InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 컬럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고, 그로 인해 데드락이 발생할 때가 많다
자동 데드락 감지
- InnoDB는 그래프 기반의 데드락 체크 방식을 사용 ( 데드락이 발생함과 동시에 바로 감지 )
- 데드락이 감지되면 관련 트랜잭션 중에서 ROLLBACK이 가장 용이한 ( 변경된 레코드 수가 가장 적은 )트랜잭션을 강제 종료해버린다, 때문에 데드락으로 인한 Timeout이나 Slow Query 발생 확률이 적어진다
자동화된 장애 복구
- InnoDB에는 손실이나 장애로부터 데이터를 보호하기 위한 여러 가지 매커니즘이 탑재되어 있다
- 해당 매커니즘을 이용해 MySQL 서버가 시작될 때, 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된 데이터 페이즈등에 대해 일련의 복구 작업을 자동으로 진행해준다

7-2. InnoDB 버퍼 풀

InnoDB 스토리지 엔진에서 가장 핵심적인 부분으로, 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간
쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼역할도 같이 한다
- InnoDB 버퍼 풀은 아직 디스크에 기록되지 않은 변경된 데이터를 가지고 있다 ( 해당 페이지를 Dirty Page라고 부른다 )
- 이러한 더티 페이지는 InnoDB에서 주기적으로 또는 어떤 조건이 되면 체크포인트 이벤트가 발생하는데, Write 스레드가 필요한 만큼의 더티페이지만 디스크로 기록한다
- 체크 포인트가 발생한다고 해서 버퍼 풀의 모든 더티 페이지를 디스크로 기록하는 것은 아니다

7-3. Undo 로그

언두 영역은 UPDATE 문장이나 DELETE 와 같은 문장으로 데이터를 변경했을 때 변경되기 전의 데이터(이전 데이터)를 보관하는 곳이다
예시 ( 현재 데이터: member_id=1, name='주지민' )
```
UPDATE member SET name='홍길동' WHERE member_id='1';
```
- 위 문장이 실행되면 트랜잭션이 커밋되지 않아도, 실제 데이터 파일은 member_id = 1 에 해당되는 name 값은 '홍길동'으로 변경된다
- 그리고 기존의 '주지민' 이라는 원래 데이터는 Undo 영역으로 백업된다
- 사용자가 정상적으로 커밋시키면 실제 데이터 파일은 유지('홍길동')되고, 롤백하게 되면 Undo 영역의 백업된 데이터를 다시 실제 데이터 파일로 복구한다
용도
- 트랜잭션의 롤백 대비용
- 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공하는데 사용

7-4. 인서트 버퍼(insert Buffer)

RDBMS에서 레코드가 INSERT되거나 UPDATE될 때는 데이터 파일을 변경하는 작업뿐 아니라 해당 테이블에 포함된 인덱스를 업데이트하는 작업도 필요하다
인덱스를 업데이트하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱스가 많다면 이 작업은 상당히 많은 자원을 쓰게된다
InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만, 디스크로부터 읽어와서 업데이트해야하면 즉시 실행하지 않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시킨다
이때 사용하는 임시 공간이 인서트 버퍼이다
사용자에게 결과를 전달하기 전에 반드시 중복 여부를 체크해야 하는 유니크 인덱스는 인서트 버퍼를 사용할 수 없다
MySQL 5.5부터는 INSERT, DELETE로 인해 키를 추가하거나 삭제하는 작업에 대해서도 버퍼링이 될 수 있게 개선됐다

7-5. Redo 로그 및 로그 버퍼

필요성
- 쿼리 문장으로 데이터를 변경하고 커밋하면 DBMS는 데이터의 ACID를 보장하기 위해 즉시 변경된 데이터의 내용을 데이터 파일로 기록해야한다
- 하지만 이러한 데이터 파일의 변경 작업은 순차적으로 많은 데이터를 한꺼번에 변경하는 것이 아니고 랜덤하게 디스크에 기록해야 하기 때문에 디스크를 상당히 바쁘게 만드는 작업이다
- 이러한 부하를 줄이기 위해 대부분의 DBMS에는 변경된 데이터를 버퍼링해 두기 위해 InnoDB 버퍼 풀과 같은 장치가 포함되어 있다
- 하지만 이 장치만으로는 ACID를 보장할 수 없는데 이를 위해 변경된 내용을 순차적으로 디스크에 기록하는 로그파일을 가지고 있고, 이 로그 파일이 리두 로그라고 부른다
리두 로그 덕분에 DBMS 데이터는 버퍼링을 통해 한꺼번에 디스크에 변경된 내용을 처리할 수 있고 그로 인해 상당한 성능 향상을 기대할 수 있게 됐다
하지만 변경 작업이 많은 DBMS 서버의 경우 이 리두 로그의 기록 작업 역시 부하가 될 수 있는데, 이러한 부분을 보완하기 위해 버퍼링을 사용하게 되고 이 공간을 로그 버퍼라고 한다
ACID
- Atomic
  - 트랜잭션은 원자성 작업이어야 한다
  - 모든 작업이 반영되거나 모두 롤백되는 특성입니다
- Consistent:
  - 일관성
  - 트랜잭션 작업이 시작되지 전에 데이터베이스 상태가 일관된 상태였다면 트랜잭션 작업이 종료된 후에도 일관성 있는 데이터 베이스 상태를 유지해아한다
  - 이전에 정해진 규칙이 트랜잭션 작업 이후에도 적용가능해야한다
- Isolated
  - 격리성
  - 트랜잭션 작업 수행 중에는 다른 트랜잭션에 영향을 주어서도 안되고, 다른 트랜잭션들에 의해 간섭을 받아서도 안 된다
  - 트랜젝션 자신은 고립된 상태에서 수행되어야 한다는 것을 의미
  - 즉 다수의 트랜잭션이 동시에 수행중인 상황에서 하나의 트랜잭션이 완료될 때까지는 현재 실행 중인 트랜잭션의 중간 수행결과를 다른 트랜잭션에서 보거나 참조 할 수 없다
- Durable
  - 지속성
  - 한번 저장된 데이터는 지속적으로 유지돼야 한다

7-6. MVCC

Multi Version Concurrency Control
일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며, 잠금을 사용하지 않는 일관된 읽기를 제공하는 데 목적이 있다
InnoDB 에서는 언두 로그를 이용해 이 기능을 구현한다
멀티 버전, 즉 하나의 레코드에 대해 여러 개의 보전이 동시에 관리된다는 의미
예제 ( 격리수준, READ)COMMITTED )
```
-- MySQL

CREATE TABLE member (
    m_id INT NOT NULL,
    m_name VARCHAR(20) NOT NULL,
    m_area VARCHAR(100) NOT NULL,
    
    PRIMARY KEY (m_Id),
    INDEX ix_area (m_area)
);


INSERT INTO member (m_id, m_name, m_area) VALUES (12, '홍길동', '서울);
COMMIT;
```
- 이후 MEMBER 테이블에 UPDATE 문장이 실행되면
```
UPDATE member SET m_area='경기' WHERE m_id=12;
```
- InnoDB 버퍼 풀에는 m_area가 '경기' 인 업데이트 된 데이터가 기록
- Undo 로그에는 기존의 m_area가 '서울'인 데이터가 백업되어 기록된다
- 실제 디스크 영역에는 '경기'일수도 있고 '서울'일 수도 있는데 이는 InnoDB 버퍼 풀의 내용이 일정 주기로 모아서 기록되기 때문에 그렇습니다 ( 체크포인트 or Write 쓰레드에 의해 )
- 이때 아직 COMMIT이나 ROLLBACK이 되지 않은 상태에서 다른 사용자가 조회 쿼리로 작업 중 인 레코드를 조회하면?
```
SELECT * FROM member WHERE m_id=12;
```
- 답은 격리 수준에 따라 달라진다
  - 격리 수준 READ_UNCOMMITTED 인 경우
    - InnoDB 버퍼 풀이나 데이터 파일로부터 아직 COMMIT되지 않은 데이터를 읽어서 반환한다 ( '경기' )
    - Dirty Read 발생
  - 격리 수준 READ_COMMITTED 이상 인 경우
    - 아직 COMMIT되기 이전의 데이터가 기록되어 있는 Undo 영역의 데이터를 반환 ( '서울' )
최종적으로 UPDATE가 정상적으로 COMMIT이 되면 InnoDB 버퍼풀과 데이터 영역에 반영되어 있는 내용으로 영구 저장이 되며 ROLLBACK이 되면 Undo 영역의 데이터를 다시 InnoDB 버퍼 풀로 복구한다
ROLLBACK이 되어 데이터가 복구되면 더이상 해당 Undo 영역을 필요로 하는 트랜잭션이 없는 경우에 Undo 영역 데이터가 삭제된다

7-7. 잠금 없는 일관된 읽기 ( Non-locking consistent read )

격리 수준이 SERIALIZABLE 이하인 경우 ( READ-UNCOMMITTED, READ_COMMITTED, REPEATABLE-READ ) INSERT와 연결되지 않은 순수한 읽기 ( SELECT ) 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행된다
특정 사용자가 레코드를 변경하고 아직 커밋을 수행하지 않았다 하더라도 이 변경 트랜잭션이 다른 사용자의 SELECT 작업을 방해하지 않는다 ( 잠금 없는 일관된 읽기 )
Undo 로그를 사용하여 해당 매커니즘을 지원한다
오랜 시간 동안 활성 상태인 트랜잭션으로 인해 MySQL 서버가 느려지거나 문제가 발생할 때가 가끔 있는데, 바로 이러한 일관된 읽기를 위해 언두 로그가 삭제되지 못하고 유지되기 때문이다
트랜잭션은 가능한 작게 유지되야하며, 커밋 혹은 롤백을 가능한 빨리 처리해야한다

8. MySQL 로그 파일

8-1. 에러 로그 파일

MySQL이 실행되는 도중에 발생하는 에러나 경고 메시지가 출력되는 로그 파일
MySQL config vkdlfdp "log_error"라는 이름의 파라미터로 정의된 경로
출력 메시지 정보
- MySQL이 시작하는 과정과 관련된 정보성 및 에러 메시지
- 마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB의 트랜잭션 복구 메시지
- 쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지
- 비정상적으로 종료된 커넥션 메시지
- InnoDB의 모니터링 명령이나 상태 조회 명령의 결과 메시지
- MySQL의 종료 메시지

8-2. 제너럴 쿼리 로그 파일

가끔 MySQL 서버에서 실행되는 쿼리로 어떤 것들이 있는지 전체 목록을 뽑아서 검토해 볼 때가 있는데, 이떄 쿼리 로그를 활성화해서 쿼리를 쿼리 로그 파일로 기록하게 하여 검토할 수 있다
시간 단위로 실행됐던 쿼리의 내용이 모두 기록된다
제너럴 쿼리 로그는 실행되기 전에 MySQL이 쿼리 요청을 받으면 바로 기록한다 ( 에러가 발생해도 로그 파일에 기록 )
"general_log_file" 파라미터로 정의된 경로로 기록

8-3. 슬로우 쿼리 로그

MySQL 서버의 쿼리 튜닝은 크게 서비스가 적용되기 전에 전체적으로 튜닝하는 경우와 서비스 운영 중에 MySQL 서버의 전체적인 성능 저하를 검사하거나 또는 정기적인 점검을 위한 튜닝으로 나눌 수 있다
서비스에서 사용되고 있는 쿼리 중에서 어떤 쿼리가 문제인지 판단하는 데 슬로우 쿼리 로그가 상당히 많은 도움이 된다
슬로우 쿼리 로그 파일에는 Configuration 파일에 정의한 시간(long_query_time 파라미터, 초단위) 이상의 시간이 소요된 쿼리가 모두 기록된다
쿼리가 정상적으로 실행이 완료되어야 슬로우 쿼리 로그에 기록될 수 있다

즉 정상적으로 쿼리가 실행 완료되었고, 수행 시간이 long_query_time에 정의된 초보다 많이 걸린 쿼리가 기록된다

# MySQL 5.1 미만
long_query_time = 1
log_slow_queries = {path}

# MySQL 5.1 이상
log-output = FILE 또는 TABLE
slow-query-log = 1
long_query_time = 1
slow_query_log_file = {path}

슬로우 쿼리 로그 분석
```
# Slow Query log

Time: 110202 12:13:14
User@Host: root[root] @ localhost []
Query_time: 15.407663 Lock_time: 0.000197 Rows_sent: 0 Rows_examined: 5
update tab set fd=100 where fd=10;
```
- Time은 쿼리가 종료된 시점
- User@Host: 쿼리를 실행한 사용자의 계정
- Query_time: 쿼리가 실행되는 데 걸린 전체 시간
- Rows_examined: 쿼리가 처리되기 위해 몇 건의 레코드에 접근했는지를 의미
- Rows_sent: 실제 몇 건의 처리 결과를 클라이언트로 보냈는지 의미
  - 일반적으로 Rows_examined가 높은 반면 Rows_sent가 상당히 적다면 쿼리가 조금 더 작은 범위의 레코드에 접근하도록 튜닝해 볼 가치가 있다 ( Group by, COUNT(), MIN() 등과 같은 집합 함수가 아닌 경우에만 )

8-4. 바이너리 로그와 릴레이 로그

바이너리 로그와 릴레이 로그의 용도나 목적은 MySQL 복제 구조를 위해 사용된다
바이너리 로그는 마스터 MySQL 서버에서, 릴레이 로그는 슬레이브 MySQL 서버에서 생성된다
둘다 파일 포맷은 동일하다

728x90

저작자표시

« 2024/04 »
일	월	화	수	목	금	토
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30

Day 1. MySQL 아키텍처

1. MySQL의 전체 구조

1-1. MySQL 엔진

1-2. 스토리지 엔진

1-3. 핸들러 API

2. MySQL 스레드 구조

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

4. 쿼리 실행 구조

5. 복제(Replication)

5-1. Master

5-2. Slave

5-3. 주의사항

6. 쿼리 캐시

7. InnoDB 스토리지 엔진

7-1. InnoDB 스토리지 엔진의 특성

7-2. InnoDB 버퍼 풀

7-3. Undo 로그

7-4. 인서트 버퍼(insert Buffer)

7-5. Redo 로그 및 로그 버퍼

7-6. MVCC

7-7. 잠금 없는 일관된 읽기 ( Non-locking consistent read )

8. MySQL 로그 파일

8-1. 에러 로그 파일

8-2. 제너럴 쿼리 로그 파일

8-3. 슬로우 쿼리 로그

8-4. 바이너리 로그와 릴레이 로그

티스토리툴바