1.如何获取查询运行时和已用时间。
例子:
Select tstart, tfinish, (tfinish-tstart) as total_time, trim(query_text)
from queries_history
Where tstart >= '2011-07-07 11:00:00' and tstart < '2011-07-07 13:00:00'
and db ='yourdatabasename'
Order by tstart;
2。
*查看上次执行的操作
gpdb=# SELECT schemaname as schema, objname as table, usename as role, actionname as action,
gpdb-# subtype as type, statime as time
gpdb-# FROM pg_stat_operations
gpdb-# WHERE objname='cust';
schema | table | role | action | type | time
--------+-------+------+--------+------+------
(0 rows)
*最后分析或创建表或ETC...
Select * from pg_stat_operations
where schemaname='SCHEMA NAME '
and actionname in ('ANALYZE','VACUUM')
order by statime; gpdb=# Select * from pg_stat_operations
gpdb-# where schemaname='SCHEMA NAME '
gpdb-# and actionname in ('ANALYZE','VACUUM')
gpdb-# order by statime;
classname | objname | objid | schemaname | usestatus | usename | actionname | subty
pe | statime
-----------+---------+-------+------------+-----------+---------+------------+------
---+---------
(0 rows)
3.显示已关闭的细分。
select * from gp_segment_configuration where status='d';
4.如何计算磁盘中的数据库大小？
select pg_size_pretty(pg_database_size('test1'));
5.如何计算磁盘中的表大小？
select pg_size_pretty(pg_relation_size('gpdb' ));
6.对于分区表，您需要使用下面的sql来查找累积表大小
select sum(pg_total_relation_size(tablename)) from pg_tables where tablename like 'table_name%' ;
7。
*授予/撤消模式中对象的权限的功能
*撤销模式对象（表，视图，序列）权限的功能
8. GreenPlum数据库GUI工具
9.您可能喜欢的数据偏斜查询
gpdb=# select gp_segment_id, count(*) , abs(count(*) - avg(count(*)) over(order by gp_segment_id rowsbetween unbounded preceding and unbounded following))/count(*) skewfrom test1group by gp_segment_idorder by skew desclimit 10;
gp_segment_id | count | skew
---------------+-------+------------------------
1 | 2 | 0.40000000000000000000
3 | 1 | 0.20000000000000000000
4 | 1 | 0.20000000000000000000
6 | 1 | 0.20000000000000000000
0 | 1 | 0.20000000000000000000
(5 rows)
10。
*segments
gpdb=# SELECT count(*)::smallint AS numsegments FROM gp_segment_configurationWHERE gp_segment_configuration.preferred_role = 'p'::"char" ANDgp_segment_configuration.content >= 0;
numsegments
-------------
8
(1 row)
*segments的大小
gpdb=# SELECT pg_size_pretty(dfspace)FROM gp_toolkit.gp_disk_freeORDER BY dfsegment;
pg_size_pretty
----------------
39 MB
39 MB
39 MB
39 MB
39 MB
39 MB
39 MB
39 MB
(8 rows)
11.确定当前的主段配置
gpdb=# SELECT dbid, content, address as host_address, port, replication_port, fselocation as datadirFROM gp_segment_configuration, pg_filespace_entryWHERE dbid=fsedbidORDER BY dbid;
dbid | content | host_address | port | replication_port | datadir ------+---------+--------------+-------+------------------+-------------------------
--------
1 | -1 | greenplum01 | 5432 | | /greenplum/data/master/g
pseg-1
2 | 0 | greenplum02 | 6000 | 34000 | /greenplum/data/primary/
gpseg0
3 | 1 | greenplum02 | 6001 | 34001 | /greenplum/data/primary/
gpseg1
4 | 2 | greenplum02 | 6002 | 34002 | /greenplum/data2/primary
/gpseg2
5 | 3 | greenplum02 | 6003 | 34003 | /greenplum/data2/primary
/gpseg3
6 | 4 | greenplum03 | 6000 | 34000 | /greenplum/data/primary/
gpseg4
7 | 5 | greenplum03 | 6001 | 34001 | /greenplum/data/primary/
gpseg5
8 | 6 | greenplum03 | 6002 | 34002 | /greenplum/data2/primary
/gpseg6
9 | 7 | greenplum03 | 6003 | 34003 | /greenplum/data2/primary
/gpseg7
10 | 0 | greenplum03 | 43000 | 44000 | /greenplum/data/mirror/g
pseg0
11 | 1 | greenplum03 | 43001 | 44001 | /greenplum/data/mirror/g
pseg1
12 | 2 | greenplum03 | 43002 | 44002 | /greenplum/data2/mirror/
gpseg2
13 | 3 | greenplum03 | 43003 | 44003 | /greenplum/data2/mirror/
gpseg3
14 | 4 | greenplum02 | 43000 | 44000 | /greenplum/data/mirror/g
pseg4
15 | 5 | greenplum02 | 43001 | 44001 | /greenplum/data/mirror/g
pseg5
16 | 6 | greenplum02 | 43002 | 44002 | /greenplum/data2/mirror/
12.时区设置：
gpdb=# BEGIN;
gpdb=# SELECT NOW();
gpdb=# SET timezone TO '-8';
gpdb=# SELECT NOW();
13.Re-create GP Toolkit objects:
You only get one shot, do not miss your chance to blow.