일상 MySQL 운영 및 유지 관리를 조정할 때 MySQL 구성 파일 my.cnf를 무시할 수 없습니다. MySQL의 기본 매개변수는 일상적인 온라인 비즈니스 요구 사항을 충족할 수 없으므로 매개변수 최적화도 필수적인 링크입니다. my.cnf 구성에 항목 수와 각 항목의 의미를 나열하고 싶지 않습니다. 이러한 항목은 공식 문서에서 찾을 수 있습니다. 다음은 일상 작업에서 주의해야 하는 일부 매개변수에 대해서만 설명합니다.
일부 매개변수는 아래에 설명되어 있습니다. 물론 로드나 하드웨어에 따라 다른 설정도 적용될 수 있습니다. 느린 메모리와 빠른 디스크, 높은 동시성 및 쓰기 집약적인 작업 부하가 있는 상황에서는 특별한 조정이 필요합니다. 그러나 여기서의 목표는 중요하지 않은 일부 MySQL 설정을 조정하거나 어떤 설정이 중요한지 찾기 위해 문서를 읽는데 너무 많은 시간을 소비하지 않고도 강력한 MySQL 구성을 빠르게 얻을 수 있도록 하는 것입니다.
MySQL 버전 5.5부터 InnoDB가 기본 스토리지 엔진으로 다른 스토리지 엔진보다 훨씬 많이 사용됩니다. 그렇기 때문에 신중하게 구성해야 합니다.
테이블 데이터와 인덱스는 공유 테이블스페이스 또는 별도의 테이블스페이스에 저장됩니다. 우리의 작업 시나리오 설치는 기본적으로 innodb_file_per_table = ON을 설정하며, 이는 작업 중에 별도의 테이블 공간을 쉽게 마이그레이션할 수도 있습니다. MySQL 5.6에서 이 속성의 기본값은 ON입니다.
기본값은 1입니다. 이는 InnoDB가 ACID 기능을 완벽하게 지원함을 의미합니다. 이 값은 마스터 노드와 같이 주요 관심사가 데이터 보안인 경우 가장 적합합니다. 그러나 디스크(읽기 및 쓰기) 속도가 느린 시스템의 경우 리두 로그에 변경 사항을 플러시할 때마다 추가 fsync가 필요하므로 엄청난 오버헤드가 발생합니다.
값을 2로 설정하면 신뢰할 수 없는 동작이 발생합니다. 커밋된 트랜잭션은 초당 한 번만 다시 실행 로그에 플러시되므로 일부 시나리오에서는 허용됩니다. 예를 들어 이 값은 기본 노드의 백업 노드에 허용됩니다. 값 0은 더 빠르지만 시스템 충돌 시 일부 데이터가 손실될 수 있습니다. 백업 노드에만 적용됩니다. 이 매개변수에 대해 이야기할 때 또 다른 sync_binlog가 확실히 떠오를 것입니다.
이 구성에 따라 데이터와 로그가 하드 디스크에 기록되는 방식이 결정됩니다. 기본적으로 O_DIRECT를 사용하는 세 가지 방법이 있습니다. O_DIRECT 모드: 데이터 파일의 쓰기 작업은 운영 체제 버퍼를 거치지 않고 mysql innodb 버퍼에서 디스크로 직접 이루어지며, 로그는 여전히 OS 버퍼를 거쳐야 합니다.
이 구성은 아직 실행되지 않은 트랜잭션에 할당되는 캐시를 결정합니다. 일반적으로 기본값(1MB)이면 충분하지만 트랜잭션에 큰 바이너리 개체나 큰 텍스트 필드가 포함된 경우 이 캐시가 빠르게 채워지고 추가 I/O 작업이 트리거됩니다. Innodb_log_waits 상태 변수를 살펴보고 0이 아닌 경우 innodb_log_buffer_size를 늘리십시오.
이 매개변수는 작동 및 유지 관리 중에 주의를 기울여야 하는 사항입니다. 버퍼 풀은 데이터와 인덱스가 캐시되는 곳으로, MySQL의 핵심 매개변수로, 기본값은 128MB이며, 이 매개변수는 실제 메모리의 60%~70%로 설정됩니다. (단, 저희 인스턴스는 기본적으로 여러 인스턴스의 혼합 배포이므로 이 값은 비즈니스 규모에 따라 분석이 필요합니다.)
리두 로그의 크기입니다. Redo 로깅은 쓰기 작업이 빠르고 안정적이며 충돌 시 복구될 수 있도록 하는 데 사용됩니다. 애플리케이션이 데이터를 자주 써야 한다는 것을 알고 있고 MySQL 5.6을 사용하고 있다면 처음부터 4G로 설정할 수 있습니다. (특정 크기는 귀하의 비즈니스에 따라 적절하게 조정되어야 합니다)
innodb_support_xa는 InnoDB의 XA 2단계 트랜잭션 제출을 전환할 수 있습니다. 기본적으로 innodb_support_xa=true는 XA 2단계 트랜잭션 제출을 지원합니다. XA 2단계 트랜잭션 제출로 인해 중복 플러시 및 기타 작업이 발생하므로 성능 영향은 10%에 도달합니다. 따라서 일부 DBA는 성능 향상을 위해 innodb_support_xa=false를 설정합니다. 이 경우 redolog와 binlog가 동기화되지 않으며, 메인 데이터베이스에 트랜잭션이 제출되었으나 binlog에 기록되지 않는 상황이 발생할 수 있다. 이로 인해 거래 데이터가 손실될 수도 있습니다.
이 매개변수는 데이터 필드 정보 및 기타 내부 데이터 구조를 저장하는 데 사용됩니다. 테이블이 많을수록 여기에 더 많은 메모리를 할당해야 합니다. InnoDB가 이 풀의 메모리가 부족하면 InnoDB는 운영 체제에서 메모리를 할당하기 시작하고 MySQL 오류 로그에 경고 메시지를 씁니다. 기본값은 8MB입니다. 일반 설정은 16MB입니다.
MySQL 서버의 기본 연결 수는 상대적으로 적으며 일반적으로 약 100개입니다. 최대값을 더 크게 설정하는 것이 가장 좋습니다. 일반적으로 500~1000으로 설정하면 각 링크가 일정량의 메모리를 차지하므로 매개변수가 클수록 좋습니다. 너무 많은 연결이 발생하면 이 매개변수의 크기를 늘리는 사람들도 있지만 실제로는 비즈니스 볼륨이나 프로그램 논리에 문제가 있거나 SQL이 제대로 작성되지 않은 경우 이 매개변수를 늘려도 도움이 되지 않습니다. 오류가 다시 발생하는 것은 시간 문제입니다. 애플리케이션에서 연결 풀링을 사용하거나 MySQL에서 프로세스 풀링을 사용하면 이 문제를 해결하는 데 도움이 될 수 있습니다.
매개변수 최적화의 궁극적인 목표는 메모리 할당을 합리적으로 제어하여 MySQL이 리소스를 더 잘 활용할 수 있도록 하는 것입니다.
스키마를 복사할 때 서버 ID가 다른지 확인하세요. 일반적으로 마스터 ID는 슬레이브 ID보다 작습니다.
데이터베이스 서버가 마스터 노드의 백업 노드 역할을 하게 하려면 바이너리 로그를 켜는 것이 필수입니다. 이렇게 하려면 server_id를 고유한 값으로 설정하는 것을 잊지 마십시오. 서버가 하나만 있어도 특정 시점 데이터 복구를 수행하려는 경우 이 기능(바이너리 로깅 켜기)이 유용합니다. 가장 최근 백업에서 복원하고(전체 백업) 바이너리 로그에 변경 사항을 적용합니다(증분 백업). .
바이너리 로그는 생성되면 영구적으로 저장됩니다. 따라서 디스크 공간이 부족해지는 것을 원하지 않으면 PURGE BINARY LOGS를 사용하여 오래된 파일을 제거하거나 만료_logs_days를 설정하여 로그가 자동으로 제거될 일 수를 지정할 수 있습니다. 바이너리 로깅에는 오버헤드가 없는 것이 아니므로 기본 노드가 아닌 레플리카 노드에서 필요하지 않은 경우 이 옵션을 끄는 것이 좋습니다.
클라이언트가 데이터베이스 서버에 연결되면 서버는 호스트 이름 확인을 수행하며, DNS가 느리면 연결 설정도 느려집니다. 따라서 DNS 조회를 수행하지 않고 서버를 시작할 때는 Skip_name_resolve 옵션을 끄는 것이 좋습니다. 유일한 제한은 나중에 GRANT 문에서 IP 주소만 사용할 수 있다는 점이므로 기존 시스템에 이 설정을 추가할 때는 주의해야 합니다.
sync_binlog의 기본값은 0입니다. 다른 파일을 새로 고치는 운영 체제의 메커니즘과 마찬가지로 MySQL은 디스크와 동기화하지 않지만 운영 체제에 의존하여 바이너리 로그를 새로 고칩니다.
sync_binlog =N(N>0)이면 MySQL은 fdatasync() 함수를 사용하여 바이너리 로그를 N 번 쓸 때마다 작성된 바이너리 로그를 디스크에 동기화합니다. innodb_flush_log_at_trx_commit과 sync_binlog가 모두 1일 때 가장 안전합니다. mysqld 서비스 충돌이나 서버 호스트 충돌의 경우 바이너리 로그는 최대 하나의 명령문이나 트랜잭션을 잃을 수 있습니다. 하지만 케이크를 먹고도 먹을 수는 없습니다. Double 1을 사용하면 IO 작업이 자주 발생하므로 이 모드도 가장 느린 방법입니다. 비즈니스 고려 사항에 따라 비즈니스 압력이 허용되는 경우 기본값은 double 1 구성입니다.
비즈니스에서 캐스케이드 아키텍처를 사용해야 하는 경우 log_slave_update = 1 매개변수를 켜야 합니다. 그렇지 않으면 세 번째 레벨이 첫 번째 레벨에서 생성된 binlog를 수신하지 못하여 데이터 동기화가 수행되지 않을 수 있습니다.
메모리 임시 테이블이 제한을 초과하면 MySQL은 자동으로 이를 디스크 기반 MyISAM 테이블로 변환하여 지정된 tmpdir 디렉터리에 저장합니다. 따라서 가능한 한 성능과 속도가 좋은 저장 장치로 tmpdir을 구성하세요.
slow_query_log = 1 #느린 로그 열기
slow_query_log_file = /mysql/log/mysql.slow
long_query_time = 0.5 #느린 로그에 쿼리가 몇 초 동안 입력되는지 설정
과학과 기술의 발전으로 점점 더 많은 저장 장치가 전통적인 기계 부품에서 전자 부품으로 구성된 영구 저장 장치로 전환되기 시작했으며 가격은 점점 더 기업에서 수용할 수 있게 되었습니다. 스토리지 구성 요소의 속도가 향상되면 기존 기계 구성 요소의 DB 구성을 사용하는 것이 낭비인 것 같습니다. 따라서 다양한 스토리지 기술에 따라 MySQL 구성을 조정해야 하며, 예를 들어 innodb_io_capacity를 늘리고 로그 파일을 다시 실행해야 합니다. SSD의 경우 원자 쓰기에는 Double Write Buffer, InnoDB 압축, 단일 머신 다중 인스턴스 + cgroup 등이 필요하지 않습니다. I/O 상황을 분석하고 innodb_io_capacity 및 innodb_max_dirty_pages_pct를 동적으로 조정하여 효과를 확인하십시오.
innodb_write_io_threads 및 innodb_read_io_threads에 대한 조정은 아직 수행하지 않았지만 8 또는 16으로 조정하면 시스템 I/O 성능이 더 좋아질 것이라고 믿습니다. 또한 다음 사항에 주의해야 합니다. 모든 조정은 데이터 지원과 엄격한 분석을 기반으로 해야 합니다. 그렇지 않으면 공허한 이야기가 될 것입니다. 이러한 유형의 튜닝은 매우 의미 있고 실제로 가치를 가져올 수 있습니다. 그러한 조정이 필요한 이유를 가능한 한 많이 이해합니다.
위 내용은 MySQL 최적화 매개변수 참조!의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
