슬로우 쿼리 조사 Shell 스크립트

슬로우 쿼리 조사 Shell 스크립트

오늘은 약간 전문 지식 공유드립니다.
비개발자분에게는 재미 없을수 있으니 스크롤을 휙 넘겨주셔도 됩니다 ㅎㅎ

백앤드 개발자분들을 위한 내용으로,
mysql 데이터베이스 슬로우 쿼리(Slow-query)에 대한 부분입니다.

서비스를 오래동안 운영하면 데이터베이스 용량이 커짐에 따라 점점 속도가 느려지는데요.
이 때 그 조짐을 알아볼 수 있는 중요한 근거가 바로 슬로우 쿼리입니다.
보통 /etc/my.cnf 에 슬로우 쿼리 파일을 기록할 경로를 설정하는데요.

slow-query-log=1
slow_query_log_file=/var/mysql/log/mysql-slow.log

이 슬로우 쿼리가 발생했는지 매번 들여다 보는건 번거롭지요.
10일간 발생한 슬로우 쿼리 건수와 평균 지연시간을 간단히 보는 기능은 없을까요?
   :
   :
만들면 됩니다.
참고로 바이브 코딩의 도움을 받아 bash(본어게인쉘)로 개발되었으며 일부 수정, 내용은 모두 검수해서 위험한 기능은 없습니다.
CentOS, Rocky 리눅스 등 RHell 계열에서 테스트해보았습니다.

터미널에 로그인, 기본 폴더에서 아래 쉘 파일을 check-slowquery.sh 파일로 작성, 저장합니다.
주의하실건 LOG_FILE 위치를 실제 슬로우 파일 위치로 맞춰주셔야 작동합니다.

#!/bin/bash

# MySQL 슬로우 쿼리 로그 파일 경로를 설정합니다.
# 실제 환경에 맞게 경로를 수정해주세요.
LOG_FILE="/var/mysql/log/mysql-slow.log"

# 어제 날짜와 오늘 날짜를 YYYY-MM-DD 형식으로 가져옵니다.
YESTERDAY=$(date -d "-10 days" +%Y-%m-%d)
TODAY=$(date +%Y-%m-%d)

# 로그 파일이 존재하는지 확인합니다.
if [ ! -f "$LOG_FILE" ]; then
    echo "오류: 슬로우 쿼리 로그 파일 '$LOG_FILE'을 찾을 수 없습니다."
    exit 1
fi

echo "MySQL 슬로우 쿼리 로그 분석을 시작합니다."
echo "로그 파일: $LOG_FILE"
echo "분석 기간: $YESTERDAY 00:00:00 부터 $TODAY 23:59:59 까지"
echo "----------------------------------------------------"

# 어제 00:00:00 부터 오늘 23:59:59 까지의 로그를 필터링하고 분석합니다.
# Query_time 라인을 찾아 시간 값을 추출하고 합산합니다.
# `awk`를 사용하여 Query_time을 추출하고 합계를 계산합니다.
# 로그 파일 형식에 따라 `grep` 패턴 및 `awk` 필드 번호 조정이 필요할 수 있습니다.
# 일반적으로 Query_time은 `Query_time: 0.123456` 형식입니다.

# 필터링된 로그에서 Query_time만 추출하여 처리합니다.
ANALYSIS_RESULT=$(
    awk -v yesterday="$YESTERDAY" -v today="$TODAY" '
    BEGIN {
        sum_query_time = 0;
        query_count = 0;
    }

    # 로그 파일에서 # Time: 으로 시작하는 라인을 찾고 날짜를 추출합니다.
    # 이 예제는 # Time: YYYY-MM-DDTHH:MM:SS.ffffffZ 형식 (예: # Time: 2025-07-30T05:38:50.021422Z)을 가정합니다.
    # substr() 함수를 사용하여 YYYY-MM-DD 부분만 추출합니다.
    /# Time: / {
        # 로그 라인에서 날짜 부분을 추출합니다 (예: 2025-07-30).
        # "# Time: " 다음 8번째 문자부터 10자리를 가져옵니다.
        current_log_date = substr($0, index($0, "# Time: ") + 8, 10);
    }

    # Query_time 라인을 찾고, 현재 날짜가 분석 기간 내에 있는지 확인합니다.
    /Query_time: / {
        if (current_log_date >= yesterday && current_log_date <= today) {
            # Query_time 값을 추출합니다. (예: Query_time: 1.142635 -> 1.142635)
            query_time = $3;
            printf "* %s : query_time = %f\n", current_log_date, query_time 
            sum_query_time += query_time;
            query_count++;
        }
    }

    END {
        if (query_count > 0) {
            avg_query_time = sum_query_time / query_count;
            printf "총 발생 건수: %d 건\n", query_count;
            printf "평균 지연 시간: %.6f 초\n", avg_query_time;
        } else {
            print "지정된 기간 동안 슬로우 쿼리가 발생하지 않았습니다.";
        }
    }
    ' "$LOG_FILE"
)

echo "$ANALYSIS_RESULT"
echo "----------------------------------------------------"
echo "분석 완료."

그리고 실행 권한을 줍니다. 이걸로 세팅은 끝!

chmod 700 check-slowquery.sh

이제 이걸 사용하시려면 아래 명령을 쓰시면 됩니다.
mysql 로그 파일에 접근 권한 때문에 앞에 sudo 는 붙여주어야 작동합니다.

sudo ./check-slowquery.sh

10일 동안 슬로우 쿼리 발생 이력이 없다면 아래 내역이,

MySQL 슬로우 쿼리 로그 분석을 시작합니다.
로그 파일: /var/mysql/log/mysql-slow.log
분석 기간: 2025-07-20 00:00:00 부터 2025-07-30 23:59:59 까지
----------------------------------------------------
지정된 기간 동안 슬로우 쿼리가 발생하지 않았습니다.
----------------------------------------------------
분석 완료.

슬로우 쿼리가 발생했었다면 아래 내역이 출력됩니다.

* 2025-07-30 : query_time = 2.4
* 2025-07-30 : query_time = 3.1
총 발생 건수: 10 건
평균 지연 시간: 4.1 초
----------------------------------------------------
분석 완료.

서버 관리가 좀 더 편해지겠지요? ㅎㅎ

관련 업무 종사자분에게 도움이 되시면 좋을것 같습니다.
아울러 재미없는 글에도 방문해주신 모든 분께 감사드립니다.