MySQL

by mamma1234 — on

Record

목차

parameter

  • innodb_buffer_pool_size는 MySQL에서 매우 중요한 설정으로, InnoDB 버퍼 풀의 크기를 결정하는 역할을 합니다. 버퍼 풀은 메모리 영역으로, InnoDB가 데이터를 캐시하여 디스크 접근을 최소화하고 읽기 및 쓰기 작업을 더 빠르게 처리할 수 있도록 돕습니다.

  • innodb_buffer_pool_size “{DBInstanceClassMemory3/4} 23622320128” {DBInstanceClassMemory1/2} “예를 들어, 100기가바이트의 데이터가 있지만 애플리케이션이 정기적으로 1기가바이트의 데이터에만 액세스한다고 가정해 봅시다. 그렇다면 버퍼 풀에 몇 기가바이트만 있으면 충분합니다. 반면에, 10기가바이트의 데이터가 있고 애플리케이션이 모든 데이터에 지속적으로 액세스하는 경우, 데이터와 인덱스 크기를 커버할 수 있을 만큼 충분히 큰 버퍼 풀을 갖고 싶습니다. 따라서, 우리는 당신이 당신의 응용 프로그램 요구 사항과 작업량 패턴에 따라 이 매개 변수의 값을 결정하는 것을 권장합니다. 전 권장 설정

    • 메모리의 50-75%: 전용 데이터베이스 서버에서는 서버 메모리의 50-75%를 버퍼 풀에 할당하는 것이 일반적입니다. • 모니터링 및 조정: 설정 후 서버 성능을 모니터링하고, 필요한 경우 버퍼 풀 크기를 조정해야 합니다. 워크로드가 변하면 innodb_buffer_pool_size도 이에 맞게 조정할 필요가 있습니다.” SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;

  • innodb_buffer_pool_instances “- 8” “기본값이 적용되는 이유

기본적으로 MySQL은 시스템의 메모리 크기와 버퍼 풀 크기에 따라 최적의 성능을 내기 위해 적절한 수의 버퍼 풀 인스턴스를 설정합니다. 따라서 -로 표시된 것은 MySQL이 최적의 설정을 자동으로 관리하고 있다는 의미입니다.

권장 설정

•	일반적으로, innodb_buffer_pool_size가 1GB 이상일 때 innodb_buffer_pool_instances를 1 이상으로 설정하는 것이 좋습니다.
•	innodb_buffer_pool_size가 충분히 크고, 서버에 높은 동시성이 요구되는 경우라면 4 또는 8개의 인스턴스를 설정할 수 있습니다."	SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';

  • innodb_log_file_size 134217728 629145600 “성능 및 복구 고려사항

    • 성능: 트랜잭션이 많고 업데이트가 빈번한 환경에서는 로그 파일의 크기를 늘려야 할 수 있습니다. 이는 로그 파일 순환을 줄이고 성능을 향상시킬 수 있습니다. • 복구 시간: 로그 파일이 클수록 시스템 충돌 후 복구 시간이 길어질 수 있습니다. 따라서 성능과 복구 시간 사이에서 적절한 균형을 찾는 것이 중요합니다.

권장 설정

일반적으로 로그 파일 크기를 너무 작게 설정하면 성능에 부정적인 영향을 미칠 수 있으므로, 트랜잭션 로드에 따라 적절한 값을 설정해야 합니다. 많은 시스템에서 256MB에서 1GB 사이의 크기가 적절한 경우가 많습니다. 그러나 구체적인 설정은 워크로드에 따라 달라질 수 있습니다.”

  • innodb_log_files_in_group 2 “권장 설정

    • 기본값 사용: 일반적으로 기본값인 2개가 적절합니다. 이 값은 대부분의 환경에서 안정적인 성능을 제공합니다. • 특수 환경: 높은 트랜잭션 처리량을 요구하거나 복구 시간을 단축하려는 특수 환경에서는 설정을 조정할 수 있습니다. 이 경우, 성능 테스트를 통해 적절한 값으로 조정하는 것이 좋습니다.”

  • innodb_change_buffering - “None (승민) All (대규)” “디폴트 all

innodb_change_buffering을 none으로 설정하면 버퍼링이 비활성화되어 즉시 디스크에 변경 사항이 기록되지만, 이로 인해 디스크 I/O가 증가하고 성능이 저하될 수 있습니다. 일반적으로 변경 버퍼링은 디스크 I/O를 줄여 성능을 개선하는 데 도움이 되므로, 이를 비활성화하기 전에 성능 테스트와 모니터링을 통해 최적의 설정을 찾는 것이 좋습니다.

권장 설정

•	대량의 인덱스 변경: 인덱스에 대한 대량의 삽입, 삭제, 업데이트가 자주 발생하는 환경에서는 변경 버퍼링을 활성화하는 것이 유리할 수 있습니다.
•	성능 모니터링: 설정 변경 후에는 시스템의 성능을 모니터링하여 버퍼링 설정이 적절한지 확인하고 필요에 따라 조정해야 합니다.

1.	all
•	설명: 모든 유형의 변경 사항(인덱스 삽입, 삭제, 업데이트)을 버퍼링합니다.
•	영향: 모든 변경 사항이 버퍼에 저장되며, 이는 디스크 I/O를 줄이고 성능을 개선할 수 있습니다.
2.	insert
•	설명: 인덱스에 대한 삽입 작업만 버퍼링합니다.
•	영향: 삭제 및 업데이트 작업은 즉시 디스크에 기록되며, 삽입 작업만 버퍼링됩니다.
3.	delete
•	설명: 인덱스에 대한 삭제 작업만 버퍼링합니다.
•	영향: 삽입 및 업데이트 작업은 즉시 디스크에 기록되며, 삭제 작업만 버퍼링됩니다.
4.	change
•	설명: 업데이트 작업만 버퍼링합니다.
•	영향: 삽입 및 삭제 작업은 즉시 디스크에 기록되며, 업데이트 작업만 버퍼링됩니다.
5.	none
•	설명: 버퍼링을 비활성화하고 모든 변경 사항을 즉시 디스크에 기록합니다.
•	영향: 디스크 I/O 작업이 증가할 수 있지만, 데이터의 즉각적인 일관성을 보장할 수 있습니다."	SHOW VARIABLES LIKE 'innodb_change_buffering';

  • innodb_io_capacity “- 200” 1000 “권장 설정

    • 디스크 성능과 워크로드에 따라 조정: innodb_io_capacity의 값을 디스크 성능과 데이터베이스 워크로드에 따라 조정합니다. SSD와 같은 고성능 디스크는 높은 값을 지원하며, 전통적인 HDD는 낮은 값을 설정해야 할 수 있습니다. • 성능 모니터링: 설정 변경 후 시스템의 I/O 성능을 모니터링하고, 필요에 따라 조정하여 최적의 성능을 유지합니다.”

  • innodb_io_capacity_max “- 2000” 2000 일반적으로 innodb_io_capacity_max는 innodb_io_capacity의 2배로 설정됩니다 innodb_sync_array_size 8 16 테스트 “권장 설정

    • 디스크 성능과 워크로드에 따라 조정: innodb_io_capacity_max의 값을 디스크 성능과 데이터베이스 워크로드에 따라 조정합니다. SSD와 같은 고성능 디스크는 높은 값을 지원하며, 전통적인 HDD는 낮은 값을 설정해야 할 수 있습니다. • 성능 모니터링: 설정 변경 후 시스템의 I/O 성능을 모니터링하고, 필요에 따라 조정하여 최적의 성능을 유지합니다.

  • innodb_sync_array_size는 InnoDB의 로그와 체크포인트를 디스크에 기록하는 방식을 최적화하는 중요한 파라미터입니다. innodb_sync_array_size=16은 일반적으로 좋은 시작값으로 알려져 있지만, 최적의 값은 데이터베이스의 동시 연결 수, 워크로드 특성, 시스템 자원 등을 고려하여 로드 테스트를 통해 결정하는 것이 바람직합니다.”

  • innodb_flush_log_at_trx_commit 2 “master 1 slave 2 (승민) 0 (대규)” “설정 값

    • innodb_flush_log_at_trx_commit = 0: • 로그는 트랜잭션 커밋 시에 디스크에 플러시되지 않고, 주기적으로 플러시됩니다. 일반적으로 1초에 한 번 디스크에 플러시됩니다. • 성능은 가장 좋지만, 시스템 크래시 시 데이터 손실 가능성이 가장 큽니다. • innodb_flush_log_at_trx_commit = 1: • 트랜잭션이 커밋될 때마다 로그가 즉시 디스크에 플러시됩니다. • 가장 높은 데이터 내구성을 보장하지만, 디스크 I/O가 증가하여 성능에 영향을 미칠 수 있습니다. • innodb_flush_log_at_trx_commit = 2: • 트랜잭션이 커밋될 때 로그가 디스크의 OS 캐시에 플러시되고, 실제 디스크에는 1초마다 플러시됩니다. • 성능과 데이터 안정성 사이에서 중간 정도의 균형을 제공합니다.

2인 경우, crash 발생시 데이터 유실 이슈 있을 수 있음 replica 사용시, master는 1, slave 2 권장, slave 0 고려”

  • internal_tmp_mem_storage_engine TempTable “설정 값

    • MEMORY: • 장점: 메모리에서 모든 작업이 이루어지므로 성능이 뛰어나며, 디스크 I/O를 피할 수 있습니다. • 단점: 서버가 재시작되거나 크래시가 발생하면 데이터가 손실됩니다. 메모리 용량에 제한이 있으므로 대량의 데이터에는 적합하지 않을 수 있습니다. • InnoDB or TempTable ?? • 장점: 데이터의 내구성을 보장합니다. 서버 재시작 후에도 데이터가 유지됩니다. 대량의 데이터 처리에 적합합니다. • 단점: 디스크 I/O가 발생하므로 MEMORY보다 성능이 떨어질 수 있습니다.”

  • tmp_table_size “- 16777216” 67108864 테스트 “tmp_table_size는 MySQL에서 임시 테이블의 최대 크기를 설정하는 파라미터입니다. 이 파라미터는 메모리 기반 임시 테이블의 최대 크기를 지정하며, 이를 초과할 경우 MySQL은 디스크 기반 임시 테이블로 전환합니다.

권장 설정

•	메모리 용량: 서버의 메모리 용량에 따라 tmp_table_size 값을 적절히 조정합니다. 너무 큰 값을 설정하면 서버 메모리 부족 현상이 발생할 수 있습니다.
•	쿼리 성능: 임시 테이블의 크기를 고려하여 성능을 최적화합니다. 임시 테이블의 크기가 자주 tmp_table_size를 초과하는 경우, 값을 조정하거나 쿼리 성능을 개선하는 방법을 고려할 수 있습니다.

역할과 중요성

•	임시 테이블의 크기 제한: tmp_table_size는 메모리 내 임시 테이블의 최대 크기를 제한합니다. 임시 테이블이 이 크기를 초과하면 MySQL은 해당 임시 테이블을 디스크에 저장하여, 성능이 떨어질 수 있습니다.
•	성능 최적화: 임시 테이블이 메모리 내에서 관리되면 성능이 더 우수하지만, 메모리 용량에는 한계가 있습니다. tmp_table_size 값을 적절히 설정하면 쿼리 성능을 최적화할 수 있습니다."

  • max_heap_table_size “- 16777216” 67108864 테스트 “tmp_table_size와의 관계

    • tmp_table_size: 메모리 기반 임시 테이블의 최대 크기를 설정합니다. tmp_table_size는 MEMORY 스토리지 엔진을 사용하는 임시 테이블의 최대 크기를 정의합니다. 이 값이 max_heap_table_size와 동일하거나 그보다 커야 합니다. • 호환성: 두 값이 서로 다를 경우, tmp_table_size가 max_heap_table_size보다 큰 경우에만 tmp_table_size의 설정이 의미가 있습니다. 따라서, 일반적으로 두 값을 동일하게 설정하거나, tmp_table_size가 max_heap_table_size보다 커야 합니다.”

  • temptable_max_ram 1073741824 테스트 “권장 설정

    • 메모리 용량: 서버의 총 메모리 용량에 따라 temptable_max_ram 값을 적절히 조정합니다. 너무 큰 값을 설정하면 메모리 부족 문제가 발생할 수 있으며, 너무 작은 값을 설정하면 디스크 기반 임시 테이블로 전환되어 성능이 저하될 수 있습니다. • 쿼리 성능: 임시 테이블의 크기와 쿼리 성능을 고려하여 설정을 조정합니다. 쿼리가 자주 메모리 기반 임시 테이블을 사용하는 경우, 메모리 사용량을 적절히 조정하여 성능을 개선할 수 있습니다.”

  • temptable_max_mmap “- 1073741824” 테스트 “temptable_max_mmap는 MySQL 8.0에서 메모리 맵핑(메모리 매핑)을 사용하여 임시 테이블을 저장할 때의 최대 크기를 설정하는 파라미터입니다. 이 파라미터는 MEMORY 스토리지 엔진을 사용하는 임시 테이블이 메모리 맵핑 방식으로 디스크에 저장될 때의 최대 크기를 결정합니다.

권장 설정

•	디스크 I/O와 메모리: 서버의 디스크 성능과 메모리 용량을 고려하여 temptable_max_mmap 값을 조정합니다. 너무 큰 값을 설정하면 메모리 사용량이 증가할 수 있으며, 너무 작은 값을 설정하면 메모리 맵핑을 사용할 수 있는 임시 테이블의 크기가 제한될 수 있습니다.
•	쿼리 성능: 임시 테이블의 크기와 쿼리 성능을 고려하여 설정을 조정합니다. 임시 테이블이 자주 메모리 맵핑 방식으로 디스크에 저장되는 경우, 메모리 맵핑 크기를 적절히 설정하여 성능을 개선할 수 있습니다."

  • table_open_cache “- 4000” 5194 “검토 및 조정

    1. 테이블 수와 연결 수: • 테이블 수: SHOW GLOBAL STATUS LIKE ‘Open_tables’; 명령어를 사용하여 현재 열린 테이블 수를 확인합니다. 이 값을 기준으로 table_open_cache 값을 조정할 수 있습니다. • 연결 수: SHOW VARIABLES LIKE ‘max_connections’; 명령어를 사용하여 max_connections 값을 확인합니다. 이를 기반으로 table_open_cache 값을 설정할 때 두 배의 값을 사용하는 것이 일반적인 방법입니다.
    2. 성능 모니터링: • Open_tables: MySQL 서버에서 Open_tables 상태 변수를 모니터링하여, 현재 열린 테이블 수와 캐시 히트 비율을 확인합니다. • 성장 여유: 현재의 Open_tables 값과 예상되는 트래픽 성장에 따라 여유를 두어 설정합니다.

2597 * 2”

  • table_open_cache_instances 16 “ • 테이블 캐시(table_open_cache): MySQL 서버가 열린 테이블의 정보를 메모리에 캐시하는 공간입니다. 이 캐시를 통해 자주 접근하는 테이블을 빠르게 열 수 있어 성능이 향상됩니다. • table_open_cache_instances: 이 파라미터는 table_open_cache를 여러 인스턴스로 나누어 관리하는 데 사용됩니다. 이를 통해 여러 스레드가 동시에 테이블 캐시를 사용하려 할 때 발생하는 잠금 충돌을 줄여 성능을 개선할 수 있습니다.

요약

•	5194의 table_open_cache 값에 대해 **table_open_cache_instances = 8 ~ 16**이 적절한 선택일 수 있습니다.
•	이 범위 내에서 선택하면 각 캐시 인스턴스가 충분한 테이블을 관리할 수 있으며, 동시성 처리도 효율적으로 이루어집니다."

  • max_connections {DBInstanceClassMemory/12582880} “DBInstanceClassMemory 16G 17179869184 23G 34359738368”

  • net_write_timeout “- 60” 120 net_write_timeout은 MySQL에서 클라이언트가 데이터를 수신하는 데 시간이 너무 오래 걸리면 서버가 연결을 끊기 전에 기다리는 최대 시간을 설정하는 파라미터입니다. 이 파라미터는 대용량 쿼리 결과를 클라이언트로 전송할 때 발생할 수 있는 네트워크 문제를 완화하는 데 유용합니다.

  • sync_binlog 0 “설정 권장 사항

    • 복제 환경: MySQL 복제를 사용하는 경우, sync_binlog = 1로 설정하는 것이 일반적으로 권장됩니다. 이 설정은 데이터 손실을 방지하고, 복제에서 데이터 일관성을 보장합니다. • 데이터 손실 위험 최소화: 서버 크래시가 발생할 때 데이터 유실을 최소화하려면 sync_binlog = 1로 설정하는 것이 좋습니다. • 성능 최적화: 데이터 손실의 위험을 감수할 수 있고, 성능이 중요한 경우 sync_binlog 값을 1보다 크게 설정하거나 0으로 설정할 수 있습니다.

요약

•	sync_binlog = 0: 성능 좋음, 데이터 손실 위험 있음.
•	sync_binlog = 1: 데이터 손실 위험 최소화, 복제 환경에서 권장.
•	sync_binlog > 1: 성능과 안정성 간의 균형을 위해 조정 가능."

  • binlog_row_image “- FULL” NOBLOB “요약

    • FULL: 모든 컬럼을 기록, 가장 안전하지만 로그 크기가 큼. • MINIMAL: 변경된 컬럼만 기록, 로그 크기가 작지만 데이터 무결성에 주의 필요. • NOBLOB: BLOB/TEXT 제외, 특정 상황에서 로그 크기 감소.”

  • max_allowed_packet “- 67108864” “개념 설명

    • 패킷(Packet): MySQL에서 패킷은 클라이언트와 서버 간에 전송되는 데이터의 단위입니다. 하나의 패킷에는 SQL 쿼리, 결과 세트, 바이너리 데이터 등이 포함될 수 있습니다. • max_allowed_packet: 이 파라미터는 MySQL 서버가 하나의 패킷에서 허용할 수 있는 최대 크기를 정의합니다. 패킷 크기가 이 값을 초과하면 서버는 데이터를 수신하지 않으며, 에러가 발생합니다.

요약

•	기본값: 64MB (MySQL 8.0 기준)
•	최대값: 일반적으로 1GB까지 설정 가능
•	사용 사례: 대용량 BLOB, TEXT, JSON 데이터를 처리하거나 큰 SQL 쿼리를 실행할 때 값 조정 필요"

  • slave_pending_jobs_size_max “- 134217728” “요약

    • 역할: 복제 슬레이브의 대기 중인 작업의 최대 크기를 설정 • 기본값: 버전에 따라 다를 수 있음 • 설정 방법: SET GLOBAL 명령어 또는 설정 파일을 통해 변경 • 조정 시 고려 사항: 복제 지연, 메모리 사용량, 성능 최적화”

  • query_cache_size “• 설명: 쿼리 캐시의 크기를 설정합니다. MySQL 8.0에서는 기본적으로 쿼리 캐시가 비활성화되어 있습니다. • 영향: 쿼리 성능과 캐시 효율성에 영향을 미칩니다.”

  • innodb_flush_method O_DIRECT “개념 설명

    • 플러시(Flush): 데이터와 로그를 디스크에 기록하는 과정입니다. InnoDB 스토리지 엔진은 메모리에서 디스크로 데이터를 플러시하여 데이터의 영속성을 보장합니다. • 플러시 방법: innodb_flush_method는 이러한 플러시 과정에서 사용될 시스템 호출 방법을 정의합니다.

요약

•	O_DSYNC: 데이터 무결성 보장, 성능 저하 가능성.
•	O_DIRECT: 성능 최적화, 캐시 우회, 데이터 무결성에 주의 필요.
•	DEFAULT: 시스템의 기본 설정 사용"

  • innodb_log_buffer_size 8388608 16777216 “요약

    • 기본값: 16MB (MySQL 8.0 기준) • 설정 방법: SET GLOBAL 명령어 또는 MySQL 설정 파일을 통해 변경 • 조정 고려 사항: 트랜잭션 빈도, 메모리 사용량, 로그 플러시 빈도”

  • innodb_flush_log_at_timeout “- 1” “개념 설명

    • 로그 플러시: InnoDB 스토리지 엔진은 트랜잭션 로그를 메모리에 버퍼링한 후 주기적으로 디스크에 플러시합니다. innodb_flush_log_at_timeout는 이 플러시 작업의 최대 간격을 설정합니다. • 목적: 로그를 주기적으로 디스크에 기록하여 데이터 무결성을 보장하면서도 성능을 조절할 수 있습니다. 이 설정은 주로 데이터베이스의 성능과 무결성 간의 균형을 맞추는 데 사용됩니다.”

  • performance_schema 1

AWS

  • BinLogDiskUsage
  • BurstBalance
  • CPUUtilization
  • DatabaseConnections
  • DBLoad
  • DBLoadCPU
  • DBLoadNonCPU
  • DBLoadRelativeToNumVCPUs
  • DiskQueueDepth
  • EBSByteBalance
  • EBSIOBalance
  • FreeableMemory
  • FreeStorageSpace
  • LVMReadIOPS
  • LVMWriteIOPS
  • NetworkReceiveThroughput
  • NetworkTransmitThroughput
  • ReadIOPS
  • ReadLatency
  • ReadThroughput
  • ReplicaLag
  • SwapUsage
  • WriteIOPS
  • WriteLatency
  • WriteThroughput
  • TotalIOPS
mamma1234's photo Author

mamma1234

mamma1234@gmail.com

A person longing for any dream for a long time resembles that dream at last .