Chapter 04.03 MyISAM 스토리지 엔진 아키텍처(architecture) ~ 04.04 MySQL 로그 파일

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

 

4.3.1 키 캐시(Key Cache)

 

키 캐시(key cache, 키 버퍼)

• MyISAM의 키 캐시는 InnoDB의 버퍼 풀과 비슷한 역할을 한다.
• 인덱스만 대상으로 동작한다.
• 키 캐시의 효율성 판단
  • key_reads: 인덱스를 디스크에서 읽어 들인 횟수를 저장하는 상태 변수
  • key_read_requests: 키 캐시로부터 인덱스를 읽은 횟수를 저장하는 상태 변수
  • 키 캐시 히트율(Hit rate) = 100 - (key_reads / key_read_requests * 100)
  • 키 캐시율 99% 이상으로 권장한다.
• 아래 코드로 key_read_requests를  확인 가능

SHOW CLOBAL STATUS LIKE 'Key%';

• key_buffer_size: 기본(default) 키 캐시 공간을 설정하는 파라미터

 

 

4.3.2 운영체제의 캐시 및 버퍼

• MyISAM 테이블의 인덱스는 키 캐시를 이용해 디스크를 검색하지 않고도 빠르게 검색 가능하다.
• 키 캐시는 최대 물리 메모리의 40% 이상을 넘지 않도록 설정한다.
• 운영 체제의 캐시 기능은  남는 메모리를 사용하는 것이 기본 원칙이다.
• 다른 애플리케이션이 전체 메모리를 모두 사용해버리면 운영 체제가 캐시 용도로 사용할 수 있는 메모리 공간이 없어진다.
  • MyISAM 테이블의 데이터를 캐시하지 못하고 MyISAM  테이블에 대한 쿼리 처리가 느려진다.
  • 따라서 MyISAM  테이블을 자주 사용하면 캐시 공간을 비워 놔야 문제를 방지할 수 있다.

 

 

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

• InnoDB 스토리지 엔진을 사용하는 테이블은 PK에 의해 클러스터링 되어 저장
• 반면, MyISAM 테이블은 PK에 의한 클러스터링 없이 데이터 파일이 힙(Heap) 공간처럼 활용
  • PK 값에 관계없이 INSERT 되는 순서대로 데이터 파일에 저장된다.
• MyISAM  테이블에서 ROWID는 가변 길이 또는 고정 길이로 저장 가능
  • 1) 고정 길이 ROWID
  • 2) 가변 길이 ROWID

 

 

 

4.4 MySQL 로그 파일

• MySQL 서버 상태를 진단하기 위해서는 깊은 지식이 필요하다.
• 그런데 로그 파일을 이용하면 MySQL 서버의 깊은 내부 지식 없이도 MySQL 의 상태나 부하를 일으키는 원인을 쉽게 찾을 수 있다. 
• 문제가 생겼을 때 가장 먼저 로그 파일부터 살펴보도록 한다.

 

 

4.4.1 에러 로그 파일

• 에러 로그 파일: MySQL기 실행되는 도중에 발생하는 에러, 경고 메시지가 출력되는 로그 파일을 말한다.
• MySQL 설정 파일(my.cnf)에서 log_error 라는 이름의 파라미터로 정의된 경로에 생성된다.
• my.cnf에 정의되지 않은 경우에는 데이터 디렉터리(datadir 파라미터에 설정된 디렉터리)에 .err 라는 확장자가 붙은 파일로 생성된다.

 

 

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

• MySQL 설정 파일을 변경하거나 데이터베이스가 비정상 종료된 다음 다시 시작하는 경우, 반드시 MySQL 에러 로그 파일을 통해 변수명이나 값이 명확하게 설정되고 의도한 대로 적용됐는지 확인한다.
  • mydqld: ready for connections => 서버가 정상적으로 기동됐다.

 

 

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

• InnoDB의 경우에는 MySQL서버가 비정상적 또는 강제 종료됐다면 다시 시작되면서 완료되지 못한 트랜잭션을 정리하고 디스크에 기록되지 못한 데이터가 있다면 다시 기록하는 재처리 작업을 한다.
• 해결: innodb_force_recovery 파라미터를 0보다 큰 값으로 설정하고 재시작해야만 MySQL 서버가 시작될 수도 있다.

 

 

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

• 예방이 어렵다.
• 에러 로그를 자주 검토하는 것이 DB의 숨겨진 문제점을 해결하는데 도움이 된다.

 

 

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

• 클라이언트 애플리케이션에서 정상적으로 접속 종료를 하지 못하고 프로그램이 종료된 경우, MySQL 서버의 에러 로그 파일에 기록되는 메시지이다.
• 네트워크에 문제가 있어서 연결이 끊어질 때도이런 메시지가 기록된다.
• 해결
  • 애플리케이션 종료 로직을 검토한다.
  • max_connect_errors 시스템 변숫값이 낮은 경우 => "Host 'host_name' is blocked"라는 에러 발생 가능
  • "Host 'host_name' is blocked": 클라이언트 호스트에서 발생한 에러(커넥션 실패나 강제 연결 종료)의 횟수가 max_connect_errors 변수의 값을 넘게 되면 발생한다.
  • max_connect_errors 증가시킨다. 

 

 

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

• InnoDB의 테이블 모니터링이나 락 모니터링, InnoDB의 엔진 상태를 조회하는 명령은 상대적으로 큰 메시지를 에러 로그 파일에 기록한다.

 

 

4.4.1.6 MySQL의 종료 메시지

• MySQL이 갑작스레 재시작 또는 종료되는 경우
• 종료되면서 마지막으로 출력한 메시지를 확인해야한다.
  
  • 누군가 종료 시킨 경우: "Received SHUTDOWN from user"
  •  MySQL  서버가 세그먼테이션 폴트(segmentation fault)로 비정상 종료된 경우 => 메시지가 없거나 스택 트레이스가 출력

 

 

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

• 슬로우 쿼리 로그와 다르게 제너럴 쿼리 로그는 실행 전에 MySQL기 쿼리 요청을 받으면 바로 기록한다.
  • 따라서, 쿼리 실행 중에 에러가 발생해도 일단 로그 파일에 기록된다.
• general_log_file: 쿼리 로그 파일의 경로 저장하는 파라미터
• log_output: 쿼리 로그를 파일로 저장할지 테이블로 저장할지 결정하는 파라미터

 

 

 

4.4.3 슬로우 쿼리 로그

MySQL 서버의 쿼리 튜닝

• 1) 서비스가 적용되지 전에 전체적으로 튜닝하는 경우
  • 모든 쿼리를 검사하고 튜닝한다.
• 2) 서비스 운영 중에 MySQL 서버의 전체적인 성능 저하를 검사 정기적으로 점검을 위한 튜닝
  • 어떤 쿼리가 문제의 쿼리인지 알 수 없다. => 슬로우 로그 쿼리를 이용하면 어떤 쿼리가 문제인지 알 수 있다.

 

슬로우 로그 파일의 변수

• long_query_time: 변수에 설정된  시간(초 단위)이상의 시간이 소요된 쿼리가 모두 기록된다.
• 정상적으로 쿼리가 실행된 경우에만 해당한다.
• log_output:  슬로우 쿼리 로그를 파일로 기록할지 테이블로 기록할지 선택 가능하다.
  • TABLE로 설정: 제너럴 로그나 슬로우 로그 쿼리를 mysql DB의 테이블(general_log와 slow_log 테이블)에 저장
    • TABLE로 설정해도 general_log 테이블과 slow_log 테이블은 CSV 스토리지 엔진을 엔진을 사용하므로 결국 CSV 파일로 저장하는 것과 동일하게 작동한다.
  • FILE로 설정: 로그의 내용을 디스크의 파일로 저장한다.

 

 

  Ex) 슬로우 로그 쿼리가 파일로 기록된 내용의 일부

<hide/>

# Time: 2020-07-17T15:44:22.178484+09:00
# User@Host: root[root] @LOCALHOST [] Id: 14
# Query_time: 1.180245 Lock_time: 0.002658 Rows_examined: 28440047
use Employees;
SET timestamp = 1595141060;
SELECT emp_no, max(salary) FRIOM salaries;

• Time: 쿼리가 종료된 시점 (시작 시점 =  Time    -   Query_time)
• User@Host: 쿼리를 실행한 사용자의 계정이다.
• Query_time: 쿼리가 실행되는데 걸린 전체 시간을 의미한다. 
• Lock_time: 두 가지 레벨의 잠금 중에서 MySQL 엔진 레벨에서 관장하는 테이블 잠금에 대한 대기 시간만 표현한다.
  • SELECT를 하기위해 0.002658초간 테이블 락을 기다렸다는 의미이다.
• Rows_exasmined: 쿼리가 처리되기 위해 몇 건의 레코드에 접근했는지를 의미한다.
• Rows_sent 실제 몇 건의 처리 결과를 클라이언트로 보냈는지를 의미한다.
• Rows_exasmined가 높은데 Rows_sent 가 낮다면 더 적은 레코드만 접근하도록 바꿔볼 필요가 있다.

 

 

 

4.4.3.1 슬로우 쿼리 통계

• 분석 결과의 최상단에 표기하고 ,ㅡ 모든 쿼리를 대상으로 슬로우 쿼리 로그의 실행 시간, 잠금 대기 시간 등에 대한 평균 및 최솟값, 최댓값을 표시한다.

 

 

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

• 각 쿼리 별로 응답 시간과 실행 횟수를 보여준다.
• pt-query-digests 명령 실행 시 --order by 옵션으로 정렬 순서를 변경 가능하다.
• Query ID는 실행된 쿼리 문장을 정규화해서 만들어진 해시값을 의미한다. 같은 모양의 쿼리는 Query ID가 같다.

 

 

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

• Query ID별 쿼리를 쿼리 랭킹에 표기된 순서대로 보여준다.
• 랭킹별 쿼리에서는 대상 테이블에 대해 어떤 쿼리인지만을 표시하는데 실제 상세 내용은 개별 쿼리의 정보를 확인해보면 된다.

 

 

Issue

• content
• content

출처 - 「Real MySQL 8.0 - 백은빈, 이성욱」 위키북스