MySQL - 새로 설치된 MySQL에 맞게 조정해야 하는 10가지 구성에 대한 자세한 소개

새로 설치된 mysql 서비스에 대해 아직도 고민 중이신가요? 어떤 기본 구성을 수정해야 할지 모르시나요? 즉시 조정해야 할 가장 중요한 매개변수는 무엇인가요? 🎜>

이 글에서는 MySQL 최적화를 위해 조정해야 할 10가지 구성을 주로 소개합니다. 이러한 방법을 사용하면 강력한 MySQL 구성을 빠르게 얻을 수 있습니다.

우리가 MySQL 성능을 모니터링하도록 고용되면 사람들은 우리가 MySQL 구성을 검토하고 개선 사항을 제안하기를 기대합니다. 수백 가지 구성 옵션이 있음에도 불구하고 몇 가지 설정만 변경하라고 제안하면 많은 사람들이 놀랐습니다. 이 문서의 목적은 매우 중요한 구성 항목 목록을 제공하는 것입니다.

몇 년 전에 블로그에서 이런 조언을 드렸는데, MySQL의 세계는 너무 빨리 변하고 있습니다.

시작하기도 전에 쓴 글입니다...

그럼에도 불구하고! 경험이 많은 사람은 실수를 할 수 있고, 이로 인해 많은 문제가 발생할 수 있습니다. 따라서 이러한 권장 사항을 맹목적으로 적용하기 전에 다음 사항을 기억하세요.

한 번에 하나의 설정만 변경하세요. 이것이 변경 사항이 유익한지 테스트할 수 있는 유일한 방법입니다.

대부분의 구성은 SET GLOBAL을 사용하여 런타임에 변경할 수 있습니다. 이는 문제가 발생한 경우 변경 사항을 신속하게 취소할 수 있는 매우 편리한 방법입니다. 그러나 이를 영구적으로 만들려면 구성 파일을 변경해야 합니다.

MySQL을 다시 시작해도 변경 사항이 적용되지 않나요?

올바른 구성 파일을 사용하고 있는지 확인하세요. 구성을 올바른 영역에 배치했는지 확인하십시오(이 문서에 언급된 모든 구성은 [mysqld]에 속함)

구성 변경 후 서버를 시작할 수 없습니다. 올바른 구성 단위를 사용하고 있는지 확인하십시오.

예를 들어 innodb_buffer_pool_size의 단위는 MB이고 max_connection에는 단위가 없습니다.

구성 파일에 중복된 구성 항목을 포함하지 마세요. 변경 사항을 추적하려면 버전 관리를 사용하세요.

“이제 서버 메모리가 2배로 늘어났으니 이전 값을 2배로 하려면 모든 값을 바꿔야 한다” 같은 순진한 계산은 하지 마세요.

1. 기본 구성

다음 3가지 구성 항목을 자주 확인해야 합니다. 그렇지 않으면 문제가 빨리 발생할 수 있습니다.

innodb_buffer_pool_size:

InnoDB 설치 후 가장 먼저 설정해야 하는 옵션입니다.

버퍼 풀은 데이터와 인덱스가 캐시되는 곳입니다. 값이 클수록 대부분의 읽기 작업에 하드 디스크 대신 메모리를 사용하는 것이 좋습니다. 일반적인 값은 5~6GB(8GB 메모리), 20~25GB(32GB 메모리), 100~120GB(128GB 메모리)이다.

innodb_log_file_size:

리두 로그의 크기입니다. Redo 로깅은 쓰기 작업이 빠르고 안정적이며 충돌 시 복구될 수 있도록 하는 데 사용됩니다.

MySQL 5.1까지는 성능 향상을 위해 크기를 더 크게 하고, 충돌 후 더 빠르게 복구하기 위해 크기를 더 작게 만들고 싶었기 때문에 튜닝이 어려웠습니다. 다행히 MySQL 5.5부터 크래시 복구 성능이 크게 향상되어 높은 쓰기 성능과 크래시 복구 성능을 동시에 가질 수 있게 되었습니다. MySQL 5.5까지는 리두 로그의 총 크기가 4GB로 제한되었습니다(기본적으로 2개의 로그 파일이 있을 수 있음). 이는 MySQL 5.6에서 개선되었습니다.

innodb_log_file_size를 처음부터 512M로 설정하면(즉, 1GB의 리두 로그가 있음) 쓰기 작업을 위한 충분한 공간이 제공됩니다. 애플리케이션이 데이터를 자주 써야 한다는 것을 알고 있고 MySQL 5.6을 사용하고 있다면 처음부터 4G로 설정할 수 있습니다.

max_connections:

'연결이 너무 많습니다' 오류가 자주 표시되는 경우는 max_connections 값이 너무 낮기 때문입니다. 이는 애플리케이션이 데이터베이스 연결을 제대로 닫지 않고 기본 연결 151개보다 더 높은 값이 필요하기 때문에 매우 일반적입니다. max_connection 값이 높게 설정된 경우(예: 1000 이상) 주요 단점은 1000개 이상의 활성 트랜잭션을 실행할 때 서버가 응답하지 않는다는 것입니다. 애플리케이션에서 연결 풀링을 사용하거나 MySQL에서 프로세스 풀링을 사용하면 이 문제를 해결하는 데 도움이 될 수 있습니다.

2. InnoDB 구성

MySQL 버전 5.5부터 InnoDB가 기본 스토리지 엔진으로 다른 스토리지 엔진보다 훨씬 많이 사용됩니다. 그렇기 때문에 신중하게 구성해야 합니다.

innodb_file_per_table:

이 설정은 InnoDB가 공유 테이블 공간(innodb_file_per_table = OFF)의 모든 테이블에 대한 데이터와 인덱스를 저장해야 하는지 또는 각 테이블에 대해 데이터와 인덱스를 저장해야 하는지 여부를 알려줍니다. 데이터는 별도의 .ibd 파일에 배치됩니다(innodb_file_per_table = ON). 테이블당 하나의 파일을 사용하면 테이블을 삭제하거나 자르거나 재구축할 때 디스크 공간을 회수할 수 있습니다. 이는 데이터 압축과 같은 일부 고급 기능에도 필요합니다. 그러나 성능상의 이점은 없습니다. 테이블당 하나의 파일을 원하지 않는 주요 시나리오는 매우 많은 수의 테이블(예: 10,000개 이상)이 있는 경우입니다.

MySQL 5.6에서는 이 속성의 기본값이 ON이므로 대부분의 경우 아무것도 수행할 필요가 없습니다. 이전 버전에서는 새로 생성된 테이블에만 영향을 주기 때문에 데이터를 로드하기 전에 이 속성을 ON으로 설정해야 했습니다.

innodb_flush_log_at_trx_commit:

기본값은 1이며 이는 InnoDB가 ACID 기능을 완벽하게 지원함을 나타냅니다. 이 값은 마스터 노드와 같이 주요 관심사가 데이터 보안인 경우 가장 적합합니다. 그러나 디스크(읽기 및 쓰기) 속도가 느린 시스템의 경우 리두 로그에 변경 사항을 플러시할 때마다 추가 fsync가 필요하므로 엄청난 오버헤드가 발생합니다. 값을 2로 설정하면 커밋된 트랜잭션이 초당 한 번만 다시 실행 로그에 플러시되기 때문에 신뢰성이 떨어지지만 기본 노드의 백업 노드와 같은 일부 시나리오에서는 허용됩니다. . 의. 값 0은 더 빠르지만 시스템 충돌 시 일부 데이터가 손실될 수 있습니다. 백업 노드에만 적용됩니다.

innodb_flush_method:

이 구성은 데이터와 로그가 하드 디스크에 기록되는 방식을 결정합니다. 일반적으로 하드웨어 RAID 컨트롤러가 있고 해당 독립 캐시가 후기입 메커니즘을 사용하고 배터리 전원 장애 보호 기능이 있는 경우 O_DIRECT로 설정해야 하며, 그렇지 않은 경우 대부분의 경우 fdatasync로 설정해야 합니다(기본값). ). sysbench는 이 옵션을 결정하는 데 도움이 되는 훌륭한 도구입니다.

innodb_log_buffer_size:

이 구성은 아직 실행되지 않은 트랜잭션에 할당되는 캐시를 결정합니다. 일반적으로 기본값(1MB)이면 충분하지만 트랜잭션에 큰 바이너리 개체나 큰 텍스트 필드가 포함된 경우 이 캐시가 빠르게 채워지고 추가 I/O 작업이 트리거됩니다. Innodb_log_waits 상태 변수를 살펴보고 0이 아닌 경우 innodb_log_buffer_size를 늘리십시오.

3. 기타 설정

query_cache_size:

쿼리 캐시(query 캐시)는 잘 알려진 병목 현상입니다. 이는 동시성이 많지 않은 경우에도 마찬가지입니다.

가장 좋은 옵션은 처음부터 이를 비활성화하고 query_cache_size = 0(현재 MySQL 5.6의 기본값)을 설정한 다음 다른 방법을 사용하여 쿼리 속도를 높이는 것입니다. 즉, 인덱스를 최적화하거나 복사본을 추가하여 부하를 분산시키거나 활성화하는 것입니다. 추가 캐시(예: Memcache 또는 Redis). 애플리케이션에 대해 쿼리 캐시를 활성화했지만 아무런 문제도 발견하지 못한 경우 쿼리 캐시가 유용할 수 있습니다. 비활성화하려는 경우 주의해야 할 사항입니다.

log_bin:

데이터베이스 서버가 마스터 노드의 백업 노드 역할을 하도록 하려면 바이너리 로그를 켜야 합니다. 이렇게 하려면 server_id를 고유한 값으로 설정하는 것을 잊지 마십시오. 서버가 하나만 있어도 특정 시점 데이터 복구를 수행하려는 경우 이 기능(바이너리 로깅 켜기)이 유용합니다. 가장 최근 백업에서 복원하고(전체 백업) 바이너리 로그에 변경 사항을 적용합니다(증분 백업). . 일단 생성된 바이너리 로그는 영구적으로 저장됩니다. 따라서 디스크 공간이 부족해지는 것을 원하지 않으면 PURGE BINARY LOGS를 사용하여 오래된 파일을 제거하거나 만료_logs_days를 설정하여 로그가 자동으로 제거될 일 수를 지정할 수 있습니다.

바이너리 로그 로깅에는 오버헤드가 없기 때문에 기본 노드가 아닌 레플리카 노드에서 필요하지 않은 경우 이 옵션을 끄는 것이 좋습니다.

skip_name_resolve:

클라이언트가 데이터베이스 서버에 연결하면 서버가 호스트 이름 확인을 수행하며, DNS가 느리면 연결 설정도 느려집니다. 따라서 DNS 조회를 수행하지 않고 서버를 시작할 때는 Skip_name_resolve 옵션을 끄는 것이 좋습니다. 유일한 제한은 나중에 GRANT 문에서 IP 주소만 사용할 수 있다는 점이므로 기존 시스템에 이 설정을 추가할 때는 주의해야 합니다.

요약

물론 로드나 하드웨어에 따라 작동할 수 있는 다른 설정도 있습니다. 느린 메모리와 빠른 디스크, 높은 동시성 및 쓰기 집약적 로드 시, 특별한 조정이 필요합니다. 그러나 여기서의 목표는 중요하지 않은 일부 MySQL 설정을 조정하거나 어떤 설정이 중요한지 찾기 위해 문서를 읽는데 너무 많은 시간을 소비하지 않고도 강력한 MySQL 구성을 신속하게 얻을 수 있다는 것입니다.

위 내용은 MySQL - 새로 설치된 MySQL에 맞게 조정해야 하는 10가지 구성에 대한 자세한 소개의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!