Aurora 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 tables
및 Histogram statistics in MySQL 을 참조하세요.
쿼리에 소요되는 시간 이해
쿼리에 소요되는 시간을 확인하는 방법은 다음과 같습니다.
- 프로파일링
-
기본적으로 프로파일링은 비활성화되어 있습니다. 프로파일링을 활성화한 다음 느린 쿼리를 실행하고 해당 프로필을 검토하세요.
SET profiling = 1;Run your query.SHOW PROFILE;-
시간이 가장 많이 소요되는 단계를 식별합니다. MySQL 설명서의 General thread states
에 따르면 SELECT문의 행 읽기 및 처리는 주어진 쿼리의 수명 동안 실행 상태가 가장 긴 경우가 많습니다.EXPLAIN문을 사용하여 MySQL이 이 쿼리를 실행하는 방식을 이해할 수 있습니다. -
느린 쿼리 로그를 검토하여 각 환경의 워크로드가 비슷한지 확인하기 위해
rows_examined및rows_sent를 평가합니다. 자세한 내용은 Aurora MySQL 데이터베이스에 대한 로깅 단원을 참조하십시오. -
식별된 쿼리에 속하는 테이블에 대해 다음 명령을 실행합니다.
SHOW TABLE STATUS\G; -
각 환경에서 쿼리를 실행하기 전과 후에 다음 출력을 캡처합니다.
SHOW GLOBAL STATUS; -
각 환경에서 다음 명령을 실행하여 이 샘플 쿼리의 성능에 영향을 미치는 다른 쿼리/세션이 있는지 확인합니다.
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
이러한 명령을 사용하여 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)
