인스턴스의 높은 메모리 소비 최적화

인스턴스에 많은 메모리가 사용되거나 메모리 부족(OOM) 문제가 발생하는 것은 흔한 문제입니다. 메모리 사용량이 높은 상태로 실행되는 데이터베이스 인스턴스는 성능 문제, 중단 또는 데이터베이스 다운타임을 일으키는 경우가 많습니다.

일부 MySQL 메모리 블록은 전역적으로 사용됩니다. 즉, 모든 쿼리 워크로드가 메모리 위치를 공유하고 항상 메모리를 차지하며 MySQL 프로세스가 중지될 때만 메모리가 해제됩니다. 일부 메모리 블록은 세션 기반이어서 세션이 닫히는 즉시 해당 세션에서 사용하는 메모리도 시스템으로 다시 해제됩니다.

MySQL용 Cloud SQL 인스턴스에서 높은 메모리 사용량이 발생할 때마다 많은 메모리를 사용하는 쿼리 또는 프로세스를 식별하고 해제하는 것이 좋습니다. MySQL 메모리 소비는 다음과 같은 세 가지 주요 부분으로 나뉩니다.

스레드 및 프로세스 메모리 소비
버퍼 메모리 소비
캐시 메모리 소비

스레드 및 프로세스 메모리 소비

각 사용자 세션은 세션에서 사용하는 실행 중인 쿼리, 버퍼, 캐시에 따라 메모리를 소비하며 MySQL의 세션 매개변수로 제어됩니다. 주요 매개변수는 다음과 같습니다.

thread_stack
net_buffer_length
read_buffer_size
read_rnd_buffer_size
sort_buffer_size
join_buffer_size
max_heap_table_size
tmp_table_size

특정 시간에 실행되는 쿼리 수가 N개이면 각 쿼리는 세션 중 이러한 매개변수에 따라 메모리를 사용합니다.

버퍼 메모리 소비

이 메모리 부분은 모든 쿼리에 공통적이며 Innodb_buffer_pool_size, Innodb_log_buffer_size, key_buffer_size 같은 매개변수로 제어됩니다.

캐시 메모리 소비

캐시 메모리에는 동일한 후속 쿼리의 빠른 데이터 가져오기를 위해 쿼리 및 결과를 저장하는 데 사용되는 쿼리 캐시가 포함됩니다. 또한 트랜잭션이 실행되는 동안 바이너리 로그에 대한 변경사항을 보존하기 위한 binlog 캐시가 포함되며 binlog_cache_size로 제어됩니다.

기타 메모리 소비

메모리는 조인 및 정렬 작업에서도 사용됩니다. 쿼리에서 조인 또는 정렬 작업을 사용하는 경우 이러한 쿼리가 join_buffer_size 및 sort_buffer_size를 기준으로 메모리를 사용합니다.

이와 별개로 성능 스키마를 사용 설정해도 메모리가 소비됩니다. 성능 스키마의 메모리 사용량을 확인하려면 다음 쿼리를 사용합니다.

SELECT *
FROM
  performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/performance_schema/%';

MySQL에는 성능 스키마를 통한 메모리 사용량을 모니터링하도록 설정할 수 있는 다양한 계측이 있습니다. 자세한 내용은 MySQL 문서를 참조하세요.

일괄 데이터 삽입을 위한 MyISAM 관련 매개변수는 bulk_insert_buffer_size입니다.

MySQL에서 메모리를 사용하는 방법은 MySQL 문서를 참조하세요.

권장사항

측정항목 탐색기를 사용하여 메모리 사용량 식별

측정항목 탐색기에서 database/memory/components.usage 측정항목으로 인스턴스의 메모리 사용량을 검토할 수 있습니다.

database/memory/components.cache 및 database/memory/components.free를 결합한 메모리가 5% 미만인 경우 OOM 이벤트가 발생할 위험이 높습니다. 메모리 사용량을 모니터링하고 OOM 이벤트를 방지하려면 database/memory/components.usage에서 측정항목 임계값 조건이 95% 이상인 알림 정책을 설정하는 것이 좋습니다.

다음 표에서는 인스턴스 메모리와 권장 알림 기준 간의 관계를 보여줍니다.

인스턴스 메모리	권장 알림 기준
최대 100GB	95%
100GB~200GB	96%
200GB~300GB	97%
300GB 초과	98%

메모리 소비 계산

MySQL 데이터베이스의 최대 메모리 사용량을 계산하여 MySQL 데이터베이스에 적합한 인스턴스 유형을 선택합니다. 다음 수식을 사용합니다.

최대 MySQL 메모리 사용량 = innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + tmp_table_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) x max_connections)

수식에 사용되는 매개변수는 다음과 같습니다.

innodb_buffer_pool_size: InnoDB에서 테이블 및 색인 데이터를 캐시하는 메모리 영역인 버퍼 풀의 크기(바이트)입니다.
innodb_additional_mem_pool_size: InnoDB에서 데이터 딕셔너리 정보와 기타 내부 데이터 구조를 저장하는 데 사용하는 메모리 풀 크기(바이트)입니다.
innodb_log_buffer_size: InnoDB가 디스크의 로그 파일에 쓰는 데 사용하는 버퍼의 크기(바이트)입니다.
tmp_table_size: MEMORY 스토리지 엔진 및 MySQL 8.0.28부터 TempTable 스토리지 엔진으로 생성된 내부 인메모리 임시 테이블의 최대 크기입니다.
Key_buffer_size: 색인 블록에 사용되는 버퍼 크기입니다. MyISAM 테이블의 색인 블록이 버퍼링되고 모든 스레드에서 공유됩니다.
Read_buffer_size: MyISAM 테이블에 순차적 스캔을 수행하는 각 스레드에서 스캔하는 테이블마다 이 크기의 버퍼(바이트)를 할당합니다.
Read_rnd_buffer_size: 이 변수는 MyISAM 테이블에서 읽기, 모든 스토리지 엔진, 다중 범위 읽기 최적화에 사용됩니다.
Sort_buffer_size: 정렬을 수행해야 하는 각 세션에서 이 크기의 버퍼를 할당합니다. sort_buffer_size는 스토리지 엔진에 국한되지 않으며 일반적인 방법으로 최적화를 위해 적용됩니다.
Join_buffer_size: 일반 색인 스캔, 범위 색인 스캔, 색인을 사용하지 않아 전체 테이블 스캔을 수행하는 조인에 사용되는 버퍼의 최소 크기입니다.
Max_connections: 허용되는 최대 동시 클라이언트 연결 수입니다.

높은 메모리 소비 문제 해결

SHOW PROCESSLIST를 실행하여 현재 메모리를 소비하고 있는 진행 중인 쿼리를 확인합니다. 연결된 모든 스레드와 현재 실행 중인 SQL 문을 표시하고 최적화를 시도합니다. 상태 열과 기간 열에 주의하세요.
```
mysql> SHOW [FULL] PROCESSLIST;
```

BUFFER POOL AND MEMORY 섹션의 SHOW ENGINE INNODB STATUS에서 현재 버퍼 풀과 메모리 사용량을 확인합니다. 버퍼 풀 크기를 설정하는 데 도움이 될 수 있습니다.

mysql> SHOW ENGINE INNODB STATUS \G
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 398063986; in additional pool allocated 0
Dictionary memory allocated 12056
Buffer pool size 89129
Free buffers 45671
Database pages 1367
Old database pages 0
Modified db pages 0

MySQL의 SHOW variables 명령어를 사용하여 카운터 값을 확인합니다. 임시 테이블 수, 스레드 수, 테이블 캐시 수, 더티 페이지, 열린 테이블, 버퍼 풀 사용량과 같은 정보를 제공합니다.
```
mysql> SHOW variables like 'VARIABLE_NAME'
```

변경사항 적용

여러 구성요소별로 메모리 사용량을 분석한 후 MySQL 데이터베이스에서 적절한 플래그를 설정합니다. MySQL용 Cloud SQL 인스턴스에서 플래그를 변경하려면 Google Cloud 콘솔 또는 gcloud CLI를 사용하면 됩니다. Google Cloud 콘솔을 사용하여 플래그 값을 변경하려면 플래그 섹션을 수정하고 플래그를 선택한 후 새 값을 입력합니다.

마지막으로, 메모리 사용량이 여전히 높고 실행 중인 쿼리와 플래그 값이 최적화되어 있다고 생각되면 인스턴스 크기를 늘려 OOM을 방지하세요.