DWS运维常用SQL

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

1. 查看用户及连接:

连接数不够会导致业务大量报错，因此，有必要监控集群上各个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

2. 查看活跃语句及执行时间：

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

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

3. 查看锁等待情况：

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

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

select * from pgxc_locks_wait;

4. 查杀语句:

• 通过查看活跃语句及执行时间，找到coorname和pid,例如cn_5001和139906305218304
• 执行execute direct on (cn_5001) 'select pg_terminate_backend(139906305218304)';
• 查看结果是否为true

5. 查看库内所有表大小：

通过以下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;

6. 查看数据倾斜

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

SELECT * FROM pgxc_get_table_skewness ORDER BY totalsize DESC;

7. 查看库大小：

select pg_database_size('your_database_name')

8. 查看脏页率：

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;

9. 查询系统内所有表行数:

使用以下两步可以获取库中所有表实际行数，建议在表数量小于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执行窗口，进行执行。