Aurora MySQL 데이터베이스 쿼리 성능 문제 해결 - Amazon Aurora

Aurora MySQL 데이터베이스 쿼리 성능 문제 해결

MySQL은 쿼리 계획 평가 방법에 영향을 미치는 시스템 변수, 전환 가능한 최적화, 최적화 프로그램 및 인덱스 힌트, 최적화 프로그램 비용 모델을 통해 쿼리 최적화 프로그램 제어 기능을 제공합니다. 이러한 데이터 포인트는 다양한 MySQL 환경을 비교할 때뿐만 아니라 이전 쿼리 실행 계획을 현재 실행 계획과 비교하고 언제든지 MySQL 쿼리의 전체 실행을 이해하는 데 유용할 수 있습니다.

쿼리 성능은 실행 계획, 테이블 스키마 및 크기, 통계, 리소스, 인덱스, 파라미터 구성 등 여러 요인에 따라 달라집니다. 쿼리를 튜닝하려면 병목 현상을 식별하고 실행 경로를 최적화해야 합니다.

  • 쿼리의 실행 계획을 찾고 쿼리가 적절한 인덱스를 사용하고 있는지 확인하세요. EXPLAIN을 사용하고 각 계획의 세부 정보를 검토하여 쿼리를 최적화할 수 있습니다.

  • Aurora MySQL 버전 3(MySQL 8.0 Community Edition과 호환)은 EXPLAIN ANALYZE 문을 사용합니다. EXPLAIN ANALYZE 문은 MySQL이 쿼리에 시간을 소비하는 위치와 그 이유를 보여주는 프로파일링 도구입니다. Aurora MySQL은 EXPLAIN ANALYZE를 사용하여 쿼리를 계획, 준비 및 실행하는 동시에 행 수를 세고 실행 계획의 다양한 시점에서 소요되는 시간을 측정합니다. 쿼리가 완료되면 EXPLAIN ANALYZE는 쿼리 결과 대신 계획과 측정값을 인쇄합니다.

  • ANALYZE 문을 사용하여 스키마 통계를 최신 상태로 유지하세요. 오래된 통계 때문에 쿼리 최적화 프로그램이 잘못된 실행 계획을 선택하는 경우가 있습니다. 이 경우 테이블과 인덱스 모두의 카디널리티 추정치가 부정확하기 때문에 쿼리 성능이 저하될 수 있습니다. innodb_table_stats 테이블의 last_update 열에는 스키마 통계가 마지막으로 업데이트된 시간이 표시되며, 이는 '무효함'을 나타내는 좋은 지표입니다.

  • 데이터 분포 편차와 같이 테이블 카디널리티에 고려되지 않는 다른 문제가 발생할 수 있습니다. 자세한 내용은 MySQL 설명서에서 Estimating ANALYZE TABLE complexity for InnoDB tablesHistogram statistics in MySQL을 참조하세요.

쿼리에 소요되는 시간 이해

쿼리에 소요되는 시간을 확인하는 방법은 다음과 같습니다.

프로파일링

기본적으로 프로파일링은 비활성화되어 있습니다. 프로파일링을 활성화한 다음 느린 쿼리를 실행하고 해당 프로필을 검토하세요.

SET profiling = 1; Run your query. SHOW PROFILE;
  1. 시간이 가장 많이 소요되는 단계를 식별합니다. MySQL 설명서의 General thread states에 따르면 SELECT 문의 행 읽기 및 처리는 주어진 쿼리의 수명 동안 실행 상태가 가장 긴 경우가 많습니다. EXPLAIN 문을 사용하여 MySQL이 이 쿼리를 실행하는 방식을 이해할 수 있습니다.

  2. 느린 쿼리 로그를 검토하여 각 환경의 워크로드가 비슷한지 확인하기 위해 rows_examinedrows_sent를 평가합니다. 자세한 내용은 Aurora MySQL 데이터베이스에 대한 로깅 단원을 참조하십시오.

  3. 식별된 쿼리에 속하는 테이블에 대해 다음 명령을 실행합니다.

    SHOW TABLE STATUS\G;
  4. 각 환경에서 쿼리를 실행하기 전과 후에 다음 출력을 캡처합니다.

    SHOW GLOBAL STATUS;
  5. 각 환경에서 다음 명령을 실행하여 이 샘플 쿼리의 성능에 영향을 미치는 다른 쿼리/세션이 있는지 확인합니다.

    SHOW FULL PROCESSLIST; SHOW ENGINE INNODB STATUS\G;

    서버의 리소스가 사용량이 많으면 쿼리를 포함하여 서버의 다른 모든 작업에 영향을 미치는 경우가 있습니다. 또한 쿼리가 실행될 때 정기적으로 정보를 캡처하거나 적절한 간격으로 정보를 캡처하도록 cron 작업을 설정할 수 있습니다.

성능 스키마

성능 스키마는 성능에 미치는 영향을 최소화하면서 서버 런타임 성능에 대한 유용한 정보를 제공합니다. 이는 DB 인스턴스에 대한 스키마 정보를 제공하는 information_schema와 다릅니다. 자세한 내용은 Aurora MySQL에서 성능 개선 도우미에 대해 성능 스키마 활성화 단원을 참조하십시오.

쿼리 최적화 프로그램 트레이스

특정 쿼리 계획을 실행하도록 선택한 이유를 이해하려면 MySQL 쿼리 최적화 프로그램에 액세스하도록 optimizer_trace를 설정할 수 있습니다.

최적화 프로그램 트레이스를 실행하여 최적화 프로그램이 사용할 수 있는 모든 경로와 선택에 대한 광범위한 정보를 표시합니다.

SET SESSION OPTIMIZER_TRACE="enabled=on"; SET optimizer_trace_offset=-5, optimizer_trace_limit=5; -- Run your query. SELECT * FROM table WHERE x = 1 AND y = 'A'; -- After the query completes: SELECT * FROM information_schema.OPTIMIZER_TRACE; SET SESSION OPTIMIZER_TRACE="enabled=off";

쿼리 최적화 프로그램 설정 검토

Aurora MySQL 버전 3(MySQL 8.0 Community Edition과 호환)은 Aurora MySQL 버전 2(MySQL 5.7 Community Edition과 호환)에 비해 최적화 프로그램 관련 변경 사항이 많습니다. optimizer_switch에 대한 사용자 지정 값이 있는 경우 기본값의 차이를 검토하고 워크로드에 가장 적합한 optimizer_switch 값을 설정하는 것이 좋습니다. 또한 Aurora MySQL 버전 3에서 사용할 수 있는 옵션을 테스트하여 쿼리가 어떻게 수행되는지 검사하는 것이 좋습니다.

참고

Aurora MySQL 버전 3은 innodb_stats_persistent_sample_pages 파라미터에 커뮤니티 기본값인 20을 사용합니다.

이러한 명령을 사용하여 optimizer_switch 값을 표시할 수 있습니다.

SELECT @@optimizer_switch\G;

다음 테이블은 Aurora MySQL 버전 2와 3에 대한 optimizer_switch 기본값을 보여줍니다.

설정 Aurora MySQL 버전 2 Aurora MySQL 버전 3
batched_key_access
block_nested_loop 켜짐 켜짐
condition_fanout_filter 켜짐 켜짐
derived_condition_pushdown 켜짐
derived_merge 켜짐 켜짐
duplicateweedout 켜짐 켜짐
engine_condition_pushdown 켜짐 켜짐
firstmatch 켜짐 켜짐
hash_join 켜짐
hash_join_cost_based 켜짐
hypergraph_optimizer
index_condition_pushdown 켜짐 켜짐
index_merge 켜짐 켜짐
index_merge_intersection 켜짐 켜짐
index_merge_sort_union 켜짐 켜짐
index_merge_union 켜짐 켜짐
loosescan 켜짐 켜짐
materialization 켜짐 켜짐
mrr 켜짐 켜짐
mrr_cost_based 켜짐 켜짐
prefer_ordering_index 켜짐 켜짐
semijoin 켜짐 켜짐
skip_scan 켜짐
subquery_materialization_cost_based 켜짐 켜짐
subquery_to_derived
use_index_extensions 켜짐 켜짐
use_invisible_indexes

자세한 내용은 MySQL 설명서의 Switchable optimizations (MySQL 5.7)Switchable optimizations (MySQL 8.0)를 참조하세요.