MySQL 설치 시 초기 파라미터 세팅 전략

처음 MySQL을 사용하는 개발자나 스타트업이라면 대부분 기본값으로 놓고 DB를 구성하게 됩니다. 하지만 나중을 생각해서 라도 기본값으로 사용해서는 안됩니다. 서비스가 한참 커진 후에는 처음에 놓치고 간 설정들 때문에 데이터 정합성, 무결성 문제가 발생하는 경우도 많고 규모가 커진 상태에서 잘못된 것들을 잡으려면 너무나 힘들어 집니다. 그래서 주요 파라미터들은 처음에 세팅을 하고 가야하는데, DB가 주로 어떤 역할을 수행 하는지에 따라 가져가야 하는 초기 파라미터 값들이 다릅니다. 하지만 처음 MySQL을 쓰는 사람이 어떤 값을 설정해야하는지 아는 것은 정말 어렵습니다.

오늘은 AWS 에서 Aurora for MySQL, RDS MySQL을 처음 구성하거나 MySQL을 설치하고 구동하기전에 파라미터 파일의 값중 세팅이 필요한 부분을 짚고 가려고 합니다.

Memory 영역

• 우선 메모리 영역은 초기 설정에서 크게 중요한 부분이 아닙니다. 사용 환경에 따라 성능 저하가 발생 했을때 튜닝을 진행해도 충분합니다. 하지만 몇몇 값들은 필수로 바꿔 주고 가면 좋습니다.

key_buffer_size

• key_buffer_size = 16384
• key_buffer_size 파라미터는 MyISAM 스토리지 엔진에서 인덱스 블록을 캐싱하는 데 사용되는데 MySQL8 버전에는 대부분 InnoDB로 대체 되었고 MyISAM은 테이블 단위로 Lock이 발생하고 트랜잭션(commit/rollback)을 지원하지 않아 이제는 거의 사용하지 않기 때문에 MyiSAM을 사용하는 경우가 아니라면 key_buffer_size에 기본값인 8MB나 설정해 줄 이유가 없습니다. 따라서 최소 수준인 16384으로 설정을 해도 크게 문제는 없습니다.
• 사용하지 않는다고 해서 key_buffer_size를 0으로 설정할 경우 MySQL 시작 시에 키 캐시가 초기화되지 않습니다.

bulk_insert_buffer_size

• bulk_insert_buffer_size = 32768
• bulk_insert_buffer_size 파라미터도 주로 MyISAM 스토리지 엔진에서 사용됩니다. 이 파라미터는 대량 삽입 작업 중에 사용되는 버퍼의 크기를 지정하며, 특히 INSERT … SELECT, INSERT … VALUES (multiple rows), LOAD DATA INFILE 등의 작업에서 MyISAM 테이블의 성능을 향상시키기 위해 사용됩니다.
• MySQL 8에서는 InnoDB가 기본 스토리지 엔진으로 사용되며, InnoDB는 bulk_insert_buffer_size 파라미터를 사용하지 않습니다. 따라서, MyISAM을 거의 사용하지 않는다면 이 값을 줄이거나 기본값으로 유지해도 무방합니다.

innodb_buffer_pool_size

• 전체 메모리 사이즈의 3/4 정도 설정해주는 것이 좋습니다. (AWS RDS 기본값)
• MySQL Default = 12321772 (128MB)
• InnoDB 버퍼 풀은 가장 최근에 액세스한 데이터를 캐싱하여 작동합니다. 메모리에 캐싱하면 디스크 대신 캐시에서 데이터를 다시 시도할 수 있습니다. 버퍼 풀은 두 개의 하위 목록을 관리하여 가장 자주 사용되는 데이터를 캐시에 보관하도록 설계되었습니다. 새로운 데이터에 액세스하면 ‘이전’ 목록의 맨 위에 저장됩니다. 이 목록에서 가장 오래된 항목은 제거되며 다시 쿼리할 경우 디스크에서 검색해야 합니다. 이 데이터는 다시 쿼리될 때 ‘최신’ 목록의 맨 위로 이동됩니다.
• innodb_buffer_pool_wait_free 의 값이 지속적으로 늘어난다면 innodb_buffer_pool_size가 충분하게 할당 되지 않은 것 입니다.

internal_tmp_mem_storage_engine

• 이 변수는 MySQL 서버가 메모리에 임시 테이블을 생성할 때 사용하는 스토리지 엔진을 결정합니다. 일반적으로 두 가지 옵션이 있습니다:

1. MEMORY: 기본값으로 설정되며, 메모리 기반의 임시 테이블을 생성합니다.
2. TempTable: MySQL 8.0부터 도입된 새로운 메모리 기반 임시 테이블 엔진으로, MEMORY 엔진보다 다양한 기능을 제공합니다.

• TempTable
  • TempTable 엔진을 사용하면 성능과 유연성을 향상시킬 수 있으며, 특히 다양한 데이터 타입과 큰 데이터셋을 다룰 때 유리합니다. 서버의 임시 테이블 사용 패턴을 분석하여 적절한 엔진을 선택하는 것이 중요합니다.

1. 1. 유연성 증가: TempTable 엔진은 더 다양한 데이터 타입과 기능을 지원하여 임시 테이블 사용의 유연성을 증가시킵니다.
   2. 성능 향상: TempTable 엔진은 메모리 사용을 최적화하고, 특히 큰 데이터셋을 다룰 때 성능을 향상시킬 수 있습니다.
   3. TEXT/BLOB 지원: TempTable 엔진은 TEXT 및 BLOB 데이터 타입을 지원하여 임시 테이블에서 더 다양한 데이터를 처리할 수 있습니다.

• Memory
  • MEMORY 엔진은 데이터를 메모리에 저장하여 디스크 I/O를 줄이고, 쿼리 응답 시간을 단축시키는 데 매우 유용합니다.

1. 1. 빠른 데이터 액세스: MEMORY 엔진은 데이터를 메모리에 저장하므로, 디스크에 저장된 데이터를 읽고 쓰는 것보다 훨씬 빠르게 데이터에 접근할 수 있습니다. 이는 특히 읽기 작업이 많은 환경에서 큰 성능 향상을 제공합니다.
   2. 낮은 디스크 I/O: MEMORY 엔진은 디스크 I/O를 거의 사용하지 않기 때문에, 디스크의 부하를 줄이고 다른 디스크 기반 작업의 성능을 개선할 수 있습니다. 이는 전체 시스템 성능을 향상시키는 데 기여합니다.
   3. 빠른 임시 테이블 생성: 임시 테이블을 생성하고 사용하는 쿼리에서 MEMORY 엔진을 사용하면 성능이 크게 향상됩니다. 예를 들어, 복잡한 JOIN, UNION, GROUP BY 쿼리에서 임시 결과를 저장하는 경우에 매우 유용합니다.
   4. 트랜잭션 오버헤드 없음: MEMORY 엔진은 트랜잭션을 지원하지 않으므로, 트랜잭션 관리에 따른 오버헤드가 없습니다. 이는 단순한 데이터를 빠르게 처리하는 데 유리합니다.
   5. 간단한 구조: MEMORY 테이블은 B-Tree 인덱스를 사용하며, 테이블 구조가 상대적으로 단순합니다. 이는 인덱스 및 데이터 관리가 용이함을 의미합니다.

• Memory 엔진 사용시 주의 사항

1. 1. 데이터 영구성 없음: MEMORY 테이블의 데이터는 MySQL 서버가 재시작되면 모두 사라집니다. 따라서 영구적으로 저장해야 하는 데이터에는 적합하지 않습니다.
   2. 메모리 사용량 관리: 많은 데이터를 MEMORY 테이블에 저장하면 서버 메모리가 빠르게 소모될 수 있습니다. 서버 메모리 용량을 초과하지 않도록 주의해야 합니다.
   3. 데이터 타입 제한: MEMORY 엔진은 BLOB, TEXT와 같은 데이터 타입을 지원하지 않습니다. 따라서 이러한 데이터 타입이 필요한 경우 다른 스토리지 엔진을 사용해야 합니다.

tmp_table_size & max_heap_size

• tmp_table_size와 max_heap_table_size의 초기 값으로 일반적으로 제안되는 값은 32MB에서 64MB입니다. 중요한 점은 MySQL이 이 두 변수에 할당된 값 중 더 낮은 값을 사용한다는 것입니다.
• tmp_table_size 값을 선택할 때, 메모리에 있을 임시 테이블의 예상 최대 크기를 고려해야 합니다. 설정된 tmp_table_size보다 내부 테이블이 크면 디스크에 저장됩니다. 디스크에 저장되면 성능이 저하되고 이러한 테이블을 사용하는 쿼리가 느려집니다.
• 임시 테이블이 디스크의 InnoDB로 변환되는 조건
  • 다음 조건에서는 임시 테이블이 디스크의 InnoDB로 변환됩니다:
  • 테이블에 TEXT 또는 BLOB 열이 있는 경우
  • GROUP BY 또는 DISTINCT 절에 있는 열이 512바이트보다 큰 경우
  • UNION 또는 UNION ALL이 있는 SELECT 목록의 열이 512바이트보다 큰 경우
  • 임시 테이블을 디스크 형식으로 변환되는 것을 방지하려면 CREATE TABLE ENGINE=MEMORY 명령을 사용할 수 있습니다. 이 명령을 사용할 때 최대 테이블 크기에 도달하면 더 이상 새로운 데이터가 추가되지 않고 테이블은 내부 메모리에 유지됩니다.

temptable_max_ram

• temptable_max_ram= GREATEST({DBInstanceClassMemory/32}, 209715200)
• MySQL 8.0.28에서 도입, 전체 메모리의 1/32 수준으로, 최소 200MiB 이상 설정
• TempTable 엔진이 메모리에 사용할 수 있는 최대 메모리 양을 설정합니다. 이 한도를 초과하는 경우, MySQL은 더 이상 TempTable 엔진을 사용하지 않고 디스크 기반의 InnoDB 임시 테이블로 전환합니다.

temptable_max_mmap

• temptable_max_mmap=GREATEST({DBInstanceClassMemory/5}, 3221225472)
• MySQL 8.0.28에서 도입, 전체 메모리의 1/5 수준으로 사용, 최소 3GiB 이상 설정
• TempTable 엔진이 메모리 매핑(memory-mapped) 파일을 사용하여 저장할 수 있는 최대 메모리 양을 설정합니다. 이 값은 TempTable 엔진이 메모리 매핑 파일을 사용하여 데이터의 일부를 디스크에 저장하는 데 사용하는 최대 용량을 정의합니다.

메모리 매핑 파일(memory-mapped file)은 운영체제의 메모리 관리 기능을 사용하여 파일의 내용을 메모리에 매핑하는 기술입니다. 이 방식은 파일의 내용을 디스크에서 직접 읽고 쓰지 않고, 메모리 주소 공간에 매핑하여 접근할 수 있게 합니다. 이를 통해 파일 입출력의 성능을 크게 향상시킬 수 있습니다.

aurora_tmptable_enable_per_table_limit (Aurora 한정)

• aurora_tmptable_enable_per_table_limit=1
• Aurora 3.04.0부터 추가된 파라미터, 변경된 임시 테이블처리 방식 적용 여부를 제어
• 1: TempTable 스토리지 엔진을 사용
• 0: MySQL 8.0.28 이전의 내부 임시 테이블 동작 방식, 기본값 (MEMORY 엔진을 사용하는 방식)
• 업그레이드 후 Reader에서 ERROR1114의 잦은 발생을 방지하기 위해 도입한 것으로 추정합니다.

cte_max_recursion_depth

• cte_max_recursion_depth = 10
• MySQL에서 Common Table Expressions(CTE)의 재귀 깊이를 제한하는 설정입니다. 이 변수는 특히 재귀적 CTE를 사용할 때 유용하며, 무한 루프와 같은 문제를 방지하기 위해 재귀 호출의 최대 깊이를 설정합니다.
• 기본값은 1000 이지만 10으로 재귀 깊이를 제한함으로써 무한 루프를 방지하고 리소스 보호할 수 있습니다.

언어셋, 정렬셋, 타임존

언어셋(character_set) 관련 파라미터

• character_set_client
• character_set_connection
• character_set_database
• character_set_filesystem
• character_set_results
• character_set_server
  -> utf8mb4

처음 DB구축할 때 세팅해줍니다. 사용중에 기본 언어셋을 바꾸면 문제가 생길 수도 있습니다. MySQL UTF8은 utf8mb3를 나타내는데 기본 문자의 바이트가 3바이트로 이모지를 저장할 수 없으면 MySQL에서 곧 Deprecate 될 예정입니다.

정렬셋(collation) 관련 파라미터

• collation_connection
• collation_server
  -> utf8mb4_general_ci

어떤 언어를 주로 사용하느냐에 따라 바뀌지만, MySQL8의 기본값인 utf8mb4_0900_ai_ci는 알파벳 문자를 기준으로 만들어진 정렬셋으로 동아시아 언어(한국어, 일본어 등)에서 치명적인 문자 인식 문제가 있습니다.  한글을 지원하는 문자셋은 많지만 특별한 상황이 아니라면 utf8mb4_general_ci로 설정하면 됩니다.

타임존

time_zone

• 국내 전용 -> Asia/Seoul
• 글로벌 서비스 -> UTC

서비스 되는 지역에 맞게 설정하면 됩니다.

binary log 활용

CDC혹은 복제기능을 이용해 데이터에 대한 2차 가공 혹은 업그레이드의 용이성을 올리기 위한 세팅입니다.

binlog_format

1. OFF: MySQL의 복제기능을 사용하지 않을때 설정합니다.
2. ROW: 행기반 복제. MySQL 복제 뿐만아니라 CDC를 사용하게 되면 반드시 ROW로 설정해줍니다. binlog의 사이즈가 증가할 수 있지만 AWS의 DMS나 Kafka Debezium 등 실시간 스트리밍을 위해선 반드시 ROW로 설정해줍니다. Aurora의 경우 재구동이 필요합니다.
   • 장점: 정확한 데이터 복제, 복잡한 트랜잭션 처리
   • 단점: 로그 크기, 디스크 I/O
3. STATEMENT: 명령문 기반 복제, 거의 쓰지 않습니다.
   • 비결정적인 함수(NOW(), RAND()등)를 사용한 명령문이 복제 시 동일한 결과를 보장하지 않을 수 있습니다.
4. MIXED: ROW와 STATEMENT의 기능을 합쳐놓은 것인데 MySQL은 상황에 따라 가장 적합한 복제 방식을 자동으로 선택합니다.
   • MySQL Replication만을 위해 사용한다면 선택할 수 있습니다.

binlog_row_image

• minimal: CDC를 하지 않고 복제 기능만 사용할 때 설정합니다.
• full: binlog를 통한 CDC를 사용하는 경우 설정해 줍니다.

binlog_cache_size

• 기본값은 32KB입니다.
• 큰 트랜잭션을 자주 사용하는 환경에서는 binlog_cache_size를 늘려야 할 수 있습니다. 그렇지 않으면 캐시가 꽉 차서 디스크의 임시 파일로 스왑되므로 성능 저하가 발생할 수 있습니다.
• SHOW GLOBAL STATUS 명령어를 사용하여 바이너리 로그 캐시의 최대 사용량을 모니터링 할 수 있습니다.

SHOW GLOBAL STATUS LIKE 'Binlog_cache_use';
SHOW GLOBAL STATUS LIKE 'Binlog_cache_disk_use';

• binlog_rows_query_log_events= 1
• MySQL에서 바이너리 로그(binlog)에 대한 설정 중 하나로, 행 기반 복제(Row-Based Replication) 시에 각 쿼리의 원본 SQL 문을 로그에 기록할지 여부를 결정합니다.
• 기본적으로 행 기반 복제는 각 행의 변경 사항만을 기록하지만, 이 설정을 활성화하면 어떤 SQL 문이 이 변경을 초래했는지도 로그에 포함됩니다.

SQL_MODE (중요)

• 제약조건이나 여러가지 이유로 SQL_MODE를 0으로 놓고 쓰는 업체가 많습니다. 몇몇 버전에서는 SQL_MODE의 기본값이 0이였던적도 있었습니다. 하지만 이 파라미터를 0으로 놓고 쓰는 것은 데이터 무결성을 매우 해치는 결과를 가져오고, 따라서 정합성과 데이터 유실을 가져오는 결과를 가져옵니다.
• 나중에 다른 DB로 이관을 하거나 버전업이나 데이터에 대한 검증이 필요한 시기가 오면 좌절을 맛보게 할 수 있는 파라미터 입니다.

• sql_mode = TRADITIONAL, NO_AUTO_CREATE_USER

TRADITIONAL

• TRADITIONAL 모드를 사용하는 것은 데이터베이스의 신뢰성과 안정성을 높이는 데 중요한 역할을 합니다.
• TRADITIONAL 모드는 여러 개의 개별 모드 조합으로 구성됩니다. 이 모드를 활성화하면 MySQL은 다음과 같은 기능을 수행합니다
  • STRICT_TRANS_TABLES : 트랜잭션 테이블에 잘못된 데이터를 삽입하려고 하면 오류를 발생시키고, 비트랜잭션 테이블에서는 경고를 발생시킵니다. 유효하지 않거나 누락 된 데이터가있는 명령문에서 누락 된 데이터가 첫 번째 행이 아닌 여러 행에 영향을 미치는 명령문의 경우를 제외하고는 중단 및 롤백됩니다.
  • STRICT_ALL_TABLES: 모든 테이블에 대해 잘못된 데이터를 삽입하려고 하면 오류를 발생시킵니다.
  • NO_ZERO_IN_DATE : 잘못된 날짜 데이터를 허용하지 않습니다. 연도가 0이 아니지만 날짜의 월 또는 일 부분 이 0 인 날짜 는 허용하지 않습니다. 예를 들어이 세트를 사용하면 0000-00-00이 허용되지만 1970-00-10또는 1929-01-00은 허용되지 않습니다.
  • NO_ZERO_DATE : 0000-00-00과 같은 잘못된 날짜를 허용되지 않습니다.
  • ERROR_FOR_DIVISION_BY_ZERO: 0으로 나누기를 시도할 때 오류를 발생시킵니다. 설정하지 않으면 0으로 나누기가 NULL을 반환합니다.
  • NO_ENGINE_SUBSTITUTION: 지정된 스토리지 엔진이 없을 때 MySQL이 대체 엔진을 사용하지 않도록 합니다. 이는 사용자가 의도한 스토리지 엔진을 정확하게 사용하도록 보장하여, 데이터베이스의 일관성을 유지할 수 있습니다. 새로운 테이블을 만들때 잘못된 스토리지 엔진을 설정한다면 에러를 반환합니다. 설정하지 않으면 기본 스토리지 엔진을 이용해 테이블을 생성합니다.
  • ONLY_FULL_GROUP_BY: GROUP BY 절에서 비집계 열이 명확하게 지정되지 않은 경우 오류를 발생시킵니다.

TRADITIONAL 모드의 장점

1. 데이터 무결성 보장: 잘못된 데이터가 데이터베이스에 삽입되지 않도록 하여 데이터 무결성을 유지할 수 있습니다.
2. 버그 조기 발견: 개발 단계에서 데이터 관련 오류를 조기에 발견하고 수정할 수 있습니다.
3. 표준 준수: SQL 표준에 더 가깝게 동작하므로, 다른 SQL 기반 시스템과의 호환성을 높일 수 있습니다.

그 밖의 SQL_MODE 옵션

• NO_AUTO_CREATE_USER: 사용자가 GRANT 명령을 사용할 때 자동으로 사용자 계정을 생성하지 않도록 하는 기능을 제공합니다. 이 모드는 보안 강화와 사용자 계정 관리를 더욱 엄격하게 하기 위해 사용됩니다.
• TIME_TRUNCATE_FRACTIONAL: MySQL에서 시간 값을 처리할 때 소수점 이하의 초 부분을 잘라내는 기능을 제공합니다. 이를 통해 시간 값을 보다 간단하게 관리할 수 있지만, 데이터 정밀도가 낮아질 수 있으므로 필요한 경우에만 사용해야 합니다. 2023-09-11 23:59:59.999999 이런 시간 데이터를 DATETIME 컬럼에 입력하면 2023-09-12 00:00:00.000 이런식으로 반올림을 해버리는 경우가 발생합니다. 이런 반올림 현상을 막기위해 사용할 수 있습니다.

건드려야 할 것 같아 보이지만 처음엔 수정할 필요가 없는 파라미터

sort_buffer_size

• sort_buffer_size는 ORDER BY 또는 GROUP BY를 사용하여 일부 쿼리에 대한 정렬을 수행합니다. sort_buffer_size를 구성하면 정렬 쿼리에 할당할 메모리 양이 결정됩니다.
• 워크로드에 상당한 수의 정렬 쿼리가 필요한 경우 sort_buffer_size를 기본값에서 조정해야 할 수 있습니다. sort_buffer_size는 세션 레벨에서 정의됩니다.
• sort_buffer_size는 정렬 쿼리 성능에 영향을 미칠 수 있습니다. sort_merge_passes 변수(정렬 중에 정렬 알고리즘이 완료해야 하는 패스 수)가 너무 큰 경우 sort_buffer_size 변수를 늘려 이에 대응할 수 있습니다.
• 쿼리 최적화나 인덱싱으로 더 이상 개선할 수 없는 정렬 작업 속도를 높이기 위해 sort_buffer_size를 늘리는 것을 고려할 수도 있습니다.
• 잘못된 값을 선택하면 성능이 저하되고 메모리 소비가 증가할 수 있으므로 sort_buffer_size는 신중하게 구성해야 합니다. 충돌이 발생할 수도 있습니다. 적절한 값은 작업 부하에 따라 매우 다르므로 서버에 대해 어떤 값을 선택해야 할지 확실하지 않은 경우 기본값을 변경하면 안 됩니다.

join_buffer_size

• MySQL Default = 256KB
• 서버에 인덱스가 없는 전체 테이블 조인이 필요할 때 join_buffer가 할당됩니다. 쿼리가 완료되면 메모리 할당이 해제됩니다.
• Join_buffer_size는 연결당 할당되기 때문에 MySQL은 시작 시 설정된 버퍼 크기 값에 최대 연결 수를 곱하여 할당합니다. 기본 Join_buffer_size는 성능에 도움이 되지 않고 서버 메모리를 낭비하므로 늘릴 필요가 거의 없습니다.
• Join_buffer 캐시 크기가 필요한 쿼리보다 큰 경우 상당한 성능 저하가 발생할 수 있습니다. 성능 향상을 위해 Join_buffer_size를 늘리는 대신 조인에 대한 인덱스를 추가하는 것이 좋습니다.
• 인덱스 추가가 불가능한 경우 세션별로 Join_buffer_size 특정 쿼리에만 설정하는 방법도 있습니다.

read_buffer_size

• MyISAM에만 적용되며 InnoDB 스토리지 엔진에는 영향을 미치지 않습니다.

read_rnd_buffer_size

• read_rnd_buffer_size 변수는 MyISAM 스토리지 엔진을 사용하여 정렬 후 정렬된 순서로 행을 읽는 데 가장 일반적으로 사용됩니다. 그러나 이 변수는 정렬 후 행을 최적으로 읽기 위해 InnoDB 엔진에서도 사용할 수도 있습니다.
• 정렬 작업 후 이 버퍼에서 읽으면 시스템이 불필요한 디스크 검색을 피할 수 있습니다.
• 상당한 성능 향상을 위해 ORDER BY 쿼리가 많은 서버의 경우 read_rnd_buffer_size 값을 늘릴 수 있습니다. 또한, 테이블 필드가 고정 크기로 변환될 수 있는 경우, 데이터 검색을 최적화하는 데 이 버퍼를 사용할 수 있습니다. 반면 데이터가 고정 길이를 갖지 않는 Blob/Text 타입에는 사용되지 않습니다.
• 이 버퍼는 서버의 각 클라이언트마다 할당되므로, 글로벌 변수를 변경하는 대신, 더 큰 쿼리를 실행하는 클라이언트 내에서 세션 변수를 변경하는 것이 좋습니다.

처음에 이 정도만 세팅을 해둔다면 시스템 규모가 커진 이후에도 DB에서 일어나는 일들에 대처 하기가 수월해지며, 나중에 더 시스템 규모가 커지거나 IPO, ISMS 등 감사가 필요하고, DBA가 필요한 시점이 되어도 개선 작업과 이관 작업등을 할 때 매우 높은 호환성과 데이터 무결성을 보장할 수 있습니다.