Azure Database for MySQL의 높은 CPU 사용률 문제 해결 - 유연한 서버
적용 대상:
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 유연한 서버는 서버의 리소스 병목 상태 및 성능 문제를 식별하는 데 사용할 수 있는 다양한 메트릭을 제공합니다. 서버의 CPU 사용률이 높은지 확인하려면 "호스트 CPU 백분율", "총 연결 수", "호스트 메모리 백분율" 및 "IO 백분율"과 같은 메트릭을 모니터링합니다. 때때로 이러한 메트릭의 조합을 확인하면 Azure Database for MySQL 유연한 서버 인스턴스에서 CPU 사용률이 증가할 수 있는 원인에 대한 인사이트를 얻을 수 있습니다.
예를 들어 CPU 사용률이 상승하는 데이터베이스 쿼리의 급증을 시작하는 연결이 급격히 증가할 수 있습니다.
메트릭을 캡처하는 것 외에도 하나 이상의 쿼리로 인해 CPU 사용률이 급증하는지 이해하기 위해 워크로드를 추적하는 것도 중요합니다.
높은 CPU 원인
CPU 급증은 주로 연결 급증과 잘못 작성된 SQL 쿼리 또는 두 쿼리의 조합으로 인해 여러 가지 이유로 발생할 수 있습니다.
연결 급증
연결이 증가하면 스레드가 증가하여 쿼리 및 리소스와 함께 이러한 연결을 관리해야 하므로 CPU 사용량이 증가할 수 있습니다. 연결 급증 문제를 해결하려면 총 커넥트 메트릭을 검사 다음 섹션에서 이러한 연결에 대한 자세한 내용을 참조해야 합니다. 다음 명령을 사용하면 Performance_schema를 활용하여 현재 서버에 연결된 호스트와 사용자를 식별할 수 있습니다.
현재 연결된 호스트
select HOST,CURRENT_CONNECTIONS From performance_schema.hosts
where CURRENT_CONNECTIONS > 0
and host not in ('NULL','localhost');
현재 연결된 사용자
select USER,CURRENT_CONNECTIONS from performance_schema.users
where CURRENT_CONNECTIONS >0
and USER not in ('NULL','azure_superuser');
잘못 작성된 SQL 쿼리
인덱스 없이 많은 수의 행을 실행하고 검색하는 데 비용이 많이 드는 쿼리 또는 다른 비효율적인 계획과 함께 임시 정렬을 수행하는 쿼리는 CPU 급증으로 이어질 수 있습니다. 일부 쿼리는 단일 세션에서 빠르게 실행될 수 있지만 여러 세션에서 실행될 때 CPU 스파이크가 발생할 수 있습니다. 따라서 항상 쇼 프로세스 목록에서 캡처한 쿼리를 설명하고 실행 계획이 효율적인지 확인하는 것이 중요합니다. 필터/where 절을 사용하여 최소 개수의 행을 스캔하고, 인덱스를 활용하며, 다른 잘못된 실행 계획과 함께 대규모 임시 정렬을 사용하지 않도록 함으로써 이를 달성할 수 있습니다. 실행 계획에 대한 자세한 내용은 EXPLAIN 출력 형식을 참조 하세요.
현재 워크로드의 세부 정보 캡처
SHOW(FULL) PROCESSLIST 명령은 현재 Azure Database for MySQL 유연한 서버 인스턴스에 연결된 모든 사용자 세션의 목록을 표시합니다. 또한 각 세션의 현재 상태 및 작업에 대한 세부 정보를 제공합니다.
이 명령은 현재 세션 상태의 스냅샷만 생성하고 기록 세션 작업에 대한 정보는 제공하지 않습니다.
이 명령을 실행한 샘플 출력을 살펴보겠습니다.
mysql> SHOW FULL PROCESSLIST;
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 13 | Waiting for next activation | NULL |
| 6 | azure_superuser | 127.0.0.1:33571 | NULL | Sleep | 115 | | NULL
|
| 24835 | adminuser | 10.1.1.4:39296 | classicmodels | Query | 7 | Sending data | select * from classicmodels.orderdetails;|
| 24837 | adminuser | 10.1.1.4:38208 | NULL | Query | 0 | starting | SHOW FULL PROCESSLIST |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
5 rows in set (0.00 sec)
동일한 IP 주소에서 고객 소유 사용자 "adminuser"가 소유한 두 개의 세션이 있습니다.
- 세션 24835는 지난 7초 동안 SELECT 문을 실행했습니다.
- 세션 24837은 "show full processlist" 문을 실행 중입니다.
필요한 경우 프로덕션 워크로드 CPU 사용량이 급증한 보고 또는 HTAP 쿼리와 같은 쿼리를 종료해야 할 수 있습니다. 그러나 CPU 사용률을 줄이기 위한 작업을 수행하기 전에 쿼리 종료의 잠재적인 결과를 항상 고려합니다. CPU 급증으로 이어지는 장기 실행 쿼리가 식별된 경우 리소스가 최적으로 활용되도록 이러한 쿼리를 조정하는 경우도 있습니다.
자세한 현재 워크로드 분석
세션, 트랜잭션 및 쿼리의 상태에 대한 정확한 정보를 얻으려면 두 개 이상의 정보 원본을 사용해야 합니다.
- SHOW [FULL] PROCESSLIST 명령을 실행하여 액세스할 수도 있는 INFORMATION_SCHEMA.PROCESSLIST 테이블의 서버 프로세스 목록입니다.
- INFORMATION_SCHEMA.INNODB_TRX 테이블의 InnoDB 트랜잭션 메타데이터입니다.
이러한 원본 중 하나의 정보만으로는 연결 및 트랜잭션 상태를 설명할 수 없습니다. 예를 들어, 프로세스 목록은 세션과 연결된 열린 트랜잭션이 있는지 여부를 알려주지 않습니다. 반면에 트랜잭션 메타데이터는 세션 상태와 해당 상태에서 보낸 시간을 표시하지 않습니다.
프로세스 목록 정보를 InnoDB 트랜잭션 메타데이터의 중요한 부분 중 일부와 결합하는 다음 예제 쿼리는 다음과 같습니다.
mysql> select p.id as session_id, p.user, p.host, p.db, p.command, p.time, p.state, substring(p.info, 1, 50) as info, t.trx_started, unix_timestamp(now()) - unix_timestamp(t.trx_started) as trx_age_seconds, t.trx_rows_modified, t.trx_isolation_level from information_schema.processlist p left join information_schema.innodb_trx t on p.id = t.trx_mysql_thread_id \G
다음 예제에서는 이 쿼리의 출력을 보여줍니다.
*************************** 1. row ***************************
session_id: 11
user: adminuser
host: 172.31.19.159:53624
db: NULL
command: Sleep
time: 636
state: cleaned up
info: NULL
trx_started: 2019-08-01 15:25:07
trx_age_seconds: 2908
trx_rows_modified: 17825792
trx_isolation_level: REPEATABLE READ
*************************** 2. row ***************************
session_id: 12
user: adminuser
host: 172.31.19.159:53622
db: NULL
command: Query
time: 15
state: executing
info: select * from classicmodels.orders
trx_started: NULL
trx_age_seconds: NULL
trx_rows_modified: NULL
trx_isolation_level: NULL
세션별로 이 정보에 대한 분석이 다음 표에 나와 있습니다.
| 영역 | 분석 |
|---|---|
| 세션 11 | 이 세션은 현재 쿼리가 실행되지 않고 유휴 상태이며 636초 동안 진행되었습니다. 세션 내에서 2908초 동안 열린 트랜잭션은 17,825,792개의 행을 수정했으며 REPEATABLE READ 격리를 사용합니다. |
| 세션 12 | 세션은 현재 15초 동안 SELECT 문을 실행 중입니다. trx_started 및 trx_age_seconds에 대한 NULL 값으로 표시된 것처럼 세션 내에서 실행 중인 쿼리가 없습니다. 세션은 더 완화된 READ COMMITTED 격리를 사용하지 않고 실행되는 한 가비지 수집 경계를 계속 유지합니다. |
세션이 유휴 상태로 보고되면 더 이상 문을 실행하지 않습니다. 이 시점에서 세션은 이전 작업을 완료하고 클라이언트의 새 문을 기다리고 있습니다. 그러나 유휴 세션은 여전히 일부 CPU 사용량 및 메모리 사용을 담당합니다.
열린 트랜잭션 나열
다음 쿼리의 출력은 트랜잭션 시작 시간 순서대로 현재 데이터베이스 서버에 대해 실행 중인 모든 트랜잭션 목록을 제공하므로 예상 런타임을 초과하는 장기 실행 및 차단 트랜잭션이 있는지 쉽게 식별할 수 있습니다.
SELECT trx_id, trx_mysql_thread_id, trx_state, Unix_timestamp() - ( To_seconds(trx_started) - To_seconds('1970-01-01 00:00:00') ) AS trx_age_seconds, trx_weight, trx_query, trx_tables_in_use, trx_tables_locked, trx_lock_structs, trx_rows_locked, trx_rows_modified, trx_isolation_level, trx_unique_checks, trx_is_read_only FROM information_schema.innodb_trx ORDER BY trx_started ASC;
스레드 상태 이해
실행 중 CPU 사용률을 높이는 트랜잭션은 다음 섹션에서 설명하는 것처럼 다양한 상태의 스레드를 가질 수 있습니다. 이 정보를 사용하여 쿼리 수명 주기 및 다양한 스레드 상태를 더 잘 이해할 수 있습니다.
권한 확인/테이블 열기
이 상태는 일반적으로 테이블 열기 작업에 오랜 시간이 소요됨을 의미합니다. 일반적으로 테이블 캐시 크기를 늘려 문제를 개선할 수 있습니다. 그러나 테이블이 느리게 열리는 것은 다른 문제를 나타낼 수도 있습니다. 예를 들어 동일한 데이터베이스에 너무 많은 테이블이 있는 경우입니다.
데이터 보내기
이 상태는 스레드가 네트워크를 통해 데이터를 보내고 있음을 의미할 수 있지만 쿼리가 디스크나 메모리에서 데이터를 읽고 있음을 나타낼 수도 있습니다. 이 상태는 순차 테이블 검사로 인해 발생할 수 있습니다. innodb_buffer_pool_reads 및 innodb_buffer_pool_read_requests 값을 확인하여 많은 수의 페이지가 디스크에서 메모리로 제공되고 있는지 여부를 확인해야 합니다. 자세한 내용은 Azure Database for MySQL 유연한 서버의 메모리 부족 문제 해결을 참조 하세요.
업데이트
이 상태는 일반적으로 스레드가 쓰기 작업을 수행하고 있음을 의미합니다. 성능 모니터 IO 관련 메트릭을 확인하면 현재 세션이 수행하는 작업을 더 잘 이해할 수 있습니다.
<lock_type> 잠금 대기 중
이 상태는 스레드가 두 번째 잠금을 기다리고 있음을 나타냅니다. 대부분의 경우 메타데이터 잠금일 수 있습니다. 다른 모든 스레드를 검토하고 잠금을 수행하는 사용자를 확인해야 합니다.
대기 이벤트 이해 및 분석
데이터베이스의 대기 수가 많거나 대기 시간이 길면 CPU 사용률이 증가할 수 있으므로 MySQL 엔진의 기본 대기 이벤트를 이해하는 것이 중요합니다. 다음 예제에서는 적절한 명령 및 샘플 출력을 보여줍니다.
SELECT event_name AS wait_event,
count_star AS all_occurrences,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_wait_time,
Concat(Round(avg_timer_wait / 1000000000, 2), ' ms') AS
avg_wait_time
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE count_star > 0 AND event_name <> 'idle'
ORDER BY sum_timer_wait DESC LIMIT 10;
+--------------------------------------+-----------------+-----------------+---------------+
| wait_event | all_occurrences | total_wait_time | avg_wait_time |
+--------------------------------------+-----------------+-----------------+---------------+
| wait/io/file/sql/binlog | 7090 | 255.54 s | 36.04 ms |
| wait/io/file/innodb/innodb_log_file | 17798 | 55.43 s | 3.11 ms |
| wait/io/file/innodb/innodb_data_file | 260227 | 39.67 s | 0.15 ms |
| wait/io/table/sql/handler | 5548985 | 11.73 s | 0.00 ms |
| wait/io/file/sql/FRM | 1237 | 7.61 s | 6.15 ms |
| wait/io/file/sql/dbopt | 28 | 1.89 s | 67.38 ms |
| wait/io/file/myisam/kfile | 92 | 0.76 s | 8.30 ms |
| wait/io/file/myisam/dfile | 271 | 0.53 s | 1.95 ms |
| wait/io/file/sql/file_parser | 18 | 0.32 s | 17.75 ms |
| wait/io/file/sql/slow_log | 2 | 0.05 s | 25.79 ms |
+--------------------------------------+-----------------+-----------------+---------------+
10 rows in set (0.00 sec)
SELECT 문 실행 시간 제한
SELECT 쿼리와 관련된 데이터베이스 작업에 대한 실행 비용 및 실행 시간을 모르는 경우 장기 실행 SELECT는 데이터베이스 서버의 예측 불가능성 또는 변동성으로 이어질 수 있습니다. 문 및 트랜잭션의 크기와 관련 리소스 사용률은 기본 데이터 세트 증가에 따라 계속 증가합니다. 이러한 무제한 증가로 인해 최종 사용자 문과 트랜잭션은 더 오래 걸리며 데이터베이스 서버에 과부하가 걸릴 때까지 점점 더 많은 리소스를 소비합니다. 무제한 SELECT 쿼리를 사용하는 경우 이 기간을 초과하는 모든 쿼리가 중단되도록 max_execution_time 매개 변수를 구성하는 것이 좋습니다.
권장 사항
- 데이터베이스에 쿼리를 실행하기에 충분한 리소스가 할당되어 있는지 확인합니다. 경우에 따라 워크로드를 수용하기 위해 더 많은 CPU 코어를 얻기 위해 인스턴스 크기를 확장해야 할 수 있습니다.
- 여러 개의 작은 트랜잭션으로 분할하여 큰 트랜잭션 또는 장기 실행 트랜잭션을 방지하세요.
- 가능한 경우 읽기 복제본 서버에서 SELECT 문을 실행합니다.
- 시스템이 지정된 임계값을 초과하는 경우 알림을 받을 수 있도록 "호스트 CPU 백분율"에 대한 경고를 사용합니다.
- Query Performance Insights 또는 Azure Workbooks를 사용하여 문제가 있거나 느리게 실행되는 쿼리를 식별한 다음, 최적화합니다.
- 프로덕션 데이터베이스 서버의 경우 모든 것이 원활하게 실행되고 있는지 확인하기 위해 정기적으로 진단을 수집합니다. 그렇지 않은 경우 식별한 문제를 해결합니다.
다음 단계
가장 중요한 질문에 대한 동료 대답을 찾거나 질문을 게시하거나 대답을 얻기 위해 Stack Overflow를 방문합니다.