분류 sql

MySQL 데이터베이스 성능: 이 일반적인 실수를 피하십시오

컨텐츠 정보

  • 조회 355 (작성일 )

본문

제가 자주 받는 지원 요청 이메일 주제 중 하나는 MySQL 데이터베이스 성능입니다. 

클라이언트는 MySQL이 너무 많은 서버 메모리를 사용하고, 너무 많은 MySQL 느린 쿼리를 사용하고, 유명한 Mysql 서버가 사라짐 오류 및 기타 많은 MySQL 성능 관련 문제에 대해 불평합니다. 

따라서 일반적인 MySQL 구성 실수에 대한 솔루션을 공유하고 싶었습니다. 올해 들어 지금까지 이 작업을 수십 번 정도 봤습니다.


DBA인 경우 의견 섹션에서 경험이나 제안을 자유롭게 공유하십시오. 

4개의 MySQL 구성 변수를 언급하고 있습니다. 네 줄은 종종 들어오는 연결에 사용할 수 있는 서버 메모리 부족으로 인해 MySQL 데이터베이스 성능 및 확장성이 저하되는 원인이 됩니다. 

이 문서는 MySQL용 MariaDBPercona 드롭인 교체에도 적용됩니다.


MySQL 연결당 버퍼를 임의로 늘리지 마십시오. 


my.cnf MySQL config file 

my.cnf 구성에서 발췌. (위험한 오용을 피하기 위해 일부 줄을 삭제하거나 흐리게 처리)


이 나쁜 습관이 어디서, 언제 시작되었는지 누가 압니까? 처음으로 프로덕션 서버에 SSH로 접속하고 이러한 변경에 대한 타당한 이유 없이 거의 모든 my.cnf 변수의 값이 증가하고 증가하는 것을 발견하는 것이 일반적이었습니다.


일반적으로 이러한 버퍼를 늘릴 타당한 이유를 제공할 수 없는 경우 기본값으로 설정하는 것이 좋습니다. 


이것은 결코 좋은 습관이 아니며 특히 이 4개의 my.cnf 변수에서 더욱 그렇습니다. 일부 변수의 값을 높이면 성능이 향상될 수 있지만 이 네 가지 변수는 증가하면 거의 항상 MySQL 서버 성능과 용량이 저하됩니다.


문제의 4가지 버퍼는 join_buffer_size, sort_buffer_size, read_buffer_size 및 read_rnd_buffer_size입니다. 

이 4개의 버퍼는 연결당 할당됩니다. 

예를 들어 max_connections=200과 함께 join_buffer_size=1M을 설정하면 연결당 추가 1M(1M x 200)을 할당하도록 MySQL이 구성됩니다. 다른 세 개의 버퍼도 마찬가지입니다. 

다시 말하지만, 모두 연결당입니다.


거의 모든 경우에 이 네 가지 구성 줄을 제거하거나 주석 처리하여 기본값을 유지하는 것이 가장 좋습니다. 

트래픽과 함께 연결이 증가함에 따라 증가된 버퍼 설정으로 인해 사용 가능한 것보다 더 많은 공간이 필요한 쿼리는 해당 버퍼를 디스크로 페이징할 수 있습니다. 이렇게 하면 DB 서버 속도가 크게 느려지고 병목 현상이 발생합니다. 또한 Top으로 Linux 서버 성능 분석을 읽으십시오.


mysql_status-868x119.png 

첫 번째 최적화 통과 후 2주 후의 MySQL 상태 출력.


종종 문제의 버퍼를 기본값으로 되돌리기만 하면 MySQL 성능이 향상되는 것을 보았습니다. 이러한 각 버퍼에 대해 간략히 살펴보겠습니다.


MySQL join_buffer_size 


join_buffer_size는 두 테이블 간의 각 전체 조인에 할당됩니다. MySQL의 문서에서 join_buffer_size는 "일반 인덱스 스캔, 범위 인덱스 스캔 및 인덱스를 사용하지 않아 전체 테이블 스캔을 수행하는 조인에 사용되는 버퍼의 최소 크기"로 설명됩니다.


MySQL의 문서에서는 "메모리 할당 시간으로 인해 전역 크기가 이를 사용하는 대부분의 쿼리에서 필요로 하는 것보다 큰 경우 상당한 성능 저하가 발생할 수 있습니다."라고 말합니다. 조인 버퍼는 조인이 인덱스를 사용할 수 없을 때 캐시 테이블 행에 할당됩니다.


데이터베이스가 인덱스 없이 수행되는 많은 조인으로 인해 문제가 있는 경우 join_buffer_size를 늘려서 해결할 수 없습니다. 문제는 "인덱스 없이 수행된 조인"입니다. 따라서 더 빠른 조인을 위한 솔루션은 인덱스를 추가하는 것입니다.


MySQL sort_buffer_size 


달리 나타내는 데이터가 없으면 sort_buffer_size도 임의로 늘리는 것을 피해야 합니다. 여기의 메모리도 연결별로 할당됩니다! MySQL의 문서에서는 다음과 같이 경고합니다. "리눅스에는 256KB와 2MB의 임계값이 있으며 이 경우 값이 클수록 메모리 할당 속도가 크게 느려질 수 있으므로 이러한 값 중 하나 미만으로 유지하는 것을 고려해야 합니다." 이점을 없애는 성능 저하가 있으므로 sort_buffer_size를 2M 이상으로 늘리지 마십시오.


MySQL read_buffer_size & read_rnd_buffer_size 


여러분 중 일부는 여전히 InnoDB보다 MyISAM을 사용합니다. 어떤 경우에는 정당한 이유가 있습니다. MyISAM은 이전 ISAM 스토리지 엔진을 기반으로 합니다. 여기에 설명된 것처럼 유용한 확장이 많이 있습니다.


read_buffer_size 일반적으로 MyISAM에만 적용되며 InnoDB에는 영향을 미치지 않습니다. 이 버퍼를 늘리기 전에 MySQL 테이블을 InnoDB 스토리지 엔진으로 변환하는 것을 고려하십시오. InnoDB는 MySQL 5.7 및 MySQL 8.0의 기본 스토리지 엔진입니다. InnoDB는 데이터를 보호하기 위한 롤백 및 충돌 복구 기능을 제공합니다.


read_buffer_size 메모리 테이블의 메모리 블록 크기를 결정하는 데도 사용됩니다. read_rnd_buffer_size 변수는 주로 테이블에서 MyISAM 읽기에 사용됩니다. 또한 InnoDB 또는 MariaDB의 Aria 스토리지 엔진을 고려하십시오. 지난 10년 동안 이러한 버퍼의 기본값은 동일하게 유지되었습니다.


결론 


좋은 경험 법칙: 이러한 버퍼를 증가시키는 타당한 이유를 제공할 수 없다면 기본값으로 설정해 두십시오. 이것은 또한 모든 MySQL 변수에 덜 엄격하게 적용됩니다. 변경할 때 주의하십시오. 전체 my.cnf를 한 번에 덮어쓰지 마십시오. 먼저 모든 것을 백업한 다음 다시 시작할 때마다 한두 번 변경하고 24~48시간 동안 테스트한 다음 다시 통과하십시오.


출처 : https://haydenjames.io/mysql-database-performance-avoid-this-common-mistake/