MySQL의 메모리 사용 방식

MySQL은 서버 시작 시 전역 버퍼를 할당하며, 이는 모든 연결에 공유됩니다. MySQL 메모리의 대부분은 innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size 등의 전역 버퍼에서 사용됩니다.

InnoDB 버퍼 풀은 일반적으로 MySQL 인스턴스에서 가장 큰 메모리를 사용하며, innodb_buffer_pool_size 매개변수를 사용하여 구성됩니다. 테이블 데이터 및 색인, 변경 버퍼, 적응형 해시 색인, 기타 내부 구조체를 캐시하는 데 사용됩니다. Google Cloud의 관리형 MySQL 서비스인 MySQL용 Cloud SQL은 인스턴스 크기에 따라 innodb_buffer_pool_size를 인스턴스 메모리의 최대 72%까지 구성합니다.

InnoDB는 버퍼 및 연관된 데이터 구조체의 추가 메모리를 예약합니다. 총 할당된 메모리는 지정된 버퍼 풀 크기보다 약 10% 더 큽니다. show engine innodb status\G 출력에서 InnoDB 버퍼 풀 메모리 사용량을 확인할 수 있습니다.

mysql> show engine innodb status\G

…

----------------------

BUFFER POOL AND MEMORY

----------------------

Total large memory allocated 11511349248

…

InnoDB 로그 버퍼는 디스크상의 InnoDB redo 로그 파일에 기록할 변경사항을 보존하기 위해 사용됩니다. innodb_log_buffer_size를 사용하여 구성됩니다. MySQL 커뮤니티 기본값은 16MB이며 MySQL용 Cloud SQL도 같은 값을 사용합니다.

키 버퍼는 MySQL이 메모리에 MyISAM 색인을 캐시하기 위해 사용됩니다. key_buffer_size를 사용하여 구성됩니다. MySQL 커뮤니티 기본값은 8MB이며 MySQL용 Cloud SQL도 같은 값을 사용합니다. MySQL용 Cloud SQL은 MyISAM 테이블을 지원하지 않으므로 기본값으로 두면 됩니다.

query_cache_size 구성 변수는 쿼리 결과를 캐시하기 위해 할당되는 메모리의 양을 정의합니다. MySQL Community 5.7 및 MySQL용 Cloud SQL 5.7에서는 기본적으로 사용 중지되어 있습니다.

쿼리 캐시는 심각한 확장성 문제를 야기하는 것으로 알려져 있으므로 MySQL 5.7.20에서 지원 중단되고 MySQL 8.0에서 삭제되었습니다. 아직 MySQL 5.7 인스턴스에 사용 중이면 워크로드에 정말 유용한지 확인해 보세요. 자세한 내용은 이 블로그를 참조하세요.

MySQL은 모든 연결에 공유되는 전역 캐시를 할당합니다. 이러한 캐시는 동적으로 할당되며, 구성 변수가 최대 한도를 정의합니다.

MySQL은 테이블 캐시를 사용하여 테이블 열기 속도를 높입니다. MySQL 테이블 캐시는 열린 테이블 캐시와 테이블 정의 캐시의 두 부분으로 구분되며, 각각 table_open_cache와 table_definition_cache로 구성됩니다. 

table_open_cache는 모든 연결된 스레드에 따라 열린 테이블의 파일 설명자를 저장하는 메모리 캐시입니다. 이 값을 늘리면 MySQL 서버로 알려진 mysqld 프로그램에 필요한 파일 설명자 수가 증가합니다. 운영체제에서 table_open_cache 설정에 명시된 열린 파일 설명자의 수를 처리할 수 있는지 확인하세요.

여러 클라이언트 세션이 지정된 테이블에 동시에 액세스할 수 있고 테이블은 각각의 동시 클라이언트 세션에 따라 개별적으로 열립니다. 이러한 이유로 열려 있는 테이블 수가 서버에 있는 테이블 수보다 많게 표시될 수 있습니다. 테이블 캐시가 가득 차면 서버에서 최근 가장 적게 사용되는 테이블부터 시작하여 현재 사용하지 않는 테이블을 해제합니다.

table_definition_cache는 테이블 정의를 저장할 메모리 캐시입니다. 전역적이며 모든 연결에서 공유됩니다.

MySQL 5.7용 CloudSQL은 table_open_cache 및 table_definition_cache의 기본값으로 2000 및 1400을 사용합니다.

MySQL 8.0용 CloudSQL은 table_open_cache 및 table_definition_cache의 기본값으로 4000 및 2000을 사용합니다.

각 클라이언트 연결에 대해 MySQL은 모든 쿼리를 실행하고 클라이언트의 연결이 해제될 때까지 클라이언트에 결과를 다시 반환하는 전용 스레드를 할당합니다. MySQL이 스레드를 캐시하므로 각 연결에 대해 스레드를 만들고 삭제할 필요가 없습니다. 스레드 캐시의 스레드 수는 thread_cache_size 변수를 사용하여 구성됩니다.

MySQL용 CloudSQL은 thread_cache_size 기본값으로 48을 사용합니다.

InnoDB에는 테이블 정의를 저장하기 위한 자체 캐시가 있으며, 이는 테이블 열기 캐시 및 테이블 정의 캐시와는 다릅니다. show engine innodb status\G 출력에서 InnoDB 데이터 사전용으로 할당된 메모리를 확인할 수 있습니다.

----------------------

BUFFER POOL AND MEMORY

----------------------

…

Dictionary memory allocated 65816817

table_definition_cache 설정은 InnoDB 데이터 사전 캐시의 테이블 인스턴스 수에 대한 소프트 한도를 설정합니다. InnoDB 데이터 사전 캐시의 테이블 인스턴스 수가 table_definition_cache 한도를 초과하면 LRU 메커니즘이 테이블 인스턴스를 제거 대상으로 표시하기 시작하고 종국에는 이 캐시에서 해상 인스턴스를 삭제합니다.

InnoDB 데이터 사전 캐시의 테이블 인스턴스 수가 항상 table_definition_cache 한도보다 적다는 의미인가요? 그렇지 않습니다. 외래 키 관계가 있는 테이블 인스턴스는 LRU 목록에 배치되지 않습니다. 해당 인스턴스는 캐시된 상태로 유지되며 테이블 인스턴스가 table_definition_cache 한도를 초과하여 추가 메모리 사용량으로 이어집니다. 외래 키 관계가 있는 테이블에서 사용하는 메모리는 MySQL 종료/다시 시작 이벤트 시에만 해제됩니다. 이 문제는 MySQL 5.7 및 8.0에 모두 발생하며 알려진 버그가 있습니다.

MySQL 인스턴스에 외래 키 관계가 있는 테이블이 많이 있는 경우 InnoDB 데이터 사전 캐시는 수 GB의 메모리를 사용할 수 있습니다. 이는 MySQL 버퍼/캐시를 구성하는 동안 간과되는 경우가 많으며 예기치 않은 높은 메모리 사용량 또는 메모리 부족(OOM) 문제의 이유 중 하나일 수 있습니다.

InnoDB 데이터 사전 캐시로 4.16GB를 사용하는 작은 인스턴스를 보여주는 샘플 출력입니다.

$ mysql -e "show engine innodb status\G" | grep -i memory

BUFFER POOL AND MEMORY

Total large memory allocated 7696023552

Dictionary memory allocated 4465193358

메모리를 소비하는 MySQL의 다른 기능은 세션 버퍼입니다. 이러한 버퍼는 세션별로 할당되며 경우에 따라서는 버퍼의 여러 쿼리가 단일 쿼리(특히 join_buffer_size)에 할당될 수 있습니다.

• sort_buffer_size 
• join_buffer_size
• read_buffer_size
• read_rnd_buffer_size

이러한 버퍼는 쿼리에 정렬, 조인, 색인/전체 테이블 검색 등에 대한 버퍼가 필요한 경우에만 할당되지만 매우 적게 필요한 경우라도 전체 크기로 할당됩니다. 이러한 버퍼를 높은 값으로 설정하면 메모리 낭비가 발생할 수 있습니다.

기본값은 MySQL 커뮤니티 및 MySQL용 Cloud SQL과 동일합니다.

MySQL은 바이너리 로그 캐시를 사용하여 트랜잭션이 실행되는 동안 바이너리 로그의 변경사항을 보존합니다. binlog_cache_size를 사용하여 구성됩니다. 바이너리 로깅이 사용 설정(log_bin=ON)된 경우 각 클라이언트에 할당됩니다.

binlog_cache_size의 기본값은 MySQL Community 및 MySQL용 Cloud SQL과 동일합니다.

MySQL은 GROUP BY, ORDER BY, DISTINCT, UNION과 같은 쿼리 유형을 처리하는 동안 중간 결과를 저장하기 위해 내부 임시 테이블을 만듭니다. 이러한 내부 임시 테이블은 메모리에 먼저 생성된 후 최대 크기에 도달했을 때 디스크 상의 테이블로 변환됩니다. 내부 임시 테이블의 최대 크기는 tmp_table_size 및 max_heap_table_size 변수의 최솟값으로 결정됩니다.

tmp_table_size 및 max_heap_table_size의 기본값은 MySQL 커뮤니티 및 MySQL용 Cloud SQL과 동일합니다.

참고: 세션별 버퍼와 메모리 내 임시 테이블은 연결마다 메모리를 따로 할당하므로 연결 수가 많은 경우 전체 메모리 사용량이 매우 높을 수 있습니다. 이러한 값을 너무 높게 설정하지 않고 실험을 통해 워크로드의 최적 값을 찾는 것이 좋습니다.

모든 스레드에서는 클라이언트 연결을 관리하는 데 메모리를 거의 요구하지 않습니다. 다음 변수는 크기를 제어합니다.

• thread_stack: 각 스레드의 스택 크기로, 기본값은 256KB입니다.
• net_buffer_length: 각 클라이언트는 연결 버퍼 및 net_buffer_length의 결과 버퍼와 연결됩니다. 추가로 max_allowed_packet 크기까지 늘릴 수 있습니다.

performance_schema를 사용 설정하면 하위 수준에서 MySQL 서버 실행을 모니터링하는 데 도움이 됩니다. Performance_schema는 메모리를 동적으로 할당하며 MySQL 종료 또는 다시 시작 시에만 해제됩니다.

MySQL용 Cloud SQL을 사용하면 RAM 크기가 15GB 이상인 인스턴스에서 performance_schema를 사용 설정할 수 있습니다. performance_schema는 기본적으로 MySQL 8.0.26 버전부터 사용 설정됩니다. Performance_schema는 기본적으로 MySQL 5.6, 5.7, 8.0.18 버전에서 중지되며 데이터베이스 플래그를 통해 사용 설정될 수 있습니다.

MySQL용 Cloud SQL은 우수한 성능을 즉시 사용할 수 있도록 메모리 관련 매개변수를 자동으로 구성합니다. 여러 테이블 또는 연결을 지원하기 위해 워크로드에 더 큰 캐시가 필요한 경우 인스턴스에서 메모리 부족 문제가 계속 발생할 수 있습니다. 세션 버퍼를 동시에 할당하는 많은 스레드에서도 동시에 메모리 관련 문제가 발생할 수 있습니다. 전역 버퍼/캐시에 할당되는 메모리가 많을수록 연결과 세션 버퍼에서 사용할 수 있는 메모리도 줄어듭니다(그 반대도 동일). 따라서 적절한 균형을 찾는 것이 중요합니다.

워크로드에 더 큰 캐시/세션 버퍼가 필요한 경우 innodb_buffer_pool_size를 사용하여 전역 버퍼 크기를 줄이면 됩니다. 데이터베이스 플래그를 사용하여 MySQL용 Cloud SQL 인스턴스의 구성 매개변수를 변경할 수 있습니다. 메모리 부족 또는 성능 문제가 계속 발생하면 인스턴스 크기를 업그레이드하여 메모리를 늘리면 됩니다.