MySQL/MariaDB 모니터링 관련

Mysql / maria DB 관련하여 모니터링 시 주요 확인해야 할 지표 <빨간색으로 표기한 지표 수치 확인>

• Select
• Table Lock
• Sort
• Temp Table & Temp file
• Threds
• Replication
• Handler
• Connections
• Qps

* Select 쿼리 관련 모니터링

show status where variable_name like ‘select%

 

Variable Name	Comment
Select_full_join	다중 테이블 조인 시 후행 테이블에서 전체 테이블 스캔이 발생한 횟수
Select_full_range_join	다중 테이블 조인시 제한된 범위내에서 JOIN키 값을 비교한 횟수
Select_range	SELECT쿼리의 범위 조건에 만족하는 레코드를 찾기 위해 제한된 범위를 디스크에서 읽은 횟수
Select_range_check	Select range와 동일한 범위에서 선행 테이블의 JOIN 칼럼 데이터가 후행 테이블의 컬럼 데이터와 같은 값인지 비교하는 횟수
Select_scan	전체 테이블 스캔이 발생한 횟수

 

일반적으로 Select_full_join 이 발생하면 select_scan이 발생하기 때문에 두 지표 모두 값이 증가 한다. 

또한 실행계획 타입에서 ALL 이 표시되면 카테시안곱이 발생한것을 의미한다.

OLTP서비스에서는 Full scan은 피해야하는 항목으로 튜닝의 최우선 포인트가 된다.

 

---

* Table lock 관련 모니터링

show status where variable_name like ‘table_locks%’

Variable Name	Commets
Table_locks_immediate	즉시 획득한 테이블 락 횟수
Table_locks_waited	테이블 락을 즉시 획득하지 못하고 대기한 횟수

* InnoDB스토리지 엔진을 사용하는 경우 Table_locks_immedate 수치가 테이블락을 획득한 절대적인 수치가 아니라는 점을 명시해야한다.

만약 Table_locks_waited 수치가 높다면 락을 획득하는데 지연이 있다는 뜻이므로 이 수치가 증가한다면 이와 관련된 쿼리가 실행되는데 걸리는 시간이 증가하는 것으로 쿼리 튜닝이 필요하다.   Lock waited 대기 비율은 아래 공식으로 구할 수 있다.

(%) = Table_locks_immediate / (Table_locks_waited + Table_locks_immediate) * 100

---

* Sort 관련 모니터링

show status where variable_name like ‘sort%’

각 지표가 의미하는 내용은 아래 표를 참고 한다.

Variable Name	Comment
Sort_merge_passes	멀티 머지 처리 횟수
Sort_range	Index Range Scan결과에 대한 정렬 작업 횟수
Sort_rows	정렬한 전체 레코드 수
Sort_scan	Full table scan 결과에 대한 정렬 작업 횟수

show variables where variable_name like ‘sort%’

sort_buffer_size는 정렬을 하기 위한 메모리 공간이며 sort_merge_passes 지표가 높게 나타난다면 sort_buffer_size를 조절하여 디스크의 사용을 줄일 수 있는 방안을 검토해야 한다.

---

* Temp Table & Temp File 관련 모니터링

Show status where variable_name like ‘%tmp %’

 

Variable Name	Commets
Created_tmp_disk_tables	디스크에 생성된 temp table 횟수
Created_tmp_files	생성된 temp file 횟수
Created_tmp_tables	메모리와 디스크에 생성된 temp table 횟수

일반적으로 아래와 같은 쿼리는 임시 테이블을 생성한다.

·       OEDER BY 및 GROUP BY에 명시된 컬럼이 다른 쿼리

·       한 개의 쿼리에 DISTINCT와 ORDER BY가 동시에 존재하는 경우

·       DISTINCT가 인데스로 처리되지 못하는 쿼리

·       UNION ALL이 사용된 쿼리

·       실행계획에 select_type이 UNION RESULT가 나타나는 경우

·       실행계획에 select_type이 DERIVDE 인 쿼리

 

레코드의 크기 >  tmp_table_size OR max_heap_table_size 에도 임시테이블을 생성한다. 하지만 디스크 사용을 줄이기 위해 tmp_table_size 및 max_heap_table_size의 시스템 변수 값을 증가시키면 MySQL 서버가 사용할 수 있는 메모리 공간이 줄어들기 때문에 주의해야 한다. 

 

show variables where variable_name in (‘tmp_table_size’, ‘max_heap_table_size’)

SELECT 절에서 사용되는 컬럼에 따라서도 디스크에 임시 테이블이 생성되는데, TEXT, BLOB 형식의 컬럼이 포함되는 경우 MEMORY 스토리지 엔진이 이 데이터 타입을 지원하지 않아 디스크에 임시 테이블을 생성한다.

---

* Threads 관련 모니터링

Show status where variable_name like ‘threads%’;

Variable Name	Comments
Threads_cached	스레드 캐시에 있는 스레드 수
Threads_connected	현재 오픈된 스레드 수
Threads_created	연결을 다루기 위해 생성된 스레드 수
Threads_running	Sleeping 하지 않는 스레드 수

 

show variables where variable_name like ‘thread%’;

Variable Name	Comments
Thread_cache_size	스레드 풀에서 캐싱한 최대 스레드 개수
Thread_handling	서버에서 사용하는 스레드 처리 모델 ·       One-thread-per-connection : 커넥션마다 전용 스레드 생성 ·       Pool-of-threads : 스레드 풀을 사용
Thread_stack	스레드 스택의 크기. 기본값은 192K 이며 64bit경우 256K.

스레드 캐시를 활용하면 클라이언트가 MySQL서버에 접속하여 스레드 할당이 필요할 때 스레드 캐시에 대기중인 스레드를 할당한다. (대기중인 스레드가 없다면 새로운 스레드를 생성하여 할당한다.) 클라이언트 작업이 종료되면 스레드는 캐시로 반환되거나 삭제 된다.

 

스레드 캐시를 활용하기 위해 캐시 사이즈르 크게 할당한다면 메모리 낭비 및 예기치 못한 leak이 발생할 수도 있다. 

반면 너무 적게 설정한다면 MySQL 서버가 스레드 캐시를 잘 활용하지 못하므로 신규 스레드를 생성하는데 리소스 낭비가 발생한다. 

스레드 캐시 사용하지 못한 비율을 보고 적절한 임계치를 설정한다.

Cache Miss Rate(%) = Threads_created / connections * 100

---

* Replication 관련 모니터링

show slave status;

Variable Name	Comments
Slave_IO_State	IO 스레드의 동작 상태
Master_Host	슬레이브 DB가 접속한 마스터 DB의 호스트 주소
Master_User	슬레이브 DB가 마스터 DB로 접속한 계정명
Master_Port	슬레이브DB가 마스터 DB로 접속한 포트번호
Connect_Retry	접속이 끊어졌을때 재시도 하는 시간(초), 기본값 60초. CHANGE MASTER TO 구문으로 설정 가능.
Master_Log_File	IO 스레드가 현재 읽고 있는 마스터DB의 바이너리 로그 파일명
Read_Master_Log_Pos	IO 스레드가 현재 읽고 있는 마스터DB바이너리 로그 파일의 로그 포지션
Relay_Log_File	SQL 스레드가 최종적으로 읽고 실행한 슬레이브 DB의 릴레이 로그 파일명
Relay_Log_Pos	SQL 스레드가 최종적으로 읽고 실행한 슬레이브 DB의 릴레이 로그 파일의 포지션
Relay_Master_Log_File	Relay_Log_File 값으로 나타나는 릴레이 로그 파일과 연관된 마스터DB의 바이너리 로그 파일명
Salve_IO_Running	IO 스레드가 실행되고 마스터 DB로 정상적으로 접속됬는지 나타냄.
Slave_SQL_Running	SQL 스레드가 작동하고 있는지 여부. Yes 또는 No 로 표시됨
Replicate_Do_DB	옵션(replicate_do_db)로 명시된 MySQL의 데이터베이스 리스트
Replicate_Ignore_DB	옵션(replicate_Ignore_db)로 명시된 MySQL의 데이터베이스 리스트
Replicate_Do_Table	옵션(replicate_do_Table)로 명시된 MySQL의 테이블 리스트
Replicate_Ignore_Table	옵션(replicate_Ignore_Table)로 명시된 MySQL의 테이블 리스트
Replicate_Wild_To_Table	옵션(replicate_Wild_To_Table)로 명시된 MySQL의 테이블 리스트
Replicate_Wild_Ignore_Table	옵션(replicate_Wild_Ignore_Table)로 명시된 MySQL의 테이블 리스트
Last_Errno	Last_SQL_Errno 와 같은 값
Last_Error	Last_SQL_Error와 같은 값
Skip_Counter	시스템 변수인 sql_slave_skip_counter의 현재 값
Exec_Master_Log_Pos	SQL 스레드가 읽고 처리한 현재 마스터DB의 바이너리 로그 파일의 포지션으로 다음 번에 수행될 트랜잭션이나 이벤트의 시작을 표시
Relay_Log_Space	존재하는 모든 릴레이 로그 파일의 크기를 합한 값
Until_Condition	마스터DB의 바이너리 로그 파일 또는 슬레이드 DB의 릴레이 로그 파일의 특정 부분까지만 실행하고자 할 때 사용하는 옵션
Until_Log_File	SQL 스레드가 실행하다가 중단시킬 로그파일의 이름
Until_Log_Pos	SQL 스레드가 실행하다가 중단시킬 로그파일의 포지션
Master_SSL *	슬레이브DB가 마스터DB로 연결할 때 사용되는 SSL과 관련된 파라메터
Second_Behind_Master	슬레이브DB의 Replicaton  속도,  0이여야 동기화 지연이 없는 것임

---

* Handler 관련 모니터링

Handler(핸들러)의 역할은 각 스토리지 엔진에 대해 읽기 또는 쓰기를 요청 한다.

show status where variable_name like ‘handler%’

 

Variable Name	Comment
Handler_commit	커밋 수
Handler_delete	행을 삭제 하기 위한 요청 횟수
Handler_discover	NDBCLUSTER 스토리지 엔진에 요청하여 테이블 이름을 요청한 횟수
Handler_external_lock	external_lock() 호출한 횟수, 일반적으로 테이블 액세스 시작과 끝에 발생
Handler_mrr_init	테이블 액세스에 다중 범위 읽기 구현을 사용한 횟수
Handler_prepare	2단계 커밋 작업을 준비한 횟수
Handler_read_first	인덱스의 첫 번째 키 값을 패치(fetch)한 횟수
Handler_read_key	단일 행의 인덱스 키 값을 읽은 횟수.
Handler_read_last	인덱스의 마지막 키를 읽은 횟수
Handler_read_next	인덱스의 후속 행의 키를 읽은 횟수
Handler_read_prev	인덱스의 이전 행의 키를 읽은 횟수
Handler_read_rnd	고정된 위치의 특정 행을 읽은 횟수
Handler_read_rnd_next	고정된 위치의 특정 행에 대한 후속 행 읽기 횟수
Handler_rollback	스토리지 엔진이 롤백 요청을 받은 횟수
Handler_savepoint	Savepoint를 요청한 횟수
Handler_savepoint_rollback	Savepoit 지점으로 롤백을 요청한 횟수
Handler_update	행을 업데이트 하기 위한 요청 횟수
Handler_write	행을 삽입 하기 위한 요청 횟수

Handler_read_first의 값은 index full scan 발행한 횟수와 동일하다.

Handler_read_first 수치가 예상보다 높게 나타난다면 index full scan을 발생시키는 쿼리가 있는지 확인하여 적절하게 인덱스를 사용할 수 있도록 튜닝해야 한다.

Handler_read_next 는 인덱스 키 순서에 따라 다음 행을 읽은 횟수로 인덱스를 스캔하거나 제한적으로 범위 검색이 발생할 때 기록된다.

Handler_read_rnd는 테이블 스캔이나 인덱스 범위 스캔이 발생할 때 데이터 파일의 고정된 위치의 행을 읽은 횟수로  대상 테이블에 적절한 인덱스가 없는 경우 발생할 수 있다. 또한 인덱스를 사용하지 않는 조인이 발생하거나 정렬 연상이 발생한 경우도 증가하기 때문에 이 수치가 높다면 요청 쿼리에 대해 적절한 인덱스가 있는지 확인 해야 한다.

---

* Connection 관련 모니터링

show variables where variable_name in ('max_connections','max_user_connections')

Variable Name	Comment
Max_connections	현재 설정된 최대 동시 연결 수
Max_user_connections	계정당 생성할 수 있는 최대 동시 연결 수

show status where variable_name in ('max_used_connections','aborted_clients','aborted_connects','threads_connected',

'connections');

Variable Name	Comment
Aborted_clients	연결된 상태에서 강제로 연결 해제 된 연결 수
Aborted_connects	연결 과정 중 fail된 연결 수
connections	연결 시도된 총 수
Max_used_connections	동시 최대 접속자 수
Threads_connected	현재 오픈된 연결 수

현재 사용중인 connection의 사용 비율 Connection Usage(%) = (Threads_conneted / max_connections) * 100

강제로 연결이 취소된 비율 (이 비율이 높다면 wait_timeout 값의 조정이 필요 할 수 있다.) Aborted connection usage(%) = (aborted_clients  / connections) * 100

---

* QPS(Query Per Seconds) 관련 모니터링

show global status where variable_name in ('questions','com_select','com_insert','com_delete','com_update','com_replace','com_load','com_delete_multi','com_insertselect','com_update_multi','com_replace_select');

Variable Name	Comment
questions	-   전체 쿼리 요청 횟수 (누적값) -   Com_ping, com_statistics, com_stmt_prepare, com_stmt_close, com_stmt_reset횟수 제외 -   Stored programs 내부에서 실행하는 쿼리 실행 횟수 제외
com_select	SELECT 쿼리 실행 횟수
com_insert	INSERT 쿼리 실행 횟수
com_delete	DELETE 쿼리 실행 횟수
com_update	UPDATE 쿼리 실행 횟수
com_replace	REPLACE 쿼리 실행 횟수
com_load	LOAD 쿼리 실행 횟수
com_delete_multi	다중 테이블 DELETE 쿼리 실행 횟수
com_insert_select	INSERT SELECT 쿼리 실행 횟수
com_update_multi	다중 테이블 UPDATE 쿼리 실행 횟수
com_replace_select	REPLACE SELEC V쿼리 실행 횟수

select   SUBSTRING(VARIABLE_NAME, 5) as query_type,   VARIABLE_VALUE as total_count,   round(VARIABLE_VALUE / ( select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status'), 2) as per_second,   round(VARIABLE_VALUE / ((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status') / (60)))       as per_minute,   round(VARIABLE_VALUE / ((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status') / (60*60)))    as per_hour,   round(VARIABLE_VALUE / ((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status') / (60*60*24))) as per_day,   FROM_UNIXTIME(round(UNIX_TIMESTAMP(sysdate()) - (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status'))) report_period_start,   sysdate() as report_period_end,   TIME_FORMAT(SEC_TO_TIME((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status')),'%Hh %im') as report_period_duration from   information_schema.GLOBAL_STATUS where   VARIABLE_NAME in ('Com_select', 'Com_delete', 'Com_update', 'Com_insert');