MySQL 메모리 문제 해결

메모리 부족(OOM) 조건은 프로세스가 메모리를 할당하려고 하지만 서버에 사용 가능한 메모리가 없어서 실패하는 경우에 발생합니다. 그 결과 시스템의 메모리가 매우 부족할 때 Linux 커널에서 사용하는 프로세스인 Linux OOM Killer가 발생하여 데이터베이스 프로세스가 종료됩니다. 

데이터베이스 프로세스가 SIGKILL 지시문으로 종료되어 애플리케이션에서 정상적으로 MySQL을 종료할 수 없으므로 OOM 조건으로 인한 데이터베이스 비정상 종료는 바람직하지 않습니다. 이로 인해 다음과 같은 문제가 발생합니다.

• MySQL 서버가 다운(비정상 종료 복구가 완료되는 동안(몇 분 정도 걸릴 수 있음))되어 사용자와 비즈니스에 영향을 미침

• MySQL 서버에서 버퍼와 캐시를 완전히 준비하여 최대 성능에 도달하는 데 시간이 추가적으로 걸림
• 예상치 못한 비정상 종료로 인한 데이터 손상 가능성

MySQL은 MySQL 메모리 할당을 이해하는 데 도움이 되도록 5.7 버전부터 performance_schema에 메모리 계측을 추가했습니다. 이 문서는 performance_schema를 사용하여 MySQL 메모리 문제를 해결하는 데 도움이 됩니다.

각 Cloud SQL 인스턴스는 호스트 Google Cloud 서버에서 실행되는 가상 머신(VM)으로 구동됩니다. 각 VM은 MySQL 서버와 같은 데이터베이스 프로그램과 로깅 및 모니터링과 같은 지원 서비스를 제공하는 서비스 에이전트를 운영합니다. Cloud SQL은 운영체제(OS)와 서비스 에이전트용으로 메모리 일부를 예약하고 mysqld와 같은 데이터베이스 프로그램에 남은 메모리를 할당합니다. 

다음 표에서는 mysqld 프로세스를 위한 메모리 할당과 각 머신 유형의 기본 InnoDB 버퍼 풀 구성을 보여줍니다.

RAM이 10GB 이상인 인스턴스의 경우 mysqld 프로세스는 인스턴스 RAM 92%만 사용하도록 제한되며 InnoDB 버퍼 풀은 mysqld 프로세스에 제공되는 메모리의 75~80% 범위로 구성됩니다. InnoDB 버퍼 풀 외에도 MySQL은 여러 버퍼와 캐시를 할당하여 성능을 향상시킵니다. MySQL에서의 메모리 할당을 이해하려면 이 문서를 참조하세요.

mysqld 메모리 사용량이 구성된 mysqld 프로세스 한도를 초과하는 경우 mysqld 프로세스가 인스턴스 메모리를 가장 많이 사용하므로 Linux OOM Killer에서 이 프로세스를 종료합니다.

performance_schema는 하위 수준에서 서버 실행을 모니터링하는 MySQL 기능입니다. MySQL용 Cloud SQL을 사용하면 RAM 크기가 3GB 이상인 인스턴스에서 performance_schema를 사용 설정할 수 있습니다. MySQL 8.0 버전부터 RAM이 15GB 이상인 인스턴스에서는 기본적으로 사용 설정됩니다. 

MySQL 5.6 및 5.7 버전에서는 기본적으로 performance_schema가 중지되어 있습니다. 데이터베이스 플래그를 사용하여 사용 설정할 수 있습니다. 

performance_schema를 사용 설정하면 성능 오버헤드가 발생하고 메모리도 영향을 받습니다. 메모리 할당을 이해하려면 성능 스키마 메모리 할당 모델을 참조하세요. 

performance_schema 메모리 계측은 MySQL의 메모리 사용량을 모니터링하는 데 도움이 됩니다. 메모리 계측에는 memory/code_area/instrument_name 형식의 이름이 있습니다. 여기서 code_area는 sql 또는 Innodb와 같은 값이며 instrument_name은 계측 세부정보입니다.

MySQL 5.7은 기본적으로 일부 메모리 계측을 사용 설정하지만 일부는 사용 설정할 수 없습니다. performance_schema.setup_instruments 테이블의 ENABLED 열을 업데이트하여 사용 설정할 수 있습니다. 

mysql> select version();

+-------------------+

| version()         |

+-------------------+

| 5.7.39-google-log |

+-------------------+

1 row in set (0.00 sec)

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES     |       70 |

| NO      |      306 |

+---------+----------+

2 rows in set (0.00 sec)

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

Query OK, 306 rows affected (0.00 sec)

Rows matched: 376  Changed: 306  Warnings: 0

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES     |      376 |

+---------+----------+

1 row in set (0.00 sec)

런타임 시 메모리 계측을 사용 설정하면 계측을 사용 설정한 후 생성된 메모리 객체만 계산됩니다. 따라서 문제 해결을 위해 서버에서 충분한 데이터를 수집할 때까지 기다리는 것이 좋습니다.

MySQL 8.0은 기본적으로 모든 메모리 계측을 사용 설정합니다.

mysql> select version();

+---------------+

| version()     |

+---------------+

| 8.0.28-google |

+---------------+

1 row in set (0.00 sec)

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES     |      490 |

+---------+----------+

1 row in set (0.00 sec)

시스템 스키마에는 performance_schema 데이터를 쉽게 이해할 수 있는 형식으로 요약하는 다양한 뷰가 포함되어 있습니다. 이러한 뷰 대부분은 쌍으로 제공됩니다. 이름은 같으며 둘 중 하나에 x$ 프리픽스가 있습니다. MySQL 5.7 및 8.0에서는 다음과 같은 뷰가 제공되므로 메모리 할당을 서로 다른 수준에서 볼 수 있습니다.

mysql> use sys;

Database changed

mysql> show tables like '%memory%';

+-------------------------------------+

| Tables_in_sys (%memory%)            |

+-------------------------------------+

| memory_by_host_by_current_bytes     |

| memory_by_thread_by_current_bytes   |

| memory_by_user_by_current_bytes     |

| memory_global_by_current_bytes      |

| memory_global_total                 |

| x$memory_by_host_by_current_bytes   |

| x$memory_by_thread_by_current_bytes |

| x$memory_by_user_by_current_bytes   |

| x$memory_global_by_current_bytes    |

| x$memory_global_total               |

+-------------------------------------+

10 rows in set (0.02 sec)

sys.memory_global_total 뷰에는 총 메모리 사용량이 읽을 수 있는 형식으로 요약되어 있습니다.

mysql> select * from sys.memory_global_total;

+-----------------+

| total_allocated |

+-----------------+

| 12.43 GiB       |

+-----------------+

1 row in set (0.44 sec)

sys.x$memory_global_total에는 같은 정보가 바이트 단위로 표시됩니다.

mysql> select * from sys.x$memory_global_total;

+-----------------+

| total_allocated |

+-----------------+

|     13351482992 |

+-----------------+

1 row in set (1.39 sec)

x$ 프리픽스가 없는 뷰는 더 사용자 친화적이고 읽기 쉬운 출력을 제공하기 위한 것입니다. 원시 형식으로 같은 값을 표시하는 x$ 프리픽스가 있는 뷰는 데이터를 자체적으로 처리하는 다른 도구와 함께 사용하기 위한 것입니다.

sys.memory_global_by_current_bytes 뷰에는 할당 유형(event_name)별로 그룹화된 현재 메모리 사용량이 표시됩니다(예: memory/innodb/buf_buf_pool(InnoDB 버퍼 풀)). 기본적으로 행은 사용된 메모리 양의 내림차순으로 정렬됩니다. 

각 할당 유형을 자세히 살펴보기 전에 Innodb, sql, performance_schema 등과 같은 각 코드 영역의 메모리 사용량을 간략히 살펴보는 것이 좋습니다. 

다음 쿼리는 코드 영역별로 현재 할당된 메모리를 집계합니다. 

SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area,

FORMAT_BYTES(SUM(current_alloc)) AS current_alloc

FROM sys.x$memory_global_by_current_bytes

GROUP BY SUBSTRING_INDEX(event_name,'/',2)

ORDER BY SUM(current_alloc) DESC;

+---------------------------+---------------+

| code_area                 | current_alloc |

+---------------------------+---------------+

| memory/innodb             | 11.28 GiB     |

| memory/performance_schema | 520.02 MiB    |

| memory/temptable          | 514.00 MiB    |

| memory/sql                | 106.12 MiB    |

| memory/mysys              | 43.25 MiB     |

| memory/vio                | 372.15 KiB    |

| memory/myisam             |  696 bytes    |

| memory/csv                |   88 bytes    |

| memory/blackhole          |   88 bytes    |

+---------------------------+---------------+

9 rows in set (1.02 sec)

이 출력에서는 innodb가 가장 많은 메모리를 소비하고 있으며 그 다음으로 performance_schema 및 temporary 테이블 순입니다.

이제 sys.memory_global_by_current_bytes를 쿼리하면 event_name 열에 설명된 대로 MySQL 내부 구조의 메모리 사용량이 바로 표시됩니다. 메모리의 비정상적인 증가 문제를 해결하는 경우 이 뷰의 출력을 확인하여 소스를 식별할 수 있습니다.

mysql> select event_name,current_alloc,high_alloc from sys.memory_global_by_current_bytes limit 5;

+-------------------------------+---------------+------------+

| event_name                    | current_alloc | high_alloc |

+-------------------------------+---------------+------------+

| memory/innodb/buf_buf_pool    | 10.72 GiB     | 10.72 GiB  |

| memory/temptable/physical_ram | 514.00 MiB    | 515.00 MiB |

| memory/innodb/hash0hash       | 292.69 MiB    | 292.69 MiB |

| memory/innodb/memory          | 191.77 MiB    | 197.94 MiB |

| memory/sql/TABLE              | 60.39 MiB     | 62.35 MiB  |

+-------------------------------+---------------+------------+

5 rows in set (0.45 sec)

다음은 각 할당 유형에 대한 세부정보입니다.

이 뷰에는 사용자별로 그룹화된 메모리 사용량이 요약되어 있습니다. 사용자가 연결을 여러 개 사용하는 경우 사용자의 모든 인스턴스에 대한 메모리 사용량이 합산됩니다.

mysql> select user,current_allocated from memory_by_user_by_current_bytes;

+-----------------+-------------------+

| user            | current_allocated |

+-----------------+-------------------+

| sbtest          | 60.30 MiB         |

| background      | 2.68 MiB          |

| root            | 1.52 MiB          |

| event_scheduler | 16.38 KiB         |

+-----------------+-------------------+

4 rows in set (1.16 sec)

애플리케이션/작업마다 다른 사용자를 사용하는 경우(권장) 이 뷰를 사용하면 특정 애플리케이션이나 작업에서 많은 메모리를 사용하고 있는지 확인할 수 있습니다.

이 뷰에는 호스트 이름별로 그룹화된 메모리 사용량이 요약되어 있습니다.

mysql> select host,current_allocated from memory_by_host_by_current_bytes;

+------------+-------------------+

| host       | current_allocated |

+------------+-------------------+

| 10.128.0.8 | 62.10 MiB         |

| background | 2.70 MiB          |

| 127.0.0.1  | 311.01 KiB        |

| localhost  | 16.38 KiB         |

+------------+-------------------+

4 rows in set (1.58 sec)

이 뷰는 클라이언트 호스트별 메모리 사용량을 식별하는 데 유용합니다. 요즘에는 여러 클라이언트/애플리케이션 서버가 매우 일반적이며 이 뷰는 특정 클라이언트나 워크로드로 인해 메모리 사용량이 증가하는지 확인하는 데 도움이 됩니다. 또한 먼저 클라이언트/애플리케이션 서버 하나에 변경사항을 배포하고 메모리 사용량을 다른 클라이언트 호스트와 비교하여 메모리에 미치는 영향을 모니터링한 후에 정보에 입각한 결정을 내릴 수 있습니다.

이 뷰에는 스레드별로 그룹화된 메모리 사용량이 요약되어 있으며 이 뷰를 사용하면 메모리를 가장 많이 사용하는 스레드를 찾을 수 있습니다.

mysql> select thread_id,user,current_allocated from sys.memory_by_thread_by_current_bytes limit 5;

+-----------+-------------------+-------------------+

| thread_id | user              | current_allocated |

+-----------+-------------------+-------------------+

|     12999 | sbtest@10.128.0.8 | 3.80 MiB          |

|     12888 | sbtest@10.128.0.8 | 3.75 MiB          |

|     12760 | sbtest@10.128.0.8 | 3.68 MiB          |

|     13128 | sbtest@10.128.0.8 | 3.67 MiB          |

|     13221 | sbtest@10.128.0.8 | 3.47 MiB          |

+-----------+-------------------+-------------------+

5 rows in set (2.29 sec)

특정 스레드에서 많은 메모리를 소비하는 것이 발견되면 thread_id를 사용하여 MySQL 느린 쿼리 로그나 일반 로그를 필터링하고 메모리를 증가시킨 세션/쿼리를 식별할 수 있습니다.

높은 메모리 사용량 문제를 해결하는 것은 까다로운 태스크입니다. 다행히 사용자가 performance_schema 메모리 계측을 사용하면 내부 MySQL 구조에서 메모리를 사용하는 방식을 이해할 수 있습니다. 시스템 스키마 뷰를 사용하면 정보를 더 쉽게 해석할 수 있습니다. 메모리 관련 문제가 발생하는 경우 이 기능을 사용하면 문제의 원인을 찾고 구성/쿼리를 변경하여 문제를 해결할 수 있습니다. 

예를 들면 다음과 같습니다.

1. 내부 임시 테이블에 많은 양의 메모리가 필요한 경우 tmp_table_size를 사용하여 임시 테이블 크기를 제한하거나 innodb_buffer_pool_size를 줄여 세션당 버퍼 공간을 확보할 수 있습니다.
2. 특정 스레드에서 메모리를 많이 소비하는 경우 thread_id를 사용하여 느린 쿼리 로그를 필터링하여 문제가 있는 쿼리를 식별하고 조정하여 해당 스레드/세션의 메모리 사용량을 줄일 수 있습니다.

MySQL 인스턴스에서 필요한 양보다 많은 메모리를 사용하고 서버에서 메모리가 부족해질 때까지 메모리 사용량이 지속적으로 증가하는 상황이 발생하고 performance_schema에서 총 메모리와 계측 메모리 간의 차이를 보여주거나 메모리가 사용되는 위치를 알려주지 않는 경우 이는 메모리 누수 증상일 수 있습니다. 메모리 누수가 의심되면 다음 단계를 수행합니다.

• MySQL 구성을 신중하게 검토하고 과도하게 할당된 버퍼/캐시가 없는지 확인합니다. 이 문서는 MySQL에서 다양한 버퍼/캐시에 메모리를 할당하는 방법을 이해하는 데 도움이 됩니다.
• 메모리 문제를 일으키는 기능/구성/쿼리를 파악하고 문제를 재현해 봅니다.
• 같은 시리즈의 다음 부 버전의 MySQL 출시 노트를 검토하여 유사한 동작이 있는 버그가 수정되었는지 확인합니다. 
• MySQL을 최신 부 버전으로 업그레이드하여 메모리 문제가 해결되는지 확인합니다.
• MySQL 버그 데이터베이스를 검색하여 다른 커뮤니티 사용자가 유사한 문제를 보고했는지 확인합니다.
• 같은 문제에 대한 기존 버그가 없으면 재현 가능한 테스트 사례로 새 버그를 만듭니다.