보다 포괄적인 MySQL 최적화 참조

이 기사에서는 MySQL에 대한 몇 가지 일반적인 최적화 방법을 정리하고 간단한 요약을 작성하여, 정규 MySQL DBA가 없는 회사에서 특정 SQL 최적화 작업에 대한 대부분의 작업을 수행할 수 있도록 돕기 위해 작성했습니다. 적절한 인덱스를 추가하면 효과를 얻을 수 있으며 더 복잡한 경우에는 자세한 분석이 필요합니다.

1. 하드웨어 계층 관련 최적화

1.1 CPU 관련

서버의 BIOS 설정에서 조정할 수 있습니다. 다음은 CPU 성능을 최대화하거나 일반적인 NUMA 문제를 방지하도록 설계되었습니다.

1. CPU 성능을 최대화하려면 DAPC(성능 최적화) 모드를 선택합니다. 일반적으로 DB를 실행하려면 높은 컴퓨팅이 필요합니다. 많은 양의 서비스를 사용하는 경우 절전을 고려하지 마세요.

2. CPU 효율성을 높이려면 C1E 및 C 상태와 같은 옵션을 끄세요.

3. 최대 성능을 선택하세요. 메모리 주파수(최고 성능);

4. NUMA 문제를 방지하려면 메모리 설정 메뉴에서 노드 인터리빙을 활성화하세요.

1.2. 디스크 I/O 관련

IOPS 성능 개선 정도에 따라 디스크 I/O에 최적화할 수 있는 몇 가지 조치를 정렬합니다.

1. SSD 또는 PCIe SSD 장치를 사용하여 최소 수백 배 또는 심지어 만 배의 IOPS 개선을 얻습니다.

2. CACHE 모듈과 BBU 모듈을 모두 탑재한 어레이 카드를 구입하면 IOPS를 대폭 높일 수 있습니다(SSD나 PCIe SSD를 제외한 주로 기계식 디스크를 말합니다. 동시에 정기적인 상태 점검도 필요합니다) 사고 시 데이터가 손실되지 않도록 CACHE 및 BBU 모듈 상태)

3. 어레이 카드가 있는 경우 어레이 쓰기 전략을 WB 또는 FORCE WB로 설정합니다(있는 경우). 이중 전원 보호이거나 데이터 보안 요구 사항이 특별히 높지 않은 경우) WT 전략을 사용하는 것은 엄격히 금지됩니다. 그리고 폐쇄형 배열 미리 읽기 전략은 기본적으로 쓸모가 없으며 거의 ​​사용되지 않습니다.

4. RAID-5 대신 RAID-10을 최대한 사용합니다.

5. 기계식 디스크, 가능한 한 고속 디스크를 선택하십시오. 예를 들어 몇 달러도 안되는 7.2KRPM 디스크 대신 15KRPM 디스크를 선택하십시오.

2. 시스템 계층 관련; 최적화

2.1. 파일 시스템 계층 최적화

파일 시스템 계층에서 다음 조치를 취하면 IOPS 성능이 크게 향상될 수 있습니다.

1. 두 개의 I/O 스케줄러 기한을 사용합니다. /noop, cfq는 절대 사용하지 마세요. (DB 서비스 실행에는 적합하지 않습니다.)

2. xfs 파일 시스템을 사용하지 마세요. ext4는 거의 사용하지 않지만, 비즈니스 규모가 큰 경우에는 반드시 사용하세요. xfs;

3. 파일 시스템 마운트 매개변수에 noatime, nodiratime, nobarrier(nobarrier는 xfs 파일 시스템에 고유함)

　2.2. 🎜>

적절한 핵심 커널 매개변수 설정 값의 목적은 스왑 경향을 줄이고 메모리 및 디스크 I/O의 큰 변동을 방지하여 순간적인 최대 로드를 발생시키는 것입니다.

1. vm을 설정합니다. .swappiness를 약 5-10으로 설정하거나 0으로 설정하여(OOM 종료가 발생하도록 허용하지 않는 한 RHEL 7 이상에서는 0으로 설정하도록 주의) SWAP 사용 가능성을 줄입니다.

　2 vm.dirty_ground_ratio를 5-10으로 설정하고 vm .dirty_ratio를 약 2배로 설정하여 즉각적인 I/O 쓰기 및 심각한 대기를 방지하기 위해 더티 데이터를 디스크에 지속적으로 플러시할 수 있도록 합니다(MySQL의 innodb_max_dirty_pages_pct와 유사).

3. net .ipv4.tcp_tw_recycle과 net.ipv4.tcp_tw_reuse를 모두 1로 설정하여 TIME_WAIT를 줄이고 TCP 효율성을 향상시킵니다.

4. read_ahead_kb 및 nr_requests의 두 매개변수는 다음과 같습니다. 테스트 후 읽기 쓰기 혼합 OLTP 환경의 영향은 크지 않지만(읽기에 민감한 시나리오에서는 더 효과적이어야 함) 내 테스트 방법에 문제가 있을 수 있음을 발견했습니다.

3. MySQL 레이어 관련 최적화

3.1. 버전 선택에 대해

정식 버전을 ORACLE MySQL이라고 합니다. 나는 대부분의 사람들이 그것을 선택할 것이라고 믿습니다.

개인적으로 Percona 브랜치 버전을 선택하는 것이 좋습니다. 성능, 안정성, 관리 측면에서 많은 개선이 이루어진 비교적 성숙하고 우수한 MySQL 브랜치 버전입니다. 기본적으로 ORACLE MySQL 공식 버전과 완벽하게 호환되며 성능도 약 20% 이상 향상되었기 때문에 먼저 추천해드리고 2008년부터 사용하고 있습니다.

또 다른 중요한 브랜치 버전은 MariaDB입니다. MariaDB의 목표는 ORACLE MySQL을 대체하는 것이기 때문에 브랜치 버전이라고 말하는 것은 실제로 부적절합니다. 주로 원본 MySQL Server 계층에서 소스 코드 수준을 많이 개선했으며 매우 안정적이고 우수한 분기 버전이기도 합니다. 그러나 이로 인해 GTID로 대표되는 새로운 기능이 공식 버전과 호환되지 않게 되었습니다(MySQL 5.7부터 GTID 모드도 온라인에서 동적으로 켜거나 끌 수 있도록 지원됨). 공식 버전이므로 MariaDB를 먼저 권장하지 않습니다.

3.2. 가장 중요한 매개변수 옵션 조정에 대한 제안

더 나은 성능을 얻으려면 다음 주요 매개변수를 조정하는 것이 좋습니다(이 사이트에서 제공하는 my.cnf 생성기를 사용하여 생성할 수 있습니다). 구성 파일 템플릿):

1. Percona 또는 MariaDB 버전을 선택하는 경우 높은 동시성 조건에서 성능이 크게 저하되지 않도록 스레드 풀 기능을 활성화하는 것이 좋습니다. 또한 매우 실용적이며 중요한 순간에 생명을 구할 수 있는 extra_port 기능도 있습니다. 또 다른 중요한 기능은 QUERY_RESPONSE_TIME 함수로, 전체 SQL 응답 시간 분포에 대한 직관적인 느낌을 갖게 해줍니다.

　2. default-storage-engine=InnoDB를 설정합니다. 이는 InnoDB 엔진이 사용된다는 의미입니다. InnoDB 엔진은 확실히 99% 이상의 비즈니스 시나리오를 충족할 수 있습니다.

3. 단일 인스턴스인 경우 크기를 조정합니다. 대부분이 InnoDB 엔진 테이블이므로 물리적 메모리의 약 50% ~ 70%로 설정하는 것이 좋습니다.

4. 실제 필요에 따라 innodb_flush_log_at_trx_commit 및 sync_binlog 값을 설정합니다. 데이터가 손실될 수 없는 경우 둘 다 1로 설정합니다. 약간의 데이터 손실이 허용된다면 각각 2와 10으로 설정할 수 있습니다. 그리고 데이터가 손실되었는지 여부를 걱정할 필요가 없는 경우(예를 들어 슬레이브에서는 어쨌든 다시 실행됨) 모두 0으로 설정할 수 있습니다. 이 세 가지 설정 값이 데이터베이스 성능에 영향을 미치는 정도는 높음, 중간, 낮음입니다. 즉, 첫 번째 값은 데이터베이스를 가장 느리게 만들고 마지막 값은 그 반대가 됩니다. 🎜>

5. innodb_file_per_table = 1로 설정합니다. 독립 테이블스페이스를 사용하면 공유 테이블스페이스를 사용할 때의 이점이 전혀 생각나지 않습니다.

6. innodb_data_file_path = ibdata1:1G:autoextend를 설정합니다. 기본값 10M을 사용하지 마십시오. 그렇지 않으면 트랜잭션이 크게 영향을 받습니다.

7. 기본적으로 90% 이상의 시나리오를 충족할 수 있는 innodb_log_file_size=256M 및 innodb_log_files_in_group=2를 설정합니다.

8. long_query_time = 1로 설정합니다. 버전 5.5 이상에서는 1 미만으로 설정할 수 있습니다. 후속 분석 및 문제 해결을 위해 느리게 실행되는 SQL을 기록하려면 0.05(50밀리초)로 설정하는 것이 좋습니다.

9. 실제 비즈니스 요구에 따라 max_connection(최대 연결 수) 및 max_connection_error(최대 오류 수)를 100,000 이상으로 설정하는 것이 좋습니다. 매개변수는 open_files_limit, innodb_open_files, table_open_cache 및 table_definition_cache는 max_connection 크기의 약 10배로 설정할 수 있습니다.

10. 일반적인 오해는 tmp_table_size와 max_heap_table_size를 상대적으로 크게 설정하는 것입니다. 너무 크게 설정하지 마십시오. 그렇지 않으면 쉽게 OOM으로 이어질 수 있습니다. sort_buffer_size, Join_buffer_size, read_buffer_size, read_rnd_buffer_size 등과 같은 일부 다른 연결 세션 수준 옵션도 설정하지 않도록 주의해야 합니다. 너무 큽니다.

11. MyISAM 엔진을 더 이상 사용하지 않는 것이 권장되므로 key_buffer_size를 32M 정도로 설정하고 쿼리 캐시 기능을 끄는 것이 좋습니다.

3.3. 스키마 설계 사양 및 SQL 사용 제안

아래에는 MySQL 효율성을 향상시키는 데 도움이 되는 몇 가지 일반적인 스키마 설계 사양과 SQL 사용 제안이 나열되어 있습니다.

1. 모든 InnoDB 테이블 비즈니스 목적이 없는 자동 증가 열을 기본 키로 사용하여 설계되었습니다. 이는 대부분의 시나리오에 해당됩니다. 순수하게 읽기 전용인 InnoDB 테이블은 많지 않습니다. TokuDB 사용

2. 필드 길이가 요구 사항을 충족한다는 전제 하에 가능한 가장 작은 길이를 선택합니다. 또한 필드 속성에 NOT NULL 제약 조건을 추가하면 어느 정도 성능이 향상될 수 있습니다.

3. 필요한 경우 TEXT/BLOB 유형을 최대한 사용하지 않는 것이 좋습니다. SELECT *가 실행될 때 읽기 성능이 저하되는 것을 방지하려면 테이블을 하위 테이블로 분할하고 기본 테이블과 병합하지 마십시오.

4. 데이터를 읽을 때, 특히 일부 TEXT/BLOB 열을 읽을 때 심각한 무작위 읽기 문제를 방지하려면 항상 SELECT *를 선택하지 마세요. 🎜> 6. 일반적인 상황에서는 하위 쿼리의 성능이 상대적으로 떨어지므로 JOIN 작성 방식으로 변경하는 것이 좋습니다

8. 다중 테이블 연결 쿼리의 경우 결과가 작은 테이블을 사용합니다. set(필터링된 결과 집합을 의미하며 전체 테이블의 데이터 양이 작을 필요는 없음)를 구동 테이블로 설정합니다.

9. 여러 테이블을 조인하고 정렬하는 경우 정렬 필드가 있어야 합니다. 그렇지 않으면 정렬 열에서 인덱스를 사용할 수 없습니다.

10. 복합 인덱스를 더 많이 사용하고 다중 독립 인덱스의 사용을 줄입니다. 특히 카디널리티가 너무 작은 일부 열에 대해서는 독립 인덱스를 생성하지 마십시오. , 해당 열의 총 고유 값 수는 255개 미만입니다.

11. 유사한 페이징 기능이 있는 SQL의 경우 기본 키 연관을 먼저 사용한 다음 결과 집합을 반환하는 것이 좋습니다. 효율성이 훨씬 높아집니다.

3.4. 기타 제안

MySQL 관리 및 유지 관리에 대한 기타 제안은 다음과 같습니다.

1. 일반적으로 단일 테이블의 물리적 크기 10GB를 초과하지 않고, 단일 테이블의 행 수는 1억개를 초과하지 않으며, 평균 행 길이는 8KB를 초과하지 않습니다. 머신 성능이 충분하다면 MySQL은 이 정도의 데이터를 완전히 처리할 필요가 없습니다. 성능 문제에 대한 우려는 온라인 DDL의 높은 비용을 고려하는 것입니다.

2. mysqld 프로세스가 메모리를 너무 많이 차지한다고 너무 걱정하지 마세요. OOM kill이 발생하지 않고 SWAP가 많이 사용된다면 괜찮을 것입니다.

3. 과거에는 단일 머신에서 여러 인스턴스를 실행하는 목적이 컴퓨팅 리소스의 사용을 극대화하는 것이었습니다. 단일 인스턴스가 이미 대부분의 컴퓨팅 리소스를 소비할 수 있다면 여러 인스턴스를 실행할 필요가 없습니다.

4. 정기적으로 pt-duplicate-key-checker를 사용하여 중복된 인덱스를 확인하고 삭제하세요. 정기적으로 pt-index-usage 도구를 사용하여 사용 빈도가 매우 낮은 인덱스를 확인하고 삭제합니다.

5. 느린 쿼리 로그를 정기적으로 수집하고 pt-query-digest 도구와 결합할 수 있습니다. 느린 쿼리 관리를 위한 Anemometer 시스템

6. pt-kill을 사용하여 장기 SQL 요청을 종료할 수 있습니다. 이 기능도 구현할 수 있는 Percona 버전

7. pt-online-schema-change를 사용하여 대형 테이블의 온라인 DDL 요구 사항을 완료합니다.

8. 정기적으로 pt-table-checksum을 사용합니다. mysql 마스터-슬레이브 복제를 확인하고 복구하는 pt-table-sync 데이터 차이점

마지막에 작성: 이 최적화 참조를 위해 대부분의 경우 애플리케이션 시나리오가 다른 경우에 적용할 수 있는 시나리오를 소개했습니다. 이 글에서 설명하는 내용이라면 기계적인 조정보다는 실제 상황에 맞춰 진행하는 것이 좋습니다. 질문하고 제안하는 것은 환영하지만, 뇌를 통하지 않는 습관적인 저항은 거부됩니다.

위 내용은 비교적 포괄적인 MySQL 최적화 참고 자료입니다. 더 많은 관련 내용을 보려면 PHP 중국어 웹사이트(www.php.cn)를 주목하세요!