MariaDB / slow query log 설정하고 로그 보는 방법

MariaDB

MariaDB의 slow query log(느린 쿼리 로그)는 실행시간이 기준을 넘는 SQL 등을 파일이나 테이블에 기록하는 기능입니다. 파일로 확인하려면 slow_query_log를 켜고 log_output에 FILE을 포함한 뒤, 실행한 연결의 시간 기준과 필터까지 확인해야 합니다. 실행 중 설정은 SQL로 바꿀 수 있지만 재시작 후에도 유지하려면 옵션 파일에 저장해야 합니다.

어떤 쿼리가 기록되며 무엇을 준비해야 하나요?

이 로그는 서버가 처리한 SQL의 실행시간과 조사한 행 수 등을 살펴보는 자료입니다. 켜기만 해도 쿼리가 빨라지는 기능은 아니며, 로그에 나온 쿼리의 실행 계획과 인덱스를 검토하는 데 사용합니다. 웹페이지 응답시간 전체에는 네트워크나 애플리케이션 처리도 포함되므로 쿼리 시간과 같다고 볼 수 없습니다.

기본적인 시간 기준은 long_query_time이며 단위는 초입니다. 예제의 1은 1초를 뜻합니다. 인덱스 미사용 쿼리 기록 옵션을 별도로 켜면 빠른 쿼리도 포함될 수 있고, 행 수 제한·필터·샘플링 설정 때문에 기준을 넘은 쿼리도 빠질 수 있습니다. 자세한 조건은 MariaDB 느린 쿼리 로그 설명을 참고합니다.

아래는 직접 관리하는 Linux 서버를 대상으로 합니다. 관리자 SQL 계정과 설정 파일을 편집할 OS 권한이 필요하며, 전역 변수 변경에는 해당 버전의 전역 설정 권한이 필요합니다. 권한 오류가 나면 애플리케이션 계정에 넓은 권한을 추가하지 말고 관리 계정으로 작업합니다. 관리형 데이터베이스는 제공업체의 파라미터 그룹과 로그 조회 화면을 사용해야 할 수 있습니다.

MariaDB 10.11부터 일부 변수 이름이 바뀌었지만 아래의 기존 이름도 별칭으로 제공됩니다. 예제는 여러 버전에서 익숙한 이름을 사용하며 실제 지원 여부는 서버에서 확인합니다. MariaDB 서버 변수 문서에 이름·범위·버전 조건이 정리되어 있습니다.

예제에서 쓰는 이름	10.11부터의 이름	역할
`slow_query_log`	`log_slow_query`	기록 사용 여부
`slow_query_log_file`	`log_slow_query_file`	파일 이름과 위치
`long_query_time`	`log_slow_query_time`	실행시간 기준
`min_examined_row_limit`	`log_slow_min_examined_row_limit`	최소 조사 행 수

재시작 없이 켜고 시험 쿼리로 확인하기

현재 값과 복구할 원래 값 기록

Ubuntu의 일반적인 로컬 소켓 인증 설치라면 sudo mariadb로 접속할 수 있습니다. 다른 인증 구성은 mariadb -u 관리계정 -p 등 해당 방식으로 접속합니다. 먼저 버전, 데이터 디렉터리와 전역 값을 기록합니다. 변수 별칭은 조회 결과에서 새 이름으로 표시될 수도 있습니다.

SELECT VERSION(), @@GLOBAL.datadir;
SHOW GLOBAL VARIABLES WHERE Variable_name IN (
  'slow_query_log', 'log_slow_query',
  'slow_query_log_file', 'log_slow_query_file',
  'long_query_time', 'log_slow_query_time',
  'log_output', 'general_log', 'log_slow_rate_limit',
  'min_examined_row_limit', 'log_queries_not_using_indexes',
  'log_slow_filter', 'log_slow_disabled_statements'
);

간단한 시험 후 원래 전역 설정으로 되돌리려면 같은 관리 연결에서 다음 값도 저장합니다. @saved_...는 이 연결 안에서만 유지되는 사용자 변수입니다. 연결이 끊어지면 사라지므로 조회 결과를 별도 작업 기록에도 남기고, 마지막 복원 SQL은 반드시 이 연결에서 실행합니다.

SET @saved_slow = @@GLOBAL.slow_query_log,
    @saved_file = @@GLOBAL.slow_query_log_file,
    @saved_output = @@GLOBAL.log_output,
    @saved_time = @@GLOBAL.long_query_time;

파일 기록 활성화와 세션 적용 구분

다음 설정은 파일 출력을 사용하는 간단한 예입니다. mariadb-slow.log처럼 상대 파일 이름을 주면 @@GLOBAL.datadir 아래에 저장됩니다. 이미 다른 느린 로그 파일을 운영 중이면 이름을 바꾸지 않고 기존 경로를 유지할 수도 있습니다.

SET GLOBAL slow_query_log_file = 'mariadb-slow.log';
SET GLOBAL log_output = 'FILE';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log = ON;

log_output은 일반 쿼리 로그에도 영향을 주는 공통 설정입니다. 기존에 TABLE 출력을 사용했다면 위 명령을 그대로 적용하기 전에 필요한 출력을 확인합니다. 테이블 출력을 유지하면서 파일도 필요하면 FILE,TABLE을 선택할 수 있습니다. NONE이면 로그 기능이 켜져 있어도 기록되지 않습니다.

전역 값은 새 연결의 기본값이며, 이미 연결된 세션의 시간 기준 등이 자동으로 바뀌는 것은 아닙니다. 다음처럼 두 범위를 비교합니다. 애플리케이션의 연결 풀도 기존 연결을 재사용하므로 새 연결에 적용할지, 해당 세션 설정을 바꿀지 계획해야 합니다. SHOW VARIABLES의 GLOBAL·SESSION 설명도 참고할 수 있습니다.

SELECT @@GLOBAL.long_query_time AS global_seconds,
       @@SESSION.long_query_time AS session_seconds,
       @@GLOBAL.slow_query_log AS global_log,
       @@SESSION.slow_query_log AS session_log;

현재 연결에서 기록 시험과 세션 복구

아래 예제는 현재 세션의 기존 설정을 저장하고 시험 동안 시간 기준을 1초, 최소 조사 행 수를 0, 샘플링을 1로 맞춥니다. 필터도 시험 세션에서만 비웁니다. SLEEP는 테이블의 행을 조사하지 않으므로 행 수 하한이 있으면 시험 로그가 빠질 수 있습니다. 선택한 서버에서 SELECT가 별도 제외되지 않는지도 앞서 조회한 설정으로 확인합니다.

SET @session_log = @@SESSION.slow_query_log,
    @session_time = @@SESSION.long_query_time,
    @session_rows = @@SESSION.min_examined_row_limit,
    @session_rate = @@SESSION.log_slow_rate_limit,
    @session_filter = @@SESSION.log_slow_filter;
SET SESSION slow_query_log = ON;
SET SESSION long_query_time = 1;
SET SESSION min_examined_row_limit = 0;
SET SESSION log_slow_rate_limit = 1;
SET SESSION log_slow_filter = '';
SELECT SLEEP(1.2) AS slow_log_probe_13708;

쿼리가 정상 완료되면 반환값은 0이고, 시간 기준을 넘은 쿼리가 완료된 뒤 로그에 slow_log_probe_13708 문장이 보여야 합니다. 실제 시간은 부하에 따라 달라집니다. 이 쿼리는 기록 경로를 시험할 뿐 성능 측정이나 인덱스 검증용이 아닙니다.

파일을 확인한 뒤 같은 SQL 연결에서 시험용 세션 설정을 되돌립니다. 원래 값으로 돌아가는 것이므로, 이후 실제 수집은 의도한 전역 기본값을 상속하는 새 연결 등을 통해 별도로 확인합니다.

SET SESSION slow_query_log = @session_log;
SET SESSION long_query_time = @session_time;
SET SESSION min_examined_row_limit = @session_rows;
SET SESSION log_slow_rate_limit = @session_rate;
SET SESSION log_slow_filter = @session_filter;

Ubuntu·Rocky Linux에서 재시작 후에도 유지하기

SQL의 SET GLOBAL 변경은 옵션 파일의 영구 설정을 대신하지 않습니다. 먼저 서비스가 실제로 읽는 파일과 실행 인수를 확인합니다. 출력에 민감한 설정이 포함될 수 있으므로 그대로 외부에 공유하지 않습니다.

sudo systemctl cat mariadb
sudo mariadbd --help --verbose
sudo mariadbd --print-defaults

설정 파일을 확인하고 백업합니다. 일반적인 Ubuntu 패키지 설치는 /etc/mysql/mariadb.conf.d/, Rocky Linux의 일반적인 패키지 설치는 /etc/my.cnf.d/를 사용합니다. Rocky의 구성 경로는 Rocky Linux MariaDB 안내에서도 확인할 수 있습니다. 실제 포함 경로를 확인한 뒤 Ubuntu에서는 60-slow-log.cnf, Rocky에서는 slow-query.cnf 같은 전용 파일을 만들 수 있습니다. 같은 이름의 기존 파일이 있으면 덮어쓰지 말고 내용을 먼저 확인합니다.
서버 옵션 그룹에 아래 설정을 넣습니다. 나중에 읽히는 파일이나 시작 인수가 값을 덮어쓰는지 확인하고, 기존 중복 설정을 함께 정리합니다. 1초는 출발점일 뿐 모든 서비스에 적합한 고정 기준은 아닙니다.

[mariadb]
slow_query_log=ON
slow_query_log_file=mariadb-slow.log
log_output=FILE
long_query_time=1
log_slow_rate_limit=1
min_examined_row_limit=0

정비 시간에 재시작하고 결과를 재조회합니다. mariadbd --print-defaults로 파일이 읽히는지 살펴본 뒤 아래처럼 재시작합니다. 로컬 관리 접속 방식이 다르면 마지막 명령을 자신의 인증 방법에 맞춥니다. 서비스 이름도 실제 값을 사용합니다.

sudo systemctl restart mariadb
sudo systemctl status mariadb --no-pager
sudo mariadb -e "SHOW GLOBAL VARIABLES LIKE 'slow_query_log%'; SHOW GLOBAL VARIABLES LIKE 'long_query_time'; SHOW GLOBAL VARIABLES LIKE 'log_output';"

새 클라이언트 연결에서 경로·시간 기준·출력 방식과 세션 값을 다시 확인하고 시험 쿼리도 재실행합니다. 절대 경로를 사용하려면 MariaDB 서비스 계정의 쓰기 권한과 Ubuntu의 AppArmor, Rocky Linux의 SELinux 정책을 함께 검토해야 합니다. 예제는 기존 데이터 디렉터리 안의 상대 파일 이름으로 시작하여 새 폴더의 접근 정책 변경을 피합니다. 파일 검색 순서는 MariaDB 옵션 파일 설명을 참고합니다.

로그 파일과 테이블 읽기

파일 확인과 실행시간·빈도별 요약

SQL로 확인한 실제 경로를 사용하여 읽습니다. 아래의 /var/lib/mysql/는 예시이므로 데이터 디렉터리가 다르면 바꿉니다. tail -F는 파일 이름을 따라 새 기록을 보여주며, 확인을 끝내려면 Ctrl+C를 누릅니다.

sudo tail -n 80 /var/lib/mysql/mariadb-slow.log
sudo tail -F /var/lib/mysql/mariadb-slow.log

원문에서 시험 쿼리와 같은 항목을 찾은 뒤 Query_time, Lock_time, Rows_sent, Rows_examined를 함께 살펴봅니다. 대량 로그는 mariadb-dumpslow로 비슷한 SQL을 묶어 볼 수 있습니다. 설치된 패키지에 도구가 없으면 해당 배포판의 제공 패키지를 확인하며, 이전 이름인 mysqldumpslow로 설치되어 있을 수도 있습니다.

sudo mariadb-dumpslow -s t -t 10 /var/lib/mysql/mariadb-slow.log
sudo mariadb-dumpslow -s at -t 10 /var/lib/mysql/mariadb-slow.log
sudo mariadb-dumpslow -s c -t 10 /var/lib/mysql/mariadb-slow.log

-s t는 누적 쿼리 시간, -s at는 평균 쿼리 시간, -s c는 실행 횟수 기준으로 정렬하며, -t 10은 상위 10개 묶음을 보여줍니다. 숫자와 문자열은 기본적으로 묶음을 만들기 위해 추상화됩니다. 요약에서 우선순위를 찾은 뒤 원문 SQL과 실행 계획을 확인합니다.

TABLE 출력을 사용하는 경우

log_output이 TABLE 또는 FILE,TABLE을 포함하도록 설정되어 있다면 권한이 있는 계정으로 mysql.slow_log를 조회할 수 있습니다. 이 글의 FILE 예제만 적용한 경우 테이블에 새 기록이 없어도 파일 기록 실패를 뜻하지 않습니다.

SELECT start_time, query_time, lock_time,
       rows_sent, rows_examined, sql_text
FROM mysql.slow_log
ORDER BY start_time DESC
LIMIT 20;

테이블 출력의 기능과 부하는 파일 출력과 같지 않으며, 예를 들어 확장 EXPLAIN 출력은 파일에서만 제공됩니다. 운영 중인 공통 출력 설정을 임의로 바꾸지 말고 테이블로 로그 기록하기에서 제한을 확인합니다.

어떤 항목부터 개선하면 좋을까요?

한 번 오래 걸린 쿼리만 고르기보다 실행 빈도와 누적 시간을 같이 봅니다. 예를 들어 한 번 5초 걸린 쿼리와 0.2초씩 1,000번 실행된 쿼리는 평균과 누적 시간의 우선순위가 다릅니다. 다만 시간 기준이 1초라면 후자는 일반 시간 기준만으로는 수집되지 않으므로, 수집 조건 밖의 쿼리까지 전체 부하를 대표한다고 해석하면 안 됩니다.

항목	읽는 방법	추가 확인
`Query_time`	문장 실행에 걸린 시간	평균뿐 아니라 누적 시간·반복 횟수와 비교
`Lock_time`	기록된 잠금 관련 시간	값 하나만으로 모든 행 잠금 대기를 설명하지 않고 동시 실행 상황 확인
`Rows_examined`와 `Rows_sent`	조사한 행 수와 반환한 행 수 비교	조사량이 많으면 필터·조인·인덱스와 실행 계획 검토
같은 형태의 SQL 실행 횟수	반복 비용과 호출 빈도 확인	불필요한 반복 조회와 애플리케이션 호출 구조 검토

로그만으로 인덱스 추가를 결정하지 않습니다. 작은 테이블의 전체 스캔이 합리적일 수 있고, 인덱스는 쓰기 비용과 저장 공간도 늘립니다. 실제 조건의 실행 계획을 확인하고 변경 전후를 같은 조건으로 비교합니다. ANALYZE처럼 문장을 실제 실행하는 진단은 부하와 변경 가능성을 검토한 뒤 사용합니다.

로그가 생기지 않거나 너무 많이 쌓일 때

켜져 있는데 기록이 없음: log_output의 NONE 여부, 파일·테이블 선택, 실제 접속 인스턴스, 세션의 켜짐 여부와 시간 기준을 확인합니다. 쿼리가 완료되기 전에는 해당 기록이 아직 없을 수 있습니다.
시험 쿼리도 빠짐: 최소 조사 행 수, log_slow_filter, 제외 문장 종류와 log_slow_rate_limit를 봅니다. 1000은 모든 적격 쿼리의 기록을 뜻하지 않으며, 처음 확인할 때는 시험 세션에서 1을 사용합니다.
빠른 쿼리까지 기록됨: log_queries_not_using_indexes 또는 필터의 not_using_index가 켜져 있는지 확인합니다. 단순히 시간을 올려도 이 조건의 기록이 사라지지 않을 수 있습니다.
파일 접근 오류: 서버 서비스 계정의 권한, 상위 폴더 접근, AppArmor·SELinux 거부와 여유 공간을 확인합니다. 클라이언트를 실행한 사용자의 파일 쓰기 권한과 서버 프로세스 권한은 다릅니다.

Linux의 기본 확인 명령은 다음과 같습니다. 경로는 실제 값으로 바꿉니다. 시작 실패 시 로그의 거부 경로와 알 수 없는 옵션 오류를 확인하며 보안 정책 전체를 끄는 방식으로 해결하지 않습니다.

sudo journalctl -u mariadb -b -n 100 --no-pager
sudo journalctl -k -b --no-pager
sudo ls -ld /var/lib/mysql
sudo ls -l /var/lib/mysql/mariadb-slow.log
df -h /var/lib/mysql

수집 종료, 원래 설정 복원과 로그 관리

수집을 중단하려면 다음처럼 끕니다. 파일 삭제 명령이 아니므로 기존 기록은 남습니다. 옵션 파일에도 켜짐 설정이 있으면 다음 재시작에 다시 활성화되므로 영구 설정도 의도에 맞게 수정합니다.

SET GLOBAL slow_query_log = OFF;
SET SESSION slow_query_log = OFF;

처음에 원래 값을 저장한 동일한 관리 연결이 유지되고 있다면 아래 SQL로 바꿨던 전역 설정을 복원할 수 있습니다. 연결이 끊어졌다면 이 명령을 그대로 실행하지 말고 따로 기록한 실제 원래 값을 사용합니다. 이미 연결된 애플리케이션의 세션 값과 옵션 파일은 별도로 원상 복구 여부를 확인합니다.

SET GLOBAL slow_query_log = OFF;
SET GLOBAL slow_query_log_file = @saved_file;
SET GLOBAL log_output = @saved_output;
SET GLOBAL long_query_time = @saved_time;
SET GLOBAL slow_query_log = @saved_slow;

장기 수집에서는 보존 기간, 파일 크기와 디스크 여유 공간을 관리합니다. 파일을 이름만 바꾸면 서버가 이전 파일 핸들에 계속 기록할 수 있으므로, 기존 패키지의 logrotate 정책과 재열기 절차를 확인합니다. FLUSH SLOW LOGS는 느린 로그를 닫고 다시 여는 명령이며, 보관·압축·자동 삭제 정책을 만드는 명령은 아닙니다. 실행에는 해당 RELOAD 권한이 필요합니다.

FLUSH SLOW LOGS;

MariaDB Linux 로그 순환 문서와 FLUSH 문서에 따라 운영 정책과 맞춰 설정합니다. 데이터 디렉터리의 파일 이름을 사용했다면 기존 순환 정책이 그 경로까지 관리하는지도 확인합니다. SQL 본문에는 개인정보나 비밀값이 포함될 수 있으므로 로그와 백업의 접근 권한을 제한하고, 외부에 공유하기 전에는 민감한 값을 제거합니다.

MariaDB