인스턴스의 높은 CPU 사용률 최적화

인스턴스의 높은 CPU 사용률은 워크로드 증가, 많은 트랜잭션, 느린 쿼리, 장기 실행 트랜잭션과 같은 다양한 이유 때문일 수 있습니다.

과소 프로비저닝된 인스턴스 추천자가 CPU 사용률을 분석합니다. 지난 30일간 상당 시간 동안 CPU 사용률 수준이 95% 이상이면 추천자가 알림을 보내고 문제를 해결하는 데 도움이 되는 추가 통계를 제공합니다.

이 문서에서는 과소 프로비저닝된 인스턴스 추천자가 MySQL용 Cloud SQL 인스턴스의 CPU 사용률이 높은 것으로 식별한 경우 해당 인스턴스를 검토하고 최적화하는 방법을 설명합니다.

권장사항

CPU 사용률은 워크로드에 비례하여 증가합니다. CPU 사용률을 줄이려면 실행 중인 쿼리를 확인하고 최적화합니다. 다음은 CPU 사용률을 확인하는 몇 가지 단계입니다.

Threads_running 및 Threads_connected 확인

다음 쿼리를 사용하여 활성 스레드 수를 확인합니다.
```
> SHOW STATUS like 'Threads_%';
```
Threads_running은 Threads_connected의 하위 집합입니다. 나머지 스레드는 유휴 상태입니다. Threads_running이 증가하면 CPU 사용률이 늘어납니다. 이 스레드에서 실행 중인 항목을 확인하는 것이 좋습니다.
쿼리 상태 확인

SHOW PROCESSLIST 명령어를 실행하여 진행 중인 쿼리를 확인합니다. 연결된 모든 스레드를 순서대로 반환하며 현재 실행 중인 SQL 문도 반환합니다.
```
mysql> SHOW [FULL] PROCESSLIST;
```
상태 및 기간 열에 주의하세요. 동일한 상태에서 중단된 쿼리가 많은지 확인합니다.
- 많은 스레드에 Updating이 표시되면 레코드 잠금 경합이 있을 수 있습니다. 다음 단계를 참조하세요.
- 여러 스레드에서 테이블 메타데이터 잠금에 대해 Waiting이 표시되면 쿼리를 확인하여 테이블을 파악한 후 메타데이터 잠금을 유지할 수 있는 DDL(예: ALTER TABLE)을 찾습니다. 또한 장기 실행 SELECT query와 같은 초기 쿼리가 이를 유지하는 경우 DDL에서 테이블 메타데이터 잠금을 기다릴 수도 있습니다.
레코드 잠금 경합 확인

트랜잭션이 널리 사용되는 색인 레코드 잠금을 유지하는 경우 동일한 잠금을 요청하는 다른 트랜잭션을 차단합니다. 이로 인해 연쇄 효과가 발생하여 여러 요청이 중단되고 Threads_running 값이 증가할 수 있습니다. 잠금 경합을 진단하려면 information_schema.innodb_lock_waits 테이블을 사용합니다.

다음 쿼리는 각 차단 트랜잭션과 연결된 차단 트랜잭션 수를 나열합니다.
```
SELECT 
  t.trx_id, 
  t.trx_state, 
  t.trx_started, 
  COUNT(distinct w.requesting_trx_id) AS blocked_trxs
FROM 
  information_schema.innodb_lock_waits w 
INNER JOIN information_schema.innodb_trx t
   ON t.trx_id = w.blocking_trx_id 
GROUP BY t.trx_id,t.trx_state, t.trx_started
ORDER BY t.trx_id;
```
큰 단일 DML과 여러 개의 작은 동시 DML 모두 행 잠금 경합이 발생할 수 있습니다. 다음 단계를 사용하면 애플리케이션 측에서 이를 최적화할 수 있습니다.
- 행 잠금은 트랜잭션이 종료될 때까지 유지되므로 긴 트랜잭션을 사용하지 마세요.
- 하나의 큰 DML을 크기가 작은 DML로 나눕니다.
- 하나의 행 DML을 작은 청크로 일괄 처리합니다.
- 스레드 간 경합을 최소화합니다. 예를 들어 애플리케이션 코드가 연결 풀을 사용하는 경우 동일한 스레드에 ID 범위를 할당합니다.
장기 실행 트랜잭션 찾기
- SHOW ENGINE INNODB STATUS 사용
  
  TRANSACTIONS 섹션에서 모든 열린 트랜잭션을 최신순으로 확인할 수 있습니다.
```
mysql> SHOW ENGINE INNODB STATUS\G
……
------------
TRANSACTIONS
------------
…
---TRANSACTION 245762, ACTIVE 262 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 9210, OS thread handle 140262286128896, query id 202218 localhost root
```
  가장 오래된 트랜잭션부터 시작하여 다음 질문에 대한 답을 찾습니다.
  - 트랜잭션이 얼마나 오랫동안 실행되었나요?
  - 잠금 구조체와 행 잠금이 몇 개 있나요?
  - 실행취소 로그 항목은 몇 개인가요?
  - 연결 호스트 및 사용자는 무엇인가요?
  - 진행 중인 SQL 문은 무엇인가요?
- information_schema.innodb_trx 사용
  
  SHOW ENGINE INNODB STATUS가 잘린 경우 모든 열린 트랜잭션을 검사하는 또 다른 방법은 information_schema.innodb_trx 테이블을 사용하는 것입니다.
```
SELECT 
 trx_id, trx_state, 
 timestampdiff(second, trx_started, now()) AS active_secs, 
 timestampdiff(second, trx_wait_started, now()) AS wait_secs, trx_tables_in_use,
 trx_tables_locked, 
 trx_lock_structs, 
 trx_rows_locked, 
 trx_rows_modified, 
 trx_query 
FROM information_schema.innodb_trx
```
트랜잭션에 현재 장기 실행 중인 문이 표시되면 이러한 트랜잭션을 중지하여 서버의 부담을 줄이거나 중요한 트랜잭션이 완료될 때까지 기다리도록 결정할 수 있습니다. 이전 트랜잭션에 활동이 표시되지 않으면 다음 단계로 이동하여 트랜잭션 기록을 찾습니다.
장기 실행 트랜잭션의 SQL 문 확인
- performance_schema 사용
  
  performance_schema를 사용하려면 먼저 사용 설정해야 합니다. 이는 인스턴스를 다시 시작해야 하는 변경사항입니다. performance_schema를 사용 설정한 후 계측 및 소비자가 사용 설정되었는지 확인합니다.
```
SELECT * FROM setup_consumers where name like 'events_statements_history';
SELECT * FROM setup_instruments where name like 'statement/sql/%';
```
  사용 설정되어 있지 않으면 사용 설정합니다.
```
UPDATE setup_instruments SET ENABLED = 'YES', timed = 'YES' WHERE NAME LIKE 'statement/%';
UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_statements%';
```
  기본적으로 각 스레드는 performance_schema_events_statements_history_size에서 정의된 마지막 10개의 이벤트를 유지합니다. 일반적으로 이 정도면 애플리케이션 코드에서 트랜잭션을 찾는 데 충분합니다. 이 매개변수는 동적 매개변수가 아닙니다.
  
  processlist_id인 mysql thread id를 사용하여 기록 이벤트를 쿼리합니다.
```
SELECT 
 t.thread_id, 
 event_name, 
 sql_text, 
 rows_affected, 
 rows_examined, 
 processlist_id, 
 processlist_time, 
 processlist_state 
FROM events_statements_history h 
INNER JOIN threads t 
ON h.thread_id = t.thread_id 
WHERE processlist_id = <mysql thread id>
ORDER BY event_id;
```
- 느린 쿼리 로그 사용
  
  디버깅의 경우 N초를 초과한 모든 쿼리를 느린 쿼리 로그에 캡처할 수 있습니다. Google Cloud 콘솔의 인스턴스 페이지 또는 gcloud CLI에서 인스턴스 설정을 수정하여 느린 쿼리 로그를 사용 설정한 후에 Google Cloud 콘솔의 로그 뷰어 또는 gloud CLI를 사용하여 로그를 볼 수 있습니다.
세마포어 경합 확인

동시 환경에서는 공유 리소스의 뮤텍스 및 읽기/쓰기 래치가 경합 지점일 수 있으며, 이로 인해 서버 성능이 저하됩니다. 또한 세마포어 대기 시간이 600초를 초과하면 시스템이 중단에서 벗어나기 위해 비정상 종료될 수 있습니다.

세마포어 경합을 보려면 다음 명령어를 사용합니다.
```
mysql> SHOW ENGINE INNODB STATUS\G
----------
SEMAPHORES
----------
...
  --Thread 140396021667584 has waited at row0purge.cc line 862 for 241.00 seconds the semaphore:
  S-lock on RW-latch at 0x30c03e8 created in file dict0dict.cc line 1183
  a writer (thread id 140395996489472) has reserved it in mode  exclusive
  number of readers 0, waiters flag 1, lock_word: 0
  Last time read locked in file row0purge.cc line 862
  Last time write locked in file /build/mysql-5.7-FFKPr6/mysql-5.7-5.7.22/storage/innobase/dict/dict0stats.cc line 2376
...
```
각 세마포어 대기에서 첫 번째 줄은 대기 중인 스레드, 특정 세마포어, 대기한 시간을 표시합니다. SHOW ENGINE INNODB STATUS를 반복적으로 실행할 때 특히 몇 초 이상 대기하는 세마포어 대기가 잦다면 시스템에 동시 실행 병목 현상이 발생했다는 의미입니다.

워크로드 및 구성에 따라 경합 지점이 다릅니다.

세마포어가 btr0sea.c에 있는 경우가 많으면 적응형 해시 색인 생성이 경합 소스가 될 수 있습니다. Google Cloud 콘솔 또는 gcloud CLI를 사용하여 이를 중지해 보세요.
긴 SELECT 쿼리 최적화

먼저 쿼리를 검토합니다. 쿼리의 목표와 결과를 얻는 가장 좋은 방법을 파악합니다. 가장 좋은 쿼리 계획은 데이터 액세스를 최소화하는 것입니다.
- 쿼리 실행 계획을 확인합니다.
```
mysql> EXPLAIN <the query>;
```
출력을 해석하고 쿼리 효율성을 평가하는 방법은 MySQL 문서를 참조하세요.
- 올바른 색인 사용
키 열에서 예상 색인이 사용되었는지 확인합니다. 사용되지 않았다면 색인 통계를 업데이트합니다.
```
mysql> analyze table <table_name> 
```
색인 통계를 계산하는 데 사용되는 샘플 페이지 수를 늘립니다. 자세한 내용은 Microsoft 문서를 참조하세요.
- 색인 최대한 활용
여러 열 색인을 사용할 때 key_len 열을 확인하여 레코드를 필터링하는 데 색인이 모두 활용되는지 검토합니다. 가장 왼쪽 열은 같음 비교여야 하며 색인은 첫 번째 범위 조건까지만 사용할 수 있습니다.
- 최적화 도구 힌트 사용
올바른 색인을 보장하는 또 다른 방법은 색인 힌트 및 테이블 조인 순서 힌트를 사용하는 것입니다.
READ COMMITTED를 사용한 긴 기록 목록 방지

기록 목록은 실행취소 테이블스페이스에서 삭제되지 않은 트랜잭션 목록입니다. 트랜잭션의 기본 격리 수준은 해당 기간 동안 트랜잭션이 동일한 스냅샷을 읽어야 하는 REPEATABLE READ입니다. 따라서 SELECT 쿼리는 쿼리(또는 트랜잭션)가 시작된 후 작성된 실행취소 로그 레코드의 삭제를 차단합니다. 그래서 기록 목록이 길면 쿼리 성능이 저하됩니다. 긴 기록 목록의 작성을 막는 한 가지 방법은 트랜잭션 격리 수준을 READ COMMITTED로 변경하는 것입니다. READ COMMITTED를 사용하면 일관된 읽기 뷰를 위해 기록 목록을 유지할 필요가 없습니다. 트랜잭션 격리 수준을 모든 세션, 단일 세션 또는 다음 단일 트랜잭션에 대해 전역적으로 변경할 수 있습니다. 자세한 내용은 Microsoft 문서를 참조하세요.
서버 구성 조정

서버 구성은 설명해야 할 내용이 많습니다. 이 문서에서는 그 모든 내용을 다루지 않지만, 서버도 관련 구성이 얼마나 적절한지에 대한 힌트를 제공하는 다양한 상태 변수를 보고한다는 점을 짚고 넘어갑니다. 예를 들면 다음과 같습니다.
- Threads_created/Connections가 크면 thread_cache_size를 조정합니다. 적절한 스레드 캐시는 스레드 생성 시간을 줄이고 동시성이 높은 워크로드에 도움을 줍니다.
- Table_open_cache_misses/Table_open_cache_hits이 작지 않으면 table_open_cache를 조정합니다. 테이블 캐시에 테이블이 있으면 쿼리 실행 시간이 절약되고 동시성이 높은 환경에 변화를 가져올 수 있습니다.
원치 않는 연결 종료

쿼리가 잘못되었거나 더 이상 필요하지 않은 경우 쿼리를 중지할 수 있습니다. MySQL 스레드를 식별하고 종료하는 방법을 알아보려면 데이터베이스 연결 관리를 참조하세요.

마지막으로 CPU 사용량이 여전히 높고 쿼리가 필요한 트래픽을 형성하는 경우에는 인스턴스의 CPU 리소스를 늘려 데이터베이스 비정상 종료나 다운타임을 방지하세요.