Azure Database for MySQL - 유연한 서버의 메모리 부족 문제 해결

Azure Database for MySQL 유연한 서버 인스턴스가 최적으로 수행되도록 하려면 적절한 메모리 할당 및 사용률을 갖는 것이 매우 중요합니다. 기본적으로 Azure Database for MySQL 유연한 서버의 인스턴스를 만들 때 사용 가능한 실제 메모리는 워크로드에 대해 선택한 계층 및 크기에 따라 달라집니다. 또한 메모리는 데이터베이스 작업을 개선하기 위해 버퍼 및 캐시에 할당됩니다. 자세한 내용은 MySQL에서 메모리를 사용하는 방법을 참조하세요.

Azure Database for MySQL 유연한 서버는 가능한 한 많은 캐시 적중을 달성하기 위해 메모리를 사용합니다. 따라서 메모리 사용률은 인스턴스의 사용 가능한 실제 메모리의 80~90% 사이를 오갈 수 있습니다. 쿼리 워크로드의 진행에 문제가 없는 한 괜찮습니다. 그러나 다음과 같은 이유로 메모리 부족 문제가 발생할 수 있습니다.

• 너무 큰 버퍼를 구성했습니다.
• 최적이 아닌 쿼리를 실행 중입니다.
• 큰 데이터 세트를 조인하고 정렬하는 쿼리가 있습니다.
• 데이터베이스 서버의 최대 연결 수를 너무 높게 설정했습니다.

서버 메모리의 대부분은 Innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size 및 query_cache_size 같은 구성 요소를 포함하는 InnoDB의 전역 버퍼 및 캐시에서 사용됩니다.

innodb_buffer_pool_size 매개 변수의 값은 InnoDB가 데이터베이스 테이블 및 인덱스 관련 데이터를 캐시하는 메모리 영역을 지정합니다. MySQL은 버퍼 풀의 테이블 및 인덱스 관련 데이터를 최대한 많이 수용하려고 합니다. 버퍼 풀이 클수록 디스크로 전환되는 I/O 작업이 적어야 합니다.

메모리 사용 모니터링

Azure Database for MySQL 유연한 서버는 데이터베이스 인스턴스의 성능을 측정하는 다양한 메트릭을 제공합니다. 데이터베이스 서버의 메모리 사용률을 보다 정확하게 이해하려면 호스트 메모리 백분율 또는 메모리 백분율 메트릭을 확인합니다.

메모리 사용률이 갑자기 증가하고 사용 가능한 메모리가 빠르게 감소하는 경우 호스트 CPU 백분율, 총 연결 수 및 IO 백분율과 같은 다른 메트릭을 모니터링하여 워크로드의 급증이 문제의 원인인지 확인합니다.

데이터베이스 서버와 설정된 각 연결에는 약간의 메모리를 할당해야 합니다. 따라서 데이터베이스 연결이 급증하면 메모리 부족이 발생할 수 있습니다.

메모리 사용률이 높은 이유

MySQL에서 메모리 사용률이 높은 몇 가지 원인을 좀 더 살펴보겠습니다. 이러한 원인은 워크로드의 특성에 따라 달라집니다.

임시 테이블의 증가

MySQL은 임시 결과 집합을 저장하도록 설계된 특수 형식의 테이블인 "임시 테이블"을 사용합니다. 임시 테이블은 세션 중에 여러 번 다시 사용할 수 있습니다. 생성된 임시 테이블은 세션에 로컬이므로 세션마다 다른 임시 테이블이 있을 수 있습니다. 대규모 임시 결과 세트의 컴파일을 수행하는 많은 세션이 있는 프로덕션 시스템에서는 피크 타임에 생성되는 임시 테이블 수를 추적하는 created_tmp_tables 전역 상태 카운터를 정기적으로 확인해야 합니다. 메모리 내 임시 테이블 수가 많을 경우 Azure Database for MySQL 유연한 서버 인스턴스에서 사용 가능한 메모리가 짧아질 수 있습니다.

MySQL을 사용하는 경우 임시 테이블 크기는 다음 표에 설명된 두 가지 매개 변수의 값에 따라 결정됩니다.

권장 사항

임시 테이블과 관련된 메모리 부족 문제를 해결하려면 다음 권장 사항을 고려합니다.

• tmp_table_size 값을 늘리기 전에, 먼저 데이터베이스가 올바르게 인덱싱되었는지 확인합니다. 특히 조인과 관련이 있고 작업별로 그룹화된 열에 대해 데이터베이스가 올바르게 인덱싱되었는지 확인합니다. 기본 테이블에서 적절한 인덱스를 사용하면 생성되는 임시 테이블 수가 제한됩니다. 인덱스를 확인하지 않고 이 매개 변수와 max_heap_table_size 매개 변수의 값을 늘리면 비효율적인 쿼리를 인덱스 없이 실행하여 임시 테이블이 필요 이상으로 많이 만들어질 수 있습니다.
• max_heap_table_size 및 tmp_table_size 매개 변수의 값을 튜닝하여 워크로드 요구 사항을 해결합니다.
• max_heap_table_size 및 tmp_table_size 매개 변수에 대해 설정한 값이 너무 낮으면 임시 테이블이 정기적으로 스토리지로 분산되어 쿼리에 대기 시간이 추가될 수 있습니다. created_tmp_disk_tables 전역 상태 카운터를 사용하여 디스크로 분할된 임시 테이블을 추적할 수 있습니다. created_tmp_disk_tables 변수 값과 created_tmp_tables 변수 값을 비교하면 생성된 내부 디스크 내 임시 테이블의 수와 생성된 내부 임시 테이블의 총 수를 볼 수 있습니다.

테이블 캐시

다중 스레드 시스템인 MySQL은 여러 세션에서 동시에 테이블을 열 수 있도록 테이블 파일 설명자의 캐시를 유지 관리합니다. MySQL은 약간의 메모리와 OS 파일 설명자를 사용하여 이 테이블 캐시를 유지 관리합니다. table_open_cache 변수는 테이블 캐시의 크기를 정의합니다.

권장 사항

케이블 캐시와 관련된 메모리 부족 문제를 해결하려면 다음 권장 사항을 고려합니다.

• table_open_cache 매개 변수는 모든 스레드에 대해 열려 있는 테이블 수를 지정합니다. 이 값을 늘리면 mysqld에 필요한 파일 설명자 수가 증가합니다. 전역 상태 표시 카운터에서 opened_tables 상태 변수를 확인하여 테이블 캐시를 늘려야 하는지 확인할 수 있습니다. 워크로드를 수용할 수 있도록 이 매개 변수를 점진적으로 늘립니다.
• table_open_cache 너무 낮게 설정하면 Azure Database for MySQL 유연한 서버가 쿼리 처리에 필요한 테이블을 열고 닫는 데 더 많은 시간을 할애할 수 있습니다.
• 이 값을 너무 높게 설정하면 더 많은 메모리를 사용하고 파일 설명자를 실행하는 운영 체제에서 연결이 거부되거나 쿼리를 처리하지 못할 수 있습니다.

다른 버퍼 및 쿼리 캐시

메모리 부족과 관련된 문제를 해결할 때 몇 가지 버퍼와 캐시를 사용하여 문제를 해결할 수 있습니다.

net 버퍼(net_buffer_length)

net 버퍼는 각 클라이언트 스레드에 대한 연결 및 스레드 버퍼의 크기이며 max_allowed_packet에 지정된 값으로 높일 수 있습니다. 예를 들어 쿼리 문이 큰 경우 모든 삽입/업데이트의 값이 크면 net_buffer_length 매개 변수의 값을 늘리면 성능 향상에 도움이 됩니다.

조인 버퍼(join_buffer_size)

조인 버퍼는 조인이 인덱스를 사용할 수 없는 경우 테이블 행을 캐시하기 위해 할당됩니다. 데이터베이스에서 인덱스 없이 많은 조인이 수행된 경우 더 빠른 조인을 위해 인덱스를 추가하는 것이 좋습니다. 인덱스를 추가할 수 없는 경우 연결당 할당된 메모리 양을 지정하는 join_buffer_size 매개 변수의 값을 늘리는 것이 좋습니다.

정렬 버퍼(sort_buffer_size)

정렬 버퍼는 일부 ORDER BY 및 GROUP BY 쿼리에 대해 정렬을 수행하는 데 사용됩니다. SHOW GLOBAL STATUS 출력에 초당 많은 Sort_merge_passes 표시되는 경우 쿼리 최적화 또는 더 나은 인덱싱을 사용하여 개선할 수 없는 ORDER BY 또는 GROUP BY 작업의 속도를 높이기 위해 sort_buffer_size 값을 늘리는 것이 좋습니다.

관련 정보가 없으면 sort_buffer_size 값을 임의로 늘리지 마세요. 이 버퍼의 메모리는 연결마다 할당됩니다. MySQL 설명서의 서버 시스템 변수 문서에서는 Linux의 임계값이 256KB와 2MB 두 가지이며, 더 큰 값을 사용하면 메모리 할당 속도가 크게 느려질 수 있다고 설명되어 있습니다. 따라서 sort_buffer_size 값을 2M보다 큰 값으로 늘리면 성능 저하가 이점보다 크기 때문에 늘리지 말아야 합니다.

쿼리 캐시(query_cache_size)

쿼리 캐시는 쿼리 결과 세트를 캐싱하는 데 사용되는 메모리 영역입니다. query_cache_size 매개 변수는 쿼리 결과 캐싱에 할당되는 메모리 양을 결정합니다. 쿼리 캐시는 기본적으로 사용되지 않습니다. 또한 쿼리 캐시는 MySQL 버전 5.7.20에서 더 이상 사용되지 않으며 MySQL 버전 8.0에서 제거되었습니다. 현재 솔루션에서 쿼리 캐시를 사용하도록 설정한 경우 사용하지 않도록 설정하기 전에 쿼리 캐시를 사용하는 쿼리가 없는지 확인합니다.

버퍼 캐시 적중률 계산

버퍼 캐시 적중 비율은 Azure Database for MySQL 유연한 서버 환경에서 버퍼 풀이 워크로드 요청을 수용할 수 있는지 여부를 파악하는 데 중요하며, 일반적으로 일반적으로 버퍼 풀 캐시 적중률이 99%를 초과하는 것이 좋습니다.

읽기 요청에 대한 InnoDB 버퍼 풀 적중 비율을 계산하려면 SHOW GLOBAL STATUS를 실행하여 카운터 "Innodb_buffer_pool_read_requests" 및 "Innodb_buffer_pool_reads"을 검색한 다음 아래 표시된 수식을 사용하여 값을 계산할 수 있습니다.

InnoDB Buffer pool hit ratio = Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100

다음 예제를 고려해 보세요.

show global status like "innodb_buffer_pool_reads";
+--------------------------+-------+
| Variable_name | Value |
| +--------------------------+-------+ |
| Innodb_buffer_pool_reads | 197 |
| +--------------------------+-------+ |
| 1 row in set (0.00 sec) |

show global status like "innodb_buffer_pool_read_requests";
+----------------------------------+----------+
| Variable_name | Value |
| +----------------------------------+----------+ |
| Innodb_buffer_pool_read_requests | 22479167 |
| +----------------------------------+----------+ |
| 1 row in set (0.00 sec) |

위의 값을 사용하여 읽기 요청에 대한 InnoDB 버퍼 풀 적중율을 계산하면 다음과 같은 결과가 나옵니다.

InnoDB Buffer pool hit ratio = 22479167/(22479167+197) * 100
Buffer hit ratio = 99.99%

select 문 버퍼 캐시 적중률 외에도, DML 문의 경우 InnoDB 버퍼 풀에 대한 쓰기는 백그라운드에서 발생합니다. 그러나 페이지를 읽거나 만들어야 하는데 클린 페이지를 사용할 수 없는 경우 페이지가 먼저 플러시될 때까지 기다려야 합니다.

Innodb_buffer_pool_wait_free 카운터는 이 작업이 발생한 횟수를 계산합니다. Innodb_buffer_pool_wait_free가 0보다 크면 InnoDB 버퍼 풀이 너무 작으므로 데이터베이스에 들어오는 쓰기를 수용하려면 버퍼 풀 크기 또는 인스턴스 크기를 늘려야 한다는 강력한 지표입니다.

권장 사항

• 데이터베이스에 쿼리를 실행하기에 충분한 리소스가 할당되어 있는지 확인합니다. 경우에 따라 더 많은 실제 메모리를 얻기 위해 인스턴스 크기를 확장해야 워크로드를 수용하기 위해 버퍼와 캐시가 필요할 수 있습니다.
• 여러 개의 작은 트랜잭션으로 분할하여 큰 트랜잭션 또는 장기 실행 트랜잭션을 방지하세요.
• 시스템에서 지정된 임계값을 초과하는 경우 알림을 받을 수 있도록 경고 "호스트 메모리 비율"을 사용합니다.
• Query Performance Insights 또는 Azure Workbooks를 사용하여 문제가 있거나 느리게 실행되는 쿼리를 식별한 다음, 최적화합니다.
• 프로덕션 데이터베이스 서버의 경우 모든 것이 원활하게 실행되고 있는지 확인하기 위해 정기적으로 진단을 수집합니다. 그렇지 않은 경우 식별한 문제를 해결합니다.

다음 단계