파라미터 권장값 MOS URL : Recommended Settings for MySQL 5.6, 5.7, 8.0 Server for Online Transaction Processing (OLTP) and Benchmarking (Doc ID 1531329.1)
■ table_open_cache
모든 스레드에 대해 열려 있는 테이블의 수입니다.
동적
예
기본값
4000
최소값
1
최대값
524288
※ 테이블 캐시 적중률 확인
일반적으로 50% 이상이어야 합니다. 낮을 시 값을 늘릴 필요가 있다.
Table cache hit rate = table_open_cache*100/Opened_tables.
= 2000*100/482099
= 0.41%
mysql> show global status like 'open%';
+--------------------------+---------+
| Variable_name | Value |
+--------------------------+---------+
| Open_files | 1583 |
| Open_streams | 0 |
| Open_table_definitions | 1400 |
| Open_tables | 2000 |
| Opened_files | 2619222 |
| Opened_table_definitions | 110583 |
| Opened_tables | 482099 |
+--------------------------+---------+
7 rows in set (0.00 sec)
■ open-files-limit
# mysql이 오픈할수 있는 file(정확히는 file descripter)개수
# 가능하면 5000으로 설정하는데 OS limit에 따라 재조정 될수 있다
# 실제로는 이 값에 영향을 받지 않고, OS의 nofile(open files) 제한이 적용됨
동적
아니
기본값
5000, with possible adjustment
최소값
0
최대값
platform dependent
※ 공식
일반적으로 table_open_cache의 2배
open_files_limit= Table_open_cache*2
■ table_open_cache_instances
열린 테이블 캐시 인스턴스의 수입니다.
동적
아니
기본값
16
최소값
1
최대값
64
16개 이상의 코어를 일상적으로 사용하는 시스템에서는 8 또는 16 값을 사용하는 것이 좋습니다.그러나 높은 메모리 로드를 유발하는 테이블에 많은 큰 트리거가 있는 경우 에 대한 기본 설정으로table_open_cache_instances인해 과도한 메모리 사용이 발생할 수 있습니다.이러한 상황에서table_open_cache_instances메모리 사용을 제한하기 위해 1로 설정하는 것이 도움이 될 수 있습니다.
■ block_encryption_mode
이 변수는 AES와 같은 블록 기반 알고리즘에 대한 블록 암호화 모드를 제어합니다. 값은 대소문자를 구분하지 않습니다.
block_encryption_mode지원되지 않는 키 길이 또는 SSL 라이브러리가 지원하지 않는 모드를 포함하는 값으로 설정하려고 하면 오류가 발생합니다 .
동적
예
유형
끈
기본값
aes-128-ecb
■ max_allowed_packet
한 패킷의 최대 크기
하나의 패킷 또는 생성된/중간 문자열 또는mysql_stmt_send_long_data()C API 함수에서 보낸 매개변수의 최대 크기입니다.
BLOB큰 열이나 긴 문자열을 사용하는 경우 이 값을 늘려야 합니다 .BLOB사용하려는최대 크기만큼 커야 합니다.프로토콜 제한max_allowed_packet은 1GB입니다.값은 1024의 배수여야 합니다.
※BLOB(Binary Large Object) : 이진파일인 이미지 등 형태의 파일 크기를 4GB까지 지원
CLOB(Character Large Object) : 텍스트형태파일 크기를 4GB까지 지원
허용된 최대 동시 클라이언트 연결 수입니다. 최대 유효 값은 open_files_limit - 810의 유효 값과 max_connections에 대해 실제로 설정된 값 중 작은 값입니다.
운영 체제의 SOFT 및 HARD 제한을 조정하고 MySQL에서 더 높은 값의 open_files_limit를 설정하는 것을 고려합니다(5000이 기본 제한임). =>OS단에서 open_files_limit를 설정
응용 프로그램이 데이터베이스에 대한연결을 올바르게 닫지 않는 경우가 매우 빈번하다는 점을 유의해야 합니다. => 개발단에서 close를 제대로 안할 경우
애플리케이션 수준에서연결 풀을 사용하면 여기에서 문제를 해결하는 데 도움이 될 수 있습니다. =>WAS단에서 컨넥션풀 사용이 도움이 된다.
커넥션 개수를 늘릴때 thread_cache 값도 영향을 미친다.
동적
예
유형
정수
기본값
151
최소값
1
에러 로그에 Too many connections 오류 발생
mysql> show status like 'Aborted%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Aborted_clients | 4 |
| Aborted_connects | 1 |
+------------------+-------+
2 rows in set (0.00 sec)
늘어나면 날수록 메모리가 고갈되고 스케줄링 오버헤드도 증가
이전 최대 접속자 수의 2배 정도 잡는다.
■ max_connect_errors
계속적으로 문제를 발생시키는 클라이언트를 몇번째 재시도후 block할지 지정
한번 block되면, 서버를 재시작하거나 flush host명령을 실행하기 전까지 접속 불가
동적
예
기본값
100
최소값
1
최대값(32비트 플랫폼)
4294967295
■ skip-name-resolve
클라이언트 연결을 확인할 때 호스트 이름을 확인할지 여부입니다. 이 변수가 OFF이면 mysqld는 클라이언트 연결을 확인할 때 호스트 이름을 확인합니다.ON인 경우 mysqld는 IP 번호만 사용합니다. 이 경우 허가 테이블의 모든 Host 열 값은 IP 주소여야 합니다.
역DNS 검색 비활성화 (IP 기반으로 접속을 하게 되면 hostname lookup 과정 생략)
동적
아니
기본값
OFF
■ max_heap_table_size
사용자 생성 메모리 테이블이 커질 수 있는 최대 크기를 설정합니다. 변수 값은 MEMORY 테이블 MAX_ROWS 값을 계산하는 데 사용됩니다. 이 변수는 내부 메모리 내 테이블의 크기를 제한하기 위해 tmp_table_size와 함께 사용됩니다.
MEMORY 테이블의 최대 크기
동적
예
기본값
16777216
최소값
16384
최대값(32비트 플랫폼)
4294966272
단위
바이트
블록 크기
1024
■ tmp_table_size
메모리에 생성될 임시 테이블의 최대 크기, 이 값을 초과하면 디스크에 임시 테이블을 씁니다.
내부 메모리 임시 테이블에 MEMORY 스토리지 엔진을 사용할 때 실제 크기 제한은 tmp_table_size와 max_heap_table_size 중 더 작은 크기입니다.
동적
예
기본값
16777216
최소값
1024
최대값
18446744073709551615
단위
바이트
■ tmpdir
디스크에 임시테이블이 생성될 때 사용될 경로
/tmp기본 디렉토리가 너무 작아서 임시 테이블을 보관할 수 없는 파티션에 있는경우 유용할 수 있습니다 .
동적
아니
유형
디렉토리 이름
■ innodb_sort_buffer_size
동적
아니
기본값
1048576
최소값
65536
최대값
67108864
단위
바이트
보조 인덱스를 만들거나 재구성하는 온라인 DDL 작업의 정렬 버퍼 크기입니다. 그러나 MySQL 8.0.27에서 이 책임은 innodb_ddl_buffer_size 변수에 포함됩니다.
온라인 DDL 작업 중 동시 DML을 기록할 때 임시 로그 파일이 확장되는 양, 임시 로그 파일 읽기 버퍼 및 쓰기 버퍼의 크기.
■ innodb_log_file_size
로그(redo) 파일 크기
일반적으로 (innodb_buffer_pool_size/innodb_log_files_in_group)를 적정 값으로 봅니다.
로그 파일의 결합 크기는 512GB보다 약간 작은 최대값을 초과할 수 없음.
로그 파일 크기가 클수록 성능이 더 좋지만 충돌 후 복구 시간을 고려해야함.
드물게 크래시 복구가 발생하는 경우 복구 시간과 피크 작업 중 처리량 최대화의 균형을 맞춰야함.
동적
아니
기본값
50331648
최소값
4194304
최대값
512GB / innodb_log_files_in_group
단위
바이트
※ 권고 값
버퍼 풀이 100기가바이트 이상이고 데이터 수정률이 높은 서버에는 수십 기가바이트 크기가 적합할 수 있지만 대부분의 프로덕션 서버는 몇 기가바이트면 괜찮습니다.
상당한 삽입, 업데이트 및 삭제 활동을 보이는 모든 MySQL 버전에 권장되는 초기 값
innodb_log_file_size = 2047M
innodb_log_files_in_group = 2 innodb_log_file_size * innodb_log_files_in_group
■ innodb_log_files_in_group
동적
아니
기본값
2
최소값
2
최대값
100
MySQL 8.0.30에서 더 이상 사용되지 않습니다 .
로그 그룹 의 로그 파일 수입니다 . 순환 방식으로 파일에 씁니다. 기본(권장) 값은 2입니다. 로그 파일의 결합 크기( * )는 최대 512GB입니다.
■ innodb_file_per_table
동적
예
기본값
ON
활성화 되면 innodb_file_per_table테이블은 기본적으로 테이블당 파일 테이블스페이스에 생성됩니다. 비활성화하면 기본적으로 시스템 테이블스페이스에 테이블이 생성됩니다.
테이블 단위로 테이블스페이스 할당, 활성시 테이블별로 .frm, .ibd 각각 생성
■ innodb_undo_directory
동적
아니
유형
디렉토리 이름
InnoDB실행 취소 테이블스페이스 를 생성하는 경로입니다. 기본값이 없습니다(NULL). 다른 경로를 지정하지 않으면 변수가 CREATE UNDO TABLESPACE정의한 디렉터리에 생성됩니다.
■ innodb_undo_tablespaces
동적
예
기본값
2
최소값
2
최대값
127
실행 취소 테이블스페이스 의 수를 정의합니다. 이 innodb_undo_tablespaces 변수는 더 이상 사용되지 않으며 MySQL 8.0.14부터 더 이상 구성할 수 없습니다.
■ innodb_max_undo_log_size
동적
예
기본값
1073741824
최소값
10485760
최대값
2**64-1
단위
바이트
실행 취소 테이블스페이스의 임계값 크기를 정의합니다. 실행 취소 테이블스페이스가 임계값을 초과하면 innodb_undo_log_truncate가 활성화된 경우 잘라내기로 표시할 수 있습니다.
■ innodb_status_output_locks
InnoDB Status를 보는 부분("SHOW ENGINE INNODB STATUS" 혹은 에러로그)에 lock 정보도 함께 표시되도록 함
■ innodb_print_all_deadlocks
에러 로그에 InnoDB 데드락 정보 기록 활성화
■ innodb_adaptive_hash_index
동적
예
기본값
ON
InnoDB 적응형 해시 인덱스를 사용할지 여부를 지정합니다. 적응형 해시 인덱스를 사용하지 않도록 설정하면 해시 테이블이 즉시 비워집니다.
워크로드에 따라 적응형 해시 인덱싱을 동적으로 활성화하거나 비활성화하여 쿼리 성능을 향상시키는 것이 바람직할 수 있습니다.
■ innodb_buffer_pool_size
동적
예
기본값
134217728
최소값
5242880
최대값(64비트 플랫폼)
2**64-1
최대값(32비트 플랫폼)
2**32-1
단위
바이트
InnoDB가 테이블 및 인덱스 데이터를 캐시하는 메모리 영역입니다. 버퍼 풀의 크기가 1GB보다 큰 경우 innodb_buffer_pool_instances를 1보다 큰 값으로 설정하면 사용 중인 서버의 확장성을 향상시킬 수 있습니다.
버퍼 풀이 클수록 동일한 테이블 데이터에 두 번 이상 액세스하는 데 필요한 디스크 I/O가 줄어듭니다.
■ innodb_buffer_pool_instances
InnoDB 버퍼 풀이 분할된 영역의 수입니다.
버퍼풀의 갯수, 적절한 갯수로 나누면 뮤텍스(mutex) 경합이 줄어들어 DB 동시 처리 성능을 높여줌
각각의 버퍼풀은 각각의 플러쉬 리스트 뮤텍스(mutex)를 가짐 (아마 LRU리스트도 가질 듯)
각 버퍼풀은 InnoDB 엔진이 자동으로 적절히 배분해서 사용함
동적
아니
기본값(Windows, 32비트 플랫폼)
(autosized)
기본값(기타)
8 (or 1 if innodb_buffer_pool_size < 1GB)
최소값
1
최대값
64
■ innodb_doublewrite
이중 쓰기 버퍼링을 제어합니다. 이중 쓰기 버퍼링은 대부분의 경우 기본적으로 사용하도록 설정됩니다.
동적(≥ 8.0.30)
예
동적(≤ 8.0.29)
아니
기본값
ON
유효한 값
ON
OFF
DETECT_AND_RECOVER
DETECT_ONLY
■ innodb_flush_log_at_trx_commit
동적
예
기본값
1
유효한 값
0
1
2
InnoDB에서 커밋될 때마다 로그(redo)를 디스크에 플러시할지를 결정하는 옵션
fsync() 함수를 호출하는 빈도에 관한 옵션
0 : 커밋될 때마다 디스크 플러시를 하지는 않음, OS에서 적절한 시점(일반적으로 4~5초 간격)마다 데이터 동기화를 처리하게 됩니다.
잘 못 되면 4~5초간의 데이터는 유실될 수도 있음
1 : 트랜잭션이 커밋될 때마다 로그 파일에 기록되고 디스크 플러시가 실행 (가장 안전, 가장 느림)
2 : 커밋되면 강제로 로그 버퍼를 로그 파일에 쓰지만, 1초가 지나기 전에는 디스크에 저장되지 않습니다.
■ innodb_flush_method
동적
아니
기본값(Unix)
fsync
기본값(Windows)
unbuffered
유효한 값(Unix)
fsync
O_DSYNC
littlesync
nosync
O_DIRECT
O_DIRECT_NO_FSYNC
유효한 값(Windows)
unbuffered
normal
■ innodb_io_capacity
동적
예
기본값
200
최소값
100
최대값(64비트 플랫폼)
2**64-1
최대값(32비트 플랫폼)
2**32-1
버퍼 풀에서 페이지를 플러시하고 변경 버퍼에서 데이터를 병합하는 것과 같은 InnoDB 백그라운드 작업에 사용할 수 있는 초당 입출력 작업 수(IOPS)를 정의합니다.
백그라운드 플러싱 속도 제어를 위한 변수, 큰 값을 지정하면 I/O 대역폭을 점유하므로 환경에 맞춰서 설정 필요.
■ sync_binlog
동적
예
기본값
1
최소값
0
최대값
4294967295
0: binary log를 기록하지만 직접적으로 플러시(동기화)를 실행하지 않고, OS에 맡김 (리눅스 계열은 3~5초 간격으로 자동 플러시함)
1: binary log의 쓰기가 발생할 때마다 디스크 동기화 수행
binary log 손실은 없지만 잦은 디스크 I/O로 느려질 수 있음
1 이상의 값: 설정된 횟수만큼 binary log 쓰기가 발생할 때마다 DB가 binary log 파일의 동기화를 실행
설정된 값이 클수록 손실될 수 있는 binary log의 양이 많아지고 binary log의 쓰기 성능은 좋아짐
