개요
QA 환경의 MYSQL에서 OOM 에러가 발생하여 원인 확인 및 조치에 대하여 확인하였던 내용입니다.
QA 환경의 장비는 T4G.Large를 사용하여 8GIB의 메모리를 사용할 수 있습니다.
메모리 사용량 관련 지표
Freeable Memory (OS 전체 영역에 대한 가용 메모리, buffer pool에 할당된 메모리는 제외하고 산출됨)
SwapUsage 메모리 SWAP 지표
Performance Schema 관련 확인 내용
Summary 테이블 중 by_account와 by_host 테이블이 대부분을 차지하고 있음
-- Performance Schema 전체 메모리 사용량
SELECT
SUBSTRING_INDEX(EVENT_NAME, '/', 2) AS category,
ROUND(SUM(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024, 2) AS memory_mb,
ROUND(SUM(HIGH_NUMBER_OF_BYTES_USED)/1024/1024, 2) AS peak_memory_mb
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/performance_schema/%'
GROUP BY category
ORDER BY memory_mb DESC;
+---------------------------+-----------+----------------+
| category | memory_mb | peak_memory_mb |
+---------------------------+-----------+----------------+
| memory/performance_schema | 3592.28 | 3592.28 |
+---------------------------+-----------+----------------+
-- InnoDB 버퍼 풀 사용량
SELECT
POOL_ID,
ROUND((POOL_SIZE * @@innodb_page_size)/1024/1024/1024, 2) AS pool_size_gb,
ROUND((FREE_BUFFERS * @@innodb_page_size)/1024/1024/1024, 2) AS free_gb,
ROUND((DATABASE_PAGES * @@innodb_page_size)/1024/1024/1024, 2) AS data_gb
FROM information_schema.INNODB_BUFFER_POOL_STATS;
+---------+--------------+---------+---------+
| POOL_ID | pool_size_gb | free_gb | data_gb |
+---------+--------------+---------+---------+
| 0 | 1.48 | 0.00 | 1.48 |
| 1 | 1.48 | 0.00 | 1.48 |
+---------+--------------+---------+---------+
-- Performance Schema 상세 메모리 사용량 (Top 20)
SELECT
EVENT_NAME,
CURRENT_COUNT_USED,
ROUND(CURRENT_NUMBER_OF_BYTES_USED/1024/1024, 2) AS current_mb,
ROUND(HIGH_NUMBER_OF_BYTES_USED/1024/1024, 2) AS peak_mb
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/performance_schema/%'
AND CURRENT_NUMBER_OF_BYTES_USED > 0
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 20;
+------------------------------------------------------------------------------+--------------------+------------+---------+
| EVENT_NAME | CURRENT_COUNT_USED | current_mb | peak_mb |
+------------------------------------------------------------------------------+--------------------+------------+---------+
| memory/performance_schema/events_errors_summary_by_account_by_error | 13416 | 644.11 | 644.11 |
| memory/performance_schema/events_statements_summary_by_account_by_event_name | 104 | 546.00 | 546.00 |
| memory/performance_schema/events_errors_summary_by_host_by_error | 10578 | 507.86 | 507.86 |
| memory/performance_schema/events_statements_summary_by_host_by_event_name | 82 | 430.50 | 430.50 |
| memory/performance_schema/memory_summary_by_account_by_event_name | 104 | 411.33 | 411.33 |
| memory/performance_schema/memory_summary_by_host_by_event_name | 82 | 324.32 | 324.32 |
| memory/performance_schema/events_waits_summary_by_account_by_event_name | 104 | 266.50 | 266.50 |
| memory/performance_schema/events_waits_summary_by_host_by_event_name | 82 | 210.13 | 210.13 |
| memory/performance_schema/events_stages_summary_by_account_by_event_name | 104 | 71.09 | 71.09 |
| memory/performance_schema/events_stages_summary_by_host_by_event_name | 82 | 56.05 | 56.05 |
| memory/performance_schema/events_errors_summary_by_thread_by_error | 257 | 12.39 | 12.39 |
| memory/performance_schema/accounts | 104 | 12.19 | 12.19 |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name | 1 | 10.50 | 10.50 |
| memory/performance_schema/table_handles | 1 | 9.06 | 9.06 |
| memory/performance_schema/hosts | 82 | 8.33 | 8.33 |
| memory/performance_schema/memory_summary_by_thread_by_event_name | 1 | 7.91 | 7.91 |
| memory/performance_schema/events_errors_summary_by_user_by_error | 129 | 6.19 | 6.19 |
| memory/performance_schema/events_statements_summary_by_user_by_event_name | 1 | 5.25 | 5.25 |
| memory/performance_schema/events_waits_summary_by_thread_by_event_name | 1 | 5.13 | 5.13 |
| memory/performance_schema/table_shares | 1 | 4.00 | 4.00 |
+------------------------------------------------------------------------------+--------------------+------------+---------+
20 rows in set (0.85 sec)
--메모리 사용량이 높은 events_errors_summary_by_account_by_error 테이블의 건수 확인
select count(*) from events_errors_summary_by_account_by_error ;
+----------+
| count(*) |
+----------+
| 21124880 |
+----------+
1 row in set (31.59 sec)
show create table events_errors_summary_by_account_by_error;
+-------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| events_errors_summary_by_account_by_error | CREATE TABLE `events_errors_summary_by_account_by_error` (
`USER` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`HOST` char(255) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL,
`ERROR_NUMBER` int DEFAULT NULL,
`ERROR_NAME` varchar(64) DEFAULT NULL,
`SQL_STATE` varchar(5) DEFAULT NULL,
`SUM_ERROR_RAISED` bigint unsigned NOT NULL,
`SUM_ERROR_HANDLED` bigint unsigned NOT NULL,
`FIRST_SEEN` timestamp NULL DEFAULT NULL,
`LAST_SEEN` timestamp NULL DEFAULT NULL,
UNIQUE KEY `ACCOUNT` (`USER`,`HOST`,`ERROR_NUMBER`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
--유저 + HOST + ERROR_NUMBER로 데이터가 수집되어 많은 사이즈 차지
select * from events_errors_summary_by_account_by_error where user ='개발자 계정아이디';
+-----------------+---------------+--------------+------------------------------------------------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| USER | HOST | ERROR_NUMBER | ERROR_NAME | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN | LAST_SEEN |
+-----------------+---------------+--------------+------------------------------------------------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| 개발자 계정아이디| 99.99.999.999 | NULL | NULL | NULL | 0 | 0 | NULL | NULL |
| 개발자 계정아이디| 99.99.999.999 | 1004 | ER_CANT_CREATE_FILE | HY000 | 0 | 0 | NULL | NULL |
| 개발자 계정아이디| 99.99.999.999 | 1005 | ER_CANT_CREATE_TABLE | HY000 | 0 | 0 | NULL | NULL |
| 개발자 계정아이디| 99.99.999.999 | 1006 | ER_CANT_CREATE_DB | HY000 | 0 | 0 | NULL | NULL |
| 개발자 계정아이디| 99.99.999.999 | 1135 | ER_CANT_CREATE_THREAD | HY000 | 0 | 0 | NULL | NULL |
| 개발자 계정아이디| 99.99.999.999 | 1136 | ER_WRONG_VALUE_COUNT_ON_ROW | 21S01 | 0 | 0 | NULL | NULL |
...
| 개발자 계정아이디| 99.99.999.999 | 63984 | ER_INVALID_REGION_SPEC | HY000 | 0 | 0 | NULL | NULL |
| 개발자 계정아이디| 99.99.999.999 | 63985 | ER_S3_API_ERROR | HY000 | 0 | 0 | NULL | NULL |
| 개발자 계정아이디| 99.99.999.999 | 63986 | ER_INVALID_LAMBDA_ARN | HY000 | 0 | 0 | NULL | NULL |
| 개발자 계정아이디| 99.99.999.999 | 63987 | ER_LAMBDA_API_ERROR | HY000 | 0 | 0 | NULL | NULL |
| 개발자 계정아이디| 99.99.999.999 | 63988 | ER_INCOMPATIBLE_SETTINGS | HY000 | 0 | 0 | NULL | NULL |
| 개발자 계정아이디| 99.99.999.999 | 63989 | ER_INVALID_FAILURE_INJECTION | HY000 | 0 | 0 | NULL | NULL |
+-----------------+---------------+--------------+------------------------------------------------------------------+-----------+------------------+-------------------+---------------------+---------------------+
1585 rows in set (0.06 sec)
select * from events_errors_summary_by_account_by_error where user like '서비스 계정 아이디%' limit 10;
+---------------------------+---------------+--------------+-------------------------+-----------+------------------+-------------------+------------+-----------+
| USER | HOST | ERROR_NUMBER | ERROR_NAME | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN | LAST_SEEN |
+---------------------------+---------------+--------------+-------------------------+-----------+------------------+-------------------+------------+-----------+
| 서비스 계정 아이디 | 99.99.999.999 | NULL | NULL | NULL | 0 | 0 | NULL | NULL |
| 서비스 계정 아이디 | 99.99.999.999 | 1004 | ER_CANT_CREATE_FILE | HY000 | 0 | 0 | NULL | NULL |
| 서비스 계정 아이디 | 99.99.999.999 | 1005 | ER_CANT_CREATE_TABLE | HY000 | 0 | 0 | NULL | NULL |
| 서비스 계정 아이디 | 99.99.999.999 | 1006 | ER_CANT_CREATE_DB | HY000 | 0 | 0 | NULL | NULL |
| 서비스 계정 아이디 | 99.99.999.999 | 1007 | ER_DB_CREATE_EXISTS | HY000 | 0 | 0 | NULL | NULL |
| 서비스 계정 아이디 | 99.99.999.999 | 1008 | ER_DB_DROP_EXISTS | HY000 | 0 | 0 | NULL | NULL |
| 서비스 계정 아이디 | 99.99.999.999 | 1010 | ER_DB_DROP_RMDIR | HY000 | 0 | 0 | NULL | NULL |
| 서비스 계정 아이디 | 99.99.999.999 | 1012 | ER_CANT_FIND_SYSTEM_REC | HY000 | 0 | 0 | NULL | NULL |
| 서비스 계정 아이디 | 99.99.999.999 | 1013 | ER_CANT_GET_STAT | HY000 | 0 | 0 | NULL | NULL |
| 서비스 계정 아이디 | 99.99.999.999 | 1015 | ER_CANT_LOCK | HY000 | 0 | 0 | NULL | NULL |
+---------------------------+---------------+--------------+-------------------------+-----------+------------------+-------------------+------------+-----------+
10 rows in set (0.05 sec)
--재부팅 이후 Performance_Schema 초기화 되어 메모리 확보된 상태
+------------------------------------------------------------------------------+--------------------+------------+---------+
| EVENT_NAME | CURRENT_COUNT_USED | current_mb | peak_mb |
+------------------------------------------------------------------------------+--------------------+------------+---------+
| memory/performance_schema/events_errors_summary_by_thread_by_error | 257 | 12.39 | 12.39 |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name | 1 | 10.50 | 10.50 |
| memory/performance_schema/table_handles | 1 | 9.06 | 9.06 |
| memory/performance_schema/memory_summary_by_thread_by_event_name | 1 | 7.91 | 7.91 |
| memory/performance_schema/events_errors_summary_by_user_by_error | 129 | 6.19 | 6.19 |
| memory/performance_schema/events_errors_summary_by_host_by_error | 129 | 6.19 | 6.19 |
| memory/performance_schema/events_errors_summary_by_account_by_error | 129 | 6.19 | 6.19 |
| memory/performance_schema/events_statements_summary_by_user_by_event_name | 1 | 5.25 | 5.25 |
| memory/performance_schema/events_statements_summary_by_account_by_event_name | 1 | 5.25 | 5.25 |
| memory/performance_schema/events_statements_summary_by_host_by_event_name | 1 | 5.25 | 5.25 |
| memory/performance_schema/events_waits_summary_by_thread_by_event_name | 1 | 5.13 | 5.13 |
| memory/performance_schema/table_shares | 1 | 4.00 | 4.00 |
| memory/performance_schema/events_statements_summary_by_digest | 1 | 3.97 | 3.97 |
| memory/performance_schema/memory_summary_by_user_by_event_name | 1 | 3.96 | 3.96 |
| memory/performance_schema/memory_summary_by_account_by_event_name | 1 | 3.96 | 3.96 |
| memory/performance_schema/memory_summary_by_host_by_event_name | 1 | 3.96 | 3.96 |
| memory/performance_schema/events_statements_current | 1 | 3.57 | 3.57 |
| memory/performance_schema/table_io_waits_summary_by_index_usage | 1 | 2.75 | 2.75 |
| memory/performance_schema/file_instances | 1 | 2.75 | 2.75 |
| memory/performance_schema/events_waits_summary_by_user_by_event_name | 1 | 2.56 | 2.56 |
+------------------------------------------------------------------------------+--------------------+------------+---------+
SHOW VARIABLES LIKE '%performance_schema_%_size%';
+----------------------------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------------------------+-------+
| aurora_performance_schema_sql_info_max_size | 4096 |
| performance_schema_accounts_size | -1 |
| performance_schema_digests_size | 1000 |
| performance_schema_error_size | 5057 |
| performance_schema_events_stages_history_long_size | 100 |
| performance_schema_events_stages_history_size | 5 |
| performance_schema_events_statements_history_long_size | 100 |
| performance_schema_events_statements_history_size | 5 |
| performance_schema_events_transactions_history_long_size | 100 |
| performance_schema_events_transactions_history_size | 5 |
| performance_schema_events_waits_history_long_size | 100 |
| performance_schema_events_waits_history_size | 5 |
| performance_schema_hosts_size | -1 |
| performance_schema_session_connect_attrs_size | 512 |
| performance_schema_setup_actors_size | -1 |
| performance_schema_setup_objects_size | -1 |
| performance_schema_users_size | -1 |
+----------------------------------------------------------+-------+
size 변수에 따른 performance_schema 영향도 테스트
size 변수는 Performance Insight가 Performance Schema를 자동관리 하지 않을 때 조정 가능함.
--5개 계정 생성
CREATE USER `minjae_test1`@`%` IDENTIFIED by '';
CREATE USER `minjae_test2`@`%` IDENTIFIED by '';
CREATE USER `minjae_test3`@`%` IDENTIFIED by '';
CREATE USER `minjae_test4`@`%` IDENTIFIED by '';
CREATE USER `minjae_test5`@`%` IDENTIFIED by '';
--권한 부여
GRANT ALL PRIVILEGES ON minjae_test.* TO 'minjae_test1'@'%';
GRANT ALL PRIVILEGES ON minjae_test.* TO 'minjae_test2'@'%';
GRANT ALL PRIVILEGES ON minjae_test.* TO 'minjae_test3'@'%';
GRANT ALL PRIVILEGES ON minjae_test.* TO 'minjae_test4'@'%';
GRANT ALL PRIVILEGES ON minjae_test.* TO 'minjae_test5'@'%';
--각 계정이 수행할 테이블 생성
create table minjae_test1 (col1 int);
create table minjae_test2 (col1 int);
create table minjae_test3 (col1 int);
create table minjae_test4 (col1 int);
create table minjae_test5 (col1 int);
--계정별 접근
mysql -h seoul-minjae-oomtest-auroramysql.cluster-cwgfom0rr1u3.ap-northeast-2.rds.amazonaws.com -P 20306 -u minjae_test1 -p
mysql -h seoul-minjae-oomtest-auroramysql.cluster-cwgfom0rr1u3.ap-northeast-2.rds.amazonaws.com -P 20306 -u minjae_test2 -p
mysql -h seoul-minjae-oomtest-auroramysql.cluster-cwgfom0rr1u3.ap-northeast-2.rds.amazonaws.com -P 20306 -u minjae_test3 -p
mysql -h seoul-minjae-oomtest-auroramysql.cluster-cwgfom0rr1u3.ap-northeast-2.rds.amazonaws.com -P 20306 -u minjae_test4 -p
mysql -h seoul-minjae-oomtest-auroramysql.cluster-cwgfom0rr1u3.ap-northeast-2.rds.amazonaws.com -P 20306 -u minjae_test5 -p
use minjae_test;
--minjae_test1 계정
insert into minjae_test1 (col1) values(1);
insert into minjae_test1 (col1) values(2);
insert into minjae_test1 (col1) values(3);
insert into minjae_test1 (col1) values(4);
insert into minjae_test1 (col1) values(5);
--minjae_test2 계정
insert into minjae_test2 (col1) values(1);
insert into minjae_test2 (col1) values(2);
insert into minjae_test2 (col1) values(3);
insert into minjae_test2 (col1) values(4);
insert into minjae_test2 (col1) values(5);
--minjae_test3 계정
insert into minjae_test3 (col1) values(1);
insert into minjae_test3 (col1) values(2);
insert into minjae_test3 (col1) values(3);
insert into minjae_test3 (col1) values(4);
insert into minjae_test3 (col1) values(5);
--minjae_test4 계정
insert into minjae_test4 (col1) values(1);
insert into minjae_test4 (col1) values(2);
insert into minjae_test4 (col1) values(3);
insert into minjae_test4 (col1) values(4);
insert into minjae_test4 (col1) values(5);
--minjae_test5 계정
insert into minjae_test5 (col1) values(1);
insert into minjae_test5 (col1) values(2);
insert into minjae_test5 (col1) values(3);
insert into minjae_test5 (col1) values(4);
insert into minjae_test5 (col1) values(5);
--기본 설정 상태
MySQL [performance_schema]> show variables like 'performance_%size%';
+----------------------------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------------------------+-------+
| performance_schema_accounts_size | -1 |
| performance_schema_digests_size | 1000 |
| performance_schema_error_size | 5361 |
| performance_schema_events_stages_history_long_size | 100 |
| performance_schema_events_stages_history_size | 5 |
| performance_schema_events_statements_history_long_size | 100 |
| performance_schema_events_statements_history_size | 5 |
| performance_schema_events_transactions_history_long_size | 100 |
| performance_schema_events_transactions_history_size | 5 |
| performance_schema_events_waits_history_long_size | 100 |
| performance_schema_events_waits_history_size | 5 |
| performance_schema_hosts_size | -1 |
| performance_schema_session_connect_attrs_size | 512 |
| performance_schema_setup_actors_size | -1 |
| performance_schema_setup_objects_size | -1 |
| performance_schema_users_size | -1 |
+----------------------------------------------------------+-------+
--각 계정이 사용한 쿼리가 events_statements_summary_by_digest에 수집됨
MySQL [performance_schema]> select DIGEST_TEXT from events_statements_summary_by_digest where DIGEST_TEXT like '%minjae_test%' ;
+--------------------------------------------------------+
| DIGEST_TEXT |
+--------------------------------------------------------+
| CREATE SCHEMA `minjae_test` |
| CREATE TABLE `minjae_test1` ( `col1` INTEGER ) |
| CREATE TABLE `minjae_test2` ( `col1` INTEGER ) |
| CREATE TABLE `minjae_test3` ( `col1` INTEGER ) |
| CREATE TABLE `minjae_test4` ( `col1` INTEGER ) |
| CREATE TABLE `minjae_test5` ( `col1` INTEGER ) |
| CREATE SYSTEM_USER `minjae_test1` @`%` IDENTIFIED BY ? |
| CREATE SYSTEM_USER `minjae_test2` @`%` IDENTIFIED BY ? |
| CREATE SYSTEM_USER `minjae_test3` @`%` IDENTIFIED BY ? |
| CREATE SYSTEM_USER `minjae_test4` @`%` IDENTIFIED BY ? |
| CREATE SYSTEM_USER `minjae_test5` @`%` IDENTIFIED BY ? |
| GRANT ALL PRIVILEGES ON `minjae_test` . * TO ? @? |
| INSERT INTO `minjae_test1` ( `col1` ) VALUES (?) |
| INSERT INTO `minjae_test2` ( `col1` ) VALUES (?) |
| INSERT INTO `minjae_test3` ( `col1` ) VALUES (?) |
| INSERT INTO `minjae_test4` ( `col1` ) VALUES (?) |
| INSERT INTO `minjae_test5` ( `col1` ) VALUES (?) |
+--------------------------------------------------------+
--각 계정에 대해 events_errors_summary_by_account_by_error 에 데이터가 생성됨
MySQL [performance_schema]> select * from events_errors_summary_by_account_by_error where user like 'minjae%' group by user;
+--------------+-------------+--------------+------------+-----------+------------------+-------------------+------------+-----------+
| USER | HOST | ERROR_NUMBER | ERROR_NAME | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN | LAST_SEEN |
+--------------+-------------+--------------+------------+-----------+------------------+-------------------+------------+-----------+
| minjaeko | 999.99.9.99 | NULL | NULL | NULL | 0 | 0 | NULL | NULL |
| minjae_test1 | 999.99.9.99 | NULL | NULL | NULL | 0 | 0 | NULL | NULL |
| minjae_test2 | 999.99.9.99 | NULL | NULL | NULL | 0 | 0 | NULL | NULL |
| minjae_test5 | 999.99.9.99 | NULL | NULL | NULL | 0 | 0 | NULL | NULL |
| minjae_test4 | 999.99.9.99 | NULL | NULL | NULL | 0 | 0 | NULL | NULL |
| minjae_test3 | 999.99.9.99 | NULL | NULL | NULL | 0 | 0 | NULL | NULL |
+--------------+-------------+--------------+------------+-----------+------------------+-------------------+------------+-----------+
6 rows in set (0.030 sec)
--각 계정이 수행한 쿼리가 events_statements_history에 수집 됨
MySQL [performance_schema]> select THREAD_ID,EVENT_NAME,DIGEST_TEXT,SQL_TEXT from events_statements_history where DIGEST_TEXT like '%minjae_test%' ;
+-----------+----------------------+--------------------------------------------------+-------------------------------------------+
| THREAD_ID | EVENT_NAME | DIGEST_TEXT | SQL_TEXT |
+-----------+----------------------+--------------------------------------------------+-------------------------------------------+
| 123 | statement/sql/insert | INSERT INTO `minjae_test1` ( `col1` ) VALUES (?) | insert into minjae_test1 (col1) values(1) |
| 123 | statement/sql/insert | INSERT INTO `minjae_test1` ( `col1` ) VALUES (?) | insert into minjae_test1 (col1) values(2) |
| 123 | statement/sql/insert | INSERT INTO `minjae_test1` ( `col1` ) VALUES (?) | insert into minjae_test1 (col1) values(3) |
| 123 | statement/sql/insert | INSERT INTO `minjae_test1` ( `col1` ) VALUES (?) | insert into minjae_test1 (col1) values(4) |
| 123 | statement/sql/insert | INSERT INTO `minjae_test1` ( `col1` ) VALUES (?) | insert into minjae_test1 (col1) values(5) |
| 124 | statement/sql/insert | INSERT INTO `minjae_test2` ( `col1` ) VALUES (?) | insert into minjae_test2 (col1) values(1) |
| 124 | statement/sql/insert | INSERT INTO `minjae_test2` ( `col1` ) VALUES (?) | insert into minjae_test2 (col1) values(2) |
| 124 | statement/sql/insert | INSERT INTO `minjae_test2` ( `col1` ) VALUES (?) | insert into minjae_test2 (col1) values(3) |
| 124 | statement/sql/insert | INSERT INTO `minjae_test2` ( `col1` ) VALUES (?) | insert into minjae_test2 (col1) values(4) |
| 124 | statement/sql/insert | INSERT INTO `minjae_test2` ( `col1` ) VALUES (?) | insert into minjae_test2 (col1) values(5) |
| 126 | statement/sql/insert | INSERT INTO `minjae_test5` ( `col1` ) VALUES (?) | insert into minjae_test5 (col1) values(1) |
| 126 | statement/sql/insert | INSERT INTO `minjae_test5` ( `col1` ) VALUES (?) | insert into minjae_test5 (col1) values(2) |
| 126 | statement/sql/insert | INSERT INTO `minjae_test5` ( `col1` ) VALUES (?) | insert into minjae_test5 (col1) values(3) |
| 126 | statement/sql/insert | INSERT INTO `minjae_test5` ( `col1` ) VALUES (?) | insert into minjae_test5 (col1) values(4) |
| 126 | statement/sql/insert | INSERT INTO `minjae_test5` ( `col1` ) VALUES (?) | insert into minjae_test5 (col1) values(5) |
| 128 | statement/sql/insert | INSERT INTO `minjae_test4` ( `col1` ) VALUES (?) | insert into minjae_test4 (col1) values(1) |
| 128 | statement/sql/insert | INSERT INTO `minjae_test4` ( `col1` ) VALUES (?) | insert into minjae_test4 (col1) values(2) |
| 128 | statement/sql/insert | INSERT INTO `minjae_test4` ( `col1` ) VALUES (?) | insert into minjae_test4 (col1) values(3) |
| 128 | statement/sql/insert | INSERT INTO `minjae_test4` ( `col1` ) VALUES (?) | insert into minjae_test4 (col1) values(4) |
| 128 | statement/sql/insert | INSERT INTO `minjae_test4` ( `col1` ) VALUES (?) | insert into minjae_test4 (col1) values(5) |
| 129 | statement/sql/insert | INSERT INTO `minjae_test3` ( `col1` ) VALUES (?) | insert into minjae_test3 (col1) values(1) |
| 129 | statement/sql/insert | INSERT INTO `minjae_test3` ( `col1` ) VALUES (?) | insert into minjae_test3 (col1) values(2) |
| 129 | statement/sql/insert | INSERT INTO `minjae_test3` ( `col1` ) VALUES (?) | insert into minjae_test3 (col1) values(3) |
| 129 | statement/sql/insert | INSERT INTO `minjae_test3` ( `col1` ) VALUES (?) | insert into minjae_test3 (col1) values(4) |
| 129 | statement/sql/insert | INSERT INTO `minjae_test3` ( `col1` ) VALUES (?) | insert into minjae_test3 (col1) values(5) |
+-----------+----------------------+--------------------------------------------------+-------------------------------------------+
--2개 계정까지 수집하도록 설정
MySQL [performance_schema]> show variables like 'performance_%size%';
+----------------------------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------------------------+-------+
| performance_schema_accounts_size | 2 |
| performance_schema_digests_size | 1000 |
| performance_schema_error_size | 5361 |
| performance_schema_events_stages_history_long_size | 100 |
| performance_schema_events_stages_history_size | 5 |
| performance_schema_events_statements_history_long_size | 100 |
| performance_schema_events_statements_history_size | 5 |
| performance_schema_events_transactions_history_long_size | 100 |
| performance_schema_events_transactions_history_size | 5 |
| performance_schema_events_waits_history_long_size | 100 |
| performance_schema_events_waits_history_size | 5 |
| performance_schema_hosts_size | -1 |
| performance_schema_session_connect_attrs_size | 512 |
| performance_schema_setup_actors_size | -1 |
| performance_schema_setup_objects_size | -1 |
| performance_schema_users_size | 2 |
+----------------------------------------------------------+-------+
--각 계정이 수행한 쿼리가 events_statements_summary_by_digest에 수집됨
MySQL [performance_schema]> select DIGEST_TEXT from events_statements_summary_by_digest where DIGEST_TEXT like '%minjae_test%' ;
+--------------------------------------------------+
| DIGEST_TEXT |
+--------------------------------------------------+
| INSERT INTO `minjae_test1` ( `col1` ) VALUES (?) |
| INSERT INTO `minjae_test2` ( `col1` ) VALUES (?) |
| INSERT INTO `minjae_test3` ( `col1` ) VALUES (?) |
| INSERT INTO `minjae_test4` ( `col1` ) VALUES (?) |
| INSERT INTO `minjae_test5` ( `col1` ) VALUES (?) |
+--------------------------------------------------+
5 rows in set (0.002 sec)
--rdsadmin 같은 계정이 먼저 수집되어 events_errors_summary_by_account_by_error에는 생성한 계정 데이터가 없음
MySQL [performance_schema]> select * from events_errors_summary_by_account_by_error where user like 'minjae%' group by user;
Empty set (0.006 sec)
--각 계정이 수행한 쿼리가 events_statements_history에 수집 됨
MySQL [performance_schema]> select THREAD_ID,EVENT_NAME,DIGEST_TEXT,SQL_TEXT from events_statements_history where DIGEST_TEXT like '%minjae_test%' ;
+-----------+----------------------+--------------------------------------------------+-------------------------------------------+
| THREAD_ID | EVENT_NAME | DIGEST_TEXT | SQL_TEXT |
+-----------+----------------------+--------------------------------------------------+-------------------------------------------+
| 90 | statement/sql/insert | INSERT INTO `minjae_test1` ( `col1` ) VALUES (?) | insert into minjae_test1 (col1) values(1) |
| 90 | statement/sql/insert | INSERT INTO `minjae_test1` ( `col1` ) VALUES (?) | insert into minjae_test1 (col1) values(2) |
| 90 | statement/sql/insert | INSERT INTO `minjae_test1` ( `col1` ) VALUES (?) | insert into minjae_test1 (col1) values(3) |
| 90 | statement/sql/insert | INSERT INTO `minjae_test1` ( `col1` ) VALUES (?) | insert into minjae_test1 (col1) values(4) |
| 90 | statement/sql/insert | INSERT INTO `minjae_test1` ( `col1` ) VALUES (?) | insert into minjae_test1 (col1) values(5) |
| 93 | statement/sql/insert | INSERT INTO `minjae_test2` ( `col1` ) VALUES (?) | insert into minjae_test2 (col1) values(1) |
| 93 | statement/sql/insert | INSERT INTO `minjae_test2` ( `col1` ) VALUES (?) | insert into minjae_test2 (col1) values(2) |
| 93 | statement/sql/insert | INSERT INTO `minjae_test2` ( `col1` ) VALUES (?) | insert into minjae_test2 (col1) values(3) |
| 93 | statement/sql/insert | INSERT INTO `minjae_test2` ( `col1` ) VALUES (?) | insert into minjae_test2 (col1) values(4) |
| 93 | statement/sql/insert | INSERT INTO `minjae_test2` ( `col1` ) VALUES (?) | insert into minjae_test2 (col1) values(5) |
| 94 | statement/sql/insert | INSERT INTO `minjae_test3` ( `col1` ) VALUES (?) | insert into minjae_test3 (col1) values(1) |
| 94 | statement/sql/insert | INSERT INTO `minjae_test3` ( `col1` ) VALUES (?) | insert into minjae_test3 (col1) values(2) |
| 94 | statement/sql/insert | INSERT INTO `minjae_test3` ( `col1` ) VALUES (?) | insert into minjae_test3 (col1) values(3) |
| 94 | statement/sql/insert | INSERT INTO `minjae_test3` ( `col1` ) VALUES (?) | insert into minjae_test3 (col1) values(4) |
| 94 | statement/sql/insert | INSERT INTO `minjae_test3` ( `col1` ) VALUES (?) | insert into minjae_test3 (col1) values(5) |
| 95 | statement/sql/insert | INSERT INTO `minjae_test4` ( `col1` ) VALUES (?) | insert into minjae_test4 (col1) values(1) |
| 95 | statement/sql/insert | INSERT INTO `minjae_test4` ( `col1` ) VALUES (?) | insert into minjae_test4 (col1) values(2) |
| 95 | statement/sql/insert | INSERT INTO `minjae_test4` ( `col1` ) VALUES (?) | insert into minjae_test4 (col1) values(3) |
| 95 | statement/sql/insert | INSERT INTO `minjae_test4` ( `col1` ) VALUES (?) | insert into minjae_test4 (col1) values(4) |
| 95 | statement/sql/insert | INSERT INTO `minjae_test4` ( `col1` ) VALUES (?) | insert into minjae_test4 (col1) values(5) |
| 96 | statement/sql/insert | INSERT INTO `minjae_test5` ( `col1` ) VALUES (?) | insert into minjae_test5 (col1) values(1) |
| 96 | statement/sql/insert | INSERT INTO `minjae_test5` ( `col1` ) VALUES (?) | insert into minjae_test5 (col1) values(2) |
| 96 | statement/sql/insert | INSERT INTO `minjae_test5` ( `col1` ) VALUES (?) | insert into minjae_test5 (col1) values(3) |
| 96 | statement/sql/insert | INSERT INTO `minjae_test5` ( `col1` ) VALUES (?) | insert into minjae_test5 (col1) values(4) |
| 96 | statement/sql/insert | INSERT INTO `minjae_test5` ( `col1` ) VALUES (?) | insert into minjae_test5 (col1) values(5) |
+-----------+----------------------+--------------------------------------------------+-------------------------------------------+
25 rows in set (0.002 sec)
user, account size 설정이 digest summary 등의 수집에는 영향을 주지 않지만 performance schema 관리를 수동으로 하면 performance insight에서 얻을 수 있는 정보가 제한적이 되므로 사실상 무의미함
각 테이블은 주기적으로 truncate 하여 메모리 관리가 가장 합리적인 방안으로 보임.
다만 각 테이블을 TRUNCATE 하여도 Performance_Schema의 특성상 각 ROW가 DELETE 되진 않고 할당된 통계 값만 초기화되는 것으로 확인.
Summary 테이블의 경우 상위 레벨의 테이블을 TRUNCATE 하면 현재 연결된 USER, HOST, THREAD 를 제외하고는 DELETE가 진행이 되어 해당 부분으로 데이터는 DELETE 가능
Rows are removed for accounts, hosts, or users that have no current connections (rows with CURRENT_CONNECTIONS = 0).
Nonremoved rows are reset to count only current connections: For rows with CURRENT_CONNECTIONS > 0, TOTAL_CONNECTIONS is reset to CURRENT_CONNECTIONS. Summary tables that depend on the connection table are implicitly truncated.
TRUNCATE TABLE is permitted for connection summary tables. It removes rows for accounts, hosts, or users with no connections, and resets the summary columns to zero for the remaining rows.
In addition, each summary table that is aggregated by account, host, user, or thread is implicitly truncated by truncation of the connection table on which it depends.
각 connection 테이블 TRUNCATE 시 암묵적으로 정리되는 summary 테이블의 관계는 아래와 같습니다
- TRUNCATE accounts → summaryby_account, summaryby_thread 계열 테이블 정리
- TRUNCATE hosts → summaryby_account, summaryby_host, summaryby_thread 계열 테이블 정리
- TRUNCATE users → summaryby_account, summaryby_user, summaryby_thread 계열 테이블 정리
하지만 Perofmance Schema가 한번 할당한 메모리를 OS로 반납하는 과정이 진행되진 않아서...
결과적으로 아래 3개 테이블을 주기적으로 TRUNCATE 하는 EVENT SCHEDULER를 돌리면서 인스턴스 재부팅이 1회 필요 (클러스터 구축 초기에 추가되면 좋을 것으로 보임)
--아래는 이벤트 스케쥴러 설정과 각 테이블 TRUNCATE 테스트 내용
-- 이벤트 스케쥴러
DELIMITER $$
CREATE EVENT truncate_performance_schema_summary_event
ON SCHEDULE EVERY 1 HOUR
STARTS '2026-03-30 23:30:00'
COMMENT '주기적인 performance_schema 특정 summary 테이블 초기화'
DO
BEGIN
TRUNCATE TABLE performance_schema.accounts;
TRUNCATE TABLE performance_schema.hosts;
TRUNCATE TABLE performance_schema.users;
END$$
DELIMITER ;
--삭제 테스트
select count(*) from events_errors_summary_by_account_by_error where USER = '서비스 계정' ;
+----------+
| count(*) |
+----------+
| 756045 |
+----------+
1 row in set (0.89 sec)
select count(*) from events_statements_summary_by_digest;
+----------+
| count(*) |
+----------+
| 228 |
+----------+
1 row in set (0.04 sec)
TRUNCATE TABLE performance_schema.accounts;
Query OK, 0 rows affected (0.13 sec)
TRUNCATE TABLE performance_schema.hosts;
Query OK, 0 rows affected (0.10 sec)
TRUNCATE TABLE performance_schema.users;
Query OK, 0 rows affected (0.04 sec)
select count(*) from events_errors_summary_by_account_by_error where USER = '서비스 계정' ;
+----------+
| count(*) |
+----------+
| 6340 |
+----------+
1 row in set (0.04 sec)
select count(*) from events_statements_summary_by_digest;
+----------+
| count(*) |
+----------+
| 232 |
+----------+
1 row in set (0.03 sec)
Aurora Error Log
아래 로그는 가용 메모리가 특정 임계값 이하로 감소하였을 Aurora MySQL 엔진에서 자동으로 생성하는 로그.
OS의 메모리 사용 현황과 DB 커넥션에서 사용하고 있는 메모리양, 각 구성 요소의 메모리 사용 현황을 로깅함.
확인할 수 있는 구성 요소는 다음과 같습니다
SQL 엔진 메모리 (memory/sql/)
| 항목 | 설명 |
| Filesort_info::record_pointers | filesort 수행 시 레코드 포인터 저장 버퍼. |
| Filesort_info::merge | filesort에서 청크 병합 시 사용하는 버퍼. |
| Filesort_buffer::sort_keys | filesort 키 정렬 버퍼. ORDER BY/GROUP BY 시 사용 |
| bison_stack | SQL 파싱 시 문법 분석용 스택 메모리. |
| Partition::prune_exec | 파티션 프루닝 실행 중 사용하는 임시 메모리 |
| test_quick_select | 인덱스 레인지 스캔 최적 경로 선택 시 사용하는 분석용 메모리 |
| READ_INFO | LOAD DATA INFILE 등 파일 읽기 작업 시 사용하는 버퍼 |
| Unique::merge_buffer | DISTINCT/GROUP BY 처리 시 중복 제거를 위한 병합 버퍼 |
| Unique::sort_buffer | DISTINCT/GROUP BY 처리 시 정렬 버퍼 |
| hash_join | Hash Join 수행 시 해시 테이블 구성에 사용하는 메모리 |
| Blob_mem_storage::storage | BLOB/TEXT 컬럼 임시 저장 버퍼 |
| TABLE::sort_io_cache | 테이블 정렬 시 I/O 캐시 버퍼 |
| IndexRangeScanIterator::mrr_buf_desc | MRR(Multi-Range Read) 최적화 시 사용하는 버퍼 디스크립터 |
| sp_head::execute_mem_root | 저장 프로시저 실행 중 사용하는 메모리 풀 |
| sp_head::call_mem_root | 저장 프로시저 호출 스택 메모리 풀 |
| THD::sp_cache | 스레드별 저장 프로시저 캐시 |
| Prepared_statement::infrastructure | Prepared Statement 관리 구조체 메모리. Prepared Statement가 활성화되어 있음을 의미 |
| Prepared_statement::main_mem_root | Prepared Statement 실행에 필요한 파싱 트리, 바인딩 정보 등 저장 메모리 |
| THD::main_mem_root | 스레드(세션) 전체 생명주기 동안 사용하는 메인 메모리 풀. 쿼리 파싱 |
TempTable 엔진 메모리 (memory/temptable/)
| 항목 | 설명 |
| physical_disk | 임시 테이블이 디스크로 넘어간 크기. |
| physical_ram | 임시 테이블이 RAM에서 사용 중인 크기. 서브쿼리, GROUP BY, UNION 등의 중간 결과 저장 |
로그 일부
{
"report_time": "2026-02-10 23:40:45 UTC",
"report_version": "1.0",
"system_report": {
"total_mem_kb": 8022360,
"free_mem_kb": 190232,
"swap_total_kb": 8388604,
"swap_used_kb": 1149384,
"swap_in_change": 0,
"swap_out_change": 0
},
"connections_report": [
{
"id": 6895192,
"used_bytes": 8240,
"max_used_bytes": 167424,
"components_report": [
{
"name": "memory/sql/THD::main_mem_root",
"used_bytes": 8240,
"max_used_bytes": 146792
},
{
"name": "memory/sql/Prepared_statement::main_mem_root",
"used_bytes": 0,
"max_used_bytes": 20576
},
{
"name": "memory/sql/test_quick_select",
"used_bytes": 0,
"max_used_bytes": 4144
},
{
"name": "memory/sql/TABLE::sort_io_cache",
"used_bytes": 0,
"max_used_bytes": 408
},
{
"name": "memory/sql/Prepared_statement::infrastructure",
"used_bytes": 0,
"max_used_bytes": 56
}
]
},
{
"id": 6895168,
"used_bytes": 8240,
"max_used_bytes": 93840,
"components_report": [
{
"name": "memory/sql/THD::main_mem_root",
"used_bytes": 8240,
"max_used_bytes": 54728
},
{
"name": "memory/sql/Prepared_statement::main_mem_root",
"used_bytes": 0,
"max_used_bytes": 39056
},
{
"name": "memory/sql/test_quick_select",
"used_bytes": 0,
"max_used_bytes": 4144
},
{
"name": "memory/sql/Prepared_statement::infrastructure",
"used_bytes": 0,
"max_used_bytes": 56
},
{
"name": "memory/temptable/physical_ram",
"used_bytes": 0,
"max_used_bytes": 0
}
]
},
...
"components_report": [
{
"key": "memory/sql/Filesort_info::record_pointers",
"value": 0
},
{
"key": "memory/sql/bison_stack",
"value": 0
},
{
"key": "memory/sql/Filesort_info::merge",
"value": 0
},
{
"key": "memory/sql/Partition::prune_exec",
"value": 0
},
{
"key": "memory/sql/test_quick_select",
"value": 0
},
{
"key": "memory/sql/READ_INFO",
"value": 0
},
{
"key": "memory/sql/Unique::merge_buffer",
"value": 0
},
{
"key": "memory/sql/Prepared_statement::infrastructure",
"value": 280
},
{
"key": "memory/temptable/physical_disk",
"value": 0
},
{
"key": "memory/sql/hash_join",
"value": 0
},
{
"key": "memory/sql/sp_head::execute_mem_root",
"value": 0
},
{
"key": "memory/sql/Unique::sort_buffer",
"value": 0
},
{
"key": "memory/temptable/physical_ram",
"value": 3145824
},
{
"key": "memory/sql/Blob_mem_storage::storage",
"value": 0
},
{
"key": "memory/sql/TABLE::sort_io_cache",
"value": 0
},
{
"key": "memory/sql/THD::main_mem_root",
"value": 137792
},
{
"key": "memory/sql/Filesort_buffer::sort_keys",
"value": 0
},
{
"key": "memory/sql/Prepared_statement::main_mem_root",
"value": 158320
},
{
"key": "memory/sql/THD::sp_cache",
"value": 0
},
{
"key": "memory/sql/IndexRangeScanIterator::mrr_buf_desc",
"value": 0
},
{
"key": "memory/sql/sp_head::call_mem_root",
"value": 0
}
]
}
{
"report_time": "2026-03-10 23:48:27 UTC",
"report_version": "1.0",
"system_report": {
"total_mem_kb": 8022360,
"free_mem_kb": 132240,
"swap_total_kb": 8388604,
"swap_used_kb": 896908,
"swap_in_change": 0,
"swap_out_change": 0
},
"connections_report": [
{
"id": 7378377,
"used_bytes": 8240,
"max_used_bytes": 89744,
"components_report": [
{
"name": "memory/sql/THD::main_mem_root",
"used_bytes": 8240,
"max_used_bytes": 50632
},
{
"name": "memory/sql/Prepared_statement::main_mem_root",
"used_bytes": 0,
"max_used_bytes": 39056
},
{
"name": "memory/sql/test_quick_select",
"used_bytes": 0,
"max_used_bytes": 4144
},
{
"name": "memory/sql/Prepared_statement::infrastructure",
"used_bytes": 0,
"max_used_bytes": 56
},
{
"name": "memory/temptable/physical_ram",
"used_bytes": 0,
"max_used_bytes": 0
}
]
},
{
"id": 7378352,
"used_bytes": 8240,
"max_used_bytes": 167424,
"components_report": [
{
"name": "memory/sql/THD::main_mem_root",
"used_bytes": 8240,
"max_used_bytes": 146792
},
{
"name": "memory/sql/Prepared_statement::main_mem_root",
"used_bytes": 0,
"max_used_bytes": 20576
},
{
"name": "memory/sql/test_quick_select",
"used_bytes": 0,
"max_used_bytes": 4144
},
{
"name": "memory/sql/TABLE::sort_io_cache",
"used_bytes": 0,
"max_used_bytes": 408
},
{
"name": "memory/sql/Prepared_statement::infrastructure",
"used_bytes": 0,
"max_used_bytes": 56
}
]
},
...
"components_report": [
{
"key": "memory/sql/Filesort_info::record_pointers",
"value": 0
},
{
"key": "memory/sql/bison_stack",
"value": 0
},
{
"key": "memory/sql/Filesort_info::merge",
"value": 0
},
{
"key": "memory/sql/Partition::prune_exec",
"value": 0
},
{
"key": "memory/sql/test_quick_select",
"value": 0
},
{
"key": "memory/sql/READ_INFO",
"value": 0
},
{
"key": "memory/sql/Unique::merge_buffer",
"value": 0
},
{
"key": "memory/sql/Prepared_statement::infrastructure",
"value": 280
},
{
"key": "memory/temptable/physical_disk",
"value": 0
},
{
"key": "memory/sql/hash_join",
"value": 0
},
{
"key": "memory/sql/sp_head::execute_mem_root",
"value": 0
},
{
"key": "memory/sql/Unique::sort_buffer",
"value": 0
},
{
"key": "memory/temptable/physical_ram",
"value": 3145824
},
{
"key": "memory/sql/Blob_mem_storage::storage",
"value": 0
},
{
"key": "memory/sql/TABLE::sort_io_cache",
"value": 0
},
{
"key": "memory/sql/THD::main_mem_root",
"value": 160368
},
{
"key": "memory/sql/Filesort_buffer::sort_keys",
"value": 0
},
{
"key": "memory/sql/Prepared_statement::main_mem_root",
"value": 158320
},
{
"key": "memory/sql/THD::sp_cache",
"value": 0
},
{
"key": "memory/sql/IndexRangeScanIterator::mrr_buf_desc",
"value": 0
},
{
"key": "memory/sql/sp_head::call_mem_root",
"value": 0
}
]
}
Aurora의 OOM 관련 파라미터
aurora_oom_response 파라미터로 MySQL이 OOM 관련 압박을 받을 때 동작을 제어할 수 있습니다.
| 옵션 | 동작 방식 | 영향 범위 | 특징 |
| decline | 새 쿼리 요청 거부 | 신규 쿼리 | 기존 쿼리는 유지, 추가 부하 차단 |
| kill_connect | 메모리 많이 쓰는 연결 종료 | 세션(연결) 전체 | 트랜잭션 + DDL까지 강제 종료 |
| kill_query | 메모리 많이 쓰는 쿼리 종료 | 쿼리 단위 | DDL은 종료 안 함 |
| 메모리 많이 쓰는 쿼리 출력만 수행 | 없음 (모니터링) | 실제 조치는 없음, 디버깅용 | |
| tune | 내부 캐시 크기 축소 | table_open_cache, table_definition_cache |
자동으로 줄였다가 정상 시 복구 |
| tune_buffer_pool | 버퍼 풀 크기 축소 | InnoDB buffer pool | 메모리 확보 효과 큼 |
각 운영 환경에 맞는 옵션을 설정하면 도움이 될 것으로 보인다. 물론 지표 모니터링을 통해 OOM 이 발생하지 않는 상황을 만드는 것이 우선이다
※
3.06 미만의 Aurora MySQL 버전에서 메모리가 4GiB보다 큰 DB 인스턴스 클래스의 경우, 파라미터 값은 기본적으로 비어 있습니다(비활성화).
3.06 이상에서는 메모리가 4GiB보다 큰 DB 인스턴스 클래스의 경우, 기본 파라미터 값은 print입니다.
3.09 이상에서 메모리가 4GiB보다 큰 DB 인스턴스 클래스의 경우, 기본 파라미터 값은 print,decline,kill_connect입니다.
즉 3.09 이상에서는 기본 설정이 REBOOT 되는 상황을 막아줌.
event scheduler 설정
DELIMITER $$
CREATE EVENT truncate_performance_schema_summary_event
ON SCHEDULE EVERY 1 HOUR
STARTS '2026-03-30 23:30:00'
COMMENT '주기적인 performance_schema 특정 summary 테이블 초기화'
DO
BEGIN
TRUNCATE TABLE performance_schema.accounts;
TRUNCATE TABLE performance_schema.hosts;
TRUNCATE TABLE performance_schema.users;
END$$
DELIMITER ;
'공부 > DATABASE' 카테고리의 다른 글
| [AWS DocumentDB] 간헐적 입력 지연 이슈 (0) | 2026.03.07 |
|---|---|
| [Aurora PostgreSQL] VACUUM TRUNCATE (0) | 2026.03.07 |
| Jenkins 로 Terraform 호출하여 AWS RDS 생성하기 (0) | 2026.02.14 |
| [PostgreSQL, MySQL] DBMS에서 인데스 조회 중 I/O 줄이는 방식 (0) | 2026.02.01 |
| [Aurora PostgreSQL] 테이블 DROP (0) | 2026.02.01 |