운영시 유용한 Performance, Sys 쿼리 모음

DB에서 뭔가 Performance나 sys테이블 봐야할거같은데

어떻게 쿼리를 쳐서 봐야할지 바로바로 생각나지 않는 경우가 많죠

그래서 유용할만한 쿼리들을 포스팅해두려 합니다.

(Real MySQL을 보고 공부한 내용을 다룹니다.)

 

호스트 접속 이력 확인

MySQL에 접속했던 host들의 전체 목록을 확인합니다.

CURRENT_CONNECTIONS는 현재 연결된 커넥션 수, TOTAL_CONNECTIONS는 연결되었던 총 커넥션 수 입니다.

SELECT * FROM performance_schema.hosts

 

MySQL 총 메모리 사용량 확인

SELECT * FROM sys.memory_global_total

 

스레드별 메모리 사용량 확인

SELECT thread_id, user, current_allocated FROM sys.memory_by_thread_by_current_bytes

 

특정 프로세스 ID의 메모리 사용내역 확인

SELECT thread_id, event_name,
       sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED)
FROM
performance_schema.memory_summary_by_thread_by_event_name
WHERE thread_id = (SELECT sys.ps_thread_id(<프로세스ID>))

미사용 인덱스 확인

SELECT * FROM sys.schema_unused_indexes

중복된 인덱스 확인

SELECT * FROM sys.schema_redundant_indexes

변경이 없는 테이블 확인 (구동된 시점부터 현재까지 쓰기가 발생하지 않은 테이블)

SELECT * FROM information_schema.tables AS t
JOIN performance_schema.table_io_waits_summary_by_table AS tio
ON tio.OBJECT_SCHEMA = t.TABLE_SCHEMA AND tio.OBJECT_NAME = t.TABLE_NAME
WHERE t.TABLE_SCHEMA NOT IN ('mysql', 'performance_schema','sys')
AND tio.COUNT_WRITE = 0

I/O 요청이 많은 테이블 목록 확인

SELECT * FROM sys.io_global_by_file_by_bytes WHERE file LIKE '%idb'

테이블별  작업량 통계 확인

SELECT table_schema,table_name, rows_updated, rows_inserted, rows_deleted, rows_inserted
        ,io_read, io_write
FROM
sys.schema_table_statistics
WHERE TABLE_SCHEMA NOT IN ('mysql', 'performance_schema','sys')

풀 테이블 스캔 쿼리 확인

SELECT db, query, exec_count, sys.format_time(total_latency),
       rows_examined_avg, rows_sent_avg, last_seen
FROM sys.x$statements_with_full_table_scans

자주 실행되는 쿼리 목록 확인

SELECT db, exec_count, query
FROM sys.statement_analysis
ORDER BY exec_count DESC

실행시간이 긴 쿼리 목록

SELECT query, exec_count, sys.format_time(avg_latency)
     ,rows_sent_avg, rows_examined_avg, last_seen
FROM sys.x$statement_analysis
ORDER BY avg_latency DESC

 

최근에 정렬 작업을 수행한 쿼리 확인

SELECT * FROM
sys.statements_with_sorting
ORDER BY last_seen DESC LIMIT 1

최근에 임시테이블을 생성한 쿼리 확인

SELECT * FROM
    sys.statements_with_temp_tables
LIMIT 10

트랜잭션이 활성 상태인 커넥션에서 실행한 쿼리 내역 확인

SELECT ps_t.PROCESSLIST_ID,
       ps_esh.THREAD_ID,
       CONCAT(ps_t.PROCESSLIST_USER,'@',ps_t.PROCESSLIST_HOST) as 'account',
       ps_esh.EVENT_NAME,
       ps_esh.SQL_TEXT,
       sys.format_time(ps_esh.TIMER_WAIT) AS 'duration',
       DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status
                                                       WHERE VARIABLE_NAME='UPTIME') - ps_esh.TIMER_START*10e-13 second) AS 'start_time',
       DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status
                                                       WHERE VARIABLE_NAME='UPTIME') - ps_esh.TIMER_END*10e-13 second) AS 'end_time'
FROM performance_schema.threads ps_t
INNER JOIN performance_schema.events_transactions_current ps_etc
ON ps_etc.THREAD_ID = ps_t.THREAD_ID
INNER JOIN performance_schema.events_statements_history ps_esh
ON ps_esh.NESTING_EVENT_ID = ps_etc.NESTING_EVENT_ID
WHERE ps_etc.STATE = 'ACTIVE'
AND ps_esh.MYSQL_ERRNO = 0
ORDER BY ps_t.PROCESSLIST_ID, ps_esh.TIMER_START

 

특정 세션에서 실행된 전체 쿼리 확인

SELECT ps_t.PROCESSLIST_ID,
       ps_esh.THREAD_ID,
       CONCAT(ps_t.PROCESSLIST_USER,'@',ps_t.PROCESSLIST_HOST) as 'account',
       ps_esh.EVENT_NAME,
       ps_esh.SQL_TEXT,
       DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status
                                                       WHERE VARIABLE_NAME='UPTIME') - ps_esh.TIMER_START*10e-13 second) AS 'start_time',
        DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status
                                                       WHERE VARIABLE_NAME='UPTIME') - ps_esh.TIMER_END*10e-13 second) AS 'end_time',
       sys.FORMAT_TIME(ps_esh.TIMER_WAIT) AS 'duration'
FROM
performance_schema.events_statements_history ps_esh
INNER JOIN performance_schema.threads ps_t
ON ps_t.THREAD_ID = ps_esh.THREAD_ID
WHERE ps_t.PROCESSLIST_ID = <PROCESS_ID>
AND ps_esh.SQL_TEXT IS NOT NULL
AND ps_esh.MYSQL_ERRNO = 0
ORDER BY ps_esh.TIMER_START DESC