[Real MySQL 8.0 1] 04 아키텍처 - 3. MyISAM 스토리지 엔진 아키텍처 / 4. MySQL 로그 파일

3. MyISAM 스토리지 엔진 아키텍처

키 캐시, 운영체제의 캐시/버퍼 -> MyISAM 스토리지 엔진의 성능에 영향

1) 키 캐시(Key cache, 키 버퍼)

• InnoDB의 버퍼 풀과 비슷한 역할
• 인덱스만을 대상으로 작동, 인덱스의 디스크 쓰기의 작업에 대해서만 부분적으로 버퍼링 역할
• 키 캐시 히트율(Hit rate) = 100 - (Key_reads / Key_read_requests * 100)
  • Key_reads: 인덱스를 디스크에서 읽어 들인 횟수를 저장하는 상태 변수
  • Key_read_requests: 키 캐시로부터 인덱스를 읽은 횟수를 저장하는 상태 변수
    • 상태값 -> `SHOW GLOBAL STATUS LIKE 'Key%';`
  • 일반적으로 99% 이상으로 유지하는 것 권장
    • 99% 미만 -> 키 캐시 조금 더 크게 설정
    • 32비트 운영체제: 하나의 키 캐시에 4GB 이상의 메모리 공간 설정 X
    • 64비트 운영체제: `OS_PER_PROCESS_LIMIT` 값에 설정된 크기만큼 메모리 할당 O
    • 제한 값 이상의 키 캐시 할당 -> 기본(Default) 키 캐시 이외에 별도의 명명된 키 캐시 공간 설정해야 함
      • `key_buffer_size`: 기본 키 캐시 공간 설정하는 파라미터

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

기본 키 캐시 4GB, `kbuf_board`, `kbuf_comment`라는 이름의 키 캐시가 각각 2GB씩 생성
But, 기본 키 캐시 외의의 명명된 키 캐시 영역은 아무런 설정 X -> 메모리 할당만 해두고 사용 X
=> 어떤 인덱스를 캐시할지 MySQL(MyISAM 스토리지 엔진)에 알려줘야 함

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

`board` 테이블의 인덱스 -> `kbuf_board` 키 캐시
`comment` 테이블의 인덱스 -> `kbuf_comment 키 캐시 

---

2) 운영체제의 캐시 및 버퍼

MyISAM 테이블의 인덱스: 키 캐시 이용 -> 디스크 검색 X 충분히 빠르게 검색 O

But, MyISAM 테이블의 데이터: 디스크로부터의 I/O를 해결해 줄 만한 어떠한 캐시 or 버퍼링 기능 X

=> 데이터 읽기/쓰기 작업은 항상 운영체제의 디스크 읽기/쓰기 작업으로 요청

• 캐시 or 버퍼링 메커니즘 탑재 O -> MySQL 서버가 요청하는 디스크 읽기 작업을 위해 매번 디스크의 파일 읽지 X
• InnoDB처럼 데이터 특성을 알고 전문적을 캐시 or 버퍼링 X But, 없는 것보다 남/ 남는 메모리 사용 
  • ex. 전체 메모리 8GB인데 MySQL/애플리케이션에서 메모리 모두 사용
    -> 운영체제가 캐시 용도로 사용할 수 있는 메모리 공간 X
  • -> MyISAM 테이블의 데이터 캐시 X => 쿼리 처리 ↓
• 데이터베이스에서 MyISAM 테이블을 주로 사용 -> 운영체제가 사용할 수 있는 캐시 공간을 위해 메모리를 비어두어야 함
• 키 캐시: 최대 물리 메모리의 40% 이상 X/ 나머지 메모리 공간은 운영체제가 자체적인 파일 시스템을 위한 캐시 공간

---

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

• InnoDB 테이블: 프라이머리 키에 의해서 클러스터링되어 저장
• MyISAM 테이블: 프라이머리 키에 의한 클러스터링 X 데이터 파일이 힙(Heap) 공간처럼 활용
  • 레코드는 프라이머리 키 값과 무관하게 `INSERT` 순서대로 데이터 파일에 저장
  • 모두 ROWID라는 물리적인 주솟값을 가짐

-> 프라이머리 키와 세컨더리 인덱스는 모두 데이터 파일에 저장된 레코드의 ROWID 값을 포인터로 가짐

 

ROWID 저장 방법

• 고정 길이 ROWID
  • MyISAM 테이블을 생성할 때 `MAX_ROWS` 옵션 설정 O (자주 사용 X)
    • MySQL 서버는 최대로 가질 수 있는 레코드가 한정된 테이블 생성
    • -> ROWID 값으로 4바이트 정수 사용/ 레코드가 `INSERT`된 순번이 ROWID로 사용
• 가변 길이 ROWID
  • MyISAM 테이블을 생성할 때 `MAX_ROWS` 옵션 설정 X
    
    • ROWID는 최대 `myisam_data_pointer_size` 시스템 변수(기본값: 7)에 설정된 바이트 수만큼 공간 사용할 수 있음
      • ROWID는 2바이트 ~ 7바이트 가변적
      • 첫 번째 바이트: ROWID 길이 저장/ 나머지 공간: 실제 ROWID를 저장
  • 데이터 파일에서 레코드의 위치(offset)가 ROWID로 사용
    • 최대 크기 256TB($2^{(8*(7-1))$}
    • IF. 256TB 이상의 데이터 파일 필요
      -> `myisam_data_pointer_size` 시스템 변수를 8로 설정(65PB($2^{(8*(8-1))$))

---

4. MySQL 로그 파일

1) 에러 로그 파일

MySQL이 실행되는 도중에 발생하는 에러/경고 메시지 출력되는 로그 파일

위치: MySQL 설정 파일(`my.cnf`)에서 `log_error`라는 이름의 파라미터로 정의된 경로에 생성

별도로 정의 X -> 데이터 디렉터리(`datadir` 파라미터에 설정된 디렉터리)에 `.err`라는 확장자가 붙은 파일

 

에러 메시지

• MySQL이 시작하는 과정과 관련된 정보성 및 에러 메시지
  • MySQL의 설정 파일 변경 or 데이터베이스가 비정상적으로 종료된 이후 다시 시작한 경우
    -> MySQL 에러 로그 파일 ~> 설정된 변수의 이름/값 명확하게 설정되고 의도한 대로 적용됐는지 확인
    • 정상적으로 기동('mysqld: ready for connections'), 새로 변경/추가한 파라미터에 대한 에러/경고성 메시지 X 
      -> 정상적으로 적용된 것
    • 특정 변수가 무시된 경우: MySQL 서버는 정상적으로 기동 But, 해당 파라미터는 적용 X
    • 변수명 인식 X or 설정된 파라미터 값의 내용 인식 X -> 에러, 시작 못했다는 메시지 출력
• 마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB의 트랜잭션 복구 메시지
  • InnoDB: MySQL 서버가 비정상적/강제적 종료
    -> 다시 시작되면서 완료되지 못한 트랜잭션 정리, 디스크에 기록되지 못한 데이터가 있다면 다시 기록하는 재처리 작업
    • 문제가 있어서 복구 X -> 해당 메시지 출력 후 MySQL 다시 종료
    • -> 상대적으로 해결하기 어려운 문제점일 때가 많음
    • (`innodb_force_recovery` 파라미터 > 0) 설정 후 재시작해야만 MySQL 시작될 수 있음
• 쿼리 도중에 발생하는 문제에 대한 메시지
  • 사전 예방 어려움, 주기적으로 에러 로그 파일 검토하는 과정에서 발견
  • 쿼리의 실행 도중 발생한 에러 or 복제에서 문제가 될 만한 쿼리에 대한 경고 메시지 기록
  • => 자주 에러 로그 파일 검토하는 것이 데이터베이스 문제 해결하는 데 도움
• 비정상적으로 종료된 커넥션 메시지(Aborted connection)
  • 클라이언트 애플리케이션이 정상적으로 접속 종료 X 프로그램 종료된 경우
  • 네트워크 문제로 의도 X 접속 끊어지는 경우
  • => 애플리케이션의 커넥션 종료 로직 검토 필요
    • `max_connect_errors` 시스템 변수 값이 너무 낮게 설정된 경우
      -> 클라이언트 프로그램이 MySQL 서버 접속 X "Host 'host_name' is blocked" 발생
    • 클라이언트 호스트에서 발생한 에러(ex. 커넥션 실패 or 강제 연결 종료)의 횟수 > `max_connect_errors`
    • => `max_connect_errors` 시스템 변수 값 증가
• InnoDB의 모니터링 or 상태 조회 명령(`SHOW ENGINE INNODB STATUS` 등)의 결과 메시지
  • InnoDB의 테이블 모니터링 or 락 모니터링 or InnoDB의 엔진 상태를 조회하는 명령
    -> 상대적으로 큰 메시지를 에러 로그 파일에 기록
  • InnoDB의 모니터링을 활성화 상태, 그대로 유지 -> 에러 로그 파일이 매우 커져서 파일 시스템 공간을 다 사용할 수 있음
    • => 모니터링 사용 후 다시 비활성화 -> 에러 로그 파일이 커지지 않게 만들어야 함
• MySQL의 종료 메시지
  • 아무도 모르게 종료/재시작되는 경우
  • 에러 로그 파일에서 MySQL이 마지막으로 종료되면서 출력한 메시지를 확인
    • 누군가 MySQL 서버 종료: 'Received SHUTDOWN from user...'
    • 비정상적 종료: 아무런 종료 관련 메시지 X or 스택 트레이스(ex. 16진수 주솟값) 출력 
      • 세그먼테이션 폴트로 인한 종료: 스택 트레이스 내용 최대한 참조 -> MySQL의 버그와 연관이 있는지 조사
      • => MySQL 버전 업그레이드 or 회피책(WorkAround) 찾음

---

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

MySQL 서버에서 실행되는 쿼리로 어떤 것들이 있는지 전체 목록 뽑아서 검토

-> 쿼리 로그 활성화 => 쿼리를 쿼리 로그 파일로 기록, 파일 검토

• 시간 단위로 실행됐던 쿼리 내용 모두 기록
• 실행되기 전에 MySQL이 쿼리 요청 받으면 바로 기록
  -> 쿼리 실행 중에 에러가 발생해도 일단 로그 파일에 기록 (<-> 슬로우 쿼리)

 

쿼리 로그 파일 경로: `general_log_file` 파라미터에 설정돼 있음

쿼리 로그를 파일이 아닌 테이블에 저장하도록 설정할 수 있음 

-> `log_output` 파라미터

SHOW GLOBAL VARIABLES LIKE 'general_log_file';

---

3) 슬로우 쿼리 로그

• 서비스가 적용되기 전에 전체적으로 튜닝하는 경우
  • 검토해야 할 대상 쿼리가 전부 -> 모두 튜닝
• 서비스 운영 중에 MySQL 서버의 전체적인 성능 저하 검사 or 정기적인 점검을 위한 튜닝
  • 어떤 쿼리가 문제의 쿼리인지 판단하기 어려움 => 슬로우 쿼리가 도움

 

• `long_query_time`시스템 변수에 설정한 시간 이상의 시간이 소요된 쿼리 모두 기록
  • `long_query_time` 파라미터: 초단위 설정/ 소수점 값으로 설정 -> 마이크로 초 단위 설정 가능
• MySQL이 쿼리를 실행한 후, 실제 소요된 시간을 기준으로 슬로우 쿼리 로그에 기록할지 여부 판단
  • -> 반드시 쿼리가 정상적으로 실행 완료돼야 기록될 수 있음
  • 슬로우 쿼리 로그 파일에 기록된 쿼리 = 정상적으로 실행 완료, 실행하는 데 걸린 시간 > `long_query_time`에 정의된 시간
• `log_output` 옵션 -> 파일/테이블 기록 선택
  • `TABLE`: 제너럴 로그/슬로우 쿼리 로그 -> MySQL DB 테이블(`general_log`, `slow_log` 테이블)에 저장
    • CSV 스토리지 엔진 사용 => CSV 파일을 저장하는 것과 동일하게 작동
    • 내용 ↑ -> 시간이 많이 걸리거나 어느 쿼리 집중적으로 튜닝해야 할지 식별하기 어려움
      => Percona에서 개발한 Percona Toolkit의 `pt-query-digest` 스크립트 이용
      -> 빈도/처리 성능별로 쿼리 정렬해서 확인 가능
  • `FILE`: 로그의 내용을 디스크 파일로 저장

## General Log 파일 분석
linux> pt-query-digest --type='genlog' general.log > parsed_general.log

## Slow log 파일 분석
linux> pt-query-digest --type='slowlog' mysql-slow.log > parsed_mysql-slog.log

 

 

MySQL의 잠금 처리 -> 2가지 레이어: MySQL 엔진 레벨 / 스토리지 엔진 레벨

• MyISAM or MEMORY 스토리지 엔진: 별도의 스토리지 엔진 레벨의 잠금 X
  • 테이블 단위의 잠금 사용, MVCC 같은 메커니즘 X -> `SELECT` 쿼리여도 `Lock_time`이 1초 이상 소요될 가능성 O
• InnoDB: MySQL 엔진 레벨의 잠금, 스토리지 엔진 자체 잠금 O
  • 가끔 `SELECT` 쿼리 경우 `Lock_time` 상대적으로 큰 값 발생할 수 있음 
    -> InnoDB의 레코드 수준 잠금 X MySQL 엔진 레벨에서 설정한 테이블 잠금 때문일 가능성 ↑
  • InnoDB 테이블에만 접근하는 쿼리 문장의 슬로우 쿼리 로그에서는 `Lock_time` 값 -> 튜닝 or 쿼리 분석에 별로 도움 X

 

분석 결과

• 슬로우 쿼리 통계
  • 모든 쿼리를 대상 평균 및 최소/최대 값 표시
  • 슬로우 쿼리 로그의 실행 시간(`Exec time`), 잠금 대기 시간(`Lock time`) 등
• 실행 빈도 및 누적 실행 시간순 랭킹
  • 각 쿼리별로 응답 시간, 실행 횟수
  • `pt-query-digest` 명령 실행 -> `--order-by` 옵션으로 정렬 순서 변경 O
  • `Query ID`: 실행된 쿼리 문장을 정규화(쿼리에 사용된 리터럴 제거)해서 만들어진 해시 값
    • 같은 모양의 쿼리 -> 동일한 `Query ID`
• 쿼리별 실행 횟수 및 누적 실행 시간 상세 정보
  • `Query ID`별 쿼리 -> 쿼리 랭킹에 표시된 순서대로 자세한 내용 보여줌
    • 쿼리가 얼마나 실행됐는지, 쿼리의 응답 시간에 대한 히스토그램 같은 상세한 정보
  • 랭킹별 쿼리 -> 대상 테이블에 대해 어떤 쿼리인지만 표시/ 실제 상세한 쿼리 내용 -> 개별 쿼리의 정보 확인

---

출처

GitHub - wikibook/realmysql80: 《Real MySQL 8.0》 예제 파일