MySQL의 최적화 개요

Optimization Overview

Optimization은 2가지로 나눌 수 있다.

• DB Level
  • table,query,설정 값 최적화
• Hardware Level
  • Hardware Level의 CPU와 I/O 명령 최적화

DB Level에서의 최적화

빠른 DB 만들 때 가장 중요한 점은 기본 디자인이다.(기본을 잘하란 말씀..!)

• Table 구성이 적절하게 이뤄졌는가?
  특히, Data Type에 대한 정확한 고려가 이뤄졌는지, 각 테이블마다 적절한 Column들을 사용하는지?
  예를 들어, ****자주 업데이트를 수행하는 애플리케이션은 Column이 적고 Table이 많은 반면, 대량의 데이터를 분석하는 애플리케이션은 Column이 많고 Table이 적습니다.
• Index를 이용해 쿼리가 효과적으로 작동할 수 있는가?
• 각 테이블마다 적절한 Storage Engine을 사용하고 있는가?
  (테이블마다 Storage Engine을 설정할 수 있다는 것은 처음 알게 된 내용이다.)
  특히 Transactional Engine인 InnoDB나 NonTransactional Engine인 MyISAM을 적절히 사용하는 것이 좋다.
• 각 테이블에 있어 행 format이 맞는가? 이 선택은 테이블에 쓰이는 Storage Engine에 기반한다.
  특히 압축된 테이블에서는 적은 공간을 사용하고 I/O작업에 더 적은 공간을 사용한다.
  압축은 모든 종류의 InnoDB 작업에서는 필요하고 ReadOnly의 MyISAM 엔진에서도 필요하다.
• 적절한 Locking 전략을 사용하고 있나?
  예를 들어, 가능한 경우 공유 액세스를 허용하여 데이터베이스 작업이 동시에 실행될 수 있도록 하고, 필요한 경우 배타적 액세스를 요청하여 중요한 작업이 최우선 순위를 갖도록 할 수 있습니다.
• _스토리지 엔진 선택*_은 매우 중요합니다.
  InnoDB 스토리지 엔진은 사용자의 개입 없이 대부분의 잠금 문제를 처리하여 데이터베이스의 동시성을 향상시키고 코드 실험 및 튜닝의 양을 줄여줍니다.
• 모든 Memory 영역은 캐싱의 Size 최적화가 필요하다.
  즉, 자주 액세스되는 데이터를 저장할 만큼 충분히 캐싱 사이즈가 커야하지만, 이 크기로 인해 DB의 많은 크기를 차지하면 곤란하다.
  이때, 구성해야할 주 메모리 영역은 InnoDB 버퍼 풀과 MyISAM 키 캐시입니다.

필자 개인적인 판단

그 중 1,2 방안은 초보적인 수준에서 학생들도 알 것 같다 싶긴 하지만, Storage Engine에 관한 부분은 처음 본다.

💡

그러나 MyISAM에 대해 자세히 공부할 필요는 없을 듯하다.

MySQL 8.0을 기준으로 이전까지는 전문 검색 DB용으로 남겨져있던 MyISAM의 영역을 InnoDB에서 대체해 나가고 있다고 한다.

Locking 파트에서는 스토리지 엔진 선택의 중요성을 다시금 언급하고 있지만,
넘쳐나는 NoSQL의 시대에 굳이 MySQL의 MyISAM 스토리지 엔진을 선택할 이유는 없을 것 같다.

물론 나 역시 전문가는 아니기에, 이 글에서 틀린 점이 있다면 지적 부탁드립니다.

마지막 6번 같은 경우는 모순적인 말이지만, 타협점을 잘 찾으라는 의미같다..

DB 최적화 결론

현재 MyISAM 엔진이 사장 단계에 접어들었으므로
DB Level에서의 최적화는 Table의 구성과 설정, Index의 사용이 주요하다.

또한, row format에 대해서도 신경쓸 부분이 있지만, 그 외에 가장 맘에 걸리는 부분이 Locking이다.

실제 DB의 운영에서는 다양한 사용자와 공유 메모리가 있을 것인즉 Locking 전략을 제대로 수립하지 않는다면 분명 문제가 생길 것이라고 본다..!

하드웨어 단계의 최적화

DB란 결국 많이 사용될 수록 Hardware의 한계에 다다르게 된다.

그러기에 DBA는 결국 언제나 튜닝을 가할 수 있어야 하고 병목을 피하기 위한 서버의 재구성을 할 능력이 있어야 한다. (서버의 재구성이요…?아직 거기까진 못하는데요…ㅠㅠㅠ)

그러지 않으면 서버를 더 사든지 (역시 돈이 많아아… 편ㅡㅡㅡ안)

시스템 병목은 보통 아래와 같은 상황으로 나타난다.

• 디스크 탐색 : 최근 디스크들은 하나의 데이터를 찾을 떄 10ms 이하에서 찾고 이론적으론 100번 찾으면 1초 이내로 걸린다.
  이 경우, 새 디스크를 쓰면 조금 개선되지만, 단일 테이블에 대해 개선하기가 매우 어렵다.
  따라서, 데이터를 분산 저장할 수 있어야 한다.
• Disk의 읽기,쓰기. 현대의 Disk들은 1개의 DIsk들은 최소 10~20MB의 대역폭을 가지고 있다.
  이 방법은 여러 디스크에서 병렬로 읽을 수 있기에 검색보다 최적화하기가 더 쉽습니다.
• CPU Cycle. 메모리 용량에 비해 테이블 크기가 큰 것이 가장 일반적인 걸림돌.(하지만 돈이 해결할 수 있을 것이다.)
• Memory 대역폭. CPU가 CPU 캐시에 저장할 수 있는 것보다 더 많은 데이터를 필요로 할 때, 메인 메모리 대역폭에 병목 현상이 발생합니다.

Balancing Portability and Performance

MySQL의 방언 사용 시 주석 처리에도 신경을 쓸 필요가 있다.

출처

https://dev.mysql.com/doc/refman/8.4/en/select-optimization.html

MySQL :: MySQL 8.4 Reference Manual :: 10.2.1 Optimizing SELECT Statements