PRO DBDATA PRO DBDATA

MySQL 8.4로 설치했는데 innodb_log_buffer_size가 16MB인 이유, variables_info로 값의 출처 추적하기

읽는 시간 약 21분
MySQL 8.4 기본값과 실제 값을 비교한 표에서 innodb_log_buffer_size만 my.ini의 16777216과 EXPLICIT로 표시된 화면

SHOW GLOBAL VARIABLES 결과에는 출처 칸이 없다

운영 DB의 설정값은 SHOW GLOBAL VARIABLES LIKE '%변수명 일부%'로 조회해서 확인합니다. 결과에 나오는 것은 변수 이름과 값 두 칸입니다. MySQL 8.4에서는 InnoDB 변수 여러 개의 기본값이 8.0과 달라졌는데, 화면의 숫자만으로는 그 값이 컴파일 기본값인지, 설정 파일에 적힌 값인지, 실행 중에 누군가 바꾼 값인지 구분할 수 없습니다.

그래서 이번 테스트는 performance_schema.variables_info의 VARIABLE_SOURCE로 값의 출처를 확인했습니다. 8.4 문서의 기본값 변경 목록을 내 서버의 실제 값과 나란히 놓고, SET 방식에 따라 출처가 어떻게 바뀌는지 서비스 재시작 앞뒤로 비교했습니다.

대상 서버는 Windows에 설치한 MySQL Community Server 8.4.11입니다. PC는 논리 CPU 22개, 물리 메모리 33,851,396,096바이트이고, 설정 파일은 설치 때 생성된 C:\ProgramData\MySQL\MySQL Server 8.4\my.ini 하나입니다. 클라이언트는 MySQL Workbench 최신 버전을 썼습니다. vardefault_lab 데이터베이스에는 비교용 기준표 20행과 추적용 뷰만 두었습니다. 먼저 서버 전체 변수의 출처 분포부터 봤습니다.

SELECT 'TC01' AS tc, VARIABLE_SOURCE, COUNT(*) AS var_cnt
FROM performance_schema.variables_info
GROUP BY VARIABLE_SOURCE
ORDER BY var_cnt DESC;
variables_info를 VARIABLE_SOURCE로 묶은 결과 COMPILED 601, EXPLICIT 46, DYNAMIC 2
시스템 변수 649개 중 601개는 컴파일 기본값, 46개는 my.ini에서, 2개는 실행 중 변경에서 값을 받았다.

649개 중 601개가 COMPILED였습니다. 설정 파일에서 온 값은 46개, 실행 중에 바뀐 값은 2개였습니다. 테스트를 시작하기 전인데도 DYNAMIC이 2개 있었다는 점은 뒤에서 따로 확인했습니다.

my.ini가 쥐고 있던 46개 변수

COMPILED가 아닌 행만 골라 값과 파일 경로를 함께 조회했습니다.

SELECT 'TC02' AS tc, vi.VARIABLE_NAME, gv.VARIABLE_VALUE,
       vi.VARIABLE_SOURCE, vi.VARIABLE_PATH
FROM performance_schema.variables_info vi
JOIN performance_schema.global_variables gv
  ON gv.VARIABLE_NAME = vi.VARIABLE_NAME
WHERE vi.VARIABLE_SOURCE <> 'COMPILED'
ORDER BY vi.VARIABLE_SOURCE, vi.VARIABLE_NAME;
EXPLICIT 출처 변수 목록 상단, innodb_buffer_pool_instances 1과 innodb_buffer_pool_size 134217728의 경로가 my.ini
EXPLICIT 행은 VARIABLE_PATH로 어느 설정 파일에서 값을 읽었는지까지 보여 준다.

46행의 경로는 모두 같은 my.ini였습니다. 포트, 로그 파일 이름, secure_file_priv 같은 설치 정보 사이에 InnoDB 값도 섞여 있었습니다. innodb_buffer_pool_size 134217728, innodb_buffer_pool_instances 1, innodb_log_buffer_size 16777216, innodb_redo_log_capacity 104857600, innodb_thread_concurrency 45, temptable_max_ram 1073741824가 모두 파일에 적힌 값이었습니다.

이 중 세 개(innodb_log_buffer_size, innodb_buffer_pool_instances, temptable_max_ram)는 8.4에서 기본값이 바뀐 변수입니다. Redo 용량 100MB가 체크포인트에 주는 영향은 Redo 로그 용량과 체크포인트 글에서 따로 측정했습니다.

my.ini를 빼면 이 PC에서 계산되는 값

EXPLICIT라는 표시만으로는 파일이 없을 때 어떤 값이 됐을지 알 수 없습니다. 그래서 명령 프롬프트에서 mysqld에 --no-defaults를 주고 도움말의 변수 목록만 걸러 봤습니다. 서버를 띄우지 않고 설정 파일도 읽지 않기 때문에, 이 PC의 CPU와 메모리로 계산된 기본값이 그대로 나옵니다.

"C:\Program Files\MySQL\MySQL Server 8.4\bin\mysqld.exe" --no-defaults --verbose --help | findstr /b /i /c:"innodb-adaptive-hash-index " /c:"innodb-change-buffering " /c:"innodb-io-capacity " /c:"innodb-io-capacity-max " /c:"innodb-log-buffer-size " /c:"innodb-doublewrite-files " /c:"innodb-doublewrite-pages " /c:"innodb-buffer-pool-size " /c:"innodb-buffer-pool-instances " /c:"innodb-page-cleaners " /c:"innodb-parallel-read-threads " /c:"innodb-purge-threads " /c:"innodb-read-io-threads " /c:"innodb-use-fdatasync " /c:"temptable-max-ram " /c:"temptable-max-mmap " /c:"temptable-use-mmap " /c:"innodb-flush-method "
명령 프롬프트에서 mysqld --no-defaults --verbose --help 출력, innodb-log-buffer-size 67108864와 innodb-read-io-threads 11
설정 파일을 읽지 않으면 innodb_log_buffer_size의 기본값은 67108864로 계산된다.

my.ini 없이 계산한 innodb-log-buffer-size는 67108864(64MiB)였습니다. 실행 중인 서버의 값은 16777216(16MiB)이니, 8.4로 설치했어도 이 변수는 8.0 기본값으로 동작하고 있던 셈입니다.

계산식 기본값은 문서의 식과 맞아떨어졌습니다. innodb-read-io-threads 11은 논리 CPU 22개의 절반입니다. innodb-parallel-read-threads는 22÷8=2.75가 최솟값 4에 걸려 4가 됐고, innodb-purge-threads는 CPU가 16개를 넘어 4였습니다. temptable-max-ram은 물리 메모리의 3%인 약 1,015,541,882바이트가 하한 1GiB보다 작아 1073741824가 나왔습니다. my.ini에 적힌 값과 같습니다.

도움말 값을 그대로 믿기 어려운 행도 있었습니다. innodb-io-capacity-max는 4294967295로 나왔는데, 실행 중인 서버의 값은 20000이었습니다. innodb-buffer-pool-instances도 0으로 표시됐습니다. 시작할 때 계산하라는 자리 표시 값으로 보이며, 이번에는 그 이상 확인하지 못했습니다. 이 명령은 파일이 가린 값을 찾는 용도로 쓰고, 최종 판단은 실행 중인 서버의 조회 결과로 하는 편이 안전합니다.

8.4 기본값 변경 20개를 서버 값 옆에 놓아 보기

MySQL 8.4 문서의 기본값 변경 목록에서 InnoDB·TempTable 변수를 고르고, 8.0에는 없던 restrict_fk_on_non_standard_key를 더해 20행짜리 기준표를 만들었습니다. 8.0 값과 8.4 값은 문서 표현을 그대로 문자열로 넣었습니다. performance_schema 쪽 VARIABLE_NAME은 utf8mb4_0900_ai_ci이므로, 조인 조건 한쪽에 COLLATE를 명시해 콜레이션 혼합 오류가 나지 않게 했습니다.

CREATE TABLE default_change_ref (
  seq            TINYINT      NOT NULL PRIMARY KEY,
  var_name       VARCHAR(64)  NOT NULL,
  default_80     VARCHAR(100) NOT NULL,
  default_84_doc VARCHAR(120) NOT NULL
) DEFAULT CHARSET = utf8mb4;

SELECT 'TC03' AS tc, r.seq, r.var_name, r.default_80, r.default_84_doc,
       gv.VARIABLE_VALUE AS now_value, vi.VARIABLE_SOURCE, vi.VARIABLE_PATH
FROM default_change_ref r
LEFT JOIN performance_schema.global_variables gv
  ON gv.VARIABLE_NAME = r.var_name COLLATE utf8mb4_bin
LEFT JOIN performance_schema.variables_info vi
  ON vi.VARIABLE_NAME = r.var_name COLLATE utf8mb4_bin
ORDER BY r.seq;
8.0과 8.4 기본값 열 옆에 현재 값과 출처를 붙인 20행 대조 결과, 5번 8번 14번 행만 EXPLICIT
20개 중 17개는 COMPILED, 3개는 my.ini 값이었고 innodb_numa_interleave는 이 서버에 변수 자체가 없었다.

20행을 값이 정해진 방식으로 묶어 보면 네 갈래로 나뉩니다.

구분변수와 이 서버의 값
8.4 고정 기본값으로 동작innodb_adaptive_hash_index OFF, innodb_change_buffering none, innodb_io_capacity 10000, innodb_io_capacity_max 20000, innodb_doublewrite_files 2, innodb_doublewrite_pages 128, innodb_use_fdatasync ON, temptable_max_mmap 0, temptable_use_mmap OFF, restrict_fk_on_non_standard_key ON
계산식으로 정해진 값innodb_page_cleaners 1, innodb_parallel_read_threads 4, innodb_purge_threads 4, innodb_read_io_threads 11
my.ini가 정한 값innodb_log_buffer_size 16777216(계산값 67108864), innodb_buffer_pool_instances 1(버퍼 풀 128MiB라 계산해도 1), temptable_max_ram 1073741824(계산해도 1GiB)
Windows에서 다른 행innodb_numa_interleave 변수 없음, innodb_buffer_pool_in_core_file ON, innodb_flush_method unbuffered

계산식 기본값 4개도 고정값과 똑같이 COMPILED로 찍혔습니다. 출처만으로는 CPU 수가 다른 서버에서 값이 달라질 변수인지 알 수 없으니, 사양이 다른 서버끼리 설정을 비교할 때는 이 네 변수를 따로 봐야 합니다.

파일에 가려진 3개 중 실제로 8.4 기본값과 다른 값을 만든 것은 innodb_log_buffer_size 하나였습니다. 나머지 둘은 my.ini를 지워도 같은 값이 나옵니다. 8.4 업그레이드나 신규 설치를 점검할 때는 EXPLICIT 목록과 8.4 변경 목록이 겹치는 행을 먼저 뽑고, 그 행마다 파일이 없을 때의 값과 비교하는 순서를 권합니다. 같은 서버에서 인증 플러그인 상태도 함께 봤는데, mysql_native_password는 DISABLED, caching_sha2_password는 ACTIVE, authentication_policy는 *,,였습니다.

옆 세션의 SET SESSION도 DYNAMIC으로 찍힌다

Workbench에서 연결을 하나 더 열어(접속 ID 17) 세션 값만 바꿨습니다.

SET SESSION sort_buffer_size = 1048576;

SELECT 'TC04-1A' AS tc,
       @@SESSION.sort_buffer_size AS session_val,
       @@GLOBAL.sort_buffer_size  AS global_val;

세션 값은 1048576, 글로벌 값은 my.ini의 262144 그대로였습니다. 그런데 variables_info에서 sort_buffer_size의 출처는 EXPLICIT에서 DYNAMIC으로 바뀌었고, SET_TIME 21:43:49.669760, SET_USER root가 채워졌습니다. 이 결과는 SET을 실행한 세션뿐 아니라 아무것도 바꾸지 않은 다른 세션에서 조회해도 같았습니다. DYNAMIC은 누군가 실행 중에 SET을 했다는 표시일 뿐, 글로벌 값이 바뀌었다는 뜻은 아니었습니다.

첫 조회에서 보인 DYNAMIC 2개도 이 동작과 관련이 있는지 확인했습니다. Workbench를 완전히 종료하고 서비스를 재시작한 뒤, mysql 명령줄 클라이언트로 먼저 접속해 조회하자 foreign_key_checks 하나만 DYNAMIC이었습니다. SET_TIME은 23:13:59.589519, SET_USER와 SET_HOST는 NULL입니다. 클라이언트가 붙기 전이라 서버 시작 과정에서 기록된 것으로 보이지만, 어느 단계에서 설정됐는지는 이번에 확인하지 못했습니다. 이어서 Workbench로 접속하자 explain_json_format_version이 23:14:54.576330, root@localhost로 추가됐습니다. Workbench가 연결할 때 이 변수의 세션 값을 설정하는 것으로 보입니다.

DYNAMIC 행을 발견하면 글로벌 값을 설정 파일 값과 비교하고, SET_USER와 SET_HOST로 누가 바꿨는지까지 확인한 뒤에 글로벌 변경 여부를 판단하는 것이 안전합니다.

재시작 한 번에 갈라진 세 가지 SET

변수 세 개를 서로 다른 방식으로 바꿨습니다. innodb_read_io_threads는 읽기 전용이라 PERSIST_ONLY로만 바꿀 수 있습니다. 변화는 variables_info, global_variables, persisted_variables를 묶은 뷰 v_track으로 확인했습니다.

SET GLOBAL innodb_io_capacity = 2000;
SET PERSIST innodb_log_buffer_size = 33554432;
SET PERSIST_ONLY innodb_read_io_threads = 3;

SELECT 'TC05' AS tc, t.* FROM vardefault_lab.v_track t;
재시작 전 추적 뷰 결과, innodb_read_io_threads가 11과 COMPILED인데 persisted_value 칸에 3
재시작 전에는 PERSIST_ONLY로 바꾼 변수가 출처 COMPILED, 값 11 그대로라 persisted_value 칸에서만 변경이 보인다.

이 시점에 innodb_io_capacity 2000과 innodb_log_buffer_size 33554432가 둘 다 DYNAMIC이었습니다. 재시작 후에도 남을 값인지는 persisted_value 칸으로만 구별됐습니다. innodb_read_io_threads는 값 11, 출처 COMPILED 그대로인데 SET_TIME과 SET_USER는 채워져 있었습니다. 출처 칸만 보는 점검으로는 재시작 대기 중인 변경을 놓치게 됩니다. 서비스를 재시작한 뒤 같은 뷰를 다시 조회했습니다.

재시작 후 추적 뷰 결과, innodb_log_buffer_size와 innodb_read_io_threads가 PERSISTED이고 경로가 mysqld-auto.cnf
재시작 뒤 SET GLOBAL 값은 사라지고, 영속화한 두 변수만 mysqld-auto.cnf 경로의 PERSISTED로 남았다.
변수실행한 SET재시작 전재시작 후
innodb_io_capacitySET GLOBAL 20002000 / DYNAMIC10000 / COMPILED
innodb_log_buffer_sizeSET PERSIST 3355443233554432 / DYNAMIC33554432 / PERSISTED
innodb_read_io_threadsSET PERSIST_ONLY 311 / COMPILED3 / PERSISTED
sort_buffer_size다른 세션의 SET SESSION262144 / DYNAMIC262144 / EXPLICIT

mysqld-auto.cnf에 남은 시각

캡처는 날짜까지만 표시돼서, 같은 순서를 한 번 더 실행해 시각을 텍스트로 받았습니다. 처음에는 persisted_variables에서 설정 시각을 보려고 했지만 Unknown column 'SET_TIME' in 'field list'(1054) 오류가 났습니다. 이 테이블에는 이름과 값만 있어서 파일 원문을 직접 열었습니다.

{"Version": 2, "mysql_static_variables": {"innodb_read_io_threads": {"Value": "3", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1793715477150144}}}, "mysql_dynamic_variables": {"innodb_log_buffer_size": {"Value": "33554432", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1793715462123704}}}}

읽기 전용 변수는 mysql_static_variables, 동적 변수는 mysql_dynamic_variables에 나뉘어 저장됐습니다. Timestamp는 UTC 기준 마이크로초입니다. 1793715462123704를 한국 시간으로 바꾸면 23:17:42.123704로, 재시작 후 innodb_log_buffer_size의 SET_TIME과 정확히 같습니다. 재시작 전 같은 변수의 SET_TIME은 23:17:42.133593이었습니다. 재시작 뒤에 보이는 시각은 SET을 실행한 순간이 아니라 파일에 기록된 시각이고, 이번에는 그 차이가 약 0.01초였습니다. PERSIST_ONLY로 바꾼 innodb_read_io_threads는 재시작 전후 모두 23:17:57.150144로 같았습니다.

RESET PERSIST 직후에도 PERSISTED는 남는다

RESET PERSIST innodb_log_buffer_size;
RESET PERSIST innodb_read_io_threads;

첫 번째 실행에서 영속화 값을 지운 직후 조회하자, persisted_value는 두 변수 모두 NULL이 됐습니다. 하지만 런타임 값은 33554432와 3 그대로였고, 출처도 PERSISTED, 경로도 mysqld-auto.cnf로 남아 있었습니다. innodb_read_io_threads만 SET_TIME과 SET_USER가 NULL로 비워졌습니다. 두 번째 재시작 후에야 16777216 / EXPLICIT, 11 / COMPILED로 돌아왔습니다. 출처가 PERSISTED라고 해서 파일에 그 값이 아직 있다고 볼 수는 없으니, persisted_variables를 함께 조회해야 합니다.

Aurora 클러스터 파라미터 그룹에서 바꾼 값은 어떤 출처로 보일까

운영에서 파라미터를 바꿀 때는 항상 콘솔의 사용자 클러스터 파라미터 그룹에서 변경합니다. 이번 테스트에서 비교한 SET GLOBAL, SET PERSIST, my.ini 수정은 운영 변경 경로가 아닙니다. 파라미터 그룹에서 지정한 값이 variables_info에서 어떤 VARIABLE_SOURCE와 경로로 보이는지, 그리고 서비스 제공 측 기본값과 사용자 그룹의 변경값이 구분되는지는 로컬 Windows 서버로는 확인할 수 없었습니다.

다음 확인 대상은 같은 조회를 클러스터에서 돌려 보는 것입니다. 조회만 하는 SQL이라 Reader 인스턴스에서 실행해도 되고, 파라미터 그룹에서 직접 바꾼 변수 하나를 조건에 넣으면 출처 표시와 콘솔 화면을 바로 대조할 수 있습니다.

SELECT VARIABLE_SOURCE, COUNT(*) AS var_cnt
FROM performance_schema.variables_info
GROUP BY VARIABLE_SOURCE;

SELECT vi.VARIABLE_NAME, gv.VARIABLE_VALUE, vi.VARIABLE_SOURCE,
       vi.VARIABLE_PATH, vi.SET_TIME, vi.SET_USER
FROM performance_schema.variables_info vi
JOIN performance_schema.global_variables gv
  ON gv.VARIABLE_NAME = vi.VARIABLE_NAME
WHERE vi.VARIABLE_NAME IN ('innodb_log_buffer_size', 'innodb_io_capacity',
                           'innodb_read_io_threads', 'innodb_adaptive_hash_index');

로컬 서버와 결과가 다르다면, 그 차이가 운영 서버에서 SHOW GLOBAL VARIABLES 값만 보고 판단할 때 놓치고 있던 정보가 됩니다.

PRODB
16년 차 현직 DBA. AWS RDS for MySQL과 Aurora MySQL을 운영하며 겪은 문제를 MySQL 8.4에서 직접 재현해 기록합니다.

함께 보면 좋은 글

댓글 0

첫 댓글을 남겨보세요.

광고 차단 알림

광고 클릭 제한을 초과하여 광고가 차단되었습니다.

단시간에 반복적인 광고 클릭은 시스템에 의해 감지되며, IP가 수집되어 사이트 관리자가 확인 가능합니다.