MySQL Tuning 방법 정리

성능을 평가하는 중요한 지표 중 하나는 사용자 경험입니다. 이는 일반적으로 '사용자가 원하는 결과를 얻기 위해 적절한 시간 이상 기다려야 하는가'로 결정됩니다. 그러나 여기서는 상황에 따라 '적절한 시간'이 다릅니다. 모바일 쇼핑 앱의 경우 적절한 시간은 2초를 초과해서는 안됩니다. 한편, 종업원의 인사 페이지에서는, 응답 시간이 몇 초 길어집니다. 성능이 사용자의 행동에 미치는 영향에 대한 데이터를 통해 다음과 같은 사실을 알 수 있습니다:

 

MySQL Tuning

    • 사용자의 79%는 저속 웹 사이트를 재방문할 가능성이 낮다고 응답했습니다.
    •  47%가 2초 이내에 Web 페이지가 로드될 것으로 예측하고 있습니다.
    • 사용자의 40%는 로딩 시간이 3초 이상인 웹사이트를 떠나고 있습니다.
    • 페이지 로드가 1초 지연되면 스위치 오버는 7% 감소하고 페이지 뷰는 11% 감소합니다.

따라서 어떤 표준에서도 애플리케이션의 성능을 유지하는 것이 필수적입니다. 성능 저하는 사용자들의 불만을 일으킬 뿐만 아니라 최악의 경우 사용자들이 다른 서비스나 애플리케이션으로 이동할 수 있기 때문에 비즈니스에도 영향을 미칩니다. 특히 데이터베이스의 성능은 전체 애플리케이션의 성능에 매우 중요한 영향을 미칩니다. 애플리케이션, 웹 사이트 및 데이터베이스 간의 상호 작용은 애플리케이션의 성능 수준을 결정하는 데 중요한 역할을 합니다.

이 교환의 중심에는 애플리케이션이 데이터베이스에 쿼리를 실행하는 방법과 데이터베이스가 이에 응답하는 방법은 전반적인 애플리케이션 성능에 매우 중요한 영향을 미칩니다. MySQL은 널리 사용되는 데이터베이스 관리 시스템 중 하나로, 쿼리에 빠르게 응답하고 애플리케이션의 성능 저하를 최소화하기 위해 설정하는 것이 매우 중요합니다. MySQL 데이터베이스의 성능을 최적화하기 위해 필요한 몇 가지 힌트를 소개합니다.

 

 

Step 1 ) EXPLAIN을 학습

 

데이터베이스에서 가장 중요한 결정 중 두 가지는 데이터베이스 스키마 설계와 쿼리 작성입니다. 데이터베이스 스키마는 엔티티 간의 관계를 테이블에 매핑하는 방법을 정의하며, 쿼리는 응용 프로그램이 필요로 하는 데이터를 필요한 형식으로 가져오는 역할을 합니다. 이 두 가지 요소는 애플리케이션의 성능과 확장성에 매우 중요한 영향을 미칩니다.

복잡한 애플리케이션에서는 데이터베이스 스키마와 쿼리가 복잡해질 수 있습니다. 이러한 복잡성을 다루기 위해 쿼리 실행 방법을 대략적으로 이해하는 것뿐만 아니라 EXPLAIN 명령어를 사용하는 방법을 배우는 것이 중요합니다. EXPLAIN 명령어는 쿼리의 실행 계획을 표시하고 예상되는 성능 및 데이터 크기 변화에 따른 쿼리의 스케일링 방법에 대한 통찰력을 제공합니다.

MySQL Workbench와 같은 툴을 사용하면 EXPLAIN의 결과를 시각화할 수 있으며, 이를 통해 쿼리 실행 계획을 더 쉽게 이해할 수 있습니다. 이러한 도구를 최대한 활용하기 위해서는 기본적인 개념을 이해하는 것이 중요합니다. 따라서 EXPLAIN 명령어를 사용하여 쿼리 실행 계획을 분석하고 데이터베이스의 성능을 향상시키는 방법에 대해 학습하는 것이 좋습니다.

 

쿼리 비용은 MySQL이 쿼리를 실행하는 데 필요한 자원과 시간을 기반으로 계산됩니다. 주목해야 할 컴포넌트 중 하나는 "query_cost"입니다. 이는 쿼리를 실행하는 전체 비용 측면에서 쿼리를 얼마나 높게 평가하는지를 나타냅니다.

쿼리 비용은 다양한 요인에 기반하여 계산됩니다. 일반적으로 단순한 쿼리는 1,000 미만의 비용을 갖습니다. 중간 비용의 쿼리는 1,000에서 100,000 사이이며, 고가의 쿼리는 10만 이상의 비용을 갖습니다. 이 수치는 쿼리가 시스템 내에서 유일한 사용자인 경우에는 빠를 수 있지만, 대화형 응용 프로그램에서는 성능 저하를 초래할 수 있습니다.

쿼리 비용을 결정하는 가장 중요한 요소 중 하나는 쿼리가 인덱스를 올바르게 사용하는지 여부입니다. EXPLAIN 명령을 사용하여 쿼리가 인덱스를 사용하지 않는지 여부를 확인할 수 있습니다. 이는 데이터베이스 내에서 인덱스가 생성된 방식이나 쿼리 자체의 설계에 의해 영향을 받을 수 있습니다. 따라서 EXPLAIN 명령을 사용하여 쿼리 실행 계획을 분석하는 것이 중요합니다. 이를 통해 쿼리의 성능을 최적화하고 데이터베이스의 성능을 향상시킬 수 있습니다.

 

 

Step 2 ) Index 생성을 적절히 하라

 

데이터베이스 색인은 서적의 색인과 비슷한 역할을 합니다. 색인은 고유한 위치에 저장되며 이미 주요 데이터베이스에 정보가 저장되어 있습니다. 이러한 인덱스는 쿼리가 데이터베이스 내에서 스캔해야 하는 데이터의 양을 줄임으로써 성능을 향상시킵니다. MySQL의 인덱스는 데이터베이스 접근 속도를 향상시키고 UNIQUE나 FOREIGN KEY와 같은 데이터베이스 조건의 적용에도 도움이 됩니다. 인덱스는 데이터베이스 내의 데이터를 변경하는 것이 아니라 단순히 데이터의 위치를 가리킵니다.

최적의 인덱스는 항상 모든 워크로드에 대해 존재하지 않습니다. 따라서 시스템에서 실행되고 있는 쿼리의 컨텍스트에서 인덱스를 항상 검토해야 합니다. 예를 들어, 적절한 인덱스가 있는 데이터베이스는 더 빠르게 실행될 수 있습니다. 반면에 하나의 인덱스가 누락되면 데이터베이스의 성능이 극단적으로 저하될 수 있습니다. 이런 경우 EXPLAIN을 사용하여 누락된 인덱스를 검색하고 추가할 수 있습니다. 다만, 불필요한 인덱스를 추가하지 않도록 주의해야 합니다. 불필요한 인덱스는 데이터베이스의 성능을 저하시킬 수 있습니다. MySQL 인덱스의 최적 사례를 고려하면 좋습니다.

 

 

 

Step 3 ) default 값은 사용하지 않는다

 

MySQL의 최적 성능을 위해 중요한 설정이 있다는 점은 맞습니다. 특히, 대규모 엔터프라이즈 환경에서는 기본 설정으로는 충분하지 않을 수 있습니다. 이러한 설정을 이해하고 조정함으로써 MySQL의 성능을 향상시킬 수 있습니다. 여기서 세 가지 중요한 MySQL 퍼포먼스 튜닝 설정을 소개합니다.

버퍼 풀 크기 설정: MySQL은 InnoDB와 같은 엔진을 사용하는 경우 버퍼 풀을 통해 데이터와 인덱스를 메모리에 보관합니다. 이를 통해 디스크에서의 I/O 작업을 줄여 성능을 향상시킬 수 있습니다. 따라서 인덱스와 데이터의 크기, 애플리케이션의 요구 사항 등을 고려하여 적절한 버퍼 풀 크기를 설정해야 합니다.

쿼리 캐시 설정: MySQL은 쿼리 캐시를 통해 쿼리의 결과를 캐시하여 빠른 접근을 가능하게 합니다. 하지만 쿼리 캐시의 크기가 작거나 쿼리 캐시가 자주 무효화되는 경우 이점을 얻을 수 없습니다. 따라서 쿼리 캐시의 크기와 관련된 설정을 조정하여 최적의 성능을 얻을 수 있습니다.

스레드 설정: MySQL은 동시에 처리할 수 있는 연결 수를 조절하는 스레드 설정을 가지고 있습니다. 너무 많은 연결을 허용하면 메모리 부족이 발생할 수 있고, 너무 적은 연결을 허용하면 동시에 많은 요청을 처리하지 못할 수 있습니다. 따라서 스레드 관련 설정을 조정하여 최적의 성능을 얻을 수 있습니다.

이러한 설정은 각 데이터베이스 환경에 따라 다를 수 있으므로 주의해서 조정해야 합니다. 또한 설정 변경 전에 백업을 만들고 테스트를 수행하여 예상치 못한 문제를 방지해야 합니다.

 

 

Step 4 ) Database를 Memory에 저장합니다

 

데이터베이스를 메모리에 저장해야 하는 정확한 양은 모든 상황에 따라 다르며 일반적으로 "매직" 번호로 정할 수 없습니다. 최적의 성능을 달성하기 위해서는 데이터베이스의 워크로드에 따라 메모리에 저장하는 데이터 양을 조정해야 합니다.

보통 데이터베이스의 기동 후 몇 시간 동안 발생하는 I/O 양을 확인하여 일반적인 상황을 파악하는 것이 중요합니다. 이는 데이터베이스가 특정 작업을 수행하는 데 필요한 메모리 양을 파악하는 데 도움이 됩니다.

데이터베이스를 메모리에 저장하면 읽기 작업의 지연이 완전히 제거될 수 있으므로 데이터를 읽는 작업은 메모리에서 빠르게 수행됩니다. 그러나 쓰기 작업은 메모리 사용량과 상관없이 항상 발생하므로 이를 고려해야 합니다.

따라서 데이터베이스를 메모리에 저장하는 양을 결정할 때는 데이터베이스의 특성과 워크로드를 고려하여 필요한 데이터를 메모리에 유지하도록 최적화해야 합니다. 데이터베이스의 메모리 사용량을 지속적으로 모니터링하고 조정하여 최상의 성능을 유지하는 것이 중요합니다.

 

 

Step 5 ) SSD 사용을 권장합니다

 

고속 스토리지 또는 SSD는 데이터베이스 성능 향상을 위해 매우 중요합니다. 메모리에 데이터베이스를 로드하든 디스크에 저장하든, 쓰기 작업은 항상 발생하고 데이터베이스가 웜업되는 동안 성능 문제를 방지하는 데 도움이 됩니다.

디스크를 회전시키는 것을 권장하는 경우가 있지만, 현재의 SSD 기술은 뛰어난 성능과 안정성을 상대적으로 저렴한 가격에 제공합니다. 그러므로 데이터베이스 서버에는 특히 안전성이 보장된 SSD를 사용하는 것이 중요합니다. 일반적인 데스크톱 또는 노트북용 SSD는 데이터베이스 서버에는 적합하지 않을 수 있습니다.

또한, NVMe 또는 인텔 Optane과 같은 기술을 통해 직접 연결된 SSD가 최고의 성능을 제공합니다. 이러한 SSD는 SAN, NAS 또는 클라우드 블록 디바이스에 원격 연결되어 있는 환경에서도 회전 디스크보다 우수한 성능을 제공할 수 있습니다.

따라서 데이터베이스 성능을 향상시키고 안정성을 보장하기 위해서는 고속 스토리지 또는 적절한 SSD를 선택하는 것이 매우 중요합니다.

 

 

Step 6 ) Scale Out 하라

 

수직 스케일업과 수평 스케일아웃은 서버의 성능을 향상시키는 두 가지 기본적인 전략입니다.

수직 스케일업은 단일 서버의 성능을 향상시키는 것으로, 기존 서버에 더 많은 리소스(예: CPU, 메모리, 디스크)를 추가하거나 더 강력한 하드웨어로 업그레이드하는 것을 의미합니다. 이 방법은 비용이 많이 들고, 서버가 노후화될 때까지 대부분의 경우 성능 향상이 제한됩니다.

수평 스케일아웃은 여러 서버를 추가하여 전체 시스템의 성능을 향상시키는 것입니다. 이 방법은 작은 규모의 서버를 사용하고, 필요에 따라 서버를 확장할 수 있기 때문에 보다 저렴하고 유연합니다. 또한, 하드웨어 장애에 대한 내구성을 향상시킬 수 있습니다.

수평 스케일아웃의 이점은 다음과 같습니다:

  1.작고 저렴한 시스템을 사용할 수 있습니다.
  2.직선적으로 확장할 수 있어 더 빠르고 간단합니다.
  3.단일 서버의 장애가 발생해도 데이터를 보호하기 위해 데이터베이스가 여러 시스템으로 분산됩니다.

그러나 수평 스케일아웃에는 몇 가지 단점이 있습니다. 예를 들어, 데이터 동기화를 위한 추가 구현이 필요할 수 있으며, 전체 시스템의 복잡성을 증가시킬 수 있습니다. 또한, 적절한 로드 밸런싱 및 데이터베이스 클러스터링이 필요할 수 있습니다.

 

 

Step 7 ) 관찰가능성을 고려하여야 한다

 

올바른 모니터링은 MySQL 데이터베이스 환경을 유지하고 문제를 신속하게 해결하는 데 매우 중요합니다. MySQL 데이터베이스가 실제 작업 환경에서 실행될 때 발생하는 다양한 이슈에 대비하기 위해 모니터링 솔루션을 사용하는 것이 좋습니다. 이를 통해 다음과 같은 기능을 수행할 수 있습니다:

1.시스템 상태 모니터링: CPU 사용량, 메모리 사용량, 디스크 사용량 등과 같은 시스템 리소스를 모니터링하여 시스템의 전반적인 상태를 파악할 수 있습니다.

2.성능 모니터링: 쿼리 실행 시간, 인덱스 사용률, 블록된 쿼리 등과 같은 성능 관련 지표를 모니터링하여 데이터베이스 성능을 추적하고 최적화할 수 있습니다.

3.이벤트 및 경고 관리: 잠긴 쿼리, 느린 쿼리, 장애 및 오류와 같은 이벤트와 경고를 모니터링하고 적시에 대응할 수 있습니다.

4.실시간 트래픽 모니터링: 현재 트래픽 패턴을 실시간으로 모니터링하여 데이터베이스에 대한 부하를 파악하고 필요한 경우 대응할 수 있습니다.

이력 추적 및 분석: 과거의 모니터링 데이터를 저장하고 분석하여 장기적인 추세를 파악하고 성능 문제를 예측할 수 있습니다.

모니터링 도구를 사용하면 문제가 발생하기 전에 경고를 받고 즉각적으로 대응할 수 있으므로 데이터베이스 환경의 안정성과 신뢰성을 유지할 수 있습니다. MySQL Enterprise Monitor와 Monyog와 같은 모니터링 도구는 MySQL 데이터베이스 환경을 모니터링하고 관리하는 데 매우 유용한 도구입니다.