MySQL 인사이트¶

작성자: 류루이 (Liu Rui)

데이터베이스는 비즈니스의 핵심으로, 애플리케이션 아키텍처와 성능을 좌우합니다. 애플리케이션 서비스 성능은 서비스 자체의 수평적 확장으로 해결할 수 있지만, 데이터베이스의 성능은 애플리케이션의 최종 성패를 결정합니다. MySQL은 데이터베이스의 왕으로서 거의 모든 산업 분야에서 사용되고 있습니다. 비즈니스가 증가함에 따라 부적절한 SQL 사용과 대량의 느린 쿼리가 애플리케이션을 느려지게 할 수 있으므로, 이를 관측하는 것이 매우 중요합니다.

MySQL 통합¶

MySQL 통합 문서

MySQL 모니터링¶

MySQL 관련 지표 정보를 전반적으로 확인하기 위해 주로 4가지 측면에서 살펴봅니다.

개요
활성 사용자 정보
InnoDB
잠금 정보

개요¶

개요 부분은 주로 연결 수, QPS, TPS, 비정상 연결 수, 초당 인덱스 없는 조인 쿼리 횟수, Schema 크기 분포, 느린 쿼리, 잠금 대기 시간 등의 차원에서 MySQL을 개괄적으로 분석합니다.

활성 사용자 정보¶

MySQL connection에 대해 주목해 본 적이 있나요? 먼저 에러를 살펴보겠습니다.

MySQL: ERROR 1040: Too many connections

MySQL 연결은 긴 연결과 짧은 연결을 허용하며, 연결을 설정하는 과정 자체에 큰 오버헤드가 발생하므로 일반적으로 긴 연결을 사용합니다. 하지만 긴 연결을 사용하면 메모리 사용량이 증가할 수 있습니다. MySQL은 쿼리 실행 중에 연결 객체를 관리하기 위해 임시로 메모리를 사용하며, 이러한 연결 객체 리소스는 연결이 끊길 때만 해제되기 때문입니다. 긴 연결이 많이 누적되면 메모리 점유가 증가하여 시스템에 의해 강제로 KILL되어 MySQL 서비스가 비정상적으로 재시작되는 현상이 발생할 수 있습니다.

이러한 긴 연결 상황에서는 주기적으로 연결을 끊어야 하며, 연결이 차지하는 메모리 크기를 통해 지속적인 긴 연결인지 추측할 수 있습니다. 또한 큰 작업을 실행할 때마다 mysql_reset_connection을 실행하여 연결 리소스를 다시 초기화할 수 있습니다.

MySQL 연결은 일반적으로 하나의 사용자 요청에 하나의 연결을 사용합니다. 요청 작업이 오랫동안 완료되지 않으면 연결이 쌓이고 데이터베이스의 연결 수가 빠르게 소모됩니다. 즉, 데이터베이스에 오랫동안 완료되지 않은 SQL이 있으면 해당 SQL이 계속해서 연결을 점유하고 해제하지 않습니다. 이때 애플리케이션의 요청은 계속해서 데이터베이스로 유입되어 데이터베이스 연결 수가 빠르게 소진됩니다.

클라우드 네이티브, 마이크로서비스 환경에서 데이터베이스 Connection에 대한 요구사항이 점점 높아지고 있으므로 MySQL Connection은 애플리케이션의 병목이 되기 쉽습니다. Too many connections는 MySQL이 실행 중인 머신의 CPU를 폭발시키고, 애플리케이션이 더 이상 Connection을 획득하지 못해 비즈니스가 중단될 수 있습니다. 이를 실시간으로 모니터링하면 데이터베이스 병목을 빠르게 찾을 수 있습니다. 또한 각 사용자 Connection의 세부 사항(예: 현재 사용자 Connection 수 및 누적 Connection 수)을 찾을 수 있습니다.

또한 현재 Connection을 기반으로 MySQL에 최적화 작업을 수행할 수 있습니다.

최대 연결 수 증가
마스터-슬레이브 백업 읽기/쓰기 분리
비즈니스 분할을 통한 여러 데이터베이스 인스턴스 도입
캐시 증감을 통한 쿼리 감소
기타 등등

InnoDB¶

mysql.conf에서 innodb=true 매개변수를 구성하여 InnoDB 지표 수집을 활성화합니다.

잠금 정보¶

MySQL 느린 쿼리¶

프로덕션 비즈니스 시스템에서 느린 쿼리는 장애이자 위험입니다. 일단 장애가 발생하면 시스템을 사용할 수 없게 되어 프로덕션 비즈니스에 영향을 미칩니다. 대량의 느린 쿼리가 있고 SQL 실행이 느릴수록 소비되는 CPU 리소스나 IO 리소스도 커집니다. 따라서 이러한 장애를 해결하고 방지하려면 느린 쿼리 자체에 주목하는 것이 핵심입니다.

현재 느린 쿼리를 최적화하는 두 가지 방법이 있습니다.

slow log 느린 쿼리 로그를 활성화하여 느린 쿼리 로그를 수집하고, 수동으로 느린 쿼리 SQL에 대해 explain을 실행합니다.

Guance을 통해 MySQL에서 dbm을 활성화하여 데이터베이스 성능 지표를 수집하고, 동시에 실행 시간이 긴 일부 SQL 문을 자동으로 선택하여 실행 계획을 획득하고 실제 실행 과정의 다양한 성능 지표를 수집합니다.

MySQL slow log¶

광의의 느린 쿼리¶

우리가 더 자주 보는 것은 협의의 느린 쿼리, 즉 쿼리 시간이 설정된 시간을 초과하는 경우입니다. 예를 들어 기본적으로 10초 이상 결과를 반환하지 않는 쿼리 문을 느린 쿼리로 표시합니다. 이 외에도 느린 쿼리가 발생할 수 있는 몇 가지 상황이 있으므로 느린 쿼리로 표시할 수 있습니다.

반환되는 레코드 집합이 비교적 큰 경우
인덱스를 사용하지 않는 쿼리를 빈번하게 사용하는 경우

느린 쿼리 로그 활성화¶

다음 구성은 MySQL 5.7에서 느린 쿼리를 활성화하는 방법입니다.

#### slow log 느린 쿼리 로그 ####
slow_query_log = 1 ## 느린 쿼리 로그 활성화
slow_query_log_file = /var/log/mysql/slow.log ## 느린 쿼리 로그 파일 이름
long_query_time = 2 ## SQL 문이 2초를 초과하면 기록
# min_examined_row_limit = 100 ## SQL 실행 중 examined_row에서 가져온 데이터가 100행을 초과해야 기록
#log-queries-not-using-indexes ## 인덱스를 사용하지 않은 SQL을 느린 쿼리에 기록
log_throttle_queries_not_using_indexes = 5 ## 분당 인덱스를 사용하지 않은 SQL 기록 횟수 제한, 즉 하나의 SQL 문이 계속 기록되면 기록이 너무 많아 저장 공간을 차지하므로 1분에 5번만 기록
log-slow-admin-statements = table ## 관리 작업 기록, 예: alter | analyze table 명령
log_output = file ## 느린 쿼리 로그 형식 FILE|TABLE|NONE, 기본값은 파일 형식, TABLE은 테이블 형식, TABLE 사용 권장하지 않음
log_timestamps = 'system' ## 느린 로그 기록 시간 형식, 시스템 시간 사용

여기에는 TOP 100 느린 쿼리 문이 기록되어 있습니다. 더 많은 느린 쿼리를 보려면 로그 탐색기에서 더 많은 로그 정보를 확인할 수 있습니다.

MySQL dbm¶

데이터베이스 성능 지표는 주로 MySQL의 내장 데이터베이스 performance_schema에서 비롯됩니다. 이 데이터베이스는 런타임에 서버 내부 실행 상황을 얻을 수 있는 방법을 제공합니다. 이 데이터베이스를 통해 DataKit은 과거 쿼리 문의 다양한 지표 통계와 쿼리 문의 실행 계획, 그리고 기타 관련 성능 지표를 수집할 수 있습니다. 수집된 성능 지표 데이터는 로그로 저장되며, source는 각각 mysql_dbm_metric, mysql_dbm_sample, mysql_dbm_activity입니다.

dbm을 활성화하면 데이터베이스 성능 지표 데이터를 직접 수집할 수 있습니다. 수집기 구성 참조: MySQL

[[inputs.mysql]]

# 데이터베이스 성능 지표 수집 활성화
dbm = true

...

# 모니터링 지표 구성
[inputs.mysql.dbm_metric]
  enabled = true

# 모니터링 샘플링 구성
[inputs.mysql.dbm_sample]
  enabled = true

# 대기 이벤트 수집
[inputs.mysql.dbm_activity]
  enabled = true   
...

`mysql_dbm_metric` 뷰¶

dbm을 활성화하여 수집된 데이터베이스 성능 지표는 뷰에서 현재 데이터의 성능을 직관적으로 분석할 수 있습니다: 느린 쿼리 최대 지연 시간, 느린 삽입 최대 지연 시간, 느린 쿼리 SQL 실행 횟수, 단일 SQL 최대 실행 횟수(실행 빈도), 최장 잠금 시간 등.

【SQL 지연 시간 TOP 20】 뷰에서는 쿼리 시간을 기준으로 내림차순 정렬하여 상위 20개의 느린 쿼리 SQL을 추출합니다. 내부 매개변수를 조정하여 원하는 TOP N을 표시할 수도 있습니다.

`mysql_dbm_activity` 뷰¶

mysql_dbm_activity 뷰를 구축하면 현재 실행 중인 SQL 수, 이벤트 유형 분포(현재 이벤트가 CPU 이벤트인지 User sleep 이벤트인지 등), 이벤트 상태 분포(예: Sending data, Creating sort index 등), 이벤트 Command Type 분포(현재 Query인지 sleep인지 등) 및 이벤트 목록을 관측할 수 있습니다.

이벤트 유형 분포¶

Processing SQL의 이벤트 유형을 의미합니다.

CPU
User sleep

이벤트 상태 분포¶

현재 Processing SQL의 상태 유형 분포 상황입니다. 상태 유형은 주로 다음과 같습니다.

init : 초기 실행
Sending data : 데이터 전송 중
Creating sort index : 정렬 인덱스 생성 중
freeing items : 현재 항목 해제 중
converting HEAP to MyISAM : HEAP을 MyISAM으로 변환 중
query end : 쿼리 완료
Opening tables : 테이블 열기 중
statistics : 통계

이벤트 Command Type 분포¶

현재 Processing SQL의 Command Type 분포 상황입니다. 주로 다음과 같은 유형이 있습니다.

query : 쿼리, query는 이벤트 상태와 함께 분석해야 함
sleep : 휴면, 아직 스케줄링되지 않음
daemon : daemon 방식으로 실행

이벤트 목록¶

이벤트 Top 100, 즉 현재 100개의 이벤트 레코드를 조회합니다. 여기에는 이벤트 ID(processlist_id), processlist_user(현재 이벤트 소속 사용자), DB Host(이벤트 호스트), SQL(실행 이벤트 문), process Host(이벤트 시작 호스트), 이벤트 유형, 이벤트 상태 및 이벤트 실행 시간 등이 포함됩니다.

Schema별 process 이벤트 추이 확인¶

해당 Schema의 이벤트 추이를 확인하여 현재 Schema의 부하 상황을 분석합니다.

뷰 템플릿¶

[MySQL 모니터링 뷰]

[MySQL Activity]

[MySQL dbm Metric]

[MySQL 느린 쿼리]