[RealMySQL 8.0] 04. MySQL 아키텍처(4.3 MyISAM 스토리지 엔진 아키텍처, 4.4 MySQL 로그 파일)

04. 아키텍처

4.3 MyISAM 스토리지 엔진 아키텍처

MyISAM 스토리지 엔진의 성능에 영향을 미치는 요소인 키 캐시와 운영체제의 캐시/버퍼에 대해 보자.

4.3.1 키 캐시

InnoDB의 버퍼 풀과 유사한 기능을 한다. 하지만 MyISAM 키 캐시는 인덱스만 대상으로 동작하며, 데이터는 캐시 하지 않는다. 디스크 쓰기에 대해서도 부분적인 버퍼링 역할만 수행한다.

 

키 캐시 히트율(Hit rate) = 100 - (Key_reads / Key_read_requests * 100)

키 캐시가 얼마나 효율적으로 작동하는지 간단히 확인할 수 있는 수식이다. Key_reads는 디스크에서 인덱스를 읽은 횟수, Key_read_requests는 키 캐시에서 인덱스를 읽은 횟수이다. 일반적으로 99% 이상 유지를 권장하며, 미만이면 key_buffer_size 확장 고려하는 것이 좋다.

 

32비트 OS는 키 캐시 크기 4GB 제한한다. 64비트 OS는 OS_PER_PROCESS_LIMIT 설정 값까지 가능하다. 제한 값 이상의 키 캐시를 할당하고 싶다면 기본 키 캐시 이외에 별도의 이름이 붙은 키 캐시 공간을 설정해야 한다. 

key_buffer_size = 4GB
kbuf_board.key_buffer_size = 2GB
kbuf_comment. key_buffer_size = 2GB

• 기본 키 캐시: key_buffer_size = 4GB
• 명명된 추가 키 캐시 (Named Key Cache) → 위와 같이 설정하면  kbuf_board, kbuf_comment라는 키 캐시가 2GB씩 생성된다. 하지만 기본 캐시 외의 영역은 명시적으로 어떤 인덱스를 캐시할지 지정해야 한다. 아닐 경우 할당만 하고 사용하지 않게 된다.

CACHE INDEX db1.board, db2.board IN kbuf_board;
CACHE INDEX db1.comment, db2.comment IN kbuf_comment;

→ 이렇게 해야 board 테이블 인덱스는 kbuf_board 키 캐시를, comment 테이블 인덱스는 kbuf_comment 키 캐시를 사용할 수 있다.

 

4.3.2 운영체제의 캐시 및 버퍼

MyISAM은 데이터 자체를 캐시하지 않는다. (키 캐시를 통해 인덱스만 캐시 한다.) 따라서 데이터 읽기/쓰기 작업은 OS 디스크 I/O에 의존한다. 다행히 대부분의 운영체제는 파일에 대한 캐시/버퍼 기능이 있다. 같은 파일을 다시 읽을 때는 디스크가 아니라 메모리에서 가져온다.

 

하지만 운영체제의 캐시 기능은 InnoDB처럼 데이터 특성을 고려한 지능적 캐시나 버퍼링을 하진 못한다.

• OS 캐시 기본 원칙: 남는 메모리를 활용
• ex) 전체 메모리 8GB 중 MySQL이나 다른 애플리케이션에서 전부 사용하면 OS는 캐시 공간을 확보 못함 → MyISAM는 캐시 하지 못하고, 이는 결국 성능 저하로 이어짐

따라서 적절한 메모리 분배가 필요하다. MyISAM을 주로 사용할 경우, 키 캐시는 전체 메모리의 40% 이하로 설정하고, 나머지는 OS 캐시로 활용될 수 있도록 비워두는 게 좋다.

 

4.3.3 데이터 파일과 프라이머리 키(인덱스) 구조

• InnoDB: 프라이머리 키 기반 클러스터링 → 데이터가 PK 순서대로 저장됨
• MyISAM: 클러스터링 없음 → 단순히 INSERT 순서대로 힙(Heap)처럼 저장됨

MyISAM 테이블에 저장되는 모든 레코드는 ROWID라는 물리적 주소 값을 가진다.

MyISAM은 실제 레코드가 저장되는 데이터 파일(.MYD) 과, 키 값과 그 키가 가리키는 데이터의 주소(ROWID)가 저장되는 인덱스 파일(. MYI)로 분리되어 있다. 이때 ROWID는 데이터 파일(. MYD) 내에서 해당 레코드가 실제로 저장된 위치(offset)를 의미한다.

즉, 프라이머리 키 인덱스와 세컨더리 인덱스는 모두 데이터 파일에 저장된 레코드의 ROWID를 포인터로 가진다. (인덱스는 실제 데이터를 저장하지 않고, 데이터가 위치한 주소를 참조하는 구조이다.)

 

MyISAM 테이블에서 ROWID 저장 방식은 두가지가 있다.

1. 고정 길이 ROWID
   • 테이블 생성 시 MAX_ROWS 옵션을 지정하면 사용 가능하다.
   • 옵션을 명시하면, MySQL 서버는 최대로 가질 수 있는 레코드가 한정된 테이블을 생성한다.
   • 한정되면, MyISAM 테이블은 ROWID 값으로 4바이트 정수(INSERT된 순번)로 사용한다.
2. 가변 길이 ROWID (기본)
   • MAX_ROWS 옵션 미지정 시 myisam_data_pointer_size 시스템 변수(기본 7바이트)를 사용한다.
   • ROWID는 2~7바이트로 가변적이다. (첫 1바이트는 길이 정보, 나머지는 실제 주소)
   • 기본값 기준 MyISAM 테이블 최대 크기: 256TB
   • 256TB이상 크기의 데이터가 필요할 경우 myisam_data_pointer_size=8 설정 시 최대 64PB 저장 가능하다.

 

4.4 MySQL 로그 파일

MySQL 서버 상태 진단을 위해 다양한 도구가 있지만, 로그 파일만 잘 읽어도 내부 지식 없이 상태·부하 원인을 상당 부분 파악하고 해결할 수 있다. 문제 발생 시 아래에 설명하는 로그들을 습관적으로 점검하면 좋다.

 

4.4.1 에러 로그 파일

MySQL 실행 중 발생하는 에러나 경고 메시지가 기록되는 로그 파일이다. 에러 로그 파일의 위치는 다음과 같다.

• my.cnf의 log_error 파라미터에 지정한 경로
• 미지정 시 datadir(데이터 디렉터리)에 .err 확장자로 생성

4.4.1.1 MySQL이 시작하는 과정과 관련된 정보성 및 에러 메시지

• 설정 변경 후 또는 비정상 종료 뒤 재기동 시 반드시 에러 로그로 적용 여부를 확인한다.
• 정상 기동 여부는 보통 다음과 같은 메시지로 확인한다. mysqld: ready for connections
• 새로 변경/추가한 파라미터에 대해 오류나 경고가 없는지 확인한다.
  • 무시됨(ignored): 서버는 뜨지만 해당 변수는 적용되지 못한 상태
  • 인식 불가/값 오류: 에러 출력 후 서버 기동 실패 가능

4.4.1.2 마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB의 트랜잭션 복구 메시지

• 비정상/강제 종료 후 재시작되면 InnoDB가 미완료 트랜잭션 정리 및 미기록 데이터 재처리를 수행한다.
• 복구 실패 시 에러 로그에 관련 메시지가 출력되고 서버가 재종료될 수 있다.
• 심각한 손상 시 일시적으로 아래 옵션을 사용해 띄운 뒤(필요 최소치부터), 데이터 백업/덤프 후 복구 전략을 취한다.
• innodb_force_recovery = 1 -> 1~6 단계, 높을수록 기능 제한 큼/데이터 손실 위험 증가

4.4.1.3 쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지

• 쿼리 도중 발생하는 문제는 사전 예방이 어려우며, 주기적으로 검토하는 과정에서 알게 된다.
• 쿼리 실행 중 에러, 복제(Replication)에서 문제 소지가 있는 쿼리 경고 메시지가 기록된다.
• 주기적 에러 로그 점검은 숨은 문제(잠재적 성능, 데이터 이슈)를 조기에 발견하는 데 매우 유용하다.

4.4.1.4 비정상적으로 종료된 커넥션 메시지(Aborted connection)

• 대부분 클라이언트 애플리케이션에서 정상적으로 접속 종료를 하지 못하고 프로그램이 종료된 경우에 누적된다.
• 중간에 네트워크 문제로 의도하지 않게 접속이 끊어지는 경우에도 기록된다.
• 이 메시지가 아주 많이 기록된다면 애플리케이션의 커넥션 종료 로직을 한 번 검토해 볼 필요가 있다.
• max_connect_errors 관련
  • max_connect_errors 시스템 변수 값이 너무 낮게 설정된 경우, 클라이언트가 MySQL 서버에 접속하지 못하고 "Host 'hostname' is blocked" 에러가 발생할 수 있다.
  • 이 에러는 클라이언트 호스트에서 발생한 에러(접속 실패나 강제 연결 종료 등)의 횟수가 max_connect_errors 값을 넘으면 발생한다.
  • 이 경우 max_connect_errors 값을 증가시키면 된다. 단, 먼저 이 에러가 어떻게 발생했는지 원인을 살펴보는 것이 좋다.

4.4.1.5 InnoDB의 모니터링 또는 상태 조회 명령(SHOW ENGINE INNODB STATUS 같은)의 결과 메시지

• InnoDB의 테이블 모니터링, 락 모니터링, 엔진 상태 조회 명령은 비교적 큰 메시지를 에러 로그 파일에 기록한다.
• 모니터링을 활성화한 상태로 계속 유지하면 에러 로그가 매우 커져 파일 시스템 공간을 소진할 수 있다.
• 모니터링을 사용한 뒤에는 반드시 비활성화하여 에러 로그가 불필요하게 커지지 않도록 한다.

4.4.1.6 MySQL의 종료 메시지

• 가끔 MySQL이 아무도 모르게 종료되거나 재시작되는 경우가 있다.
• 이때 원인 확인의 유일한 방법은 에러 로그에서 마지막 종료 시 출력된 메시지를 확인하는 것이다.
• 사람이 종료한 경우: 에러 로그에 Received SHUTDOWN from user '...' 메시지가 기록된다.
• 종료 관련 메시지가 없거나, 스택 트레이스(16진수 주소 값 등이 잔뜩 출력)가 보이면 세그멘테이션 폴트(Segmentation fault)로 비정상 종료된 것으로 판단할 수 있다.
• 세그멘테이션 폴트로 비정상 종료된 경우에는 스택 트레이스를 참고해 MySQL 버그 연관 여부를 조사한 뒤 버전 업그레이드 또는 회피책(Workaround)을 찾는 것이 최선이다.

 

4.4.2 제너럴 쿼리 로그 파일(제너럴 로그 파일, General log)

서버에서 실행되는 모든 쿼리의 전체 목록을 뽑아 검토할 때 사용하며, 로그를 활성화하여 쿼리를 로그 파일에 기록한 뒤 그 파일을 검토한다. 쿼리 로그 파일에는 시간 단위로 실행된 쿼리 내용이 모두 기록된다. 슬로우 로그와 달리 실행 전에, MySQL이 쿼리 요청을 받자마자 기록한다. 따라서 실행 중 에러가 발생해도 일단 로그 파일에 기록된다.

 

• mysqld 경로/버전, TCP 포트(예: 3306), Unix 소켓 경로 등이 상단에 출력될 수 있다.
• 레코드는 Time | Id | Command | Argument 형식으로 기록된다.

general_log_file 파라미터에 로그 파일 경로가 설정된다. 또한, 쿼리 로그를 파일이 아닌 테이블에 저장하도록 설정할 수 있으므로 그 경우는 SQL로 테이블을 조회해 검토해야 한다. 

• 파일 또는 테이블에 저장 가능하며, 이는 log_output 파라미터로 결정된다.
• 테이블에 저장한 경우에는 SQL로 테이블을 조회해 내용 검토한다.

 

4.4.3 슬로우 쿼리 로그

쿼리 튜닝은 (1) 서비스 적용 전 전체 튜닝, (2) 운영 중 성능 저하나 정기 점검을 위한 튜닝으로 나뉜다. 후자의 경우 어떤 쿼리가 문제인지 식별하기가 어려운데, 이때 슬로우 쿼리 로그가 큰 도움을 준다.

 

long_query_time 이상 시간이 소요된 쿼리를 기록한다. 쿼리 실행이 끝난 후 실제 소요 시간을 기준으로 기록 여부를 판단하므로, 정상 완료된 쿼리만 기록된다.

 

log_output 옵션을 통해 저장 대상을 선택할 수 있다.

• TABLE로 설정하면 mysql DB의 general_log, slow_log 테이블에 저장된다.
• FILE로 설정하면 디스크의 파일로 저장된다.
• TABLE을 사용해도 두 테이블은 CSV 스토리지 엔진을 사용하므로, 실질적으로 CSV 파일 저장과 동일하게 동작한다.

 

• Time: 쿼리 종료 시각. 시작 시각은 Time - Query_time으로 역산.
• Query_time: 쿼리 전체 실행 시간.
• Lock_time: MySQL 엔진 레벨의 테이블 잠금 대기 시간만 표시. 잠금 체크 등 실행 오버헤드가 포함될 수 있어 아주 작은 값은 무시 가능.
• Rows_examined / Rows_sent: 이 쿼리가 처리되기 위해 접근한 레코드 수와 응답으로 보낸 레코드 수.

 

MySQL의 잠금처리는 MySQL 엔진 레벨과 스토리지 엔진 레벨의 두 가지 레이어로 처리된다. 이때, MyISAM이나 MEMORY 스토리지 엔진과 같은 경우엔 별도의 스토리지 엔진 레벨의 잠금을 가지지 않지만 InnoDB의 경우 MySQL 엔진 레벨과 스토리지 엔진 자체 잠금을 가지고 있다. 

• MyISAM/MEMORY: 테이블 잠금 사용, MVCC 없음 → SELECT도 Lock_time이 1초 이상일 수 있음.
• InnoDB: 레코드 잠금이지만, 간혹 MySQL 엔진 레벨 테이블 잠금 영향으로 Lock_time이 크게 보일 수 있음.
  → InnoDB 전용 쿼리의 Lock_time은 튜닝/분석에 큰 도움이 안 될 수 있음.

일반적으로 슬로우 쿼리, 제너럴 로그 파일의 내용이 많기 때문에 직접하긴 어렵다. 따라서 Percona Toolkit의 pt-query-digest 스크립트를 이용해서 빈도/성능 기준 정렬 및 요약 가능하다.

linux> pt-query-digest --type='slowlog' mysql-slow.log > parsed_mysql-slow.log

분석 결과는 크게 3개 그룹으로 요약된다.

 

4.4.3.1 슬로우 쿼리 통계

• 보고서 최상단에 모든 쿼리를 대상으로 실행 시간(Exec time), 잠금 대기 시간(Lock time) 등의 평균/최소/최대가 표시된다.
• 수집 기간(Time range), 유니크 쿼리 수, QPS, 동시성 등 집계 정보가 함께 출력된다.

4.4.3.2 실행 빈도 및 누적 시간 랭킹

• 쿼리별 응답 시간 합계 또는 실행 횟수 기준 랭킹을 보여준다. (--order-by로 정렬 기준 변경 가능)
• Query ID는 쿼리를 정규화(리터럴 제거)해 생성된 해시로, 같은 형태의 쿼리는 동일 ID를 갖는다.

4.4.3.3 쿼리별 실행 횟수 및 누적 실행 시간 상세 정보

• 랭킹 순서대로 각 Query ID에 대한 상세 정보를 보여준다.
• 랭킹별 쿼리에서는 대상 테이블에 대해 어떤 쿼리인지만 표시했다면, 상세한 쿼리 내용은 실행 건수, 응답 시간 분포(히스토그램), Rows sent/examined, 표본 EXPLAIN, 관련 테이블 메타 등 을 보여준다.

 

 

마무리

이로써 04. MySQL 아키텍처(4.1 엔진, 4.2 InnoDB, 4.3 MyISAM, 4.4 로그) 장을 다 읽고 정리했다. 

크게 보면 MySQL은 MySQL 엔진(파서/옵티마이저/실행)과 스토리지 엔진(실제 데이터 파일·잠금·트랜잭션)으로 나뉜다. SQL을 해석하고 실행 계획을 세우는 건 MySQL 엔진, 디스크에 어떻게 저장/검색/동시성 제어할지는 스토리지 엔진(InnoDB, MyISAM, MEMORY 등)이 맡는다. 

 

InnoDB는 버퍼 풀(Free/LRU/Flush)로 읽기 성능을 확보하고, 쓰기를 묶어 처리한다. Redo/Undo 로그와 로그 버퍼로 복구 가능성과 일관성을 보장하며, 백그라운드 스레드가 플러시·퍼지·머지·데드락 감시를 수행한다. 동시성과 안정성을 중심에 둔 범용 기본 엔진이다.

 

MyISAM은 데이터(.MYD)를 INSERT 순서로 저장하고 인덱스(.MYI)는 ROWID(물리 주소)를 포인터로 사용한다. 인덱스 캐싱은 키 캐시가 담당하고, 데이터 I/O는 운영체제 캐시가 담당하는 구조이다. MyISAM 위주 구성에서는 키 캐시를 전체 메모리의 40% 이하로 두고 나머지를 OS 캐시에 남겨두는 편이 바람직하다. 필요시 명명된 Key Cache로 테이블별 인덱스 캐시를 분리할 수 있다.

 

InnoDB가 기본이 되는 이유는 명확하다. ACID 트랜잭션과 Crash Recovery(redo)로 장애 복구가 확실하고, MVCC/레코드 잠금으로 동시성이 우수하며, 외래키·제약조건을 지원하여 대규모 운영에 적합하기 때문이다. 결과적으로 “별다른 이유가 없다면 InnoDB 하나로 대부분 커버”가 현실이다.

 

로그는 운영의 핵심 근거이다. 에러 로그는 시작·설정 적용(ready for connections), InnoDB 복구, 쿼리 중 오류·복제 경고, aborted connection, 모니터링 과다 출력, 종료 원인(수동 종료/세그폴트)을 기록한다. 제너럴 로그는 요청 시점의 모든 쿼리를, 슬로우 로그는 long_query_time을 초과한 완료된 느린 쿼리를 남긴다. 양이 많을 때는 pt-query-digest로 빈도·누적시간·상세 기준의 우선순위를 도출하는 것이 유효하다.

 

사실 그동안 데이터베이스가 알아서 저장하고 검색해 줘서 아키텍처에 관심을 가져본 적은 없었던 것 같다. 하지만 이번 장을 통해 그 뒤에서 움직이는 아키텍처를 구체적으로 확인할 수 있어 좋았다. 물론 생소한 내용이 많아 단번에 모두 이해되진 않았다. 다만 큰 흐름을 잡고 정리하면서 이해해 볼 수 있어서 좋았다.

 

다음은 5장 트랜잭션으로 돌아올게용! 모두들 해피추석🙌🏻