파라미터 튜닝 기준: innodb_buffer_pool_size부터 redo, flush, temp까지

파라미터 튜닝은 권장 설정값을 복사하는 작업이 아니다. 읽기 캐시 부족, redo 공간 부족, 커밋 시 동기화 지연, 임시 테이블 메모리 부족은 모두 응답 시간 증가로 나타날 수 있지만 해결해야 할 자원과 위험은 다르다. 잘못된 변수를 키우면 병목을 옮기거나 장애 여유만 줄인다.

이 글은 메모리 예산 → 읽기 경로 → redo와 페이지 flush → 임시 작업 공간 → 변경 검증 순서로 판단 기준을 설명한다. 기본 대상은 Community MySQL 8.0.30 이상이며, 8.4에서도 적용할 수 있는 원칙과 버전별 확인 사항을 구분한다. SQL은 폐기용 MySQL 8.0 인스턴스에서 실행한 진단·세션 설정 예제다. 출력 수치는 작은 테스트 인스턴스의 관측값이지 운영 권장값이 아니다. 부하 시험, 장애 복구 시간, Aurora 실행 성능은 측정하지 않았다.

1. 먼저 연결해야 하는 네 가지 자원

InnoDB는 자주 읽는 데이터·인덱스 페이지를 Buffer Pool에 보관한다. 변경은 메모리의 페이지를 dirty 상태로 만들고 redo를 생성한다. 커밋의 내구성을 위한 로그 기록과 dirty page를 데이터 파일로 내보내는 작업은 서로 연관되지만 같은 작업은 아니다. 일반적인 커밋마다 해당 데이터 페이지를 모두 디스크에 쓰는 구조도 아니다.

flowchart TD
    Q[업무 SQL과 동시 실행량] --> B[Buffer Pool에서 페이지 접근]
    B -->|캐시 미스| R[데이터 페이지 읽기]
    B -->|변경| D[Dirty page]
    B -->|변경 기록| L[Redo log buffer]
    L --> F[로그 기록과 내구성 동기화]
    D --> P[페이지 flush와 checkpoint 진전]
    P --> U[오래된 redo 공간 재사용]
    Q --> T[정렬·집계·중간 결과]
    T --> M[TempTable 메모리]
    M --> S[설정에 따른 임시 디스크 공간]

여기서 Buffer Pool은 주로 캐시와 변경 페이지의 메모리 공간, redo capacity는 재사용 전 로그를 유지할 수 있는 디스크 공간, I/O capacity는 백그라운드 작업의 처리 속도 기준, 임시 테이블 한도는 중간 결과의 자원 사용 제한이다. 크기·속도·내구성이라는 서로 다른 축을 같은 숫자 조정으로 취급하면 안 된다.

실무에서는 먼저 업무 p95/p99 지연, 처리량, 오류율, 동시 실행 수, CPU, 메모리 압력, I/O 지연을 같은 시간축에 놓는다. SQL 실행 계획이나 잠금 대기가 원인이라면 파라미터 확대보다 접근 행 수와 트랜잭션 길이를 줄이는 편이 먼저다.

2. 현재값을 기록하고 메모리 예산을 세운다

다음 쿼리는 튜닝을 시작할 때 확보할 최소 구성 정보다. innodb_redo_log_capacity는 MySQL 8.0.30부터 도입되었다. 조회 목록에 행이 없으면 값을 0으로 해석하지 말고 버전과 제공 여부를 확인한다.

SELECT VERSION() AS mysql_version;
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN (
  'innodb_buffer_pool_size', 'innodb_log_buffer_size',
  'innodb_redo_log_capacity', 'innodb_io_capacity',
  'innodb_io_capacity_max', 'innodb_flush_log_at_trx_commit',
  'sync_binlog', 'log_bin'
)
ORDER BY VARIABLE_NAME;

실행 결과(MySQL 8.0.x):

mysql> SELECT VERSION() AS mysql_version;

+---------------+
| mysql_version |
+---------------+
| 8.0.46        |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT VARIABLE_NAME, VARIABLE_VALUE
    -> FROM performance_schema.global_variables
    -> WHERE VARIABLE_NAME IN (
    ->   'innodb_buffer_pool_size', 'innodb_log_buffer_size',
    ->   'innodb_redo_log_capacity', 'innodb_io_capacity',
    ->   'innodb_io_capacity_max', 'innodb_flush_log_at_trx_commit',
    ->   'sync_binlog', 'log_bin'
    -> )
    -> ORDER BY VARIABLE_NAME;

+--------------------------------+----------------+
| VARIABLE_NAME                  | VARIABLE_VALUE |
+--------------------------------+----------------+
| innodb_buffer_pool_size        | 67108864       |
| innodb_flush_log_at_trx_commit | 1              |
| innodb_io_capacity             | 200            |
| innodb_io_capacity_max         | 2000           |
| innodb_log_buffer_size         | 16777216       |
| innodb_redo_log_capacity       | 104857600      |
| log_bin                        | OFF            |
| sync_binlog                    | 1              |
+--------------------------------+----------------+
8 rows in set (0.01 sec)

설정 파일에 적힌 값, 실행 중인 전역값, 이미 연결된 세션값, 재시작 후 적용될 값은 다를 수 있다. 변경 요청서에는 이 네 층을 구분해 기록한다. 테스트 출력의 log_bin=OFF는 검증 인스턴스의 구성이다. 이 상태에서 sync_binlog 숫자를 읽었다고 binlog 내구성이 검증된 것은 아니다.

메모리 예산은 호스트의 전체 RAM보다 mysqld가 실제 사용할 수 있는 한도를 기준으로 한다. 컨테이너라면 cgroup 한도가 더 낮을 수 있다. 다음 항목을 함께 남겨야 한다.

• Buffer Pool과 그 밖의 InnoDB 메모리, 로그 버퍼
• Performance Schema, 테이블·메타데이터 캐시, 연결 관리 메모리
• 동시 실행 SQL의 정렬·조인·읽기 버퍼와 임시 테이블
• 운영체제, 파일 캐시, 백업·수집 에이전트, 다른 프로세스의 몫
• 순간적인 트래픽과 작업량 변화를 흡수할 여유

max_connections × 모든 세션 버퍼의 설정값은 실제 상시 할당량이 아니다. 버퍼는 작업에 따라 할당되고 한 문장 안에서도 여러 연산이 필요할 수 있다. 반대로 평시 RSS만 보고 여유가 충분하다고 판단하면 배치와 접속 급증이 겹칠 때 OOM을 놓친다. 평시·피크·장애 시 재접속 폭증을 나누어 측정한다.

3. innodb_buffer_pool_size: 적중률보다 절대 읽기량과 여유 메모리

Buffer Pool을 키우는 후보 조건은 반복 접근하는 작업 집합이 캐시에서 자주 밀려나고, 실제 읽기 I/O가 지연에 기여하며, 다른 메모리를 침범하지 않고 확장할 수 있는 경우다. 테이블 전체가 RAM에 들어가야 한다는 뜻은 아니다. 읽지 않는 과거 데이터까지 캐시 크기의 목표에 포함할 필요는 없다.

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
  'Uptime', 'Innodb_buffer_pool_read_requests',
  'Innodb_buffer_pool_reads', 'Innodb_buffer_pool_wait_free',
  'Innodb_buffer_pool_pages_total', 'Innodb_buffer_pool_pages_free',
  'Innodb_buffer_pool_pages_dirty', 'Innodb_pages_written'
)
ORDER BY VARIABLE_NAME;

실행 결과(MySQL 8.0.x):

mysql> SELECT VARIABLE_NAME, VARIABLE_VALUE
    -> FROM performance_schema.global_status
    -> WHERE VARIABLE_NAME IN (
    ->   'Uptime', 'Innodb_buffer_pool_read_requests',
    ->   'Innodb_buffer_pool_reads', 'Innodb_buffer_pool_wait_free',
    ->   'Innodb_buffer_pool_pages_total', 'Innodb_buffer_pool_pages_free',
    ->   'Innodb_buffer_pool_pages_dirty', 'Innodb_pages_written'
    -> )
    -> ORDER BY VARIABLE_NAME;

+----------------------------------+----------------+
| VARIABLE_NAME                    | VARIABLE_VALUE |
+----------------------------------+----------------+
| Innodb_buffer_pool_pages_dirty   | 0              |
| Innodb_buffer_pool_pages_free    | 2931           |
| Innodb_buffer_pool_pages_total   | 4096           |
| Innodb_buffer_pool_read_requests | 15398          |
| Innodb_buffer_pool_reads         | 1017           |
| Innodb_buffer_pool_wait_free     | 0              |
| Innodb_pages_written             | 191            |
| Uptime                           | 7              |
+----------------------------------+----------------+
8 rows in set (0.01 sec)

이 쿼리를 동일 서버에서 두 번 수집해 구간 차이를 계산한다. 누적값의 서버 기동 이후 평균은 최근 피크를 숨긴다. 재시작이나 카운터 초기화가 있으면 이전 표본과의 차이는 버린다.

• 구간 논리 읽기 대비 디스크 읽기 비율은 ΔInnodb_buffer_pool_reads / ΔInnodb_buffer_pool_read_requests로 본다. 분모가 0이면 비율을 계산하지 않는다.
• 같은 비율이어도 요청량이 크면 절대 디스크 읽기량은 클 수 있다. ΔInnodb_buffer_pool_reads / 관측 초와 스토리지 지연을 함께 본다. 이 수치를 장치의 실제 IOPS와 완전히 동일시하지 않는다.
• Innodb_buffer_pool_wait_free 증가에는 사용 가능한 페이지를 확보하지 못하는 문제가 담긴다. 단순 캐시 크기 부족뿐 아니라 dirty page를 제때 내보내지 못하는 상황도 점검한다.
• pages_free가 작다는 사실만으로 부족을 판정하지 않는다. 캐시가 가득 차서 재사용되는 것은 정상일 수 있다. dirty page 비율은 누적 카운터 차이가 아니라 같은 시점의 상태값으로 계산한다.

대형 순차 스캔이나 잘못된 조인으로 읽는 행이 늘었다면 캐시 확대가 근본 대책이 아닐 수 있다. 변경 전후에 동일한 쿼리 혼합과 캐시 준비 상태를 맞추고 읽기 지연·메모리 압력·업무 지연이 함께 개선되는지 확인한다. 동적 크기 변경도 무비용 작업이 아니다. 적용 단위와 진행 상태를 확인하고, 축소 과정의 지연과 이후 캐시 미스를 원복 계획에 포함한다.

4. redo capacity와 log buffer를 구분한다

innodb_redo_log_capacity는 로그 파일의 전체 용량을 제어한다. 로그 생성 위치가 앞으로 가더라도 checkpoint가 충분히 진전되지 않으면 오래된 로그 공간을 재사용할 수 없다. 용량이 작으면 짧은 쓰기 급증에도 페이지 flush 압력이 커진다. 더 큰 용량은 완충 공간을 늘릴 수 있지만 저장장치가 지속적인 변경량을 감당하지 못하는 문제까지 해결하지는 않는다.

innodb_log_buffer_size는 redo를 파일로 기록하기 전의 메모리 버퍼다. 큰 트랜잭션이 버퍼 공간을 자주 소진하는 문제와, checkpoint가 뒤처져 로그 파일 공간이 압박받는 문제는 구분해야 한다. Innodb_log_waits는 로그 버퍼 공간 부족으로 flush를 기다린 횟수이며 모든 커밋 지연이나 redo capacity 부족을 대표하지 않는다.

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
  'Innodb_os_log_written', 'Innodb_log_waits',
  'Innodb_redo_log_current_lsn', 'Innodb_redo_log_checkpoint_lsn',
  'Innodb_redo_log_capacity_resized'
)
ORDER BY VARIABLE_NAME;

실행 결과(MySQL 8.0.x):

mysql> SELECT VARIABLE_NAME, VARIABLE_VALUE
    -> FROM performance_schema.global_status
    -> WHERE VARIABLE_NAME IN (
    ->   'Innodb_os_log_written', 'Innodb_log_waits',
    ->   'Innodb_redo_log_current_lsn', 'Innodb_redo_log_checkpoint_lsn',
    ->   'Innodb_redo_log_capacity_resized'
    -> )
    -> ORDER BY VARIABLE_NAME;

+----------------------------------+----------------+
| VARIABLE_NAME                    | VARIABLE_VALUE |
+----------------------------------+----------------+
| Innodb_log_waits                 | 0              |
| Innodb_os_log_written            | 66048          |
| Innodb_redo_log_capacity_resized | 104857600      |
| Innodb_redo_log_checkpoint_lsn   | 29632628       |
| Innodb_redo_log_current_lsn      | 29632628       |
+----------------------------------+----------------+
5 rows in set (0.00 sec)

current_lsn - checkpoint_lsn은 checkpoint가 따라잡아야 할 범위를 이해하는 데 유용하다. 다만 상태 조회는 모든 값이 한 순간에 고정되는 원자적 스냅샷이 아니므로 짧은 순간의 작은 차이를 정밀한 경계처럼 쓰지 않는다. 실제 사용 가능 공간과 안전 여유도 있으므로 설정 용량에서 이 차이를 뺀 값을 곧바로 “가득 차기까지 남은 정확한 바이트”라고 부르지 않는다.

ΔInnodb_os_log_written / 관측 초는 redo 기록량의 구간 평균을 보는 출발점이다. 평시뿐 아니라 배치와 대형 변경 시간대를 포함한다. 용량을 늘렸다면 Innodb_redo_log_capacity_resized로 실제 크기 변경의 진전을 확인한다. 설정값 변경 성공만으로 파일 조정이 즉시 끝났다고 단정하지 않는다.

큰 redo가 항상 복구 시간을 일정 비율로 늘리는 것은 아니다. 장애 당시 미처리 로그 범위와 복구 I/O 성능이 중요하다. 따라서 용량 변경은 디스크 여유, 피크 쓰기 지연, checkpoint 동작, 별도로 측정한 crash recovery 시간을 함께 검토하는 작업이다. 이 글의 상태 조회는 crash recovery 시험을 대신하지 않는다.

5. flush 튜닝은 페이지 쓰기와 커밋 내구성을 나누어 판단한다

5.1 백그라운드 페이지 쓰기

innodb_io_capacity는 InnoDB의 백그라운드 I/O 작업에 사용할 처리 능력 기준이며, innodb_io_capacity_max는 더 적극적인 flush가 필요할 때의 상한 설정에 관여한다. 두 값은 장치 전체 I/O를 제한하는 엄격한 QoS 스위치가 아니다. 동기 flush 등 다른 경로와 설정의 영향도 받는다.

너무 낮으면 dirty page와 checkpoint 지연이 쌓여 뒤늦게 강한 flush를 유발할 수 있다. 너무 높으면 foreground 읽기와 경쟁하고, 더 많은 변경을 모아서 쓸 기회를 줄일 수 있다. 장치 사양서의 최대 IOPS를 그대로 입력하지 말고 실제 지연 목표를 지키면서 지속 가능한 쓰기 처리량을 기준으로 검증한다.

dirty 비율 하나로 결론을 내리지 않는다. checkpoint 차이의 추세, 페이지 쓰기량, 읽기 지연, 스토리지 queue, 업무 p99를 연결한다. 급격한 쓰기 증가가 잠시 뒤 해소되는지, 처리 능력을 초과해 계속 누적되는지도 분리한다. innodb_flush_method 변경은 파일시스템·스토리지·운영체제 조합과 재시작 조건에 영향을 받으므로 일반 처방으로 복사하지 않는다.

5.2 커밋 시 로그 동기화

Community MySQL에서 innodb_flush_log_at_trx_commit=1은 각 커밋의 redo를 기록·flush하는 내구성 기준이다. 2는 커밋 시 파일 쓰기와 주기적인 flush를 분리하며, 0은 로그 기록과 flush를 주기적으로 수행한다. 주기 실행은 정확한 시간 보장이 아니며 프로세스 장애와 운영체제·전원 장애의 손실 범위도 다르다.

binlog를 사용하면 sync_binlog와 함께 검토해야 한다. 일반적으로 innodb_flush_log_at_trx_commit=1, sync_binlog=1을 내구성 기준으로 출발하되, 스토리지의 flush 보장과 장애 복구 시험을 별도로 확인한다. Group commit 때문에 커밋 수와 장치 동기화 횟수가 반드시 일대일인 것은 아니다.

이 값을 완화해 처리량이 올랐다면 “공짜 성능 개선”이 아니라 장애 시 손실 위험을 교환했을 수 있다. 승인된 RPO 없이 조정하지 않는다. 로그 capacity를 늘리는 것과 커밋 동기화를 생략하는 것은 전혀 다른 변경이다.

6. temp 튜닝: 엔진과 한도의 적용 단위부터 확인한다

MySQL 8.0의 내부 임시 테이블에서는 TempTable과 MEMORY의 규칙을 혼동하기 쉽다. 정렬·집계·파생 테이블 등이 중간 결과를 요구하지만, 모든 정렬이 내부 임시 테이블을 만드는 것도 아니며 모든 임시 테이블이 같은 공간을 쓰는 것도 아니다.

SELECT @@session.internal_tmp_mem_storage_engine AS internal_engine,
       @@session.tmp_table_size AS per_table_bytes,
       @@session.max_heap_table_size AS memory_table_bytes;
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN ('temptable_max_ram', 'temptable_max_mmap')
ORDER BY VARIABLE_NAME;
SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED AS current_bytes,
       HIGH_NUMBER_OF_BYTES_USED AS high_bytes
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME IN ('memory/temptable/physical_ram',
                     'memory/temptable/physical_disk')
ORDER BY EVENT_NAME;

실행 결과(MySQL 8.0.x):

mysql> SELECT @@session.internal_tmp_mem_storage_engine AS internal_engine,
    ->        @@session.tmp_table_size AS per_table_bytes,
    ->        @@session.max_heap_table_size AS memory_table_bytes;

+-----------------+-----------------+--------------------+
| internal_engine | per_table_bytes | memory_table_bytes |
+-----------------+-----------------+--------------------+
| TempTable       |        16777216 |           16777216 |
+-----------------+-----------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT VARIABLE_NAME, VARIABLE_VALUE
    -> FROM performance_schema.global_variables
    -> WHERE VARIABLE_NAME IN ('temptable_max_ram', 'temptable_max_mmap')
    -> ORDER BY VARIABLE_NAME;

+--------------------+----------------+
| VARIABLE_NAME      | VARIABLE_VALUE |
+--------------------+----------------+
| temptable_max_mmap | 1073741824     |
| temptable_max_ram  | 1073741824     |
+--------------------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED AS current_bytes,
    ->        HIGH_NUMBER_OF_BYTES_USED AS high_bytes
    -> FROM performance_schema.memory_summary_global_by_event_name
    -> WHERE EVENT_NAME IN ('memory/temptable/physical_ram',
    ->                      'memory/temptable/physical_disk')
    -> ORDER BY EVENT_NAME;

+--------------------------------+---------------+------------+
| EVENT_NAME                     | current_bytes | high_bytes |
+--------------------------------+---------------+------------+
| memory/temptable/physical_disk |             0 |          0 |
| memory/temptable/physical_ram  |             0 |          0 |
+--------------------------------+---------------+------------+
2 rows in set (0.01 sec)

판단 기준은 다음과 같다.

1. Community MySQL 8.0.28 이상 TempTable: tmp_table_size는 개별 내부 메모리 임시 테이블의 한도다. TempTable 전체의 RAM 사용에는 temptable_max_ram, mmap 공간에는 temptable_max_mmap이 별도로 관여한다. max_heap_table_size를 TempTable 한도로 적용하지 않는다.
2. MEMORY 기반 내부 임시 테이블: tmp_table_size와 max_heap_table_size 중 작은 값이 메모리 테이블 한도다.
3. 직접 만든 MEMORY 테이블: max_heap_table_size가 크기 제한을 제어한다. 내부 임시 테이블처럼 자동으로 디스크 테이블로 전환되지 않는다.
4. 전체 메모리 예산: temptable_max_ram에 모든 관련 할당이 포함되는 것은 아니다. TempTable의 스레드 로컬 블록 등 추가 사용량과 동시 실행을 고려한다.

Created_tmp_disk_tables가 작아도 mmap 기반 TempTable 디스크 사용을 놓칠 수 있다. 이 카운터는 해당 mmap 임시 테이블을 세지 않는다. 위의 physical_disk와 임시 저장공간 사용량을 함께 본다. 메모리 계측의 high 값은 해당 집계 기간의 최고치이지 현재값이 아니며, 계측 설정과 초기화 여부도 확인한다.

한도를 늘리기 전에는 중간 결과의 행 수·행 너비·동시 실행 수를 줄일 여지가 있는지 본다. 전역 메모리 한도를 키워 한 보고서를 빠르게 만드는 대신 다른 연결의 메모리 여유를 빼앗을 수 있다. 디스크 임시 테이블을 무조건 0으로 만드는 것은 목표가 아니다.

7. 작은 세션 실험으로 MEMORY 한도 관계 확인하기

다음 예제는 전역 설정을 바꾸지 않고 현재 세션의 설정을 저장했다가 복원한다. 내부 임시 테이블 엔진을 MEMORY로 지정한 뒤 두 한도의 작은 값을 계산한다. 실제 디스크 전환, 한도 초과 오류, 성능 개선은 재현하지 않는다. 아래 계산을 TempTable의 상한 계산으로 재사용해서는 안 된다.

SET @saved_engine = @@session.internal_tmp_mem_storage_engine,
    @saved_tmp = @@session.tmp_table_size,
    @saved_heap = @@session.max_heap_table_size;
SET SESSION internal_tmp_mem_storage_engine = MEMORY;
SET SESSION tmp_table_size = 33554432;
SET SESSION max_heap_table_size = 8388608;
SELECT @@session.internal_tmp_mem_storage_engine AS internal_engine,
       @@session.tmp_table_size DIV 1048576 AS tmp_mib,
       @@session.max_heap_table_size DIV 1048576 AS heap_mib,
       LEAST(@@session.tmp_table_size,
             @@session.max_heap_table_size) DIV 1048576 AS memory_limit_mib;
SET SESSION internal_tmp_mem_storage_engine = @saved_engine;
SET SESSION tmp_table_size = @saved_tmp;
SET SESSION max_heap_table_size = @saved_heap;
SELECT @@session.internal_tmp_mem_storage_engine = @saved_engine AS engine_restored,
       @@session.tmp_table_size = @saved_tmp AS tmp_restored,
       @@session.max_heap_table_size = @saved_heap AS heap_restored;

실행 결과(MySQL 8.0.x):

설정 저장·변경 부분은 생략하고, 한도 계산과 복원 확인 결과를 발췌했다.

mysql> SELECT @@session.internal_tmp_mem_storage_engine AS internal_engine,
    ->        @@session.tmp_table_size DIV 1048576 AS tmp_mib,
    ->        @@session.max_heap_table_size DIV 1048576 AS heap_mib,
    ->        LEAST(@@session.tmp_table_size,
    ->              @@session.max_heap_table_size) DIV 1048576 AS memory_limit_mib;

+-----------------+---------+----------+------------------+
| internal_engine | tmp_mib | heap_mib | memory_limit_mib |
+-----------------+---------+----------+------------------+
| MEMORY          |      32 |        8 |                8 |
+-----------------+---------+----------+------------------+
1 row in set (0.00 sec)

mysql> SET SESSION internal_tmp_mem_storage_engine = @saved_engine;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SESSION tmp_table_size = @saved_tmp;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SESSION max_heap_table_size = @saved_heap;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@session.internal_tmp_mem_storage_engine = @saved_engine AS engine_restored,
    ->        @@session.tmp_table_size = @saved_tmp AS tmp_restored,
    ->        @@session.max_heap_table_size = @saved_heap AS heap_restored;

+-----------------+--------------+---------------+
| engine_restored | tmp_restored | heap_restored |
+-----------------+--------------+---------------+
|               1 |            1 |             1 |
+-----------------+--------------+---------------+

이처럼 적용 범위가 분명한 세션 실험부터 시작하면 전역 확대에 따른 동시성 위험을 줄일 수 있다. 단, 연결 풀은 설정을 가진 세션을 재사용한다. 실험 뒤 복원 성공을 확인해야 하며, 전역값 변경 후 기존 세션까지 모두 바뀌었다고 생각해서는 안 된다.

8. Aurora MySQL에서는 같은 변수명도 별도로 검토한다

Aurora의 분산 스토리지 경로는 Community MySQL의 로컬 데이터 파일·로그 파일 경로와 다르다. Community에서 효과가 있었던 redo 파일 용량, 페이지 flush, 파일 I/O 설정을 Aurora에 그대로 적용하지 않는다. 엔진 버전에 해당하는 파라미터 제공 여부, 수정 가능 여부, DB cluster/DB instance parameter group 범위, 재부팅 필요성을 먼저 확인한다.

Buffer Pool과 동시 작업 메모리 예산은 여전히 중요하다. writer만 볼 것이 아니라 각 reader의 쿼리 혼합과 인스턴스 크기도 확인한다. 관리형 스토리지를 사용한다고 임시 작업 공간까지 무제한인 것은 아니다. FreeableMemory, FreeLocalStorage, SQL 지연과 임시 공간 오류를 함께 관찰한다.

특히 Aurora MySQL 버전 3의 TempTable 규칙은 Community와 구분해야 한다. 공식 문서에 따르면 3.04 이상에서 aurora_tmptable_enable_per_table_limit=ON일 때 TempTable에도 tmp_table_size의 개별 한도가 적용된다. 기본값은 OFF이며, OFF이면 TempTable의 개별 제한으로 이 값을 사용하지 않는다. 전역 TempTable 자원 제한은 별도로 적용된다.

또한 reader는 writer와 동일한 InnoDB 디스크 임시 테이블 전환을 기대하면 안 된다. 적용되는 한도에 도달하면 writer는 디스크 임시 테이블로 전환할 수 있지만 reader에서는 ERROR 1114 ... table is full로 끝날 수 있다. reader의 mmap·로컬 공간과 쿼리 중간 결과를 함께 조사한다. 이 설명은 해당 Aurora 버전 3 문서의 범위이며, 다른 메이저 버전에 무조건 확대하지 않는다. 위 SQL 출력은 Aurora가 아닌 Community 실행 결과다.

9. 변경의 성공·중단·원복 조건을 먼저 쓴다

파라미터 변경은 가설 하나와 검증 구간 하나로 관리한다. “Buffer Pool을 늘리면 이 반복 조회의 디스크 읽기가 줄어들 것”처럼 기대하는 경로를 명시하고, 로그 용량과 temp 한도까지 동시에 바꾸지 않는다.

운영 변경 기록에는 다음을 포함한다.

• 기준선: 버전, 실행값, 지속 설정, 부하 구성, 표본 시간, p95/p99, 오류율, 자원 여유
• 기대 효과: 어느 카운터나 대기가 어떤 방향으로 움직여야 하는지
• 안전 조건: 메모리·임시 공간·스토리지 지연·복제 지연이 허용 범위에 있는지
• 중단 조건: 업무 지연 악화, OOM 징후, 공간 부족, 오류 증가, 설정 적용 지연
• 원복 방식: 이전 실행값과 지속값, 기존 세션 처리, 재시작 여부, 캐시 재준비와 관찰 시간

SET GLOBAL 성공은 지속 설정 저장이나 모든 기존 세션 반영을 뜻하지 않는다. SET PERSIST를 지원하는 환경이라도 저장값과 실제 실행값을 구분한다. RESET PERSIST는 저장 항목 제거이지 현재값 원복 명령이 아니다. Aurora에서는 파라미터 그룹 적용 상태와 재부팅 후 상태까지 확인한다.

관측 결과가 기대와 다르면 확대를 중단한다. 캐시 확대 후 읽기가 줄지 않았다면 읽기 대상 자체가 바뀌었거나 작업 집합이 재사용되지 않는지 살핀다. redo 확대 후 지연이 그대로라면 커밋 동기화나 행 잠금이 주원인일 수 있다. temp 확대 후 OOM 압력이 높아졌다면 SQL 구조와 동시성을 다시 설계한다.

10. 운영 점검표

• 버전과 실제 실행값을 확보하고 기본값·설정 파일·세션값과 구분했는가?
• 호스트 RAM뿐 아니라 컨테이너·인스턴스의 실제 메모리 한도를 확인했는가?
• Buffer Pool 외의 동시 작업 메모리와 순간 피크 여유를 남겼는가?
• 캐시 적중률뿐 아니라 절대 읽기량·스토리지 지연·업무 p99를 함께 보았는가?
• redo capacity 부족, log buffer 대기, 커밋 동기화 지연을 구분했는가?
• dirty page와 checkpoint 추세를 보고 백그라운드 flush를 판단했는가?
• 내구성 파라미터 변경은 승인된 RPO와 장애 시험을 전제로 했는가?
• TempTable·MEMORY·사용자 생성 테이블의 한도를 구분했는가?
• mmap 사용을 디스크 임시 테이블 카운터만으로 판단하지 않았는가?
• Aurora에서는 버전·writer/reader·파라미터 그룹 차이를 확인했는가?
• 한 번에 한 가설을 검증하고 중단·원복·재관찰 기준을 정했는가?

맺음말

튜닝의 기준은 큰 값이나 높은 적중률 자체가 아니라, 자원 사용과 업무 지연 사이의 인과관계를 확인하는 데 있다. Buffer Pool은 읽기 작업 집합, redo는 변경량과 checkpoint, flush는 지속 가능한 I/O와 내구성, temp는 중간 결과와 동시성으로 설명할 수 있어야 한다. 이후의 용량 계획과 성능 회귀 점검에서도 이 분리를 유지하면 설정값을 관행이 아니라 검증 가능한 운영 근거로 관리할 수 있다.

참고 문서

• MySQL 8.0: Redo Log
• MySQL 8.0: Internal Temporary Table Use
• MySQL 8.4: InnoDB Startup Options and System Variables
• Aurora MySQL 버전 3의 임시 테이블 동작