[MySQL] 모니터링 쿼리

// 스크립트 파일명에 '+' 는 필수, '-' 는 옵션 조건

 

 

# bfpLoadRate-db-tab.sql

// buffer pool에 몇 퍼센트의 테이블(인덱스 포함)이 캐시되어있는지 확인. 

// ※ MySQL에서 제공하는 n_cached_pages 자체가 정확하지 않음.

select
table_name,
n_cached_pages,
total_pages,
round((n_cached_pages/total_pages)*100,2) as "cached_pct(%)"
from (
select
i.name as table_name,
i.n_cached_pages,
#t.data_length,
#t.index_length,
#t.data_free,
((t.data_length + t.index_length - t.data_free) / @@innodb_page_size) as total_pages
from (select it.name, sum(ici.n_cached_pages) as n_cached_pages
        from information_schema.innodb_tables it
        inner join information_schema.innodb_indexes ii on ii.table_id = it.table_id
        inner join information_schema.innodb_cached_indexes ici on ici.index_id = ii.index_id
	where it.name like (case when @db is null and @tab is null then it.name
                        when @db is not null and @tab is not null then concat(@db,'/',@tab)
                        when @db is not null and @tab is null then concat(@db,'%')
                        when @db is null and @tab is not null then concat('%',@tab)
                end)
	group by it.name) i
inner join information_schema.tables t
on i.name = concat(t.table_schema,'/',t.table_name)
) a;

 

# pstVar-var.sql

// persist 모드로 등록한 variable 확인

select
a.variable_name,
b.variable_value,
a.set_time,
a.set_user,
a.set_host
from performance_schema.variables_info a
inner join performance_schema.persisted_variables b
on a.variable_name = b.variable_name
where b.variable_name like concat('%',ifnull(@var,''),'%');

 

# lock.sql

with recursive cte as (
select
1 as lv,
trx_id,
convert(null,unsigned) as blocking_trx_id,
convert(null,unsigned) as requesting_thread_id,
convert(null,unsigned) as blocking_thread_id,
convert(null,unsigned) as requesting_event_id,
convert(null,unsigned) as blocking_event_id,
convert(trx_id,char(100)) as sort
from information_schema.innodb_trx
where trx_requested_lock_id is null
union all
select
a.lv + 1 as lv,
b.requesting_trx_id,
b.blocking_trx_id,
b.requesting_thread_id,
b.blocking_thread_id,
b.requesting_event_id,
b.blocking_event_id,
convert(concat(a.sort,',',b.requesting_trx_id),char(100)) as sort
from cte a,(
        select
        requesting_engine_transaction_id as requesting_trx_id,
        blocking_engine_transaction_id as blocking_trx_id,
        requesting_thread_id,
        blocking_thread_id,
        requesting_event_id,
        blocking_event_id
        from performance_schema.data_lock_waits
        group by requesting_engine_transaction_id
) b
where b.blocking_trx_id = a.trx_id
)
select 
lpad(concat((case when a.lv > 1 then '┗' else '' end),a.trx_mysql_thread_id),length(a.trx_mysql_thread_id) + ((lv-1) * 2),' ') as mysql_thread_id, 
a.trx_id,
b.object_name,
b.index_name,
b.lock_type,
b.lock_mode,
b.lock_status,
b.lock_data,
substring(a.trx_query,1,30) as trx_query,
a.trx_lock_memory_bytes,
timediff(now(),a.trx_wait_started) as duration
from 
(select
c.lv,
t.trx_mysql_thread_id,
(case when c.lv = 1 then lead(c.blocking_thread_id) over(order by c.sort) else c.requesting_thread_id end) as requesting_thread_id,
t.trx_id,
t.trx_query,
t.trx_lock_memory_bytes,
c.blocking_trx_id,
(case when c.lv = 1 then lead(c.blocking_event_id) over(order by c.sort) else c.requesting_event_id end) as requesting_event_id,
t.trx_wait_started,
c.sort
from cte c, information_schema.innodb_trx t
where t.trx_id = c.trx_id
) a, (select 
engine_transaction_id,
thread_id,
event_id,
group_concat(distinct trim(trailing '.' from concat(object_schema,'.',object_name,'.',ifnull(partition_name,''),'.',ifnull(subpartition_name,'')))) as object_name,
group_concat(index_name) as index_name,
group_concat(lock_type) as lock_type,
group_concat(lock_mode) as lock_mode,
group_concat(lock_status) as lock_status,
group_concat(lock_data) as lock_data
from performance_schema.data_locks
group by engine_transaction_id,thread_id,event_id
) b
where b.engine_transaction_id = a.trx_id
and b.thread_id = a.requesting_thread_id
and b.event_id = a.requesting_event_id
order by a.sort;

 

# lockDet-txid.sql

select
engine_transaction_id as tx_id,
object_schema,
object_name,
index_name,
lock_type,
lock_mode,
lock_status,
lock_data
from performance_schema.data_locks
where 1=1
and engine_transaction_id = ifnull(@txid,engine_transaction_id);

 

# undoCnt.sql

// 사용 중인 undo 갯수?

select count
from information_schema.innodb_metrics
where subsystem = 'transaction' and name = 'trx_rseg_history_len';

 

# chgbfSize.sql

// update할 때 사용하는 change buffer 사용량 확인

select event_name,current_number_of_bytes_used
from performance_schema.memory_summary_global_by_event_name
where event_name = 'memory/innodb/ibuf0ibuf';

 

# hashIdxUsg.sql

// Hash index 사용량 확인

select event_name,current_number_of_bytes_used
from performance_schema.memory_summary_global_by_event_name
where event_name = 'memory/innodb/adaptive hash index';

 

# tbsUsg-top.sql

// rollup(tablespace,table or index) 사용량 확인

select t.schm,t.name,t.alloc_size_mb
from (
select a.no,a.schm,a.name,a.alloc_size_mb,row_number() over(partition by a.schm order by alloc_size_mb desc,name) as rn
from (
select
lv.no,
(case when lv.no = 3 then '' else sys.extract_schema_from_file_name(name) end) as schm,
(case when lv.no = 1 then name else '' end) as name,
round(ifnull(sum(allocated_size),0)/1024/1024,2) as alloc_size_mb
from information_schema.innodb_tablespaces it, (select 1 as no union all select 2 union all select 3) lv
group by lv.no,(case when lv.no = 3 then '' else sys.extract_schema_from_file_name(name) end),
(case when lv.no = 1 then name else '' end)
) a
) t
where 1=1
and t.rn <= (ifnull(@top,100) + 1)
order by 1 desc,(case when (case when t.no = 1 then t.name else '' end) = '' then null else t.alloc_size_mb end) desc;

 

# notUseUser.sql

// 사용하지 않는 계정 확인

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;

 

#notUpdTab.sql

// 변경이 없는 테이블 목록 확인

select
t.table_schema,
t.table_name,
t.table_rows,
tio.count_read,
tio.count_write
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
order by t.table_schema, t.table_name;

 

#autoInc.sql

// 테이블의 Auto-Increment 컬럼 사용량 확인

select 
table_schema,
table_name,
column_name,
auto_increment as "current_value",
max_value,
round(auto_increment_ratio*100,2) as "usgae_ratio"
from sys.schema_auto_increment_columns;

 

#ftsQry.sql

// 풀 테이블 스캔 쿼리 확인

select 
db,
query,
exec_count,
sys.format_time(total_latency) as "formatted_total_latency",
rows_sent_avg,rows_examined_avg,last_seen
from sys.x$statements_with_full_table_scans
order by total_latency desc \G;

 

#longQry.sql

// 실행시간이 긴 쿼리 목록 확인

select 
query,
exec_count,
sys.format_time(avg_latency) as "formatted_avg_latency",
rows_sent_avg,rows_examined_avg,last_seen
from sys.x$statement_analysis
order by avg_latency desc;

 

# actTrxQry.sql

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

select
ps_t.processlist_id,
ps_esh.thread_id,
concat(ps_t.processlist_user,'@',ps_t.processlist_host) as "db_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.event_id
where ps_etc.state = 'ACTIVE'
and ps_esh.mysql_errno = 0
order by ps_t.processlist_id,ps_esh.timer_start\G;