Amazon RDS for MySQL 또는 MariaDB 인스턴스가 스토리지 가득 참으로 표시되는 이유는 무엇인가요?

간략한 설명

스토리지 가득 참 문제를 해결하려면 먼저 DB 인스턴스에서 사용되는 총공간을 분석해야 합니다. DB 인스턴스의 공간은 다음과 같은 용도로 사용됩니다.

• 사용자 생성 데이터베이스
• 임시 테이블
• 이진 로그 또는 MySQL 스탠바이 인스턴스 릴레이 로그(읽기 전용 복제본을 사용하는 경우)
• InnoDB 테이블스페이스
• 일반 로그, 느린 쿼리 로그 및 오류 로그

무엇이 스토리지 공간을 사용하고 있는지 식별한 후 스토리지 공간을 회수할 수 있습니다. 그런 다음 FreeStorageSpace 지표를 모니터링하여 공간이 다시 부족해지는 것을 방지하세요.

**참고:가용 스토리지가 갑자기 감소하는 경우 ** SHOW FULL PROCESSLIST 명령을 실행하여 DB 인스턴스 수준에서 진행 중인 쿼리를 확인하세요. SHOW FULL PROCESSLIST 명령은 각 연결에서 수행하는 모든 활성 연결 및 쿼리에 대한 정보를 제공합니다. 오랜 시간 동안 활성 상태인 트랜잭션을 검토하려면 INFORMATION_SCHEMA.INNODB_TRX 또는 SHOW ENGINE INNODB STATUS 명령을 실행하세요. 그런 다음 출력을 검토하세요.

해결 방법

DB 인스턴스(사용자 생성 데이터베이스)에서 사용되는 전체 공간 분석

각 사용자 생성 데이터베이스의 크기를 확인하려면 다음 쿼리를 실행하세요.

mysql> SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024/1024,2) "size in GB" FROM information_schema.tables GROUP BY 1 ORDER BY 2 DESC;

특정 데이터베이스(DB 인스턴스 내)의 각 테이블 크기를 확인하려면 다음 쿼리를 실행하세요.

mysql> SELECT table_schema "DB Name", table_name,(data_length + index_length)/1024/1024/1024 AS "TableSizeinGB" from information_schema.tables where table_schema='database_name';

MySQL 버전 5.7 이상 또는 MySQL 8.0 이상에서 더 정확한 테이블 크기를 가져오려면 다음 쿼리를 사용하세요.
**참고:**information_schema.files 쿼리는 MariaDB 엔진에 적용할 수 없습니다.

mysql> SELECT file_name, ROUND(SUM(total_extents * extent_size)/1024/1024/1024,2) AS "TableSizeinGB" from information_schema.files where file_name like '%/database_name/%';

전체 스토리지 세부 정보를 얻고 데이터베이스 수준 및 테이블 수준에서 조각화된 공간을 대략적으로 알아보려면 다음 쿼리를 실행하세요.
참고: 이 쿼리는 공유 테이블스페이스에 있는 테이블에는 적용되지 않습니다.

mysql> SELECT table_schema AS "DB_NAME", SUM(size) "DB_SIZE", SUM(fragmented_space) APPROXIMATED_FRAGMENTED_SPACE_GB FROM (SELECT table_schema, table_name, ROUND((data_length+index_length+data_free)/1024/1024/1024,2) AS size, ROUND((data_length - (AVG_ROW_LENGTH*TABLE_ROWS))/1024/1024/1024,2)
    AS fragmented_space FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema NOT IN ('performance_schema', 'mysql', 'information_schema') ) AS TEMP GROUP BY DB_NAME ORDER BY APPROXIMATED_FRAGMENTED_SPACE_GB DESC;

mysql> SELECT table_schema DB_NAME, table_name TABLE_NAME, ROUND((data_length+index_length+data_free)/1024/1024/1024,2) SIZE_GB, ROUND((data_length - (AVG_ROW_LENGTH*TABLE_ROWS))/1024/1024/1024,2) APPROXIMATED_FRAGMENTED_SPACE_GB from information_schema.tables
    WHERE table_type='BASE TABLE' AND table_schema NOT IN ('performance_schema', 'mysql', 'information_schema') ORDER BY APPROXIMATED_FRAGMENTED_SPACE_GB DESC;

이 두 쿼리에서 얻은 데이터베이스 크기를 기록하고 Amazon RDS의 Amazon CloudWatch 지표와 비교하세요. 그러면 데이터 사용량 때문에 전체 스토리지가 발생했는지 확인할 수 있습니다.

임시 테이블

InnoDB 사용자 생성 임시 테이블과 디스크상의 내부 임시 테이블은 ibtmp1이라는 임시 테이블스페이스 파일에 생성됩니다. 경우에 따라 임시 테이블스페이스 파일이 MySQL 데이터 디렉터리의 ibtmp2까지 확장될 수도 있습니다.

**팁:**임시 테이블(ibtmp1) 이 과도한 스토리지를 사용하는 경우 DB 인스턴스를 재부팅하여 공간을 확보하세요.

온라인 DDL 작업은 다음과 같은 작업에 임시 로그 파일을 사용합니다.

• 동시 DML 기록
• 인덱스 생성 시 임시 정렬 파일 생성
• 테이블 재구축 시 임시 테이블이 스토리지를 차지할 수 있도록 임시 중간 테이블 파일 생성

**참고:**InnoDB 테이블스페이스의 파일 크기는 MySQL 버전 5.7 이상 또는 MySQL 8.0 이상을 사용해야만 쿼리할 수 있습니다.

InnoDB 임시 테이블스페이스를 찾으려면 다음 쿼리를 실행하세요.

mysql> SELECT file_name, tablespace_name, table_name, engine, index_length, total_extents, extent_size from information_schema.files WHERE file_name LIKE '%ibtmp%';

글로벌 임시 테이블스페이스 데이터 파일이 차지하고 있는 디스크 공간을 회수하려면 MySQL 서버를 다시 시작하거나 DB 인스턴스를 재부팅하세요. 자세한 내용은 MySQL 웹 사이트의 임시 테이블스페이스를 참조하세요.

InnoDB 테이블 스페이스

때때로 MySQL은 쿼리가 개입하고 있으므로 제거할 수 없는 내부 임시 테이블을 생성합니다. 이러한 임시 테이블은 information_schema 내부의 "tables"라는 테이블의 일부가 아닙니다. 자세한 내용은 MySQL 웹 사이트에서 MySQL의 내부 임시 테이블 사용을 참조하세요.

이러한 내부 임시 테이블을 찾으려면 다음 쿼리를 실행하세요.

mysql> SELECT * FROM information_schema.innodb_sys_tables WHERE name LIKE '%#%';

InnoDB 시스템 테이블스페이스는 InnoDB 데이터 사전의 스토리지 영역입니다. InnoDB 시스템 테이블스페이스에는 데이터 사전과 함께 이중 쓰기 버퍼, 변경 버퍼 및 실행 취소 로그도 있습니다. 또한 테이블이 시스템 테이블스페이스에 생성된 경우(테이블당 파일 또는 일반 테이블스페이스에 생성되지 않음) 테이블스페이스에 인덱스 및 테이블 데이터가 포함될 수 있습니다.

InnoDB 시스템 테이블스페이스를 찾으려면 다음 쿼리를 실행하세요.

mysql> SELECT file_name, tablespace_name, table_name, engine, index_length, total_extents, extent_size from information_schema.files WHERE file_name LIKE '%ibdata%';

**참고:**이 쿼리는 MySQL 버전 5.7 이상 또는 MySQL 8.0 이상에서 실행됩니다.

시스템 테이블 공간의 크기를 늘린 후에는 줄일 수 없습니다. 하지만 InnoDB 테이블을 모두 덤프하고 새 MySQL DB 인스턴스로 가져올 수 있습니다. 시스템 테이블스페이스가 크지 않도록 하려면 테이블당 파일 테이블스페이스를 사용해 보세요. 자세한 내용은 MySQL 웹 사이트의 테이블당 파일 테이블스페이스를 참조하세요.

Innodb_file_per_table을 활성화하면 각 테이블이 자체 테이블스페이스 파일에 데이터와 인덱스를 저장합니다. 해당 테이블에서 OPTIMIZE TABLE을 실행하여 데이터베이스 및 테이블의 단편화로 인한 공간을 회수할 수 있습니다. OPTIMIZE TABLE 명령은 테이블의 새로운 빈 복사본을 생성합니다. 그런 다음 이전 테이블의 데이터가 새 테이블에 행별로 복사됩니다. 이 프로세스 중 새 .ibd 테이블스페이스가 생성되고 공간이 회수됩니다. 이 프로세스에 대한 자세한 내용은 MySQL 웹 사이트의 OPTIMIZE TABLE 문을 참조하세요.

**중요:**OPTIMIZE TABLE 명령은 COPY 알고리즘을 사용하여 원본 테이블과 동일한 크기의 임시 테이블을 생성합니다. 이 명령을 실행하기 전에 가용 디스크 공간이 충분한지 확인하세요.

테이블을 최적화하려면 다음의 명령 구문을 실행하세요.

mysql> OPTIMIZE TABLE <tablename>;

또는 다음 명령을 실행하여 테이블을 다시 구축할 수 있습니다.

mysql> ALTER TABLE <table_name> ENGINE=INNODB;

이진 로그

Amazon RDS 인스턴스에서 자동 백업을 활성화하면 DB 인스턴스에서 이진 로그도 자동으로 활성화됩니다. 이러한 이진 로그는 디스크에 저장되고 스토리지 공간을 사용하지만 모든 이진 로그 보존 구성에서 제거됩니다. 인스턴스의 기본 binlog 보존값도 "Null"로 설정됩니다. 즉, 파일이 즉시 제거됩니다.

스토리지 공간 부족 문제를 방지하려면 Amazon RDS for MySQL에서 적절한 이진 로그 보존 기간을 설정하세요. mysql.rds_show_configuration 명령 구문을 통해 이진 로그가 유지되는 시간을 검토할 수 있습니다.

CALL mysql.rds_show_configuration;

또한 이 값을 줄이면 로그를 더 짧은 기간 동안 유지하여 로그가 사용하는 공간을 줄일 수 있습니다. 값이 NULL이면 로그가 최대한 빨리 제거됩니다. 활성 인스턴스에 대한 스탠바이 인스턴스가 있는 경우 스탠바이 인스턴스에서 ReplicaLag 지표를 모니터링하세요. ReplicaLag 지표는 활성 인스턴스의 이진 로그 처리 또는 스탠바이 인스턴스의 릴레이 로그 중에 발생하는 지연을 나타냅니다.

활성 인스턴스에 대한 스탠바이 인스턴스가 있는 경우 스탠바이 인스턴스에서 ReplicaLag 지표를 모니터링하세요. ReplicaLag 지표는 활성 인스턴스의 이진 로그 제거와 스탠바이 인스턴스의 릴레이 로그 중 발생하는 지연을 나타냅니다. 제거 또는 복제 문제가 있는 경우 시간이 지남에 따라 이러한 이진 로그가 누적되어 추가 디스크 공간을 사용할 수 있습니다. 인스턴스의 이진 로그 수와 파일 크기를 확인하려면 SHOW BINARY LOGS 명령을 사용하세요. 자세한 내용은 MySQL 웹 사이트의 SHOW BINARY LOGS 문을 참조하세요.

DB 인스턴스가 복제 대기 인스턴스로 작동하는 경우 다음의 명령을 사용하여 릴레이 로그값의 크기(Relay_Log_Space)를 확인하세요.

SHOW SLAVE STATUS\G

MySQL 로그(일반 로그, 느린 쿼리 로그 및 오류 로그)

Amazon RDS for MySQL은 데이터베이스를 모니터링하는 데 사용할 수 있는 로그(예: 일반 로그, 느린 쿼리 로그 및 오류 로그)를 제공합니다. 오류 로그는 기본적으로 활성 상태입니다. 하지만 일반 로그와 느린 쿼리 로그는 RDS 인스턴스의 사용자 지정 파라미터 그룹을 사용하여 활성화할 수 있습니다. 느린 쿼리 로그와 일반 로그가 활성화되면 MySQL 데이터베이스 내의 slow_log 및 general_log 테이블에 자동으로 저장됩니다. 느린 쿼리, 일반 로그("FILE" 유형) 및 오류 로그의 크기를 보려면 데이터베이스 로그 파일을 확인 및 나열하세요.

느린 쿼리 로그와 일반 로그 테이블이 과도한 스토리지를 사용하는 경우 로그 테이블을 수동으로 회전하여 테이블 기반의 MySQL 로그를 관리하세요. 오래된 데이터를 완전히 제거하고 디스크 공간을 회수하려면 다음 명령을 두 번 연속으로 호출하세요.

mysql> CALL mysql.rds_rotate_slow_log;
mysql> CALL mysql.rds_rotate_general_log;

**참고:**테이블은 로그의 정확한 파일 크기를 제공하지 않습니다. slow_log 및 general_log에 대한 log_output값이 "Table"이 아닌 "File"이 되도록 파라미터를 수정하세요.

또한 Amazon CloudWatch를 사용하여 Amazon RDS DB 인스턴스를 모니터링하는 것도 모범 사례입니다. FreeStorageSpace 지표에서 CloudWatch 경보를 설정하여 스토리지 공간이 특정 임곗값 아래로 떨어질 때마다 알림을 받을 수 있습니다. 마지막으로, DB 인스턴스의 여유 공간이 부족할 때마다 알림을 받도록 CloudWatch 경보를 설정하여 FreeStorageSpace 지표를 모니터링하세요. 자세한 내용은 Amazon RDS의 여유 스토리지 공간을 모니터링하고 스토리지가 가득 차는 문제를 방지하기 위해 CloudWatch 경보를 생성하려면 어떻게 해야 하나요?를 참조하세요.

또한 Amazon RDS 스토리지 자동 크기 조정 기능을 사용하여 용량을 자동으로 관리할 수 있습니다. 스토리지 자동 크기 조정을 사용하면 데이터베이스 스토리지를 수동으로 스케일 업할 필요가 없습니다. Amazon RDS 스토리지 자동 크기 조정에 대한 자세한 내용은 Amazon RDS DB 인스턴스의 스토리지를 사용한 작업을 참조하세요.

---

관련 정보

예상보다 많은 스토리지를 사용하는 Amazon RDS for MySQL DB 인스턴스의 문제를 해결하려면 어떻게 해야 하나요?