슬로우 쿼리란?
사용자가 지정한 특정 시간(seconds) 이상 걸리는 쿼리를 의미한다.
1. MySQL 설치 및 실행
brew update
brew install mysql
brew services start mysql 위에서부터 차례대로 homebrew 업데이트, mysql 설치, mysql 실행이다.
2. 샘플 DataBase 다운로드
먼저 샘플 DB를 다운받는다.
방법1. MySQL 공식 사이트에서 원하는 샘플을 다운받고 압축 해제
방법2. curl -O https://downloads.mysql.com/docs/sakila-db.tar.gz 명령어로 현재 디렉토리에 다운받고 tar -xvf sakila-db.tar.gz로 압축 해제
압축 해제하면 다운받은 샘플 DB 폴더가 생기고, 그 안에 data와 schema와 모델링 파일(.mwb)이 있다. .mwb 파일은 해당 데이터베이스의 ERD 및 인덱스를 포함한 데이터베이스 구조를 표현하는 파일이라고 한다.
나는 sakila sample db를 다운 받아서 위의 이미지와 같은 폴더가 생성됐다.
3. MySQL에 샘플 DataBase 설치
mysql -u root -p 명령어로 DB에 root라는 사용자로 접속한다. (비밀번호는 설정하지 않았을 경우 엔터 입력)
SOURCE {path}/sakila-db/sakila-schema.sql;
SOURCE {path}/sakila-db/sakila-data.sql;{path}에는 샘플 DB가 있는 파일 경로를 입력한다.
만약 다운로드 폴더에 샘플 DB가 있다면 아래와 같이 입력한다.
SOURCE 명령어를 사용해서 파일에 있는 SQL문을 실행한다면, 성공적으로 databases와 tables이 생성된 것을 볼 수 있다.
4. 슬로우 쿼리 프로파일링 설정
먼저, my.cnf 파일을 찾아서 아래와 같이 수정한다. (homebrew로 설치한 경우 /opt/homebrew/etc 에 위치했을 가능성이 높다.)
slow_query_log 는 0 또는 1의 값을 갖는다. default 값은 0으로 슬로우 쿼리 로그를 비활성화한다. no argument거나 값이 1인 경우 슬로우 쿼리 로그가 활성화된다.
log_output 은 슬로우 쿼리를 기록할 위치를 지정한다. TABLE, FILE, NONE 값을 가지며 디폴트는 FILE이다.
slow_query_log_file 는 슬로우 쿼리 로그 파일의 경로를 지정한다.
long_query_time 는 슬로우 쿼리로 간주되는 쿼리의 실행 시간(초)을 지정한다. 기본값은 10이며, 0~31536000의 범위를 가질 수 있다. 31536000는 365일을 second로 바꾼 것이다.
5. MySQL 재시작
brew services restart mysql 로 mysql을 재시작해 변경사항을 반영한다.
➡️ `/opt/homebrew/var/mysql/slow-query.log` 파일이 생성되었다.
추가로, 아래의 명령어를 입력하면 my.cnf 에서 설정한 값을 확인할 수 있다.
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';
6. 슬로우 쿼리 실행
먼저, 매우 간단한 쿼리를 실행해보자.
SELECT SLEEP(0.1);
=> 실행 시간이 0.2 sec 미만이므로 slow-query.log 에 기록 ❌
다음으로 실행 시간이 0.2 sec 초과인 쿼리를 실행해보자.
SELECT SLEEP(0.5);
=> slow-query.log 에 정상적으로 기록 ✅
어떤 정보가 기록되는지 확인하기 위해 최상단에 위치한 주석을 먼저 보자.
슬로우 쿼리의 시간: # Time: 2024-07-24T06:22:43.696156Z
사용자와 호스트 정보: # User@Host: root[root] @ localhost [] Id: 8 (Id: 8은 MySQL 세션의 ID)
쿼리 상세 정보: # Query_time: 0.516293 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
Query_time: 쿼리가 실행되는 데 걸린 시간Lock_time: 쿼리 실행 중에 잠금이 걸려 있는 시간Rows_sent: 쿼리 결과로 전송된 행의 수Rows_examined: 쿼리 실행 중에 검사된 행의 수
주석 아래에는 구체적인 쿼리 내용이 서술된다.
use sakila;
SET timestamp=1721802163;
SELECT SLEEP(0.5);sakila 데이터베이스를 사용했고, 0.5초 동안 대기하는 SELECT SLEEP(0.5); 쿼리를 실행했다는 것을 알 수 있다.
중간의 SET timestamp=1721802163; 명령은 쿼리의 시작 시간을 지정하는 역할이다. 다음 줄인 SELECT SLEEP(0.5); 쿼리의 시작 시간을 timestamp=1721802163; 으로 지정했는데, Unix 타임스탬프 형식이라 시간을 확인하기 위해서는 계산을 거쳐야 한다.
조금 더 복잡한 쿼리를 날려보자.
SELECT
c.customer_id,
c.first_name,
c.last_name,
COUNT(rental.rental_id) AS total_rentals,
(SELECT SUM(p.amount)
FROM payment p
WHERE p.customer_id = c.customer_id
AND p.payment_date > '2005-01-01') AS total_payments,
(SELECT COUNT(*)
FROM rental r
JOIN inventory i ON r.inventory_id = i.inventory_id
JOIN film f ON i.film_id = f.film_id
WHERE r.customer_id = c.customer_id
AND f.rating = 'R') AS r_rated_rentals
FROM
customer c
LEFT JOIN rental ON c.customer_id = rental.customer_id
LEFT JOIN payment ON c.customer_id = payment.customer_id
GROUP BY
c.customer_id,
c.first_name,
c.last_name
ORDER BY
total_rentals DESC
LIMIT 1000;
정상적으로 기록이 된다.
참고자료
https://dev.mysql.com/doc/refman/8.4/en/slow-query-log.html
