MySQL Concept & Structure 블랙박스 뽀개기

Mysql 아키텍처

• mysql은 모듈식 아키텍처 형태로 설계되었다.
  • 커넥터 레이어
    • 해당 레이어에서 다양한 커넥터 (드라이버)를 제공한다.
    • client가 mysql 서버에 연결할 수 있도록 지원한다.
    • client 요청은 sql 형태로 전달된다.
  • SQL 레이어
    • sql parser와 optimizer가 해당 레이어에서 동작하낟.
    • query를 분석 및 최적화하고, 실행 계획을 수립한다.
    • 데이터 접근 및 처리를 위한 논리적 인터페이스 역할을 한다.
  • 스토리지 엔진
    • 실제 데이터는 스토리지 엔진이 관리한다.
    • mysql은 plugin 방식으로 다양한 스토리지 엔진을 지원하고, 대표적으로 innodb, myisam이 있다.
    • 각 엔진별로 데이터 read/ write 및 indexing 방식이 다르다.
    • innodb
      • mysql의 default 엔진이다.
      • acid 특성과 트랜잭션을 지원한다.
      • mvcc를 통해 동시성을 지원한다.
      • 데이터와 인덱스를 클러스터링 방식으로 저장한다.
    • myisam
      • 과거 mysql 기본 엔진이다.
      • 간단하고 빠른 읽기 작업에 최적화되어있다.
      • 트랜잭션이나 fk 등을 지원하지않는다.
      • table level lock을 사용하므로 동시성이 매우 낮다.
• 스레딩 방식
  • 멀티 스레드 기반 아키텍처로 작동한다
  • 클라이언트에 대해 별도의 스레드를 생성한다
  • 각 스레드는 독립적으로 쿼리를 처리하고, 결과를 client에 반환한다.
  • 스레드 풀을 이용하여 스레드를 효율적으로 관리할 수 있다.

 

 

MVCC (Multi Version concurrency Control)

• 레코드 레벨의 트랜잭션을 지원하는 dbms가 제공하는 기능이다.
• 사용하는 가장 큰 목적은 잠금을 사용하지않는 일관된 읽기를 제공하는데 있다.
• InnoDB는 Undo Log를 이용해 이 기능을 구현한다.
• 멀티 비전?
  • 하나의 레코드에 대해 여러개의 버전이 동시에 관리된다는 의미임.

예시) 격리 수준 READ_COMMITED인 경우 테이블 데이터 변경이 되는 과정.

CREATE TABLE user (
	id INT NOT NULL,
    name VARCHAR(20) NOT NULL,
    address VARCHAR(20) NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_name (name)
);

INSERT INTO user (id, name, address) VALUES (1, '정균', '인천');
COMMIT;

위 Insert 문을 실행하면 db는 아래와 같은 상태가 된다.

 

 

 

위 상태에서 아래의 UDPATE 문을 실행하면 다음과 같이 변경된다.

UPDATE user SET name = "Eddy" WHERE id = 1

 

 

• UPDATE 명령이 실행되면 커밋 여부와 관계없이 innodb 버퍼 풀은 변경 값으로 업데이트 된다.
• 그리고 디스크에는 innodb의 write 스레드에 의해 새로운 값으로 업데이트 되어있을 수 있고, 아닐 수도 있다.
  • innodb가 acid를 보장하기때문에 일반적으로는 버퍼 풀과 디스크는 동일한 상태로 봐도 무방함.

아직 커밋이나 롤백이 되지 않은 상태에서 다른 사용자가 아래 쿼리로 조회하면 어디에 있는 데이터를 조회할까?

SELECT * FROM user WHERE id = 1;

 

• 답은 MySQL 서버의 시스템 변수(transaction_isolation)에 설정된 격리 수준에 따라 다르다.
• 격리 수준이 READ_UNCOMMITED인 경우에는 InnoDB 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환한다.
  • 즉, 데이터가 커밋됐든 아니든 변경된 상태의 데이터를 반환한다.
• READ_COMMITED나 그 이상의 Level인 경우 아직 커밋되지않았기때문에 InnoDB 버퍼 풀이나 데이터 파일에 있는 내용 대신 변경되기 이전의 내용을 보관하고있는 언두 영역의 데이터를 반환한다.
  • 즉, 하나의 레코드(id 1인 레코드)에 대해 2개의 버전이 유지되는것이고, 만약 이 상태에서 Commit 명령을 수행하면 InnoDB는 더 이상의 변경 작업없이 지금의 상태를 영속 데이터로 만들어버린다.
  • 만약 Rollack 명령을 실행하면 InnoDB는 언두 영역에 있는 백업된 데이터를 InnoDB 버퍼 풀로 다시 복구하고, 언두 영역의 내용을 삭제해버린다.
  • Commit이 된다고 언두 영역의 백업 데이터가 항상 바로 삭제되는것은 아니고, 언두 영역을 필요로 하는 Tx이 더는 없을 때 비로소 삭제된다.

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

 

 

 

 

 

자동 데드락 감지

• InnoDB SE는 내부적으로 잠금(Lock)이 교착상태에 빠지지않았는지 체크하기 위해 잠금 대기 목록을 그래프(Wait-For List) 형태로 관리한다고 한다.
• InnoDB SE는 데드락 감지 스레드를 가지고있는데, 이 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 Tx를 찾아서 그 중 하나를 강제 종료시킨다.
  • 이 때, 어느 Tx를 먼저 강제 종료할것인지를 판단하는 기준은 Tx의 언두 로그 양으로 판단한다.
  • 언두 로그 레코드를 더 적게 가진 Tx가 일반적으로 롤백의 대상이 된다.
  • 언두 레코드를 적게 가졌다는 이야기는 롤백을 해도 언두 처리를 해야할 내용이 적다는 것이고, 강제 롤백으로 인한 MySQL 서버의 부하도 덜 유발하기 때문이라고한다.
• 자동 데드락 감지 작업이 크게 부담되진 않으나, 동시 처리 스레드가 매우 많아지거나 Tx가 가진 잠금의 개수가 많아지면 데드락 감지 스레드가 느려질 수 있다.
  • 그래서 MySQL에서는 innodb_deadlock_detect 시스템 변수를 제공하여 Detect를 OFF 시킬 수 있도록 제공도한다.
  • OFF해서 스레드가 작동하지 않으면 DeadLock이 발생해도 중재하지못해 무한정 대기 상태에 빠진다.
    • 그래서 Innodb_lock_wait_timeout 시스템 변수를 활성화하면 DeadLock 상황에서 일정 시간이 지나면 자동으로 요청이 실패하고 Exception을 발생시킬 수 있다.
    • 그래서 innodb_deadlock_detect를 OFF한다면, Innodb_lock_wait_timeout의 기본값인 50초보다 훨씬 낮은 시간으로 변경해서 사용할 것을 권장한다고한다.

 

 

언두 로그(Undo Log)

위에서도 많이 언급된 언두 로그에 대한 개념에 대해 이해해보자

• InnoDB SE는 Tx와 격리 수준을 보장하기 위해 DML로 변경되기 이전의 데이터를 별도로 백업한다.
• 이렇게 백업된 데이터를 언두 로그(Undo Log)라고 한다.

트랜잭션 보장

• Tx가 롤백되면 Tx도중 변경된 데이터를 변경 전 데이터로 복구해야 하는데, 이 때 언두 로그에 백업해둔 이전 버전의 데이터를 이용해 복구한다.

격리 수준 보장

• 특정 커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 데이터를 조회하면 Tx 격리 수준에 맞게 변경중인 레코드를 읽지않고 언두 로그에 백업해둔 데이터를 읽어서 반환한다.

 

언두 로그가 사용되는 2가지

1. Tx 롤백 대비용으로 사용된다.
2. Tx의 격리 수준을 유지하면서 높은 동시성을 제공하는데 있다. (MVCC)

 

• MySQL 5.5 이전 버전 때는 언두 로그 저장 공간이 다시 줄어들지 않았다고한다.
  • 만약, 1억건 레코드가 저장된 100GB 크기의 테이블을 DELETE로 삭제한다면, 언두 로그 공간 100GB를 계속 먹고있게되는셈이였던거다.
  • (5.7 버전 이후로 업그레이드되면서 언두 로그 공간의 문제점은 완전 해결됐다고함)
• 그 이후 버전이라고해서 커밋된다고 바로 언두로그가 삭제되는건 아니다.
  • Tx이 오랜 시간 실행될 때에도 언두 로그 양은 급격히 증가할 수 있다.

아래 명령을 통해 언두 로그 레코드 수를 확인할 수 있다고함.

mysql> SHOW ENGINE INNODB STATUS \G

 

 

 

 

 

리두 로그(Redo Log)

• 리두로그(Redo Log)sms ACID에서 영속성(Durable)과 가장 밀접하게 연관되어있다.
  • 일부 DBMS에서는 리두 로그를 WAL(Write Ahead Log)이라 해서 데이터를 디스크에 기록하기 전에 기록되는 로그의 의미로도 사용된다.
• 하드웨어나 소프트웨어 등 여러 가지 문제점으로 인해 MySQL 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지않게 해주는 안정장치이다.

MySQL 서버가 비정상 종료되는 경우 InnoDB SE의 데이터 파일은 크게 두가지 종료의 일관되지 않은 데이터를 가질 수 있다.

1. Commit 되었지만 데이터 파일에 기록되지 않은 데이터
2. Rollback 되었지만 데이터 파일에 이미 기록된 데이터

• 1번의 경우 리두 로그에 저장된 데이터를 데이터 파일에 다시 복하기만 하면 된다. 하지만 2번의 경우는 리두 로그로는 해결할 수 없다.
• 이때는 변경되기 전 데이터를 가진 언두 로그의 내용을 가져와 데이터 파일에 복사하면 된다. 그렇다고 리두 로그가 아예 필요없는것은 아니고, 최소한 그 변경이 커밋됐는지, 롤백됐는지, 트랜잭션의 실행 중간 상태였는지를 파악하려면 리두 로그도 결국 필요하다.
• 그래서 DB Server에서 리두 로그는 트랜잭션이 커밋되면 즉시 디스크로 기록되도록 시스템 변수를 설정하는것을 권장한다.
  • 당연히 요렇게 되어야지 서버가 비정상적으로 종료됐을 때 직전까지의 Tx Commit 내용이 리두 로그에 기록될 수 있고 이 리두 로그를 이용해 장애 직전 시점까지의 복구가 가능해지기 때문이다.
  • 근데 Commit 될 때마다 디스크에 기록하는 작업은 당연히 많은 부하를 유발한다..
  • 그래서 InnoDB SE에서 리두 로그를 어느 주기로 디스크에 동기화할지 결정하는 innodb_flush_log_at_trx_commit 시스템 변수를 제공한다. (3가지 설정 값이 있는데 생략함)

 

슬로우 쿼리 로그

서비스 운영중에 MySQL 서버의 전체적인 성능 저하를 검사하거나 정기적인 점검을 위한 튜닝을 할 때 유용한 로그이다.

• 슬로우 쿼리 로그 파일에는 long_query_time 시스템 변수에 설정한 시간(마이크로 초 단위로도 설정 가능) 이상의 시간이 소요된 쿼리가 모두 기록된다.
• 슬로우 쿼리 로그 파일에는 정상적으로 실행이 완료돼야 슬로우 쿼리에 기록될 수 있다.
  • 슬로우 쿼리 로그 파일에는 일단 정상적으로 실행이 완료됐고, 실행하는데 걸린 시간이 long_query_time에 정의된 시간보다 많이 걸린 쿼리만 존재하게된다.
• log_output 옵션을 이용해 슬로우 쿼리 로그를 파일로 기록할지, 테이블로 기록할지 선택할 수 있다.
  • 만약 옵션을 TABLE로 설정하면, 제너럴 로그, 슬로우 쿼리 로그를 mysql DB의 테이블인 general_log, slow_log table에 저장한다.
  • FILE로 설정하면, 로그의 내용을 디스크의 파일로 저장한다

슬로우 쿼리는 아래와 같이 기록된다.

# Time: 2024-11-16T12:26:10 14324+09:00
# User@Host: root[root] @ localhost [] ...
# Query_time 1.180245    Lock_time 0.002658    Rows_sent: 1    Rows_examined: 2844047
use payments;
SET timestamp=...
select transcation, item from purchases;

 

• Time
  • 항목은 쿼리가 종료된 시점을 의미한다.
  • 그래서 쿼리가 언제 시작됐는지 확인하려면 Time - Query_time만큼 빼서 확인해야한다.
• User@Host
  • 쿼리를 실행한 사용자의 계정이다
• Query_time
  • 쿼리가 실행되는 걸린 전체 시간을 의미한다.
• Lock_time
  • MySQL 엔진 레벨에서 관장하는 테이블 잠금에 대한 대기 시간만 표현한다.
  • 예제의 경우 0.002658초간 테이블 락을 기다렸다는것으로 해석할 수 있으나, 0이 아니라고 무조건 잠금 대기가 있었다고 판단하기는 어렵다.
  • 표기된 시간은 실제 쿼리가 실행되는데 필요한 잠금 체크와 같은 코드 실행 부분의 시간까지 모두 포함되기때문이다.
  • 그래서 요 값이 매우 작은 값이면 무시해도 무방하다.
• Rows_examined
  • 해당 쿼리가 처리되기위해 몇 건의 레코드에 접근했는지를 의미한다.
  • 레코드 건수가 높지만 Rows_sent에 표시된 레코드 건수가 적다면 이 쿼리는 조금 더 적은 레코드만 접근하도록 튜닝해볼 가치가 있다는 뜻이다.
• Rows_sent
  • 실제 몇 건의 처리 결과를 클라이언트로 보냈는지를 의미한다.

참고) 그런데 슬로우 쿼리 또는 제너럴 로그 파일의 내용이 상당히 많기때문에 직접 쿼리를 하나씩 까보기는 비용이 많이 든다. 그래서 Percona Toolkit의 pt-query-digest script를 이용하면 쉽게 처리 성능별로 쿼리를 정렬해서 살펴볼 수 있다고함. (자세한 내용은 생략)

 

 

 

 

----

네임드 락

자동 증가 락

인덱스와 잠금

격리 수준