MySQL 서버는 서버의 상태를 진단할 수 있는 많은 도구들을 지원하지만 이러한 기능은 많은 지식을 필요로 하는 경우가 많다. 로그 파일을 활용하면 MySQL 서버에 대해 깊은 지식이 없어도 서버의 상태나 부하를 일으키는 원인을 쉽게 찾아 해결할 수 있다.

에러 로그 파일

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

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

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

MySQL 서버가 정상적으로 기동했고(mysqld: ready for commections 메시지 확인), 새로 변경하거나 추가된 파라미터에 대한 에러나 경고성 메시지가 없다면 정상적으로 적용된 것으로 판단하면 된다.

• 무시 ignore: 서버는 정상적으로 기동하지만 해당 파라미터가 적용안됨.
• 실패: 에러 메시지를 출력하고 시작하지 못했다는 메시지가 노출됨

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

InnoDB의 경우 MySQL 서버가 비정상적으로 종료됐다면 다시 시작되면서 완료하지 못한 트랜잭션을 정리하고 디스크에 기록되지 못한 데이터가 있다면 다시 기록하는데, 이 과정에서 간단한 메시지가 출력된다.

복구가 불가능한 경우 해당 에러메시지를 출력하고 MySQL은 다시 종료되며, 이 단계에서 발생하는 문제는 해결하기 어려운 문제점 일 때가 많고, innodb_force_recovery 파라미터를 설정하여 재시작 해야 할 수 있다.

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

쿼리 도중 발생하는 문제점은 사전 예방이 어려우며, 에러 로그 파일을 검토하는 과정에서 알게 된다.

쿼리 실행 도중 발생한 에러나 복제에서 문제가 될 만한 쿼리에 대한 경고 메시지가 에러 로그에 기록되므로 자주 검토하는 것이 숨겨진 문제점을 해결하는데 많은 도움이 될 수 있다.

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

클라이언트 애플리케이션에서 정상적으로 접속 종료를 하지 못하고 프로그램이 종료된 경우 MySQL 서버의 에러 로그 파일에 이런 내용이 기록된다. (네트워크 문제로 인한 겨우 포함)

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

max_connect_errors 시스템 변숫값이 너무 낮게 설정된 경우 클라이언트 프로그램이 MySQL 서버에 접속하지 못하고 Host 'host_name' is blocked라는 에러가 발생 가능하며, 이러한 경우는 에러가 어떻게 발생하게 됐는지 원인을 확인하고, 문제가 없다면 해당 시스템 변수의 값을 증가시키면 해결된다.

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

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

InnoDB의 모니터링을 활성화 상태로 유지하는 경우에는 에러 로그 파일이 매우 커져서 파일 시스템의 공간을 많이 사용할 수 있으므로, 다시 비활성화하여 에러 로그 파일이 커지지 않게 만들어야 한다.

MySQL의 종료 메시지

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

• Received SHOUTDOWN from user ... 메시지: 특정 유저가 종료한 경우
• 없거나 스택 트레이스(16진수 주소값이 잔뜩 출력되는) 메시지: 세그멘테이션 폴트로 비정상 종료된 경우
  • 세그멘테이션 폴트로 종료된 경우 스택 트레이스 내용을 최대한 참조하여 MySQL의 버그와 연관이 있는지 조사 후 버전을 업그레이드 하거나 회피책(WorkAround)를 찾는다.
  • MySQL “The Error Log"절을 확인한다.

제너럴 쿼리 로그 파일

MySQL 서버에서 실행되는 쿼리 목륵을 검토하고 싶다면, 쿼리 로그를 활성화하여 실행 쿼리를 쿼리 로그 파일로 기록하게 한 다음, 해당 파일을 검토한다.

슬로우 쿼리 로그와는 다르게 제너럴 로그는 실행되기 전에 MySQL이 요청을 받으면 바로 기록하기 때문에 쿼리 실행 중에 에러가 발생해도 일단 로그 파일에 기록된다.

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

1

SHOW GLOBAL VARIABLES LIKE 'general_log_file';

슬로우 쿼리 로그

서비스 운영 중에 MySQL 서버의 전체적인 성능 저하를 검사하거나 정기적인 점검을 위한 튜닝이 필요할 때 어떤 쿼리가 문제인지를 판단하는데 많은 도움을 준다.

• long_query_time: 해당 시스템 변수에 설정한 시간 이상의 시간이 소요된 쿼리가 모두 기록된다.
• log_output: 슬로우 커리 로그를 파일 또는 테이블에 기록할지 설정할 수 있다.
  • TABLE: mysql DB의 테이블에 제너럴로그나 슬로우 쿼리 로그를 테이블(slow_log, general_log)에 저장한다.
  • FILE: 로그 내용을 디스크의 파일로 저장한다.

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

슬로우 쿼리 통계

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

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

각 쿼리별로 응답 시간과 실행 횟수를 보여주는데, pt-query-digest 명령 실행 시 --oorder-by옵션으로 정렬 순서를 변경할 수 있다.

Query ID는 실행된 쿼리 문장을 정규화(쿼리에 사용된 리터럴을 제거)해서 만들어진 해시 값을 의미하는데, 같은 모양의 쿼리라면 동일한 Query ID를 가지게 된다.

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

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