Azure Database for MySQL 유연한 서버의 쿼리 성능 문제 해결

적용 대상: Azure Database for MySQL - 단일 서버 Azure Database for MySQL - 유연한 서버

Important

Azure Database for MySQL 단일 서버는 사용 중지 경로에 있습니다. Azure Database for MySQL 유연한 서버로 업그레이드하는 것이 좋습니다. Azure Database for MySQL - 유연한 서버로 마이그레이션하는 방법에 대한 자세한 내용은 Azure Database for MySQL 단일 서버에 대한 새로운 소식을 참조하세요.

쿼리 성능은 여러 요인에 의해 영향을 받을 수 있으므로, 먼저 Azure Database for MySQL 유연한 서버 인스턴스에서 발생하는 증상의 범위를 살펴보는 것이 중요합니다. 예를 들어 다음과 같은 경우 쿼리 성능이 느려지나요?

• Azure Database for MySQL 유연한 서버 인스턴스에서 실행되는 모든 쿼리는 무엇인가요?
• 특정 쿼리 세트
• 특정 쿼리

또한 쿼리하는 테이블의 구조 또는 기본 데이터의 최근 변경 내용이 성능에 영향을 줄 수 있습니다.

로깅 기능 사용

개별 쿼리를 분석하기 전에 쿼리 벤치마크를 정의해야 합니다. 이 정보가 있으면 데이터베이스 서버에서 로깅 기능을 구현하여 애플리케이션의 요구 사항에 따라 지정한 임계값을 초과하는 쿼리를 추적할 수 있습니다.

Azure Database for MySQL 유연한 서버는 느린 쿼리 로그 기능을 사용하여 실행하는 데 N초보다 오래 걸리는 쿼리를 식별하는 것이 좋습니다. 느린 쿼리 로그에서 쿼리를 식별한 후에는 MySQL 진단을 사용하여 이러한 쿼리 문제를 해결할 수 있습니다.

장기 실행 쿼리를 추적하려면 먼저 Azure Portal 또는 Azure CLI를 사용하여 slow_query_log 매개 변수를 사용하도록 설정해야 합니다. 이 매개 변수를 사용하도록 설정한 경우 long_query_time 매개 변수 값도 구성해야 합니다. 쿼리 실행 시간이 이 값(초)을 초과하면 "실행 속도가 느린" 쿼리로 식별됩니다. 이 매개 변수의 기본값은 10초이지만, 애플리케이션의 SLA 요구 사항을 충족하도록 값을 조정할 수 있습니다.

느린 쿼리 로그는 장기 실행 쿼리를 추적하는 데 매우 유용한 도구이지만, 느린 쿼리 로그가 효과적이지 않은 시나리오가 있습니다. 예를 들어 느린 쿼리 로그는 다음과 같은 특징이 있습니다.

• 쿼리 수가 매우 많거나 쿼리 문이 매우 큰 경우 성능에 부정적인 영향을 줍니다. long_query_time 매개 변수 값을 적절하게 조정하세요.
• 모든 행을 검색해야 하는 쿼리를 기록하도록 지정하는 log_queries_not_using_index 매개 변수도 사용하도록 설정한 경우에는 유용하지 않을 수 있습니다. 전체 인덱스 검사를 수행하는 쿼리는 인덱스를 활용하지만 인덱스가 반환되는 행 수를 제한하지 않으므로 기록됩니다.

로그에서 정보 검색

로그는 생성 시점에서 최대 7일까지 사용할 수 있습니다. Azure Portal 또는 Azure CLI를 통해 느린 쿼리 로그를 나열하고 다운로드할 수 있습니다. Azure Portal에서 서버로 이동하고 모니터링 아래에서 서버 로그를 선택한 다음, 항목 옆에 있는 아래쪽 화살표를 선택하여 조사 중인 날짜 및 시간과 연결된 로그를 다운로드합니다.

또한 느린 쿼리 로그가 진단 로그를 통해 Azure Monitor 로그와 통합되는 경우 편집기에서 쿼리를 실행하여 추가로 분석할 수 있습니다.

AzureDiagnostics
| where Resource == '<your server name>'
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, Resource , event_class_s, start_time_t , query_time_d, sql_text_s
| where query_time_d > 10

참고 항목

진단 로그를 통해 느린 쿼리 로그 진단을 시작하는 더 많은 예제는 Azure Monitor 로그에서 로그 분석을 참조하세요.

다음 스냅샷은 샘플 느린 쿼리를 보여줍니다.

# Time: 2021-11-13T10:07:52.610719Z
# User@Host: root[root] @  [172.30.209.6]  Id: 735026
# Query_time: 25.314811  Lock_time: 0.000000 Rows_sent: 126  Rows_examined: 443308
use employees;
SET timestamp=1596448847;
select * from titles where DATE(from_date) > DATE('1994-04-05') AND title like '%senior%';;

쿼리가 26초 만에 실행되었고, 443k개 행을 검사했으며, 126개의 결과 행을 반환했습니다.

일반적으로 Query_time 및 Rows_examined의 값이 높은 쿼리에 집중해야 합니다. 그러나 Query_time은 높지만 Rows_examined는 얼마 없는 쿼리를 발견할 경우 리소스 병목 상태가 있는 경우가 많습니다. 이러한 경우 IO 제한 또는 CPU 사용량을 확인해야 합니다.

쿼리 프로파일링

실행 속도가 느린 쿼리를 식별한 후에는 EXPLAIN 명령 및 프로파일링을 사용하여 자세한 세부 정보를 수집할 수 있습니다.

쿼리 계획을 확인하려면 다음 명령을 실행합니다.

EXPLAIN <QUERY>

참고 항목

EXPLAIN 문 사용에 대한 자세한 내용은 EXPLAIN을 사용하여 Azure Database for MySQL 유연한 서버에서 쿼리 성능을 프로파일링하는 방법을 참조하세요.

쿼리에 대한 EXPLAIN 계획을 만드는 것 외에도 SHOW PROFILE 명령을 사용하여 현재 세션 내에서 실행된 명령문 실행을 진단할 수 있습니다.

프로파일링을 사용하도록 설정하고 세션의 특정 쿼리를 프로파일링하려면 다음 명령 세트를 실행합니다.

SET profiling = 1;
<QUERY>;
SHOW PROFILES;
SHOW PROFILE FOR QUERY <X>;

참고 항목

개별 쿼리 프로파일링은 세션에서만 사용할 수 있으며 기록 명령문은 프로파일링할 수 없습니다.

이러한 명령을 사용하여 쿼리를 프로파일링하는 방법을 좀 더 자세히 살펴보겠습니다. 먼저 현재 세션에 프로파일링을 사용하도록 설정하고 SET PROFILING = 1 명령을 실행합니다.

mysql> SET PROFILING = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

다음으로, 전체 테이블 검색을 수행하는 최적이 아니면 쿼리를 실행합니다.

mysql> select * from sbtest8 where c like '%99098187165%';
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k       | c                                                                                                                       | pad                                                         |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 10 | 5035785 | 81674956652-89815953173-84507133182-62502329576-99098187165-62672357237-37910808188-52047270287-89115790749-78840418590 | 91637025586-81807791530-84338237594-90990131533-07427691758 |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (27.60 sec)

그런 다음, SHOW PROFILES 명령을 실행하여 사용 가능한 모든 쿼리 프로필 목록을 표시합니다.

mysql> SHOW PROFILES;
+----------+-------------+----------------------------------------------------+
| Query_ID | Duration    | Query                                              |
+----------+-------------+----------------------------------------------------+
|        1 | 27.59450000 | select * from sbtest8 where c like '%99098187165%' |
+----------+-------------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

마지막으로, 쿼리 1에 대한 프로필을 표시하려면 SHOW PROFILE FOR QUERY 1 명령을 실행합니다.

mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000102 |
| checking permissions |  0.000028 |
| Opening tables       |  0.000033 |
| init                 |  0.000035 |
| System lock          |  0.000018 |
| optimizing           |  0.000017 |
| statistics           |  0.000025 |
| preparing            |  0.000019 |
| executing            |  0.000011 |
| Sending data         | 27.594038 |
| end                  |  0.000041 |
| query end            |  0.000014 |
| closing tables       |  0.000013 |
| freeing items        |  0.000088 |
| cleaning up          |  0.000020 |
+----------------------+-----------+
15 rows in set, 1 warning (0.00 sec)

데이터베이스 서버에서 가장 많이 사용되는 쿼리 나열

쿼리 성능 문제를 해결할 때마다 Azure Database for MySQL 유연한 서버 인스턴스에서 가장 자주 실행되는 쿼리를 알아 두는 것이 좋습니다. 이 정보를 사용하여 상위 쿼리 중 실행 시간이 평소보다 오래 걸리는 쿼리가 있는지 측정할 수 있습니다. 또한 개발자 또는 DBA는 이 정보를 사용하여 쿼리 실행 횟수 및 기간이 갑자기 증가하는 쿼리가 있는지 식별할 수 있습니다.

Azure Database for MySQL 유연한 서버 인스턴스에 대해 가장 많이 실행된 상위 10개 쿼리를 나열하려면 다음 쿼리를 실행합니다.

SELECT digest_text AS normalized_query,
 count_star AS all_occurrences,
 Concat(Round(sum_timer_wait / 1000000000000, 3), ' s') AS total_time,
 Concat(Round(min_timer_wait / 1000000000000, 3), ' s') AS min_time,
 Concat(Round(max_timer_wait / 1000000000000, 3), ' s') AS max_time,
 Concat(Round(avg_timer_wait / 1000000000000, 3), ' s') AS avg_time,
 Concat(Round(sum_lock_time / 1000000000000, 3), ' s') AS total_locktime,
 sum_rows_affected AS sum_rows_changed,
 sum_rows_sent AS sum_rows_selected,
 sum_rows_examined AS sum_rows_scanned,
 sum_created_tmp_tables,
 sum_select_scan,
 sum_no_index_used,
 sum_no_good_index_used
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

참고 항목

이 쿼리를 사용하여 데이터베이스 서버에서 가장 많이 실행된 상위 쿼리를 벤치마킹하고 상위 쿼리가 변경되었는지 또는 초기 벤치마크에서 기존 쿼리의 실행 기간이 증가했는지 확인합니다.

총 실행 시간별로 가장 비용이 많이 드는 쿼리 10개 나열

다음 쿼리의 출력은 데이터베이스 서버에 대해 실행되는 상위 10개 쿼리와 데이터베이스 서버에서의 실행 횟수에 대한 정보를 제공합니다. 쿼리 대기 시간, 잠금 시간, 쿼리 런타임의 일부로 만든 임시 테이블 수 등과 같은 다른 유용한 정보도 제공합니다. 이 쿼리 출력을 사용하여 데이터베이스의 상위 쿼리를 추적하고 대기 시간과 같은 요인에 대한 변경 내용을 추적합니다. 이는 향후 위험을 방지하기 위해 쿼리를 추가로 미세 조정할 수 있는 기회를 나타낼 수 있습니다.

SELECT REPLACE(event_name, 'statement/sql/', '') AS statement, 
 count_star AS all_occurrences , 
 Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency, 
 Concat(Round(avg_timer_wait / 1000000000000, 2), ' s') AS avg_latency, 
 Concat(Round(sum_lock_time / 1000000000000, 2), ' s') AS total_lock_time  , 
 sum_rows_affected AS sum_rows_changed, 
 sum_rows_sent AS  sum_rows_selected, 
 sum_rows_examined AS  sum_rows_scanned, 
 sum_created_tmp_tables,  sum_created_tmp_disk_tables, 
 IF(sum_created_tmp_tables = 0, 0, Concat( Truncate(sum_created_tmp_disk_tables / 
 sum_created_tmp_tables * 100, 0))) AS 
 tmp_disk_tables_percent, 
 sum_select_scan, 
 sum_no_index_used, 
 sum_no_good_index_used 
FROM performance_schema.events_statements_summary_global_by_event_name 
WHERE event_name LIKE 'statement/sql/%' 
 AND count_star > 0 
ORDER BY sum_timer_wait DESC 
LIMIT 10;

InnoDB 가비지 수집 모니터링

InnoDB 가비지 수집이 차단되거나 지연되면 데이터베이스에서 스토리지 사용률 및 쿼리 성능에 부정적인 영향을 줄 수 있는 상당한 제거 지연이 발생할 수 있습니다.

InnoDB 롤백 세그먼트 HLL(기록 목록 길이)은 실행 취소 로그에 저장된 변경 레코드 수를 측정합니다. HLL 값이 증가하면 InnoDB의 가비지 수집 스레드(스레드 제거)가 쓰기 워크로드를 따라잡지 못하거나 장기 실행 쿼리 또는 트랜잭션에 의해 제거가 차단된다는 뜻입니다.

가비지 수집의 과도한 지연은 심각하고 부정적인 결과를 초래할 수 있습니다.

• InnoDB 시스템 테이블스페이스가 확장되므로 기본 스토리지 볼륨의 증가가 가속화됩니다. 가끔은 차단된 제거의 결과로 시스템 테이블스페이스가 수 테라바이트씩 팽창할 수 있습니다.
• 삭제 표시된 레코드는 적시에 제거되지 않습니다. 이로 인해 InnoDB 테이블스페이스가 확장되고 엔진이 이러한 레코드가 점유한 스토리지를 다시 사용하지 못하게 할 수 있습니다.
• 모든 쿼리의 성능이 저하될 수 있으며 InnoDB 스토리지 구조의 확장으로 인해 CPU 사용률이 증가할 수 있습니다.

따라서 HLL 값, 패턴 및 추세를 모니터링하는 것이 중요합니다.

HLL 값 찾기

show engine innodb status 명령을 실행하여 HLL 값을 찾을 수 있습니다. 이 값은 다음과 같이 출력의 TRANSACTIONS 제목 아래에 나열됩니다.

mysql> show engine innodb status\G 
*************************** 1. row *************************** 
 
(...) 
 
------------ 
TRANSACTIONS 
------------ 
Trx id counter 52685768 
Purge done for trx's n:o < 52680802 undo n:o < 0 state: running but idle 
History list length 2964300 
 
(...) 

다음과 같이 information_schema.innodb_metrics 테이블을 쿼리하여 HLL 값을 확인할 수도 있습니다.

mysql> select count from information_schema.innodb_metrics  
    -> where name = 'trx_rseg_history_len'; 
+---------+ 
|  count  | 
+---------+ 
| 2964300 | 
+---------+ 
1 row in set (0.00 sec)

HLL 값 해석

HLL 값을 해석할 때 다음 표에 나열된 지침을 고려해야 합니다.

값	참고
10,000 미만	가비지 수집이 뒤처지지 않음을 나타내는 정상 값입니다.
10,000~1,000,000	가비지 수집이 약간 지연되고 있음을 나타냅니다. 이러한 값이 증가하지 않고 안정적으로 유지된다면 허용할 수 있습니다.
1,000,000 초과	이러한 값을 조사해야 하며 수정 작업이 필요할 수 있습니다.

과도한 HLL 값 해결

HLL이 급증을 보이거나 주기적인 증가 패턴을 보이는 경우 Azure Database for MySQL 유연한 서버 인스턴스에서 실행되는 쿼리 및 트랜잭션을 즉시 조사합니다. 그런 다음, 가비지 수집 프로세스의 진행을 방해할 수 있는 워크로드 문제를 해결할 수 있습니다. 데이터베이스에서 제거 지연이 없지는 않겠지만, 지연이 걷잡을 수 없이 높아지게 두면 안 됩니다.

예를 들어 information_schema.innodb_trx 테이블에서 트랜잭션 정보를 얻으려면 다음 명령을 실행합니다.

select * from information_schema.innodb_trx  
order by trx_started asc\G

trx_started 열의 세부 정보는 트랜잭션 기간을 계산하는 데 도움이 됩니다.

mysql> select * from information_schema.innodb_trx  
    -> order by trx_started asc\G 
*************************** 1. row *************************** 
                    trx_id: 8150550 
                 trx_state: RUNNING 
               trx_started: 2021-11-13 20:50:11 
     trx_requested_lock_id: NULL 
          trx_wait_started: NULL 
                trx_weight: 0 
       trx_mysql_thread_id: 19 
                 trx_query: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';
(…) 

세션의 현재 상태에서 소요된 시간을 포함하여 현재 데이터베이스 세션에 대한 자세한 내용은 information_schema.processlist 테이블을 확인하세요. 예를 들어 다음 출력은 지난 1462초 동안 쿼리를 적극적으로 실행한 세션을 보여줍니다.

mysql> select user, host, db, command, time, info  
    -> from information_schema.processlist  
    -> order by time desc\G 
*************************** 1. row *************************** 
   user: test 
   host: 172.31.19.159:38004 
     db: employees 
command: Query 
   time: 1462 
   info: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';
 
(...) 

권장 사항

• 데이터베이스에 쿼리를 실행하기에 충분한 리소스가 할당되어 있는지 확인합니다. 워크로드를 수용하기 위해 인스턴스 크기를 스케일 업하여 더 많은 CPU 코어와 추가 메모리를 확보해야 할 때가 있습니다.

• 여러 개의 작은 트랜잭션으로 분할하여 큰 트랜잭션 또는 장기 실행 트랜잭션을 방지하세요.

• 워크로드에 따라 innodb_purge_threads를 구성하여 백그라운드 제거 작업의 효율성을 향상하세요.

  참고 항목

  각 환경에 대해 이 서버 변수의 변경 내용을 테스트하여 엔진 동작의 변화를 측정합니다.

• 시스템이 지정된 임계값을 초과하면 알림을 받을 수 있도록 "호스트 CPU 백분율", "호스트 메모리 백분율" 및 "총 연결 수"에 대한 경고를 사용합니다.

• Query Performance Insights 또는 Azure Workbooks를 사용하여 문제가 있거나 느리게 실행되는 쿼리를 식별한 다음, 최적화합니다.

• 프로덕션 데이터베이스 서버의 경우 모든 것이 원활하게 실행되고 있는지 확인하기 위해 정기적으로 진단을 수집합니다. 그렇지 않은 경우 식별한 문제를 해결합니다.

다음 단계

가장 중요한 질문에 대한 동료 대답을 찾거나 질문을 게시하거나 대답을 얻기 위해 Stack Overflow를 방문합니다.