mysql 매트릭 값 분석

Connection 부분 지표

• Connected Threads

• 현재 MySQL 서버에 연결된 총 클라이언트 스레드 수
• 쿼리: mysql_global_status_threads_connected
• 모니터링 포인트: 과도한 연결은 서버 부하를 증가시킬 수 있음

• Running Threads

• 현재 실행 중인 활성 스레드 수
• 쿼리: mysql_global_status_threads_running
• 의미: 실제로 작업을 수행 중인 스레드 수

• Max Used Connections

• 최대 동시 연결 수
• 쿼리: mysql_global_status_max_used_connections
• 용도: 피크 로드 시 연결 상태 확인

• Threads Created

• 서버 시작 후 생성된 총 스레드 수
• 쿼리: mysql_global_status_threads_created
• 모니터링: 지속적으로 높은 값은 연결 관리 최적화 필요성 시사

• Thread Cache
• Threads_cached
  • 의미: 스레드 캐시에 현재 대기 중인 스레드 수
  • 모니터링 포인트:
    • 캐시 효율성 판단
    • 스레드 재사용 상태 확인

• Thread Cache Hit Rate

• 계산식: (Threads_created / Connections) * 100
• 의미: 스레드 캐시의 효율성 측정
• 높은 hit rate는 스레드 재사용이 잘 되고 있음을 의미
• 과도한 스레드 생성은 서버 성능 저하

Temporary Objects 부분 지표

• Temporary Objects : MySQL 서버에서 생성된 임시 테이블의 총 수
• 임시 테이블 유형:
  • 메모리 내 임시 테이블
  • 디스크 기반 임시 테이블
• 임시 테이블 생성 원인
  • SELECT 쿼리 실행 중 중간 결과 저장
  • GROUP BY, ORDER BY 연산
  • 서브쿼리 처리
  • 복잡한 조인 연산
    
    임시 테이블 생성 비율 = (created_tmp_tables / total_queries) * 100
    디스크 임시 테이블 비율 = (created_tmp_disk_tables / created_tmp_tables) * 100
  • 메모리내 임시테이블 확인 방법
    SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';
    SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
    Created_tmp_tables - Created_tmp_disk_tables
    
    

관련 변수
SHOW VARIABLES LIKE 'tmp_table_size';               ← 사용자가 생성할 수 있는 임시 테이블의 최대 크기 결정
SHOW VARIABLES LIKE 'max_heap_table_size';    ← 메모리 내 임시 테이블의 최대 크기 결정 

Select Type 부분 지표

Select_full_join (인덱스 없는 조인)

• 의미: 인덱스 없이 수행되는 조인 연산 횟수
• 성능 영향: 가장 심각한 성능 저하
• 특징:
  • 테이블 간 조인에 인덱스 미사용
  • 대량 데이터 처리 시 치명적
• 개선 방안: 인덱스 생성, 조인 조건 최적화

Select_full_range_join (인덱스 있는 조인)

• 의미: 범위 기반 조인에서 전체 테이블 스캔 횟수
• 특징:
  • 부분적으로 인덱스 활용
  • 완전한 인덱스 활용보다는 비효율적
• 성능 개선:
  • 적절한 인덱스 설계
  • 조인 조건 최적화

Select_range (범위 스캔)

• 의미: 인덱스를 사용한 범위 검색 횟수
• 장점:
  • 전체 테이블 스캔보다 효율적
  • 인덱스 활용으로 빠른 데이터 접근
• 예시:
  • WHERE 절 범위 조건
  • BETWEEN 연산
  • 부분 인덱스 활용

•  

Select_scan (전체 테이블 스캔)

• 의미: 전체 테이블 스캔 횟수
• 성능 특성:
  • 가장 비효율적인 데이터 접근 방식
  • 대규모 테이블에서 심각한 성능 저하
• 주요 원인:
  • 부적절한 인덱스
  • 비효율적인 쿼리 설계
  • WHERE 절 미최적화
    
    

Sort 부분 지표

Sort_merge_passes

• 정의: 정렬 작업을 수행할 때, MySQL이 임시 파일을 생성해 정렬 데이터를 병합한 횟수를 나타냅니다.
• 동작:
  • 정렬 작업의 데이터 크기가 sort_buffer_size를 초과하면, MySQL은 데이터를 디스크에 기록한 후 병합 작업을 수행합니다.
  • 병합 횟수가 많을수록 디스크 I/O가 증가하며, 쿼리 성능 저하로 이어질 수 있습니다.
• 최적화 방법:
  • sort_buffer_size 값을 적절히 조정하여 메모리 내에서 더 많은 데이터를 처리하도록 설정합니다.
  • 필요 이상으로 크게 설정하면 메모리 낭비가 발생할 수 있으니 신중히 조정해야 합니다.

---

Sort_range

• 정의: MySQL이 정렬 작업에서 **범위 스캔(Range Scan)**을 사용한 횟수를 나타냅니다.
• 동작:
  • ORDER BY 절에서 인덱스를 사용할 수 있는 경우, MySQL은 특정 범위의 데이터만 스캔하고 정렬합니다.
  • 이는 전체 테이블을 스캔하여 정렬하는 것보다 성능이 뛰어납니다.
• 주요 특징:
  • Sort_range 값이 높다면, MySQL이 효율적으로 인덱스를 활용하고 있음을 나타냅니다.
• 최적화 방법:
  • ORDER BY에서 자주 사용하는 컬럼에 적절한 인덱스를 생성합니다.

---

Sort_rows

• 정의: MySQL이 정렬 작업에서 처리한 **총 행(Row)**의 수를 나타냅니다.
• 동작:
  • ORDER BY 또는 GROUP BY를 포함한 쿼리 실행 중 정렬에 포함된 데이터 행의 총 개수를 집계합니다.
• 의미:
  • 이 값이 클수록 정렬 대상 데이터가 많음을 나타냅니다.
  • 정렬 행 수가 많은 경우, 쿼리 성능에 영향을 미칠 수 있습니다.
• 최적화 방법:
  • 불필요한 정렬을 피하고, 필요한 데이터만 정렬되도록 쿼리를 작성합니다.
  • LIMIT을 사용하여 정렬 대상 행의 수를 제한합니다.

---

Sort_scan

• 정의: MySQL이 정렬 작업 중 **전체 테이블 스캔(Full Table Scan)**을 수행한 횟수를 나타냅니다.
• 동작:
  • ORDER BY 절에서 인덱스를 활용할 수 없는 경우, MySQL은 전체 테이블을 읽어 정렬 작업을 수행합니다.
• 문제점:
  • Sort_scan 값이 높다면, 인덱스를 제대로 활용하지 못하고 정렬 작업을 위해 불필요한 테이블 스캔이 발생하고 있음을 의미합니다.
• 최적화 방법:
  • 정렬 작업에 필요한 컬럼에 적절한 인덱스를 생성하여 전체 테이블 스캔을 방지합니다.
  • 쿼리를 리팩터링하여 테이블 스캔을 최소화합니다.

Open Table 부분 지표

 

• Open_tables: 현재 열려 있는 테이블 핸들의 수.
• Opened_tables: 서버가 시작된 이후 새로 열린 테이블의 총 수.
• Open_table_definitions: 현재 캐시에 저장된 테이블 정의의 수.
• Opened_table_definitions: 서버가 시작된 이후 캐시에서 누락되어 새로 로드된 테이블 정의의 총 수.

 

• Opened_tables 값 증가:
  • 캐시 크기가 부족하여 **캐시 미스(Cache Miss)**가 발생.
  • table_open_cache 값을 늘리거나 쿼리 최적화 필요.
    - MySQL이 열 수 있는 테이블 핸들의 최대 수.
    - 캐시가 가득 차면 사용하지 않는 핸들이 제거되고, 새로운 테이블이 열리며 디스크에서 데이터를 다시 읽어야 합니다.
• Opened_table_definitions 값 증가:
  • 테이블 정의 캐시(table_definition_cache)가 부족함.
  • MySQL이 테이블 정의 데이터를 반복적으로 디스크에서 읽어야 하므로, 이 값을 늘려야 함.
    - MySQL이 **테이블 정의(메타데이터)**를 캐싱하는 데 사용하는 설정.
    - 테이블의 .frm 파일과 같은 정의 정보를 캐싱하여, 자주 참조되는 테이블의 정의 데이터를 빠르게 로드할 수 있습니다.
    
    

Query Cache 부분 지표

- 쿼리 캐시는 MySQL 5.7.20부터 더 이상 사용되지 않으며, MySQL 8.0에서 제거되었습니다.

have_query_cache	YES: Query Cache 기능이 활성화 가능. NO: Query Cache 기능이 서버에 포함되지 않음 (MySQL 8.0에서는 항상 NO).
query_cache_limit	Query Cache에 저장될 수 있는 단일 쿼리 결과의 최대 크기
query_cache_min_res_unit	Query Cache에서 결과를 저장할 때 메모리 블록의 최소 크기를 지정
query_cache_size	Query Cache에 할당되는 전체 메모리 크기 MySQL 5.6 이상에서는 일반적으로 1MB로 설정되어 있음. MySQL 8.0에서는 이 값이 항상 0입니다(Query Cache 제거됨).
query_cache_type	0 (또는 OFF): Query Cache 비활성화. 1 (또는 ON): 모든 SELECT 쿼리에 대해 Query Cache를 활성화. 2 (또는 DEMAND): 명시적으로 캐시를 요청한 쿼리만 캐시. 예: SELECT SQL_CACHE * FROM table_name;
query_cache_wlock_invalidate	쓰기 잠금(WRITE LOCK)이 발생했을 때 Query Cache에 저장된 데이터를 사용할 수 있는지 여부를 제어합니다. 이 값이 1이면 쓰기 잠금이 발생한 테이블과 관련된 캐시는 무효화됩니다. 값이 0이면 쓰기 잠금이 있더라도 Query Cache 결과를 사용