MySQL 로그 파일

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

MySQL 서버에 문제가 생겼을 때는 다음 로그 파일들을 자세히 확인하는 습관을 들일 필요가 있다.

• 에러 로그 파일
• 제너럴 쿼리 로그 파일 (제너럴 로그 파일, General log)
• 슬로우 쿼리 로그

에러 로그 파일

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

출력되는 메시지 종류

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

MySQL의 설정 파일을 변경하거나 데이터베이스가 비정상적으로 종료된 이후, 재시작하는 경우에는 반드시 MySQL 에러 로그 파일에서 다음 항목을 확인해야 한다.

• 설정된 변수의 이름이나 값이 명확하게 설정되었는지
• 의도한 대로 적용되었는지

정상적으로 적용되었을 경우

• MySQL 서버가 정상적으로 기동
• 새로 변경하거나 추가한 파라미터에 대한 특별한 에러나 경고성 메시지 없음

정상적으로 적용되지 않았을 경우

• MySQL 서버가 에러 메시지와 시작하지 못했다는 메시지 출력
• 특정 변수가 무시(ignore)된 경우

비정상 종료 시 나타나는 InnoDB의 트랜잭션 복구 메시지

InnoDB의 경우, MySQL 서버가 비정상적 또는 강제적으로 종료됐다면 다시 시작되면서 다음 작업을 한다.

• 완료되지 못한 트랜잭션 정리
• 디스크에 기록되지 못한 데이터를 다시 기록 (재처리 작업)

이 과정에서 문제가 있어 복구하지 못할 경우 해당 에러 메시지를 출력한 후 MySQL을 다시 종료한다.

• 상대적으로 해결하기 어려운 문제점일 때가 많다.
• innodb_force_recovery 파라미터를 0보다 큰 값으로 설정하고 재시작해야만 MySQL이 시작될 수 있다.

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

쿼리 처리 도중 발생하는 문제점은 사전 예방이 어려우므로 자주 에러 로그 파일을 검토해야 한다.

• 쿼리 실행 도중 발생한 에러나 복제에서 문제가 될 만한 쿼리에 대한 경고 메시지가 에러 로그에 기록된다.
• 데이터베이스의 숨겨진 문제점을 해결하는데 많은 도움이 된다.

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

다음의 경우 MySQL 서버의 에러 로그 파일에 이런 내용이 기록된다.

• 클라이언트 애플리케이션에서 정상적으로 접속 종료를 하지 못하고 프로그램이 종료된 경우
• 중간에 네트워크에 문제가 있어 의도치 않게 접속이 끊어지는 경우

이런 메시지가 많이 기록된다면 애플리케이션 커넥션 종료 로직을 검토해볼 필요가 있다.

• 이 에러가 어떻게 발생하게 되었는지 원인을 파악해야 한다.
• 혹은 max_connect_erros 시스템 변숫값이 너무 낮게 설정된 경우 발생할 수 있다.
  • 클라이언트가 MySQL 서버에 접속하지 못한다.
  • Host ‘host_name’ is blocked 에러 발생

InnoDB의 모니터링 또는 상태 조회 명령의 결과 메시지

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

• 상태 조회 명령은 SHOW ENGINE INNODB STATUS 같은 것을 말한다.

InnoDB의 모니터링을 활성화 상태로 유지하는 경우, 에러 로그 파일이 매우 커져서 파일 시스템의 공간을 다 사용할 수 있다.

• 모니터링을 사용한 이후에는 다시 비활성화해서 에러 로그 파일이 커지지 않게 한다.

MySQL 종료 메시지

MySQL이 아무도 모르게 종료되어 있거나, 아무도 모르게 재시작되는 경우 에러 로그 파일에서 MySQL이 마지막으로 종료되면서 출력한 메시지를 확인하는 것이 원인을 확인하는 유일한 방법이다.

• 누군가 MySQL 서버를 종료시켰을 경우
  • 에러 로그 파일에서 ‘Received SHUTDOWN from user …’ 메시지 확인 가능
• MySQL 서버가 세그먼테이션 폴트(Segmentation fault)로 비정상 종료된 경우
  • 아무런 종료 관련 메시지가 없거나 스택 트레이스와 같은 내용이 출력된다.
  • 스택 트레이스의 내용을 최대한 참조해서 MySQL 버전을 업그레이드 하거나 회피책을 찾는 것이 최적의 방법이다.

제너럴 쿼리 로그 파일

MySQL 서버에서 실행되는 쿼리의 전체 목록을 뽑아서 검토해 볼 경우가 있다.

• 쿼리 로그를 활성화해서 쿼리를 쿼리 로그 파일로 기록한다.
• 쿼리 로그 파일을 검토한다.

쿼리 로그 파일

• 시간 단위로 실행됐던 쿼리의 내용이 모두 기록된다.
• 슬로우 쿼리 로그와는 조금 다르게, 실행되기 전에 MySQL이 쿼리 요청을 받으면 바로 기록하기 때문에 쿼리 실행 중에 에러가 발생해도 일단 로그 파일에 기록된다.
• 경로
  • general_log_file이라는 이름의 파라미터에 설정돼 있다.
  • 파일이 아닌 테이블에 저장하도록 설정할 경우, 파일이 아닌 테이블을 SQL로 조회해서 검토한다.
  • log_output 파라미터: 쿼리 로그를 파일로 저장할지 테이블로 저장할지 결정

슬로우 쿼리 로그

쿼리 튜닝과 슬로우 쿼리 로그

쿼리 튜닝

MySQL 서버의 쿼리 튜닝은 크게 두 가지로 나눌 수 있다.

• 서비스가 적용되기 전에 전체적으로 튜닝하는 경우
  • 검토해야 할 대상 쿼리가 전부라서 모두 튜닝한다.
• 서비스 운영 중 MySQL 서버의 전체적인 성정 저하를 검사하거나 정기적인 점검을 위한 튜닝
  • 서비스에서 사용되는 쿼리 중 어떤 쿼리가 문제인지 판단해야 한다.

이때, 어떤 쿼리가 문제인지 판단하는 데 슬로우 쿼리 로그가 상당히 많은 도움이 된다.

슬로우 쿼리 로그

• long_query_time 시스템 변수에 설정한 시간 이상의 시간이 소요된 쿼리를 모두 기록한다.
• 반드시 쿼리가 정상적으로 실행이 완료되어야 슬로우 쿼리 로그에 기록될 수 있다.
• log_output 옵션을 이용해 파일로 기록할지 테이블로 기록할지 선택할 수 있다.
  • 기록되는 내용은 동일하다.
  • 옵션을 TABLE로 설정하더라도 mysql DB의 slow_log 테이블과 general_log 테이블은 CSV 스토리지 엔진을 사용하기 때문에, CSV 파일로 저장하는 것과 동일하게 작동한다.
• InnoDB는 MySQL 엔진 레벨의 잠금과 스토리지 엔진 자체 잠금을 가지고 있다.
  • MySQL의 잠금 처리는 MySQL 엔진 레벨과 스토리지 엔진 레벨의 두 가지 레이어로 처리됨
  • MyISAM이나 MEMORY 스토리지 엔진과 같은 경우, 별도의 스토리지 엔진 레벨의 잠금을 갖지 않음

pt-query-digest 스크립트

Percona에서 개발한 Percona Toolkit의 pt-query-digest 스크립트를 이용해 쉽게 빈도나 처리 성능별로 쿼리를 정렬해서 살펴볼 수 있다.

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

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

로그 파일의 분석이 완료되면 그 결과는 다음 3개의 그룹으로 나뉘어 저장된다.

• 슬로우 쿼리 통계
• 실행 빈도 및 누적 실행 시간순 랭킹
• 쿼리별 실행 횟수 및 누적 실행 시간 상세 정보

슬로우 쿼리 통계

• 분석 결과의 최상단
• 모든 쿼리를 대상으로 슬로우 쿼리 로그의 실행 시간(Exec time), 잠금 대기 시간(Lock time) 등에 대해 평균 및 최소/최대 값을 표시한다.

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

• 각 쿼리별로 응답 시간과 실행 횟수를 보여준다.
• --order-by 옵션으로 정렬 순서를 변경할 수 있다.
• Query ID 는 실행된 쿼리 문장을 정규화(쿼리에 사용된 리터럴을 제거)해서 만들어진 해시값을 의미한다.
  • 일반적으로 같은 모양의 쿼리라면 동일한 Query ID 를 갖는다.

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

• Query ID 별 쿼리를 쿼리 랭킹에 표시된 순서대로 자세한 내용을 보여준다.
  • 쿼리가 얼마나 실행됐는지, 쿼리에 응답 시간에 대한 히스토그램 등
• 실제 상세한 쿼리 내용은 개별 쿼리의 정보를 확인해보면 된다.
  • 랭킹별 쿼리는 대상 테이블에 대해 어떤 쿼리인지만을 표시한다.

Reference

참고 서적

📔 Real MySQL 8.0