인스턴스의 높은 메모리 사용량 최적화

인스턴스에 많은 메모리가 사용되거나 메모리 부족 (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와 같은 매개변수로 제어됩니다.

innodb_buffer_pool_size 플래그로 구성되는 InnoDB 버퍼 풀은 MySQL용 Cloud SQL 인스턴스에서 상당한 양의 메모리를 차지하며 성능 향상을 위한 캐시 역할을 합니다. 메모리 부족 (OOM) 이벤트의 위험을 줄이려면 관리형 버퍼 풀을 사용 설정하면 됩니다.

캐시 메모리 소비

캐시 메모리에는 동일한 후속 쿼리의 빠른 데이터 가져오기를 위해 쿼리 및 결과를 저장하는 데 사용되는 쿼리 캐시가 포함됩니다. 또한 트랜잭션이 실행되는 동안 바이너리 로그에 대한 변경사항을 보존하기 위한 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 문서를 참고하세요.

권장사항

다음 섹션에서는 최적의 메모리 사용을 위한 몇 가지 권장사항을 제공합니다.

관리형 버퍼 풀 사용 설정

관리형 버퍼 풀을 사용 설정하면 인스턴스 메모리가 높을 때 InnoDB 버퍼 풀 (또는 innodb_buffer_pool_size)의 메모리 소비량을 줄일 수 있습니다. 이렇게 하면 다른 데이터베이스 프로세스에서 사용할 수 있는 메모리가 확보됩니다. 인스턴스 메모리 사용량이 높으면 인스턴스에서 메모리 부족 (OOM) 이벤트가 발생할 수 있습니다. OOM 이벤트를 방지하려면 인스턴스에서 관리형 버퍼 풀을 사용 설정하는 것이 좋습니다.

메모리 사용량이 10분 이상 낮은 값으로 안정화되면 관리 버퍼 풀은 innodb_buffer_pool_size 값을 원래 값으로 점진적으로 늘립니다. 메모리 사용량이 안정화된 후 innodb_buffer_pool_size 플래그 값을 선택한 값으로 늘릴 수도 있습니다.

자격 기준

공유 코어 인스턴스, MySQL 5.6 또는 MySQL 5.7에서는 관리형 버퍼 풀을 사용 설정할 수 없습니다.

기능 사용 설정

인스턴스에 관리형 버퍼 풀을 사용 설정하려면 innodb_cloudsql_managed_buffer_pool 플래그를 on로 설정합니다. 데이터베이스 플래그 설정에 대한 자세한 내용은 데이터베이스 플래그 설정을 참고하세요.

innodb_cloudsql_managed_buffer_pool 플래그의 값을 변경해도 Cloud SQL 인스턴스를 다시 시작할 필요가 없습니다.

관리형 버퍼 풀을 사용 설정했고 인스턴스의 메모리 소비량이 할당된 메모리의 기본 임계값 비율을 초과하면 Cloud SQL에서 innodb_buffer_pool_size의 크기를 줄이기 시작합니다. 이 기본 임계값 비율은 인스턴스의 RAM 용량에 따라 90% ~97% 로 달라집니다. 기준을 수정하려면 innodb_cloudsql_managed_buffer_pool_threshold_pct 플래그를 다른 백분율 값으로 설정합니다. 예를 들어 기준점을 97%로 조정하려면 다음 명령어를 사용합니다.

gcloud sql instances patch INSTANCE_NAME \
   --database-flags=EXISTING_FLAGS,innodb_cloudsql_managed_buffer_pool=on,\
     innodb_cloudsql_managed_buffer_pool_threshold_pct=97

innodb_cloudsql_managed_buffer_pool_threshold_pct 플래그를 50~99 사이의 정수 값으로 설정할 수 있습니다. 메모리 사용량 임계값을 변경해도 Cloud SQL 인스턴스를 다시 시작할 필요가 없습니다.

관리 버퍼 풀은 이전 감소 후 10분 이상 메모리 사용량이 안정화되면 innodb_buffer_pool_size 플래그를 늘립니다. 기본적으로 데이터베이스 메모리 사용량이 70% 이하로 유지되어야 증가가 트리거됩니다. 기준을 수정하려면 innodb_cloudsql_managed_buffer_pool_tuneup_pct 플래그를 다른 백분율 값으로 설정합니다. 예를 들어 증가 기준을 80%로 변경하려면 다음 명령어를 사용합니다.

gcloud sql instances patch INSTANCE_NAME \
   --database-flags=EXISTING_FLAGS,innodb_cloudsql_managed_buffer_pool=on,\
     innodb_cloudsql_managed_buffer_pool_tuneup_pct=80

조정 로직

관리형 버퍼 풀은 innodb_buffer_pool_size를 미리 결정된 고정 최소 크기로 축소하지 않습니다. 대신 구성된 임곗값 비율(innodb_cloudsql_managed_buffer_pool_threshold_pct) 아래로 총 인스턴스 메모리 사용량이 떨어질 때까지 반복적으로 동적으로 크기를 줄입니다. innodb_buffer_pool_size 플래그 값을 조정하여 버퍼 풀을 축소하고 InnoDB의 내장 버퍼 풀 크기 조절 기능을 활용합니다.

메모리 사용량이 감소해도 성능에 심각한 영향을 미치는 크기로 innodb_buffer_pool_size가 축소되지 않도록 이 기능은 내부 안전 하한을 사용합니다. 값은 버퍼 풀에 할당해야 하는 총 인스턴스 메모리의 비율을 나타냅니다.

innodb_buffer_pool_size가 감소하는 양은 데이터베이스 인스턴스의 메모리 용량에 따라 다릅니다. 다음 표에는 각 컨테이너 크기의 감소 비율이 나와 있습니다.

새로운 감소된 값을 계산한 후 관리 버퍼 풀은 innodb_buffer_pool_size을 innodb_buffer_pool_instances 및 innodb_buffer_pool_chunk_size 값의 가장 가까운 배수로 반올림합니다.

관리형 버퍼 풀에서 innodb_buffer_pool_size 값을 조정하면 변경사항이 Google Cloud 콘솔에 반영되지 않습니다. 관리 버퍼 풀이 사용 설정된 경우 innodb_buffer_pool_size의 현재 값을 보려면 MySQL 클라이언트를 사용하면 됩니다.

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

제한사항

버퍼 풀 크기를 줄여도 모든 경우에 OOM을 방지할 수는 없습니다. 예를 들어 일부 워크로드가 지속 불가능한 방식으로 메모리를 소비하거나 갑자기 증가할 수 있고, 일부 Cloud SQL 인스턴스가 프로비저닝 부족일 수 있으며, 버퍼 풀이 워밍업되지 않았을 수 있습니다. Cloud SQL은 메모리 워크로드의 갑작스러운 변화를 수용할 만큼 빠르게 메모리를 확보하지 못할 수 있습니다. 또한 Cloud SQL은 다른 메모리 플래그의 잘못 구성된 값을 수용할 수 없습니다.

모니터링

MySQL 오류 로그에서 관리형 버퍼 풀을 모니터링할 수 있습니다. Cloud Logging 로그 탐색기에서 mysql.err 로그를 필터링하여 Managed Buffer Pool Plugin: 또는 Tuner Plugin: 접두사가 있는 항목을 찾아 최신 조정 이벤트를 확인할 수 있습니다.

관리형 버퍼 풀이 처음 시작되면 다음과 같은 로그가 출력됩니다.

[Note] [MY-000000] [Server] Managed Buffer Pool Plugin: MySQL Instance memory limit: 29533, Current MySQL memory usage: 2663641088, Max Allowed MySQL memory usage: 30732730368 ...

다음 로그는 innodb_buffer_pool_size의 자동 감소의 예를 보여줍니다.

[Note] [MY-000000] [Server] Managed Buffer Pool Plugin: Decreasing InnoDB Buffer Pool Size.
[Note] [MY-000000] [Server] Managed Buffer Pool Plugin: Updated innodb_buffer_pool_size=805306368 bytes.

시간이 지남에 따라 관리형 버퍼 풀 조정 이벤트를 추적하도록 로그 기반 측정항목을 구성할 수 있습니다.

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

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

일반적으로 database/memory/components.cache 및 database/memory/components.free를 결합한 메모리가 10% 미만이면 OOM 이벤트가 발생할 위험이 높습니다. 메모리 사용량을 모니터링하고 OOM 이벤트를 방지하려면 database/memory/components.usage에서 측정항목 기준점 조건이 있는 알림 정책을 설정하는 것이 좋습니다.

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

메모리 소비 계산

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을 방지하세요.

다음 단계

• Google Cloud 추천자