스터디/MySQL

MySQL 스터디 - 아키텍처 2부

황동리 2025. 6. 28. 22:43
반응형

Real MySQL 8.0 1권 <= 해당 책 내용을 바탕으로 스터디를 진행하였습니다.

1. InnoDB 스토리지 엔진 아키텍처

앞서 1부에서는 MySQL 엔진의 전체적인 구조를 살펴보았습니다.

이번에는 MySQL의 스토리지 엔진 가운데 가장 많이 사용되는 InnoDB 스토리지 엔진에 대해 살펴보겠습니다.



위 그림은 InnoDB의 개략적인 구조 입니다.

1-1. 프라이머리 키에 의한 클러스터링

프라이머리 키에 의한 클러스터링(Clustering by Primary Key)은 InnoDB 스토리지 엔진의 핵심적인 데이터 저장 방식입니다.

InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링 되어 저장됩니다.

즉, 프라이머리 키 값의 순서대로 디스크에 저장됩니다.

모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용합니다.

예시를 들면, 아래 SQL 문으로 테이블을 생성하면 이 경우, id가 프라이머리 키이므로 users 테이블은 다음과 같이 클러스터형 인덱스로 저장됩니다.

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

1-2. 외래 키 지원

외래 키에 대한 지원은 InnoDB 스토리지 엔진 레벨에서 지원하는 기능 입니다.

여기서 외래 키란?

외래 키(Foreign Key)는 두 테이블 간의 관계를 정의하고, 데이터의 무결성(참조 무결성)을 유지하기 위한 제약 조건


외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는 지 체크 해야합니다.

그렇지 않으면 그로 인해 데드락이 발생할 수 있습니다.

외래 키를 생성하는 예시를 들어보면 아래와 같습니다.

CREATE TABLE departments (
  dept_id INT PRIMARY KEY,    
  name VARCHAR(100)
);

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  name VARCHAR(100),
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id) # departments의 Primary Key를 사용하도록 정의
);


그래서 서비스에 문제가 있어서 긴급하게 뭔가 조치를 해야할 때, foreign_key_checks 시스템 변수를 OFF 로 설정 해두면 외래 키 관계에 대한 체크 작업을 일시적으로 멈출 수 있습니다.

mysql> SET foreign_key_checks=OFF;

1-3. MVCC (Multi Version Concurrency Control)

✅ MVCC란?
MVCC는 데이터의 여러 버전을 관리하여, 트랜잭션 간에 읽기/쓰기 충돌 없이 동시성을 보장하는 방법입니다.

예를 들자면,

  • A 트랜잭션: 어떤 값을 읽고 있음
  • B 트랜잭션: 같은 값을 수정하고 있음

그런데도 A는 예전 값을, B는 바뀐 값을 각각 사용할 수 있어요.

이렇게 각각 "다른 시점의 데이터"를 버전처럼 따로 관리하는 게 MVCC 입니다.

이걸 비유로 들어보자면,

📦 도서관 예시

  • 트랜잭션 = 책을 읽는 사람
  • 데이터 = 책의 내용

A가 책 내용을 수정하고 있는 동안, B는 그 책을 읽고 있어요.

그런데 B는 수정 전 버전의 책 내용을 계속 볼 수 있어요.

바로 이게 MVCC의 핵심입니다.

실제 코드 예시로 더 자세히 알아보겠습니다.

-- 트랜잭션 A
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;

-- 트랜잭션 B
START TRANSACTION;
SELECT balance FROM account WHERE id = 1;  -- 변경 전 값 조회됨

---
1. 트랜잭션 A가 아직 COMMIT하지 않았기 때문에,
2. 트랜잭션 B는 변경 전의 balance 값을 MVCC로부터 읽어옵니다.

결론, 트랜잭션 A가 실제로 데이터 페이지를 변경했더라도,
트랜잭션 B에게는 보이지 않도록(MVCC 스냅샷) 되어 있기 때문입니다.

1-4. 자동 데드락 감지

  • ✅ 데드락(Deadlock)이란?
    두 개 이상의 트랜잭션이 서로가 가진 락을 기다리면서 무한히 대기하는 상태를 말합니다.

  • ✅ 자동 데드락 감지란?
    MySQL(InnoDB)은 이런 상황이 발생하면 자동으로 감지해서
    👉 둘 중 하나의 트랜잭션을 강제로 ROLLBACK 시킵니다.
    이를 통해 나머지 트랜잭션은 계속 진행할 수 있게 됩니다

예시를 통해 알아보겠습니다.

mysql> CREATE TABLE demo (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

mysql> INSERT INTO demo VALUES (1, 'A'), (2, 'B');

mysql> START TRANSACTION;
mysql> UPDATE demo SET name = 'X' WHERE id = 1;
-- 🔒 id=1 row 잠김

mysql> START TRANSACTION;
mysql> UPDATE demo SET name = 'Y' WHERE id = 2;
-- 🔒 id=2 row 잠김

mysql> UPDATE demo SET name = 'X2' WHERE id = 2;
-- ❗ 트랜잭션 2번이 이미 id=2을 잠갔기 때문에 대기

mysql> UPDATE demo SET name = 'Y2' WHERE id = 1;
-- ❗ 트랜잭션 1번이 이미 id=1을 잠갔기 때문에 대기

💥 결과: 데드락 발생!
- 트랜잭션 1: id=1 잠금 → id=2 대기
- 트랜잭션 2: id=2 잠금 → id=1 대기

🔁 서로가 서로의 락을 기다림 → MySQL이 감지해서 둘 중 하나를 ROLLBACK

이런 식으로 자동으로 데드락을 감지해서 롤백을 시키는데,

동시 처리 스레드가 매우 많아지면 데드락 감지 스레드는 많은 CPU 자원을 소모하게 됩니다.

이런 문제점을 해결하기 위해, MySQL은 innodb_deadlock_detect 시스템 변수를 제공하고, 해당 변수를 OFF로 설정하면 데드락 감지 스레드는 작동하지 않습니다.

하지만 해당 시스템 변수를 꺼두면 무한 대기 상태가 발생을 하니,
innodb_lock_wait_timeout 의 설정 값을 낮게 설정하여 대기 시간을 줄이는 방식으로 같이 사용 해야 합니다.

1-5. InnoDB 버퍼 풀

InnoDB 스토리지 엔진에서 가장 핵심적인 부분 입니다.

  • 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시 해두는 공간.
  • 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 합니다.

MySQL 5.7 부터 버퍼 풀을 동적으로 조절할 수 있게 되었습니다.

✅ 기본 설정 방법
MySQL 설정 파일 (my.cnf 또는 my.ini)에서 설정합니다:

[mysqld]
innodb_buffer_pool_size = 4G

✅ 일반적인 권장 기준

서버 유형 권장 비율
전용 DB 서버 전체 RAM의 70~80%
다른 앱도 함께 사용 전체 RAM의 50% 이하

1-6. Double Write Buffer

  • ✅ Doublewrite Buffer란?
    InnoDB는 데이터를 디스크에 쓸 때 바로 데이터 파일에 쓰지 않고,
    먼저 중간 영역인 doublewrite buffer에 한 번 더 안전하게 써둡니다.

그리고 그게 문제 없으면 → 진짜 데이터 파일에 반영합니다.

  • 📌 이런 Double-Write 기법을 사용하는 이유

문제 상황: 디스크에 쓰는 중간에 전원이 나가면?

  • 데이터 파일 일부만 써졌을 경우 → 파일 시스템 손상
  • 페이지 하나라도 반쯤 써지면 → 깨진 데이터가 DB에 남게 됨
  • 심하면 MySQL 자체가 안 뜰 수도 있음

해결: doublewrite buffer

  • 임시 공간에 먼저 다 쓰고, OK 나면 본 파일에 저장

중간에 멈춰도 → 그 임시 공간(doublewrite)에 있는 정상 버전으로 복구 가능

  • 🔁 동작 순서
  1. 데이터를 변경
  2. 변경된 페이지를 Doublewrite Buffer에 먼저 씀
  3. 문제 없으면 → 실제 ibdata1 등의 데이터 파일에 최종 쓰기
  4. 시스템 크래시 발생 시 → Doublewrite Buffer에서 정상 페이지만 골라 복구 가능

기본적으로 기본적으로 활성화되어 있고, MySQL이 알아서 처리합니다.

따라서 저희는 이런 기법을 사용한다. 라고 알아두면 됩니다.

2. MySQL 로그 파일

MySQL에서 제공하는 로그 파일들을 이용하면 MySQL 서버의 깊은 내부 지식이 없어도 MySQL의 상태나 부하를 일으키는 원인을 쉽게 찾아서 해결 할 수 있습니다.

2-1. 에러 로그 파일

MySQL이 실행되는 도중 발생하는 에러나 경고 메시지가 출력되는 로그 파일 입니다.


주로 MySQL의 설정 파일 my.cnf 에서 log_error 라는 이름의 파라미터로 정의된 경로에 생성 됩니다.


혹은, MySQL 설정 파일에 별도로 정의되지 않은 경우에는 데이터 디렉터리(datadir 파라미터에 설정된 디렉터리)에 .err 라는 확장자가 붙은 파일로 생성 됩니다.


MySQL의 설정 파일을 변경하거나 데이터베이스가 비정상적으로 종료된 이후 다시 시작하는 경우에는 반드시 MySQL 에러 로그 파일을 통해 설정된 변수의 이름이나 값이 명확하게 설정되고 의도한 대로 적용되었는지 확인해야 합니다.

2-2. 제네럴 쿼리 로그 파일(General log)

  • ✅ 제너럴 쿼리 로그란?
    MySQL 서버에 들어온 모든 클라이언트의 요청(쿼리)을 기록하는 로그입니다.
    SELECT, INSERT, UPDATE, DELETE 뿐 아니라
    접속/연결/쿼리 실행 등 거의 모든 동작을 남깁니다.

📦 예를 들어 이런 것들이 기록됩니다

  • 클라이언트 접속/종료
  • SQL 쿼리 실행 기록
  • 스토어드 프로시저 호출
  • Prepare/Execute 같은 명령도 포함

기본적으로 my.cnf 또는 my.ini 설정 파일에서 설정할 수 있습니다:

-- 제너럴 로그 켜기
SET GLOBAL general_log = 'ON';

-- 로그 파일 위치 확인
SHOW VARIABLES LIKE 'general_log_file';

아니면 mysql 서버에 접속해서 명령어로 확인 할 수도 있습니다.

mysql> SHOW GLOBAL VARIABLES LIKE 'general_log_file';
+------------------+-----------------------------------------+
| Variable_name    | Value                                   | 
+------------------+-----------------------------------------+
| general_log_file | /usr/local/mysql/data/localhost_matt.log|
+------------------+-----------------------------------------+

제너럴 로그 파일의 내용이 상당히 많아서 직접 쿼리를 하나씩 검토하기 힘들 때,

Percona Toolkit의 pt-query-digest 스크립트를 이용하여 쿼리를 정렬해서 볼 수 있습니다.

linux> pt-query-digest --type='genlog' general.log > parsed_general.log

2-3. 슬로우 쿼리 로그

  • ✅ 슬로우 쿼리 로그란?
  • *"실행 속도가 느린 쿼리만 골라서 기록해주는 로그"**입니다.

즉, MySQL이 "느리다고 판단한 쿼리"만 따로 기록해주는 기능입니다.

디버깅과 성능 튜닝에 매우 유용합니다.

long_query_time 이라는 설정을 사용하여 사용자가 시간을 정해놓고 슬로우 쿼리로 분류 할 수 있습니다.

long_query_time = 1
=> 1초 이상 걸린 쿼리만 슬로우 쿼리 로그에 기록
  • 슬로우 쿼리 설정 방법
    # 설정 확인
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

슬로우 쿼리 켜기 및 시간 설정

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

슬로우 쿼리 로그 파일 위치 확인

SHOW VARIABLES LIKE 'slow_query_log_file';


앞서 제너럴 로그와 마찬가지로 슬로우 쿼리의 양이 많을 때, pt-query-digest 스크립트를 이용하여 쿼리를 정렬해서 볼 수 있습니다.
```
linux> pt-query-digest --type='slowlog' mysql-slow.log > parsed_mysql-slog.log

이상 입니다.

반응형

'스터디 > MySQL' 카테고리의 다른 글

MySQL 스터디 - 트랜잭션과 잠금  (2) 2025.07.06
MySQL 스터디 - 아키텍처 1부  (1) 2025.06.26
MySQL 스터디 - 사용자 및 권한  (0) 2025.06.20

'스터디/MySQL'의 다른글

  • 현재글MySQL 스터디 - 아키텍처 2부

관련글

티스토리툴바