[MySQL&Slow Query Monitoring] 데이터베이스 슬로우 쿼리 모니터링 및 개선

데이터베이스 슬로우 쿼리 모니터링 및 개선

슬로우 쿼리 프로파일링 설정

MySQL Configuration 파일 (my.cnf) 업데이트

cat <<EOF > ./etc/my.cnf
[mysqld]
log_output = TABLE
slow_query_log = 1
long_query_time = 0.05
EOF

• log_output = TABLE : 슬로우 쿼리를 TABLE로 출력, FILE로도 설정가능
  • FILE로 설정했다면 slow_query_log_file로 출력할 파일 위치를 설정
• slow_query_log = 1 : 슬로우 쿼리 활성화
• long_query_time = 0.05 : 아래 변수에 지정된 초 이상 쿼리가 수행되면 슬로우 쿼리에 기록

파일을 수정했다면 MySQL 서버를 재시작한다.

만약 MySQL 서버를 재시작하지 않고 슬로우 쿼리를 활성화하고 싶다면 MySQL 서버에서 아래 명령어를 실행

SET GLOBAL log_output = 'TABLE';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.05;

아래 명령어를 MySQL 서버에서 실행하면 슬로우 쿼리가 설정되었는지 확인

SHOW VARIABLES WHERE
Variable_Name LIKE 'slow_query_log' OR
Variable_Name LIKE 'long_query_time' OR
Variable_Name LIKE 'log_output';

비효율적인 쿼리 실행

EX) university schema - 약 0.169 s

SELECT s.ID, s.name AS student_name,
       (SELECT i.name
        FROM advisor a
        JOIN instructor i ON a.i_id = i.ID
        WHERE a.s_id = s.ID) AS advisor_name,
       (SELECT c.title
        FROM section sec
        JOIN course c ON sec.course_id = c.course_id
        WHERE sec.course_id = t.course_id
          AND sec.sec_id = t.sec_id
          AND sec.semester = t.semester
          AND sec.year = t.year) AS course_title,
       sec.building,
       sec.room_number,
       t.grade
FROM student s
JOIN takes t ON s.ID = t.ID
JOIN section sec ON t.course_id = sec.course_id
                 AND t.sec_id = sec.sec_id
                 AND t.semester = sec.semester
                 AND t.year = sec.year
WHERE (SELECT i.dept_name
       FROM advisor a
       JOIN instructor i ON a.i_id = i.ID
       WHERE a.s_id = s.ID) = 'Comp. Sci.'
AND sec.semester = 'Fall';

슬로우 쿼리 로그 조회

SELECT * FROM mysql.slow_log;

SELECT start_time, user_host, query_time, lock_time, rows_sent, rows_examined, db, CONVERT(sql_text USING utf8 ) sql_text
FROM mysql.slow_log;

• start_title: 쿼리가 시작된 시간
• user_host: 쿼리를 실행한 사용자
• query_time: 쿼리가 실행되는 데 걸린 시간
• lock_time: 테이블 잠금에 대한 대기 시간
• rows_sent: 실제 클라이언트에 전달된 레코드의 수
• rows_examined: 쿼리가 처리되기 위해 실제 접근한 레코드의 수
• db: 쿼리가 수행된 데이터베이스
• sql_text: 실제 수행된 쿼리

최적화 수행

• subquery → join
• indexing

참고: https://dreamcoding.tistory.com/62