Profiling Performance 쿼리 성능 분석

mocaccino·2024년 10월 24일

백엔드로드맵

목록 보기

3/19

SQL 쿼리 프로파일링이란 무엇인가?

데이터베이스 쿼리를 분석하고, 성능을 평가하고, 잠재적인 문제를 식별하는 방법.
문제가 있는 쿼리를 분석하고 식별함으로써 데이터베이스 성능을 측정하고 개선할 수 있다.
쿼리 프로파일링을 통해 반응성을 개선시키고, 고객 만족도 향상 뿐 아니라 향후 확장성까지 증가시킬 수 있다.

1. SQL 쿼리 프로파일링 방법

MySQL : MySQL에서 사용할 수 있는 쿼리 프로파일링을 위한 도구와 기술
MariaDB : MariaDB에서 사용할 수 있는 쿼리 프로파일링을 위한 도구와 기술
Postgres : Postgres에서 사용할 수 있는 쿼리 프로파일링을 위한 도구와 기술

1. EXPLAIN

사용가능 : MySQL MariaDB Postgres

SQL 쿼리를 분석하는데 사용할 수 있는 명령
쿼리에서 EXPLAN 명령을 실행하면 어떤 인덱스가 사용되었는지, 몇 개의 행이 검사되었는지 등 쿼리가 어떻게 실행되었는지 볼 수 있다.

사용 예시

EXPLAIN 
SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE customers.name = 'John Smith';

결과

O id : 실행계획에서 쿼리의 고유 식별자
O select_type : 쿼리 유형(SIMPLE, SUBQUERY)
O table : 쿼리되는 테이블
O type : 조인 유형(JOIN, INDEX)
O possible_keys : 쿼리를 처리하는데 사용할 수 있는 인덱스
O key : 쿼리를 처리하는데 실제로 사용한 인덱스
O key_len : 사용된 키의 길이
O line : 쿼리에 대해 검사할 것으로 추정하는 행 수
O Extra : 전체 테이블 스캔이 수행되었는지, 또는 임시테이블이 사용되었는지에 대한 정보

2. EXPLAIN EXTENDED

사용가능 : MariaDB

EXPLANE 에서 추가적으로 더 정보를 제공하기 위한 옵션
검사한 행 수, 반환된 행 수, 사용된 JOIN 유형에 대한 정보, 스캔한 테이블의 순서, 사용된 인덱스, 쿼리를 실행하는데 걸린 시간 등의 정보를 확인할 수 있다.

쿼리의 병목현상을 더 쉽게 파악할 수 있으며 병목 현상을 완화하고 쿼리 성능을 높이는데 필요한 변경을 수행할 수 있다.

정확한 결과를 얻으려면 명령 수행 전 이 테스트와 최적화를 수행할 때 쿼리 캐싱을 비활성화했는지도 확인해야한다. 아래 명령을 수행해 쿼리 캐싱을 비활성화 할 수 있다.

SET SESSION query_cache_type=0;

사용예시

EXPLAIN EXTENDED SELECT * FROM your_table WHERE column_name = 'value';

3. EXPLAIN ANALAYZE

사용가능 : MySQL MariaDB Postgres
쿼리의 실행 계획을 분석하고 쿼리가 실행되는데 걸린 실제 시간과 실제로 검사한 행 수와 같은 성능 지표를 반환하는데 사용됨, 인덱스 부족이나 검사해야할 행 수가 많은것과 같이 쿼리 실행에 잠재적인 병목현상을 식별할 수 있다.

4. 느린 쿼리 로그

사용가능 : MySQL MariaDB

실행하는데 특정 시간보다 오래걸리는 모든 쿼리를 기록한다.
아래 행을 설정파일(my.cnf 또는 my.ini파일)에 추가하여 느린 쿼리 로그를 활성화 할 수 있다.

log_slow_queries = /path/to/slow.log
long_query_time = 1

5. 모니터링 도구를 사용한 프로파일링

Prometheus, Grafana, Nagios와 같은 여러 모니터링 도구를 사용하여 쿼리를 프로파일링하고 데이터베이스 성능을 모니터링할 수 있다.

Prometheus : 메트릭 데이터를 수집, 저장 및 쿼리할 수 있는 효율적인 모니터링 시스템으로, 실시간으로 분석할 수 있다. 수집된 메트릭을 저장하기 위해 MariaDB(및 MySQL)와 통합되며 효과적인 시각화를 위해 Grafana와 함께 제공된다.

Grafana : Prometheus에서 수집된 데이터를 모니터링하고 시각화하는 데 사용할 수 있는 강력한 오픈소스 분석 도구로 사용자 지정 대시보드와 알림을 설정하면 데이터베이스 성능을 실시간으로 확인할 수 있다.

Nagios : 데이터베이스의 상태를 모니터링 하는데 도움이 된다. CPU, RAM, 디스크 공간과 같은 주요 리소스를 모니터링하도록 설정할 수 있으며, 다른 서비스와 네트워크 장치를 추적할 수도 있다.

2. 일반적인 쿼리 최적화 기술

1. 인덱싱

쿼리속도를 높이는 방법, 특히 WHERE를 사용하는 쿼리에서 더욱 유용하다.
인덱스는 테이블 내의 1개의 컬럼, 혹은 여러개의 컬럼을 이용해 생성 가능하다. 고속의 동작과 빠른 레코드 접근을 위해서 효율적인 순서로 인덱스를 작성해야 한다.

생성 방법

ADD TABLE {테이블명} ADD INDEX {필드명(크기);

2. LIMIT 사용

쿼리를 실행하기 위해 액세스 해야하는 행과 열의 수를 최소화한다.
예를 들어 로그인을 하기 위해 사용자의 데이터를 조회하는 경우, 쿼리의 결과는 LIMIT 1이어야 한다. 2명 이상의 사용자 데이터는 필요하지 않기 때문이다.

3. Partition

테이블에 있는 특정 컬럼값을 기준으로 데이터를 분할해 저장해놓은 테이블로 대량의 데이터는 PK의 성격에 따라 부분적인 테이블인 파티셔닝 기법으로 성능을 향상 시킬 수 있다. 논리적인 테이블은 1개지만 물리적으로는 분할한 만큼 파티션이 만들어져 입력되는 컬럼 값에 따라 분할된 파티션별로 데이터가 저장된다.