GaussDB(DWS)問題定位常用SQL

1、查詢本地正在運(yùn)行中的事務(wù)的狀態(tài)情況

select * from pg_get_running_xacts();

2、查詢集群中正在運(yùn)行的所有事務(wù)的情況

select * from pgxc_get_running_xacts();

3、查詢當(dāng)前用戶正在執(zhí)行的查詢?nèi)蝿?wù)

select * from PG_STAT_ACTIVITY;

4、查詢鎖的使用情況

select l.locktype,l.relation,l.pid,l.mode,l.granted,p.query_start,p.query,p.state from pg_locks l,pg_stat_activity p where l.locktype='relation' and l.pid=p.pid and query not like '%pg_stat_activity%';

5、查詢當(dāng)前實(shí)例上整體內(nèi)存使用狀態(tài)和信息

SELECT * FROM pv_total_memory_detail;

6、查詢內(nèi)存上下文級(jí)別的內(nèi)存占用詳細(xì)信息

SELECT * FROM pv_session_memory_detail ORDER BY totalsize desc LIMIT 10;

7、查詢集群每個(gè)物理節(jié)點(diǎn)內(nèi)存、每個(gè)節(jié)點(diǎn)dn個(gè)數(shù)

SELECT sessid, contextname, level,parent,totalsize,freesize,usedsize, datname,query_id FROM pv_session_memory_detail a , pg_stat_activity b WHERE split_part(a.sessid,'.',2) = b.pid and b.state='active' ORDER BY usedsize desc limit 20 ;

8、查詢監(jiān)控session total memory size占用最多的TOP20 session

SELECT sessid, sum_total, sum_free,sum_used, query_id, query_start, state, waiting, enqueue,query from (select sessid, sum(totalsize) as sum_total, sum(freesize) as sum_free, sum(usedsize) as sum_used from pv_session_memory_detail group by sessid ORDER BY sum_total desc limit 20 ) a , pg_stat_activity b WHERE split_part(a.sessid,'.',2) = b.pid;

9、監(jiān)控session中占用內(nèi)存最多的context TOP20 session

SELECT sessid, contextname, level,parent, pg_size_pretty(totalsize),pg_size_pretty(freesize),pg_size_pretty(usedsize), datname,query_id, query from pv_session_memory_detail a , pg_stat_activity b WHERE split_part(a.sessid,'.',2) = b.pid order by totalsize desc limit 20 ;

10、監(jiān)控當(dāng)前實(shí)例總totalsize memroy大小

SELECT pg_size_pretty(sum(totalsize)) FROM pv_session_memory_detail;

11、監(jiān)控當(dāng)前實(shí)例總usedsize memroy大小

SELECT pg_size_pretty(sum(usedsize)) FROM pv_session_memory_detail;

12、監(jiān)控當(dāng)前實(shí)例內(nèi)存總體使用情況

SELECT * FROM pg_total_memory_detail;

13、監(jiān)控共享內(nèi)存實(shí)時(shí)使用情況

SELECT * FROM pg_shared_memory_detail;

EI企業(yè)智能 Gauss AP 數(shù)據(jù)倉(cāng)庫(kù)服務(wù) GaussDB(DWS)

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。