MySQL 또는 MariaDB를 실행하는 Amazon RDS DB 인스턴스의 스토리지를 사용하는 항목을 확인하려면 어떻게 해야 합니까?

최종 업데이트 날짜: 2020년 3월 13일

MySQL 또는 MariaDB를 실행하는 Amazon Relational Database Service(Amazon RDS) DB 인스턴스의 스토리지를 사용하는 항목을 확인하려면 어떻게 해야 합니까?

간략한 설명

DB 인스턴스에서 사용된 총 공간을 분석합니다. DB 인스턴스의 공간은 다음과 같은 용도로 사용됩니다.

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

스토리지 공간을 사용하는 항목을 식별한 후에 스토리지 공간을 회수할 수 있습니다. 그런 다음, FreeStorageSpace 지표를 모니터링하여 향후 공간 부족을 방지할 수 있습니다.

​해결 방법

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;

데이터베이스 수준 및 테이블 수준에서 조각화된 대략적인 공간을 식별하려면 다음 쿼리를 실행합니다.

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;

참고: MySQL 5.7 또는 MariaDB 10.2.1보다 높은 DB 엔진 버전을 사용하는 경우 information_schema 내부에서 다음 쿼리를 사용하여 보다 정확한 데이터베이스/테이블 크기를 검색할 수 있습니다.

mysql> SELECT SUBSTRING_INDEX(it.name, '/', 1) AS table_schema,
ROUND(SUM(its.allocated_size)/1024/1024/1024, 2) "size in GB" FROM
information_schema.innodb_sys_tables it INNER JOIN information_schema.innodb_sys_tablespaces its ON it.space =
its.space GROUP BY 1 ORDER BY 2 DESC;
mysql> SELECT SUBSTRING_INDEX(it.name, '/', 1) AS table_schema, ROUND(SUM(its.allocated_size)/1024/1024/1024,2) "size in GB",
ROUND(SUM(t.data_free)/1024/1024/1024,2) "fragmented size in GB"  FROM
information_schema.innodb_sys_tables it INNER
JOIN  information_schema.innodb_sys_tablespaces
its ON it.space = its.space INNER JOIN information_schema.innodb_sys_tablestats istat ON istat.table_id =
it.table_id INNER JOIN information_schema.tables t ON t.table_schema = SUBSTRING_INDEX(it.name,
'/', 1) AND t.table_name = SUBSTRING_INDEX(it.name, '/', -1) GROUP BY 1 ORDER BY 2 DESC;
mysql> SELECT SUBSTRING_INDEX(it.name, '/', 1) AS
table_schema, t.table_name, ROUND(its.allocated_size/1024/1024/1024,2)
"size in GB", ROUND(t.data_free/1024/1024/1024,2) "fragmented
size in GB"  FROM information_schema.innodb_sys_tables
it INNER JOIN information_schema.innodb_sys_tablespaces its ON it.space = its.space INNER JOIN
information_schema.innodb_sys_tablestats istat ON istat.table_id = it.table_id INNER JOIN information_schema.tables t
ON t.table_schema = SUBSTRING_INDEX(it.name, '/', 1) AND t.table_name =
SUBSTRING_INDEX(it.name, '/', -1)  WHERE
t.table_schema NOT IN ('performance_schema', 'mysql', 'information_schema')
ORDER BY 4 DESC;

DB 인스턴스에서 사용 가능한 각 이진 로그 파일의 이름과 크기를 검색하려면 다음 명령을 실행합니다.

SHOW MASTER LOGS;

Amazon RDS 콘솔 또는 AWS CLI(명령줄 인터페이스)를 사용하여 느린 쿼리, 일반 쿼리 및 오류 쿼리의 크기를 확인합니다.

참고: 기본적으로 느린 쿼리 로그와 일반 로그는 MySQL 데이터베이스 내의 slow_loggeneral_log 테이블에 저장됩니다. 테이블은 로그에 대한 정확한 파일 크기를 제공하지 않습니다. slow_loggeneral_log에 대한 log_output 값이 Table이 아니라 File이 되도록 파라미터를 수정합니다.

경우에 따라, MySQL은 쿼리가 개입 중이기 때문에 제거할 수 없는 내부 임시 테이블을 생성합니다. 이러한 임시 테이블은 information_schema 내부의 "tables"라는 이름의 테이블에 포함되지 않습니다. 자세한 내용은 MySQL 설명서에서 MySQL에서 내부 임시 테이블 사용을 참조하십시오. 이러한 내부 임시 테이블을 찾으려면 다음 쿼리를 실행합니다.

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

MySQL 버전 5.5 및 5.6에서는 InnoDB 테이블스페이스의 파일 크기를 쿼리할 수 없지만 MySQL 버전 5.7 이상에서는 파일 크기를 쿼리할 수 있습니다. 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%';

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%';

DB 인스턴스가 복제 대기 인스턴스의 역할을 하는 경우 데이터베이스의 다음 명령에서 Relay_Log_Space 값을 사용하여 릴레이 로그의 크기를 확인할 수 있습니다.

SHOW SLAVE STATUS\G

스토리지 공간 회수

사용자 데이터베이스 및 테이블의 조각화에서 공간을 회수하려면 InnoDB 테이블을 최적화합니다. InnoDB 테이블의 경우 OPTIMIZE TABLEALTER TABLE ... FORCE에 매핑됩니다. 그러면 테이블을 다시 빌드하여 인덱스 통계를 업데이트하고 클러스터링된 인덱스에서 사용하지 않는 공간을 확보합니다. 자세한 내용은 MySQL 설명서에서 OPTIMIZE TABLE 문을 참조하십시오. OPTIMIZE TABLE에 대한 다음 예제 명령을 참조하십시오.

mysql> OPTIMIZE TABLE foo;

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

mysql> ALTER TABLE <table_name> ENGINE=INNODB;

중요: 이 명령은 COPY ALGORITHM을 사용하여 원본 테이블과 같은 크기의 임시 테이블을 생성합니다. 이 명령을 실행하기 전에 사용 가능한 디스크 공간이 충분히 있는지 확인합니다.

MySQL 5.7 이상에서 임시 테이블(ibtmp1)이 과도한 스토리지를 사용하는 경우, DB 인스턴스를 재부팅하여 공간을 해제합니다.

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

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

향후 스토리지 공간 부족 문제 방지

이진 로그 보존 기간은 이진 로그가 DB 인스턴스에 얼마나 오래 저장될 지를 결정합니다. 현재 이진 로그 보존 기간을 확인하려면 다음 명령을 실행합니다.

mysql> CALL mysql.rds_show_configuration;

이 값을 줄여 더 짧은 기간 동안 로그를 보존할 수 있습니다. 이렇게 하면 로그가 사용하는 공간의 양이 줄어듭니다. NULL 값은 로그가 가능한 한, 빨리 제거됨을 의미합니다. 활성 인스턴스에 대한 대기 인스턴스가 있는 경우 대기 인스턴스에서 ReplicaLag 지표를 모니터링합니다. 이 경우 활성 인스턴스에서 이진 로그 처리가 지연되고 대기 인스턴스에서 로그를 릴레이하기 때문입니다.

사용 가능한 스토리지가 갑자기 감소하는 경우, 데이터베이스에 SHOW FULL PROCESSLIST; 명령을 실행하여 DB 인스턴스 수준에서 진행 중인 쿼리를 확인합니다. 이 명령을 실행하면 모든 활성 연결, 각 연결에 의해 실행된 쿼리 및 쿼리가 현재 상태를 얼마나 오래 유지했는지에 대한 세부 정보가 제공됩니다. 어떤 트랜잭션이 긴 시간 동안 활성 상태인지를 검토하려면 SHOW ENGINE INNODB STATUS; 또는 SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX 명령을 실행한 다음 출력을 검토합니다.

마지막으로, DB 인스턴스의 여유 공간이 부족할 때 알림을 수신할 수 있도록 Amazon CloudWatch 경보를 설졍하여 FreeStorageSpace 지표를 모니터링합니다.