开发者社区> 阿福Chris> 正文

Deepgreen(Greenplum) DBA常用运维SQL

简介:
+关注继续查看

1.查看对象大小(表、索引、数据库等)

select pg_size_pretty(pg_relation_size(’$schema.$table’));
示例:
tpch=# select pg_size_pretty(pg_relation_size('public.customer'));
 pg_size_pretty
----------------
 122 MB
(1 row)
2.查看用户(非系统)表和索引

tpch=# select * from pg_stat_user_tables;
 relid | schemaname |       relname        | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | last_vacuum | last_autovacuum | last_analyze
| last_autoanalyze
-------+------------+----------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+-------------+-----------------+--------------
+------------------
 17327 | public     | partsupp             |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             |                 |
|
 17294 | public     | customer             |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             |                 |
|
 17158 | public     | part                 |        0 |            0 |          |               |         0 |         0 |         0 |             |                 |
|
 17259 | public     | supplier             |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             |                 |
|
 16633 | gp_toolkit | gp_disk_free         |        0 |            0 |          |               |         0 |         0 |         0 |             |                 |
|
 17394 | public     | lineitem             |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             |                 |
|
 17361 | public     | orders               |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             |                 |
|
 16439 | gp_toolkit | __gp_masterid        |        0 |            0 |          |               |         0 |         0 |         0 |             |                 |
|
 49164 | public     | number_xdrive        |        0 |            0 |          |               |         0 |         0 |         0 |             |                 |
|
 17193 | public     | region               |        0 |            0 |          |               |         0 |         0 |         0 |             |                 |
|
 49215 | public     | number_gpfdist       |        0 |            0 |          |               |         0 |         0 |         0 |             |                 |
|
 16494 | gp_toolkit | __gp_log_master_ext  |        0 |            0 |          |               |         0 |         0 |         0 |             |                 |
|
 40972 | public     | number               |        0 |            0 |          |               |         0 |         0 |         0 |             |                 |
|
 16413 | gp_toolkit | __gp_localid         |        0 |            0 |          |               |         0 |         0 |         0 |             |                 |
|
 16468 | gp_toolkit | __gp_log_segment_ext |        0 |            0 |          |               |         0 |         0 |         0 |             |                 |
|
 17226 | public     | nation               |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             |                 |
|
(16 rows)

tpch=# select * from pg_stat_user_indexes;
 relid | indexrelid | schemaname | relname  |      indexrelname       | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+----------+-------------------------+----------+--------------+---------------
 17259 |      17602 | public     | supplier | idx_supplier_nation_key |        0 |            0 |             0
 17327 |      17623 | public     | partsupp | idx_partsupp_partkey    |        0 |            0 |             0
 17327 |      17644 | public     | partsupp | idx_partsupp_suppkey    |        0 |            0 |             0
 17294 |      17663 | public     | customer | idx_customer_nationkey  |        0 |            0 |             0
 17361 |      17684 | public     | orders   | idx_orders_custkey      |        0 |            0 |             0
 17394 |      17705 | public     | lineitem | idx_lineitem_orderkey   |        0 |            0 |             0
 17394 |      17726 | public     | lineitem | idx_lineitem_part_supp  |        0 |            0 |             0
 17226 |      17745 | public     | nation   | idx_nation_regionkey    |        0 |            0 |             0
 17394 |      17766 | public     | lineitem | idx_lineitem_shipdate   |        0 |            0 |             0
 17361 |      17785 | public     | orders   | idx_orders_orderdate    |        0 |            0 |             0
(10 rows)
3.查看表分区

select b.nspname||'.'||a.relname as tablename, d.parname as partname from pg_class a, pg_namespace b, pg_partition c,pg_partition_rule d where a.relnamespace = b.oid and b.nspname = 'public' and a.relname = 'customer' and a.oid = c.parrelid and c.oid = d.paroid order by parname;
 tablename | partname
-----------+----------
(0 rows)
4.查看Distributed key

tpch=# select b.attname from pg_class a, pg_attribute b, pg_type c, gp_distribution_policy d, pg_namespace e where d.localoid = a.oid and a.relnamespace = e.oid and e.nspname = 'public' and a.relname= 'customer' and a.oid = b.attrelid and b.atttypid = c.oid and b.attnum > 0 and b.attnum = any(d.attrnums) order by attnum;
  attname
-----------
 c_custkey
(1 row)
5.查看当前存活的查询

select procpid as pid, sess_id as session, usename as user, current_query as query, waiting,date_trunc('second',query_start) as start_time, client_addr as useraddr from pg_stat_activity where datname ='$PGDATABADE'
and current_query not like '%from pg_stat_activity%where datname =%' order by start_time;

示例:
tpch=# select procpid as pid, sess_id as session, usename as user, current_query as query, waiting,date_trunc('second',query_start) as start_time, client_addr as useraddr from pg_stat_activity where datname ='tpch'
tpch-# and current_query not like '%from pg_stat_activity%where datname =%' order by start_time;
 pid | session | user | query | waiting | start_time | useraddr
-----+---------+------+-------+---------+------------+----------
(0 rows)

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
集群运维2:监控、SQL限流与索引优化 | 学习笔记(2)
快速学习集群运维2:监控、SQL限流与索引优化
33 0
集群运维2:监控、SQL 限流与索引优化 | 学习笔记(一)
快速学习集群运维2:监控、SQL限流与索引优化
47 0
PostgreSQL集群篇——常用的运维SQL
本文主要是我日常使用的一些运维SQL和整理于互联网上的SQL,为了方便日常的使用,特把其汇总起来,遇到常用的时将会进行补充该文,欢迎大家在评论区进行提出一些常用的SQL。
136 0
AnalyticDB PostgreSQL常用运维SQL与命令练习题
AnalyticDB PostgreSQL常用运维SQL与命令练习题
92 0
SQL Server DBA日常运维语句(下)
今天我们介绍一下,日常工作中的一些运维语句,可以当作工具直接来使用。
58 0
SQL Server DBA日常运维语句(上)
今天我们介绍一下,日常工作中的一些运维语句,可以当作工具直接来使用。
60 0
sql server 运维时CPU,内存,操作系统等信息查询(用sql语句)
原文:sql server 运维时CPU,内存,操作系统等信息查询(用sql语句) 我们只要用到数据库,一般会遇到数据库运维方面的事情,需要我们寻找原因,有很多是关乎处理器(CPU)、内存(Memory)、磁盘(Disk)以及操作系统的,这时我们就需要查询他们的一些设置和内容,下面讲的就是如何查询它们的相关信息。
941 0
+关注
阿福Chris
Greenplum,Deepgreen
文章
问答
文章排行榜
最热
最新
相关电子书
更多
企业运维之云上网络原理与实践
立即下载
企业运维之弹性计算原理与实践
立即下载
企业运维之数据库原理与实践
立即下载