MySQL의 10 가지 필수 성능 팁

모든 관계형 데이터베이스와 마찬가지로 MySQL은 복잡한 짐승임을 입증 할 수 있습니다. 즉, 순간적으로 크롤링하여 애플리케이션을 불안정하게 만들고 비즈니스를 중단시킬 수 있습니다.

사실, 일반적인 실수는 대부분의 MySQL 성능 문제의 기저에 있습니다. MySQL 서버가 최고 속도로 윙윙 거리면서 안정적이고 일관된 성능을 제공하려면 워크로드 또는 구성 트랩의 미묘함으로 인해 종종 가려지는 이러한 실수를 제거하는 것이 중요합니다.

다행히도 많은 MySQL 성능 문제에 유사한 솔루션이있는 것으로 밝혀져 MySQL의 문제 해결 및 튜닝을 관리 가능한 작업으로 만듭니다.

다음은 MySQL에서 뛰어난 성능을 얻기위한 10 가지 팁입니다.

MySQL 성능 팁 1 : 워크로드 프로파일 링

서버가 시간을 보내는 방식을 이해하는 가장 좋은 방법은 서버의 워크로드를 프로파일 링하는 것입니다. 워크로드를 프로파일 링하여 추가 튜닝을 위해 가장 비싼 쿼리를 노출 할 수 있습니다. 여기서 시간은 가장 중요한 지표입니다. 서버에 대해 쿼리를 실행할 때 완료 속도를 제외하고는 아무것도 신경 쓰지 않기 때문입니다.

워크로드를 프로파일 링하는 가장 좋은 방법은 MySQL Enterprise Monitor의 쿼리 분석기 또는 Percona Toolkit의 pt-query-digest와 같은 도구를 사용하는 것입니다. 이러한 도구는 서버가 실행하는 쿼리를 캡처하고 응답 시간의 감소 순서로 정렬 된 작업 테이블을 반환하여 가장 비용이 많이 들고 시간이 많이 소요되는 작업을 즉시 맨 위로 올림으로써 작업에 집중할 수있는 위치를 확인할 수 있습니다.

워크로드 프로파일 링 도구는 유사한 쿼리를 그룹화하여 속도가 느린 쿼리와 빠르지 만 여러 번 실행되는 쿼리를 볼 수 있도록합니다.

MySQL 성능 팁 2 : 네 가지 기본 리소스 이해

데이터베이스 서버가 작동하려면 CPU, 메모리, 디스크 및 네트워크의 네 가지 기본 리소스가 필요합니다. 이들 중 하나가 약하거나, 비정상적이거나, 과부하 상태이면 데이터베이스 서버의 성능이 저하 될 가능성이 높습니다.

기본 리소스를 이해하는 것은 하드웨어 선택과 문제 해결이라는 두 가지 특정 영역에서 중요합니다.

MySQL 용 하드웨어를 선택할 때 모든 부분에서 우수한 성능의 구성 요소를 확인하십시오. 마찬가지로 중요한 것은 서로에 대해 합리적으로 균형을 유지하는 것입니다. 종종 조직에서는 CPU와 디스크가 빠르지 만 메모리가 부족한 서버를 선택합니다. 경우에 따라 메모리 추가는 특히 디스크 바운드 워크로드에서 성능을 몇 배나 높이는 저렴한 방법입니다. 이것은 직관적이지 않은 것처럼 보일 수 있지만 많은 경우 서버의 작업 데이터 세트를 저장할 메모리가 충분하지 않기 때문에 디스크가 과도하게 사용됩니다.

이 균형의 또 다른 좋은 예는 CPU와 관련이 있습니다. 대부분의 경우 MySQL은 각 쿼리가 단일 스레드에서 실행되고 CPU간에 병렬화 될 수 없기 때문에 빠른 CPU에서 잘 수행됩니다.

문제 해결과 관련하여 네 가지 리소스 모두의 성능과 활용도를 확인하고 성능이 저조한 지 아니면 단순히 너무 많은 작업을 요청 받는지 신중하게 확인하십시오. 이 지식은 문제를 신속하게 해결하는 데 도움이 될 수 있습니다.

MySQL 성능 팁 3 : MySQL을 대기열로 사용하지 마십시오

큐 및 큐와 같은 액세스 패턴은 사용자가 알지 못하는 사이에 애플리케이션에 스며들 수 있습니다. 예를 들어 특정 작업자 프로세스가 작업을 수행하기 전에 항목을 요청할 수 있도록 항목의 상태를 설정하면 무의식적으로 대기열을 생성하는 것입니다. 이메일을 보내지 않음으로 표시하고 보낸 다음 보낸 것으로 표시하는 것이 일반적인 예입니다.

대기열은 두 가지 주요 원인으로 문제를 일으 킵니다. 작업 부하를 직렬화하여 작업이 병렬로 수행되는 것을 방지하며 종종 오래 전에 처리 된 작업의 기록 데이터와 WIP가 포함 된 테이블이 생성됩니다. 둘 다 애플리케이션에 지연 시간을 추가하고 MySQL에로드합니다.

MySQL 성능 팁 4 : 가장 저렴한 순서로 결과 필터링

MySQL을 최적화하는 가장 좋은 방법은 저렴하고 부정확 한 작업을 먼저 수행 한 다음 더 작은 결과 데이터 세트에 대해 어렵고 정확한 작업을 수행하는 것입니다.

예를 들어 지리적 지점의 주어진 반경 내에서 무언가를 찾고 있다고 가정합니다. 많은 프로그래머의 도구 상자에서 첫 번째 도구는 구 표면을 따라 거리를 계산하는 대원 (Haversine) 공식입니다. 이 기술의 문제는 수식에 CPU 집약적 인 많은 삼각 연산이 필요하다는 것입니다. 대원 계산은 느리게 실행되고 컴퓨터의 CPU 사용률이 급증하는 경향이 있습니다.

대원 공식을 적용하기 전에 레코드를 합계의 작은 하위 집합으로 줄인 다음 결과 집합을 정확한 원으로 자릅니다. 원을 포함하는 사각형 (정확하게 또는 부정확하게)은이를 수행하는 쉬운 방법입니다. 이렇게하면 광장 밖의 세계는 값 비싼 모든 삼각 함수로 타격을받지 않습니다.

MySQL 성능 팁 5 : 두 가지 확장 성 죽음의 함정 파악

확장 성은 당신이 생각하는 것만 큼 모호하지 않습니다. 사실, 방정식으로 표현되는 확장 성의 정확한 수학적 정의가 있습니다. 이 방정식은 시스템이 제대로 확장되지 않는 이유를 강조합니다.

시스템의 확장 성 특성을 표현하고 정량화하는 데 유용한 정의 인 Universal Scalability Law를 살펴보십시오. 직렬화와 누화라는 두 가지 기본 비용 측면에서 확장 문제를 설명합니다.

직렬화 된 작업이 발생하기 위해 중지해야하는 병렬 프로세스는 본질적으로 확장 성이 제한됩니다. 마찬가지로 병렬 프로세스가 작업을 조정하기 위해 항상 서로 채팅해야하는 경우 서로 제한됩니다.

직렬화 및 누화를 피하면 애플리케이션이 훨씬 더 잘 확장됩니다. 이것은 MySQL 내부에서 무엇으로 번역됩니까? 다양하지만 몇 가지 예는 행에 대한 배타적 잠금을 피하는 것입니다. 위의 3 번 지점 인 대기열은 이러한 이유로 인해 제대로 확장되지 않는 경향이 있습니다.

MySQL 성능 팁 6 : 구성에 너무 집중하지 마십시오.

DBA는 구성을 조정하는 데 엄청난 시간을 소비하는 경향이 있습니다. 결과는 일반적으로 큰 개선이 아니며 때로는 매우 손상 될 수도 있습니다. 지속적으로 충돌하고 메모리가 부족하며 워크로드가 조금 더 심해지면 성능이 저하되는 "최적화 된"서버를 많이 보았습니다.

MySQL과 함께 제공되는 기본값은 한 가지 크기에 적합하지 않고 매우 오래되었지만 모든 것을 구성 할 필요는 없습니다. 기본 사항을 올바르게 설정하고 필요한 경우에만 다른 설정을 변경하는 것이 좋습니다. 대부분의 경우 약 10 개의 옵션을 올바르게 설정하면 서버 최고 성능의 95 %를 얻을 수 있습니다. 이것이 적용되지 않는 몇 가지 상황은 귀하의 상황에 고유 한 경우가 될 것입니다.

대부분의 경우 서버 "튜닝"도구는 특정 경우에 적합하지 않은 지침을 제공하는 경향이 있으므로 권장되지 않습니다. 일부는 캐시 적중률 및 메모리 소비 공식과 같은 위험하고 부정확 한 조언이 코딩되어 있습니다. 이것들은 결코 옳지 않았으며 시간이 지남에 따라 훨씬 덜 정확 해졌습니다.

MySQL 성능 팁 7 번 : 페이지 매김 쿼리주의

페이지를 매기는 응용 프로그램은 서버를 무너 뜨리는 경향이 있습니다. 다음 페이지로 이동할 수있는 링크가있는 결과 페이지를 표시 할 때 이러한 응용 프로그램은 일반적으로 인덱스를 사용할 수없는 방식으로 그룹화하고 정렬 하며 서버가 많은 작업을 생성하도록 하는 LIMITand offset를 사용합니다 . 그런 다음 행을 버립니다.

최적화는 종종 사용자 인터페이스 자체에서 찾을 수 있습니다. 결과에 정확한 페이지 수와 각 페이지에 대한 링크를 개별적으로 표시하는 대신 다음 페이지에 대한 링크 만 표시 할 수 있습니다. 또한 사람들이 첫 페이지에서 너무 먼 페이지로 이동하는 것을 방지 할 수 있습니다.

쿼리 쪽에서는 LIMITwith 를 사용 하는 대신 offset필요한 것보다 하나 더 많은 행을 선택할 수 있으며 사용자가 "다음 페이지"링크를 클릭하면 해당 최종 행을 다음 결과 집합의 시작 지점으로 지정할 수 있습니다. 예를 들어, 사용자가 101 행부터 120 행까지의 페이지를 본 경우 121 행도 선택합니다. 다음 페이지를 렌더링하려면 서버에 121, 제한 21 이상의 행을 쿼리합니다.

MySQL 성능 팁 8 : 통계를 열심히 저장하고 마지 못해 경고

모니터링 및 경고는 필수적이지만 일반적인 모니터링 시스템은 어떻게됩니까? 오탐을 보내기 시작하고 시스템 관리자는 소음을 막기 위해 이메일 필터링 규칙을 설정합니다. 곧 귀하의 모니터링 시스템은 완전히 쓸모가 없습니다.

측정 항목 캡처와 경고라는 두 가지 방법으로 모니터링하는 것을 좋아합니다. 시스템에서 변경된 사항을 파악하려고 할 때 기뻐할 것이기 때문에 가능한 모든 메트릭을 캡처하고 저장하는 것이 매우 중요합니다. 언젠가는 이상한 문제가 생기고 그래프를 가리키고 서버 작업 부하의 변화를 보여주는 기능을 좋아할 것입니다.

대조적으로 너무 많이 경고하는 경향이 있습니다. 사람들은 종종 버퍼 적중률이나 초당 생성되는 임시 테이블 수와 같은 것에 대해 경고합니다. 문제는 그러한 비율에 대한 좋은 임계 값이 없다는 것입니다. 올바른 임계 값은 서버마다 다를뿐만 아니라 워크로드가 변경됨에 따라 시간마다 다릅니다.

결과적으로 명확하고 실행 가능한 문제를 나타내는 조건에 대해서만 경고를 드물게합니다. 낮은 버퍼 적중률은 실행 불가능하거나 실제 문제를 나타내지 않지만 연결 시도에 응답하지 않는 서버는 해결해야하는 실제 문제입니다.

MySQL 성능 팁 9 번 : 인덱싱의 세 가지 규칙 알아보기

인덱싱은 인덱스가 작동하는 방식과 서버가 인덱스를 사용하는 방식을 혼동하는 방법이 너무 많기 때문에 데이터베이스에서 가장 오해를받는 주제 일 것입니다. 무슨 일이 일어나고 있는지 정말로 이해하려면 많은 노력이 필요합니다.

적절하게 설계된 인덱스는 데이터베이스 서버에서 세 가지 중요한 용도로 사용됩니다.

  1. 인덱스를 사용하면 서버가 단일 행 대신 인접한 행 그룹을 찾을 수 있습니다. 많은 사람들이 인덱스의 목적이 개별 행을 찾는 것이라고 생각하지만 단일 행을 찾으면 임의 디스크 작업이 발생하여 속도가 느립니다. 한 번에 하나씩 행을 찾는 것보다 전체 또는 대부분이 흥미로운 행 그룹을 찾는 것이 훨씬 낫습니다.
  2. 인덱스를 사용하면 서버가 원하는 순서로 행을 읽어 정렬을 피할 수 있습니다. 정렬은 비용이 많이 듭니다. 원하는 순서로 행을 읽는 것이 훨씬 빠릅니다.
  3. 인덱스를 사용하면 서버가 인덱스에서만 전체 쿼리를 충족 할 수 있으므로 테이블에 전혀 액세스 할 필요가 없습니다. 이를 커버링 인덱스 또는 인덱스 전용 쿼리라고 다양하게 알려져 있습니다.

이러한 세 가지 기회를 활용하기 위해 인덱스와 쿼리를 디자인 할 수 있다면 쿼리를 몇 배 더 빠르게 만들 수 있습니다.

MySQL 성능 팁 10 : 동료의 전문 지식 활용

혼자 가려고하지 마십시오. 문제에 대해 수수께끼를하고 논리적이고 합리적으로 보이는 일을한다면 그것은 훌륭합니다. 이것은 20 개 중 약 19 번 작동 할 것입니다. 다른 경우에는 매우 비용이 많이 들고 시간이 많이 소요되는 토끼 구멍에 빠지게 될 것입니다. 정확히 시도하는 솔루션이 의미가있는 것처럼 보이기 때문입니다.

MySQL 관련 리소스 네트워크를 구축하십시오. 이는 도구 세트 및 문제 해결 가이드를 넘어서는 것입니다. 메일 링리스트, 포럼, Q & A 웹 사이트 등에 매우 지식이 풍부한 사람들이 숨어 있습니다. 컨퍼런스, 무역 박람회 및 지역 사용자 그룹 이벤트는 통찰력을 얻고 급히 도움을 줄 수있는 동료와 관계를 구축 할 수있는 소중한 기회를 제공합니다.

이러한 팁을 보완 할 도구를 찾는 사람들은 MySQL 용 Percona 구성 마법사, MySQL 용 Percona Query Advisor 및 Percona 모니터링 플러그인을 확인할 수 있습니다. (참고 : 처음 두 링크에 액세스하려면 Percona 계정을 만들어야합니다. 무료입니다.) 구성 마법사를 사용하면 함께 제공되는 샘플 파일보다 우수한 새 서버에 대한 기준 my.cnf 파일을 생성 할 수 있습니다. 섬기는 사람. 쿼리 어드바이저는 페이지 매김 쿼리 (7 번)와 같이 잠재적으로 잘못된 패턴을 감지하는 데 도움이되도록 SQL을 분석합니다. Percona Monitoring Plugin은 통계를 열심히 저장하고 마지 못해 경고하는 데 도움이되는 모니터링 및 그래프 플러그인 세트입니다 (8 번). 이러한 도구는 모두 무료로 사용할 수 있습니다.