MySQL5.6 기본 구성 상세 설명

이 글에서는 주로 MySQL5.6의 기본 최적화 구성을 소개하고, MySQL5.6에서 최적화가 필요한 구성 항목을 자세하게 분석하고, 마지막으로 도움이 필요한 친구들이 참고할 수 있는 최적화 사례를 제공합니다

개선과 함께. 많은 기본 옵션 중에서 MySQL 5.6은 이전 버전보다 튜닝 옵션이 훨씬 적습니다. 이 기사에서는 최적화가 필요한 구성 항목에 대해 설명하겠습니다.

1.innodb_buffer_pool_size ——기본값은 128M입니다. InnoDB가 데이터를 로드하는 데 사용하는 메모리 양과 인덱스

(데이터+인덱스)를 지정하므로 가장 중요한 최적화 옵션입니다. 예를 들어 물리적 메모리가 64GB인 경우 캐시 풀은 약 50GB로 설정해야 합니다.

이 값을 더 크게 설정하면 여유 메모리가 부족해지는 등의 위험이 있을 수 있습니다. 바이너리 로그, InnoDB 트랜잭션 로그(transaction 로그) 등을 포함하여 파일 시스템캐시)을 사용하는 운영 체제 및 일부 MySQL 하위 시스템
2.innodb_log_file_size - 기본값은 48M입니다. 높은 쓰기 처리량은 이 값을 늘려야 합니다. 백그라운드 체크포인트 활동이 장기간에 걸쳐 원활하게 기록되도록 허용하면 이 값을 4G 미만으로 설정하는 것이 매우 안전합니다. 과거 사례에 따르면 대용량 로그 파일의 단점은 충돌이 증가한다는 것입니다. time 필요한 수리 시간이지만 5.5 및 5.6에서 크게 개선되었습니다.

3.innodb_flush_method - 기본값은 fdatasync입니다. 하드웨어 RAID 디스크 controller를 사용하는 경우 O_DIRECT로 설정해야 할 수 있습니다. InnoDB 버퍼 풀을 읽으면 "이중 버퍼링" 효과를 방지할 수 있습니다. 그렇지 않으면 파일 시스템 캐시와 InnoDB 캐시 사이에 2개의 복사본이 형성됩니다. 하드웨어 RAID 컨트롤러를 사용하지 않거나 SAN 스토리지를 사용하는 경우 O_DIRECT 성능 저하가 발생할 수 있습니다. MySQL 사용자 매뉴얼과 버그 #54306에 이에 대한 자세한 설명이 있습니다.

4.innodb_flush_neighbors - 기본값은 1입니다. SSD 스토리지에서는 성능 향상이 없으므로 0(비활성화)으로 설정해야 합니다.
5.innodb_io_capacity 및 innodb_io_capacity_max

- 이 설정은 InnoDB가 수행하는 작업 수에 영향을 미칩니다. 초당 백그라운드에서 수행할 수 있는 작업 수 등 하드웨어 성능에 대해 깊이 이해하고 있다면 유휴 상태로 두는 것보다 이러한 기능을 사용하는 것이 매우 좋습니다. 비유: 특정 항공편에 대해 단일 티켓이 판매되지 않은 경우 나중에 악천후가 발생할 경우 이후 항공편의 일부 사람들이 해당 항공편을 이용하도록 하는 것이 좋은 전략일 수 있습니다.

매우 간단한 계산이 있습니다. 각 디스크가 초당 200회(IOPS)를 읽고 쓸 수 있다면 10개 디스크가 있는 RAID10 디스크 어레이의 이론적 IOPS는 ( 10/2) * 200 = 1000. RAID 컨트롤러는 일반적으로 추가 병합을 제공하고 IOPS 용량을 효과적으로 늘릴 수 있기 때문에 "매우 간단하다"고 말합니다. SSD 디스크의 경우 IOPS가 수천에 쉽게 도달할 수 있습니다. 이 두 값을 너무 높게 설정하면 백그라운드 작업이 포그라운드 작업 IO 작업의 성능을 방해하는 것을 원하지 않을 것입니다. 과거 경험에 따르면 두 값을 너무 높게 설정하면 됩니다. ​​너무 높게 설정되면 InnoDB가 보유한 내부 잠금으로 인해 성능이 저하됩니다(제가 아는 정보에 따르면 이는 MySQL5.6에서 크게 개선되었습니다).

innodb_lru_scan_length - 기본값은 1024입니다. mysql 5.6에 도입된 새로운 옵션 Mark Callaghan은 몇 가지 구성 제안을 제공했습니다. 간단히 말해서 innodb_io_capacity 값을 늘리면 innodb_lru_scan_length도 늘려야 합니다.

Replication(복제)


서버가 마스터-슬레이브 지원을 원하는 경우 복제 또는 특정 시점 복구를 수행하려면 다음이 필요합니다.

1.log-bin - 바이너리 로그를 활성화합니다. 기본적으로 바이너리 로그는 충돌로부터 안전하지 않습니다. 하지만 이전 기사에서 언급했듯이 대부분의 사용자는 안정성을 목표로 해야 한다고 권장합니다. 이 경우 sync_binlog=1, sync_relay_log=1, Relay-log-info-repository=TABLE 및 master-info-repository=TABLE.

2도 활성화해야 합니다. 만료-로그-일
- 기본적으로 오래된 로그는 영원히 보관됩니다. 1~10일로 설정하는 것이 좋습니다. 백업에서 복원하는 것이 훨씬 빠르기 때문에 더 오랜 시간을 절약해도 소용이 없습니다.

3. server-id

- 마스터-슬레이브 복제 토폴로지의 모든 서버는 고유한 서버 ID로 설정되어야 합니다.

4.binlog_format=ROW - 행 기반 복제로 수정됨 최근에 행 기반 복제에 대한 또 다른 기사를 작성했는데, 이는 두 가지 추가 설정도 줄여서 성능을 향상시킬 수 있기 때문에 이를 정말 좋아하는 이유를 설명합니다. 활성화해야 합니다: transaction-isolation=READ-COMMITTED 및 innodb_autoinc_lock_mode = 2.

기타 구성(기타)

1.timezone=GMT 시간대를 GMT로 설정하는 것이 점점 더 많은 시스템 관리자가 권장됩니다. 그리니치 표준시(GMT)는 요즘 거의 모든 비즈니스가 글로벌이기 때문에 개인적으로 이것을 매우 좋아합니다. 현지 시간대로 설정하는 것은 다소 임의적인 것 같습니다.

2.character-set-server=utf8mb4 및 collation-server =utf8mb4_general_ci 이전 기사에서 언급했듯이 utf8 인코딩은 새 애플리케이션에 대한 더 나은 기본 옵션입니다. 또한 애플리케이션이 설정하는 다른 character-set(문자 집합)을 무시하도록 Skip-character-set-client-handshake를 설정할 수도 있습니다. 설정하고 싶습니다.

3.sql-mode - MySQL은 기본적으로 비표준 데이터를 허용하며 데이터를 자동으로 자릅니다. 제 경우에는 이전 기사에서 새 애플리케이션을 다음과 같이 설정하는 것이 가장 좋다고 언급했습니다.

STRICT_TRANS_TABLES,ERROR_FOR_pISION_BY_ZERO,
 NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,
 NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,
 NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY.

4.skip-name-resolve - 역방향 이름 확인을 비활성화합니다. 일부 시스템에서는 DNS 확인이 약간 느리거나 좋지 않을 수 있으므로 호스트 이름 기반 인증이 필요하지 않은 경우 이 해결 방법을 사용하지 않는 것이 좋습니다.

5.max_connect_errors - Todd Farmer는 "[이 기능]은 무차별 접근 공격에 대한 모의 보호를 제공합니다." 실제로 Skip-name-resolve를 설정할 때 max_connect_errors는 작동하지 않습니다(이전 단락 참조). 방화벽이 더 적합한 솔루션입니다. 일반적으로 공용 포트이든 인트라넷 포트이든 포트 3306을 차단하고 특정 응용 프로그램만 MySQL에 액세스하고 연결할 수 있습니다.

일반적으로 "이중 구성"을 방지하기 위해 max_connect_errors=100000을 설정합니다. " 방해가 되지 않도록 하세요.

6.max-connections

- 기본값은 151입니다. 많은 사용자가 이 값을 더 큰 값(주로 300에서 1000 사이)으로 설정하는 것을 봅니다. 일반적으로 불가피합니다. 이 값은 더 큰 값으로 설정되겠지만, 조금 불안한 점은 16코어 머신의 경우 IO 블로킹의 경우 연결 실행 용량이 2배~10배 정도 밖에 되지 않는다는 점입니다 많이 오픈해주셨으면 좋겠습니다. 연결이 유휴 상태이고 휴면 상태입니다. 그러나 연결이 모두 활성화된 경우 많은 수의 새 스레드가 생성될 수 있습니다(스레드 스래시).
조건이 허용되면 애플리케이션이 해결하도록 최적화된 데이터베이스 연결 풀(연결 풀)을 구성할 수 있습니다. 이 문제는 많은 수의 연결을 열고 유지하는 대신
연결 풀을 사용하지 않는 것(비풀링), 빠르게 열고 작업을 수행한 다음 가능한 한 빨리 연결을 닫는 응용 프로그램도 가능합니다.
5.5부터 시작하는 또 다른 솔루션(MySQL Community Edition과 Enterprise Edition 사이에는 약간의 차이가 있음)은 스레드 풀 플러그인을 사용하는 것입니다.

결론

MySQL 서버의 구성이

1.64GB 물리적 메모리

2라고 가정합니다. . 하드웨어 RAID 컨트롤러(IO가 초당 2000 IOPS에 도달할 수 있다고 가정)
3. 마스터-슬레이브 복제(복제)가 필요합니다
4 .새로운 애플리케이션(예: 레거시가 아닌 시스템)
5. 인증 기반 없음 도메인 이름(호스트 이름, 호스트 이름)이 필요합니다.
7. 글로벌 애플리케이션은 특정 시간대에 고정되기를 원하지 않습니다.
8. 프로그램이 안정적이고 안정적이기를 원하는 경우

구성은 다음과 같습니다. 다음과 같습니다:

# InnoDB settings
innodb_buffer_pool_size=50G
innodb_log_file_size=2G
innodb_flush_method=O_DIRECT
innodb_io_capacity=2000
innodb_io_capacity_max=6000
innodb_lru_scan_depth=2000
# Binary log/replication
log-bin
sync_binlog=1
sync_relay_log=1
relay-log-info-repository=TABLE
master-info-repository=TABLE
expire_logs_days=10
binlog_format=ROW
transaction-isolation=READ-COMMITTED
innodb_autoinc_lock_mode = 2
# Other
timezone=GMT
character-set-server=utf8
collation-server=utf8_general_ci
sql-mode="STRICT_TRANS_TABLES,
 ERROR_FOR_DIVISION_BY_ZERO,
 NO_AUTO_CREATE_USER,
 NO_AUTO_VALUE_ON_ZERO,
 NO_ENGINE_SUBSTITUTION,
 NO_ZERO_DATE,
 NO_ZERO_IN_DATE,
 ONLY_FULL_GROUP_BY"
skip-name_resolve
max-connect-errors=100000
max-connections=500
# Unique to this machine
server-id=123

위 내용은 MySQL5.6 기본 구성 상세 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!