[모니터링] 쿼리

# 부하쿼리

SELECT *

FROM performance_schema.events_statements_summary_by_digest

ORDER BY SUM_TIMER_WAIT DESC LIMIT 5\G;

#[출처] Detailed Profiling of SQL Activity in MySQL 5.6 (MySQL Power Group) |작성자 더데빌

# 사용하지 않는 인덱스

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME

FROM performance_schema.table_io_waits_summary_by_index_usage

WHERE index_name IS NOT NULL

AND OBJECT_SCHEMA NOT IN ('mysql', 'test')

AND COUNT_STAR = 0

ORDER BY OBJECT_SCHEMA, OBJECT_NAME;

# 스키마별 엔진별 테이블 수와 데이터 사이즈, 인덱스 사이즈를 구합니다.

[출처] 제법 유용한 쿼리 (MySQL Power Group) |작성자 더데빌

SELECT TABLE_SCHEMA AS 'SCHEMA', ENGINE, COUNT(*) AS 'TABLES'

, ROUND(SUM(DATA_LENGTH)/1024/1024, 0) AS DATA_MB

, ROUND(SUM(INDEX_LENGTH)/1024/1024, 0) AS INDEX_MB

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')

AND ENGINE IS NOT NULL

GROUP BY TABLE_SCHEMA, ENGINE;

# 서버가 시작한 이래 한번도 로그인하지 않은 계정

SELECT DISTINCT m_u.user, m_u.host

FROM mysql.user m_u

LEFT JOIN performance_schema.accounts ps_a

ON m_u.user = ps_a.user AND m_u.host = ps_a.host

WHERE ps_a.user IS NULL

ORDER BY m_u.user;

# 서버가 가장 최근 시작된 이래로 한 번도 연결되지 않은 유저

SELECT DISTINCT m_u.user

FROM mysql.user m_u

LEFT JOIN performance_schema.users ps_u

ON m_u.user = ps_u.user

WHERE ps_u.user IS NULL

ORDER BY m_u.user;

# 서버가 가장 최근 시작된 이래로 한 번도 사용되지 않은 계정( 프로시저, 뷰, 이벤트, 트리거 조차도 생성되어 있지 않은 경우 )

SELECT DISTINCT m_u.User, m_u.Host

FROM mysql.user m_u

LEFT JOIN performance_schema.accounts ps_a 

ON m_u.User = ps_a.User AND ps_a.Host = m_u.Host

LEFT JOIN information_schema.views is_v 

ON is_v.DEFINER = CONCAT(m_u.User, '@', m_u.Host) AND is_v.security_type = 'DEFINER'

LEFT JOIN information_schema.routines is_r 

ON is_r.DEFINER = CONCAT(m_u.User, '@', m_u.Host) AND is_r.security_type = 'DEFINER'

LEFT JOIN information_schema.events is_e 

ON is_e.DEFINER = CONCAT(m_u.User, '@', m_u.Host)

LEFT JOIN information_schema.triggers is_t 

ON is_t.DEFINER = CONCAT(m_u.User, '@', m_u.Host)

WHERE ps_a.USER IS NULL

AND is_v.DEFINER IS NULL

AND is_r.DEFINER IS NULL

AND is_e.DEFINER IS NULL

AND is_t.DEFINER IS NULL

ORDER BY m_u.User, m_u.Host;

[출처] 사용하지 않고 있는 인덱스 찾기 (MySQL Power Group) |작성자 더

데빌