웹사이트 검색

MySQL 쿼리 프로파일링 사용 방법


소개

MySQL 쿼리 프로파일링은 데이터베이스 기반 애플리케이션의 전체 성능을 분석하려고 할 때 유용한 기술입니다. 중대형 규모의 애플리케이션을 개발할 때 대규모 코드 베이스 전체에 수백 개의 쿼리가 분산되는 경향이 있으며 잠재적으로 초당 수많은 쿼리가 데이터베이스에 대해 실행됩니다. 일종의 쿼리 프로파일링 기술이 없으면 병목 현상 및 응용 프로그램 속도 저하의 위치와 원인을 파악하기가 매우 어려워집니다. 이 기사에서는 MySQL 서버에 내장된 도구를 사용하여 몇 가지 유용한 쿼리 프로파일링 기술을 보여줍니다.

MySQL 느린 쿼리 로그는 무엇입니까?

MySQL 느린 쿼리 로그는 MySQL이 느리고 잠재적으로 문제가 있는 쿼리를 보내는 로그입니다. 이 로깅 기능은 MySQL과 함께 제공되지만 기본적으로 꺼져 있습니다. 기록되는 쿼리는 애플리케이션의 성능 요구 사항을 기반으로 쿼리 프로파일링을 허용하는 사용자 지정 가능한 서버 변수에 의해 결정됩니다. 일반적으로 기록되는 쿼리는 지정된 실행 시간보다 오래 걸리는 쿼리 또는 인덱스에 제대로 적중하지 않는 쿼리입니다.

프로파일링 변수 설정

MySQL 느린 쿼리 로그를 설정하기 위한 기본 서버 변수는 다음과 같습니다.

slow_query_log			G 
slow_query_log_file			G 
long_query_time			G / S
log_queries_not_using_indexes	G
min_examined_row_limit		G / S

참고: (G) 전역 변수, (S) 세션 변수

slow_query_log - 느린 쿼리 로그를 켜고 끄는 부울입니다.

slow_query_log_file - 쿼리 로그 파일의 절대 경로입니다. 파일의 디렉토리는 mysqld 사용자가 소유해야 하며 읽고 쓸 수 있는 올바른 권한이 있어야 합니다. mysql 데몬은 "mysql"로 실행될 가능성이 높지만 확인하려면 Linux 터미널에서 다음을 실행하십시오.

 ps -ef | grep bin/mysqld | cut -d' ' -f1

출력에는 현재 사용자와 mysqld 사용자가 표시될 수 있습니다. 디렉토리 경로 /var/log/mysql을 설정하는 예:

cd /var/log
mkdir mysql
chmod 755 mysql
chown mysql:mysql mysql

long_query_time - 쿼리 길이를 확인하는 시간(초)입니다. 값이 5이면 실행하는 데 5초 이상 걸리는 모든 쿼리가 기록됩니다.

log_queries_not_using_indexes - 인덱스에 도달하지 않는 쿼리를 기록할지 여부를 나타내는 부울 값입니다. 쿼리 분석을 수행할 때 인덱스에 도달하지 않는 쿼리를 기록하는 것이 중요합니다.

min_examined_row_limit - 검사해야 하는 행 수에 대한 하한을 설정합니다. 값 1000은 1000개 미만의 행을 분석하는 모든 쿼리를 무시합니다.

MySQL 서버 변수는 MySQL conf 파일에서 설정하거나 MySQL GUI 또는 MySQL 명령줄을 통해 동적으로 설정할 수 있습니다. conf 파일에 변수가 설정되어 있으면 서버를 다시 시작할 때 변수가 유지되지만 활성화하려면 서버를 다시 시작해야 합니다. MySQL conf 파일은 일반적으로 \/etc 또는 /usr\, 일반적으로 \/etc/my.cnf\ 또는 \/etc/mysql/my.cnf\에 있습니다. conf 파일을 찾으려면(더 많은 루트 디렉토리로 검색 범위를 넓혀야 할 수 있음):

find /etc -name my.cnf
find /usr -name my.cnf

conf 파일을 찾으면 [mysqld] 제목 아래에 원하는 값을 추가하기만 하면 됩니다.

[mysqld]
….
slow-query-log = 1
slow-query-log-file = /var/log/mysql/localhost-slow.log
long_query_time = 1
log-queries-not-using-indexes

다시 말하지만 변경 사항은 서버를 다시 시작할 때까지 적용되지 않으므로 변경 사항이 즉시 필요한 경우 변수를 동적으로 설정하십시오.

mysql> SET GLOBAL slow_query_log = 'ON';
mysql> SET GLOBAL slow_query_log_file = '/var/log/mysql/localhost-slow.log';
mysql> SET GLOBAL log_queries_not_using_indexes = 'ON';
mysql> SET SESSION long_query_time = 1;
mysql> SET SESSION min_examined_row_limit = 100;

변수 값을 확인하려면:

mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
mysql> SHOW SESSION VARIABLES LIKE 'long_query_time';

MySQL 변수를 동적으로 설정하는 것의 한 가지 단점은 서버를 다시 시작할 때 변수가 손실된다는 것입니다. 유지해야 하는 중요한 변수는 MySQL conf 파일에 추가하는 것이 좋습니다.

참고: SET를 통해 변수를 동적으로 설정하고 conf 파일에 배치하는 구문은 약간 다릅니다. \slow_query_log\ 대 \slow-query-log\. 다양한 구문에 대한 MySQL의 동적 시스템 변수 페이지를 확인하십시오. Option-File Format은 conf 파일의 형식이고 System Variable Name은 변수를 동적으로 설정하기 위한 변수 이름입니다.

쿼리 프로필 데이터 생성

이제 MySQL 느린 쿼리 로그 구성이 설명되었으므로 프로파일링을 위한 일부 쿼리 데이터를 생성할 시간입니다. 이 예제는 이전에 느린 로그 구성이 설정되지 않은 실행 중인 MySQL 인스턴스에서 작성되었습니다. 예제의 쿼리는 MySQL GUI 또는 MySQL 명령 프롬프트를 통해 실행할 수 있습니다. 느린 쿼리 로그를 모니터링할 때 서버에 대해 두 개의 연결 창을 열어 두는 것이 유용합니다. 하나는 MySQL 문을 작성하기 위한 연결이고 다른 하나는 쿼리 로그를 보기 위한 연결입니다.

MySQL 콘솔 탭에서 SUPER ADMIN 권한이 있는 사용자로 MySQL 서버에 로그인합니다. 시작하려면 테스트 데이터베이스와 테이블을 만들고 더미 데이터를 추가한 다음 느린 쿼리 로그를 켭니다. 이 예제는 이상적으로는 MySQL을 사용하는 다른 애플리케이션이 없는 개발 환경에서 실행해야 쿼리 로그가 모니터링될 때 복잡해지는 것을 방지할 수 있습니다.

$> mysql -u  -p

mysql> CREATE DATABASE profile_sampling;
mysql> USE profile_sampling;
mysql> CREATE TABLE users ( id TINYINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) );
mysql> INSERT INTO users (name) VALUES ('Walter'),('Skyler'),('Jesse'),('Hank'),('Walter Jr.'),('Marie'),('Saul'),('Gustavo'),('Hector'),('Mike');

mysql> SET GLOBAL slow_query_log = 1;
mysql> SET GLOBAL slow_query_log_file = '/var/log/mysql/localhost-slow.log';
mysql> SET GLOBAL log_queries_not_using_indexes = 1;
mysql> SET long_query_time = 10;
mysql> SET min_examined_row_limit = 0;

이제 소량의 테스트 데이터가 포함된 테스트 데이터베이스와 테이블이 있습니다. 느린 쿼리 로그가 켜져 있지만 쿼리 시간이 의도적으로 높게 설정되었고 최소 행 검사 플래그가 계속 꺼져 있습니다. 로그를 보기 위한 콘솔 탭에서:

cd /var/log/mysql
ls -l

쿼리가 실행되지 않았으므로 아직 폴더에 느린 쿼리 로그가 없어야 합니다. 있는 경우 이는 느린 쿼리 로그가 과거에 설정되고 구성되었음을 의미하므로 이 예제의 결과 중 일부가 왜곡될 수 있습니다. MySQL 탭으로 돌아가서 다음 SQL을 실행합니다.

mysql> USE profile_sampling;
mysql> SELECT * FROM users WHERE id = 1;

실행된 쿼리는 테이블의 기본 키 인덱스를 사용한 단순 선택이었습니다. 이 쿼리는 빠르고 인덱스를 사용했으므로 이 쿼리에 대한 느린 쿼리 로그에는 항목이 없습니다. 쿼리 로그 디렉터리를 다시 살펴보고 생성된 로그가 없는지 확인합니다. 이제 MySQL 창으로 돌아가서 다음을 실행합니다.

mysql> SELECT * FROM users WHERE name = 'Jesse';

이 쿼리는 인덱싱되지 않은 열 이름에서 실행되었습니다. 이 시점에서 로그에는 다음 정보가 포함된 쿼리가 있습니다(정확히 동일하지 않을 수 있음).

/var/log/mysql/localhost-slow.log

# Time: 140322 13:54:58
# User@Host: root[root] @ localhost []
# Query_time: 0.000303  Lock_time: 0.000090 Rows_sent: 1  Rows_examined: 10
use profile_sampling;
SET timestamp=1395521698;
SELECT * FROM users WHERE name = 'Jesse';

쿼리가 성공적으로 기록되었습니다. 또 하나의 예입니다. 검사된 최소 행 제한을 높이고 유사한 쿼리를 실행합니다.

mysql> SET min_examined_row_limit = 100;
mysql> SELECT * FROM users WHERE name = 'Walter';

최소 100개의 행이 분석되지 않았기 때문에 로그에 데이터가 추가되지 않습니다.

참고: 로그에 채워지는 데이터가 없는 경우 확인할 수 있는 몇 가지 사항이 있습니다. 먼저 로그가 생성되는 디렉토리의 권한입니다. 소유자/그룹은 mysqld 사용자(위의 예 참조)와 동일해야 하며 올바른 권한(chmod 755)을 가져야 합니다. 둘째, 예제를 방해하는 기존의 느린 쿼리 변수 구성이 있을 수 있습니다. conf 파일에서 느린 쿼리 변수를 제거하고 서버를 다시 시작하여 기본값을 재설정하거나 전역 변수를 동적으로 다시 기본값으로 설정합니다. 동적으로 변경된 경우 로그아웃했다가 MySQL에 다시 로그인하여 글로벌 업데이트가 적용되도록 하십시오.

쿼리 프로필 정보 분석

위의 예에서 쿼리 프로필 데이터를 보면 다음과 같습니다.

# Time: 140322 13:54:58
# User@Host: root[root] @ localhost []
# Query_time: 0.000303  Lock_time: 0.000090 Rows_sent: 1  Rows_examined: 10
use profile_sampling;
SET timestamp=1395521698;
SELECT * FROM users WHERE name = 'Jesse';

항목이 표시됩니다.

  • 쿼리가 실행된 시간
  • 실행한 사람
  • 검색에 걸린 시간
  • 잠금 길이
  • 반환된 행 수
  • 검사된 행 수

이는 서버 변수로 지정된 성능 요구 사항을 위반하는 모든 쿼리가 로그에 기록되기 때문에 유용합니다. 이를 통해 개발자 또는 관리자는 쿼리가 제대로 수행되지 않을 때 [소스 코드를 읽고 잘못 작성된 쿼리를 찾으려고 시도하는 것과 반대되는] MySQL이 경고하도록 할 수 있습니다. 또한 쿼리 프로파일링 데이터는 일정 기간 동안 프로파일링될 때 유용할 수 있으며, 이는 어떤 상황이 애플리케이션 성능 저하에 영향을 미치는지 확인하는 데 도움이 될 수 있습니다.

mysqldumpslow 사용

보다 현실적인 예에서 프로파일링은 데이터베이스 기반 응용 프로그램에서 활성화되어 프로파일링할 적절한 데이터 스트림을 제공합니다. 로그는 계속해서 기록될 것이며 다른 사람이 보는 것보다 더 자주 기록될 것입니다. 로그 크기가 커지면 모든 데이터를 파싱하기 어려워지고 문제가 있는 쿼리는 로그에서 쉽게 손실됩니다. MySQL은 느린 쿼리 로그를 분해하여 이 문제를 방지하는 데 도움이 되는 또 다른 도구인 mysqldumpslow를 제공합니다. 바이너리는 MySQL(Linux의 경우)과 함께 번들로 제공되므로 간단히 명령을 실행하고 로그 경로를 전달하면 됩니다.

mysqldumpslow -t 5 -s at /var/log/mysql/localhost-slow.log

출력을 사용자 지정하는 데 도움이 되는 명령과 함께 사용할 수 있는 다양한 매개 변수가 있습니다. 위의 예에서는 평균 쿼리 시간을 기준으로 정렬된 상위 5개의 쿼리가 표시됩니다. 결과 행은 더 읽기 쉽고 쿼리별로 그룹화됩니다(이 출력은 높은 값을 보여주는 예제와 다릅니다).

Count: 2  Time=68.34s (136s)  Lock=0.00s (0s)  Rows=39892974.5 (79785949), root[root]@localhost
  SELECT PL.pl_title, P.page_title
  FROM page P
  INNER JOIN pagelinks PL
  ON PL.pl_namespace = P.page_namespace
  WHERE P.page_namespace = N
… 

표시되는 데이터:

  • 개수 - 쿼리가 기록된 횟수
  • 시간 -()의 평균 시간과 총 시간 모두
  • 잠금 - 테이블 잠금 시간
  • 행 - 반환된 행 수

이 명령은 숫자와 문자열을 추상화하므로 WHERE 절이 다른 동일한 쿼리는 동일한 쿼리로 계산됩니다(page_namespace = N에 유의). mysqldumpslow와 같은 도구를 사용하면 느린 쿼리 로그를 지속적으로 감시할 필요가 없으며 대신 주기적 또는 자동 검사가 가능합니다. mysqldumpslow 명령에 대한 매개변수는 로그의 다양한 쿼리로 드릴다운하는 데 도움이 되는 몇 가지 복잡한 표현식 일치를 허용합니다.

다양한 데이터 보기를 제공하는 타사 로그 분석 도구도 있습니다. 가장 많이 사용되는 도구는 pt-query-digest입니다.

쿼리 분석

알아야 할 마지막 프로파일링 도구는 쿼리를 복잡하게 분석할 수 있는 도구입니다. 이 도구의 좋은 사용 사례는 느린 쿼리 로그에서 문제가 있는 쿼리를 가져와서 MySQL에서 직접 실행하는 것입니다. 첫 번째 프로파일링을 켜야 합니다. 그런 다음 쿼리가 실행됩니다.

mysql> SET SESSION profiling = 1;
mysql> USE profile_sampling;
mysql> SELECT * FROM users WHERE name = 'Jesse';
mysql> SHOW PROFILES;

프로파일링이 켜진 후 SHOW PROFILES는 Query_ID를 SQL 문에 연결하는 테이블을 표시합니다. 실행한 쿼리에 해당하는 Query_ID를 찾아 다음 쿼리를 실행합니다(#을 Query_ID로 바꿉니다).

mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=#;

샘플 출력:

SEQ STATE DURATION
1 starting 0.000046
2 checking permissions 0.000005
3 opening tables 0.000036
... ... ...

STATE는 쿼리 실행 프로세스의 "단계"이고 DURATION은 해당 단계를 완료하는 데 걸린 시간(초)입니다. 이것은 지나치게 유용한 도구는 아니지만 흥미롭고 쿼리 실행의 어떤 부분이 가장 많은 대기 시간을 유발하는지 확인하는 데 도움이 될 수 있습니다.

다양한 열에 대한 자세한 개요:

다양한 "단계"에 대한 자세한 개요:

참고: 이 도구는 프로덕션 환경에서 특정 쿼리를 분석하는 데 사용해서는 안 됩니다.

느린 쿼리 로그 성능

해결해야 할 마지막 질문은 느린 쿼리 로그가 성능에 미치는 영향입니다. 일반적으로 프로덕션 환경에서 느린 쿼리 로그를 실행하는 것이 안전합니다. CPU도 I/O 로드도 문제가 되지 않습니다 ². 그러나 로그 파일 크기가 파일 시스템에 비해 너무 커지지 않도록 로그 크기를 모니터링하기 위한 몇 가지 전략이 있어야 합니다. 또한 프로덕션 환경에서 느린 쿼리 로그를 실행할 때 좋은 경험 법칙은 long_query_time을 1초 이상으로 두는 것입니다.

중요: 프로파일링 도구인 SET profiling=1을 사용하거나 작업 부하가 높은 프로덕션 환경에서 모든 쿼리(예: general_log 변수)를 기록하는 것은 좋지 않습니다.

결론

느린 쿼리 로그는 문제가 있는 쿼리를 선별하고 전체 쿼리 성능을 프로파일링하는 데 매우 유용합니다. 느린 쿼리 로그로 쿼리 프로파일링을 수행할 때 개발자는 애플리케이션 MySQL 쿼리가 수행되는 방식을 심층적으로 이해할 수 있습니다. mysqldumpslow와 같은 도구를 사용하여 느린 쿼리 로그를 모니터링하고 평가하면 관리가 쉬워지고 개발 프로세스에 쉽게 통합될 수 있습니다. 문제가 있는 쿼리가 식별되었으므로 다음 단계는 최대 성능을 위해 쿼리를 조정하는 것입니다.