GaussDB(DWS)运维常用SQL

GaussDB DWS 2022-02-18

2940

DWS提供了丰富的接口、视图来用于查看和诊断当前集群的运行状况，为了提高运维效率，现整理一些比较常用的，供DBA、DWS运维人员参考。

查看用户及连接

连接数不够会导致业务大量报错，因此，有必要监控集群上各个CN上的连接数，确保其在正常范围内，集群内每个CN的最大连接数可以通过show max_connections得到，集群当前已使用的连接数可以用以下SQL查询。其中活跃连接指当前正在使用的连接，缓存连接指数据库内部连接池缓存的连接，这两种连接都会占用数据库连接数，因此都需要进行监控。

活跃连接：select usename, count(*) from pgxc_stat_activity where usename != 'Ruby' group by 1 order by 2 desc

活跃+缓存连接：Select usename, count(*) from pgxc_stat_activity where usename != 'Ruby' group by 1 order by 2 desc

查看活跃语句及执行时间

通过查看活跃语句及执行时间，可以找出当前运行时间较长的语句，分析是否有问题。

Select now()-query_start,* from pgxc_stat_activity where state='active' and usename != 'Ruby' order by 1 desc;

查看锁等待情况

通过查看锁等待情况，可以找出当前出现锁冲突的SQL，并进行解决

首先执行附件创建相关视图,然后执行以下视图查询锁等待情况。

select * from pgxc_locks_wait;

附件：

向上滑动阅览

DROP FUNCTION PUBLIC.pgxc_relation_locks() cascade;

CREATE OR REPLACE FUNCTION PUBLIC.pgxc_relation_locks

(

OUT client_addr inet,

OUT application_name text,

OUT nodename text,

OUT datname text,

OUT usename text,

OUT locktype text,

OUT nspname text,

OUT relname text,

OUT partname text,

OUT mode text,

OUT granted text,

OUT pid bigint,

OUT xact_start timestamptz,

OUT query_start timestamptz,

OUT state_change timestamptz,

OUT waiting boolean,

OUT enqueue text,

OUT state text,

OUT query_id bigint,

OUT query text

)

RETURNS setof RECORD

AS $$

DECLARE

row_data record;

node record;

locks_info text;

fetch_node_str text;

BEGIN

fetch_node_str := 'SELECT node_name FROM pgxc_node';

FOR node IN EXECUTE(fetch_node_str) LOOP

nodename := node.node_name;

locks_info := 'EXECUTE DIRECT ON (' || node.node_name || ') ''with pg_oid_locks as

(

select

locktype,

database,

case when k.locktype = ''''relation'''' then k.relation

when k.locktype = ''''partition'''' then k.classid

end as relid,

case when k.locktype = ''''relation'''' then NULL

when k.locktype = ''''partition'''' then k.objid

end as partid,

pid,

mode,

case when granted = true then ''''hold lock''''

else ''''acquire lock''''

end as granted

from pg_locks k

where k.locktype in (''''relation'''', ''''partition'''') and pid <> pg_backend_pid()

pg_readable_locks as

(

select

locktype,

database,

n.nspname,

c.relname,

p.relname as partname,

pid,

mode,

granted

from pg_oid_locks k

inner join pg_class c on (c.oid = k. relid)

inner join pg_namespace n on (n.oid = c.relnamespace)

left join pg_partition p on (p.parentid = k.relid and p.oid = partid)

pg_active_locks as

(

select

client_addr,

application_name,

datname,

usename,

locktype,

nspname,

relname,

partname,

mode,

granted,

s.pid,

xact_start,

query_start,

state_change,

waiting,

enqueue,

state,

query_id,

query

from pg_stat_activity s

inner join pg_readable_locks k on (s.pid = k.pid)

)

select * from pg_active_locks

''';

FOR row_data IN EXECUTE(locks_info) LOOP

client_addr := row_data.client_addr;

application_name := row_data.application_name;

datname := row_data.datname;

usename := row_data.usename;

locktype := row_data.locktype;

nspname := row_data.nspname;

relname := row_data.relname;

partname := row_data.partname;

mode := row_data.mode;

granted := row_data.granted;

pid := row_data.pid;

xact_start := row_data.xact_start;

query_start := row_data.query_start;

state_change := row_data.state_change;

waiting := row_data.waiting;

enqueue := row_data.enqueue;

state := row_data.state;

query_id := row_data.query_id;

query := row_data.query;

RETURN NEXT;

END LOOP;

return;

END; $$

LANGUAGE 'plpgsql';

CREATE OR REPLACE VIEW PUBLIC.pgxc_relation_locks AS SELECT * FROM PUBLIC.pgxc_relation_locks() ORDER BY query_id, nspname, relname, partname, locktype;

CREATE or replace VIEW PUBLIC.pgxc_locks_wait AS

WITH lockMatrix (lock_type, lock_level, conflict_level) AS

(

VALUES

('AccessShareLock', 1, 8),

('RowShareLock', 2, 7),

('RowExclusiveLock', 3, 5),

('ShareUpdateExclusiveLock', 4, 4),

('ShareLock', 5, 3),

('ShareRowExclusiveLock', 6, 3),

('ExclusiveLock', 7, 2),

('AccessExclusiveLock', 8, 1)

lock_2_level AS

(

SELECT

client_addr ,

application_name ,

nodename ,

datname ,

usename ,

locktype ,

nspname ,

relname ,

partname ,

mode ,

lock_level ,

conflict_level ,

granted ,

pid ,

xact_start ,

query_start ,

state_change ,

waiting ,

enqueue ,

state ,

query_id ,

query

FROM PUBLIC.pgxc_relation_locks() k

INNER JOIN lockMatrix m on (m.lock_type = k.mode)

)

SELECT

t1.client_addr ,

t1.application_name ,

t1.nodename ,

t1.datname ,

t1.usename ,

t1.locktype ,

t1.nspname ,

t1.relname ,

t1.partname ,

t1.mode ,

t1.granted ,

t1.pid ,

now() - t1.query_start wait_time,

t1.xact_start ,

t1.query_start ,

t1.state_change ,

t1.waiting ,

t1.enqueue ,

t1.state ,

t1.query_id ,

t1.query ,

t2.pid as block_pid,

t2.mode as block_mode,

t2.granted as block_granted,

t2.query_id as block_query_id,

t2.query as block_query

FROM lock_2_level t1

INNER JOIN lock_2_level t2 ON (t1.nodename = t2.nodename AND t1.nspname = t2.nspname AND t1.relname = t2.relname AND ((t1.partname = t2.partname) or (t1.partname IS NULL AND t2.partname IS NULL)))

WHERE t1.granted = 'acquire lock'

AND t2.granted = 'hold lock'

AND t1.conflict_level <= t2.lock_level

ORDER BY wait_time desc, nspname, relname, partname;

查杀语句

通过查看活跃语句及执行时间，找到coorname和pid,例如cn_5001和139906305218304

执行execute direct on (cn_5001) 'select pg_terminate_backend(139906305218304)';

查看结果是否为true

查看库内所有表大小

通过以下SQL可以查看库内所有表大小。建议在表数量不多时使用，库内超过1000张表时，运行速度可能较慢。

Select nspname, relname, pg_table_size(c.oid) from pg_class c, pg_namespace n where c.relnamespace = n.oid and c.relkind = 'r' order by 3 desc;

查看数据倾斜

建议在表数量不多时使用，库内超过1000张表时，运行速度可能较慢。

SELECT * FROM pgxc_get_table_skewness ORDER BY totalsize DESC;

查看库大小

select pg_database_size('your_database_name')

查看脏页率

DWS表数据在经过更新、删除后，会产生脏页，脏页会占用空间，需要使用vacuum full命令清理。通过以下命令可以检查脏页率情况。注意如果检查过程中有表被删除，此SQL可能报错，找其他时间重新运行即可。

SELECT c.oid AS relid, n.nspname AS schemaname, c.relname,

pg_stat_get_live_tuples(c.oid) AS n_live_tup,

pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,

round(n_dead_tup * 100 / (n_live_tup + n_dead_tup+0.0001),2) AS dead_tup_ratio

FROM pg_class c

LEFT JOIN pg_index i ON c.oid = i.indrelid

LEFT JOIN pg_namespace n ON n.oid = c.relnamespace

WHERE c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])

GROUP BY c.oid, n.nspname, c.relname

order by dead_tup_ratio desc;

查询系统内所有表行数

使用以下两步可以获取库中所有表实际行数，建议在表数量小于1000时使用，表数量较大时执行可能较慢。

执行以下语句：select string_agg(a.v_sql,'union all ') from (select 'select '''||relname||''',count(*) from '||relname||' ' as v_sql from pg_class where relnamespace=2200 and relkind='r') a

将执行结果拷贝到SQL执行窗口，进行执行。

往期精彩回顾

华为云GaussDB(DWS)携手兴盛优选，一站式实现数据业务科学决策 NEW
华为云GaussDB(DWS)助招行摘得2021 IDC中国金融行业技术应用场景创新奖 HOT
数据仓库GaussDB(DWS)蝉联“2021 中国大数据·数据仓库领域最佳产品奖”
华为云GaussDB(DWS)数据仓库以2048大规模节点通过信通院评测认证
数仓性能调优必读，带你进阶为性能调优高手

gaussdb

文章转载自GaussDB DWS，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

GaussDB(DWS)运维常用SQL

华为云GaussDB(DWS)携手兴盛优选，一站式实现数据业务科学决策 NEW

华为云GaussDB(DWS)助招行摘得2021 IDC中国金融行业技术应用场景创新奖 HOT

数据仓库GaussDB(DWS)蝉联“2021 中国大数据·数据仓库领域最佳产品奖”

评论