Amazon RDS for MySQL 데이터베이스에서 가용 메모리 부족 문제를 해결하려면 어떻게 해야 합니까?

최종 업데이트 날짜: 2021년 8월 17일

Amazon Relational Database Service(Amazon RDS) for MySQL 인스턴스를 실행 중입니다. 사용 가능한 메모리 수준이 낮거나, 데이터베이스의 메모리가 부족하거나, 메모리 부족으로 인해 애플리케이션에 지연 시간 문제가 발생하는 것을 볼 수 있습니다. 메모리 사용의 출처를 식별하고 여유 메모리 수준이 낮은 문제를 해결하려면 어떻게 해야 합니까?

간략한 설명

Amazon RDS for MySQL에서는 다음 네 가지 메모리 상태를 모니터링할 수 있습니다.

  • 활성: 데이터베이스 프로세스 또는 스레드에서 활발하게 사용되고 있는 메모리입니다.
  • 버퍼: 버퍼는 데이터 블록을 보유하는 데 사용되는 메모리의 임시 공간입니다.
  • 사용 가능한 메모리: 사용할 수 있는 메모리입니다.
  • 캐시: 캐싱은 데이터가 일시적으로 메모리에 저장되어 데이터를 빠르게 검색할 수 있는 기술입니다.

기본적으로 Amazon RDS for MySQL 인스턴스를 생성하면 데이터베이스 작업을 개선하기 위해 버퍼와 캐시가 할당됩니다. Amazon RDS for MySQL에는 특정 작업을 수행하기 위해 내부 임시 테이블을 생성하는 내부 메모리 구성 요소(예: key_buffers_size 또는 query_cache_size)도 있습니다.

Amazon RDS for MySQL을 사용하는 경우 MySQL에서 메모리를 사용하고 할당하는 방법을 이해해야 합니다. 메모리를 사용하는 구성 요소를 식별했으면 인스턴스 및 데이터베이스 수준의 병목 현상을 찾을 수 있습니다. 그런 다음 이러한 특정 지표를 모니터링하고 최적의 성능을 위해 세션을 구성합니다.

해결 방법

MySQL에서 메모리를 사용하는 방법

Amazon RDS for MySQL의 경우 인스턴스에서 사용 가능한 메모리의 80%~90%가 기본 파라미터를 사용하여 할당됩니다. 이 할당은 성능에 최적화되어 있지만 더 많은 메모리를 사용하는 파라미터를 설정하는 경우에는 이를 보상하도록 더 적은 메모리를 사용하는 다른 파라미터를 설정합니다.

다음과 같이 RDS for MySQL DB 인스턴스 사용량을 대략적으로 계산할 수 있습니다.

Maximum MySQL Memory Usage = innodb_buffer_pool_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_log_buffer_size, key_buffer_sizequery_cache_size 같은 구성 요소를 포함합니다. innodb_buffer_pool_size 파라미터는 innodb가 데이터베이스 테이블과 인덱스 관련 데이터를 캐시하는 RAM의 메모리 영역입니다. 버퍼 풀이 클수록 디스크로 다시 전환되는 I/O 작업이 줄어듭니다. 기본적으로 innodb_buffer_pool_size는 Amazon RDS DB 인스턴스에 할당된 사용 가능한 메모리의 최대 75% 를 사용합니다.

innodb_buffer_pool_size = {DBInstanceClassMemory*3/4}

이 파라미터를 먼저 검토하여 메모리 사용 출처를 식별합니다. 그런 다음 사용자 정의 파라미터 그룹에서 파라미터 값을 수정하여 innodb_buffer_pool_size에 대한 값을 줄이는 것이 좋습니다.

예를 들어 기본값인 DBInstanceClassMemory*3/4*5/8 또는 *1/2로 줄일 수 있습니다. 인스턴스의 BufferCacheHitRatio 값이 너무 낮지 않은지 확인하십시오. BufferCacheHitRatio 값이 낮은 경우 더 많은 RAM을 위해 인스턴스 크기를 늘려야 할 수 있습니다. 자세한 내용은 Best practices for configuring parameters for Amazon RDS for MySQL, part 1: Parameters related to performance를 참조하십시오.

MySQL 스레드

메모리는 MySQL DB 인스턴스에 연결된 각 MySQL 스레드에도 할당됩니다. 다음 스레드가 할당된 메모리를 필요로 합니다.

  • thread_stack
  • net_buffer_length
  • read_buffer_size
  • sort_buffer_size
  • join_buffer_size
  • max_heap_table_size
  • tmp_table_size

또한 MySQL은 일부 작업을 수행하기 위해 내부 임시 테이블을 생성합니다. 이러한 테이블은 처음에 메모리 기반 테이블 형태로 생성됩니다. 테이블이 tmp_table_size 또는 max_heap_table_size에 의해 지정된 크기(둘 중 가장 낮은 값 적용)에 도달하면 테이블이 디스크 기반 테이블로 변환됩니다. 여러 세션이 내부 임시 테이블을 생성할 때 메모리 사용률이 증가하는 것을 볼 수도 있습니다. 메모리 사용률을 줄이려면 쿼리에 임시 테이블을 사용하지 않도록 합니다.

참고: tmp_table_sizemax_heap_table_size 제한을 늘리면 더 큰 임시 테이블이 인메모리에 있을 수 있습니다. 암시적 임시 테이블이 생성되었는지 확인하려면 created_tmp_tables 변수를 사용합니다. 이 변수에 대한 자세한 내용은 MySQL 웹 사이트의 created_tmp_tables를 참조하세요.

JOIN 및 SORT 작업

join_buffer_size 또는 sort_buffer_size와 같은 동일한 유형의 여러 버퍼가 JOIN 또는 SORT 작업 중에 할당되면 메모리 사용량이 증가합니다. 예를 들어, MySQL은 두 테이블 사이에 JOIN을 수행하기 위해 하나의 JOIN 버퍼를 할당합니다. 쿼리에 다중 테이블 JOIN이 수반되며 모든 쿼리에 JOIN 버퍼가 필요한 경우, MySQL은 총 테이블 수보다 한 개 더 적은 JOIN 버퍼를 할당합니다. 너무 높은 값으로 세션 변수를 구성하면 쿼리가 최적화되지 않은 경우 문제를 유발할 수 있습니다. join_buffer_size, join_buffer_sizesort_buffer_size와 같은 세션 수준 변수에 최소 메모리를 할당할 수 있습니다. 자세한 내용은 DB 파라미터 그룹 작업을 참조하세요.

MYISAM 테이블에 대량 삽입을 수행하는 경우에는 bulk_insert_buffer_size 바이트의 메모리가 사용됩니다. 자세한 내용은 MySQL 스토리지 엔진으로 작업하기 위한 모범 사례를 참조하세요.

성능 스키마

Amazon RDS for MySQL에 대한 성능 개선 도우미를 위해 성능 스키마를 활성화한 경우 성능 스키마에 의해 메모리가 소비될 수 있습니다. 성능 스키마가 활성화된 경우, MySQL은 인스턴스를 시작할 때 및 서버 작업을 수행하는 동안 내부 버퍼를 할당합니다. 성능 스키마가 메모리를 사용하는 방식에 대한 자세한 내용은 MySQL 설명서의 The Performance Schema memory-allocation model을 참조하세요.

성능 스키마 테이블과 함께 MySQL sys 스키마를 사용할 수도 있습니다. 예를 들어, performance_schema 이벤트를 사용하면 성능 스키마에 의해 사용되는 내부 버퍼에 얼마나 많은 메모리가 할당되었는지를 표시할 수 있습니다. 또는 다음과 같은 쿼리를 실행하여 할당된 메모리 양을 확인할 수 있습니다.

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

메모리 계측은 "memory/code_area/instrument_name" 형식에 따라 setup_instruments 테이블에 나열됩니다. 메모리 계측을 활성화하려면 setup_instruments 테이블에서 관련 계측 장치의 ENABLED 열을 업데이트합니다.

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';

인스턴스의 메모리 사용량 모니터링

Amazon CloudWatch 지표

사용 가능한 메모리 수준이 낮으면 DatabaseConnections, CPUUtilization, ReadIOPSWriteIOPS에 대한 Amazon CloudWatch 지표를 모니터링합니다.

DatabaseConnections의 경우 데이터베이스에 대한 각 연결에 할당된 메모리가 어느 정도 필요합니다. 따라서 데이터베이스 연결이 급증하면 사용 가능한 메모리가 줄어들 수 있습니다. Amazon RDS에서 max_connections의 소프트 한도는 다음과 같이 계산됩니다.

{DBInstanceClassMemory/12582880}

Amazon CloudWatch에서 DatabaseConnections 지표를 확인하여 이 소프트 한도를 초과하는지 여부를 모니터링합니다.

또한 FreeableMemory뿐 아니라 SwapUsage에 대한 CloudWatch 지표를 모니터링하여 메모리 압력을 확인합니다. 많은 양의 스왑이 사용되고 FreeableMemory 수준이 낮은 경우 인스턴스의 JVM 메모리 압력이 높은 것일 수 있습니다. JVM 메모리 압력이 높으면 데이터베이스 성능에 영향을 줍니다. JVM 메모리 압력 수준을 95% 이하로 유지하는 것이 가장 좋습니다. 자세한 내용은 왜 내 Amazon RDS 인스턴스는 충분한 메모리가 있는 데에도 스왑 메모리를 사용합니까?를 참조하세요.

향상된 모니터링

DB 인스턴스 리소스 사용량을 모니터링하려면 향상된 모니터링을 활성화합니다. 그런 다음 1초에서 5초 사이의 세부 수준을 설정합니다(기본값은 60초). 향상된 모니터링을 사용하면 사용 가능한 메모리와 활성 메모리를 실시간으로 모니터링할 수 있습니다.

DB 인스턴스의 스레드를 나열하여 최대 CPU 및 메모리를 사용하는 스레드를 모니터링할 수도 있습니다.

mysql> select THREAD_ID, PROCESSLIST_ID, THREAD_OS_ID from performance_schema.threads;

그런 다음 thread_OS_ID를 thread_ID에 매핑합니다.

select p.* from information_schema.processlist p, performance_schema.threads t 
where p.id=t.processlist_id and t.thread_os_id=<Thread ID from EM processlist>;

사용 가능한 메모리 부족 문제 해결

사용 가능한 메모리 부족 문제가 발생하는 경우 다음 문제 해결 팁을 고려합니다.

  • 쿼리를 실행할 수 있는 충분한 리소스가 데이터베이스에 할당되어 있는지 확인합니다. Amazon RDS의 경우 할당된 리소스의 양은 인스턴스 유형에 따라 다릅니다. 또한 저장 프로시저와 같은 특정 쿼리는 실행되는 동안 무제한의 메모리를 사용할 수 있습니다.
  • 대규모 쿼리를 더 작은 쿼리로 분할하여 장기 실행 트랜잭션을 방지할 수 있습니다.
  • 데이터베이스의 모든 활성 연결 및 쿼리를 보려면 SHOW FULL PROCESSLIST 명령을 사용합니다. JOIN 또는 SORTS 작업을 사용하여 장기 실행 쿼리를 관찰하는 경우 최적화 프로그램이 실행 계획을 계산하기에 충분한 RAM이 있어야 합니다. 또한 임시 테이블이 필요한 쿼리를 식별하는 경우 테이블에 할당할 추가 메모리가 있어야 합니다.
  • 장기 실행 트랜잭션, 메모리 사용률 통계 및 잠금을 보려면 SHOW ENGINE INNODB STATUS 명령을 사용합니다. 출력을 검토하고 버퍼 풀 및 메모리 항목을 확인합니다. BUFFER POOL AND MEMORY 항목은 “Total Memory Allocated”, “Internal Hash Tables” 및 “Buffer Pool Size”와 같이 InnoDB에 대한 메모리 할당에 대한 정보를 제공합니다. InnoDB Status는 래치, 잠금 및 교착 상태에 대한 추가 정보도 제공합니다.
  • 작업 로드에 교착 상태가 자주 발생하는 경우 사용자 정의 파리미터 그룹에서 innodb_lock_wait_timeout 파라미터를 수정합니다. InnoDB는 innodb_lock_wait_timeout 설정을 사용하여 교착 상태가 발생할 때 트랜잭션을 롤백합니다.
  • 데이터베이스 성능을 최적화하려면 쿼리가 제대로 조정되었는지 확인합니다. 그렇지 않으면 성능 문제가 발생하고 대기 시간이 연장될 수 있습니다.
  • Amazon RDS 성능 인사이트를 사용하여 DB 인스턴스를 모니터링하고 문제가 있는 쿼리를 식별할 수 있습니다.
  • 인스턴스가 스로틀되지 않도록 CPU 사용률, IOPS, 메모리 및 스왑 사용량과 같은 Amazon CloudWatch 지표를 모니터링합니다.
  • 사용 가능한 메모리가 95%에 도달할 때 알림을 수신하도록 FreeableMemory 측정치에 대한 CloudWatch 경보를 설정합니다. 인스턴스 메모리의 5% 이상을 여유 공간으로 유지하는 것이 가장 좋습니다.