Xpand 유용한 쿼리

- table별 사용량

select
`database`, `table`,round(bytes/1024/1024) as Size_MB
from system.table_sizes
where `database`='clustrix_statd';

 

- 사용자 정보(xpand는 mysql database가 없음)

select * from system.users;

 

- DB용량 확인

select name, format(value,1)
from statd_metadata natural join statd_current
where name like 'clustrix.capacity.disks%'
order by name;

 

- DB 사용률

select name, timestamp, value
from statd_metadata natural join statd_history
where name like 'clustrix.cpu.load_avg'
order by timestamp desc limit 10;

 

- Node별 DB 읽기/쓰기 정보

SELECT
tr.`Database`,
cs.nodeid,
sum(cs.reads) as reads,
sum(cs.inserts + cs.deletes
+ cs.replaces) as writes
FROM system.container_stats cs
JOIN system.table_replicas tr
ON cs.replica = tr.replica
GROUP BY 1,2
ORDER BY 1,2;

 

- System Activity 별 자원 사용률

select * from system.activity order by 1;

o CPU usage ( 0.1 = 10% ) : core0: CPU % of just core0, til_min, _avg, _max: CPU% of the other cores
o Network: recv_mbps, send_mbps: Network IO activity
o Disk: read_MBs, write_MBs: Disk IO activity

 

- Connections/Sessions

select * from statd_metadata natural join statd_history
where name = 'clustrix.stats.connections'
order by timestamp desc limit 10;

 

select * from statd_metadata natural join statd_history
where name = 'clustrix.stats.executing_sessions'
order by timestamp desc limit 10;

 

- 세션별 벨런스 확인

select nodeid, count(1)
from system.sessions
group by nodeid order by nodeid;

 

SELECT nodeid, database, time_in_state_s, xid, last_statement
FROM system.sessions
WHERE statement_State = 'executing'
ORDER BY time_in_state_s desc limit 5 \G;

 

SELECT database, nodeid, count(*)
FROM system.sessions
GROUP BY 1,2 ORDER BY 1,2;

 

- 쿼리별 정보

SELECT
database, left(statement, 25) as stmt_group_25, query_id,
exec_count, exec_ms as total_cpu_ms, avg_latency_ms as avg_exec_ms,
(exec_ms / exec_count) as cpu_ms_per,
(exec_count * avg_latency_ms) total_exec_ms,
(compile_ms / compile_count) as avg_compile_ms,
reads, inserts, deletes, updates,
rows_read, rows_output, broadcasts,
lockman_waits, lockman_waittime_ms, trxstate_waits, trxstate_waittime_ms
FROM system.qpc_queries
WHERE flushed = 0
ORDER BY
total_cpu_ms desc -- Top most CPU consuming queries
-- total_exec_ms -- Top longest queries
-- rows_read -- Top readers
-- inserts + deletes + updates -- Top writers
-- broadcasts -- Top broadcasting
-- lockman_waittime_ms -- Top waiting for locks
limit 20;

 

SELECT statement from system.qpc_queries where query_id = q <uery_id>;

 

- 라이선스 확인

select system.license_valid();
select system.license_status();