Aurora MySQL 데이터베이스의 워크로드 문제 해결
데이터베이스 워크로드는 읽기 및 쓰기로 볼 수 있습니다. '일반적인' 데이터베이스 워크로드를 이해하면 변화하는 수요에 맞춰 쿼리와 데이터베이스 서버를 튜닝할 수 있습니다. 성능이 변경될 수 있는 이유는 다양하므로 먼저 무엇이 변경되었는지 파악해야 합니다.
-
메이저 버전 또는 마이너 버전 업그레이드가 있었나요?
메이저 버전 업그레이드에는 쿼리 실행 계획을 변경할 수 있는 엔진 코드, 특히 최적화 프로그램의 변경 사항이 포함됩니다. 데이터베이스 버전, 특히 메이저 버전을 업그레이드할 때는 데이터베이스 워크로드를 분석하고 그에 따라 튜닝하는 것이 매우 중요합니다. 튜닝에는 테스트 결과에 따라 쿼리를 최적화 및 재작성하거나 파라미터 설정을 추가 및 업데이트하는 작업이 포함될 수 있습니다. 영향을 미치는 원인을 이해하면 해당 영역에 집중할 수 있습니다.
자세한 내용은 MySQL 설명서의 What is new in MySQL 8.0
과 Server and status variables and options added, deprecated, or removed in MySQL 8.0 및 Aurora MySQL 버전 2와 Aurora MySQL 버전 3의 비교 섹션을 참조하세요. -
처리 중인 데이터(행 수)가 증가했나요?
-
동시에 실행되는 쿼리가 더 많나요?
-
스키마 또는 데이터베이스 변경 사항이 있나요?
-
코드 결함이나 수정이 있었나요?
목차
인스턴스 호스트 지표
CPU, 메모리, 네트워크 활동과 같은 인스턴스 호스트 지표를 모니터링하면 워크로드 변경 여부를 이해하는 데 도움이 됩니다. 워크로드 변화를 이해하는 데는 두 가지 주요 개념이 있습니다.
-
사용률 - CPU 또는 디스크와 같은 디바이스의 사용량. 시간 기반 또는 용량 기반일 수 있습니다.
-
시간 기반 - 특정 관찰 기간 동안 리소스가 많이 사용된 시간
-
용량 기반 - 시스템 또는 구성 요소가 제공할 수 있는 처리량(용량의 백분율)
-
-
포화도 - 리소스에 처리할 수 있는 것보다 더 많은 작업이 필요한 정도. 용량 기준 사용량이 100%에 도달하면 추가 작업을 처리할 수 없으므로 대기열에 추가되어야 합니다.
CPU 사용량
다음 도구를 사용하여 CPU 사용량 및 포화도를 파악할 수 있습니다.
-
CloudWatch는
CPUUtilization지표를 제공합니다. 이 수치가 100%에 도달하면 인스턴스가 포화 상태입니다. 하지만 CloudWatch 지표는 1분 단위로 평균을 낸 값이며 세분성이 부족합니다.CloudWatch 지표에 대한 자세한 내용은 Amazon Aurora에 대한 인스턴스 수준 지표 섹션을 참조하세요.
-
향상된 모니터링은 운영 체제
top명령으로 반환된 지표를 제공합니다. 로드 평균과 다음 CPU 상태를 1초 단위로 보여줍니다.-
Idle (%)= 유휴 시간 -
IRQ (%)= 소프트웨어 중단 -
Nice (%)= Niced우선순위가 있는 프로세스의 Nice 시간 -
Steal (%)= 다른 테넌트에게 서비스를 제공하는 데 소요된 시간(가상화 관련) -
System (%)= 시스템 시간 -
User (%)= 사용자 시간 -
Wait (%)= I/O 대기
향상된 모니터링 지표에 대한 자세한 내용은 Aurora의 지표 섹션을 참조하세요.
-
메모리 사용량
시스템이 메모리 부족 상태이고 리소스 사용량이 포화 상태에 이르면 페이지 스캔, 페이징, 교체 및 메모리 부족 오류가 많이 발생합니다.
다음 도구를 사용하여 메모리 사용량 및 포화도를 파악할 수 있습니다.
CloudWatch는 일부 OS 캐시와 현재 사용 가능한 메모리를 비워서 회수할 수 있는 메모리 양을 보여주는 FreeableMemory 지표를 제공합니다.
CloudWatch 지표에 대한 자세한 내용은 Amazon Aurora에 대한 인스턴스 수준 지표 섹션을 참조하세요.
향상된 모니터링은 메모리 사용 문제를 식별하는 데 도움이 되는 다음과 같은 지표를 제공합니다.
-
Buffers (KB)- 스토리지 디바이스에 쓰기 전에 I/O 요청을 버퍼링하는 데 사용되는 메모리의 양(KB) -
Cached (KB)- 파일 시스템 기반 I/O를 캐시하는 데 사용된 메모리의 양 -
Free (KB)- 할당되지 않은 메모리의 양(KB) -
Swap- 캐시됨, 사용할 수 있음, 합계
예를 들어 DB 인스턴스에서 Swap 메모리를 사용하는 경우 워크로드의 총 메모리 용량은 현재 인스턴스에서 사용할 수 있는 양보다 큽니다. DB 인스턴스의 크기를 늘리거나 메모리 사용량을 줄이도록 워크로드를 튜닝하는 것이 좋습니다.
향상된 모니터링 지표에 대한 자세한 내용은 Aurora의 지표 섹션을 참조하세요.
성능 스키마 및 sys 스키마를 사용하여 메모리를 사용하는 연결 및 구성 요소를 확인하는 방법에 대한 자세한 내용은 Aurora MySQL 데이터베이스의 메모리 사용량 문제 해결 섹션을 참조하세요.
네트워크 처리량
CloudWatch는 총 네트워크 처리량에 대해 다음과 같은 지표를 제공하며, 모두 1분 단위로 평균을 낸 값입니다.
-
NetworkReceiveThroughput- Aurora DB 클러스터의 각 인스턴스가 클라이언트에서 수신하는 네트워크 처리량 -
NetworkTransmitThroughput- Aurora DB 클러스터의 각 인스턴스가 클라이언트로 전송하는 네트워크 처리량 -
NetworkThroughput- Aurora DB 클러스터의 각 인스턴스가 클라이언트에서 수신하고 클라이언트로 전송하는 네트워크 처리량 -
StorageNetworkReceiveThroughput- DB 클러스터의 각 인스턴스가 Aurora 스토리지 하위 시스템에서 수신하는 네트워크 처리량 -
StorageNetworkTransmitThroughput- Aurora DB 클러스터의 각 인스턴스가 Aurora 스토리지 하위 시스템으로 전송하는 네트워크 처리량 -
StorageNetworkThroughput- Aurora DB 클러스터의 각 인스턴스가 Aurora 스토리지 하위 시스템과 송수신하는 네트워크 처리량
CloudWatch 지표에 대한 자세한 내용은 Amazon Aurora에 대한 인스턴스 수준 지표 섹션을 참조하세요.
향상된 모니터링은 network 수신(RX) 및 전송(TX) 그래프를 최대 1초의 세부 단위로 제공합니다.
향상된 모니터링 지표에 대한 자세한 내용은 Aurora의 지표 섹션을 참조하세요.
데이터베이스 지표
워크로드 변화에 대한 다음 CloudWatch 지표를 살펴보세요.
-
BlockedTransactions- 데이터베이스에서 1초마다 차단되는 평균 트랜잭션 수 -
BufferCacheHitRatio- 버퍼 캐시에서 처리하는 요청 비율 -
CommitThroughput- 초당 커밋 작업의 평균 수 -
DatabaseConnections- 데이터베이스 인스턴스에 대한 클라이언트 네트워크 연결 수 -
Deadlocks- 데이터베이스 1초마다 발생하는 평균 교착 수 -
DMLThroughput- 초당 평균 삽입, 업데이트 및 삭제 수 -
ResultSetCacheHitRatio- 쿼리 캐시에서 처리하는 요청 비율 -
RollbackSegmentHistoryListLength- 삭제 표시 레코드로 커밋된 트랜잭션을 기록하는 실행 취소 로그 -
RowLockTime- InnoDB 테이블에 대한 행 잠금을 획득하는 데 걸린 총 시간 -
SelectThroughput- 초당 평균 선택 쿼리 수
CloudWatch 지표에 대한 자세한 내용은 Amazon Aurora에 대한 인스턴스 수준 지표 섹션을 참조하세요.
워크로드를 검토할 때는 다음 질문을 고려해 보세요.
-
인스턴스 크기를 8xlarge에서 4xlarge로 줄이거나 db.r5에서 db.r6으로 변경하는 등 최근에 DB 인스턴스 클래스를 변경했나요?
-
클론을 생성하여 문제를 재현할 수 있나요? 아니면 해당 인스턴스에서만 발생하나요?
-
서버 리소스 고갈, 높은 CPU 또는 메모리 고갈이 있나요? 그렇다면 추가 하드웨어가 필요할 수 있습니다.
-
하나 이상의 쿼리가 더 오래 걸리나요?
-
변경 사항이 업그레이드, 특히 메이저 버전 업그레이드로 인해 발생했나요? 그렇다면 업그레이드 전후 지표를 비교하세요.
-
리더 DB 인스턴스 수에 변화가 있나요?
-
일반, 감사 또는 바이너리 로깅을 활성화했나요? 자세한 내용은 Aurora MySQL 데이터베이스에 대한 로깅 단원을 참조하십시오.
-
바이너리 로그(binlog) 복제 사용을 활성화, 비활성화 또는 변경나요?
-
다수의 행 잠금이 있는 장기 실행 트랜잭션이 있나요? InnoDB 기록 목록 길이(HLL)에서 장기 실행 트랜잭션의 징후를 확인하세요.
자세한 내용은 InnoDB 기록 목록 길이가 크게 늘어남 및 Amazon Aurora MySQL DB 클러스터에서 SELECT 쿼리가 느리게 실행되는 이유는 무엇인가요?
블로그 게시물을 참조하세요. -
쓰기 트랜잭션으로 인해 큰 HLL이 발생한 경우
UNDO로그가 누적되고 있다는 의미입니다(정기적으로 정리되지 않음). 대규모 쓰기 트랜잭션의 경우 이 누적이 빠르게 증가할 수 있습니다. MySQL에서는UNDO가 SYSTEM 테이블스페이스에 저장됩니다. SYSTEM테이블스페이스는 축소할 수 없습니다.UNDO로그로 인해SYSTEM테이블스페이스가 몇 GB, 심지어는 몇 TB까지 증가할 수 있습니다. 삭제 후에는 데이터를 논리적으로 백업(덤프)하여 할당된 공간을 해제한 다음 덤프를 새 DB 인스턴스로 가져오세요. -
읽기 트랜잭션(장기 실행 쿼리)으로 인해 큰 HLL이 발생하는 경우 쿼리가 많은 양의 임시 공간을 사용하고 있다는 의미일 수 있습니다. 재부팅하여 임시 공간을 해제하세요. 성능 개선 도우미 DB 지표를 검토하여
Temp섹션의 변경 사항(예:created_tmp_tables)이 있는지 확인하세요. 자세한 내용은 성능 개선 도우미를 통한 Amazon Aurora 모니터링 단원을 참조하십시오.
-
-
장기 실행 트랜잭션을 더 적은 행을 수정하는 더 작은 트랜잭션으로 분할할 수 있나요?
-
차단된 트랜잭션에 변경 사항이 있거나 교착 상태가 증가했나요? 성능 개선 도우미 DB 지표를 검토하여
Locks섹션의 상태 변수 변경 사항(예:innodb_row_lock_time,innodb_row_lock_waits,innodb_dead_locks)이 있는지 확인하세요. 1분 또는 5분의 간격을 사용하세요. -
대기 이벤트가 늘어났나요? 1분 또는 5분 간격을 사용하여 성능 개선 도우미의 대기 이벤트와 대기 유형을 검사하세요. 상위 대기 이벤트를 분석하고 이러한 이벤트가 워크로드 변경 또는 데이터베이스 경합과 상관관계가 있는지 확인하세요. 예를 들어,
buf_pool mutex는 버퍼 풀 경합을 나타냅니다. 자세한 내용은 대기 이벤트로 Aurora MySQL 튜닝 단원을 참조하십시오.
Aurora MySQL 데이터베이스의 메모리 사용량 문제 해결
CloudWatch, 향상된 모니터링 및 성능 개선 도우미는 운영 체제 수준에서 데이터베이스 프로세스에서 사용하는 메모리 양 등의 메모리 사용량에 대한 유용한 개요를 제공하지만, 이러한 메모리 사용량을 유발할 수 있는 엔진 내 연결 또는 구성 요소를 분석할 수는 없습니다.
이 문제를 해결하기 위해 성능 스키마와 sys 스키마를 사용할 수 있습니다. Aurora MySQL 버전 3에서 메모리 계측은 성능 스키마가 사용 설정되면 기본적으로 활성화됩니다. Aurora MySQL 버전 2에서는 성능 스키마 메모리 사용량의 메모리 계측만 기본적으로 사용 설정됩니다. 성능 스키마에서 메모리 사용량을 추적하고 성능 스키마 메모리 계측을 사용 설정하는 데 사용할 수 있는 테이블에 대한 자세한 내용은 MySQL 설명서의 메모리 요약 테이블
성능 스키마에서 현재 메모리 사용량을 추적할 수 있는 자세한 정보가 나와 있지만, MySQL sys 스키마
sys 스키마에서 다음 뷰를 사용하여 연결, 구성 요소 및 쿼리별로 메모리 사용량을 추적할 수 있습니다.
| 보기 | 설명 |
|---|---|
|
호스트별 엔진 메모리 사용량에 대한 정보를 제공합니다. 이 정보를 통해 메모리를 사용하는 애플리케이션 서버 또는 클라이언트 호스트를 식별할 수 있습니다. |
|
|
스레드 ID별 엔진 메모리 사용량에 대한 정보를 제공합니다. MySQL의 스레드 ID는 클라이언트 연결 또는 백그라운드 스레드일 수 있습니다. sys.processlist |
|
|
사용자별 엔진 메모리 사용량에 대한 정보를 제공합니다. 이 정보를 통해 메모리를 사용하는 사용자 계정 또는 클라이언트를 식별할 수 있습니다. |
|
|
엔진 구성 요소별 엔진 메모리 사용량에 대한 정보를 제공합니다. 이 정보를 통해 엔진 버퍼 또는 구성 요소별 메모리 사용량을 전체적으로 식별할 수 있습니다. 예를 들어 InnoDB 버퍼 풀의 |
|
|
데이터베이스 엔진에서 추적된 총 메모리 사용량에 대한 개요를 제공합니다. |
Aurora MySQL 버전 3.05 이상에서는 성능 스키마 문 요약 테이블MAX_TOTAL_MEMORY 열을 통해 통계가 마지막으로 재설정된 이후 또는 데이터베이스 인스턴스가 재시작된 이후 쿼리 다이제스트에서 사용된 최대 메모리를 식별할 수 있습니다. 이를 통해 메모리를 많이 사용할 수 있는 특정 쿼리를 식별할 수 있습니다.
참고
성능 스키마와 sys 스키마는 서버의 현재 메모리 사용량과 연결 및 엔진 구성 요소당 사용된 메모리의 상한선을 보여줍니다. 성능 스키마는 메모리에 유지 관리되므로 DB 인스턴스가 다시 시작될 때 정보가 재설정됩니다. 시간이 지나도 기록을 계속해서 유지하려면 성능 스키마 외부에서 이 데이터를 검색 및 저장하도록 구성하는 것이 좋습니다.
예제 1: 지속적인 높은 메모리 사용량
전반적으로 CloudWatch에서 FreeableMemory를 살펴보면 2024-03-26 02:59 UTC를 기준으로 메모리 사용량이 크게 증가한 것을 확인할 수 있습니다.
이것이 전부는 아닙니다. 어떤 구성 요소가 메모리를 가장 많이 사용하고 있는지 확인하려면 데이터베이스에 로그인하여 sys.memory_global_by_current_bytes를 살펴보시기 바랍니다. 이 표에는 MySQL이 추적하는 메모리 이벤트 목록과 이벤트별 메모리 할당 정보가 포함되어 있습니다. 각 메모리 추적 이벤트는 memory/%로 시작되며 이벤트와 관련된 엔진 구성 요소/특성에 대한 기타 정보가 뒤따릅니다.
예를 들어 memory/performance_schema/%는 성능 스키마와 관련된 메모리 이벤트용이고 memory/innodb/%는 InnoDB용 등입니다. 이벤트 이름 지정 규칙에 대한 자세한 내용은 MySQL 설명서의 성능 스키마 계측 이름 지정 규칙
다음 쿼리에서는 current_alloc을 기반으로 유력한 원인을 찾을 수 있지만 많은 memory/performance_schema/% 이벤트도 확인할 수 있습니다.
mysql> SELECT * FROM sys.memory_global_by_current_bytes LIMIT 10; +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | memory/sql/Prepared_statement::main_mem_root | 512817 | 4.91 GiB | 10.04 KiB | 512823 | 4.91 GiB | 10.04 KiB | | memory/performance_schema/prepared_statements_instances | 252 | 488.25 MiB | 1.94 MiB | 252 | 488.25 MiB | 1.94 MiB | | memory/innodb/hash0hash | 4 | 79.07 MiB | 19.77 MiB | 4 | 79.07 MiB | 19.77 MiB | | memory/performance_schema/events_errors_summary_by_thread_by_error | 1028 | 52.27 MiB | 52.06 KiB | 1028 | 52.27 MiB | 52.06 KiB | | memory/performance_schema/events_statements_summary_by_thread_by_event_name | 4 | 47.25 MiB | 11.81 MiB | 4 | 47.25 MiB | 11.81 MiB | | memory/performance_schema/events_statements_summary_by_digest | 1 | 40.28 MiB | 40.28 MiB | 1 | 40.28 MiB | 40.28 MiB | | memory/performance_schema/memory_summary_by_thread_by_event_name | 4 | 31.64 MiB | 7.91 MiB | 4 | 31.64 MiB | 7.91 MiB | | memory/innodb/memory | 15227 | 27.44 MiB | 1.85 KiB | 20619 | 33.33 MiB | 1.66 KiB | | memory/sql/String::value | 74411 | 21.85 MiB | 307 bytes | 76867 | 25.54 MiB | 348 bytes | | memory/sql/TABLE | 8381 | 21.03 MiB | 2.57 KiB | 8381 | 21.03 MiB | 2.57 KiB | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ 10 rows in set (0.02 sec)
앞서 설명했듯이 성능 스키마는 메모리에 저장됩니다. 즉, performance_schema 메모리 계측에서 추적할 수도 있습니다.
참고
성능 스키마가 메모리를 많이 사용하고 있고 메모리 사용량을 제한하려는 경우 요구 사항에 따라 데이터베이스 파라미터를 조정할 수 있습니다. 자세한 내용은 MySQL 설명서의 성능 스키마 메모리 할당 모델
가독성을 위해 동일한 쿼리를 다시 실행하되 성능 스키마 이벤트는 제외할 수 있습니다. 출력은 다음과 같이 표시됩니다.
-
memory/sql/Prepared_statement::main_mem_root에서 주로 메모리가 사용됩니다. -
current_alloc열에는 MySQL이 현재 이 이벤트에 4.91GiB를 할당하고 있음이 나와 있습니다. -
high_alloc column에는 통계가 마지막으로 재설정된 이후 또는 서버가 다시 시작된 이후 4.91GiB가 최고current_alloc수치임이 나와 있습니다. 즉,memory/sql/Prepared_statement::main_mem_root가 현재 해당 최대치에 있다는 의미입니다.
mysql> SELECT * FROM sys.memory_global_by_current_bytes WHERE event_name NOT LIKE 'memory/performance_schema/%' LIMIT 10; +-----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +-----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | memory/sql/Prepared_statement::main_mem_root | 512817 | 4.91 GiB | 10.04 KiB | 512823 | 4.91 GiB | 10.04 KiB | | memory/innodb/hash0hash | 4 | 79.07 MiB | 19.77 MiB | 4 | 79.07 MiB | 19.77 MiB | | memory/innodb/memory | 17096 | 31.68 MiB | 1.90 KiB | 22498 | 37.60 MiB | 1.71 KiB | | memory/sql/String::value | 122277 | 27.94 MiB | 239 bytes | 124699 | 29.47 MiB | 247 bytes | | memory/sql/TABLE | 9927 | 24.67 MiB | 2.55 KiB | 9929 | 24.68 MiB | 2.55 KiB | | memory/innodb/lock0lock | 8888 | 19.71 MiB | 2.27 KiB | 8888 | 19.71 MiB | 2.27 KiB | | memory/sql/Prepared_statement::infrastructure | 257623 | 16.24 MiB | 66 bytes | 257631 | 16.24 MiB | 66 bytes | | memory/mysys/KEY_CACHE | 3 | 16.00 MiB | 5.33 MiB | 3 | 16.00 MiB | 5.33 MiB | | memory/innodb/sync0arr | 3 | 7.03 MiB | 2.34 MiB | 3 | 7.03 MiB | 2.34 MiB | | memory/sql/THD::main_mem_root | 815 | 6.56 MiB | 8.24 KiB | 849 | 7.19 MiB | 8.67 KiB | +-----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ 10 rows in set (0.06 sec)
이벤트 이름을 보면 이 메모리가 준비된 문에 사용되고 있음을 알 수 있습니다. 어떤 연결이 이 메모리를 사용하고 있는지 알아보려면 memory_by_thread_by_current_bytes
다음 예제에서 각 연결에는 약 7MiB가 할당되어 있으며 최고 수치는 약 6.29MiB(current_max_alloc)입니다. 이 예제에서는 준비된 문을 포함하는 80개의 테이블과 800개의 연결을 가진 sysbench를 사용하고 있으므로 납득 가능한 수치입니다. 이 시나리오에서 메모리 사용량을 줄이려면 준비된 문의 애플리케이션 사용량을 최적화하여 메모리 사용량을 줄일 수 있습니다.
mysql> SELECT * FROM sys.memory_by_thread_by_current_bytes; +-----------+-------------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+ | thread_id | user | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated | +-----------+-------------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+ | 46 | rdsadmin@localhost | 405 | 8.47 MiB | 21.42 KiB | 8.00 MiB | 155.86 MiB | | 61 | reinvent@10.0.4.4 | 1749 | 6.72 MiB | 3.93 KiB | 6.29 MiB | 14.24 MiB | | 101 | reinvent@10.0.4.4 | 1845 | 6.71 MiB | 3.72 KiB | 6.29 MiB | 14.50 MiB | | 55 | reinvent@10.0.4.4 | 1674 | 6.68 MiB | 4.09 KiB | 6.29 MiB | 14.13 MiB | | 57 | reinvent@10.0.4.4 | 1416 | 6.66 MiB | 4.82 KiB | 6.29 MiB | 13.52 MiB | | 112 | reinvent@10.0.4.4 | 1759 | 6.66 MiB | 3.88 KiB | 6.29 MiB | 14.17 MiB | | 66 | reinvent@10.0.4.4 | 1428 | 6.64 MiB | 4.76 KiB | 6.29 MiB | 13.47 MiB | | 75 | reinvent@10.0.4.4 | 1389 | 6.62 MiB | 4.88 KiB | 6.29 MiB | 13.40 MiB | | 116 | reinvent@10.0.4.4 | 1333 | 6.61 MiB | 5.08 KiB | 6.29 MiB | 13.21 MiB | | 90 | reinvent@10.0.4.4 | 1448 | 6.59 MiB | 4.66 KiB | 6.29 MiB | 13.58 MiB | | 98 | reinvent@10.0.4.4 | 1440 | 6.57 MiB | 4.67 KiB | 6.29 MiB | 13.52 MiB | | 94 | reinvent@10.0.4.4 | 1433 | 6.57 MiB | 4.69 KiB | 6.29 MiB | 13.49 MiB | | 62 | reinvent@10.0.4.4 | 1323 | 6.55 MiB | 5.07 KiB | 6.29 MiB | 13.48 MiB | | 87 | reinvent@10.0.4.4 | 1323 | 6.55 MiB | 5.07 KiB | 6.29 MiB | 13.25 MiB | | 99 | reinvent@10.0.4.4 | 1346 | 6.54 MiB | 4.98 KiB | 6.29 MiB | 13.24 MiB | | 105 | reinvent@10.0.4.4 | 1347 | 6.54 MiB | 4.97 KiB | 6.29 MiB | 13.34 MiB | | 73 | reinvent@10.0.4.4 | 1335 | 6.54 MiB | 5.02 KiB | 6.29 MiB | 13.23 MiB | | 54 | reinvent@10.0.4.4 | 1510 | 6.53 MiB | 4.43 KiB | 6.29 MiB | 13.49 MiB | . . . . . . | 812 | reinvent@10.0.4.4 | 1259 | 6.38 MiB | 5.19 KiB | 6.29 MiB | 13.05 MiB | | 214 | reinvent@10.0.4.4 | 1279 | 6.38 MiB | 5.10 KiB | 6.29 MiB | 12.90 MiB | | 325 | reinvent@10.0.4.4 | 1254 | 6.38 MiB | 5.21 KiB | 6.29 MiB | 12.99 MiB | | 705 | reinvent@10.0.4.4 | 1273 | 6.37 MiB | 5.13 KiB | 6.29 MiB | 13.03 MiB | | 530 | reinvent@10.0.4.4 | 1268 | 6.37 MiB | 5.15 KiB | 6.29 MiB | 12.92 MiB | | 307 | reinvent@10.0.4.4 | 1263 | 6.37 MiB | 5.17 KiB | 6.29 MiB | 12.87 MiB | | 738 | reinvent@10.0.4.4 | 1260 | 6.37 MiB | 5.18 KiB | 6.29 MiB | 13.00 MiB | | 819 | reinvent@10.0.4.4 | 1252 | 6.37 MiB | 5.21 KiB | 6.29 MiB | 13.01 MiB | | 31 | innodb/srv_purge_thread | 17810 | 3.14 MiB | 184 bytes | 2.40 MiB | 205.69 MiB | | 38 | rdsadmin@localhost | 599 | 1.76 MiB | 3.01 KiB | 1.00 MiB | 25.58 MiB | | 1 | sql/main | 3756 | 1.32 MiB | 367 bytes | 355.78 KiB | 6.19 MiB | | 854 | rdsadmin@localhost | 46 | 1.08 MiB | 23.98 KiB | 1.00 MiB | 5.10 MiB | | 30 | innodb/clone_gtid_thread | 1596 | 573.14 KiB | 367 bytes | 254.91 KiB | 970.69 KiB | | 40 | rdsadmin@localhost | 235 | 245.19 KiB | 1.04 KiB | 128.88 KiB | 808.64 KiB | | 853 | rdsadmin@localhost | 96 | 94.63 KiB | 1009 bytes | 29.73 KiB | 422.45 KiB | | 36 | rdsadmin@localhost | 33 | 36.29 KiB | 1.10 KiB | 16.08 KiB | 74.15 MiB | | 33 | sql/event_scheduler | 3 | 16.27 KiB | 5.42 KiB | 16.04 KiB | 16.27 KiB | | 35 | sql/compress_gtid_table | 8 | 14.20 KiB | 1.77 KiB | 8.05 KiB | 18.62 KiB | | 25 | innodb/fts_optimize_thread | 12 | 1.86 KiB | 158 bytes | 648 bytes | 1.98 KiB | | 23 | innodb/srv_master_thread | 11 | 1.23 KiB | 114 bytes | 361 bytes | 24.40 KiB | | 24 | innodb/dict_stats_thread | 11 | 1.23 KiB | 114 bytes | 361 bytes | 1.35 KiB | | 5 | innodb/io_read_thread | 1 | 144 bytes | 144 bytes | 144 bytes | 144 bytes | | 6 | innodb/io_read_thread | 1 | 144 bytes | 144 bytes | 144 bytes | 144 bytes | | 2 | sql/aws_oscar_log_level_monitor | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 4 | innodb/io_ibuf_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 7 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 8 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 9 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 10 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 11 | innodb/srv_lra_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 12 | innodb/srv_akp_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 18 | innodb/srv_lock_timeout_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 248 bytes | | 19 | innodb/srv_error_monitor_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 20 | innodb/srv_monitor_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 21 | innodb/buf_resize_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 22 | innodb/btr_search_sys_toggle_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 32 | innodb/dict_persist_metadata_table_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 34 | sql/signal_handler | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | +-----------+-------------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+ 831 rows in set (2.48 sec)
앞서 설명한 것처럼 여기서 스레드 ID(thd_id) 값은 서버 백그라운드 스레드 또는 데이터베이스 연결을 참조할 수 있습니다. 스레드 ID 값을 데이터베이스 연결 ID에 매핑하려면 performance_schema.threads 테이블 또는 sys.processlist 뷰를 사용할 수 있으며, 연결 ID는 conn_id입니다.
mysql> SELECT thd_id,conn_id,user,db,command,state,time,last_wait FROM sys.processlist WHERE user='reinvent@10.0.4.4'; +--------+---------+-------------------+----------+---------+----------------+------+-------------------------------------------------+ | thd_id | conn_id | user | db | command | state | time | last_wait | +--------+---------+-------------------+----------+---------+----------------+------+-------------------------------------------------+ | 590 | 562 | reinvent@10.0.4.4 | sysbench | Execute | closing tables | 0 | wait/io/redo_log_flush | | 578 | 550 | reinvent@10.0.4.4 | sysbench | Sleep | NULL | 0 | idle | | 579 | 551 | reinvent@10.0.4.4 | sysbench | Execute | closing tables | 0 | wait/io/redo_log_flush | | 580 | 552 | reinvent@10.0.4.4 | sysbench | Execute | updating | 0 | wait/io/table/sql/handler | | 581 | 553 | reinvent@10.0.4.4 | sysbench | Execute | updating | 0 | wait/io/table/sql/handler | | 582 | 554 | reinvent@10.0.4.4 | sysbench | Sleep | NULL | 0 | idle | | 583 | 555 | reinvent@10.0.4.4 | sysbench | Sleep | NULL | 0 | idle | | 584 | 556 | reinvent@10.0.4.4 | sysbench | Execute | updating | 0 | wait/io/table/sql/handler | | 585 | 557 | reinvent@10.0.4.4 | sysbench | Execute | closing tables | 0 | wait/io/redo_log_flush | | 586 | 558 | reinvent@10.0.4.4 | sysbench | Execute | updating | 0 | wait/io/table/sql/handler | | 587 | 559 | reinvent@10.0.4.4 | sysbench | Execute | closing tables | 0 | wait/io/redo_log_flush | . . . . . . | 323 | 295 | reinvent@10.0.4.4 | sysbench | Sleep | NULL | 0 | idle | | 324 | 296 | reinvent@10.0.4.4 | sysbench | Execute | updating | 0 | wait/io/table/sql/handler | | 325 | 297 | reinvent@10.0.4.4 | sysbench | Execute | closing tables | 0 | wait/io/redo_log_flush | | 326 | 298 | reinvent@10.0.4.4 | sysbench | Execute | updating | 0 | wait/io/table/sql/handler | | 438 | 410 | reinvent@10.0.4.4 | sysbench | Execute | System lock | 0 | wait/lock/table/sql/handler | | 280 | 252 | reinvent@10.0.4.4 | sysbench | Sleep | starting | 0 | wait/io/socket/sql/client_connection | | 98 | 70 | reinvent@10.0.4.4 | sysbench | Query | freeing items | 0 | NULL | +--------+---------+-------------------+----------+---------+----------------+------+-------------------------------------------------+ 804 rows in set (5.51 sec)
이제 sysbench 워크로드를 중지하여 연결을 닫고 메모리를 해제합니다. 이벤트를 다시 확인해 보면 메모리가 해제된 것을 확인할 수 있지만, high_alloc을 통해 최고 수치를 계속 확인할 수 있습니다. high_alloc 열은 현재 할당된 메모리만 보여주는 current_alloc의 메모리 사용량을 즉시 식별하지 못할 수도 있는 단기 메모리 사용량 급증을 식별하는 데 매우 유용할 수 있습니다.
mysql> SELECT * FROM sys.memory_global_by_current_bytes WHERE event_name='memory/sql/Prepared_statement::main_mem_root' LIMIT 10; +----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | memory/sql/Prepared_statement::main_mem_root | 17 | 253.80 KiB | 14.93 KiB | 512823 | 4.91 GiB | 10.04 KiB | +----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ 1 row in set (0.00 sec)
high_alloc을 재설정하려는 경우 performance_schema 메모리 요약 테이블을 잘라낼 수 있지만 이렇게 하면 모든 메모리 계측이 재설정됩니다. 자세한 내용은 MySQL 설명서의 성능 스키마 일반 테이블 특성
다음 예제에서는 잘린 후 high_alloc이 재설정되는 것을 볼 수 있습니다.
mysql> TRUNCATE `performance_schema`.`memory_summary_global_by_event_name`; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM sys.memory_global_by_current_bytes WHERE event_name='memory/sql/Prepared_statement::main_mem_root' LIMIT 10; +----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | memory/sql/Prepared_statement::main_mem_root | 17 | 253.80 KiB | 14.93 KiB | 17 | 253.80 KiB | 14.93 KiB | +----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ 1 row in set (0.00 sec)
예제 2: 일시적인 메모리 급증
또 다른 일반적인 현상은 데이터베이스 서버의 메모리 사용량이 일시적으로 급증하는 것입니다. 사용 가능한 메모리가 주기적으로 떨어지면 메모리가 이미 비워졌기 때문에 sys.memory_global_by_current_bytes에서 current_alloc을 사용할 때 문제를 해결하기가 어려울 수 있습니다.
참고
성능 스키마 통계를 재설정하거나 데이터베이스 인스턴스를 다시 시작한 경우 sys 또는 performance_schema에서 이 정보를 사용할 수 없습니다. 이 정보를 유지하려면 외부 지표 수집을 구성하는 것이 좋습니다.
향상된 모니터링의 다음 os.memory.free 지표 그래프는 7초간의 단기 메모리 사용량 급증을 보여줍니다. 향상된 모니터링을 통해 1초 정도의 짧은 간격으로 모니터링할 수 있어 이와 같은 일시적 급증을 포착하는 데 적합합니다.
여기서 메모리 사용량의 원인을 진단하는 데 도움이 되도록 sys 메모리 요약 뷰의 high_alloc과 성능 스키마 문 요약 테이블
예상한 바와 같이 현재 메모리 사용량이 많지 않으므로 current_alloc의 sys 스키마 뷰에서 주요 문제 인자를 확인할 수 없습니다.
mysql> SELECT * FROM sys.memory_global_by_current_bytes LIMIT 10; +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | memory/innodb/hash0hash | 4 | 79.07 MiB | 19.77 MiB | 4 | 79.07 MiB | 19.77 MiB | | memory/innodb/os0event | 439372 | 60.34 MiB | 144 bytes | 439372 | 60.34 MiB | 144 bytes | | memory/performance_schema/events_statements_summary_by_digest | 1 | 40.28 MiB | 40.28 MiB | 1 | 40.28 MiB | 40.28 MiB | | memory/mysys/KEY_CACHE | 3 | 16.00 MiB | 5.33 MiB | 3 | 16.00 MiB | 5.33 MiB | | memory/performance_schema/events_statements_history_long | 1 | 14.34 MiB | 14.34 MiB | 1 | 14.34 MiB | 14.34 MiB | | memory/performance_schema/events_errors_summary_by_thread_by_error | 257 | 13.07 MiB | 52.06 KiB | 257 | 13.07 MiB | 52.06 KiB | | memory/performance_schema/events_statements_summary_by_thread_by_event_name | 1 | 11.81 MiB | 11.81 MiB | 1 | 11.81 MiB | 11.81 MiB | | memory/performance_schema/events_statements_summary_by_digest.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | | memory/performance_schema/events_statements_history_long.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | | memory/performance_schema/events_statements_history_long.sql_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ 10 rows in set (0.01 sec)
뷰를 high_alloc 기준으로 정렬하여 펼치면 이제 memory/temptable/physical_ram 구성 요소가 매우 적합한 후보임을 알 수 있습니다. 최대 용량으로는 515.00MiB를 소비했습니다.
이름에서 알 수 있듯이 memory/temptable/physical_ram은 MySQL 8.0에 도입된 MySQL의 TEMP 스토리지 엔진의 메모리 사용량을 측정합니다. MySQL에서 임시 테이블을 사용하는 방법에 대한 자세한 내용은 MySQL 설명서의 MySQL의 내부 임시 테이블 사용
참고
이 예제에서는 sys.x$memory_global_by_current_bytes 뷰를 사용합니다.
mysql> SELECT event_name, format_bytes(current_alloc) AS "currently allocated", sys.format_bytes(high_alloc) AS "high-water mark" FROM sys.x$memory_global_by_current_bytes ORDER BY high_alloc DESC LIMIT 10; +-----------------------------------------------------------------------------+---------------------+-----------------+ | event_name | currently allocated | high-water mark | +-----------------------------------------------------------------------------+---------------------+-----------------+ | memory/temptable/physical_ram | 4.00 MiB | 515.00 MiB | | memory/innodb/hash0hash | 79.07 MiB | 79.07 MiB | | memory/innodb/os0event | 63.95 MiB | 63.95 MiB | | memory/performance_schema/events_statements_summary_by_digest | 40.28 MiB | 40.28 MiB | | memory/mysys/KEY_CACHE | 16.00 MiB | 16.00 MiB | | memory/performance_schema/events_statements_history_long | 14.34 MiB | 14.34 MiB | | memory/performance_schema/events_errors_summary_by_thread_by_error | 13.07 MiB | 13.07 MiB | | memory/performance_schema/events_statements_summary_by_thread_by_event_name | 11.81 MiB | 11.81 MiB | | memory/performance_schema/events_statements_summary_by_digest.digest_text | 9.77 MiB | 9.77 MiB | | memory/performance_schema/events_statements_history_long.sql_text | 9.77 MiB | 9.77 MiB | +-----------------------------------------------------------------------------+---------------------+-----------------+ 10 rows in set (0.00 sec)
예제 1: 지속적인 높은 메모리 사용량에서는 각 연결의 현재 메모리 사용량을 확인하여 문제의 메모리 사용을 담당하는 연결을 확인했습니다. 이 예제에서는 메모리가 이미 비워졌으므로 현재 연결의 메모리 사용량을 확인하는 것은 유용하지 않습니다.
더 자세히 알아보고 문제가 되는 문, 사용자, 호스트를 찾기 위해 성능 스키마를 사용합니다. 성능 스키마에는 이벤트 이름, 문 다이제스트, 호스트, 스레드, 사용자 등 다양한 차원으로 구분된 여러 문 요약 테이블이 포함되어 있습니다. 각 뷰를 통해 특정 문이 실행되는 위치와 수행하는 작업을 더 자세히 살펴볼 수 있습니다. 이 섹션은 MAX_TOTAL_MEMORY를 중점적으로 다루지만, 사용 가능한 모든 열에 대한 자세한 내용은 성능 스키마 문 요약 테이블
mysql> SHOW TABLES IN performance_schema LIKE 'events_statements_summary_%'; +------------------------------------------------------------+ | Tables_in_performance_schema (events_statements_summary_%) | +------------------------------------------------------------+ | events_statements_summary_by_account_by_event_name | | events_statements_summary_by_digest | | events_statements_summary_by_host_by_event_name | | events_statements_summary_by_program | | events_statements_summary_by_thread_by_event_name | | events_statements_summary_by_user_by_event_name | | events_statements_summary_global_by_event_name | +------------------------------------------------------------+ 7 rows in set (0.00 sec)
먼저 events_statements_summary_by_digest를 확인해 MAX_TOTAL_MEMORY를 살펴봅니다.
여기서 다음 내용을 확인할 수 있습니다.
-
다이제스트
20676ce4a690592ff05debcffcbc26faeb76f22005e7628364d7a498769d0c4a가 포함된 쿼리가 이 메모리 사용량에 적합한 것으로 보입니다.MAX_TOTAL_MEMORY는 5537450710으로,sys.x$memory_global_by_current_bytes의memory/temptable/physical_ram이벤트에서 살펴본 최고 수치와 일치합니다. -
이는 4번(
COUNT_STAR) 실행되었는데, 처음은 2024-03-26 04:08:34.943256에, 마지막은 2024-03-26 04:43:06.998310에 실행되었습니다.
mysql> SELECT SCHEMA_NAME,DIGEST,COUNT_STAR,MAX_TOTAL_MEMORY,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest ORDER BY MAX_TOTAL_MEMORY DESC LIMIT 5; +-------------+------------------------------------------------------------------+------------+------------------+----------------------------+----------------------------+ | SCHEMA_NAME | DIGEST | COUNT_STAR | MAX_TOTAL_MEMORY | FIRST_SEEN | LAST_SEEN | +-------------+------------------------------------------------------------------+------------+------------------+----------------------------+----------------------------+ | sysbench | 20676ce4a690592ff05debcffcbc26faeb76f22005e7628364d7a498769d0c4a | 4 | 537450710 | 2024-03-26 04:08:34.943256 | 2024-03-26 04:43:06.998310 | | NULL | f158282ea0313fefd0a4778f6e9b92fc7d1e839af59ebd8c5eea35e12732c45d | 4 | 3636413 | 2024-03-26 04:29:32.712348 | 2024-03-26 04:36:26.269329 | | NULL | 0046bc5f642c586b8a9afd6ce1ab70612dc5b1fd2408fa8677f370c1b0ca3213 | 2 | 3459965 | 2024-03-26 04:31:37.674008 | 2024-03-26 04:32:09.410718 | | NULL | 8924f01bba3c55324701716c7b50071a60b9ceaf17108c71fd064c20c4ab14db | 1 | 3290981 | 2024-03-26 04:31:49.751506 | 2024-03-26 04:31:49.751506 | | NULL | 90142bbcb50a744fcec03a1aa336b2169761597ea06d85c7f6ab03b5a4e1d841 | 1 | 3131729 | 2024-03-26 04:15:09.719557 | 2024-03-26 04:15:09.719557 | +-------------+------------------------------------------------------------------+------------+------------------+----------------------------+----------------------------+ 5 rows in set (0.00 sec)
이제 문제가 되는 다이제스트를 알았으므로 쿼리 텍스트, 실행한 사용자, 실행 위치 등의 세부 정보를 더 자세히 확인할 수 있습니다. 반환된 다이제스트 텍스트를 바탕으로 이는 임시 테이블 4개를 만들고 테이블 스캔을 4회 수행하는 일반적인 테이블 표현식(CTE)이며, 매우 비효율적이라는 것을 알 수 있습니다.
mysql> SELECT SCHEMA_NAME,DIGEST_TEXT,QUERY_SAMPLE_TEXT,MAX_TOTAL_MEMORY,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,SUM_CREATED_TMP_TABLES,SUM_NO_INDEX_USED FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST='20676ce4a690592ff05debcffcbc26faeb76f22005e7628364d7a498769d0c4a'\G; *************************** 1. row *************************** SCHEMA_NAME: sysbench DIGEST_TEXT: WITH RECURSIVE `cte` ( `n` ) AS ( SELECT ? FROM `sbtest1` UNION ALL SELECT `id` + ? FROM `sbtest1` ) SELECT * FROM `cte` QUERY_SAMPLE_TEXT: WITH RECURSIVE cte (n) AS ( SELECT 1 from sbtest1 UNION ALL SELECT id + 1 FROM sbtest1) SELECT * FROM cte MAX_TOTAL_MEMORY: 537450710 SUM_ROWS_SENT: 80000000 SUM_ROWS_EXAMINED: 80000000 SUM_CREATED_TMP_TABLES: 4 SUM_NO_INDEX_USED: 4 1 row in set (0.01 sec)
events_statements_summary_by_digest 테이블 및 기타 성능 스키마 문 요약 테이블에 대한 자세한 내용은 MySQL 설명서의 문 요약 테이블
EXPLAIN
참고
EXPLAIN ANALYZE는 EXPLAIN보다 많은 정보를 제공할 수 있지만 쿼리를 실행하기도 하므로 주의해야 합니다.
-- EXPLAIN mysql> EXPLAIN WITH RECURSIVE cte (n) AS (SELECT 1 FROM sbtest1 UNION ALL SELECT id + 1 FROM sbtest1) SELECT * FROM cte; +----+-------------+------------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 19221520 | 100.00 | NULL | | 2 | DERIVED | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 9610760 | 100.00 | Using index | | 3 | UNION | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 9610760 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec) -- EXPLAIN format=tree mysql> EXPLAIN format=tree WITH RECURSIVE cte (n) AS (SELECT 1 FROM sbtest1 UNION ALL SELECT id + 1 FROM sbtest1) SELECT * FROM cte\G; *************************** 1. row *************************** EXPLAIN: -> Table scan on cte (cost=4.11e+6..4.35e+6 rows=19.2e+6) -> Materialize union CTE cte (cost=4.11e+6..4.11e+6 rows=19.2e+6) -> Index scan on sbtest1 using k_1 (cost=1.09e+6 rows=9.61e+6) -> Index scan on sbtest1 using k_1 (cost=1.09e+6 rows=9.61e+6) 1 row in set (0.00 sec) -- EXPLAIN ANALYZE mysql> EXPLAIN ANALYZE WITH RECURSIVE cte (n) AS (SELECT 1 from sbtest1 UNION ALL SELECT id + 1 FROM sbtest1) SELECT * FROM cte\G; *************************** 1. row *************************** EXPLAIN: -> Table scan on cte (cost=4.11e+6..4.35e+6 rows=19.2e+6) (actual time=6666..9201 rows=20e+6 loops=1) -> Materialize union CTE cte (cost=4.11e+6..4.11e+6 rows=19.2e+6) (actual time=6666..6666 rows=20e+6 loops=1) -> Covering index scan on sbtest1 using k_1 (cost=1.09e+6 rows=9.61e+6) (actual time=0.0365..2006 rows=10e+6 loops=1) -> Covering index scan on sbtest1 using k_1 (cost=1.09e+6 rows=9.61e+6) (actual time=0.0311..2494 rows=10e+6 loops=1) 1 row in set (10.53 sec)
하지만 누가 실행했을까요? 성능 스키마에서 destructive_operator 사용자가 537450710의 MAX_TOTAL_MEMORY를 보유한 것을 확인할 수 있는데, 이 역시 이전 결과와 일치합니다.
참고
성능 스키마는 메모리에 저장되므로 감사의 유일한 소스로 사용해서는 안 됩니다. 문의 실행 내역과 사용자 기록을 관리해야 하는 경우 감사 로깅을 사용 설정하는 것이 좋습니다. 메모리 사용량에 대한 정보도 유지해야 하는 경우 이러한 값을 내보내고 저장하도록 모니터링을 구성하는 것이 좋습니다.
mysql> SELECT USER,EVENT_NAME,COUNT_STAR,MAX_TOTAL_MEMORY FROM performance_schema.events_statements_summary_by_user_by_event_name ORDER BY MAX_CONTROLLED_MEMORY DESC LIMIT 5; +----------------------+---------------------------+------------+------------------+ | USER | EVENT_NAME | COUNT_STAR | MAX_TOTAL_MEMORY | +----------------------+---------------------------+------------+------------------+ | destructive_operator | statement/sql/select | 4 | 537450710 | | rdsadmin | statement/sql/select | 4172 | 3290981 | | rdsadmin | statement/sql/show_tables | 2 | 3615821 | | rdsadmin | statement/sql/show_fields | 2 | 3459965 | | rdsadmin | statement/sql/show_status | 75 | 1914976 | +----------------------+---------------------------+------------+------------------+ 5 rows in set (0.00 sec) mysql> SELECT HOST,EVENT_NAME,COUNT_STAR,MAX_TOTAL_MEMORY FROM performance_schema.events_statements_summary_by_host_by_event_name WHERE HOST != 'localhost' AND COUNT_STAR>0 ORDER BY MAX_CONTROLLED_MEMORY DESC LIMIT 5; +------------+----------------------+------------+------------------+ | HOST | EVENT_NAME | COUNT_STAR | MAX_TOTAL_MEMORY | +------------+----------------------+------------+------------------+ | 10.0.8.231 | statement/sql/select | 4 | 537450710 | +------------+----------------------+------------+------------------+ 1 row in set (0.00 sec)
Aurora MySQL 데이터베이스의 메모리 부족 문제 해결
The Aurora MySQL aurora_oom_response 인스턴스 수준 파라미터를 사용하면 DB 인스턴스가 시스템 메모리를 모니터링하고 다양한 명령문 및 연결에서 소비되는 메모리를 예측할 수 있습니다. 시스템 메모리가 부족해지면 시스템은 이 메모리를 해제하려고 시도하는 작업 목록을 수행할 수 있습니다. 메모리 부족(OOM) 문제로 인한 데이터베이스 재시작을 피하려고 시도하는 과정에서 그러한 작업 목록을 수행합니다. 이 인스턴스 수준 파라미터는 메모리가 부족할 때 DB 인스턴스가 수행하는 쉼표로 구분된 작업 문자열을 사용합니다. aurora_oom_response 파라미터는 Aurora MySQL 버전 2 및 3에서 지원됩니다.
다음 값과 그 조합을 aurora_oom_response 파라미터에 사용할 수 있습니다. 문자열을 비워두면 어떠한 작업도 수행하지 않는다는 뜻이며 해당 기능이 사실상 해제되어 데이터베이스가 OOM으로 인해 재시작되기 쉽습니다.
-
decline– DB 인스턴스 메모리가 부족해지면 새 쿼리를 거부합니다. kill_connect- 많은 양의 메모리를 사용하는 데이터베이스 연결을 닫고 현재 트랜잭션과 데이터 정의 언어(DDL) 문을 종료합니다. 이 응답은 Aurora MySQL 버전 2에서 지원되지 않습니다.자세한 내용은 MySQL 설명서의 KILL statement
를 참조하세요. -
kill_query– 인스턴스 메모리가 하한값 이상이 될 때까지 메모리 사용량이 많은 순서로 쿼리를 종료합니다. DDL 문이 종료되지 않습니다.자세한 내용은 MySQL 설명서의 KILL statement
를 참조하세요. -
print- 많은 양의 메모리를 사용하는 쿼리만 인쇄합니다. -
tune– 내부 테이블 캐시를 조정하여 일부 메모리를 시스템으로 돌려줍니다. Aurora MySQL은 메모리가 부족해지면table_open_cache,table_definition_cache같은 캐시에 사용하는 메모리를 줄입니다. 그리고 시스템의 메모리가 부족해지지 않게 되면 이러한 캐시에 사용하는 메모리를 정상 수준으로 되돌립니다.자세한 내용은 MySQL 설명서의 table_open_cache
및 table_definition_cache 를 참조하세요. tune_buffer_pool- 버퍼 풀의 크기를 줄여 일부 메모리를 확보하고 데이터베이스 서버가 연결을 처리하는 데 사용할 수 있도록 합니다. 이 응답은 Aurora MySQL 버전 3.06 이상에서 지원됩니다.tune_buffer_pool을aurora_oom_response파라미터 값 중kill_query또는kill_connect와 짝지어야 합니다. 그러지 않으면 파라미터 값에tune_buffer_pool을 포함하더라도 버퍼 풀 크기가 조정되지 않습니다.
3.06 미만의 Aurora MySQL 버전에서 메모리가 4GiB 이하인 DB 인스턴스 클래스의 경우, 인스턴스에 메모리 부족 현상이 발생하면 기본 작업에 print, tune, decline, kill_query 등이 포함됩니다. 메모리가 4GiB보다 큰 DB 인스턴스 클래스의 경우, 파라미터 값은 기본적으로 비어 있습니다(비활성화됨).
Aurora MySQL 버전 3.06 이상에서는 메모리가 4GiB 이하인 DB 인스턴스 클래스의 경우, Aurora MySQL은 메모리를 가장 많이 소비하는 연결도 종료합니다(kill_connect). 메모리가 4GiB보다 큰 DB 인스턴스 클래스의 경우, 기본 파라미터 값은 print입니다.
메모리 부족 문제가 자주 발생하는 경우, performance_schema가 활성화되면 메모리 요약 테이블
OOM과 관련된 Amazon CloudWatch 지표에 대해서는 Amazon Aurora에 대한 인스턴스 수준 지표의 내용을 참조하세요. OOM과 관련된 글로벌 상태 변수는 Aurora MySQL 글로벌 상태 변수의 내용을 참조하세요.
