장기 실행 쿼리는 느린 응답 시간부터 모든 사용자에게 영향을 미치는 본격적인 병목 현상에 이르기까지 모든 것을 유발하여 MySQL 데이터베이스 성능 측면에서 심각한 골칫거리가 될 수 있습니다. 이러한 성가신 쿼리를 처리하는 것(쿼리가 무엇인지, 왜 발생하는지, 관리 방법을 아는 것)은 데이터베이스를 원활하게 실행하는 데 중요합니다.

조기 발견, 중단, 자동 처리 방법 설정 등 이 가이드에서 모든 내용을 다룹니다.

장기 실행 쿼리란 무엇입니까?

MySQL의 장기 실행 쿼리는 실행하는 데 비정상적으로 오랜 시간이 걸리는 쿼리입니다.
쿼리를 "장기 실행"으로 분류하는 특정 기간은 애플리케이션의 성능 표준에 따라 달라질 수 있습니다. 일반적으로 쿼리가 평소보다 오래 실행되어 데이터베이스 속도가 느려지기 시작하면 장기 실행으로 간주됩니다.

장기 실행 쿼리의 근본 원인

장기 실행 쿼리의 원인은 다양할 수 있습니다.

• 적절한 인덱싱 부족 – 적절한 인덱싱이 없으면 MySQL은 필요한 데이터를 검색하기 위해 전체 테이블을 스캔해야 합니다. 이 프로세스는 특히 대규모 테이블의 경우 상당한 시간과 리소스를 소비하므로 매우 비효율적입니다.

• 과중한 부하 상황 – 서버가 대량의 쿼리를 처리하거나 몇 가지 복잡한 쿼리를 동시에 처리하는 경우 사용 가능한 리소스(CPU 및 메모리 등)가 얇아집니다. 이러한 리소스 경쟁으로 인해 쿼리 실행이 지연되어 특히 사용량이 가장 많은 기간 동안 실행 시간이 길어질 수 있습니다.

• 잠금 경합 – 이는 여러 트랜잭션이 동일한 데이터에 동시에 액세스해야 하지만 다른 작업이 필요한 잠금을 보유하고 있기 때문에 차단되는 경우에 발생합니다. 예를 들어, 한 트랜잭션이 행을 업데이트하는 경우 동일한 행을 읽거나 업데이트하려는 다른 트랜잭션은 첫 번째 트랜잭션이 완료되고 잠금이 해제될 때까지 기다려야 합니다.

• 부적절한 정규화 – 정규화는 데이터 중복을 방지하고 데이터 무결성을 향상시키는 데 도움이 되지만 과도하게 정규화된 데이터베이스는 여러 조인이 포함된 복잡한 쿼리로 이어질 수 있습니다. 이로 인해 성능이 저하될 수 있습니다. 반대로 정규화가 부족하면 데이터가 과도하게 중복되어 테이블이 커지고 쿼리 속도가 느려질 수 있습니다.

• 대형 조인 – 특히 적절한 인덱스가 없는 대규모 테이블 조인과 관련된 쿼리는 속도가 느려질 수 있습니다. 데이터베이스는 조인 조건을 기반으로 테이블 전체의 행을 일치시켜야 합니다. 이 프로세스는 리소스를 많이 사용하고 효율적인 인덱싱이 없으면 속도가 느려질 수 있습니다.

장기 실행 쿼리 식별

장기 실행 쿼리를 효과적으로 관리하려면 먼저 쿼리를 식별해야 합니다. 다음은 몇 가지 방법입니다.

1. SHOW PROCESSLIST 사용

프로세스 목록 표시; 명령은 서버에서 실행 중인 모든 활성 쿼리의 스냅샷을 얻는 빠른 방법입니다. 이 명령은 각 쿼리가 실행된 기간을 포함하여 여러 주요 정보와 함께 각 쿼리를 표시합니다. "시간" 값이 높은 쿼리는 장기 실행 쿼리일 가능성이 높습니다. 이 명령을 사용하는 방법은 다음과 같습니다.

전체 프로세스 목록 표시;

이 명령은 현재 프로세스를 모두 나열하고, 누가 프로세스를 시작했는지, 어떤 유형의 명령을 실행하고 있는지, 그리고 결정적으로 해당 프로세스를 수행한 시간을 보여줍니다. 비정상적으로 오랜 시간 동안 실행된 쿼리를 발견하면 이는 장기 실행 쿼리입니다. 그런 다음 최적화를 위해 더 깊이 파고들지 아니면 시스템 성능을 저하시키는 경우 간단히 제거할지 결정할 수 있습니다.

2. 느린 쿼리 로그

느린 쿼리 로그를 설정하는 것은 문제가 있는 쿼리를 포착하기 위한 또 다른 훌륭한 전략입니다. 이 편리한 MySQL 기능은 특정 임계값보다 실행하는 데 시간이 오래 걸리는 모든 쿼리를 기록합니다. 이는 장기 실행 쿼리를 잡는 것뿐만 아니라 인덱스를 효율적으로 사용하지 않는 쿼리를 식별하는 데도 도움이 될 수 있습니다.

느린 쿼리 로그를 실행하려면 MySQL 구성 파일(my.cnf 또는 my.ini)에서 몇 가지 설정을 조정해야 합니다.

• Slow_query_log – 로그를 활성화하려면 이 값을 1로 설정합니다.
• Slow_query_log_file - 로그를 저장할 파일 경로를 지정합니다.
• Long_query_time – 쿼리를 기록할 수 있는 최소 실행 시간(초)을 설정합니다. 예를 들어 '2'로 설정하면 실행하는 데 2초 이상 걸리는 모든 쿼리가 기록됩니다.

3. 성능 스키마

MySQL의 성능 스키마는 보다 자세한 조사에 매우 중요합니다. 이 도구는 서버 이벤트를 모니터링하고 성능 지표를 추적하도록 설계되어 쿼리 실행 및 전체 시스템 성능을 더욱 명확하게 보여줍니다.

다음 줄을 추가하여 MySQL 구성에서 활성화되었는지 확인하세요.

[mysqld]
Performance_schema = ON

활성화되면 다양한 성능 스키마 테이블을 탐색하여 쿼리 성능을 분석할 수 있습니다. 예를 들어, 장기 실행 쿼리를 정확히 찾아내려면 events_statements_history_long 테이블을 살펴보는 것이 좋습니다. 쿼리하는 방법은 다음과 같습니다.

SELECT EVENT_ID, SQL_TEXT, TIMER_WAIT/1000000000 AS '기간(초)'
Performance_schema.events_statements_history_long
에서 WHERE TIMER_WAIT > 10000000000;

이 쿼리는 10초 이상 실행된 쿼리를 찾는 데 도움이 됩니다. SQL 텍스트 및 각 쿼리가 실행된 기간과 같은 세부 정보를 제공합니다.

장기 실행 쿼리를 수동으로 종료

시간이 너무 오래 걸리고 시스템 리소스에 부담을 주는 쿼리를 식별한 경우 수동으로 종료할 수 있는 옵션이 있습니다. 이는 KILL 명령 뒤에 쿼리의 특정 프로세스 ID를 사용하여 수행됩니다.

현재 실행 중인 모든 프로세스와 해당 ID를 표시하는 SHOW PROCESSLIST 명령을 실행하여 프로세스 ID를 찾을 수 있습니다. 실행 기간을 나타내는 높은 "시간" 값을 표시하는 쿼리가 있는지 목록을 살펴보세요.

문제가 있는 쿼리를 식별하고 해당 프로세스 ID를 기록한 후에는 KILL 명령을 사용하여 쿼리를 종료할 수 있습니다.

KILL [프로세스 ID];

[프로세스 ID]를 SHOW PROCESSLIST 출력의 실제 번호로 바꿉니다.

이 접근 방식에 주의하세요. 쿼리를 갑자기 중지하면 쿼리가 정보를 쓰거나 업데이트하는 도중에 데이터가 일관되지 않은 상태로 유지되는 등의 문제가 발생할 수 있습니다.

장기 실행 쿼리 종료를 자동화하는 방법

장기 실행 쿼리를 처리하기 위해 자동화를 설정하면 느리거나 최적화되지 않은 쿼리로 인해 데이터베이스 리소스가 소모되고 전체 시스템이 느려지거나 잠기는 것을 방지하여 실질적인 생명을 구할 수 있습니다. 하지만 올바른 확인 없이 이 도구를 사용하면 주의가 필요한 더 깊은 성능 문제를 실제로 숨길 수 있으므로 주의 깊게 진행하세요.

종료된 쿼리가 애플리케이션에 미치는 영향을 분석하기 위해 포괄적인 로깅 및 모니터링이 마련되어 있는지 항상 확인하고 쿼리를 자동으로 종료하는 대신 쿼리 개선을 고려하세요. 자동 종료를 모든 문제를 해결하는 솔루션이 아니라 성능 최적화를 위한 더 큰 전략의 일부로 생각하세요.

1. 이벤트 스케줄러 활성화

먼저, 기본적으로 비활성화되어 있는 MySQL 이벤트 스케줄러를 활성화해야 합니다. 이벤트 스케줄러를 사용하면 서버가 미리 정의된 시간에 자동으로 실행하도록 하는 작업을 생성하고 예약할 수 있습니다. 다음 명령을 실행하십시오:

글로벌 event_scheduler 설정 = ON;

2. 장기 실행 쿼리를 종료하는 이벤트 만들기

스케줄러가 활성화되면 다음 단계는 장기 실행 쿼리를 모니터링하고 종료하는 실제 이벤트를 정의하는 것입니다. 이벤트는 지정된 임계값(예: 60초)보다 오랫동안 실행되는 쿼리를 확인하기 위해 1분마다 실행됩니다. 식별되면 이러한 쿼리가 자동으로 종료됩니다. 다음은 이 이벤트를 설정하는 SQL 코드의 분석입니다.

`이벤트 생성 kill_long_running_queries
ON SCHEDULE EVERY 1 MINUTE - 이벤트 실행 빈도를 지정합니다.
하다
시작하다
DECLARE 완료 INT DEFAULT FALSE;
DECLARE proc_id INT; -- 각 쿼리의 프로세스 ID를 저장하는 변수
information_schema.processlist
에서 ID 선택에 대한 cur1 커서 선언 WHERE 명령 = '쿼리' AND 시간 > 60; -- '60'을 초 단위로 임계값으로 변경합니다.
찾을 수 없는 세트에 대한 계속 핸들러 선언 done = TRUE;

현재 열기1;

read_loop: 루프
cur1을 proc_id로 가져오기;
완료했다면
읽기 루프에서 나가기;
종료 IF;
proc_id를 죽입니다. -- proc_id
로 식별된 프로세스를 종료합니다. 끝 루프;

현재 닫기1;
끝;`

3. MySQL에서 최대 쿼리 실행 시간 설정

쿼리의 최대 실행 시간을 제어하면 지나치게 오래 실행되는 쿼리로 인해 데이터베이스가 묶이는 것을 방지할 수 있습니다. 이는 모든 읽기 전용 SELECT 쿼리에 대해 시스템 전체 실행 시간 제한을 설정하여 MySQL 5.7.8 이상 버전에서 max_execution_time 시스템 변수를 사용하여 수행됩니다.

글로벌 설정 max_execution_time = 2000;

제한을 2000밀리초(2초)로 설정합니다.

이 설정은 저장 프로시저, 함수 또는 트리거에 적용되지 않으며 MySQL 구성 파일에 추가하지 않는 한 서버를 다시 시작할 때 기본값으로 재설정됩니다.

[mysqld]
max_execution_time = 2000

4. MariaDB에서 최대 명령문 시간 설정

MariaDB는 MySQL에서 파생되었지만 쿼리 실행 시간 관리에 대해 유사하지만 뚜렷한 접근 방식을 제공합니다. MariaDB 10.1.1부터 이 목적으로 max_statement_time 시스템 변수를 사용할 수 있습니다:

전역 max_statement_time = 2로 설정;

이는 모든 쿼리에 대해 실행 시간을 2초로 제한합니다.

서버 재시작을 통한 지속적인 구성을 위해서는 MariaDB 구성 파일에 다음 줄을 추가하세요:

[mysqld]
max_statement_time = 2

Releem의 쿼리 분석을 사용하여 장기 실행 쿼리 식별 및 해결

Releem의 쿼리 분석 도구는 데이터베이스 성능을 모니터링하고 최적화하는 방법을 혁신합니다. 상위 100개 쿼리에 대한 자세한 정보를 자동으로 수집하여 평균 실행 시간 및 각 쿼리가 데이터베이스 운영 효율성에 미치는 전반적인 영향과 같은 주요 지표를 제공합니다.

Releem을 사용하면 성능이 저하된 쿼리를 식별하기 위해 PROCESSLIST 출력을 수동으로 조사하거나 느린 쿼리 로그를 조사할 필요가 없습니다. 이 도구에는 지연되거나 과도한 시간이 소요되는 쿼리를 쉽게 정렬하고 찾아낼 수 있는 직관적인 대시보드가 ​​있습니다. 이러한 즉각적인 통찰력은 병목 현상을 즉시 식별하고 해결하는 데 도움이 됩니다.